[Day15] Oracle 15 - trigger, package, cursor+paramete, 에러처리, 동적쿼리

[Day15] Oracle15 [10/18]

문제 1)tbl_score 테이블에 새로운 학생의 성적 정보를 저장하는 프로시저 : up_insScore
p : num, name, kor, eng, mat 입력하지 않으면 0 처리
총점, 평균, 등급 까지는 처리가 되도록 ..

CREATE OR REPLACE PROCEDURE up_insScore
(
      pnum   IN    tbl_score.num%type
    , pname  IN    tbl_score.name%type
    , pkor   IN    tbl_score.kor%type
    , peng   IN    tbl_score.eng%type
    , pmat   IN    tbl_score.mat%type
)
IS
   vtot tbl_score.tot%type;
   vavg tbl_score.avg%type;
   vgrade tbl_score.grade%type;
   -- vrank insa.rank%type;
BEGIN
   vtot := pkor + peng + pmat;
   vavg := vtot / 3;
   vgrade := CASE 
                       WHEN vavg >= 90 THEN 'A'
                       WHEN vavg >= 80 THEN 'B'
                       WHEN vavg >= 70 THEN 'C'
                       WHEN vavg >= 60 THEN 'D'
                       ELSE 'F'
              END;
    INSERT INTO tbl_score ( num, name, kor, eng, mat, tot, avg, grade )
       VALUES ( pnum, pname, pkor, peng, pmat, vtot, vavg, vgrade );
    
    up_rankScore;   --  또 다른 저장 프로시저 호출/실행
    COMMIT;
-- EXCEPTION
END;

EXEC UP_INSSCORE( 1102, '박신입', 99,88,67);

SELECT * 
FROM tbl_score;

문제 5) 등수를 처리하는 프로시저 : up_rankScore

CREATE OR REPLACE PROCEDURE up_rankScore
IS 
  vrank tbl_score.rank%type;
BEGIN
    UPDATE tbl_score t
    SET rank = (  SELECT COUNT(*)+1 FROM tbl_score WHERE tot > t.tot ) ;
    -- WHERE
-- EXCEPTION
END;

EXEC UP_RANKSCORE;

-- 각 팀에서 풀이~~

문제 2) tbl_score 테이블에 새로운 학생의 성적 정보를 수정하는 프로시저 : up_updScore
   p  : num,   kor, eng, mat  입력하지 않으면  수정 전의 점수로 처리
     총점, 평균, 등급 까지는 처리가 되도록 ..

문제 3)      tbl_score 테이블에  삭제하는 프로시저 : up_delScore
  p  : num

문제 4)   tbl_score 테이블에  모든 학생 정보를 조회하는 프로시저 : up_selScore

-------------------------------------------------------------------------------------
-- PLSQL  문법 :  IF/WHILE/FOR 문 := INTO

-- 예) 저장 프로시저
--     회원가입   중    "ID 중복 체크"
--    ID [         keni      ]  [ID 중복체크 버튼]
     이미 사용 중인 아이디 입니다.
     사용 가능한 아이디 입니다.

CREATE OR REPLACE PROCEDURE up_idCheck
(
    pempno  IN emp.empno%type
    ,pempnoCheck OUT NUMBER --  사용가능 0,   사용불가능 1
)
IS
BEGIN
   SELECT COUNT(*) INTO pempnoCheck
   FROM emp
   WHERE empno = pempno;
-- EXCEPTION
END;

-- 프로시저 테스트 용도의 익명 프로시저.

DECLARE 
   vempno  emp.empno%type;
   vempnoCheck NUMBER(1);
BEGIN
    vempno := 7369;
   up_idCheck( vempno, vempnoCheck );
   -- DBMS_OUTPUT.PUT_LINE( vempnoCheck );  --0 , 1
   IF vempnoCheck = 0 THEN
      DBMS_OUTPUT.PUT_LINE( '사용 가능한 아이디 입니다.' );
   ELSE
      DBMS_OUTPUT.PUT_LINE( '이미 사용 중인 아이디 입니다.' );
   END IF;
END;

-- 문제) ID/PWD 입력해서 로그인 처리
--      empno/ename
--    로그인 성공/ ID존재X / ID존재O PWD X
        0            1          -1
--        저장 프로시저 생성 + 테스트  up_logon

CREATE OR REPLACE PROCEDURE up_logon
(
    pempno    emp.empno%type
    , pename  emp.ename%type
    , plogonCheck OUT  NUMBER  -- 크기 X  성공 0  ID X -1            1
)
IS
   vename emp.ename%type;
BEGIN
    SELECT COUNT(*)  INTO plogonCheck -- 1,  0
    FROM emp
    WHERE empno = pempno;  -- AND ename = pename
    
    IF plogonCheck = 1  THEN  -- ID 존재한다.
        SELECT ename INTO vename
        FROM emp
        WHERE empno = pempno;        
        IF  vename = pename THEN
            plogonCheck := 0;
        ELSE
            plogonCheck := 1;
        END IF;        
    ELSE 
       plogonCheck := -1;
    END IF;
-- EXCEPTION
END;

DECLARE
  vlogonCheck NUMBER;
BEGIN
   --UP_LOGON( 7369, 'SMITH', vlogonCheck);
   UP_LOGON( 7369, 'KENIK', vlogonCheck);
   --UP_LOGON( 9999, 'SMITH', vlogonCheck);
   
   IF vlogonCheck=0 THEN
       DBMS_OUTPUT.PUT_LINE('로그인 성공');
   ELSIF vlogonCheck=1 THEN
      DBMS_OUTPUT.PUT_LINE('ID는 존재하지만 pwd가 잘못되었다.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('ID는 존재 X.');
   END IF;
END;

-- 저장 함수( Stored Function )
-- 주민번호 -> 남자/여자 반환하는 uf_gender 저장 ..

-- 문제) 주민번호 -> 생년월일(yyyy.mm.dd) 반환하는 uf_birth()
-- 문제) 주민번호 -> 만나이 반환하는 uf_age()
SELECT num, name, ssn
, uf_birth(ssn)
, uf_age( ssn ) --
FROM insa;

