Question

我正试图建立一个Oracle储存的程序,接受一张表名作为参数。这一程序将重建表格上的所有索引。

我的问题是,在使用“ALTER”指挥系统时,我犯了一个错误,因为如果PLSQL不允许这一指挥。

Answer 1

这里有两种可能性。首先,你们必须把卡片作为动态的宝库对待。其次,Oracle DDL声明不能在交易中进行(或终止目前的交易,不能自行撤销)。这可能影响到你能否在所储存的程序中使用这些程序,或者在你能够使用含有这些储存的程序的情况下。

如果上述任何一项都适用——可能很容易发生其他的欺骗——我建议张贴一些法典。

Answer 2

利用<条码>立即销毁说明,在PL/SQL中执行DDL。

create procedure RebuildIndex(index_name varchar2) as
begin
   execute immediate  alter index   || index_name ||   rebuild ;
end;

我测试了该守则;该守则行之有效。

Answer 3

Documentation。

www.un.org/Depts/DGACM/index_spanish.htm 通过Schema 反对名

Suppose you need a procedure that accepts the name of any database table, then drops 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:

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE  DROP TABLE   || table_name;
END;
/

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

In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure (drop_table) by specifying the procedure name when executed.

CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) ASBEGIN
   EXECUTE IMMEDIATE  CALL "  || proc_name ||  " ( :proc_name )  using table_name;
END;
/

If you want to drop a table with the drop_table procedure, you can run the procedure as follows. Note that the procedure name is capitalized.

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
BEGIN 
  run_proc( DROP_TABLE ,  employees_temp ); 
END;
/

友情链接