oracle dbms_sql执行查询select_dml_ddl(一）

SQL> conn scott/system
SQL>
  1  create or replace procedure p_emp(salary number)
  2  as
  3  cursor_name integer;
  4  rows_processes integer;
  5  begin
  6  cursor_name:=dbms_sql.open_cursor;  --打开游标
  7  dbms_sql.parse(cursor_name,'delete from emp where sal>:x',dbms_sql.native);--解析游标,dbms_sql.parse三个参数:游标名称,要解析的sql或ddl或dml,第三个参数
  8  dbms_sql.bind_variable(cursor_name,':x',salary);--因为解析中用到了绑定变量,所以此处采用dbms_sql.bind_variable过程,把存储过程的输入参数salary传递给:x绑定

                                                    --变量
  9  rows_processes:=dbms_sql.execute(cursor_name);--执行游标(也就是执行解析过的sql或dml或ddl)
 10  dbms_sql.close_cursor(cursor_name);--关闭游标,用完了吗;不关就会占用内存
 11  exception  --exception关键字
 12  when others then
 13    dbms_sql.close_cursor(cursor_name);  --采用异常关闭游标
 14* end;

Procedure created.

SQL> exec p_emp(1100); --调用存储过程,从emp表中删除工资小于1100的记录

PL/SQL procedure successfully completed.

SQL> commit;---最好把commit写进dbms.sql的存储过程中,dbms_sql不会提交解析过的dml

Commit complete.

 

SQL> r
  1  create or replace procedure p_dynamic(string in  varchar2)
  2  as
  3  cursor_name integer;
  4  result integer;
  5  begin
  6  cursor_name:=dbms_sql.open_cursor;
  7  dbms_sql.parse(cursor_name,string,dbms_sql.native);
  8  result:=dbms_sql.execute(cursor_name);
  9  dbms_sql.close_cursor(cursor_name);
 10* end;

Procedure created.

SQL> exec p_dynamic('create table pp(a int)');---看到没,报权限 不足啊,怪了吗??
BEGIN p_dynamic('create table pp(a int)'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.P_DYNAMIC", line 7
ORA-06512: at line 1

SQL> r
  1  create or replace procedure p_dynamic(string in  varchar2)
  2  as
  3  cursor_name integer;
  4  result integer;
  5  begin
  6  cursor_name:=dbms_sql.open_cursor;
  7  dbms_sql.parse(cursor_name,string,dbms_sql.native);
  8  result:=dbms_sql.execute(cursor_name);
  9  dbms_sql.close_cursor(cursor_name);
 10* end;

Procedure created.

 

 

  1  create or replace procedure p_dynamic(string in  varchar2)  --这个存储过程应用dbms_sql执行一个ddl建表语句
  2  authid current_user  --为以上报权限不足的存储过程添加此行,再次调用存储过程就不会报错了
  3  as
  4  cursor_name integer;
  5  result integer;
  6  begin
  7  cursor_name:=dbms_sql.open_cursor;--开一个游标
  8  dbms_sql.parse(cursor_name,string,dbms_sql.native);--解析游标
  9  result:=dbms_sql.execute(cursor_name);--执行游标
 10  dbms_sql.close_cursor(cursor_name);--关闭游标
 11* end;

Procedure created.

SQL> exec p_dynamic('create table pp(a int)');--调用以上存储过程建表,ok了

PL/SQL procedure successfully completed.

SQL> desc pp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

 

 

-bash-3.2$ sqlplus scott/system

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 2 02:02:38 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure p_copy(source in varchar2,destination in varchar2) --利用dbms_sql复制一个表数据到另一个表(二表结构相同)
                                                                                    --存储过程2个输入参数各为:源与目标表的名字
  2  is
  3  id_var number;  --源与目标表的列1
  4  name_var varchar2(30);--同上,列2
  5  birthdate_var date;--同上,列3
  6  source_cursor integer;--提取源表数据的游标
  7  destination_cursor integer;--同上,插入到目标表的游标
  8  ignore integer;--执行游标
  9  begin
 10  source_cursor:=dbms_sql.open_cursor;--开源表游标
 11  dbms_sql.parse(source_cursor,'select id,name,birthdate from '||source,dbms_sql.native);--解析源表游标,此处第二个参数用了||连接符,传入参数source(存储过程

                                                                                           --的输入参数)
 12  dbms_sql.define_column(source_cursor,1,id_var);--定义接收源表游标数据的列,此处为列1,对应存储过程内部定义参数 id_var
 13  dbms_sql.define_column(source_cursor,2,name_var,30);--同上,列2
 14  dbms_sql.define_column(source_cursor,3,birthdate_var);--同上,列3
 15  ignore:=dbms_sql.execute(source_cursor);--执行源表游标
 16  destination_cursor:=dbms_sql.open_cursor;--开目标表游标
 17  dbms_sql.parse(destination_cursor,'insert into '||destination ||' values (:id_bind,:name_bind,:birthdate_bind)',dbms_sql.native);
            --解析目标表游标,以绑定变量方式插入数据到目标表

 

 18  loop  --用一个loop循环处理，因为源游标会提取多行记录啊
 19  if dbms_sql.fetch_rows(source_cursor)>0 then --使用dbms_sql.fetch_rows判断源游标提取数据是否还有记录；此处dbms_sql.fetch_rows用于从源游--标提取记录
 20  dbms_sql.column_value(source_cursor,1,id_var);--dbms_sql.column_value返回特定游标具体位置元素的值；它用于访问dbms_sql.fetch_rows提取的--记录
 21  dbms_sql.column_value(source_cursor,2,name_var);--column_name有三个参数：游标名称，游标中特定位置的元素，要返回的值
 22  dbms_sql.column_value(source_cursor,3,birthdate_var);--此处三个column_value用于返回源表游标三个参数（对应select三列）给存储过程内部定义的变量
 23
 24
 25  dbms_sql.bind_variable(destination_cursor,':id_bind',id_var);---牛吧，以上三个column_name接收了源游标的参数值，这里用bind_variable把以上参数的值传递给目

                                                                 --标表游标，这不就实现把源表的数据复制到了目标表了吗，牛
 26  dbms_sql.bind_variable(destination_cursor,':name_bind',name_var);--同上
 27  dbms_sql.bind_variable(destination_cursor,':birthdate_bind',birthdate_var);--同上;bind_variable三个参数：游标名称，游标中对应要解析sql或dml或ddl的绑定变

                                                                               ---量，要传递给绑定变量的参数
 28
 29  ignore:=dbms_sql.execute(destination_cursor);--执行目标表游标，说明在存储过程中定义一个ignore可以在存储过程中多处执行游标处使用,指的是：execute过程
 30  else  --否则如果从源游标提取不到记录了
 31  exit;  --牛了，就退出源游标了
 32  end if;
 33  end loop; ---对应上面的loop
 34  commit; ---处理了sql,dml,ddl就提交sql了，存储过程内部不会自动提交
 35  dbms_sql.close_cursor(source_cursor);--处理完了工作，也提交了工作，就关闭游标吧，别浪费内存哟
 36  dbms_sql.close_cursor(destination_cursor);
 37  exception  --为了健壮性，加上异常处理
 38  when others then
 39   if dbms_sql.is_open(source_cursor) then  --用dbms_sql.is_open(游标名称）判断游标是否打开
 40     dbms_sql.close_cursor(source_cursor); --用close_cursor关游标
 41   end if;
 42   if dbms_sql.is_open(destination_cursor) then
 43     dbms_sql.close_cursor(destination_cursor);
 44  end if;
 45  raise;
 46  end;
 47  /

Procedure created.

SQL> exec p_copy('source','destination'); --调用以上存储过程，复制源表数据到目标表

PL/SQL procedure successfully completed.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-670008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-670008/