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;