oracle学习记录之十八

游标

declare 

type sp_emp_cursor is ref cursor;

test_cursor sp_emp_cursor;

begin

open test_cursor for select ename, sal from emp where deptno=&no;

end;

declare 

type sp_emp_cursor is ref cursor;

test_cursor sp_emp_cursor;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

open test_cursor for select ename, sal from emp where deptno=&no;

loop

fetch test_cursor into v_ename, v_sal;

exit when test_cursor%notfound;

dbms_output.put_line('姓名：'||v_ename||'薪水：'||v_sal);

end loop;

end;

create or replace procedure mypro1(name varchar2) is 

v_sal test.sal%type;

begin

select sal into v_sal from test where ename=name;

if v_sal<2000 then

update test set sal=sal+sal*0.1 where ename=name;

end if;

end;

create or replace procedure mypro2(name varchar2) is

v_comm emp.comm%type;

begin

select comm into v_comm from emp where ename=name;

if v_comm<>0 then

update emp set comm=comm+100 where ename=name;

else 

update emp set comm=comm+200 where ename=name;

end if;

end;

create or replace procedure mypro3(no number) is

v_job emp.job%type;

begin

select job into v_job from emp where empno=no;

if v_job='PRESIDENT' then

update emp set sal=sal+100 where empno=no;

elseif v_job='MANAGER' then

update emp set sal=sal+200 where empno=no;

else 

update emp set sal=sal+300 where empno=no;

end if;

end;

create or replace procedure mypro2(dno number) is
type cc is ref cursor;
aa cc;
v_ename emp.ename%type;
begin 
open aa for select ename from emp where deptno=dno;
loop
fetch aa into v_ename;
exit when aa%notfound;
dbms_output.put_line(v_ename);
end loop;
end;
/

create or replace function fun1(dno number) return number is renshu number(2);

begin

select count(*) into renshu from emp where deptno=dno;

dbms_output.put_line(renshu);

return renshu;  --函数必须有返回值

end;

create table user2(userno number(10), username varchar2(10));

create or replace procedure mypro11(name varchar2) is
v_num number:=1;
begin
loop
insert into user2(userno, username) values(v_num, name);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;
/

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

create or replace procedure mypro44(no number, p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=no;
end;
/

create or replace procedure mypro33(name varchar2) is

v_num number:=11;

begin

while v_num<=20 loop

insert into user2(userno, username) values(v_num, name);

v_num:=v_num+1;

end loop;

end;

/

--待检

create or replace procedure fenye

(tablename varchar2, pagesize number, myrows out number, mypagecount out number, p_cursor out testpackage.test_cursor) is

v_sql varchar2(1000);

v_begin number:=(pagenow-1)*pagesize+1;

v_end number:=pagenow*pagesize;

begin

v_sql:=' select * from (select a1.*, rownum rn from (select * from '||tablename||' ) a1 where rownum<'||?||') where rn>'||?;

end;

declare
i number:=1;
begin
loop
dbms_output.put_line(i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('end_loop');
end;
/