Oracle包（Package)的创建

Oracle创建包分两步：

  1.创建包头

 2.创建包体

包体和包头创建要分开，而且要在SQL WINDOWS

SQL> create or replace package MyPackage as
  2  type MyCursor is ref cursor;
  3  procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  4    resultCount out int, resultCursor out MyCursor);
  5  end MyPackage;
  6  /

Package created

SQL> 
SQL> create or replace package Body MyPackage is
  2  procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  3    resultCount out int, resultCursor out MyCursor)
  4    is
  5    --定义变量
  6    newtableName varchar2(4000);
  7    rowStart  int;
  8    rowEnd    int;
  9    mySql varchar2(8000);
 10    whereOnly varchar2(8000);
 11    OrderOnly varchar2(400);
 12    begin
 13      newtableName:=tableName;
 14      mySql:='select count(*) from '||tableName;
 15  
 16  
 17      if whereStr is not null and length(whereStr)>0
 18        then
 19            rowStart:=instr(whereStr,'order by');
 20           if rowStart>0
 21            then
 22              whereOnly:=substr(whereStr, 1,rowStart-1);    --取得条件
 23              OrderOnly:=substr(whereStr,rowStart, length(whereStr)-rowStart+1);    --取得排序方式(order by 字段 方式)
 24            else
 25              whereOnly:=whereStr;
 26              OrderOnly:='';
 27              end if;
 28             whereOnly:=' where '|| whereOnly;
 29             mySql:=mySql||whereOnly;
 30  
 31       end if;
 32       execute immediate mySql into resultCount;
 33         -- dbms_output.put_line('查询总条数SQL=>'||whereStr||'--'||mySql||resultCount);
 34      --执行查询,查询总条数
 35  
 36  
 37  
 38              --不分页查所有
 39  
 40          if pageIndex=0 and pageSize=0
 41          then
 42          mySql:='select * from '||tableName||whereOnly||OrderOnly;
 43         else
 44  --计算起始和结束索引
 45  
 46          rowStart:=(pageIndex-1)*pageSize+1;
 47          rowEnd:=rowStart+pageSize-1;
 48          mySql:='select * from (select t.*,RowNum as rn from (select * from '||newtableName||whereOnly||OrderOnly||') t) where rn between '||rowStart||' and '||rowEnd;
 49  
 50          end if;
 51      open ResultCursor for mySql;
 52     --dbms_output.put_line('SQL=>'||mySql);
 53      end SelectBase;
 54    end MyPackage;
 55  /

Package body created