执行动态SQL

# 执行动态SQL

```SQL
--insert,update,delete
execute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

--select
execute immediate 'select count(*) from user_tables where table_name = upper(''emp'')' into v_cnt;

--动态sql执行plsql代码块
--创建存储过程供动态sql执行
CREATE OR REPLACE PROCEDURE pro_create_dept (
  deptid IN OUT NUMBER,
  dname  IN     VARCHAR2,
  loc  IN     VARCHAR2
) AS
BEGIN
  INSERT INTO dept (
    deptno,
    dname,
    loc)
  VALUES (deptid, dname, loc);
  COMMIT;
END;

DECLARE
  new_deptid  NUMBER(4) := 99;
  new_dname   VARCHAR2(30) := 'Ad';
  new_loc   VARCHAR2(100)    := 'loc';
BEGIN
  --绑定变量有 in/out
  EXECUTE IMMEDIATE 'BEGIN pro_create_dept(:deptno, :dname, :loc); END;'
    USING IN OUT new_deptid, IN new_dname, IN new_loc;    --指定in/out，默认为 in，如果为 in可省略不写
      --写成这样也可以 ...,IN 'location'   直接用常量
  dbms_output.put_line(new_deptid);
END;

DECLARE
  a_null  CHAR(1);  --未初始化
BEGIN
  EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
    USING a_null;  --未初始化的变量为NULL
END;

--ref cursor/sys_cursor
open v_cursor for 'select * from emp where deptno = :deptno' using v_deptno;
```