Oracle Stored Procedure

The Oracle stored procedure consists of three parts: Process Declaration, execution process, and stored procedure exception.

1. Oracle stored procedures can contain parameter stored procedures and parameter stored procedures.
2. I. No parameter program process syntax
3. 1Create Or Replace ProcedureNoParPro
4. 2As;
5. 3Begin
6. 4;
7. 5 exception // The stored procedure is abnormal.
8. 6;
9. 7End;
10. 8
12. Ii. Example of stored procedure with Parameters
13. 1Create Or Replace ProcedureQueryempname (sfindno emp. empno % type)As
14. 2 sName emp. ename % type;
15. 3 sjob emp. job % type;
16. 4Begin
17. 5 ....
18. 7 exception
19. ....
20. 14End;
21. 15
23. Iii. Parameter stored procedures and value assignment methods
24. 1Create Or Replace ProcedureRunbyparmeters (isalInEmp. sal % type,
25. SnameOut Varchar, SjobIn Out Varchar)
26. 2AsIcount number;
27. 3Begin
28. 4Select Count(*)IntoIcountFromEmpWhereSal> isalAndJob = sjob;
29. 5 if icount = 1Then
30. 6 ....
31. 9Else
32. 10 ....
33. 12EndIf;
34. 13 exception
35. 14WhenToo_many_rowsThen
36. 15 DBMS_OUTPUT.PUT_LINE ('Return value more than 1 Line');
37. 16WhenOthersThen
38. 17 DBMS_OUTPUT.PUT_LINE ('An error occurred during the RUNBYPARMETERS process! ');
39. 18End;
40. 19
42. 4. Calling stored procedures in Oracle
43. Method 1
44. 1Declare
45. 2 realsal emp. sal % type;
46. 3 realnameVarchar(40 );
47. 4 realjobVarchar(40 );
48. 5Begin// The Stored Procedure Call starts.
49. 6. realsal: = 1100;
50. 7 realname: ='';
51. 8 realjob: ='Cler';
52. 9 runbyparmeters (realsal, realname, realjob); -- must be in order
53. 10 DBMS_OUTPUT.PUT_LINE (REALNAME |''| REALJOB );
54. 11END; // Process call ends
55. 12
57. Method 2
58. 1Declare
59. 2 realsal emp. sal % type;
60. 3 realnameVarchar(40 );
61. 4 realjobVarchar(40 );
62. 5Begin// Process call starts
63. 6. realsal: = 1100;
64. 7 realname: ='';
65. 8 realjob: ='Cler';
66. 9 runbyparmeters (sname => realname, isal => realsal, sjob => realjob); -- the variable sequence of the specified value is variable.
67. 10 DBMS_OUTPUT.PUT_LINE (REALNAME |''| REALJOB );
68. 11END; // Process call ends
69. 12