- 以DDL语句为参数。
- 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.
2:Sql代码 ![复制代码](http://taosst.javaeye.com/images/icon_copy.gif)
- PL/SQL中使用动态SQL编程
-
- 在PL/SQL程序设计过程中,会遇到很多必须使用动态sql的地方,oracle系统所提供的DMBS_SQL包可以帮助你解决问题。
- (一)介绍
- DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:
-
- function open_cursor:打开一个动态游标,并返回一个整型;
-
- procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;
-
- procedure parse(c in integer, statement in varchar2, language_flag in integer):对动态游标所提供的sql语句进行解析,参数C表示游标,statement为sql语句,language-flag为解析sql语句所用oracle版本,一般有V6,V7跟native(在不明白所连database版本时,使用native);
-
- procedure define_column(c in integer, position in integer, column any datatype, [column_size in integer]):定义动态游标所能得到的对应值,其中c为动态游标,positon为对应动态sql中的位置(从1开始),column为该值所对应的变量,可以为任何类型,column_size只有在column为定义长度的类型中使用如VARCHAR2,CHAR等(该过程有很多种情况,此处只对一般使用到的类型进行表述);
-
- function execute(c in integer):执行游标,并返回处理一个整型,1表示成功,0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);
-
- function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;
-
- procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;
-
- procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;
-
- 以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql
-
- (二)一般过程
- 对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:
- open cursor
- 而对于dml操作(insert,update)则需要进行以下几个步骤:
- open cursor
- 对于delete操作只需要进行以下几个步骤:
- open cursor
-
- (三)实例应用
- 1. declare
- v_cid integer;
- v_updatestr varchar2(100);
- v_rowupdated integer;
- begin
- v_cid:=dbms_sql.open_cursor;
- v_updatestr:='update emp set comm=400 where empno=7499';
- dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
- v_rowupdated:=dbms_sql.execute(v_cid);
- dbms_sql.close_cursor(v_cid);
- exception
- when others then
- dbms_sql.close_cursor(v_cid);
- raise;
- end;
- 2.create or replace function updatecomm(p_comm emp.comm%type, p_empno emp.empno%type
- return integer as
- v_cid integer;
- v_updatestr varchar2(100);
- v_rowupdated integer;
- begin
- v_cid:=dbms_sql.open_cursor;
- v_updatestr:='update emp set comm=:comm where empno=:empno';
- dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);
- dbms_sql.bind_variable(v_cid,'comm','p_comm');
- dbms_sql.bind_variable(v_cid,'empno','p_empno');
- v_rowupdated:=dbms_sql.execute(v_cid);
- dbms_sql.close_cursor(v_cid);
- return p_rowsupdated;
- exception
- when others then
- dbms_sql.close_cursor(v_cid);
- raise;
- end;
- 调用
- declare
- a integer;
- begin
- a:=updatecomm(5000,a);
- dbms_output.put_line(a);
- end;
- 3.create or replace procedure dynamiccopy(p_deptno1 emp.deptno%type default null,p_deptno2 emp.deptno%type default null)
- as
- v_cid integer;
- v_select varchar2(100);
- v_empno char(4);
- v_ename varchar2(10);
- v_deptno char(2);
- v_dummy integer;
- begin
- v_cid:=dbms_sql.open_cursor;
- v_select:='select empno,ename,deptno from emp where deptno in(:d1,:d2)';
- dbms_sql.parse(v_cid,v_select,dbms_sql.native);
- dbms_sql.bind_variable(v_cid,'d1',p_deptno1);
- dbms_sql.bind_variable(v_cid,'d2',p_deptno2);
- dbms_sql.define_column(v_cid,1,v_empno,4);
- dbms_sql.define_column(v_cid,2,v_ename,10);
- dbms_sql.define_column(v_cid,3,v_deptno,2);
- v_dummy:=dbms_sql.execute(v_cid);
- loop
- if dbms_sql.fetch_rows(v_cid)=0 then
- exit;
- end if;
- dbms_sql.column_value(v_cid,1,v_empno);
- dbms_sql.column_value(v_cid,2,v_ename);
- dbms_sql.column_value(v_cid,3,v_deptno);
- insert into emp1(empno,ename,deptno) values(v_empno,v_ename,v_deptno);
- end loop;
- dbms_sql.close_cursor(v_cid);
- commit;
- exception
- when others then
- dbms_sql.close_cursor(v_cid);
- raise;
- end;
- 4.DDL语句:DDL中联编变量是非法的,即使在解析后不能够调用bind_variable过程。另外,DDL解析后立即执行,不需要调用EXECUTE过程,即使调用了也没有用。
- create or replace procedure recreatetable(p_table in varchar2,p_description in varchar2)
- as
- v_cursor number;
- v_createstring varchar2(100);
- v_dropstring varchar2(100);
- begin
- v_cursor:=dbms_sql.open_cursor;
- v_dropstring:='drop table'||p_table;
- begin
- dbms_sql.parse(v_cursor,v_dropstring,dbms_sql.v7);
- exception
- when others then
- if sqlcode!=-942 then
- raise;
- end if;
- end;
- v_createstring:='create table'||p_table||p_description;
- dbms_sql.parse(v_cursor,v_createstring,dbms_sql.native);
- dbms_sql.close_cursor(v_cursor);
- exception
- when others then
- dbms_sql.close_cursor(v_cursor);
- raise;
- end;
posted on
2011-02-17 23:12
马侃
阅读(
701)
评论()
编辑
收藏
举报