--3 使用带参数的静态游标查询某工资区间的员工信息(最高工资,最低工资是参数)
declare
va_emp emp%rowtype;
va_min emp.sal%type:=&最低工资;
va_max emp.sal%type:=&最高工资;
cursor va_empcur(va_mingsal emp.sal%type,va_maxsal emp.sal%type) is select * from emp where sal>va_mingsal and sal<va_maxsal;
begin
open va_empcur(va_min,va_max);
loop
fetch va_empcur into va_emp;
exit when va_empcur%notfound;
dbms_output.put_line(va_emp.empno ||va_emp.ename);
end loop;
close va_empcur;
end;
--4 用动态游标结合动态sql查询某部门的员工信息
declare
v_deptno emp.empno%type:=&输入编号;
empinfo2 emp%rowtype;
type ref_cursor is ref cursor;
deptno_cursor ref_cursor;
v_str varchar(100):='select * from emp where deptno =:deptxx';
begin
open deptno_cursor for v_str using v_deptno;
loop
fetch deptno_cursor into empinfo2;
exit when deptno_cursor%notfound;
dbms_output.put_line(empinfo2.empno ||empinfo2.ename);
end loop;
close deptno_cursor;
end;
--5 创建一个存储过程可以查看某订单的商品个数和总价
create or replace procedure selectinfo(ordid in number)
is
begin
select sum(es_orderdetail.count),sum(price)
from es_orderdetail,es_order
where es_orderdetail.order_id=es_order.id
and es_order.id=ordid;
end;
--6 创建一个存储过程可以看到某用户下的所有订单信息(传出游标)
create or replace procedure selectorderinfo(
usid number,
ord_pro out sys_refcursor
)
begin
open ord_pro for select * from es_order where user_id=usid;
--谁调用量谁要关闭它
end;
--调用
declare
ord_proxx sys_refcursor;
va_order es_order%rowtype;
begin
selectorderinfo(1,ord_proxx);
loop
fetch ord_proxx into va_order;
exit when ord_proxx%notfound ;
dbms_output.put_line(va_order.id );
end loop;
end;
---7 创建一个存储过程可以给一组员工加工资(传入员工游标)
create or replace procedure upd_sal(
ord_pro sys_refcursor
va_order emo.empno%type;
)
is
eid emp.empno%type;
begin
open uord_pro into eid;
loop
fetch ord_pro into eid;
exit when ord_pro%notfound
update emp set sal=sal+sal*0.8 where empno=eid;
end loop;
end;
--1:创建一个对雇员表分页查询的存储过程,包含两个输入参数PAGEINDEX(页码),PAGESIZE(每页的记录)。
--和两个输出参数TOTALPAGE(总页数)、PAGERESULTSET(存放结果的游标)
--2:调用该分页存储过程
--select a.ename,a.job from (select e.*,rownum r from emp e) a where a.r>2 and a.r<10;
--SELECT * FROM ( SELECT emp.*, ROWNUM RN FROM emp)a WHERE a.rn <=2 and a.rn >= 0
create or replace procedure fenyeemp(
PAGEINDEX number,
PAGESIZE number,
TOTALPAGE out number,
PAGERESULTSET out sys_refcursor,
errorcode out varchar2,
errorinfo out varchar2
)
is
rowss number(5);
page_exp exception;
begin
select count(0) into rowss from emp;
TOTALPAGE:=ceil(rowss/ PAGESIZE);
if PAGEINDEX<1 or PAGEINDEX>TOTALPAGE then
raise page_exp;
end if;
open PAGERESULTSET for select a.ename,a.job from (select e.*,rownum r from emp e) a where a.r>(PAGEINDEX-1)*PAGESIZE and a.r<PAGESIZE*PAGEINDEX+1;
errorcode:='0';
errorinfo:='cg';
exception
when page_exp then
errorcode:='1';
errorinfo:='sb';
when others then
errorcode:='2';
errorinfo:='qt';
end;
--2:调用该分页存储过程
declare
page number(10):=&请输入页码;
pagesize number(10):=&请输入页码条数;
page_yb sys_refcursor;
rowss number(10);
emp_row emp%rowtype;
ename emp.ename%type;
jobs emp.job%type;
begin
fenyeemp(page,pagesize,rowss,page_yb);
loop
fetch page_yb into ename,jobs;
exit when page_yb%notfound;
dbms_output.put_line(ename||'--'||jobs);
end loop;
end;