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.