oracle-存儲過程練習

oracle-存儲過程練習

 

--====================================存儲過程===============================================
/*
*scm_iss.test_imit_pro1
*無參數存儲過程
*/
CREATE OR REPLACE PROCEDURE TEST_IMIT_PRO1 AS
  P_IMTI_NAME VARCHAR2(200);
  x_message VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME INTO P_IMTI_NAME FROM SCM_ISS.T_IMTI_TEST I WHERE I.IMTI_NO = 1001;
  DBMS_OUTPUT.PUT_LINE('result:' || P_IMTI_NAME);
/*EXCEPTION
  WHEN OTHERS
    x_message := SUBSTR(SQLERRM,1,240);*/
END TEST_IMIT_PRO1;

/*
*scm_iss.test_imti_pro2
*帶輸入參數的存儲過程
*/

CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO2(P_NO IN NUMBER) AS
  P_IMTI_NAME VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME
    INTO P_IMTI_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = P_NO;
  DBMS_OUTPUT.PUT_LINE('NAME:' || P_IMTI_NAME);
END TEST_IMTI_PRO2;

--測試調用
CALL scm_iss.test_imti_pro2(1003);

/*
*scm_iss.test_imti_pro3
*帶輸入輸出參數的存儲過程
*不能直接掉用，需要在Function中調用
*/

CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO3(P_NO   IN NUMBER,
                                           P_NAME OUT VARCHAR2) AS
  T_NAME VARCHAR2(200);
BEGIN
  T_NAME := 'HELLO WORD.';
  DBMS_OUTPUT.PUT_LINE('T_NAME:' || T_NAME);
  SELECT I.IMTI_NAME
    INTO P_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = P_NO;
  DBMS_OUTPUT.PUT_LINE('TEST_IMTI_PRO3 RETURN:' || P_NAME);
END TEST_IMTI_PRO3;


/**
*TEST_SALT_PRO1
*往數據庫表中插入數據存儲過程
**/
CREATE OR REPLACE PROCEDURE TEST_SALT_PRO1(P_SALT_NUM IN NUMBER,P_SALT_NAME IN VARCHAR2,P_SALT_DESC IN VARCHAR2) AS

BEGIN
  INSERT INTO T_SALT_TEST(SALT_NO,SALT_NAME,SALT_DESC) VALUES(P_SALT_NUM,P_SALT_NAME,P_SALT_DESC);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,240));
END TEST_SALT_PRO1;


/*
*scm_iss.test_imti_fun1
*無參數Function
*/

CREATE OR REPLACE FUNCTION TEST_IMTI_FUN1 RETURN VARCHAR2 IS
P_ITME_NAME VARCHAR2(200);
X_MESSAGE VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME INTO P_ITME_NAME FROM SCM_ISS.T_IMTI_TEST I WHERE I.IMTI_NO = 1002;
  --調用無參數存儲過程
  SCM_ISS.TEST_IMIT_PRO1;
  --調用輸入參數存儲過程
  SCM_ISS.TEST_IMTI_PRO2(1003);
  RETURN P_ITME_NAME;
EXCEPTION
  WHEN OTHERS THEN
   X_MESSAGE := SUBSTR(SQLERRM,1,240);
   RETURN X_MESSAGE;
END TEST_IMTI_FUN1;