2014년 11월 27일 목요일

DYNAMIC SQL[오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

　 BULLETIN CATEGORY 
BULLETIN TOPIC : SQL*Plus　　　　 
: DYNAMIC SQL 

-------------------------------------------------------------------------------- 
DYNAMIC SQL 이란?　　　 
　　　 
PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있다.　　　 
PL/SQL 2.1(RDBMS 7.1)이후 Version에서는 DBMS_SQL Package로 Dynamic SQL Statement의 사용을 가능하게 한다. 이는 Database Object의 Name을 Runtime에 줄 수 있을 뿐더러 DDL문장을 기술할 수도 있는 장점이 있다.　　　 
　* Function Open_Cursor　　　 
　 : SQL문의 실행에 필요한 새로운 Cursor를 열고 Cursor ID Number를Return 한다.　　　 

　* Function Is_Open　　　 
　 : 주어진 Cursor가 현재 Open되어 있으면 TRUE를, 아니면 FALSE를 Return한다.　　　 

　* Procedure Parse　　　 
　 : Statement를 Check하고 Cursor와 결합시킨다.　　　 

　* Procedure Bind_Variable　　　 
　 : Program내에서 Data를 저장한 Placeholder의 값을 제공하는 역할을 한다.　　　 

　* Procedure Define_Column　　　 
　 : Cursor로부터 Select된 Column의 값을 받는 변수를 지정한다.　　　 

　* Function Execute　　　 
　 : SQL문을 실행하고 처리된 Row의 수를 Return한다. (Insert, Update, Delete인 경우에만　　　 
　　　 해당)　　　 

　* Function Fetch_Rows　　　 
　 : Cursor로부터 Row를 Fetch하고 실제로 Fetch된 Row의 수를 Return한다. 이 Row들은 Buffer에　　　 
　　　 들어가며,　 Column_Value를 호출하여 읽어들여야 한다.　　　　 

　* Function Execute_And_Fetch　　　 
　 : Execute와 Fetch Row를 동시에 수행하고 실제로 Fetch된 Row의 수를 Return한다.　　　 

　* Procedure Variable_Value　　　 
　 : 주어진 변수의 값을 Return한다.　　　 

　* Procedure Column_Value　　　 
　 : Fetch_Rows에 의해 Fetch된 Data의 값을 Return한다.　　　 

　* Procedure Close_Cursor　　　 
　 : Cursor를 닫는다.　　　 

　　　　 
l Using The DBMS_SQL Package To Execute DDL Statements:　　　 

< Example 1 >　　　 
　　 Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.　　　 

CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2,　　　　 
cols varchar2) AS　　　 
　 cursor1 INTEGER;　　　 
BEGIN　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||　　　 
　　　 ' ( ' || cols || ' )', dbms_sql.v7);　　　 
　 dbms_sql.close_cursor(cursor1);　　　 
end;　　　 
/　　　 
　　　　 
SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> desc mytable;　　　 
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　 Type　　　 
　-------------------------- ------ ------------　　　 
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　 
　COL2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(10)　　　 
　　　　 
　 DDL Statement는 Parse Command에 의해 수행된다. 그러므로 DDL Statement에서는 Bind Variable을 사용할 수가 없다.　　　 

다음은 DDL Statement내에 Bind Variable을 사용한 잘못된 예이다.　　　 

CREATE OR REPLACE PROCEDURE ddlproc (tablename　　　　 
VARCHAR2, colname　　 VARCHAR2,　 coltype　　 VARCHAR2)　　　　 
AS　　　 
　 cursor1 INTEGER;　　　 
　 ignore　 INTEGER;　　　 
BEGIN　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse(cursor1,'CREATE TABLE :x1 (:y1 :z1)',　　　　 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 dbms_sql.v7);　　　 
　 dbms_sql.bind_variable(cursor1, ':x1', tablename);　　　 
　 dbms_sql.bind_variable(cursor1, ':y1', colname);　　　 
　 dbms_sql.bind_variable(cursor1, ':z1', coltype);　　　 
　 ignore := dbms_sql.execute(cursor1);　　　 
　 dbms_sql.close_cursor(cursor1);　　　 
end;　　　 
/　　　 

