꿈꾸는 개발자, DBA 커뮤니티 구루비

PL/SQL ?
왜 PL/SQL 인가?
PL/SQL 과 Java 의 비교
PL/SQL은 이식 및 재사용이 가능하다
PL/SQL 과 기타 언어 의 비교
데모

PL/SQL ?

PL/SQL Demo


create or replace procedure process_data ( p_inputs in varchar2 )
as 
begin
  for x in ( select * from emp where ename like p_inputs )
  loop
    process ( x );
  end loop
end;

왜 PL/SQL 인가?

오라클의 애플리케이션 슈트, 워크플로 엔진(http://plflow.sourceforge.net/), 데이터베이스의 관리용 인터페이스는 PL/SQL로 작성 되었다.
데이터 처리가 목적이라면, Java 나 C 같은 언어에 비해서 PL/SQL은 속도와 생산성 측면에서 가장 탁월하다.

PL/SQL 과 Java 의 비교

	PL/SQL	Java
데이터 유형	SQL 데이터 유형과 같음(변환 불필요)	SQL 데이터 유형과 다름(변환 필요)
SQL 과 결합성	쉽게 혼합됨, 묵시적/암묵적 기능 제공	SQL 과 무관한 절차형 API(Java/JDBC)
데이터베이스(오브젝트)의 수정	변경 불필요	변경 필요
커서 캐싱	암시적 지원	수작업 필요(PreparedStatement)

PL/SQL은 이식 및 재사용이 가능하다

Java 와 C 도 재사용성이 우수 하지만, 상호간의 호출에 있어서는, 어떤 방식이나 형태로든 데이터베이스에 연결할 수 있는 것은 PL/SQL 을 호출 할 수 있기 때문에 더욱 우수 하다.
다양한 언어로부터 호출되는 데이터 중심의 루틴은 PL/SQL 이 생산적 이며 안전하다.
PL/SQL 은 데이터베이스와의 대화에 보편적으로 사용되는 언어이다. (데이터베이스를 구성, 조정, 사용하는 대다수의 API 가 PL/SQL 기반이다.)
다른 언어로 코드를 작성할 때 생길수 있는 오류가 PL/SQL 에서도 나타날 수 있지만, 어렵다.

PL/SQL 과 기타 언어 의 비교

	PL/SQL	기타 언어
바인드 변수	정적 SQL - 바인드 변수 사용 동적 SQL - 바인드 변수 선택적 ^데모#1	바인드 변수 생략이 쉽다
한 번의 파스와 여러 번의 실행	암시적 커서 캐싱(동적 SQL도 가능)	?
SELECT *	일부 SELECT * 사용가능 ^데모#2	SELECT * 사용불가 (컬럼 추가, 삭제 변경등)
데이터베이스 스키마의 변경	영향 없음	변경 필요
의존성(나는 당신이 그것을 사용하고 있는지 몰랐다)	PL/SQL 에서는 연결 고리가 존재 하므로 비교적 안전 ^데모#3	시스템에 의존성 관리 추적이 부족하다면 추적이 힘듦

데모

데모#1 - 동적 SQL 에서의 바인드 변수 사용 과 암시적 커서 캐싱


SQL> select executions, sql_text from v$sql
 where sql_text like '%oracleclub%' and sql_text not like '%sql%'
 order by sql_text;  2    3

선택된 레코드가 없습니다.

SQL> declare
  2    x number;
  3    sql_dynamic1 varchar2(4000)  := 'select /* oracleclub */ * from dual where rownum = ';
  4    sql_dynamic2 varchar2(4000)  := 'select /* oracleclub */ * from dual where rownum = :rnum';
  5  begin
  6    for x in 1..3 loop
  7      execute immediate sql_dynamic1 || to_char(x);
  8      execute immediate sql_dynamic2 using x;
  9    end loop;
 10  end;
 11  /

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

SQL> select executions, sql_text from v$sql
 where sql_text like '%oracleclub%' and sql_text not like '%sql%'
 order by sql_text;
  2    3
EXECUTIONS SQL_TEXT
---------- ------------------------------------------------------------------------------------------------------------------------
         1 select /* oracleclub */ * from dual where rownum = 1
         1 select /* oracleclub */ * from dual where rownum = 2
         1 select /* oracleclub */ * from dual where rownum = 3
         3 select /* oracleclub */ * from dual where rownum = :rnum

데모#2 - SELECT * 사용


SQL> create table emp (empno number);

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

SQL> insert into emp (empno) values (1);

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

SQL> insert into emp (empno) values (2);

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

SQL> insert into emp (empno) values (3);

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

SQL> declare
begin
  for x in (select * from emp) loop
    dbms_output.put_line('EMPNO: ' || to_char(x.empno));
  end loop;
end;
/
  2    3    4    5    6    7  EMPNO: 1
EMPNO: 2
EMPNO: 3

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

SQL> alter table emp add ename varchar2(10);

테이블이 변경되었습니다.

SQL> desc emp;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(10)

SQL> declare
begin
  for x in (select * from emp) loop
    dbms_output.put_line('EMPNO: ' || to_char(x.empno));
  end loop;
end;
/
  2    3    4    5    6    7  EMPNO: 1
EMPNO: 2
EMPNO: 3

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

데모#3 - 의존성(나는 당신이 그것을 사용하고 있는지 몰랐다)


SQL> create table emp1 (empno number);

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

SQL> create table emp2 (empno number);

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

SQL> select name, type, referenced_name, referenced_type
  from user_dependencies
 where referenced_owner = 'OCSTUDY';  2    3

선택된 레코드가 없습니다.

SQL> create or replace procedure p as
begin
  for x in ( select * from emp1 )
  loop
    dbms_output.put_line('EMPNO: ' || to_char(x.empno));
  end loop;
  execute immediate 'select * from emp2';
end;
/
  2    3    4    5    6    7    8    9
프로시저가 생성되었습니다.

SQL> select name, type, referenced_name, referenced_type
  from user_dependencies
 where referenced_owner = 'OCSTUDY';  2    3

NAME            TYPE            REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
P               PROCEDURE       EMP1            TABLE
P               PROCEDURE       DBMS_OUTPUT     NON-EXISTENT

SQL> select object_name, status from user_objects;

OBJECT_NAME     STATUS
--------------- ---------------
P               VALID
EMP1            VALID
EMP2            VALID

SQL> drop table emp2;

테이블이 삭제되었습니다.

SQL> select object_name, status from user_objects;

OBJECT_NAME     STATUS
--------------- ---------------
P               VALID
EMP1            VALID

SQL> drop table emp1;

테이블이 삭제되었습니다.

SQL> select name, type, referenced_name, referenced_type
  from user_dependencies
 where referenced_owner = 'OCSTUDY';  2    3

NAME            TYPE            REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
P               PROCEDURE       BIN$el7Bmn+r5nH TABLE
                                gQAEKGAM1Lw==$0

P               PROCEDURE       DBMS_OUTPUT     NON-EXISTENT

SQL> select object_name, status from user_objects;

OBJECT_NAME     STATUS
--------------- ---------------
P               INVALID

SQL> execute p;
BEGIN p; END;

      *
1행에 오류:
ORA-06550: 줄 1, 열7:PLS-00905: OCSTUDY.P 오브젝트가 부당합니다
ORA-06550: 줄 1, 열7:PL/SQL: Statement ignored


SQL> @ud

NAME            TYPE            REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
P               PROCEDURE       EMP1            NON-EXISTENT