150325 서브프로그램

◎ 서브 프로그램 

PL/SQL로 만든 또다른 프로그램, 한번 만들어 놓고 저장해 놓은 것 

 ↔ 저장을 안해 놓으면 익명 블럭 

◎ 종류

 ① 프로시저

 ② 함수

 ③ 패키지

 ④ 트리거 

□ 프로시저 

  어떠한 작업을 하는 프로그램  

  별도의 컴파일 없이 생성된 P_CODE를 실행

  SP : stored procedure 

※ 변수의 위치에 따라서 PL/SQL이 다르게 인식함 

※ 변수 종류

 - 사용자에게 입력 받는 변수 : CREATE 와 IS 사이에 있어야함  

 - 테이블에서 가져와서 저장하는 변수 : IS 와 BEGIN  사이에 있어야함 

   변수 MODE

 - IN :  외부에서 PL/SQL 로 인자값을 념겨줌 (DEAFAULT)

  - OUT : PL/SQL 의 변수 값을 외부로 인자값을 넘겨줌(다른프로시져도 가능)

절차

① CREATE 를 통해서 프로시저를 생성한다 

② 받을 인자값, 밖으로 보낼 인자값을 선언한다 (IS , CREATE 사이에 만듬)

    ※ 이때 프로시저를 사용할때 와 순서가 같아야 함 

   CREATE 프로지서 (인자1, 인자2 , 인자3)=프로시저(인자1, 인자2, 인자3);

③ 필요 하면 테이블에서 가져올 변수를 담을 곳을 만들어줌 (IS  BEGIN 사이)

④ PL/SQL 블럭을 만듬 

-- 프로시저 사용시--

⑤  프로시저 사용시 EXEC ②; 같이 해서 사용해 주어야 함 

CREATE [OR REPLACE] PROCEDURE procedure_name

[( parameter1 [mode1] datatype1,

parameter2 [mode2] datatype2,

… )]

IS | AS

PL/SQL Block ;

CREATE OR REPLACE PROCEDURE ename_sal

 -- 외부에서 입력되는 데이터 CREATE ,IS 사이 (IN 생략)

( vempno emp.empno%TYPE )

IS

  -- 테이블에 서 가져오는 데이터 IS, BEGIN 사이 (IN 생략)

 vename emp.ename%TYPE ;

 vsal emp.sal%TYPE ;

 BEGIN

 -- 1건의 데이터만 일때 사용 하기 

 SELECT ename , sal INTO vename , vsal

 FROM emp

 WHERE empno=vempno;

  

 DBMS_OUTPUT.PUT_LINE ('Name is '||vename);

 DBMS_OUTPUT.PUT_LINE ('Sal is '||vsal );

 END;

 /

실행문장

exec ename_sal(&in);

※ 프로시저를 실행할때() 안에 지정한 변수를 순서대로 전부 적어주어야함 

-- 프로시저 만들기

CREATE OR REPLACE PROCEDURE info_emp

-- ☆ 아래의 실행시에 순서와 같음  info_emp(7782,v_sal2,v_ename2)

 (  v_empno IN emp.empno%TYPE ,

  -- 이름값을 저장할 변수임

   v_sal OUT emp.sal%TYPE,

 -- 급여를 저장할 변수임 

 v_ename OUT emp.ename%TYPE 

  

  ) 

 IS

 BEGIN

 -- 테이블 데이터를 빼와서 외부로 나갈수 있는 변수에 저장 

 SELECT ename , sal INTO v_ename , v_sal

 FROM emp

 WHERE empno = v_empno ;

 END ;

 /

--실행블록 생성

DECLARE

 -- 인자값을 받을 것을 설정 

 v_ename2 emp.ename%TYPE ;

 v_sal2 emp.sal%TYPE ;

 BEGIN

-- ☆ 위에 프로시저의 순서와 같음

-- 프로시저에서 반환한 값을 다시 받음 

  info_emp(7782,v_sal2,v_ename2) ;

 DBMS_OUTPUT.PUT_LINE (v_sal2||' ''s salary is '||v_ename2);

 END ;

 /

□ 함수 

  프로시져와 거이 비슷 하지만 return 값이 있음 

  프로시저도 out을 사용해서 return 값이 있음 

  2개의 칼럼을 리턴하는 함수는 없음

 절차

① 함수를 선언 하고  들어올 인자값 을 설정한다, (IS CREATE 사이에 만듬)

②  RETURN TYPE를 설정한다 . 

③  테이블에서 뽑아온 데이터를 저장하는 변수를 생성 (IS BEAGIN 사이에 만듬)

④  내부 PL/SQL을 만들고

⑤ 연산이 종료 되면 RETURN을 하는데 IS BEAGIN  사이에 변수를 해줌 

CREATE [OR REPLACE] FUNCTION function_name

