- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900648&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
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
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900648&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.