CREATE OR REPLACE FUNCTION uf_age
(
  prrn VARCHAR2
)
RETURN NUMBER 
IS
   vischeck number(1);
   vt_year  number(4);
   vb_year  number(4);
   vage     number(3);
BEGIN
   vischeck :=  SIGN(  TRUNC( SYSDATE ) -  TO_DATE(  SUBSTR( prrn, 3,4), 'MMDD') );
   vt_year  := TO_CHAR( SYSDATE  , 'YYYY');
   vb_year  := CASE  
                 WHEN SUBSTR( prrn, 8, 1 ) IN (1,2,5,6) THEN '1900' + SUBSTR( prrn, 1,2)
                 WHEN SUBSTR( prrn, 8, 1 ) IN (3,4,7,8) THEN '2000' + SUBSTR( prrn, 1,2)
                 ELSE                                       '1800'  + SUBSTR( prrn, 1,2)
             END;
   vage     :=  CASE  VISCHECK
                WHEN -1 THEN  -- 생일 안지난것
                 vt_year - vb_year-1
                ELSE   -- 0, 1
                 vt_year - vb_year
            END  ;        
   RETURN vage;         
--EXCEPTION
END;

CREATE OR REPLACE FUNCTION uf_birth
(
   pssn insa.ssn%type
)
RETURN VARCHAR2
IS
    -- 1900,1800,2000
    vcentry NUMBER(2);
    vgender NUMBER(1);
    vbirth VARCHAR2(20);
    vssn6 VARCHAR2(6);
BEGIN

   vssn6 := SUBSTR( pssn, 0, 6 );
   vgender := SUBSTR( pssn, -7, 1);
   vcentry := CASE 
                   WHEN vgender IN (1,2,5,6) THEN 19
                   WHEN vgender IN ( 3,4,7,8) THEN 20
                   ELSE      18
              END;
              -- 19911223
  vbirth :=  TO_CHAR(  TO_DATE(  CONCAT( vcentry,  vssn6) ), 'YYYY.MM.DD' );

  RETURN vbirth;
--EXCEPTION
END;

--예) IN, OUT, IN OUT 입출력 파라미터 X

DECLARE 
   vphone VARCHAR2(9) := '8765-8653';
BEGIN
   up_phone( vphone );
   
   DBMS_OUTPUT.PUT_LINE( vphone );  -- '8765'
END;

CREATE OR REPLACE PROCEDURE up_phone
(
   pphone IN OUT VARCHAR2  -- 입 출력용 파라미터
)
IS
BEGIN
   pphone :=  SUBSTR( pphone, 0, 4);
--EXCEPTION
END;

-- [트리거] / 예외처리 / 패키지 --
PL/SQL 6가지 종류
1) 익명 프로시저
2) 저장 프로시저
3) 저장 함수
4) 트리거
5) 패키지
6) X

[ 트리거 ( trigger ) ]
1. 트리거 ? 방아쇠 -> 자동 -> 총알~
2. 어떤 작업 전(befor) 또는 후( after) 트리거에 정의한 로직을 실행하는 PL/SQL의 한 종류
3. 대상 테이블에 미리 트리거를 지정하면
어떤 이벤트(DML)가 발생할 때 자동으로 지정된 트리거가 작동하도록 한 객체를 트리거라 한다.
4. 예)

[트랜잭션처리 ]

        1)   입고테이블(대상테이블) + DML(insert)
           PK     상품      입고날짜    입고수량
           1     P001      22.10.18     10

           자동으로
        2)  재고테이블   재고수량   수정(update)
           1     P001    60

5. 트리거 예약어

  1) before  작업 전에 트리거가 자동 처리.
  2) after        후
  3) for each row : 행마다 처리 되는 트리거( 행 트리거 )
  4) referencing : 영향받는 행의 값 참조
  5) :old 참조 전 컬럼의 값
:new  참조 후 컬럼의 값

6. 트리거 선언 형식

CREATE OR REPLACE TRIGGER 트리거명
[ before 또는 after ]
트리거이벤트  ON 대상테이블
[for each row  [ when trigger 조건] ]
DECLARE
     -- 변수 선언
BEGIN
     -- 실행 블럭
EXCEPTION
    -- 예외 처리블럭
END;

7. 트리거 확인

SELECT *
FROM user_triggers;

8. 트리거 생성, 삭제, 활성화/비활성화.

9. 트리거 예제)
A 테이블 : insert, update, delete 이벤트가 발생을 하면
B 테이블 : 로그 기록을 자동으로 남기는 트리거 생성..

CREATE TABLE tbl_trigger1(
  id   number primary key
  , name varchar2(20)
);

CREATE TABLE tbl_trigger2(
  memo  varchar2(100)  -- 로그 기록
  , ilja date  default sysdate
);

AFTER 트리거 선언)

-> tbl_trigger1에 insert가 일어난 후 begin 실행

  CREATE OR REPLACE TRIGGER ut_ex01
  AFTER
  INSERT ON tbl_trigger1
  --FOR EACH ROW 행트리거
  --DECLARE
  BEGIN
     INSERT INTO tbl_trigger2 ( memo ) VALUES ( 'TBL_TRIGGER1 INSERT...' );     
     -- COMMIT/ROLLBACK X
  --EXCEPTION
  END;

INSERT INTO tbl_trigger1 VALUES ( 1 , '홍길동' );
INSERT INTO tbl_trigger1 VALUES ( 2 , '김길동' );
INSERT INTO tbl_trigger1 VALUES ( 3 , '최기수' );
INSERT INTO tbl_trigger1 VALUES ( 4 , '박기수' );

