Oracle-Stored Procedure Exercises

 --==================================== 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 to 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 to 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 calls call Scm_iss.test_imti_pro2 (1003);/**scm_iss.test_imti_pro3* stored procedure with input and output parameters * cannot be dropped directly, You need to call */create OR REPLACE PROCEDURE Test_imti_pro3 (p_no in number, p_n) in function AME out VARCHAR2) as T_name VARCHAR2 (200); BEGIN  T_name: = ' HELLO WORD. '; Dbms_output. Put_Line (' t_name: ' | |  T_name); SELECT i.imti_name to 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* Inserts a data stored procedure into a 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 and then Dbms_output. Put_Line (SUBSTR (sqlerrm,1,240)); END test_salt_pro1;/**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 to P_itme_name from Scm_iss.  T_imti_test I WHERE i.imti_no = 1002; --Call the parameterless stored procedure Scm_iss.  Test_imit_pro1; --Call the input parameter stored procedure 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;

Oracle-Stored Procedure Exercises