关于dbms_sql的使用

1：Sql代码 复制代码
  1. 以DDL语句为参数。   
  2. 　　create procedure anyddl (s1 varchar2) as     
  3. 　　 cursor1 integer;    
  4. 　　begin    
  5. 　　 cursor1 := dbms_sql.open_cursor;    
  6. 　　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);    
  7. 　　 dbms_sql.close_cursor(cursor1);    
  8. 　　end;    
  9. 　　/    
  10. 　　SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');    
  11. 　　PL/SQL procedure successfully completed.    
  12. 　　SQL> desc mytable;    
  13. 　　Name Null? Type    
  14. 　　------------------------------- -------- ----    
  15. 　　COL1 NUMBER    
  16. 　　SQL> execute anyddl('drop table mytable');    
  17. 　　PL/SQL procedure successfully completed.  

2：Sql代码 复制代码

  1. PL/SQL中使用动态SQL编程   
  2.   
  3. 在PL/SQL程序设计过程中，会遇到很多必须使用动态sql的地方，oracle系统所提供的DMBS_SQL包可以帮助你解决问题。   
  4. (一)介绍   
  5. DBMS_SQL系统包提供了很多函数及过程,现在简要阐述其中使用频率较高的几种:   
  6.   
  7. function open_cursor:打开一个动态游标,并返回一个整型;   
  8.   
  9. procedure close_cursor(c in out integer);关闭一个动态游标,参数为open_cursor所打开的游标;   
  10.   
  11. 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);   
  12.   
  13. 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等(该过程有很多种情况,此处只对一般使用到的类型进行表述);   
  14.   
  15. function execute(c in integer):执行游标,并返回处理一个整型，1表示成功，0表示失败,代表处理结果(对insert,delete,update才有意义,而对select语句而言可以忽略);   
  16.   
  17. function fetch_rows(c in integer):对游标进行循环取数据,并返回一个整数,为0时表示已经取到游标末端;   
  18.   
  19. procedure column_value(c in integer, position in integer, value):将所取得的游标数据赋值到相应的变量,c为游标,position为位置,value则为对应的变量;   
  20.   
  21. procedure bind_variable(c in integer, name in varchar2, value):定义动态sql语句(DML)中所对应字段的值,c为游标,name为字段名称,value为字段的值;   
  22.   
  23. 以上是在程序中经常使用到的几个函数及过程,其他函数及过程请参照oracle所提供定义语句dbmssql.sql   
  24.   
  25. (二)一般过程   
  26. 对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤:   
  27. open cursor--->parse--->define column--->excute--->fetch rows--->close cursor;   
  28. 而对于dml操作(insert,update)则需要进行以下几个步骤:   
  29. open cursor--->parse--->bind variable--->execute--->close cursor;   
  30. 对于delete操作只需要进行以下几个步骤:   
  31. open cursor--->parse--->execute--->close cursor;   
  32.   
  33. (三)实例应用   
  34. 1. declare  
  35. v_cid integer;   
  36. v_updatestr varchar2(100);   
  37. v_rowupdated integer;   
  38. begin  
  39. v_cid:=dbms_sql.open_cursor;   
  40. v_updatestr:='update emp set comm=400 where empno=7499';   
  41. dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);   
  42. v_rowupdated:=dbms_sql.execute(v_cid);   
  43. dbms_sql.close_cursor(v_cid);   
  44. exception   
  45. when others then  
  46. dbms_sql.close_cursor(v_cid);   
  47. raise;   
  48. end;   
  49. 2.create or replace function updatecomm(p_comm emp.comm%type, p_empno emp.empno%type   
  50. return integer as  
  51. v_cid integer;   
  52. v_updatestr varchar2(100);   
  53. v_rowupdated integer;   
  54. begin  
  55. v_cid:=dbms_sql.open_cursor;   
  56. v_updatestr:='update emp set comm=:comm where empno=:empno';   
  57. dbms_sql.parse(v_cid,v_updatestr,dbms_sql.native);   
  58. dbms_sql.bind_variable(v_cid,'comm','p_comm');   
  59. dbms_sql.bind_variable(v_cid,'empno','p_empno');   
  60. v_rowupdated:=dbms_sql.execute(v_cid);   
  61. dbms_sql.close_cursor(v_cid);   
  62. return p_rowsupdated;   
  63. exception   
  64. when others then  
  65. dbms_sql.close_cursor(v_cid);   
  66. raise;   
  67. end;   
  68. 调用--   
  69. declare  
  70. a integer;   
  71. begin  
  72. a:=updatecomm(5000,a);   
  73. dbms_output.put_line(a);   
  74. end;   
  75. 3.create or replace procedure dynamiccopy(p_deptno1 emp.deptno%type default null,p_deptno2 emp.deptno%type default null)   
  76. as  
  77. v_cid integer;   
  78. v_select varchar2(100);   
  79. v_empno char(4);    
  80. v_ename varchar2(10);   
  81. v_deptno char(2);   
  82. v_dummy integer;   
  83. begin  
  84. v_cid:=dbms_sql.open_cursor;   
  85. v_select:='select empno,ename,deptno from emp where deptno in(:d1,:d2)';   
  86. dbms_sql.parse(v_cid,v_select,dbms_sql.native);   
  87. dbms_sql.bind_variable(v_cid,'d1',p_deptno1);   
  88. dbms_sql.bind_variable(v_cid,'d2',p_deptno2);   
  89. dbms_sql.define_column(v_cid,1,v_empno,4);   
  90. dbms_sql.define_column(v_cid,2,v_ename,10);   
  91. dbms_sql.define_column(v_cid,3,v_deptno,2);   
  92. v_dummy:=dbms_sql.execute(v_cid);   
  93. loop   
  94. if dbms_sql.fetch_rows(v_cid)=0 then  
  95. exit;   
  96. end if;   
  97. dbms_sql.column_value(v_cid,1,v_empno);   
  98. dbms_sql.column_value(v_cid,2,v_ename);   
  99. dbms_sql.column_value(v_cid,3,v_deptno);   
  100. insert into emp1(empno,ename,deptno) values(v_empno,v_ename,v_deptno);   
  101. end loop;   
  102. dbms_sql.close_cursor(v_cid);   
  103. commit;   
  104. exception   
  105. when others then  
  106. dbms_sql.close_cursor(v_cid);   
  107. raise;   
  108. end;    
  109. 4.DDL语句：DDL中联编变量是非法的，即使在解析后不能够调用bind_variable过程。另外，DDL解析后立即执行，不需要调用EXECUTE过程，即使调用了也没有用。   
  110. create or replace procedure recreatetable(p_table in varchar2,p_description in varchar2)   
  111. as  
  112. v_cursor number;   
  113. v_createstring varchar2(100);   
  114. v_dropstring varchar2(100);   
  115. begin  
  116. v_cursor:=dbms_sql.open_cursor;   
  117. v_dropstring:='drop table'||p_table;   
  118. begin  
  119. dbms_sql.parse(v_cursor,v_dropstring,dbms_sql.v7);   
  120. exception   
  121. when others then  
  122. if sqlcode!=-942 then  
  123. raise;   
  124. end if;   
  125. end;   
  126. v_createstring:='create table'||p_table||p_description;   
  127. dbms_sql.parse(v_cursor,v_createstring,dbms_sql.native);   
  128. dbms_sql.close_cursor(v_cursor);   
  129. exception   
  130. when others then  
  131. dbms_sql.close_cursor(v_cursor);   
  132. raise;   
  133. end;  
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/advocate/p/1957414.html