-- 트리거 + UPDATE

CREATE OR REPLACE TRIGGER ut_ex02
  AFTER
  UPDATE ON tbl_trigger1
  --FOR EACH ROW 행트리거
  --DECLARE
  BEGIN
     INSERT INTO tbl_trigger2 ( memo ) VALUES ( 'TBL_TRIGGER1 UPDATE...' );     
     -- COMMIT/ROLLBACK X
  --EXCEPTION
  END;

  UPDATE tbl_trigger1
  SET name = '관리자'
  WHERE id = 1;

합쳐서 트리거 생성 가능! (IF / ELSEIF)

CREATE OR REPLACE TRIGGER ut_ex01
  AFTER
  INSERT OR UPDATE OR DELETE ON tbl_trigger1
  --FOR EACH ROW 행트리거
  --DECLARE
  BEGIN
        
     -- COMMIT/ROLLBACK X
     
     IF  INSERTING THEN
        INSERT INTO tbl_trigger2 ( memo ) VALUES ( 'TBL_TRIGGER1 INSERT...' );  
     ELSIF UPDATING THEN      
         INSERT INTO tbl_trigger2 ( memo ) VALUES ( 'TBL_TRIGGER1 UPDATE...' );  
     ELSIF DELETING THEN      
         INSERT INTO tbl_trigger2 ( memo ) VALUES ( 'TBL_TRIGGER1 DELETE...' );       
     END IF;
     
  --EXCEPTION
  END;

--예제2) 만약에
-- tbl_trigger1 테이블에 I/U/D 작업은 근무시간에만 할 수 있도록 하겠다.
-- 토/일 X 근무시간은 주말(토/일)제외 하고 평일( 9:00~18:00 )

-- BEFORE 트리거로 선언--

CREATE OR REPLACE TRIGGER ut_ex03
BEFORE  -- AFTER
INSERT OR UPDATE OR DELETE ON tbl_trigger1
-- FOR EACH ROW 행트리거
-- DECLARE
BEGIN
   IF  TO_CHAR(SYSDATE, 'DY')  IN ('토','일')  
   OR   NOT (TO_CHAR(SYSDATE, 'HH24') BETWEEN 9 AND 12 )  THEN
       -- 강제로 에러 발생 -> I,U,D  DML 문도 취소.
       -- JAVA    throw 문 new Exception();
       -- RAISE_APPLICATION_ERROR(에러코드, 에러메시지);
       RAISE_APPLICATION_ERROR(-20000, '지금은 근무시간이 아니기 때문에 작업은 안됩니다.');
   END IF;
-- EXCEPTION
END;

오류 보고
--ORA-20000: 지금은 근무시간이 아니기 때문에 작업은 안됩니다.
--ORA-06512: at "SCOTT.UT_EX03", line 7
--ORA-04088: error during execution of trigger 'SCOTT.UT_EX03'

-- 예제3)

create table tbl_trg1
(
    hak varchar2(10) primary key
  , name varchar2(20)
  , kor number(3)
  , eng number(3)
  , mat number(3)
);

create table tbl_trg2
(
  hak varchar2(10) primary key
  , tot number(3)
  , avg number(5,2)
  , constraint fk_test2_hak foreign key(hak)   references tbl_trg1(hak)
);

-- 자동으로 tbl_trg2 테이블  1, tot, avg 처리... 저장. (트리거)
-- ORA-04082: NEW or OLD references not allowed in table level triggers
-- 테이블 레벨 트리거에서는 :NEW,   :OLD 사용할 수 없다.
-- 행 레벨 트리거 FOR EACH ROW는
-- UPDATE 10번부서 사원 3명 -> 트리거 1번(테이블레벨) /  3번(로우레벨)

CREATE OR REPLACE TRIGGER ut_trg1dml
AFTER
INSERT ON tbl_trg1
FOR EACH ROW    -- 행 레벨 트리거
DECLARE
  vtot NUMBER(3) ;
  vavg NUMBER(5,2) ;
BEGIN
   -- 1, 'hong', 90,78, 99 
   vtot := :NEW.kor + :NEW.eng + :NEW.mat;
   vavg := vtot / 3;
   INSERT INTO tbl_trg2 ( hak, tot, avg ) VALUES ( :NEW.hak , vtot , vavg );
-- EXCEPTION
END;

INSERT INTO tbl_trg1 ( hak, name, kor, eng, mat ) VALUES ( 1, 'hong', 90,78, 99 );

2) 1 hong 90 7 8 99 수정된기 전에 원래 값 :OLD
1 hong 87 67 100 새로 수정될 값 :NEW

UPDATE TBL_TRG1
SET kor = 87, eng = 67, mat = 100
WHERE hak = 1;

자동 tot/avg 수정 되어야함

CREATE OR REPLACE TRIGGER ut_trg1dml
AFTER
UPDATE OR INSERT ON tbl_trg1
FOR EACH ROW    -- 행 레벨 트리거
DECLARE
  vtot NUMBER(3) ;
  vavg NUMBER(5,2) ;
BEGIN
   -- 1   hong   87   67    100
   vtot := :NEW.kor + :NEW.eng + :NEW.mat;
   vavg := vtot / 3;
   
   IF  INSERTING THEN
           -- 1, 'hong', 90,78, 99            
           INSERT INTO tbl_trg2 ( hak, tot, avg ) VALUES ( :NEW.hak , vtot , vavg );
   ELSIF UPDATING THEN
       UPDATE tbl_trg2
       SET tot = vtot , avg = vavg
       WHERE hak = :OLD.hak;  -- :NEW.hak
   END IF;   
-- EXCEPTION
END;

