Oracle包（Package)的创建问题

Oracle中包相当于命名空间，创建包分两步：

  1.创建包头

 2.创建包体

包体和包头创建要分开,而且要再sql window种执行，否则会出现错误：Error: PLS-00103: Encountered the symbol "CREATE"。

下面是实例：

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

调用方法：

declare ResultCursor MyPackage.MyCursor; ResultCount int; begin MyPackage.SelectBase(1,2,'person','1=1 order by personname',ResultCount,ResultCursor); dbms_output.put_line('resultcount:'||ResultCount); end;

转载于:https://www.cnblogs.com/qb371/archive/2011/09/28/2366302.html