oracle之动态sql、游标

动态sql

declare
--需要执行的动态sql语句：mysql
mysql varchar2(500);
emp_id number:=7566;
salary number(7,2);
dept_id number(2):=2;
dept_name varchar2(14);='PERSONNEL';
location varchar2(13):='DALLS';
emp_rec emp%rowtype;
begin
  execute immediate 'create table bonus1(id number,amt number)'
  mysql:='insert into dept values(:1,:2,:3)';
 --将声明变量通过using传入动态sql
  execute immediate mysql using dept_id,dept_name,mylocation;
 
  --有查询结果的动态sql
  mysql='select * from emp where empno=:id';
 
  --将查询结果存入emp_rec中，将emp_id作为参数传给上面的sql语句中id
  execute immediate mysql into emp_rec using emp_id;
  dbms_output.put_line(emp_rec.ename);
  end;
  

---更新数据的动态sql
  mysql:='update emp set sal=200 where empno=:1 returning sal into:2';
  --returning sal into :2将更新的sal的值返回给下面执行时的参数 returning into salary 中 salary接收
  execute immediate mysql using emp_id returning into salary;
  dbms_output.put_line(salary);
  end;
  

---删除数据的动态sql
  mysql:='delete from dept where deptno=:num';
  execute immediate mysql using dept_id;
  end;
 
 
  ---分页动态sql
  --表名动态，每页数据条数动态，总页数，总数据条数
  --变量：表名、每页长度、页码
  declare
  tablename varchar2(500):='emp';
  pagesize number:=3;
  pagenum number:=1;
  mysql varchar2(2000);
  begin
    mysql:='select * from (select rownum n,e.* from '||tablename||' e)where n between '||((pagenum-1)*pagesize+1) ||'and '||pagenum*pagesize;
    execute immediate mysql;
  end;

游标

create or replace procedure selAll_emp_proc as
cursor sel_emp is select * from emp;--定义游标,该游标指向select * from emp 查询结果
rowresult emp%rowtype;
begin
  open sel_emp;--打开游标
  loop fetch sel_emp into rowresult;--将游标中的值赋给rowresult
    exit when sel_emp%notfound;--当游标不存在时，跳出循环
    dbms_output.put_line('员工名：'||rowresult.ename||'工资：'||rowresult.sal);
  end loop;
  close sel_emp;--关闭游标
  end;