oracle中动态SQL的使用

1.查询出一行记录动态SQL

create or replace procedure proc_insert 
(
id number
)
as 
str_sql varchar2(500);
mysal number;
begin 
  str_sql:='select sal from emp where empno = :1';
  execute immediate str_sql into mysal using id; 
  dbms_output.put_line('结果是：'||mysal);
  exception 
    when others then 
      dbms_output.put_line(SQLERRM);
end ;
/
exec proc_insert(7566);

2.上面的代码将查询出来的结果存储在mysal里面，用id作为变量传入第1个参数

---

如果是添加，修改，删除，中间部分修改为

create or replace procedure proc_insert 
(
id number,name varchar2(20)
)
 str_sql:='insert into emp(empno,ename) values(:1,:2)';
  execute immediate str_sql using id,name; 

---

如果查询多行，比如分页，要用游标 ,先看如果查询一行怎么写，因为分页中有rownum这一列，所以查询时一定先写好除rownum的其它列，否则类型不一样，会报错。

create or replace procedure proc_insert 
as 
str_sql varchar2(500);
empinfo emp%rowtype;
begin 
  str_sql:='select t.empno,t.ename,t.job,t.mgr,t.hiredate,t.sal,t.comm,t.deptno from (select rownum r,e.* from (select * from emp order by sal) e where rownum<=3) t where r>=3';
  execute immediate str_sql into empinfo; 
  dbms_output.put_line('结果是：'||empinfo.ename);  
end ;
/
exec proc_insert();