Oracle存储过程中execute immediate使用

佚名 6年前 (2019-04-16) 随笔 1277人围观抢沙发百度已收录

CREATE OR REPLACE PROCEDURE PRO_TEST() IS
  V_SQL VARCHAR2(2000) := '';
  V_NUM NUMBER;
BEGIN
  --EXECUTE IMMEDIATE用法1:立刻执行
  V_SQL := 'create or replace view mytest as select employee_id, first_name from employees';
  EXECUTE IMMEDIATE V_SQL;
  --EXECUTE IMMEDIATE用法2:立刻执行,并赋值给某变量
  V_SQL := 'select count(1) from employees';
  EXECUTE IMMEDIATE V_SQL;
  INTO V_NUM;
  --EXECUTE IMMEDIATE用法3:带参数的sql
  V_SQL := 'select * from employees e where e.employee_id = :1 and first_name = :2';
  EXECUTE IMMEDIATE V_SQL;
  USING 200, 'Jennifer';
END PRO_TEST;