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;