在 PL/SQL 块中,可以使用 DDL 语句和事物控制语句,但还有很多语句不能直接在 PL/SQL 中运行,比如 DDL,这些语句可以使用动态 SQL 来执行。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
动态 SQL 的语法格式
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]
代码演示
declare
sql_stmt varchar2(200);
emp_id number(4):=7566;
salary number(7,2);
dept_id number(2):=90;
dept_name varchar2(14):='PERSONNEL';
loc varchar2(13):='DALLS';
emp_rec emp%ROWTYPE;
begin
--执行无字句的动态 SQL
execute immediate 'CREATE table tttt(id number,name varchar2(10))';
--参数名必须以冒号开头,名字随便写,但不能重复
sql_stmt:='insert into dept values(:1,:2,:3)';
execute immediate sql_stmt using dept_id,dept_name,loc;
sql_stmt:='select * from emp where empno = :i';
execute immediate sql_stmt into emp_rec using emp_id;
sql_stmt:='update emp set sal = 2000 where empno=:1 returning sal into :2';
execute immediate sql_stmt using emp_id returning into salary;
execute immediate 'delete from dept where deptno = :n' using dept_id;
end;