문제3) tbl_trg1 테이블의 1번 학생의 정보를 삭제하면
자동 tbl_trg2 테이블의 1번 정보(레코드) 삭제....하는 트리거 생성, 테스트하세요..

  DELETE FROM   tbl_trg1
  WHERE hak = 1;

-- ORA-02292: integrity constraint (SCOTT.FK_TEST2_HAK) violated - child record found
-- tbl_trg2 테이블에서 참조

CREATE OR REPLACE TRIGGER ut_trg102
BEFORE
DELETE ON tbl_trg1
FOR EACH ROW    -- 행 레벨 트리거
BEGIN
     DELETE FROM tbl_trg2
     WHERE hak = :OLD.hak ;   -- :NEW.hak X
-- EXCEPTION
END;

--DELETE FROM dept
--WHERE deptno = 10;
--ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

-- 패키지(package )--
1. 관계되는 타입, 프로그램 객체, 서브프로그램(procedure, function)을 논리적으로 묶어 놓은 것.
2. 패키지는 specification( 명세, 사양 )과 body( 몸체 ) 부분으로 구성
--dbms_output . put_line()
--패키지명.함수명()

-- specification부분 --

CREATE OR REPLACE PACKAGE employee_pkg 
as 
   procedure print_ename(p_empno number); 
   procedure print_sal(p_empno number); 
end employee_pkg; 
-- Package EMPLOYEE_PKG이(가) 컴파일되었습니다.

-- body 부분

CREATE OR REPLACE PACKAGE BODY employee_pkg
as 
   
       procedure print_ename
       (
         p_empno number
       ) 
       is 
        l_ename emp.ename%type; 
      begin 
        select ename 
          into l_ename 
          from emp 
          where empno = p_empno; 
       dbms_output.put_line(l_ename); 
     exception 
       when NO_DATA_FOUND then 
         dbms_output.put_line('Invalid employee number'); 
     end print_ename; 
  
  procedure print_sal(p_empno number) is 
     l_sal emp.sal%type; 
   begin 
     select sal 
       into l_sal 
       from emp 
       where empno = p_empno; 
     dbms_output.put_line(l_sal); 
   exception 
     when NO_DATA_FOUND then 
       dbms_output.put_line('Invalid employee number'); 
   end print_sal; 
  
   end employee_pkg;

exec employee_pkg.print_ename(1234); 
exec employee_pkg.print_ename(7369); 
exec employee_pkg.print_sal(7369);

-- 오전) id 중복체크, 로그인 저장프로시저.....

1) specification 부분

CREATE OR REPLACE PACKAGE   logon_pkg
AS
    PROCEDURE up_idCheck
    (
       pempno IN emp.empno%TYPE  -- ID
       , pempnoCheck OUT NUMBER  --  0(사용가능)     1(사용불가능)
    );
    PROCEDURE up_logon
    (
       pempno IN emp.empno%TYPE    -- ID
       , pename IN emp.ename%TYPE  -- PWD
       , plogonCheck OUT NUMBER  --  0(로그인성공)     1(ID존재하지않으면)  -1(ID o, PWD X)
    );
    FUNCTION uf_age
    (
      prrn VARCHAR2
    )
    RETURN NUMBER ;
    
END logon_pkg;

2) body 부분

CREATE OR REPLACE PACKAGE BODY   logon_pkg
AS
    PROCEDURE up_idCheck
    (
       pempno IN emp.empno%TYPE  -- ID
       , pempnoCheck OUT NUMBER  --  0(사용가능)     1(사용불가능)
    )
    IS
    BEGIN
      SELECT COUNT(*) INTO pempnoCheck
      FROM emp
      WHERE empno = pempno;
    --EXCEPTION
    END up_idCheck;
    
    PROCEDURE up_logon
    (
       pempno IN emp.empno%TYPE    -- ID
       , pename IN emp.ename%TYPE  -- PWD
       , plogonCheck OUT NUMBER  --  0(로그인성공)     1(ID존재하지않으면)  -1(ID o, PWD X)
    )
    IS
      vename emp.ename%TYPE;
    BEGIN
      SELECT COUNT(*) INTO plogonCheck  -- 1   , 0
      FROM emp
      WHERE empno = pempno;
    
      IF plogonCheck = 1  THEN  -- ID 존재한다면 
        SELECT ename INTO vename
        FROM emp
        WHERE empno = pempno; 
        IF vename = pename THEN  -- PWD 일치한다면
          plogonCheck := 0;  -- 로그인 성공
        ELSE
          plogonCheck := -1;  -- 로그인 실패
        END IF;
      ELSE 
         plogonCheck := 1;  -- ID 존재하지 않는경우
      END IF;
    --EXCEPTION
    END up_logon;
    
    FUNCTION uf_age
    (
      prrn VARCHAR2
    )
    RETURN NUMBER 
    IS
       vischeck number(1);
       vt_year  number(4);
       vb_year  number(4);
       vage     number(3);
    BEGIN
       vischeck :=  SIGN(  TRUNC( SYSDATE ) -  TO_DATE(  SUBSTR( prrn, 3,4), 'MMDD') );
       vt_year  := TO_CHAR( SYSDATE  , 'YYYY');
       vb_year  := CASE  
                     WHEN SUBSTR( prrn, 8, 1 ) IN (1,2,5,6) THEN '1900' + SUBSTR( prrn, 1,2)
                     WHEN SUBSTR( prrn, 8, 1 ) IN (3,4,7,8) THEN '2000' + SUBSTR( prrn, 1,2)
                     ELSE                                       '1800'  + SUBSTR( prrn, 1,2)
                 END;
       vage     :=  CASE  VISCHECK
                    WHEN -1 THEN  -- 생일 안지난것
                     vt_year - vb_year-1
                    ELSE   -- 0, 1
                     vt_year - vb_year
                END  ;        
       RETURN vage;         
    --EXCEPTION
    END uf_age;
    
