execute immediate中的
我想知道是否可以在Oracle SQL中的select语句中动态评估case语句。以以下pseudo-sql代码为例：
WITH temp AS (
SELECT
'WHEN ' || when_col || ' THEN ''' || then_value || '''' as case_logic
FROM some_table
)
SELECT
case
{temp.case_logic} -- EVALUATE THIS FOR ALL ROWS IN TEMP DURING RUNTIME
else 'NA'
end as case_when,
table2.*
FROM table2;
我尝试用占位符实现上面的内容，如下所示：
DECLARE
case_when_logic VARCHAR2(4000 byte);
plsql_block VARCHAR2(4000 byte);
TYPE case_when_logic_tbl_type IS TABLE OF VARCHAR2(1000 BYTE);
case_when_logic_tbl case_when_logic_tbl_type;
BEGIN
case_when_logic := q'[SELECT 'WHEN ' | | when_condition_col | | ' THEN ''' | | then_condition_col | | '''' as case_when FROM some_table]';
execute immediate case_when_logic BULK COLLECT INTO case_when_logic_tbl;
plsql_block := q'[SELECT CASE :case_when
ELSE 'some other value'
END case_when_col,
table2.*
FROM table2]';
EXECUTE IMMEDIATE plsql_block USING case_when_logic_tbl;
END;
/
但不幸的是，这引发了一个错误：
PLS-00457: expressions have to be of SQL types
有办法做到这一点吗？请提供一个代码示例，因为我还没有看到类似的解决方案。
USING子句采用位置表示法(docs)中的绑定变量。所以不能传递关联数组。并且您只能传递有效的绑定变量(在这里解释)。没有表名、子句等
您可以将select语句连接起来，然后执行以下操作：
DECLARE
case_when_logic VARCHAR2(4000 byte);
plsql_block VARCHAR2(4000 byte);
TYPE t_emp_tab IS TABLE OF emp.ename%TYPE;
l_ename_tab t_emp_tab;
BEGIN
case_when_logic := q'[WHEN ENAME = 'KING' THEN 'BOSS' ELSE 'NOBODY']';
plsql_block := q'[SELECT
CASE ]'||case_when_logic||q'[
END
FROM
emp WHERE ename = :name]';
EXECUTE IMMEDIATE plsql_block BULK COLLECT INTO l_ename_tab USING 'KING';
FOR r IN 1 .. l_ename_tab.COUNT LOOP
dbms_output.put_line(l_ename_tab(r));
END LOOP;
END;
/
如果您必须循环通过一个数字，如果WHEN x THEN y子句，则会影响您的语句。将语句传递给EXECUTE IMMEDIATE时，该语句必须完整
DECLARE
plsql_block VARCHAR2(4000 byte);
TYPE vc_tab IS TABLE OF VARCHAR2(4000);
l_case_when_logic_tab vc_tab;
TYPE t_emp_tab IS TABLE OF emp.ename%TYPE;
l_ename_tab t_emp_tab;
BEGIN
l_case_when_logic_tab := vc_tab
(q'[WHEN JOB = 'PRESIDENT' THEN 'BOSS' ]',
q'[WHEN JOB = 'MANAGER' THEN 'TRYHARD' ]',
q'[WHEN JOB = 'SALESMAN' THEN 'STORYTELLER' ]'
);
plsql_block := q'[SELECT CASE ]';
FOR i IN l_case_when_logic_tab.FIRST .. l_case_when_logic_tab.LAST LOOP
plsql_block := plsql_block || l_case_when_logic_tab(i);
END LOOP;
plsql_block := plsql_block ||q'[
END
FROM
emp WHERE ename = :name]';
dbms_output.put_line('statement: ');
dbms_output.put_line(plsql_block);
EXECUTE IMMEDIATE plsql_block BULK COLLECT INTO l_ename_tab USING 'KING';
FOR r IN 1 .. l_ename_tab.COUNT LOOP
dbms_output.put_line(l_ename_tab(r));
END LOOP;
END;
/