Oracle学习笔记(6) 动态SQL

这是第六章的学习笔记，学习完第五章的游标之后，开始要学习动态SQL的使用了…… 
    编程时使用的工具是PLSQL Developer 7.1.4 

动态SQL: 
注意：不能用绑定变量替换实际的数据库对象名(表、视图、列等)，只能替换字面量 
动态SQL的实现可以用：dbms_sql、execute immediate、open...for...(用于游标) 

Sql代码 

1. declare  
2.    type emp_table_type is table of employee%rowtype index by binary_integer;  
3.    emp_table emp_table_type;  
4.    emps employee%rowtype;  
5.    v_sql varchar2(1000);  
6.    v_name employee.name%type;  
7.    n_salary employee.salary%type;  
8.    type emp_cur_type is ref cursor;  
9.    emp_cur emp_cur_type;  
10. begin  

在PL/SQl程序块中执行DDL语句，在execute immediate后面直接接SQL字符串 
如果要对单引号进行转意，只要用两个单引号连在一起就可以了(不是双引号) 

Sql代码 

1. v_sql := 'insert into Employee values(66,''xxx'',''yyy'',0,to_char(sysdate,''yyyy-MM-dd hh24:mm:ss''),9999,''zzz'',66)';  
2. execute immediate v_sql;  
3. -- 通过绑定变量来执行DDL语句，在SQL语句中使用 : 号来指定要动态替换的数据  
4. v_sql := 'insert into Employee values(:empno,''xxx'',''yyy'',0,to_char(sysdate,''yyyy-MM-dd hh24:mm:ss''),9999,''zzz'',66)';  
5. execute immediate v_sql using &要插入的员工编号：;  

用returning 返回execute immediate 执行后影响的列 
将返回的信息传递给两个绑定变量占位符(:1,:2)，注意占位符可以使用任意名称，我们可以对应的在immediate中使用returning接收绑定变量执行后的结果值 

Sql代码 

1. v_sql := 'update employee set salary=:salary where empno=:empno returning name,salary into :1,:2';  
2. execute immediate v_sql using '&员工工资更改为：',&员工编号 returning into v_name,n_salary;  
3. dbms_output.put_line(v_name || ' 的工资更改为 ' || n_salary);  
4. -- 使用Select返回隐式游标的结果集  
5. v_sql := 'select * from employee where empno=:empno';  

因为Select语句本来就会返回值，所以在execute immediate中就不再需要写Returning语句了,记忆的方法是如果在SQL字符串中含有Returning，那么在execute immediate就需要写Returning，反之就不需要写了 
要注意的是在execute immediate子句中的using必须写在最后面 

Sql代码 

1.    execute immediate v_sql bulk collect into emp_table using &要查询的员工编号1：;  
2.    for i in 1..emp_table.count loop  
3.        dbms_output.put_line(emp_table(i).name || '当前的工资是 ' || emp_table(i).salary || ',他负责的工作是 ' || emp_table(i).job);  
4.    end loop;  
5.    -- 使用Select返回游标变量的结果集(open...for...)  
6.    v_sql := 'select * from employee where empno=:empno';  
7.    open emp_cur for v_sql using &要查询的员工编号2：;  
8.    loop  
9.       fetch emp_cur into emps;  
10.       exit when emp_cur%notfound;  
11.       dbms_output.put_line(emps.name || '加入公司的时间是 ' || emps.hiredate);  
12.    end loop;  
13. end;  
14. /