PL/SQL의 stored procedure

http://blog.naver.com/nikeruga?Redirect=Log&logNo=60127805956

 

 

 

 

PL/SQL의 stored procedure

---

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

【형식】

생성	CREATE [OR REPLACE] PROCEDURE [프로시저이름] (argument1 [mode] data_type1, argument2 [mode] data_type2, ............... IS [AS] BEGIN ...... EXCEPTION ...... END;	프로시저이름 데이터베이스내에 저장될 프로시저 이름 argument 운영체제에서 프로시저 내로 어떤 값을 전달할 때의 변수 mode IN, OUT, IN OUT data_type argument변수의 데이터 타입 begin ... end 실행하려는 처리절차
삭제	DROP PROCEDURE [프로시져이름];

---

개발절차

1) 운영체제 상에서 편집기를 통해 stored procedure를 작성한다.(파일 확장자:.sql)
2) SQL*Plus상에서 이미 생성한 .sql 스크립트를 실행하여 stored procedure를 생성한다.(예: SQL> start **.sql처럼)
3) create procedure가 실행되면서 작성된 소스코드의 의한 프로시져가 생성된다.

또한 컴파일러에 의해 pcode가 생성되고 데이터베이스 내에 저장된다.
4) SQL*Plus상에서 생성된 프로시저를 호출하여 실행할 때는 EXECUTE 명령을 사용한다.(예: SQL> EXECUTE aa)

또한 응용 프로그램에서 실행할 때는 프로시저명을 정의만 하면 된다.

---

PL/SQL에서 SQL문의 사용방법

select 문	SELECT [column-1], ... , [column-n] INTO {variable-1, ... , variable-n} FROM table_name WHERE [조건절];	• PL/SQL 블럭 내에서 어떤 조건을 가진 SQL 문을 실행할 수 있다. • PL/SQL 내의 SELECT 문에는 반드시 INTO절이 정의되어야 한다 • 만약 SELECT 문에 의한 반환되는 결과가 여러 개의 행을 반환하는 경우에는 에러가 발생한다.  • 그러나, CURSOR 문을 사용하면 여러 개의 행을 처리할 수 도 있다.
insert 문	INSERT INTO table_name VALUES ([variable-1, ... , variable-n ]);	• PL/SQL 불럭 내에서의 DML 문은 일반적인 DML 문법과 동일하게 사용할 수 있다.
update 문	UPDATE table_name SET [column-1] = value] WHERE [조건절];
delete 문	DELETE INTO table_name WHERE [조건절];

---

실행방법

EXECUTE에 의한	SQL> EXECUTE a_dept (v_name, '서울');	EXECUTE 명령에 의해서 실행할 수 있음 자신의 스키마에 생성된 PL/SQL만 실행 할 수 있음 ALTER PROCEDURE 권한이 주어져야 함 만약 타인이 생성한 PL/SQL을 실행하려면 ALTER ANY PRECEDURE권한이 있어야 함
anonymous procedure에서 호출에 의한 실행	SQL> declare ( v_name IN varchar2) begin a_dept(v_name, '서울'); end;	begin ... end절에 호출하려는 storedprocedure이름을 정의하면 됨
하나의 stored procedure에서 호출에 의한 실행	SQL> create or replace procedure a_emp ( v_loc IN varchar2) begin a_dept( '총무과' v_loc); end;	begin ... end절에 호출하려는 storedprocedure이름을 정의하면 됨

【예제】

stored_procedure 예제

 

stored procedure 예제

---

stored procedure의 설명

【예제】
1단계 : 저장할 테이블 생성

$ sqlplus scott/tiger

SQL> CREATE TABLE log_table(userid VARCHAR2(10), log_date DATE);

2단계 : 실행 파일 생성

$ vi log_execution.sql

CREATE OR REPLACE PROCEDURE log_execution IS BEGIN INSERT INTO log_table(userid, log_date) VALUES (user, sysdate); END log_execution; /

3단계 : 실행 파일 실행

SQL> start log_execution.sql

Procedure created.

SQL> SHOW errors

no errors.

SQL> EXECUTE log_execution

PL/SQL procedure successfully completed.

