oracle开发中的存储过程,游标,if判断经典例子

、、、

存储过程由浅到深:

1.简单输出
create or replace procedure t_xuanze(name in varchar)
as
tname varchar(10);
begin
dbms_output.put_line('this is error!');
end;
（注意:1.没有declare;2.形参上没有类型范围）
///
2.形参赋值输出
create or replace procedure t_xuanze(name in varchar)
as
tname varchar(10):=name;
begin
dbms_output.put_line(tname);
end;
/
注:在as下tname类型为varchar(10)，含有范围

3.带有if判断的存储过程
create or replace procedure t_xuanze(name in varchar)
as
tname varchar(10):=name;
begin
if(tname='emp') then
dbms_output.put_line('this is emp!');
elsif(tname='dept') then
dbms_output.put_line('this is dept!');
else
dbms_output.put_line('this is error!');
end if;
end;
/
4.存储过程中套游标打印多行记录问题
create or replace procedure t_xuanze(name in varchar)
as
tname varchar(10):=name;
begin
if(tname='emp') then
declare
cursor cc is select * from emp where sal>(select avg(sal) from emp where 


deptno=10);
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'--'||ccrec.ename||'--'||


ccrec.job||'--'||ccrec.sal);
end loop;
close cc;
end;
elsif(tname='dept') then
dbms_output.put_line('this is dept!');
else
dbms_output.put_line('this is error!');
end if;
end;
//
5.存储过程中使用if条件判断进行游标打印多行记录
create or replace procedure t_xuanze(name in varchar)
as
tname varchar(10):=name;
begin
if(tname='emp') then
declare
cursor cc is select * from emp where sal>(select avg(sal) from emp where 


deptno=10);
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'--'||ccrec.ename||'--'||


ccrec.job||'--'||ccrec.sal);
end loop;
close cc;
end;
elsif(tname='dept') then
declare
cursor cc is select b.dname,count(a.empno) as zongshu from emp a,dept b 


where a.deptno=b.deptno group by b.dname;
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.dname||'----'||ccrec.zongshu);
end loop;
close cc;
end;
else
dbms_output.put_line('this is error!');
end if;
end; 
6.存储过程中使用传参后游标查询多行记录显示
create or replace procedure t_chaxun(dno number)
as
num1 number(10):=dno;
begin
declare
cursor cc is select * from emp where deptno=num1;
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'---'||ccrec.empno||'---'||


ccrec.sal||'---'||ccrec.deptno);
end loop;
end;
end;

2.游标由浅到深

1.查询工资大于10号部门平均工资的职工信息
declare
cursor cc is select * from emp where sal>(select avg(sal) from emp where deptno=10);
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.empno||'--'||ccrec.ename||'--'||ccrec.job||'--'||ccrec.sal);
end loop;
close cc;
end;
2.查询各个部门的人数
declare
cursor cc is select b.dname,count(a.empno) as zongshu from emp a,dept b where a.deptno=b.deptno group by b.dname;
ccrec cc%rowtype;
begin
open cc;
loop
fetch cc into ccrec;
exit when cc%notfound;
dbms_output.put_line(ccrec.dname||'----'||ccrec.zongshu);
end loop;
close cc;
end;