Oracle 存储过程例子_自己编写的！

CREATE OR REPLACE PACKAGE SELECT_CONTRACT_OFFICES AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE Get_CONTRACT_OFFICES (
cur_CONTRACT_OFFICES OUT T_CURSOR
);
END SELECT_CONTRACT_OFFICES;
//----------------
CREATE OR REPLACE PACKAGE BODY SELECT_CONTRACT_OFFICES AS

PROCEDURE Get_CONTRACT_OFFICES
(
cur_CONTRACT_OFFICES OUT T_CURSOR
--cur_CONTRACT_OFFICES OUT CURSOR
)
IS
BEGIN
-- return all CONTRACT_OFFICES records
OPEN cur_CONTRACT_OFFICES FOR
select t.code,t.description_c as office_name from cmdba.cmcdms t where t.code_item_no='001' and t.data1='1';
END Get_CONTRACT_OFFICES;

END SELECT_CONTRACT_OFFICES;
/
//******************************
//******************************

 

CREATE OR REPLACE PACKAGE SELECT_CONTRACT_OFFICES1 AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE Get_C1010 (
cur_CONTRACT_OFFICES OUT T_CURSOR
);
END SELECT_CONTRACT_OFFICES1;
/

//-------------------
CREATE OR REPLACE PACKAGE BODY SELECT_CONTRACT_OFFICES1 AS
PROCEDURE Get_C1010 
(
cur_CONTRACT_OFFICES OUT T_CURSOR
--cur_CONTRACT_OFFICES OUT CURSOR
)
IS
BEGIN
-- return all CONTRACT_OFFICES records
OPEN cur_CONTRACT_OFFICES FOR
 SELECT T.JOB_NO,T.ITEM_NO,T.ITEM_VALUE FROM DCDBA.DCJBSC T
WHERE T.JOB_NO='AMA2002' AND T.CUR_FLAG='1' AND T.ITEM_NO='F1220';
END Get_C1010 ;
END SELECT_CONTRACT_OFFICES1;

//******************************
//******************************

CREATE OR REPLACE PACKAGE SELECT_CONTRACT_OFFICES2 AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE Get_C1010 (
cur_CONTRACT_OFFICES OUT T_CURSOR
);
END SELECT_CONTRACT_OFFICES2;
/
//-------------------
CREATE OR REPLACE PACKAGE BODY SELECT_CONTRACT_OFFICES2 AS
PROCEDURE Get_C1010 
(
cur_CONTRACT_OFFICES OUT T_CURSOR
--cur_CONTRACT_OFFICES OUT CURSOR
)
IS
BEGIN
-- return all CONTRACT_OFFICES records
OPEN cur_CONTRACT_OFFICES FOR
  SELECT CN1.PROJECT_NO,CN2.JOB_STATUS,DC.JOB_NO,CN1.PROJECT_NAME_C AS JOB_NAME,                 CN2.JOB_SITE_DUE AS DELIVERY,M1.DELITYPE,
        CN1.L_CONTRACT_DATE AS ISSUE_DATE,DC2.APP_DUEDATE,
        (TO_DATE(DC2.APP_DUEDATE,'yyyymmdd')-TO_DATE(TO_CHAR(SYSDATE,'yyyymmdd'),'YYYYMMDD')) AS FLAG,
        SU.SALESPERSON,M2.PCOMPANY FROM CNDBA.CNPRJ1 CN1,CNDBA.CNPRJ2 CN2,
        (SELECT D.JOB_NO FROM DCDBA.DCJBSC D
        WHERE D.ITEM_NO='F1040' AND D.DA_FLAG='2' AND D.Cur_Flag='1' and D.ITEM_VALUE IS NULL) DC,
        (SELECT D.JOB_NO,D.ITEM_VALUE AS APP_DUEDATE FROM DCDBA.DCJBSC D
        WHERE D.ITEM_NO='F1040' AND D.DA_FLAG='1' AND D.Cur_Flag='1' ) DC2,
        (SELECT T.CODE,T.DESCRIPTION_C AS DELITYPE FROM CMDBA.CMCDMS T
        WHERE T.CODE_ITEM_NO='524') M1,
        (select t.code,t.description_c as pcompany,mm.pSaleArea from cmdba.cmcdms t,
         (select tt.code,tt.description_c as pSaleArea from cmdba.cmcdms tt
        where tt.code_item_no='001') mm
        where t.code_item_no='001' and t.data1='1' and  t.data6=mm.code) M2,
        (select S.USER_ID,S.LAST_NAME||S.FIRST_NAME AS SalesPerson from sydba.syuserms S) su
        WHERE CN1.PROJECT_NO<>'000000' AND CN1.CONTRACT_OFFICE='523' AND CN1.PROJECT_NO=CN2.PROJECT_NO
        AND CN2.JOB_STATUS='O'
        AND CN2.JOB_SITE_DUE<>'29991231'
        AND SUBSTR(DC.JOB_NO,1,2)=CN2.JOB_TYPE_A
        AND SUBSTR(DC.JOB_NO,3,1)=CN2.JOB_TYPE_B
        AND SUBSTR(DC.JOB_NO,4,4)=CN2.JOB_SEQ_NO
        AND DC.JOB_NO=DC2.JOB_NO
        AND CN1.CONTRACT_OFFICE=M2.CODE
        AND CN2.DELIVERY_TYPE=M1.CODE
        AND CN1.SALES_PERSON=SU.USER_ID
        ORDER BY dc2.app_duedate,cn2.job_site_due,cn1.project_no,dc.job_no;
END Get_C1010 ;
END SELECT_CONTRACT_OFFICES2;

存储过程不像sql，可以跨用户使用。只能对自己用户的表编写存储过程。但SYS 用户除外。SYS 默认密码：change_on_install

 

//************************
//************************

CREATE OR REPLACE PACKAGE SELECT_CONTRACT_OFFICES3 AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE Get_test (
cur_CONTRACT_OFFICES OUT T_CURSOR,
item_no varchar2
);
END SELECT_CONTRACT_OFFICES3;
/
//----------------
CREATE OR REPLACE PACKAGE BODY SELECT_CONTRACT_OFFICES3 AS

PROCEDURE Get_test
(
cur_CONTRACT_OFFICES OUT T_CURSOR,
item_no varchar2
--cur_CONTRACT_OFFICES OUT CURSOR
)
IS
v_item_no varchar2(3);
BEGIN
-- return all CONTRACT_OFFICES records
v_item_no:=item_no;
OPEN cur_CONTRACT_OFFICES FOR
select t.code,t.description_c as office_name from cmdba.cmcdms t where t.code_item_no=v_item_no ;
END Get_test;

END SELECT_CONTRACT_OFFICES3;
/