SQL> SELECT * FROM log_table;

      USERID     LOG_DATE
      ---------- ------------
      JIJOE      13-JAN-09

 SQL>

---

예제를 위한 테이블 생성

SQL> create table emp(empno number(4) not null, ename varchar2(10));

테이블이 생성되었습니다.

SQL> insert into emp values(7654,'Corea');

1 개의 행이 만들어졌습니다.

SQL> insert into emp values(7902,'jijoe');

1 개의 행이 만들어졌습니다.

SQL>

프로시저에서 다른 프로시저 부름
$ vi aa.sql
create or replace procedure del_emp
(v_emp_no IN emp.empno%type)
is
begin
 log_execution;
 delete from emp where empno=v_emp_no;
end fire_emp;
/

스크립트를 실행하여 stored procedure를 생성
$ sqlplus jijoe/joe_password
SQL> start aa.sql

프로시저가 생성되었습니다.

SQL> select * from emp where empno=7654;

     EMPNO ENAME
---------- ----------
      7654 Corea

SQL> execute del_emp(7654);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from emp;

     EMPNO ENAME
---------- ----------
      7902 jijoe

SQL>

【예제】
SQL> set serveroutput on
SQL>  create or replace procedure joe_proc as 
  2  begin 
  3    dbms_output.put_line('Beautiful Korea'); 
  4  end joe_proc; 
  5  / 
  
Procedure created. 
  
SQL> set serveroutput on      ☜ 패키지에서 출력하도록 알림 
SQL> begin                    ☜ PL/SQL에서 프로시저를 호출하는 경우 
  2   joe_proc; 
  3  end; 
  4  / 
 
Beautiful Korea 
  
SQL> execute joe_proc;        ☜ SQL*Plus 명령에서 실행하는 경우 

Beautiful Korea 
  
SQL> select object_name from user_procedures;

 

 

 

 

 

---

매개변수의 종류	stored procedure 내에서 정의한 IN, OUT, INOUT 매개변수
매개변수의 전달 방법	프로시져 내에 정의된 매개변수의 순서에 맞게 값을 전달하는 방법
PL/SQL 변수의 종류	프로시져 내에 정의된 변수의 종류와 설명

---

stored procedure와 stored function의 차이

프로시져	함수
EXECUTE 명령에 의해 호출되고, 실행되며, 때로는 다른 프로시저나 함수 내에서 호출되어 실행	EXECUTE 명령과 다른 프로시져, 함수에 의해 호출되거나, 사용자의 SQL 문에 의해 호출할 수 있음
반환되는 데이터형이 없음	RETURN 명령에 의해 반환되는 한개의 데이터형이 존재
OUT 매개변수를 통해 함수처럼 사용하여 여러 개의 처리 결과를 반환	RETURN 명령에 의해 하나의 값만 반환

user_procedures

 

 

 

USER_procedures

---

사용자가 생성한 프로시저

 
【예제】
SQL> desc user_procedures;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 PROCEDURE_NAME                                     VARCHAR2(30)
 AGGREGATE                                          VARCHAR2(3)
 PIPELINED                                          VARCHAR2(3)
 IMPLTYPEOWNER                                      VARCHAR2(30)
 IMPLTYPENAME                                       VARCHAR2(30)
 PARALLEL                                           VARCHAR2(3)
 INTERFACE                                          VARCHAR2(3)
 DETERMINISTIC                                      VARCHAR2(3)
 AUTHID                                             VARCHAR2(12)

SQL> select count(*) from user_procedures;

  COUNT(*)
----------
         1

SQL> select object_name, procedure_name from user_procedures;

OBJECT_NAME                    PROCEDURE_NAME
------------------------------ ------------------------------
LOG_EXECUTION

SQL> select count(*) from all_procedures;

  COUNT(*)
----------
      8331

SQL>

[출처] PL/SQL의 stored procedure |작성자 세상바다

 

 

 

 

 

 

---

http://blog.naver.com/dawn0611?Redirect=Log&logNo=100005293500

 

1. EXECUTE IMMEDIATE를 이용한 Dynamic SQL  


Oracle 8i에서의 Dynamic SQL은 두가지 방법이 있습니다.

