프로시져(Stored Procedure)

-- 프로시져(Stored Procedure)

   PL/SQL에서 가장 대표적인 구조인 스토어드 프로시져는 개발자가 자주 실행해야 하는 업무 흐름을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행할 수 있다.

-- 프로시져 작성

CREATE [OR REPLACE] PROCEDURE 프로시져이름

[(

   매개변수 IN 자료형,           // 입력용, 인수에서는 자료형크기를 지정하면 안 된다.

   매개변수 OUT 자료형,        // 출력용

   매개변수 IN OUT 자료형  // 입/출력용

)]

IS 

   [변수의 선언]

BEGIN

   .............

   [EXCEPTION]

   .............

END;

 IN 파라미터 : 호출자에 의해 프로시져로 전달되는 파라미터이며, '읽기' 전용의 값으로 프로시져는 이 파라미터의 값을 변경할 수 없다.(디폴트 모드)

 OUT 파라미터 : 프로시져에서 값을 변경할 수 있고, '쓰기' 기능으로 프로시져가 정보를 호출자에게 돌려주는 기능이다. OUT 파라미터는 디폴트값을 지정할 수 없다.

 IN OUT 파라미터 : 프로시져가 읽고 쓰는 작업을 동시에 할 수 있는 파라미터이다.

  -- 주의

    1) 프로시져 안에서는 INSERT, UPDATE, DELETE문을 사용하는 경우 자동 커밋이 되지 않으므로 COMMIT; 구문을 추가 해야 한다.

    2) 프로시져에서 INSERT 문을 2개 이상 사용하여 데이터를 추가하는 경우 첫번째 INSERT문은 성공하고 두번째 INSERT 에서 추가를 실패하면 첫번째 INSERT는 자동으로 ROLLBACK 된다.

-- 테이블의 프로시져, 함수, 패키지 등 상호 참조되는 관계 확인

 SELECT * FROM user_dependencies;

-- 소스확인

SELECT text FROM user_source;

-- 프로시져 목록 확인

SELECT object_name FROM user_procedures;

-- 프로시져 실행

EXEC[UTE] 프로시져이름[(인수, 인수)];

-- 프로시져 삭제

DROP PROCEDURE 프로시져이름;

 * 테이블이 삭제된다고 연관된 프로시져가 삭제 되는것은 아니다.

   하지만 테이블이 삭제된 상태에서 연관된 프로시져를 실행 하면 오류가 발생 한다.

=============================================================================================================================

--테이블 작성

1.테이블명 : test1

CREATE TABLE test1(

 num NUMBER PRIMARY KEY

,name VARCHAR2(20)

,birth DATE

);

2.테이블명 : test2

CREATE TABLE test2(

 num NUMBER PRIMARY KEY

,score NUMBER(3) NOT NULL

,pan VARCHAR2(10)

,CONSTRAINT fk_test2_hakbeon FOREIGN KEY (num) REFERENCES test1 (num)

);

3. 시퀀스 작성 -- 시퀀스는 num에 데이터 추가할떄마다 번호를 자동으로 붙여주는 기능을 한다.

  CREATE SEQUENCE exam_seq

    INCREMENT BY 1 

    START WITH 1 

    NOMAXVALUE

    NOCYCLE

    NOCACHE;

    

    COMMIT;

    SELECT * FROM tab;

    SELECT * FROM user_sequences;

    

    

  -- 데이터를 추가하는 프로시져 작성

  CREATE OR REPLACE procedure  insertEaxm

  ( -- 여기에 프로시져 실행문으로 입력받을 매개변수 입력

    pname IN test1.name%TYPE  -- %TYPE => test1테이블의 name컬럼의 타입을 그대로 가져오겠다는 단어

    ,pbirth IN test1.birth%TYPE

    ,pscore IN NUMBER --인수는 컬럼폭을 지정할수없다.

    )

IS --여기에는 BEGIN에 사용될 변수 입력

  vpan VARCHAR2(10);

  vnum NUMBER;

