oracle 包和包体的综合应用

----声明一个游标

create or replace package tomorrow as

  type p_cursor is ref Cursor;

end;

------声明一些函数和存储过程

create or replace package itfuture is
  -----新增
  procedure save_userinfo(user_id  pmusrinf.user_id%type,
                          user_nam pmusrinf.user_nam%type);
  ------删除
  procedure delete_userinfo(user_id pmusrinf.user_id%type);
  ---修改
  procedure update_userinfo(user_id  pmusrinf.user_id%type,
                            user_nam pmusrinf.user_nam%type);
  ---查询多条记录，返回一个游标
  procedure query_userinfo(v in out tomorrow.p_cursor);
  ---查询符合条件的记录数
  function getCount(v_sql varchar2, cond varchar2) return number;
  ---分页查询，返回一个游标
  procedure query_date(v_sql    varchar2,
                       curpage  number,
                       unitpage number,
                       cond     varchar2,
                       v        in out tomorrow.p_cursor);

  ---综合分页
  function getPageDate(v_sql     varchar2,
                       cond      varchar2,
                       orderby   varchar2,
                       curpage   number,
                       unitpage  number,
                       ordertype number,
                       v_count   in out number) return tomorrow.p_cursor;
  ---返回一个记录数，输出一个游标
  function getCounts(v_sql    varchar2,
                     curpage  number,
                     unitpage number,
                     cond     varchar2,
                     v        in out tomorrow.p_cursor) return number;
  ----返回一个游标，输出一个记录数
  function getCursors(v_sql    varchar2,
                      curpage  number,
                      unitpage number,
                      cond     varchar2,
                      v_count  in out number) return tomorrow.p_cursor;
end;

-----包体

create or replace package body itfuture is
--新增
procedure save_userinfo(user_id  pmusrinf.user_id%type,
                        user_nam pmusrinf.user_nam%type) as
begin
  insert into pmusrinf values (user_id, user_nam,'','123','','','','','','','','','','');
  commit;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end save_userinfo;
--删除
procedure delete_userinfo(user_id pmusrinf.user_id%type) as
  v_sql varchar2(200);
begin
  v_sql := 'delete from pmusrinf A where A.user_id=' || user_id;
  execute immediate v_sql;
  commit;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end delete_userinfo;
--修改
procedure update_userinfo(user_id  pmusrinf.user_id%type,
                          user_nam pmusrinf.user_nam%type) as
  v_sql varchar2(300);
begin
  v_sql := 'update pmusrinf A set A.user_nam= ' || user_nam ||
           ' where A.user_id = ' || user_id;
  execute immediate v_sql;
  commit;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end update_userinfo;
---输出一个游标
procedure query_userinfo(v in out tomorrow.p_cursor) as
begin
  open v for
    select * from pmusrinf;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end query_userinfo;
-----------查询符合条件的记录数------------------
function getCount(v_sql varchar2, cond varchar2)
  return number as
  v_count number;
  all_sql varchar2(300);
begin
  if ((v_sql is not null) and (cond is not null)) then
    all_sql := 'select  count(*) from( ' || v_sql || ' where 1=1 and ' || cond || ')';
  elsif ((v_sql is not null) and (cond is null)) then
    all_sql := 'select  count(*) from( ' || v_sql || ')';
  end if;
  execute immediate all_sql
    into v_count;
  return v_count;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end getCount;
----分页查询数据
procedure query_date(v_sql    varchar2,
                     curpage  number,
                     unitpage number,
                     cond     varchar2,
                     v        in out tomorrow.p_cursor) as
  startpos number;
  endpos   number;
  all_sql  varchar2(300);
begin
  if ((curpage is not null) and (unitpage is not null)) then
    startpos := (curpage - 1) * unitpage + 1;
    endpos   := curpage * unitpage;
  end if;
  if ((v_sql is not null) and (cond is not null)) then
    all_sql := v_sql || ' where 1=1 and  ' || cond;
  elsif ((v_sql is not null) and (cond is null)) then
    all_sql := v_sql;
  end if;
  all_sql := 'select  * from (select  B.*,rownum rid from (' || all_sql ||
             ')B where rownum<=' || endpos || ')A where A.rid>=' ||
             startpos;
  open v for all_sql;
  dbms_output.put_line(all_sql);
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end query_date;

-----综合分页-------------------------
function getPageDate(v_sql     varchar2,
                     cond      varchar2,
                     orderby   varchar2,
                     curpage   number,
                     unitpage  number,
                     ordertype number,
                     v_count   in out number) return tomorrow.p_cursor as
  startpos number;
  endpos   number;
  all_sql  varchar2(2000);
  v_cursor tomorrow.p_cursor;
begin
  v_count := getCount(v_sql, cond);
  if (curpage is not null) and (unitpage is not null) then
    startpos := (curpage - 1) * unitpage + 1;
    endpos   := curpage * unitpage;
  end if;
  if (cond is not null) then
    all_sql := v_sql || ' where ' || cond;
  else
    all_sql := v_sql;
  end if;
  if (orderby is not null) then
    if (ordertype = 1) then
      all_sql := all_sql || ' order by  ' || orderby || ' asc';
    else
      all_sql := all_sql || ' order by  ' || orderby || ' desc';
    end if;
  end if;
  all_sql := 'select * from (select B.*,rownum rid from (' || all_sql ||
             ') B where rownum<=' || endpos || ')A where A.rid>=' ||
             startpos;
  execute immediate all_sql;
  dbms_output.put_line(all_sql);
  open v_cursor for all_sql;
  return v_cursor;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end getPageDate;
