存储过程解答（一）

存储过程解答（1）
    最近学习了下存储过程，在网上搜索到一些关于存储过程的题目，并尝试做出了解答，下面将这些解答写下来有两个目的：
1.方便自己以后查阅和体会。
2.希望某些高手能指出解答不够好的地方加以指正，共同探讨进步
不定期更新中。下面开始

1.有一张公司员工表MEMBER，有2个字段ID（唯一标示），core_id(员工工号)，由于设计的失误导致很多员工的员工编号重复了，请写出一段存储过程来解决这个问题。
解答：

create or replace procedure update_core_id(message out varchar2) is
e_id member.id%type;
e_sql varchar2(1000);

cursor cs_id 
is 
select id from member s where s.core_id in(select t.core_id from member t group by t.core_id having count(t.core_id)>1);

begin
    open cs_id;
    loop
        fetch cs_id into e_id;
        exit when cs_id%Notfound;
        e_sql:='update member s set s.core_id=(select max(core_id)+1 from member) where s.id='||e_id; 
        DBMS_OUTPUT.put_line(e_id);
        execute immediate e_sql;
    end loop;
    commit;
    message:='已经输出SQL语句,并执行了对应的SQL,请检查是否正确';
    close cs_id;
    Exception
             when others then
             message:='出现异常了';
             rollback;           
end update_core_id;

2.创建一个存储过程P1，要求执行改存储过程能够返回考生数据表(tblkaosheng) 
中bmdwdm（报名单位代码）为100,130,500,830,2920,2490的考生的zkzh（准考证号）,（xm）姓名和kscj（考试成绩）结果集，并且该结果集要按照bmdwdm（报名单位代码） 
和准考证号升序排列
解答：
2种方式实现：
A.使用sys_refcursor

create or replace procedure GET_XS_KSQK(resultXS out sys_refcursor) is
begin
open resultXS for
select tl.zkzh,tl.xm,kscj from tblkaosheng tl
       where tl.bmdwdm = '100' or tl.bmdwdm='130' or
             tl.bmdwdm = '500' or tl.bmdwdm='830' or
             tl.bmdwdm = '2920' or tl.bmdwdm='2490' 
       order by tl.bmdwdm,tl.zkzh;
end GET_XS_KSQK;

B.package中定义游标

create or replace package ref_xs is
  type ref_xs_cursor is ref cursor;
end ref_xs;

create or replace procedure GET_XS_KSQK_PACK(resultxs out ref_xs.ref_xs_cursor) is
begin
  open resultxs for
  select tl.zkzh,tl.xm,kscj from tblkaosheng tl
       where tl.bmdwdm = '100' or tl.bmdwdm='130' or
             tl.bmdwdm = '500' or tl.bmdwdm='830' or
             tl.bmdwdm = '2920' or tl.bmdwdm='2490' 
       order by tl.bmdwdm,tl.zkzh;
end GET_XS_KSQK_PACK;

另外该题目还可以用定义function来实现，也可以将procedure或function写在packagebody中来实现