BEGIN --실행문 입력

  IF pscore >= 80 THEN

    vpan:='우수';

  ELSIF pscore >= 60 THEN

    vpan:='보통';

  ELSE

    vpan:='노력';

  END IF;

  

  SELECT exam_seq.NEXTVAL INTO vnum FROM dual;

  

  INSERT INTO test1(num, name, birth)

    VALUES(vnum,pname,pbirth);

  INSERT INTO test2(num,score,pan)

    VALUES(vnum,pscore,vpan);

    

  COMMIT;

END;

/

프로시져를 실행

EXEC insertEaxm('홍길동','2000-10-10',90); -- 프로시져의 매개변수 선언 순서대로 입력

EXEC insertEaxm('이이이','2000-09-10',70);

EXEC insertEaxm('후후후','2000-08-10',60);

EXEC insertEaxm('미미미','2000-07-10',70);

SELECT * FROM test1;

SELECT * FROM test2;

ROLLBACK;

-------------------------------------------------------------------------------------

-- 수정하는 프로시져 작성

-- 프로시져명 : updateExam

  실행예 : EXEC updateExam(1,'홍길동','2000-10-10',75);

  1번의 자료중 exam1과 exam2테이블의 정보를 수정.

  단 기본키는 수정하지 않는다.

  

   CREATE OR REPLACE procedure updateExam

  (

    pnum IN test1.num%TYPE

    ,pname IN test1.name%TYPE

    ,pbirth IN test1.birth%TYPE

    ,pscore IN NUMBER 

    )

IS

  vpan VARCHAR2(10);

BEGIN

  IF pscore >= 80 THEN

    vpan:='우수';

  ELSIF pscore >= 60 THEN

    vpan:='보통';

  ELSE

    vpan:='노력';

  END IF;

  

  UPDATE test1 SET name=pname ,birth=pbirth WHERE num=pnum; --입력받은 pname,pbirth,pnum의 값을 name,birth,num에 대입하여 수정하라

  UPDATE test2 SET  score=pscore ,pan=vpan WHERE num=pname;

  

  COMMIT;

END;

/

-- updateExam 프로시져 실행

EXEC updateExam(1,'홍길동','2000-10-10',75);

--=> 컬럼의 순서는 프로시져에서 생성한 컬럼 순서대로 써준다.

------------------------------------------------------------------------------------

-- 삭제하는 프로시져 작성

-- 프로시져명 : deleteExam

  실행예 : EXEC deleteEXAM(1);

  num이 1인 exam1과 exam2 테이블 자료 삭제

  

CREATE OR REPLACE procedure deleteExam

  (

    pnum IN test1.num%TYPE

  )

IS

BEGIN

DELETE FROM test2 WHERE num=pnum;

DELETE FROM test1 WHERE num=pnum; 

-- FOREIGN KEY 때문에 자식을 먼저 지우고 부모를 나중에 지워야한다.

COMMIT;

END;

/

-- deleteExam 프로시져 실행

EXEC deleteExam (1); --=> num 1의 값을 지운다. --=> 컬럼의 순서는 프로시져에서 생성한 컬럼 순서대로 써주지만 하나밖에 없으므로 신경안씀..

SELECT * FROM test1;

--프로시져 목록 확인

SELECT * FROM user_procedures;

--소스확인

SELECT * FROM user_source;

--상호참조관계 확인

SELECT * FROM user_dependencies;

--인지가 없는 프로시져 작성

CREATE OR REPLACE PROCEDURE selectAllExam

      --여기에 인자가 없음

IS

BEGIN

  FOR rec IN (SELECT a.num, name, birth, score, pan

    FROM test1 a JOIN test2 b ON a.num=b.num) LOOP

  DBMS_OUTPUT.PUT(rec.num || '   '); -- DBMS_OUTPUT.PUT => 자바의 System.out.print와 같다.

  DBMS_OUTPUT.PUT(rec.name || '   ');

  DBMS_OUTPUT.PUT(rec.birth || '   ');

  DBMS_OUTPUT.PUT(rec.score || '   ');

  DBMS_OUTPUT.PUT(rec.pan);

  DBMS_OUTPUT.NEW_LINE();

END LOOP;

END;

/

EXEC selectAllExam;

SELECT * FROM test1;