-------得到某一条件下的总记录数，并返回某数据的游标 --------
function getCursors(v_sql    varchar2,
                    curpage  number,
                    unitpage number,
                    cond     varchar2,
                    v_count  in out number) return tomorrow.p_cursor as
  startpos number;
  endpos   number;
  all_sql  varchar2(300);
  p_cur    tomorrow.p_cursor;
begin
  v_count := getCount(v_sql, cond);
  if ((curpage is not null) and (unitpage is not null)) then
    startpos := (curpage - 1) * unitpage + 1;
    endpos   := curpage * unitpage;
  end if;
  if ((v_sql is not null) and (cond is not null)) then
    all_sql := v_sql || ' where 1=1 and ' || cond;
  elsif ((v_sql is not null) and (cond is null)) then
    all_sql := v_sql;
  end if;
  all_sql := ' select  * from (select  B.*, rownum rid from (' || all_sql ||
             ')B where rownum<=' || endpos || ')A where A.rid>=' ||
             startpos;
  execute immediate all_sql;
  open p_cur for all_sql;
  return p_cur;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end getCursors;
-----得到某数据的游标，并返回一定条件下的记录数----------
function getCounts(v_sql    varchar2,
                   curpage  number,
                   unitpage number,
                   cond     varchar2,
                   v        in out tomorrow.p_cursor) return number as
  startpos number;
  endpos   number;
  v_count  number;
  all_sql  varchar2(300);
begin
  v_count := getCount(v_sql, cond);
  if ((curpage is not null) and (unitpage is not null)) then
    startpos := (curpage - 1) * unitpage + 1;
    endpos   := curpage * unitpage;
  end if;
  if ((v_sql is not null) and (cond is not null)) then
    all_sql := v_sql || ' where 1=1 and ' || cond;
  elsif ((v_sql is not null) and (cond is null)) then
    all_sql := v_sql;
  end if;
  all_sql := 'select  * from (select  B.*,rownum rid from (' || all_sql ||
             ')B where rownum<=' || endpos || ') A where A.rid>=' ||
             startpos;
  execute immediate all_sql;
  open v for all_sql;
  return v_count;
Exception
  when others then
    dbms_output.put_line('数据库出现异常！');
    raise;
end getCounts;
end;


---调用包体中的函数和方法

declare
  v_sql    varchar2(200);
  cond     varchar2(200);
  v_cursor tomorrow.p_cursor;
  names    varchar2(10) := '%谢辉%';
  v_count  number;
  x_user_id pmusrinf.user_id%type;
  x_user_nam pmusrinf.user_nam%type;
begin
  v_sql   := 'select user_id from pmusrinf';
  cond    := 'user_nam like ''' || names || '''';
  itfuture.query_date(v_sql, 1, 20, cond, v_cursor);
  fetch v_cursor into x_user_id,x_user_nam;
  loop
  exit when not v_cursor%found;
  dbms_output.put_line(x_user_id||'------------------'||x_user_nam);
  fetch v_cursor into x_user_id,x_user_nam;
  end loop;
  close v_cursor;
end;

declare
  v_sql      varchar2(200);
  cond       varchar2(200);
  v_cursor   tomorrow.p_cursor;
  names      varchar2(10) := '%谢辉%';
  v_count    number;
  x_user_id  pmusrinf.user_id%type;
  x_user_nam pmusrinf.user_nam%type;
  orderby    varchar2(20) := 'user_id,user_nam';
begin
  v_sql    := 'select user_id from pmusrinf';
  cond     := 'user_nam like ''' || names || '''';
  v_cursor := itfuture.getPageDate(v_sql, cond, orderby, 1, 20, 1, v_count);
  fetch v_cursor
    into x_user_id, x_user_nam;
  loop
    exit when not v_cursor%found;
    dbms_output.put_line(x_user_id || '------' || x_user_nam || '------' ||
                         v_count);
    fetch v_cursor
      into x_user_id, x_user_nam;
  end loop;
  close v_cursor;
end;

declare
  v_sql    varchar2(200);
  cond     varchar2(200);
  v_cursor tomorrow.p_cursor;
  names    varchar2(10) := '%谢辉%';
  v_count  number;
begin
  v_sql   := 'select * from pmusrinf';
  cond    := 'user_nam like ''' || names || '''';
  v_cursor := itfuture.getCursors(v_sql, 1, 5, cond, v_count);
  dbms_output.put_line(v_count);
end;


declare
  v_sql    varchar2(200);
  cond     varchar2(200);
  v_cursor tomorrow.p_cursor;
  names    varchar2(10) := '%谢辉%';
  v_count  number;
begin
  v_sql   := 'select * from pmusrinf';
  cond    := 'user_nam like ''' || names || '''';
  v_count := itfuture.getCounts(v_sql, 1, 5, cond, v_cursor);
  dbms_output.put_line(v_count);
end;