Passing Schema Object Names As Parameters(用模式对象名称作为存储过程的参数）

Passing Schema Object Names As Parameters
Suppose you need a procedure that accepts the name of any database table, then truncate that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

create table t1
as
select * from all_objects
where rownum<21;

create table t2
as
select * from all_objects
where rownum<21;

create table t3
as
select * from all_objects
where rownum<21;

create table t4
as
select * from all_objects
where rownum<21;

create table t5
as
select * from all_objects
where rownum<21;

create table t6
as
select * from all_objects
where rownum<21;

SQL> CREATE or replace PROCEDURE truncate_table (table_name IN VARCHAR2) AS
2 BEGIN
3 EXECUTE IMMEDIATE 'truncate TABLE ' || table_name;
4 END;
5 /

SQL>
create table t7
as
select * from all_objects
where rownum<21;

create table t8
as
select * from all_objects
where rownum<21;

create table t9
as
select * from all_objects
where rownum<21;

create or replace procedure truncate_table(table_name varchar2)
as
begin
execute immediate 'truncate table '||table_name;
end;
/

SQL> execute truncate_table('t1');

PL/SQL 过程已成功完成。

SQL> select count(*) from t1;

COUNT(*)
----------
0

SQL> execute truncate_table('t2');

PL/SQL 过程已成功完成。

SQL> select count(*) from t2;

COUNT(*)
----------
0

SQL> execute truncate_table('t3');

PL/SQL 过程已成功完成。

SQL> execute truncate_table('t4');

PL/SQL 过程已成功完成。

SQL> select count(*) from t3;

COUNT(*)
----------
0

SQL> select count(*) from t4;

COUNT(*)
----------
0

用绑定变量测试：
create or replace procedure truncate_table(table_name varchar2)
as
begin
execute immediate 'truncate table :tab' using table_name;
end;
/

过程已创建。

SQL> execute truncate_table('t7');
BEGIN truncate_table('t7'); END;

*
第 1 行出现错误:
ORA-00903: 表名无效
ORA-06512: 在 "TEST.TRUNCATE_TABLE", line 4
ORA-06512: 在 line

正如： Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

不知why?

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

转载于:http://blog.itpub.net/9599/viewspace-472978/