1.txt

利用表tab_student在PL/SQL中编写一过程

5、	利用表tab_student在PL/SQL中编写一过程，同时利用游标取出指定条件的学生信息输出，要求：
1）	条件可能是学号、姓名或年龄中的一个，其中对于学号和姓名应该支持模糊查询，对于年龄搜索比输入值小的所有记录；
2）	用包来编写，其中应有一个过程或函数用来判断用户选择的是什么条件

SQL> create table tab_student
  2  (sno varchar2(8),
  3  sname varchar2(20),
4	age integer);
 insert into tab_student  values('001','huang',20);
SQL> create or replace package test_package
  2  is
  3  procedure show_out(v_sname tab_student.sname%type);
  4  procedure proc_selectby_sno(v_sno tab_student.sno%type);
  5  procedure proc_selectby_sname(v_sname tab_student.sname%type);
  6  procedure proc_selectby_age(v_age tab_student.age%type);
  7  procedure proc_select(index_value integer,canshu varchar2);
end test_package;
------------------------------------------------------
create or replace package body test_package
as
procedure show_out(v_sname tab_student.sname%type)
as
begin
dbms_output.put_line(v_sname);
end show_out;
--begin select by sno
procedure proc_selectby_sno(v_sno tab_student.sno%type)
as
sno tab_student.sno%type;
v_sname tab_student.sname%type;
cursor mycursor(var_sno tab_student.sno%type) is select sname from tab_student where sno like var_sno;
begin
sno:=v_sno||'%';
open mycursor(sno);
loop
fetch mycursor into v_sname;
exit when mycursor%notfound;
show_out(v_sname);
end loop;
close mycursor;
end proc_selectby_sno;
--end select by sno
--begin select by sname
procedure proc_selectby_sname(v_sname tab_student.sname%type)
as
sname tab_student.sname%type;
v1_sname tab_student.sname%type;
cursor mycursor(var_sname tab_student.sname%type) is select sname from tab_student where sname like var_sname;
begin
sname:=v_sname||'%';
open mycursor(sname);
loop
fetch mycursor into v1_sname;
exit when mycursor%notfound;
show_out(v1_sname);
end loop;
close mycursor;
end proc_selectby_sname;
-- end select by sname
--begin select by age
procedure proc_selectby_age(v_age tab_student.age%type)
as
--sno tab_student.sno%type;
v_sname tab_student.sname%type;
cursor mycursor(var_age tab_student.age%type) is select sname from tab_student where age>=var_age;
begin
open mycursor(v_age);
loop
fetch mycursor into v_sname;
exit when mycursor%notfound;
show_out(v_sname);
end loop;
close mycursor;
end proc_selectby_age;
--end select by age
--begin select
procedure proc_select(index_value integer,canshu varchar2)
as
begin
case index_value
when 1 then
proc_selectby_sno(canshu);
when 2 then
proc_selectby_sname(canshu);
when 3 then
proc_selectby_age(to_number(canshu));
end case;
end proc_select;
end test_package;