案例2

说明如何在特定的情况下，将动态SQL修改为静态SQL。

点击(此处)折叠或打开

CREATE OR REPLACE PROCEDURE proc1(p_cname VARCHAR2, p_empno NUMBER)
IS
v_value VARCHAR2(100);
BEGIN
--使用动态SQL
EXECUTE IMMEDIATE \'SELECT \' || p_cname || \' FROM emp WHERE empno = :x\' INTO v_value USING p_empno;
dbms_output.put_line(\'Dynamic SQL: \' || v_value);
--修改为静态SQL
SELECT DECODE(UPPER(p_cname), \'ENAME\', ename, \'EMPNO\', empno, \'HIREDATE\', to_char(hiredate, \'YYYY-MM-DD\'))
INTO v_value FROM emp WHERE empno = p_empno;
dbms_output.put_line(\'Static SQL: \' || v_value);
END;
/
set serveroutput on
exec proc1(\'hiredate\', 7788);
Dynamic SQL: 19-APR-87
Static SQL: 1987-04-19
exec proc1(\'ename\', 7788);
Dynamic SQL: SCOTT
Static SQL: SCOTT

ITPub博客

避免动态SQL(二)