첫번째 방법은  "EXECUTE IMMEDIATE" 절을 사용하여 embedded dynamic sql을 구현하는 
방법이고,

두번째 방법은 선언되어지는 것 대신에 실행시에 문장을 보내는 ref cursor의 확장된 개념으로
query를 위해 사용되어지는 방법 입니다.


여기서는 EXECUTE IMMEDIATE를 이용한 Dynamic SQL에 대해서 알아보겠습니다.

[Syntax]

        EXECUTE IMMEDIATE dynamic_sql_string
            [INTO {define_var1 [, define_var2] ... | plsql_record }]
            [USING [IN | OUT | IN OUT] bind_arg1 [,
                        [IN | OUT | IN OUT] bind_arg2] ...]; 

2. 간단하게 테이블을 생성하는 예제 입니다    첫번째 예제는 간단하게 테이블을 생성하는 예제 입니다.    ========================================================== CREATE OR REPLACE PROCEDURE dynamic_sql_01 IS     str varchar2(200); BEGIN     str := 'CREATE TABLE  total (total number)';     EXECUTE IMMEDIATE str; END; ========================================================== 프로시저가 생성되었습니다.   -- 프로시저를 실행해서 테이블을 생성 합니다.  SQL> EXEC dynamic_sql_01; PL/SQL 처리가 정상적으로 완료되었습니다.     -- 생성된 테이블을 확인해 봅니다.  SQL> DESC total;  이름                                      널?      유형  ----------------------------------------- -------- -----------  TOTAL                                              NUMBER * 프로시저 생성시 "ORA-01031: 권한이 불충분합니다" 에러가 발생하면   system유저로 접속을 해서 EXECUTE IMMEDIATE를 실행하는 유저에게    CREATE ANY TABLE 권한을 부여 합니다.    SQL> CONN system/manager   SQL> GRANT create any table TO scott;

3. 테이블 생성 후 INSERT 예제 두번째 예제는 TABLE_ROWS라는 테이블을 생성하고, 다이나믹 하게 테이블명을 입력하면  테이블명과 테이블에 등록된 데이터수를 TABLE_ROWS에 INSERT하고 출력하는 예제 입니다.  ===============================================================  CREATE OR REPLACE PROCEDURE dynamic_sql_02  (v_table_name IN VARCHAR2) IS         v_str VARCHAR2(200);     v_cnt NUMBER;     v_temp VARCHAR2(50);          CURSOR cur_exists IS     SELECT TABLE_NAME     FROM USER_TABLES     WHERE table_name = 'TABLE_ROWS';      BEGIN          OPEN cur_exists     FETCH cur_exists INTO v_temp;         -- 테이블이 존재하면 테이블을 삭제 합니다.      IF  cur_exists%FOUND THEN              v_str := 'DROP TABLE  table_rows';         EXECUTE IMMEDIATE v_str;     END IF;         -- 테이블 생성     v_str := 'CREATE  TABLE  table_rows (total number, table_name varchar2(50))';     EXECUTE IMMEDIATE v_str;        -- 데이터 카운트 조회     v_str := 'SELECT COUNT(*) cnt FROM '||v_table_name ;     EXECUTE IMMEDIATE v_str INTO v_cnt ;     -- 데이터 insert     v_str := 'INSERT INTO table_rows  VALUES ('||v_cnt||', :A1 )';        EXECUTE IMMEDIATE v_str USING v_table_name;     DBMS_OUTPUT.PUT_LINE(' 테이블 명 : '||v_table_name||'  데이터 수 : '||v_cnt);         CLOSE cur_exists;      END; / ===============================================================  프로시저가 생성되었습니다.   SQL> SET SERVEROUTPUT ON: -- emp 테이블명과 테이블의 데이터카운트를 INSERT합니다.  SQL> EXEC dynamic_sql_02('emp'); 테이블 명 : emp  데이터 수 : 14 PL/SQL 처리가 정상적으로 완료되었습니다. -- 정상적으로 처리되었는지 확인해 봅니다. SQL> SELECT * FROM table_rows;        TOTAL TABLE_NAME ---------- --------------         14 emp

[출처] EXECUTE IMMEDIATE를 이용한 Dynamic SQL |작성자 볼펜과 자