　Procedure를 create했을때에는 error를 만나지 않았지만, runtime에는 "ORA-00903: invalid table name" 이라는 error가 난다.　　　 
　　　　 
SQL> execute ddlproc ('MYTABLE', 'COL1', 'NUMBER');　　　 

begin ddlproc ('MYTABLE', 'COL1', 'NUMBER'); end;　　　 
　　　　 
*　　　 
ERROR at line 1:　　　 
ORA-00903: invalid table name　　　 
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239　　　 
ORA-06512: at "SYS.DBMS_SQL", line 25　　　 
ORA-06512: at "SCOTT.DDLPROC", line 8　　　 
ORA-06512: at line 1　　　 
　　　　 

< Example 2 >　　　 
　 Table을 Drop하는 Procedure로 Table Name을 Parameter로 받는다.　　　 
　　　　 

create or replace procedure droptable (table_name varchar2) as　　　 
　 cursor1 integer;　　　 
begin　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse(cursor1, 'DROP TABLE ' || table_name,　　　　 
　　　　　　　　　　　　　　　　　　　　　　　　　　 dbms_sql.v7);　　　 
　 dbms_sql.close_cursor(cursor1);　　　 
end;　　　 
/　　　 
　　　　 
SQL> begin　　　 
　 2　　　 droptable('MYTABLE');　　　 
　 3　 end;　　　 
　 4　 /　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 

　　　　 
< Example 3 >　　　 
　 DDL Statemenet를 수행하는 Procedure로 DDL Statement자체를　 Parameter로 받는다.　　　 
　　　　 
create procedure anyddl (s1 varchar2) as　　　 
　 cursor1 integer;　　　 
begin　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);　　　 
　 dbms_sql.close_cursor(cursor1);　　　 
end;　　　 
/　　　 
　　　　 
SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> desc mytable;　　　 
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　　 Type　　　 
　-------------------------　　 -----　　 -----------　　　 
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　 
　　　　 
SQL> execute anyddl('drop table mytable');　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
　　 l Using the DBMS_SQL Package to Execute Dynamic SQL Statements:　　　 

