【Oracle】oracle-存储过程练习

小雨涵

1. --====================================存储过程===============================================
   
2. /*
   
3. *scm_iss.test_imit_pro1
   
4. *无参数存储过程
   
5. */
   
6. CREATE OR REPLACE PROCEDURE TEST_IMIT_PRO1 AS
   
7.   P_IMTI_NAME VARCHAR2(200);
   
8.   x_message VARCHAR2(200);
   
9. BEGIN
   
10.   SELECT I.IMTI_NAME INTO P_IMTI_NAME FROM SCM_ISS.T_IMTI_TEST I WHERE I.IMTI_NO = 1001;
    
11.   DBMS_OUTPUT.PUT_LINE('result:' || P_IMTI_NAME);
    
12. /*EXCEPTION
    
13.   WHEN OTHERS
    
14.     x_message := SUBSTR(SQLERRM,1,240);*/
    
15. END TEST_IMIT_PRO1;
    
16. 
    
17. /*
    
18. *scm_iss.test_imti_pro2
    
19. *带输入参数的存储过程
    
20. */
    
21. 
    
22. CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO2(P_NO IN NUMBER) AS
    
23.   P_IMTI_NAME VARCHAR2(200);
    
24. BEGIN
    
25.   SELECT I.IMTI_NAME
    
26.     INTO P_IMTI_NAME
    
27.     FROM SCM_ISS.T_IMTI_TEST I
    
28.    WHERE I.IMTI_NO = P_NO;
    
29.   DBMS_OUTPUT.PUT_LINE('NAME:' || P_IMTI_NAME);
    
30. END TEST_IMTI_PRO2;
    
31. 
    
32. --测试调用
    
33. CALL scm_iss.test_imti_pro2(1003);
    
34. 
    
35. /*
    
36. *scm_iss.test_imti_pro3
    
37. *带输入输出参数的存储过程
    
38. *不能直接掉用，需要在Function中调用
    
39. */
    
40. 
    
41. CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO3(P_NO   IN NUMBER,
    
42.                                            P_NAME OUT VARCHAR2) AS
    
43.   T_NAME VARCHAR2(200);
    
44. BEGIN
    
45.   T_NAME := 'HELLO WORD.';
    
46.   DBMS_OUTPUT.PUT_LINE('T_NAME:' || T_NAME);
    
47.   SELECT I.IMTI_NAME
    
48.     INTO P_NAME
    
49.     FROM SCM_ISS.T_IMTI_TEST I
    
50.    WHERE I.IMTI_NO = P_NO;
    
51.   DBMS_OUTPUT.PUT_LINE('TEST_IMTI_PRO3 RETURN:' || P_NAME);
    
52. END TEST_IMTI_PRO3;
    
53. 
    
54. 
    
55. /**
    
56. *TEST_SALT_PRO1
    
57. *往数据库表中插入数据存储过程
    
58. **/
    
59. CREATE OR REPLACE PROCEDURE TEST_SALT_PRO1(P_SALT_NUM IN NUMBER,P_SALT_NAME IN VARCHAR2,P_SALT_DESC IN VARCHAR2) AS
    
60. 
    
61. BEGIN
    
62.   INSERT INTO T_SALT_TEST(SALT_NO,SALT_NAME,SALT_DESC) VALUES(P_SALT_NUM,P_SALT_NAME,P_SALT_DESC);
    
63.   COMMIT;
    
64. EXCEPTION
    
65.   WHEN OTHERS THEN
    
66.     DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,240));
    
67. END TEST_SALT_PRO1;
    
68. 
    
69. 
    
70. /*
    
71. *scm_iss.test_imti_fun1
    
72. *无参数Function
    
73. */
    
74. 
    
75. CREATE OR REPLACE FUNCTION TEST_IMTI_FUN1 RETURN VARCHAR2 IS
    
76. P_ITME_NAME VARCHAR2(200);
    
77. X_MESSAGE VARCHAR2(200);
    
78. BEGIN
    
79.   SELECT I.IMTI_NAME INTO P_ITME_NAME FROM SCM_ISS.T_IMTI_TEST I WHERE I.IMTI_NO = 1002;
    
80.   --调用无参数存储过程
    
81.   SCM_ISS.TEST_IMIT_PRO1;
    
82.   --调用输入参数存储过程
    
83.   SCM_ISS.TEST_IMTI_PRO2(1003);
    
84.   RETURN P_ITME_NAME;
    
85. EXCEPTION
    
86.   WHEN OTHERS THEN
    
87.    X_MESSAGE := SUBSTR(SQLERRM,1,240);
    
88.    RETURN X_MESSAGE;
    
89. END TEST_IMTI_FUN1;

复制代码