动态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;