[( parameter1 [mode1] datatype1,

parameter2 [mode2] datatype2,

… )]

RETURN datatype

IS | AS

PL/SQL Block ;

CREATE OR REPLACE FUNCTION max_sal

 -- 외부로 부터 인자값을 받음 

 (v_deptno emp.deptno%TYPE )

 -- 반환할 데이터 타입형이 무엇인지 정해줌 

 RETURN NUMBER

 

 IS

-- 데이터를 뽑아온 후저장할 변수를 선언해줌  

 max_sal emp.sal%TYPE ;

 

 BEGIN

 SELECT max(sal) INTO max_sal

 FROM emp

 WHERE deptno=v_deptno;

-- 이 부분의 데이터 형이 같아야 함

 RETURN max_sal ; 

 END;

 /

-- 그냥 함수 처럼 사용하면 됨 -

SELECT deptno , max_sal(deptno)

FROM emp

GROUP BY deptno

ORDER BY 1 ;

-  함수 내에 서브쿼리 사용하기 

CREATE OR REPLACE FUNCTION dname

-- 인자값 데이터

 (v_profno IN professor.profno%TYPE )

 RETURN VARCHAR2

 IS

 -- 테이블에서 가져올 이름 

 v_dname department.dname%TYPE ;

 BEGIN

 

 -- 첫번째 쿼리를 수행해서 이름을 가져온다음 

 SELECT dname INTO v_dname

 FROM department

 -- 1 쿼리에서 교수번호에 해당하는 deptno 를 가져와서 

 -- 서브쿼리에 넣어서 학과테이블에서 조회를 한 값을 넣음 

 WHERE deptno = ( SELECT deptno

           FROM professor

           WHERE profno=v_profno);

 

 RETURN v_dname ;

 END ;

 /

-조회 하기

SELECT text

FROM user_source

 WHERE type='FUNCTION'

-- 함수명

 AND name='MAX_SAL' ;

□ 패키지

  프로시져나 함수를 모듈 별로 모아둔 곳 

   선언부(Spec) + 몸체부(Body) 로 구성됨  

 - 선언부

  몸체부에서 프로시저에서 사용하는 파라미터를 미리 다 선언을 해줌 

CREATE [OR REPLACE] PACKAGE PACKAGE_name

IS | AS

Public type and item declarations

Subprogram specifications

END PACKAGE_name ;

CREATE OR REPLACE PACKAGE member_mg

IS

 

 --성별을 찾는 프로시저

 PROCEDURE find_sex 

 (v_name member.name%TYPE ) ;

 

 --id를 찾는 프로시저

 PROCEDURE find_id 

 ( v_name member.name%TYPE ,

   v_no member.jumin%TYPE ) ;

 

 -- 비밀번호를 찾는 프로시저 

 PROCEDURE find_pwd

 (v_id IN member.id%TYPE ,

  v_an IN member.an_key_dap%TYPE ) ;

 

 END member_mg ;

 /

◎  트리거

- 어떠한 이벤트가 발생했을때 미리 만들어진 이벤트를 실행 

  ex) 테이블에 상품이 입고되면 자동으로 재고테이블을 증가하게 만듬 

  TRIGGER 생성시 정의한 특정 사건에 의해서 묵시적인 자동 실행 

   ※ 잘못생성하면 종속관계가 심해짐 

◇ 종류

- 단순 DML 트리거 

  트리거의 실행 위치에 따라서 구분

  ① BEFORE TRIGGER(多)    

      테이블에서 작업을 하기 전에 트리거를 먼저수행 

 ex)데이터를 삭제 하기 전에 백업하기 등 

   ② AFTER TRIGER(多)

        테이블에서 DML 작업을 수행후에 TRIGER 실행 

   ③ INSTEAD OF TRIGGER (少)

   트리거 영향에 따른 구분 

     ① 문장 TRIGGER : 테이블 자체를 대상으로 실행하는 경우

  

     ② 행 TRIGGER : 특정 record 값을 대상으로 사용 

          사용시: FOR EACH ROW 

- 기타 DML 이아닌 TRIGGER (책참조)

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [ OR event2 OR event3 … ]

ON {table_name|view_name | SCHEMA | DATABASE}

[REFERENCING OLD AS old | NEW AS new]

[FOR EACH ROW [WHEN ( condition ) ] ]

TRIGGER_BODY

예제전 명령 실행하기 

GRANT create trigger TO scott;

CREATE OR REPLACE TRIGGER tri_order

  /* 프로세스

   데이터가 입력되기 전에 시간을

   체크하는것이 실행이 되야한다.  

  */

  BEFORE INSERT ON tab_order

  BEGIN

  IF (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN   '13:33' AND

  '13:50') THEN

  RAISE_APPLICATION_ERROR(-20100,'Time Error!!');

 END IF ;

조회하기

SELECT trigger_name , table_name , status

FROM user_triggers;