SQL 带有 Alter 命令的 Oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2502302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Stored Procedure with Alter command
提问by Will
I am trying to build an Oracle stored procedure which will accept a table name as a parameter. The procedure will then rebuild all indexes on the table.
我正在尝试构建一个将接受表名作为参数的 Oracle 存储过程。然后该过程将重建表上的所有索引。
My problem is I get an error while using the ALTER command from a stored procedure, as if PLSQL does not allow that command.
我的问题是在使用存储过程中的 ALTER 命令时出现错误，就好像 PLSQL 不允许该命令一样。
采纳答案by Jonathan Leffler
Here are a couple of possibilities. First, you would have to treat the SQL as dynamic SQL. Second, Oracle DDL statements cannot be run in a transaction (or, they terminate the current transaction and cannot themselves be rolled back). This may affect whether you can use them in stored procedures, or where you can use stored procedures that contain them.
这里有几种可能性。首先，您必须将 SQL 视为动态 SQL。其次，Oracle DDL 语句不能在事务中运行（或者，它们终止当前事务并且本身不能回滚）。这可能会影响您是否可以在存储过程中使用它们，或者您可以在何处使用包含它们的存储过程。
If none of the above apply at all - there could easily be something else astray - I suggest posting some code.
如果以上都不适用 - 很容易有其他东西误入歧途 - 我建议发布一些代码。
回答by Igby Largeman
Use the execute immediatestatement to execute DDL inside PL/SQL.
使用该execute immediate语句在 PL/SQL 中执行 DDL。
create procedure RebuildIndex(index_name varchar2) as
begin
execute immediate 'alter index ' || index_name || ' rebuild';
end;
I tested this code; it works.
我测试了这段代码；有用。
回答by Brian
Passing Schema Object Names As Parameters
将架构对象名称作为参数传递
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:
假设您需要一个过程来接受任何数据库表的名称，然后从架构中删除该表。您必须使用包含对象名称的语句构建一个字符串，然后使用 EXECUTE IMMEDIATE 来执行该语句：
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.
使用串联来构建字符串，而不是尝试通过 USING 子句将表名作为绑定变量传递。
此外，如果您需要调用一个名称在运行时之前未知的过程，您可以传递一个标识该过程的参数。例如，下面的过程可以通过在执行时指定过程名称来调用另一个过程（drop_table）。
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.
如果要使用 drop_table 过程删除表，可以按如下方式运行该过程。请注意，过程名称是大写的。
CREATE TABLE employees_temp AS SELECT last_name FROM employees;
BEGIN
run_proc('DROP_TABLE', 'employees_temp');
END;
/
