oracle--Stored Procedures

1, Baotou:

CREATE OR REPLACE    Package bawq_proc_page are-- bawq_proc_page  is the packet header    TYPE t_cursor is REF CURSOR    ; -- dynamic cursor    PROCEDURE  proc_page-- stored procedure name    (              cs_zbin              INT: = pagenumber in int,              pageSize in int, out               INT, out  t_cursor      ); END Bawq_proc_page;

2. Package Body:

CREATE OR REPLACE package BODY bawq_proc_page isprocedure proc_page (cs_zb in INT:= -, pagenumber in int, pageSize in int, SUM1 outINT, P_recursor0 outt_cursor) IsbeginifPageNumber isNotNULLand PageSize isNotNULL ThenSelectCOUNT (1) into SUM1 from        (            SelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUnion AllSelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUNION AllSelect 'Province','City','County',- -Sales this year,- -Sales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',NULLSales this year,NULLSales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',0Sales this year,0Sales last year fromDual WHERE cs_zb>Ten) AAA; Open P_recursor0 for SelectProvince, city, county, sales this year, sales last year from        (            SelectRowNum rownumber,a.* from            (                SelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUnion AllSelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUNION AllSelect 'Province','City','County',- -Sales this year,- -Sales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',NULLSales this year,NULLSales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',0Sales this year,0Sales last year fromDual WHERE cs_zb>Ten) A) B WHERE rownumber between (pagenumber-1) *pagesize+1and pagenumber*pageSize; ElseOpen P_recursor0 for    SelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUnion AllSelectYJ Province, EJ City, SJ County, SZ1 this year sales, SZ2 last year sales from SZQCSBJ WHERE cs_zb>TenUNION AllSelect 'Province','City','County',- -Sales this year,- -Sales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',NULLSales this year,NULLSales last year fromDual WHERE cs_zb>TenUnion AllSelect 'Province','City','County',0Sales this year,0Sales last year fromDual WHERE cs_zb>Ten; Endif; END Proc_page; END Bawq_proc_page;

oracle--Stored Procedures