END logon_pkg;

SELECT num, name , ssn, logon_pkg.uf_age(ssn) age
FROM insa;

-- PL/SQL 작성 제출 (회사,실무) : VIEW, SP, SF, T, P--
-- 커서( CURSOR ) + 파라미터를 이용하는 방법 --

EXEC UP_SelDeptEmp( 20 ); -- 6명 + 커서 사용(명,암)

-- 1) 방법

CREATE OR REPLACE PROCEDURE UP_SelDeptEmp
(
   pdeptno IN dept.deptno%type
)
IS
    vdeptno emp.deptno%type;
    vename emp.ename%type;
    vsal emp.sal%type;    
     CURSOR c_emp IS     (
                             SELECT deptno, ename, sal
                             FROM emp 
                             WHERE deptno = pdeptno
                         );
BEGIN
     OPEN c_emp;     
     LOOP
        FETCH c_emp INTO vdeptno, vename, vsal;
       EXIT WHEN c_emp%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE( vdeptno || ', ' || vename || ', ' || vsal);
     END LOOP;
     
     CLOSE c_emp;
--EXCEPTION
END;

-- 2) 방법

CREATE OR REPLACE PROCEDURE UP_SelDeptEmp
(
   pdeptno IN dept.deptno%type
)
IS
    vdeptno emp.deptno%type;
    vename emp.ename%type;
    vsal emp.sal%type;    
     CURSOR c_emp( cpdeptno dept.deptno%type ) IS     (
                             SELECT deptno, ename, sal
                             FROM emp 
                             WHERE deptno = cpdeptno
                         );
BEGIN
     OPEN c_emp ( pdeptno );     
     LOOP
        FETCH c_emp INTO vdeptno, vename, vsal;
       EXIT WHEN c_emp%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE( vdeptno || ', ' || vename || ', ' || vsal);
     END LOOP;
     
     CLOSE c_emp;
--EXCEPTION
END;

-- 3) UP_출력하는 프로시저들은 지금까지 DBMS_OUPUT.PUT_LINE() 여러 레코드를 화면에  출력
--     자바 프로그램 연동 <-  커서(데이터 저장 객체) 자체를 출력용 매개변수 반환.
--        [ SYS_REFCURSOR, 오라클9i 이후~  ] 이걸로 기억 ***
--        REF CURSORS ( 오라클 9i 이전 )

CREATE OR REPLACE PROCEDURE  up_selInsa
(
   pcursor IN SYS_REFCURSOR
)
IS
   vname insa.name%type;
   vcity insa.city%type;
   vbasicpay insa.basicpay%type;
BEGIN
  LOOP
     FETCH pcursor INTO vname, vcity, vbasicpay;
     EXIT WHEN pcursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE( vname || ', ' || vcity  || ', ' || vbasicpay);
  END LOOP;
END;

ㄴ 여긴 OPEN이 없음

[저장 프로시저]

CREATE OR REPLACE PROCEDURE up_test_selInsa
IS
  vcursor SYS_REFCURSOR;  -- 커서 변수 선언
BEGIN   
   OPEN vcursor FOR SELECT name, city, basicpay FROM insa;   
   UP_SELINSA( vcursor );  -- LOOP   FETCH   
   CLOSE vcursor;   
-- EXCEPTION
END;

EXEC up_test_selInsa;

-- + 오라클 저장 프로시저 실행 후 커서 출력용 매개변수로 넘겨주면
-- 자바에서 커서를 받아서 사용하는 코딩. ( 기억 , 암기 )

CREATE OR REPLACE PROCEDURE up_ex05
(
    pcursor  OUT  SYS_REFCURSOR
)
IS
BEGIN
    OPEN pcursor FOR SELECT name, city, basicpay FROM insa;
    
    -- LOOP~ FETCH X
    -- CLOSE X
END;

-- PL/SQL 블럭( EXCEPTION 블럭 ) - 예외 처리 방법
1) 미리 정의된 에러 처리방법 ( 7가지 )

SELECT ename, sal  
FrOM emp
WHERE sal = 1250;

EXEC up_emplist(  sal );
EXEC up_emplist(  10000 );  -- 0  ORA-01403: no data found
EXEC up_emplist(  5000 );   -- 1
EXEC up_emplist(  1250 );   -- 2  ORA-01422: exact fetch returns more than requested number of rows

CREATE OR REPLACE PROCEDURE up_emplist
(
   psal emp.sal%type
)
IS 
   vename emp.ename%type;
BEGIN
   SELECT ename  INTO vename
   FROM emp
   WHERE sal = psal;
   
   DBMS_OUTPUT.PUT_LINE(  psal || ' ==> '  || vename );   
EXCEPTION  -- 예외처리 절
   WHEN no_data_found THEN
     RAISE_APPLICATION_ERROR(-20002, '> QUERY DATA NOT FOUND.');  -- JAVA 연동 + 예외처리.
   WHEN TOO_MANY_ROWS THEN
     RAISE_APPLICATION_ERROR(-20003, '> QUERY TOO MANY ROWS FOUND.');
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20004, '> QUERY OTHERS EXCEPTION FOUND.');
END;
-- Procedure UP_EMPLIST이(가) 컴파일되었습니다.

--2) 미리 정의 되지 않는 예외 처리 방법

DELETE FROM dept WHERE deptno >= 50;  
COMMIT;


SELECT * FROM dept;

INSERT INTO dept  VALUES ( 40, 'QC', 'SEOUL');
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

-- 미리 정의되지 않는 예 -02291
-- ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

INSERT INTO emp ( empno, ename, deptno ) VALUES ( 9999, 'kenik', 90 );

