oracle包体的定义案例

create or replace package body DotNet is -- Private type declarations --type <TypeName> is <Datatype>; -- Private constant declarations --<ConstantName> constant <Datatype> := <Value>; -- Private variable declarations --<VariableName> <Datatype>; -- Function and procedure implementations --function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is -- <LocalVariable> <Datatype>; --begin -- <Statement>; -- return(<Result>); --end; PROCEDURE DotNetPagination( Pindex in number, --1 Psql in varchar2, Psize in number, --3 Pcount out number, v_cur out type_cur ) AS v_sql varchar2(1000); v_count number; v_Plow number; v_Phei number; begin v_sql := 'select count(*) from('||Psql||')'; execute immediate v_sql into v_count; Pcount := ceil(v_count/Psize); v_Phei := Pindex*Psize+Psize; --6 v_Plow := v_Phei+Psize-12; --0 v_sql := 'select * from ( '||Psql||' ) where rownum between '||v_Plow||' and '||v_Phei; -- rownum --open v_cur for select * from custom; open v_cur for v_sql; end DotNetPagination; PROCEDURE DotNetPageRecordsCount( Psqlcount in varchar2, Prcount out number ) AS v_sql varchar2(1000); v_prcount number; begin v_sql := 'select count(*) from ('||Psqlcount||')'; execute immediate v_sql into v_prcount; Prcount := v_prcount; end DotNetPageRecordsCount; FUNCTION getBank(codeno IN varchar2) RETURN varchar2 IS rt_bank varchar2(10); BEGIN SELECT bank INTO rt_bank FROM custom WHERE code = codeno; RETURN rt_bank; END; end DotNet;