Oracle包（Package)的建立問題

Oracle中包相當於命名空間，建立包分兩步：

  1.建立包頭

 2.建立包體

包體和包頭建立要分開,而且要再sql window種執行，否則會出現錯誤：Error: PLS-00103: Encountered the symbol "CREATE"。

下面是執行個體：

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

調用方法：

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