首先得定义一个包,也就是package
- 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
- 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语句的写法一样。
转载于:https://blog.51cto.com/haveonce/1169595