存储过程解答（一）

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

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

create or replace procedure update_core_id(message out varchar2) ise_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) isbeginopen resultXS forselect 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) isbegin  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中来实现

查看更多 下一篇