oracle-預存程序練習，oracle-預存程序

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) ASBEGIN  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 ISP_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;