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