　DBMS_SQL package는 dynamic SQL statement를 수행하는데 이용되어 질 수 있는데 이는 runtime전에 statement의 일부분 혹은 전체를 알 수 없는 경우에 쓰여진다.　　　 

　　　　 
< Example 4 >　　　 
　 이 예제는 Run Time시에 주어진 Number보다 더 높은 Employee Number를 가진 모든 Employee 의 이름과 Employee Number를 Return한다.　　　 
　　　　 
CREATE or REPLACE PROCEDURE rows_greater_than (low_value　　　　 
　　　 number) AS　　　 
　 cursor1　 integer;　　　 
　 rows_processed　 integer;　　　 
　 myempno number;　　　 
　 myename varchar2(20);　　　 
BEGIN　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse (cursor1, 'select empno, ename from emp　　　　 
　　　　　　　　 where empno > :x', dbms_sql.v7);　　　 
　 dbms_sql.bind_variable(cursor1, 'x', low_value);　　　 
　 dbms_sql.define_column (cursor1, 1, myempno);　　　 
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　 
　 rows_processed := dbms_sql.execute (cursor1);　　　 
　 loop　　　 
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　 
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　 
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　 
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　 
　　　 else　　　 
　　　　　 exit;　　　 
　　　 end if;　　　 
　 end loop;　　　 
　 dbms_sql.close_cursor (cursor1);　　　 
EXCEPTION　　　 
　 WHEN OTHERS THEN　　　 
　　　 dbms_output.put_line(sqlerrm);　　　 
　　　 if dbms_sql.is_open (cursor1) then　　　 
　　　　　 dbms_sql.close_cursor (cursor1);　　　 
　　　 end if;　　　 
END;　　　 
/　　　 
　　　　 
　 DBMS_OUTPUT package을 사용하기 전에 먼저 SET SERVEROUTPUT ON command를 사용하여야 한다.　　　 
　　　　 
SQL> set serveroutput on　　　 
SQL> execute rows_greater_than(7500);　　　 
7521　　 WARD　　　 
7566　　 JONES　　　 
7654　　 MARTIN　　　 
7698　　 BLAKE　　　 
7782　　 CLARK　　　 
7788　　 SCOTT　　　 
7839　　 KING　　　 
7844　　 TURNER　　　 
7876　　 ADAMS　　　 
7900　　 JAMES　　　 
7902　　 FORD　　　 
8100　　 MILLER　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> execute rows_greater_than(8000);　　　 
8100　　 MILLER　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 

< Example 5 >　　　 
　이 예제는 Where Clause의 Parameter를 Column_Name과 Operator(<,<=,=,>=,>), New_Value로 받아서 Employee 이름과 Number를 Return한다.　　　 

　　　　 
CREATE or REPLACE PROCEDURE get_rows (column_name　　　　 
varchar2,　 comparison_type varchar2, new_value number)　　　 
　AS　　　 
　 cursor1　 integer;　　　 
　 rows_processed　 integer;　　　 
　 myempno number;　　　 
　 myename varchar2(20);　　　 
BEGIN　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse (cursor1, 'select empno, ename from emp　　　 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 where ' || column_name ||　　　 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ' ' || comparison_type || ' :x',　　　 
　　　　　　　　　　　　　　　　　　　　　 dbms_sql.v7);　　　 
　 dbms_sql.bind_variable(cursor1, 'x', new_value);　　　 
　 dbms_sql.define_column (cursor1, 1, myempno);　　　 
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　 
　　　 rows_processed := dbms_sql.execute (cursor1);　　　 
　 loop　　　 
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　 
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　 
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　 
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　 
　　　 else　　　 
　　　　　 exit;　　　 
　　　 end if;　　　 
　 end loop;　　　 
　 dbms_sql.close_cursor (cursor1);　　　 
EXCEPTION　　　 
　 WHEN OTHERS THEN　　　 
　　　 dbms_output.put_line(sqlerrm);　　　 
　　　 if dbms_sql.is_open (cursor1) then　　　 
　　　　　 dbms_sql.close_cursor (cursor1);　　　 
　　　 end if;　　　 
END;　　　 
/　　　 
　　　　 
SQL> begin　　　 
　 2　　　 get_rows('EMPNO', '<', 2000);　　　 
　 3　 end;　　　 
　 4　 /　　　 
1111　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> execute get_rows('SAL', '>', 3000);　　　 
7566　　 JONES　　　 
7788　　 SCOTT　　　 
7839　　 KING　　　 
7902　　 FORD　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> begin　　　 
　 2　　　 get_rows('DEPTNO', '>=', 20);　　　 
　 3　 end;　　　 
　 4　 /　　　 
7369　　 SMITH　　　 
7499　　 ALLEN　　　 
7521　　 WARD　　　 
7566　　 JONES　　　 
7654　　 MARTIN　　　 
7698　　 BLAKE　　　 
7788　　 SCOTT　　　 
7844　　 TURNER　　　 
7876　　 ADAMS　　　 
7900　　 JAMES　　　 
7902　　 FORD　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
< Example 6 >　　　 
　　 이 예제는 Where Clase 전체를 Runtime시에 Parameter로 받는다.　　　 
　　　　 
CREATE or REPLACE PROCEDURE get_rows (where_clause varchar2) AS　　　 
　 cursor1　 integer;　　　 
　 rows_processed　 integer;　　　 
　 myempno number;　　　 
　 myename varchar2(20);　　　 
BEGIN　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse (cursor1, 'select empno, ename from emp where '　　　　 
　　　　　　　　　　　　　　 || where_clause, dbms_sql.v7);　　　 
　 dbms_sql.define_column (cursor1, 1, myempno);　　　 
　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　 
　　　 rows_processed := dbms_sql.execute (cursor1);　　　 
　 loop　　　 
　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　 
　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　 
　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　 
　　　　　 dbms_output.put_line(to_char(myempno) || '　　 ' || myename);　　　 
　　　 else　　　 
　　　　　 exit;　　　 
　　　 end if;　　　 
　 end loop;　　　 
　 dbms_sql.close_cursor (cursor1);　　　 
EXCEPTION　　　 
　 WHEN OTHERS THEN　　　 
　　　 dbms_output.put_line(sqlerrm);　　　 
　　　 if dbms_sql.is_open (cursor1) then　　　 
　　　　　 dbms_sql.close_cursor (cursor1);　　　 
　　　 end if;　　　 
END;　　　 
/　　　 
　　　　 
SQL> execute get_rows('ENAME = ''KING'' ');　　　 
7839　　 KING　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> execute get_rows('SAL > 1000 AND DEPTNO = 10');　　　 
7782　　 CLARK　　　 
7839　　 KING　　　 
8100　　 MILLER　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
< Example 7 >　　　 
　 이 예제는 Non-Query SQL Statement를 실행한다.　　　 
　　　　 
create procedure anysql (s1 varchar2) as　　　 
　 cursor1 integer;　　　 
　 return_value integer;　　　 
begin　　　 
　 cursor1 := dbms_sql.open_cursor;　　　 
　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);　　　 
　 return_value := dbms_sql.execute(cursor1);　　　 
　 dbms_sql.close_cursor(cursor1);　　　 
end;　　　 
/　　　 
　　　　 
　　　　 
SQL> execute anysql('CREATE TABLE MYTABLE (COL1 number,　　　 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 col2 varchar2(3))');　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL　　　　　　　　　　　　　　　　　　　　 
SQL> desc mytable;　　　 
　Name　　　　　　　　　　　　　　　　　　　　　　　　　　　 Null?　　　 Type　　　 
　-------------------------- ------- -----------　　　 
　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　 
　COL2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(3)　　　 
　　　　 
SQL> execute anysql('INSERT INTO MYTABLE VALUES(1, ''ABC'')');　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
SQL> begin　　　 
　 2　　　 anysql(　　　 
　 3　　　　　 'declare　　　 
　 4　　　　　　　　 var1 varchar2(3);　　　 
　 5　　　　　　 begin　　　 
　 6　　　　　　　　 select col2　　　 
　 7　　　　　　　　　　 into var1　　　 
　 8　　　　　　　　　　 from mytable　　　 
　 9　　　　　　　　　　 where col1 = 1;　　　 
　10　　　　　　　　 dbms_output.put_line(''var1 = '' || var1);　　　 
　11　　　　　　 end;');　　　 
　12　 end;　　　 
　13　 /　　　 
var1 = ABC　　　 
　　　　 
PL/SQL procedure successfully completed.　　　 
　　　　 
　　　　 

　　　　　 
　　 더 자세한 사항은 Oracle7 Server Documentation Addendum을 참조.　　　 

　 n Forms3.0은 PL/SQL 1.1만을 지원하므로 dynamic SQL을 쓸 수 없으며 stored procedure를 만들어 호출해서 사용해야 한다. 이때 Forms가 부른 stored procedure나 function은 그 내에서 commit을 사용할 수 없게 되어 있기 때문에 COMMIT이나 ROLLBACK을 만나면 ORA-00034: Commit and Rollback from PL/SQL disabled for this session이란 error가 난다. 따라서 DDL statement도 사용을 할 수 없다.　　　 
　　　




 [100%환급,개발자전문]빅데이터/SQL/자바/스프링/안드로이드/닷…오라클자바…12-272641
 [채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육…오라클자바…12-111909
53 [평일100%환급7건]Spring,자바&JSP,안드로이드,웹퍼블리싱,C#닷… 오라클자바…03-151723
52 [주말]C#,ASP.NET마스터 오라클자바…01-311847
51 [기업100%환급,평일주간]SQL기초에서스키마오브젝트,PLSQL,힌트… 오라클자바…01-312684
50 [평일주간야간,주말]C기본&자료구조,알고리즘 오라클자바…01-311437
49 [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정-… 오라클자바…01-191759
48 [평일야간,주말]안드로이드개발자과정(Android기초실무) 오라클자바…01-111645
47 [평일야간,주말주간야간]JAVA,Network&JSP&Spring,MyBatis,Hiber… 오라클자바…01-032163
46 [100%환급,개발자전문]빅데이터/SQL/자바/스프링/안드로이드/닷… 오라클자바…12-272641
45 [평일주간]NoSQL,MongoDB,빅데이터기초과정 오라클자바…12-191868
44 [평일주간야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX… 오라클자바…12-141838
43 [채용확정무료교육]오라클자바개발잘하는신입뽑기2개월과정,교육… 오라클자바…12-111909
42 [평일주간]빅데이터하둡기초과정(BigData Hadoop) 오라클자바…12-091499
41 [평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 오라클자바…12-011730
40 [기업100%환급]오라클&자바웹스프링신입과정3주(SQL,JAVA,JSP,Se… 오라클자바…12-011907
39 [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) 오라클자바…12-011373
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)