oracle 如何在Oracle存储过程中编写动态sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10309752/
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
How to write dynamic sql in Oracle Stored procedure?
提问by ashishjmeshram
Basically in my update sql query column names going to be dynamic like
基本上在我的更新 sql 查询列名将是动态的
update bi_employee set <this_is_dynamic_column> where emp_id = 12
Below is the stored procedure that I have written so far.
下面是我到目前为止编写的存储过程。
CREATE OR replace PROCEDURE Sp_run_employee_updates
IS
CURSOR c_emp IS
SELECT *
FROM BI_EMPLOYEE_UPDATE
WHERE EFFECTIVE_DATE = To_date('30-Apr-2012', 'dd-mm-yy');
BEGIN
FOR employee_update IN c_emp LOOP
declare update_sql varchar2(225);
update_sql := 'UPDATE BI_EMPLOYEE SET '
|| employee_update.column_name
|| '= employee_update.new_value WHERE emp_id = '
|| employee_update.employee_id;
END LOOP;
END;
Its giving me foloowing errors
它给了我以下错误
Error(17,13): PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar The symbol "" was substituted for "=" to continue.
错误（17,13）：PLS-00103：遇到符号“=”，期望以下之一：常量异常表 long double ref char 时间时间戳间隔日期二进制国家字符 nchar 符号“”被替换为“=”继续。
Error(22,5): PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "UPDATE" to continue.
错误（22,5）：PLS-00103：在预期以下情况之一时遇到符号“UPDATE”： begin 函数 pragma procedure subtype type current cursor delete exists before 符号“begin”被替换为“UPDATE”以继续。
Error(31,6): PLS-00103: Encountered the symbol ";" when expecting one of the following: loop
错误（31,6）：PLS-00103：遇到符号“;” 当期待以下之一时：循环
回答by A.B.Cade
a- This should be like this:
a- 这应该是这样的：
to_date('30-Apr-2012','dd-mon-yyyy');
b- You can do it like this:
b- 你可以这样做：
CREATE OR REPLACE
PROCEDURE SP_RUN_EMPLOYEE_UPDATES IS
update_sql varchar2(225);
CURSOR c_emp IS
SELECT *
FROM BI_EMPLOYEE_UPDATE
WHERE EFFECTIVE_DATE = to_date('30-Apr-2012','dd-mon-yyyy');
BEGIN
FOR employee_update in c_emp LOOP
update_sql := 'UPDATE BI_EMPLOYEE SET ' || employee_update.column_name ||
'= :1 WHERE emp_id = :2' ;
execute immediate update_sql using employee_update.new_value, employee_update.employee_id;
END LOOP;
END SP_RUN_EMPLOYEE_UPDATES;
