Oracle 存过初始化

1、没有参数

CREATE OR REPLACE PROCEDURE PROC_XXXXX IS
      p_RtnCode  NUMBER;
      p_RtnMsg  VARCHAR2(100);
      i_step  NUMBER(9) := 0;
      d_KSSJ     DATE;
      d_JSSJ     DATE;
      i_ROWCOUNT NUMBER;
---CURSOR
CURSOR c_TJNY IS  SELECT DISTINCT (t.tjny) FROM TB_BB_TJSJ@SHTLJ T 
	WHERE T.YXBZ='0' AND SUBSTR(T.TJNY,1,4)IN('2016','2017') ORDER BY t.tjny;
BEGIN
  --返回参数初始化
  i_step := 0;
  p_RtnCode := 0;
  p_RtnMsg := '数据转储成功';


………………………………………………………………

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        p_RtnCode := SQLCODE;
        p_RtnMsg := SQLERRM;
        ROLLBACK;
        Pkg_Error_Log.Pro_Error_In('PROC_XXXXX', i_step, p_RtnCode, p_RtnMsg);
END PROC_XXXXX;

2、含参数

CREATE OR REPLACE PROCEDURE PROC_XXX
          (p_Bxnd IN VARCHAR2, p_RtnCode OUT NUMBER, p_RtnMsg OUT VARCHAR2) IS

      i_step  NUMBER(9) := 0;

CURSOR c_TJNY IS SELECT DISTINCT (t.tjny) FROM TB_BB_TJSJ@SHTLJ T
                    WHERE T.YXBZ='0' AND substr(t.tjny,1,4)=p_Bxnd ORDER BY t.tjny;
BEGIN
  --返回参数初始化
  i_step := 0;
  p_RtnCode := 0;
  p_RtnMsg := '数据转储成功';

------------------------------    数据处理  ------------------------------
……………………………………………………………………………………

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        p_RtnCode := SQLCODE;
        p_RtnMsg := SQLERRM;
        ROLLBACK;
        Pkg_Error_Log.Pro_Error_In('PROC_XXX', i_step, p_RtnCode, p_RtnMsg);
END PROC_XXX;