动态SQL——PL/SQL教程（五）

动态sql

/*
它是指在PL/SQL程序执行时生成的SQL语句，在编译过程中对它不做处理。
而是在程序运行时动态构造语句、对语句进行语法分析并执行。

我们之前在PL/SQL中只能使用DML、DQL。不能直接使用DDL;
但是我们可以通过动态SQL来执行DDL语句命令。

格式：
execute immediate 字符串参数 [into] 变量 using 参数
*/

-- 按照员工编号查询员工的信息
declare
	v_emp emp%rowtype;
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
    execute immediate 'select * from emp where empno=7369' into v_emp;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;



declare
	v_emp emp%rowtype;
	v_sql varchar2(100);
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
	v_sql:='select * into v_emp from emp where empno=7369';
    execute immediate v_sql into v_emp;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;


declare
	v_emp emp%rowtype;
	v_sql varchar2(100);
begin
/*
	select * into v_emp from emp where empno=7369;
	dbms_output.put_line(v_emp.empno||','||v_emp.ename);
*/ 
	v_sql:='select * into v_emp from emp where empno=:a and deptno=:b';
    execute immediate v_sql into v_emp using 7369,20;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);		
end;







--执行DDL语句

-- 创建test001表
begin
  execute immediate 'create table test001(id number(2),name varchar2(10))';
end;


declare
	stmt varchar2(200):='create table test002(id number(2),name varchar2(10))';
begin
  execute immediate stmt;
end;
总结：PLSQL块中不能直接执行DDL语句，所以可以用动态sql去执行
-- excute immediate sql语句

--案例1: 编写一个plsql块，往test001中插入一条数据，要求从键盘输入
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type:=&name;
begin
  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;
end;
--案例2：编写一个pl/sql块，往test001中插入一条记录，要求值是从键盘输入
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type:=&name;
begin
  execute immediate 'insert into test001 values(:1,:2)' using v_id,v_name;
end;
总结：execute immediate sql语句 using 值1，值2，…;
:1,:2：需要用到变量的地方用:1,:2来代替

-- 案例3：查询test001，从键盘接收id，输出其姓名
declare
    v_id test001.id%type:=&id;
    v_name test001.name%type;
begin
  execute immediate 'select name from test001 where id=:1' into v_name using v_id;
  dbms_output.put_line('姓名是：'||v_name);
end;
 总结：execute immediate sql语句 into 变量1 using 变量2;
(1)sql语句中没有select...into...
(2)where id=:1:1是占位符，表示这个地方在运行的时候需要有一个值替代

--案例4：从键盘输入一个员工编号，查询该员工的姓名和工资，如果工资小于2000元，那么给他增加500，返回增加后的工资。

declare
    v_empno emp.empno%type:=&no;
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
begin
  select ename,sal into v_ename,v_sal from emp where empno=v_empno;
  if v_sal<2000 then
    execute immediate 'update emp set sal=sal+500 where empno=:1 returning sal into :2' using v_empno returning into v_sal;
  end if;
  dbms_output.put_line('新工资是：'||v_Sal);
end;
总结：execute immediate sql语句 using 变量1 returning into 变量2;
update emp set sal=sal+500 where empno=:1 returning sal into :2 意思是更新sal,返回更新后的工资。

--案例5：从键盘接受一个员工编号，删除该员工信息
declare
	v_empno emp.empno%type:=&no;
begin
  execute immediate 'delete from emp where empno=:num' using v_empno;
end;