Oracle-stored procedure exercises, oracle-Stored Procedure

Oracle-stored procedure exercises, oracle-Stored Procedure

-- ==================================================== Stored Procedure = ========================================================== =======/ ** scm_iss.test_imit_pro1 * No parameter Stored PROCEDURE */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 * stored PROCEDURE with input parameters */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; -- Test CALL scm_iss.test_imti_pro2 (1003 ); /** scm_iss.test_imti_pro3 * stored procedures with input and output parameters * cannot be used directly. You need to call */create or replace procedure TEST_IMTI_PRO3 (P_NO in number, P_NAME OUT VARCHAR2) IN the Function) 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 * insert data stored PROCEDURE into the database table **/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 values (SUBSTR (SQLERRM, 1,240); END; /** scm_iss.test_imti_fun1 * No Parameter 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; -- call the parameter-free Stored Procedure SCM_ISS.TEST_IMIT_PRO1; -- call the input parameter Stored Procedure timeout (1003); RETURN P_ITME_NAME; exception when others then X_MESSAGE: = SUBSTR (SQLERRM, 1,240 ); RETURN X_MESSAGE; END TEST_IMTI_FUN1;