[Oracle] PL/SQL 예제

 

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 예제

https://hermeslog.tistory.com/289

[Oracle] Procedure 예제

https://hermeslog.tistory.com/291

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