--====================================存储过程===============================================
/*
*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;