oracle（pl/sql）包、存储过程的增、删、查、改等操作的实现（源代码）

--创建存储过程
create or replace package pkg_rights is
       -- Author : Keleesy
       -- Created : 2011-10-12
type ResultData is ref cursor;
  --添加菜单
procedure addMenu(
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type,
  p_mid out number
);
  --更新菜单
procedure updateMenu(
  p_mid rights_menu_data.mid%type,
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_del rights_menu_data.del%type,
  p_release rights_menu_data.release%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type
);

  --按id(通用)查询
procedure findDataByid(entity nvarchar2,mid number,p_OutCursor out ResultData);

  --(通用)根据实体名，实体id名,实体id值和del值进行删除或恢复数据
procedure delreByid(entity nvarchar2,mid number,del int);

  --(通用)分页查询
procedure sp_Page(
  p_PageSize int,--每页记录数
  p_PageNo int, --当前页码,从 1 开始                 
  p_SqlSelect varchar2,    --查询语句,含排序部分                 
  p_SqlCount varchar2,     --获取记录总数的查询语句                 
  p_OutRecordCount out int,--返回总记录数                 
  p_OutCursor out ResultData
);
end pkg_rights;

/

 

create or replace package body pkg_rights is
  --添加菜单
procedure addMenu(
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type,
  p_mid out number
)as
begin
  select rights_menu_id_seq.nextval into p_mid from dual;
  insert into rights_menu_data values (p_mid,p_mid,p_pid,p_name,p_layout,p_src,p_mgrsrc,1,1);
end addMenu;
  --更新菜单

procedure updateMenu(
  p_mid rights_menu_data.mid%type,
  p_pid rights_menu_data.pid%type,
  p_name rights_menu_data.name%type,
  p_layout rights_menu_data.layout%type,
  p_del rights_menu_data.del%type,
  p_release rights_menu_data.release%type,
  p_src rights_menu_data.src%type,
  p_mgrsrc rights_menu_data.mgrsrc%type
)as
begin
  update rights_menu_data m set m.pid=p_pid,m.name=p_name,m.layout=p_layout,m.del=p_del,m.release=p_release,m.src=p_src,m.mgrsrc=p_mgrsrc where m.mid=p_mid;
end updateMenu;
  --根据实体名,id和del值进行删除或恢复数据
procedure delreByid(entity nvarchar2,mid number,del int)is
  v_sql varchar2(1000);
  begin
    v_sql:='update '||entity||' set del ='|| del ||'where mid ='||mid;
    execute immediate v_sql;
end delreByid;
  --按id通用查询
procedure findDataByid(entity nvarchar2,mid number,p_OutCursor out ResultData)as
v_sql varchar2(1000);
begin
  v_sql:='select * from '||entity||' where mid = '||mid||' and del=1';
  open p_OutCursor for v_sql;   
end findDataByid;

  --通用分页查询
procedure sp_Page(
  p_PageSize int,--每页记录数
  p_PageNo int, --当前页码,从 1 开始                 
  p_SqlSelect varchar2,    --查询语句,含排序部分                 
  p_SqlCount varchar2,     --获取记录总数的查询语句                 
  p_OutRecordCount out int,--返回总记录数                 
  p_OutCursor out ResultData
) as     
v_count int;     
v_heiRownum int;     
v_lowRownum int;
v_sql varchar2(3000);  
begin    ----取记录总数   
execute immediate p_SqlCount into v_count;   
p_OutRecordCount := v_count;    ----执行分页查询   
v_heiRownum := p_PageNo * p_PageSize;  --第p_PageNo页的最后一条记录
v_lowRownum := v_heiRownum - p_PageSize +1;  --第p_PageNo页的第一条记录 
v_sql := 'SELECT B.* FROM (SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A ) B WHERE B.rn <= '|| v_heiRownum ||' and B.rn >= ' || v_lowRownum;   
OPEN p_OutCursor FOR  v_sql; 
end sp_Page;
end pkg_rights;