CREATE OR REPLACE PROCEDURE up_insEmp
(
   pempno  emp.empno%type
   , pename emp.ename%type
   , pdeptno emp.deptno%type
)
IS
    v_invalid_deptno EXCEPTION;
    PRAGMA EXCEPTION_INIT( v_invalid_deptno,-02291);
BEGIN
   INSERT INTO emp ( empno, ename, deptno ) VALUES ( pempno, pename, pdeptno );
   COMMIT;
EXCEPTION
   WHEN v_invalid_deptno THEN 
      RAISE_APPLICATION_ERROR(-20999, '> QUERY DEPTNO FK NOT FOUND.');
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20004, '> QUERY OTHERS EXCEPTION FOUND.');
END;

EXEC UP_INSEMP( 9999, 'KENIK', 90 );

-- ORA-20999: > QUERY DEPTNO FK NOT FOUND.

3) 사용자가 정의한 에러 처리방법

자바에서 throw 와 비슷

--자바 - class STUDNENT{
-- int kor; 0~100 국어점수 범위벗어낫다 예외 처리
--}
--class ScoreOutOFBoundException extends Exception{
--}
-- throw new ScoreOutOFBoundException();

SELECT COUNT(*) INTO vempcount
FROM emp
WHERE sal BETWEEN  (psal-100) AND (psal+100) ;

만약 400~600 사원 X COUNT(*) INTO vempcount == 0 강제로 사용자 정의한 예외 발생시키고
EXCEPTION
WHEN 예외 THEN
처리~

-- ORA-20011: > QUERY EMP COUNT = 0 .... <
EXEC UP_EXCEPTION02( 500 );
EXEC UP_EXCEPTION02( 1200 );

CREATE OR REPLACE PROCEDURE up_exception02
(
   psal IN emp.sal%type
)
IS
   vempcount NUMBER;
   
   ve_no_emp_returned EXCEPTION;  -- 예외 변수 선언
BEGIN
   SELECT COUNT(*) INTO vempcount
   FROM emp
   WHERE sal BETWEEN  (psal-100) AND (psal+100) ;
   
    IF  vempcount = 0 THEN
--      -- 강제 예외 발생         throw  new MyScoreException();
      RAISE ve_no_emp_returned;  -- 예외 강제 발생
    ELSE
      DBMS_OUTPUT.PUT_LINE('>처리 결과 : ' || vempcount );
     END IF;
EXCEPTION
   WHEN ve_no_emp_returned THEN
      RAISE_APPLICATION_ERROR(-20011, '> QUERY EMP COUNT = 0 .... <');
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20004, '> QUERY OTHERS EXCEPTION FOUND <');
END;

-- [ 동적 SQL ] --
JAVA 동적 배열

int [] m ; -- 배열크기 X
n = new int[n];

PL/SQL
1. 동적 SQL ? [컴파일 시]에 SQL문장이 확정이 되지 않은 경우 -> 실행될 때 SQL 문을 확정.
-> 프로시저가 컴파일될때 쿼리가 완성되는 것이 아니라 실행될 때 쿼리가 완성됨.

-- 검색 --

SELECT *
FROM board
WHERE 제목 LIKE '%컴퓨터%' OR 내용 LIKE '%컴퓨터%';;

SELECT *
FROM board
WHERE 작성자 LIKE '%컴퓨터%';

SELECT *
FROM board
WHERE 내용 LIKE '%컴퓨터%';

SELECT *
FROM board
WHERE 제목 LIKE '%컴퓨터%';

-- 다나와 검색 조건 (여러 조건을 체크해야할때 꼭 써야함!)

2. PL/SQL 동적 쿼리를 사용하는 2가지 방법

1) 원시 동적 쿼리 ( NDS : Native Dynamic SQL )
2) dbms_sql 패키지 사용 X

3. 동적 쿼리를 실행하는 방법
1) 형식

EXECUTE IMMEDIATE  동적쿼리문
            [ INTO 변수명,변수명... ]
            [ USING MODE(IN, OUT, IN OUT ) 파라미터..... ];

4. 동적 쿼리 예제
1) 예제1 - 익명프로시저

DECLARE
  vsql VARCHAR2(1000); -- 동적 쿼리 저장할 변수
  vdeptno emp.deptno%type;
  vempno emp.empno%type;
  vename emp.ename%type;
  vjob emp.job%type;
BEGIN

    -- ㄱ) 동적 쿼리 작성
    vsql := 'SELECT deptno, empno, ename, job ';
    vsql := vsql || ' FROM emp ';
    vsql := vsql || ' WHERE empno = 7369';
    
    -- ㄴ) 동적 쿼리 실행.
     EXECUTE IMMEDIATE  vsql
             INTO  vdeptno, vempno, vename, vjob;
     
     DBMS_OUTPUT.PUT_LINE( vdeptno || ', ' || vempno || ', ' || vename || ', ' || vjob )     ;
    

--EXCEPTION
END;

1) 예제2 - 저장 프로시저

CREATE OR REPLACE  PROCEDURE UP_NDSEmp
(
   pempno emp.empno%type
)
IS
  vsql VARCHAR2(1000);  
  vdeptno emp.deptno%type;
  vempno emp.empno%type;
  vename emp.ename%type;
  vjob emp.job%type;
BEGIN 
    vsql := 'SELECT deptno, empno, ename, job ';
    vsql := vsql || ' FROM emp ';
    vsql := vsql || ' WHErE empno = :pempno'; 
    
     EXECUTE IMMEDIATE  vsql
             INTO  vdeptno, vempno, vename, vjob
             USING pempno;
     
     DBMS_OUTPUT.PUT_LINE( vdeptno || ', ' || vempno || ', ' || vename || ', ' || vjob )     ;
     
--EXCEPTION
END;

