oracle 的存储过程写法

mianfeis

首先得定义一个包，也就是package

• [Shell] 纯文本查看 复制代码

  create or replace package PKG_SC_STUDY is --创建包头  
    type t_cur is ref cursor;               --定义一个cursor  
   
    --得到敏感词列表  
    procedure listFilterWord (   
                          p_strWhere in varchar2,  
                          p_pageSize in number,  
                          p_pageNow in number,  
                          c_cur out t_cur);  
                            
    --得到敏感词列表总数  
  procedure countFilterWord (   
                          p_strWhere in varchar2,  
                          totalCount out number);  
                            
   --根据条件得到相关信息  
  procedure getDetail( p_strWhere in varchar2,  
                          c_cur out t_cur);  
                            
  --添加敏感词  
  procedure addFilterWord ( p_fname in sc_stu_filterword.fname%type,  
                            p_remarks in sc_stu_filterword.remarks%type,  
                            p_adduser in sc_stu_filterword.adduser%type,  
                            p_grade in sc_stu_filterword.grade%type);  
   
  --根据id修改信息  
  procedure updateFilterWord ( p_id in sc_stu_filterword.id%type,  
                            p_fname in sc_stu_filterword.fname%type,  
                            p_remarks in sc_stu_filterword.remarks%type,  
                            p_grade in sc_stu_filterword.grade%type);  
                              
  --根据id启用/禁用敏感词  
  procedure enabledFilterWord ( p_id in sc_stu_filterword.id%type,  
                                p_isenable in sc_stu_filterword.isenable%type);  
   
  --根据id删除敏感词  
  procedure deletedFilterWord ( p_id in sc_stu_filterword.id%type);  
      
  end PKG_SC_STUDY; 

  
  

接着定义一个包体，也就是package bodies

• [Shell] 纯文本查看 复制代码

  create or replace package body PKG_SC_STUDY is 
   
    --得到敏感词详细表列表  
  procedure listFilterWord (   
                          p_strWhere in varchar2,  
                          p_pageSize in number,  
                          p_pageNow in number,  
                          c_cur out t_cur) is 
      
  v_sql varchar(4000);  
  begin 
    v_sql := 'select t.id,t.fname,t.remarks,t.adduser,b.name,t.adddate,t.isenable,t.grade   
      from sc_stu_filterword t,sc_base_users b where t.adduser = b.userid '||p_strWhere;   
   
    v_sql:= 'select * from (select a.*,rownum from ('||v_sql||') a where rownum <='||(p_pageNow*p_pageSize)||')  
      where rownum >'||((p_pageNow-1)*p_pageSize);  
      
    open c_cur for v_sql;  
  end listFilterWord;    
      
   
  --得到敏感词列表总数  
  procedure countFilterWord (   
                          p_strWhere in varchar2,  
                          totalCount out number) is 
      
  v_sql varchar(4000);  
  begin 
    v_sql := 'select count(t.id) from sc_stu_filterword t,sc_base_users b where t.adduser = b.userid  '||p_strWhere;  
    EXECUTE IMMEDIATE v_sql into totalCount;  
  end countFilterWord;   
   
  --根据条件得到相关信息  
  procedure getDetail( p_strWhere in varchar2,  
                          c_cur out t_cur) is 
      
  v_sql varchar(4000);  
  begin 
    v_sql := 'select t.id,t.fname,t.remarks,t.adduser,b.name,t.adddate,t.isenable,t.grade   
      from sc_stu_filterword t,sc_base_users b where t.adduser = b.userid '||p_strWhere;  
    open c_cur for v_sql;  
  end getDetail;  
   
  --添加敏感词  
  procedure addFilterWord ( p_fname in sc_stu_filterword.fname%type,  
                            p_remarks in sc_stu_filterword.remarks%type,  
                            p_adduser in sc_stu_filterword.adduser%type,  
                            p_grade in sc_stu_filterword.grade%type) is 
  begin 
    insert into sc_stu_filterword (id,fname,remarks,adduser,adddate,grade)   
    values (sc_stu_filterword_seq.nextval,p_fname,p_remarks,p_adduser,sysdate,p_grade);  
  end addFilterWord;  
   
  --根据id修改信息  
  procedure updateFilterWord ( p_id in sc_stu_filterword.id%type,  
                            p_fname in sc_stu_filterword.fname%type,  
                            p_remarks in sc_stu_filterword.remarks%type,  
                            p_grade in sc_stu_filterword.grade%type) is 
  begin 
    update sc_stu_filterword t set t.fname = p_fname, t.remarks = p_remarks, t.grade = p_grade where t.id = p_id;  
  end updateFilterWord;  
   
  --根据id启用/禁用敏感词  
  procedure enabledFilterWord ( p_id in sc_stu_filterword.id%type,  
                                p_isenable in sc_stu_filterword.isenable%type) is 
  begin 
    update sc_stu_filterword t set t.isenable = p_isenable where t.id = p_id;  
  end enabledFilterWord;  
   
  --根据id删除敏感词  
  procedure deletedFilterWord ( p_id in sc_stu_filterword.id%type) is 
  begin 
    delete from sc_stu_filterword t where t.id = p_id;  
  end deletedFilterWord;  
   
  end PKG_SC_STUDY;  

  
  

package跟package bodies 里面的方法要一一对应，连参数也一样，否则就会报错。

至于方法内部的方法，跟sql语句的写法一样。

果果、

支持一下:lol

lilingjie2015

解释就是掩饰，掩饰就是编故事!

bobbai

关羽五绺长髯，风度翩翩，手提青龙偃月刀，江湖人送绰号——刀郎。

心海恋歌

要是我灌水，就骂我“三个代表”没学好吧。

eddik

写的真的很不错

downmovies

男人与女人,终究也只是欲望的动物吧!真的可以因为爱而结合吗?对不起,我也不知道。。