[오라클자바커뮤니티,오라클SQL,초보실무SQL교육학원,오라클학원]Dynamic SQL 사용하기 ,오라클자바커뮤니티학원
Dynamic SQL은 사용자의 입력에 의해 SQL문이 작성되거나 다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우 이용하게 됩니다.
Oracle에서의 Dynamic SQL은 EXECUTE IMMEDIATE 절을 사용하여 embedded dynamic sql을 구현하는 방법과 다른 방법은 선언되어지는 것 대신에 실행 시에 문장을 보내는 ref cursor의 확장된 개념으로 query를 위해 사용되어지는 방법이 있습니다.
아래 예제를 보면서 이해하도록 합니다.
----------------------------------------------------
SQL> conn scott/tiger
연결되었습니다.
SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
2 sql_stmt varchar2(4000);
3 BEGIN
4 EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
5 END;
6 /
프로시저가 생성되었습니다.
SQL>-- EXECUTE IMMEDIATE를 실행하기 위해서는 create any table 권한이 필요…!
SQL> exec CREATE_TABLE1;
BEGIN CREATE_TABLE1; END;
*
1행에 오류:
ORA-01031: 권한이 불충분합니다
ORA-06512: "SCOTT.CREATE_TABLE1", 줄 4에서
ORA-06512: 줄 1에서
SQL> conn / as sysdba
연결되었습니다.
SQL> grant create any table to scott;
권한이 부여되었습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> exec CREATE_TABLE1;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> desc x;
이름 널? 유형
----------------------------------------- -------- -----------------------
A NUMBER
SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
2 cur integer;
3 rc integer;
4 BEGIN
5 cur := DBMS_SQL.OPEN_CURSOR;
6 DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
7 rc := DBMS_SQL.EXECUTE(cur);
8 DBMS_SQL.CLOSE_CURSOR(cur);
9 END;
10 /
프로시저가 생성되었습니다.
SQL> drop table x;
테이블이 삭제되었습니다.
SQL> exec CREATE_TABLE2;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> desc x;
이름 널? 유형
----------------------------------------- -------- ------
Y DATE
SQL> -- 다음은 bind variable을 사용한 예제 입니다.
SQL> CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
2 v_cursor integer;
3 v_dname char(20);
4 v_rows integer;
5 BEGIN
6 v_cursor := DBMS_SQL.OPEN_CURSOR;
7 DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS
_SQL.V7);
8 DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
9 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
10 v_rows := DBMS_SQL.EXECUTE(v_cursor);
11 loop
12 if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
13 exit;
14 end if;
15 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
16 DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
17 end loop;
18 DBMS_SQL.CLOSE_CURSOR(v_cursor);
19 EXCEPTION
20 when others then
21 DBMS_SQL.CLOSE_CURSOR(v_cursor);
22 raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcod
e||' '||sqlerrm);
23 END;
24 /
프로시저가 생성되었습니다.
SQL> exec DEPARTMENTS(10);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set serveroutput on
SQL> exec DEPARTMENTS(10);
Deptartment name: RESEARCH
Deptartment name: SALES
Deptartment name: OPERATIONS
PL/SQL 처리가 정상적으로 완료되었습니다.
Dynamic SQL은 사용자의 입력에 의해 SQL문이 작성되거나 다른 STATIC SQL문의 결과에 의해서 SQL문이 생성되는 경우 이용하게 됩니다.
Oracle에서의 Dynamic SQL은 EXECUTE IMMEDIATE 절을 사용하여 embedded dynamic sql을 구현하는 방법과 다른 방법은 선언되어지는 것 대신에 실행 시에 문장을 보내는 ref cursor의 확장된 개념으로 query를 위해 사용되어지는 방법이 있습니다.
아래 예제를 보면서 이해하도록 합니다.
----------------------------------------------------
SQL> conn scott/tiger
연결되었습니다.
SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE1 AS
2 sql_stmt varchar2(4000);
3 BEGIN
4 EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
5 END;
6 /
프로시저가 생성되었습니다.
SQL>-- EXECUTE IMMEDIATE를 실행하기 위해서는 create any table 권한이 필요…!
SQL> exec CREATE_TABLE1;
BEGIN CREATE_TABLE1; END;
*
1행에 오류:
ORA-01031: 권한이 불충분합니다
ORA-06512: "SCOTT.CREATE_TABLE1", 줄 4에서
ORA-06512: 줄 1에서
SQL> conn / as sysdba
연결되었습니다.
SQL> grant create any table to scott;
권한이 부여되었습니다.
SQL> conn scott/tiger
연결되었습니다.
SQL> exec CREATE_TABLE1;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> desc x;
이름 널? 유형
----------------------------------------- -------- -----------------------
A NUMBER
SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS
2 cur integer;
3 rc integer;
4 BEGIN
5 cur := DBMS_SQL.OPEN_CURSOR;
6 DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
7 rc := DBMS_SQL.EXECUTE(cur);
8 DBMS_SQL.CLOSE_CURSOR(cur);
9 END;
10 /
프로시저가 생성되었습니다.
SQL> drop table x;
테이블이 삭제되었습니다.
SQL> exec CREATE_TABLE2;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> desc x;
이름 널? 유형
----------------------------------------- -------- ------
Y DATE
SQL> -- 다음은 bind variable을 사용한 예제 입니다.
SQL> CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
2 v_cursor integer;
3 v_dname char(20);
4 v_rows integer;
5 BEGIN
6 v_cursor := DBMS_SQL.OPEN_CURSOR;
7 DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS
_SQL.V7);
8 DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
9 DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
10 v_rows := DBMS_SQL.EXECUTE(v_cursor);
11 loop
12 if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
13 exit;
14 end if;
15 DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
16 DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
17 end loop;
18 DBMS_SQL.CLOSE_CURSOR(v_cursor);
19 EXCEPTION
20 when others then
21 DBMS_SQL.CLOSE_CURSOR(v_cursor);
22 raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcod
e||' '||sqlerrm);
23 END;
24 /
프로시저가 생성되었습니다.
SQL> exec DEPARTMENTS(10);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> set serveroutput on
SQL> exec DEPARTMENTS(10);
Deptartment name: RESEARCH
Deptartment name: SALES
Deptartment name: OPERATIONS
PL/SQL 처리가 정상적으로 완료되었습니다.
오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급
www.oraclejavacommunity.com
평일주간(9:30~18:10) 개강
(6/30)[기업100%환급]PL/SQL,ORACLE HINT,TUNING[개강확정]
(6/30)[기업100%환급]안드로이드개발자과정[개강확정]
(6/30)[기업100%환급]SQL기초에서 Schema Object까지[개강확정]
(7/07)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/07)[기업100%환급]Spring ,MyBatis,Hibernate실무과정[개강확정]
(7/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(7/07)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정
평일야간(19:00~21:50) 개강
(6/26)SQL초보에서실전전문가까지[개강확정]
(7/01)안드로이드개발자과정[개강확정]
(7/01)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(7/02)Spring3.X, MyBatis, Hibernate실무과정[개강확정]
(7/02)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/02)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
(7/03)웹퍼블리싱 마스터
(7/15)MyBatis3.X, Hibernate4.X ORM실무과정
(7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
주말(10:00~17:50) 개강
(6/28)Spring3.X, MyBatis, Hibernate실무과정[개강확정]
(6/28)안드로이드개발자과정
(6/29)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지[개강확정]
(7/05)SQL초보에서 Schema Object까지
(7/12)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/12)MyBatis3.X, Hibernate4.X ORM실무과정
(7/12)개발자를위한PLSQL,SQL튜닝,힌트
(7/12)실무예제로 배워보는 jQuery(개발자/디자이너를위한)
(7/13)C#,ASP.NET마스터
주말저녁(18:30~22:20) 개강
(6/28)JAVA,Network&WEB&Framework
(6/28)SQL기초에서실무까지
www.oraclejavacommunity.com
평일주간(9:30~18:10) 개강
(6/30)[기업100%환급]PL/SQL,ORACLE HINT,TUNING[개강확정]
(6/30)[기업100%환급]안드로이드개발자과정[개강확정]
(6/30)[기업100%환급]SQL기초에서 Schema Object까지[개강확정]
(7/07)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍
(7/07)[기업100%환급]Spring ,MyBatis,Hibernate실무과정[개강확정]
(7/07)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지
(7/07)[채용예정교육]오라클자바개발잘하는신입뽑기프로젝트,교육전취업확정
평일야간(19:00~21:50) 개강
(6/26)SQL초보에서실전전문가까지[개강확정]
(7/01)안드로이드개발자과정[개강확정]
(7/01)닷넷(C#,Network,ADO.NET,ASP.NET)마스터과정
(7/02)Spring3.X, MyBatis, Hibernate실무과정[개강확정]
(7/02)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/02)JAVA,Network&WEB&Framework(자바기초에서웹스프링까지)
(7/03)웹퍼블리싱 마스터
(7/15)MyBatis3.X, Hibernate4.X ORM실무과정
(7/22)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
주말(10:00~17:50) 개강
(6/28)Spring3.X, MyBatis, Hibernate실무과정[개강확정]
(6/28)안드로이드개발자과정
(6/29)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지[개강확정]
(7/05)SQL초보에서 Schema Object까지
(7/12)자바웹(JSP,Spring,MyBatis,XPlatform)프로젝트과정
(7/12)MyBatis3.X, Hibernate4.X ORM실무과정
(7/12)개발자를위한PLSQL,SQL튜닝,힌트
(7/12)실무예제로 배워보는 jQuery(개발자/디자이너를위한)
(7/13)C#,ASP.NET마스터
주말저녁(18:30~22:20) 개강
(6/28)JAVA,Network&WEB&Framework
(6/28)SQL기초에서실무까지
댓글 없음:
댓글 쓰기