EXEC UP_NDSEmp( 7369 );

     3) 예제3 - 저장 프로시저
  dept 테이블에 새로운 부서 정보를 저장하는 프로시저 생성 + 동적 쿼리 사용.
  deptno 10/20/30/40
  seq_dept  시퀀스   시작값50  10증가  X , 시퀀스 사용 안하고

  CREATE OR REPLACE PROCEDURE UP_INSDept
  (
     pdname  dept.dname%type
     , ploc  dept.loc%type
  )
  IS
     vsql VARCHAR2(1000);
     vdeptno dept.deptno%type;
  BEGIN
      SELECT MAX(deptno) + 10 INTO vdeptno
      FROM dept;
  
       vsql := 'INSERT INTO dept ';
       vsql := vsql ||  ' ( deptno, dname, loc ) ';
       vsql := vsql ||  '  VALUES ( :vdeptno, :pdname, :ploc ) ';
       
       EXECUTE IMMEDIATE vsql
                 -- INTO
                 USING  vdeptno, pdname, ploc;
                 
     -- COMMIT;                 
--  EXCEPTION
  END;

 EXEC UP_INSDEPT( 'QC', 'SEOUL');

--예) 사용자 원하는 형태의 게시판 여러개 생성.. ( 테이블 생성 DDL 문 ) 동적 쿼리

 DECLARE
   vsql VARCHAR2(1000);
   vtablename VARCHAR2(20);
 BEGIN
    vtablename := 'tbl_nds';
    
     vsql := 'CREATE TABLE ' || vtablename;
   --vsql := 'CREATE TABLE   :vtablename '; USING 사용할경우!
    vsql :=  vsql ||  ' ( ';
    vsql :=  vsql ||  '      id number primary key ';
    vsql :=  vsql ||  '      , name varchar2(20) ';
    vsql :=  vsql ||  ' ) ';
    
    DBMS_OUTPUT.PUT_LINE( vsql );
    
    EXECUTE IMMEDIATE vsql;
                --USING vtablename;
 END;

-- 마지막 동적 쿼리 예제 ( 암기 ) --
예 ) OPEN FOR 문 ?
동적 SQL의 실행 결과가 여러 개의 행을 반환하는 SELECT문 + 커서

CREATE OR REPLACE PROCEDURE up_nds02
(
   pdeptno emp.deptno%type
)
IS
   vsql varchar2(1000);
   vrow emp%ROWTYPE;
   vcur SYS_REFCURSOR;  -- 9i  REF CURSOR
BEGIN
   vsql := 'SELECT * ';
   vsql :=  vsql || ' FROM emp ';
   vsql :=  vsql || ' WHERE deptno = :pdeptno ';
   
   
--    EXECUTE IMMEDIATE 동적쿼리문  X
--        INTO
--        USING pdeptno;
      
      OPEN vcur FOR vsql  USING pdeptno;
       
      LOOP
         FETCH vcur INTO vrow;
         EXIT WHEN vcur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE( vrow.empno || ', ' || vrow.ename );
      END LOOP;
      
      CLOSE vcur;  
--EXCEPTION
END;

EXEC UP_NDS02( 30 );
EXEC UP_NDS02( 10 );

트리거 실습
-- 상품 테이블 작성

CREATE TABLE 상품 (
   상품코드        VARCHAR2(6) NOT NULL PRIMARY KEY
  ,상품명           VARCHAR2(30)  NOT NULL
  ,제조사        VARCHAR2(30)  NOT NULL
  ,소비자가격  NUMBER
  ,재고수량     NUMBER DEFAULT 0
);

-- 입고 테이블 작성

CREATE TABLE 입고 (
   입고번호      NUMBER PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_ibgo_no
                 REFERENCES 상품(상품코드)
  ,입고일자     DATE
  ,입고수량      NUMBER
  ,입고단가      NUMBER
);

-- 판매 테이블 작성

CREATE TABLE 판매 (
   판매번호      NUMBER  PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_pan_no
                 REFERENCES 상품(상품코드)
  ,판매일자      DATE
  ,판매수량      NUMBER
  ,판매단가      NUMBER
);

-- 상품 테이블에 자료 추가

INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('AAAAAA', '디카', '삼싱', 100000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('BBBBBB', '컴퓨터', '엘디', 1500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('CCCCCC', '모니터', '삼싱', 600000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('DDDDDD', '핸드폰', '다우', 500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
         ('EEEEEE', '프린터', '삼싱', 200000);
COMMIT;

------------------------------------------------------------
-- 3. 트리거 작성 (문제)
-- 1) 입고 테이블에 INSERT 트리거를 작성 한다.
-- [입고] 테이블에 자료가 추가 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

-- 입고 테이블에 데이터 입력

INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (1, 'AAAAAA', '2004-10-10', 5,   50000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (2, 'BBBBBB', '2004-10-10', 15, 700000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (3, 'AAAAAA', '2004-10-11', 15, 52000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (4, 'CCCCCC', '2004-10-14', 15,  250000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
              VALUES (5, 'BBBBBB', '2004-10-16', 25, 700000);
COMMIT;

  CREATE OR REPLACE TRIGGER ins
  AFTER
  INSERT ON 입고
  FOR EACH ROW
  BEGIN    
    UPDATE 상품
    SET 재고수량 = 재고수량 + :NEW.입고수량
    WHERE 상품코드 = :NEW.상품코드;
  END;

-- 2) 입고 테이블에 UPDATE 트리거를 작성 한다. (문제)
-- [입고] 테이블의 자료가 변경 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER ups
AFTER
UPDATE ON 입고
FOR EACH ROW
BEGIN
    UPDATE 상품
    SET 재고수량 = 재고수량 - :OLD.입고수량 + :NEW.입고수량
    WHERE 상품코드 = :NEW.상품코드;
END;

-- UPDATE 테스트

UPDATE 입고 SET 입고수량 = 30 WHERE 입고번호 = 5;
COMMIT;

-- 3) 입고 테이블에 DELETE 트리거를 작성 한다.
-- [입고] 테이블의 자료가 삭제되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER des
AFTER
DELETE ON 입고
FOR EACH ROW
BEGIN
    UPDATE 상품
    SET 재고수량 = 재고수량 - :OLD.입고수량
    WHERE 상품코드 = :OLD.상품코드;
END;

-- DELETE 테스트

DELETE FROM 입고 WHERE 입고번호 = 5;
COMMIT;

-- 입고 테이블의 재고 수량 수정 및 삭제는 상품 테이블의 재고 수량이 적거나 없으면 할 수 없으므로

-- UPDATE 및 DELETE 트리거를 BEFORE 트리거로 수정하여 상품 테이블의 재고 수량에 따라 수정 또는 삭제를 할수 없도록 수정한다.

-- 4) 판매 테이블에 INSERT 트리거를 작성한다.(BEFORE 트리거로 작성)
-- [판매] 테이블에 자료가 추가 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER in_판매
BEFORE
INSERT ON 판매
FOR EACH ROW
DECLARE
    v재고수량 NUMBER;
 BEGIN
    SELECT 재고수량 INTO v재고수량
    FROM 상품 
    WHERE 상품코드 = :NEW.상품코드;
    
    IF :NEW.판매수량 > v재고수량
        THEN RAISE_APPLICATION_ERROR(-20000, '재고부족! 판매 불가');
    ELSE        
       UPDATE 상품
       SET 재고수량 = 재고수량 - :NEW.판매수량
       WHERE 상품코드 = :NEW.상품코드;
    END IF;
END;

-- 판매 테이블에 데이터 입력

INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES
               (1, 'AAAAAA', '2004-11-10', 5, 1000000);
COMMIT;

INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES
               (1, 'AAAAAA', '2004-11-10', 50, 1000000);
COMMIT;

-- 5) 판매 테이블에 UPDATE 트리거를 작성한다.(BEFORE 트리거로 작성)
-- [판매] 테이블의 자료가 변경 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER up_판매
BEFORE
UPDATE ON 판매
FOR EACH ROW
DECLARE
    v재고수량 NUMBER;
BEGIN
    SELECT 재고수량 INTO v재고수량
    FROM 상품 
    WHERE 상품코드 = :NEW.상품코드;
    
    IF :NEW.판매수량 > v재고수량 + :OLD.판매수량
        THEN RAISE_APPLICATION_ERROR(-20001, '판매수정불가');
    ELSE        
        UPDATE 상품
        SET 재고수량 = 재고수량 + :OLD.판매수량 - :NEW.판매수량
        WHERE 상품코드 = :NEW.상품코드;
    END IF;
END;

-- UPDATE 테스트

UPDATE 판매 SET 판매수량 = 200 WHERE 판매번호 = 1;
UPDATE 판매 SET 판매수량 = 10 WHERE 판매번호 = 1;
COMMIT;

-- 6) 판매 테이블에 DELETE 트리거를 작성 한다.
-- [판매] 테이블에 자료가 삭제되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER de_판매
AFTER
DELETE ON 판매
FOR EACH ROW
BEGIN        
        UPDATE 상품
        SET 재고수량 = 재고수량 + :OLD.판매수량
        WHERE 상품코드 = :OLD.상품코드;
END;

-- DELETE 테스트

DELETE 판매 WHERE 판매번호 = 1;
COMMIT;

초기화


------------------------------------------------------------
-- 상품 테이블 작성
DROP TABLE 판매;
DROP TABLE 입고;
DROP TABLE 상품;


CREATE TABLE 상품 (
   상품코드        VARCHAR2(6) NOT NULL PRIMARY KEY
  ,상품명           VARCHAR2(30)  NOT NULL
  ,제조사        VARCHAR2(30)  NOT NULL
  ,소비자가격  NUMBER
  ,재고수량     NUMBER DEFAULT 0
);

-- 입고 테이블 작성
CREATE TABLE 입고 (
   입고번호      NUMBER PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_ibgo_no
                 REFERENCES 상품(상품코드)
  ,입고일자     DATE
  ,입고수량      NUMBER
  ,입고단가      NUMBER
);

-- 판매 테이블 작성
CREATE TABLE 판매 (
   판매번호      NUMBER  PRIMARY KEY
  ,상품코드      VARCHAR2(6) NOT NULL CONSTRAINT FK_pan_no
                 REFERENCES 상품(상품코드)
  ,판매일자      DATE
  ,판매수량      NUMBER
  ,판매단가      NUMBER
);

-- 상품 테이블에 자료 추가
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('AAAAAA', '디카', '삼싱', 100000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('BBBBBB', '컴퓨터', '엘디', 1500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('CCCCCC', '모니터', '삼싱', 600000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
        ('DDDDDD', '핸드폰', '다우', 500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
         ('EEEEEE', '프린터', '삼싱', 200000);
COMMIT;
SELECT * FROM 상품;

저작자표시

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

[Day14] Oracle 14 - PL/SQL(procedure, cursor, type, stored function, if/while) (0)	2022.10.19
[Day13] Oracle 13 - 정규화, 이상현상, 뷰, 시퀀스 (0)	2022.10.19
[Day12] Oracle 12 - CROSS JOIN, DB Modeling (0)	2022.10.13
[Day11] Oracle 11 - PURGE, 제약조건2, JOIN (0)	2022.10.12
[Day10] Oracle 10 - MODIFY, DROP COLUMN, Table copy, sub query(insert, update, delete), MERGE, Constraint (0)	2022.10.12

[Day15] Oracle 15 - trigger, package, cursor+paramete, 에러처리, 동적쿼리

[Day15] Oracle15 [10/18]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day15] Oracle15 [10/18]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바