헤르메스 LIFE

[Oracle] PL/SQL 예제 본문

Database

[Oracle] PL/SQL 예제

헤르메스의날개 2015. 10. 2. 11:32
728x90

 

FOR..LOOP

DECLARE
BEGIN
    DBMS_OUTPUT.ENABLE;

    FOR EMP_LIST IN  (
        SELECT *
          FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYYMMDD') BETWEEN '20000101' AND '20101231'
           AND LAST_NAME = 'King'
    ) LOOP

        DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID :: ' || EMP_LIST.EMPLOYEE_ID);
        DBMS_OUTPUT.PUT_LINE('FIRST_NAME :: ' || EMP_LIST.FIRST_NAME);
        DBMS_OUTPUT.PUT_LINE('LAST_NAME :: ' || EMP_LIST.LAST_NAME);
        DBMS_OUTPUT.PUT_LINE('EMAIL :: ' || EMP_LIST.EMAIL);
        DBMS_OUTPUT.PUT_LINE('PHONE_NUMBER :: ' || EMP_LIST.PHONE_NUMBER);
        DBMS_OUTPUT.PUT_LINE('HIRE_DATE :: ' || EMP_LIST.HIRE_DATE);
        DBMS_OUTPUT.PUT_LINE('JOB_ID :: ' || EMP_LIST.JOB_ID);
        DBMS_OUTPUT.PUT_LINE('SALARY :: ' || EMP_LIST.SALARY);
        DBMS_OUTPUT.PUT_LINE('COMMISSION_PCT :: ' || EMP_LIST.COMMISSION_PCT);
        DBMS_OUTPUT.PUT_LINE('MANAGER_ID :: ' || EMP_LIST.MANAGER_ID);
        DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID :: ' || EMP_LIST.DEPARTMENT_ID);
        
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('SQLCODE :: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM :: ' || SQLERRM);
END;

CURSOR

DECLARE
    CURSOR CUR_EMP IS
        SELECT *
          FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYYMMDD') BETWEEN '20000101' AND '20101231'
           AND LAST_NAME = 'King'
    ;
    
BEGIN
    FOR CUR IN CUR_EMP LOOP
        BEGIN
        
            DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID :: ' || CUR.EMPLOYEE_ID);
            DBMS_OUTPUT.PUT_LINE('FIRST_NAME :: ' || CUR.FIRST_NAME);
            DBMS_OUTPUT.PUT_LINE('LAST_NAME :: ' || CUR.LAST_NAME);
            DBMS_OUTPUT.PUT_LINE('EMAIL :: ' || CUR.EMAIL);
            DBMS_OUTPUT.PUT_LINE('PHONE_NUMBER :: ' || CUR.PHONE_NUMBER);
            DBMS_OUTPUT.PUT_LINE('HIRE_DATE :: ' || CUR.HIRE_DATE);
            DBMS_OUTPUT.PUT_LINE('JOB_ID :: ' || CUR.JOB_ID);
            DBMS_OUTPUT.PUT_LINE('SALARY :: ' || CUR.SALARY);
            DBMS_OUTPUT.PUT_LINE('COMMISSION_PCT :: ' || CUR.COMMISSION_PCT);
            DBMS_OUTPUT.PUT_LINE('MANAGER_ID :: ' || CUR.MANAGER_ID);
            DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID :: ' || CUR.DEPARTMENT_ID);
        END;
    END LOOP;
    
EXCEPTION
  WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('SQLCODE :: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM :: ' || SQLERRM);
END;
DECLARE
    CURSOR CUR_EMP IS
        SELECT *
          FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYYMMDD') BETWEEN '20000101' AND '20101231'
           AND LAST_NAME = 'King'
    ;
    
    CUR     EMPLOYEES%ROWTYPE;
BEGIN
    OPEN CUR_EMP;
    
    LOOP
        FETCH CUR_EMP
         INTO CUR;
        
        EXIT WHEN CUR_EMP%NOTFOUND;

        BEGIN
        
            DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID :: ' || CUR.EMPLOYEE_ID);
            DBMS_OUTPUT.PUT_LINE('FIRST_NAME :: ' || CUR.FIRST_NAME);
            DBMS_OUTPUT.PUT_LINE('LAST_NAME :: ' || CUR.LAST_NAME);
            DBMS_OUTPUT.PUT_LINE('EMAIL :: ' || CUR.EMAIL);
            DBMS_OUTPUT.PUT_LINE('PHONE_NUMBER :: ' || CUR.PHONE_NUMBER);
            DBMS_OUTPUT.PUT_LINE('HIRE_DATE :: ' || CUR.HIRE_DATE);
            DBMS_OUTPUT.PUT_LINE('JOB_ID :: ' || CUR.JOB_ID);
            DBMS_OUTPUT.PUT_LINE('SALARY :: ' || CUR.SALARY);
            DBMS_OUTPUT.PUT_LINE('COMMISSION_PCT :: ' || CUR.COMMISSION_PCT);
            DBMS_OUTPUT.PUT_LINE('MANAGER_ID :: ' || CUR.MANAGER_ID);
            DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID :: ' || CUR.DEPARTMENT_ID);
        END;
    END LOOP;
    
    CLOSE CUR_EMP;
    
EXCEPTION
  WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('SQLCODE :: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM :: ' || SQLERRM);
END;

 


https://hermeslog.tistory.com/287

 

[Oracle] Function 예제

CREATE OR REPLACE FUNCTION F_GET_DATA_ID ( I_VALLUE_1 VARCHAR2, I_VALLUE_2 VARCHAR2 ) /******************************************************************* PROGRAM NAME : 프로그램명 DESCRIPTION : 설명 AUTHOR : 성명 HISTORY : 날짜 ****************

hermeslog.tistory.com

https://hermeslog.tistory.com/289

 

[Oracle] Procedure 예제

CREATE OR REPLACE PROCEDURE USER_PROC ( V_VALUE_1 IN VARCHAR2, V_VALUE_2 IN VARCHAR2, V_DEPTNO IN EMP.DEPTNO%TYPE , O_ERRORCODE OUT VARCHAR2, O_ERRORMESG OUT VARCHAR2 ) IS /***************************************************************** Program Name :

hermeslog.tistory.com

https://hermeslog.tistory.com/291

 

[Oracle] PL/SQL PREDEFINED EXCEPTION - 미리정의된 예약

출처 : http://safitservice.blogspot.jp/2014/01/oracle-plsql-predefined-exception.html 오랜만에 PL/SQL을 하다 보니 예외처르 부분에 대해서 작성하다가 자꾸 책을 찾아 보게 되서 정리된 내용을 블러그에 올려 놓고

hermeslog.tistory.com

 

728x90