Oracle Stored Procedure syntax

 

1. The Stored Procedure contains three parts: Declaration, execution, and exception.
2. There are no parameter programs or stored procedures with parameters.
3. No parameter program syntax
4. 1 create or replace procedure noparpro
5. 2;
6. 3 begin
7. 4;
8. 5 exception
9. 6;
10. 7 end;
11. 8
13. Example of a stored procedure with Parameters
14. 1 create or replace procedure queryempname (sfindno EMP. empno % Type)
15. 2 sname EMP. ename % type;
16. 3 sjob EMP. Job % type;
17. 4 begin
18. 5 ....
19. 7 exception
20. ....
21. 14 end;
22. 15
24. Stored Procedures with parameters include the Assignment Method
25. 1 create or replace procedure runbyparmeters (isal in EMP. Sal % type,
26. Sname out varchar, sjob in out varchar)
27. 2 As icount number;
28. 3 begin
29. 4 select count (*) into icount from EMP where SAL> isal and job = sjob;
30. 5IfIcount = 1 then
31. 6 ....
32. 9Else
33. 10 ....
34. 12 EndIf;
35. 13 exception
36. 14 when too_many_rows then
37. 15 dbms_output.put_line ('Return value more than 1 line ');
38. 16 when others then
39. 17 dbms_output.put_line ('An error occurred during runbyparmeters! ');
40. 18 end;
41. 19
43. Process call
44. Method 1
45. 1 declare
46. 2 realsal EMP. Sal % type;
47. 3 realname varchar (40 );
48. 4 realjob varchar (40 );
49. 5 begin
50. 6. realsal: = 1100;
51. 7 realname: = '';
52. 8 realjob: = 'cler ';
53. 9 runbyparmeters (realsal, realname, realjob); -- must be in order
54. 10 dbms_output.put_line (realname | ''| realjob );
55. 11 end;
56. 12
58. Method 2
59. 1 declare
60. 2 realsal EMP. Sal % type;
61. 3 realname varchar (40 );
62. 4 realjob varchar (40 );
63. 5 begin
64. 6. realsal: = 1100;
65. 7 realname: = '';
66. 8 realjob: = 'cler ';
67. 9 runbyparmeters (sname => realname, isal => realsal, sjob => realjob); -- the variable sequence of the specified value is variable.
68. 10 dbms_output.put_line (realname | ''| realjob );
69. 11 end;
70. 12

The Stored Procedure contains three parts: Declaration, execution, and exception. There are no parameter programs or stored procedures with parameters. No parameter program Syntax 1 create or replace procedure noparpro2 as; 3 begin4; 5 exception6; 7 end; 8 with parameter Stored Procedure instance 1 create or replace procedure queryempname (sfindno EMP. empno % Type) as 2 sname EMP. ename % type; 3 sjob EMP. job % type; 4 Begin 5 .... 7 exception .... 14 end; 15 with parameter stored procedures include assignment method 1 create or replace procedure runbyparmeters (isal in EMP. sal % type, sname out varchar, sjob in out varchar) 2 as icount number; 3 begin 4 Se Lect count (*) into icount from EMP where SAL> isal and job = sjob; 5 If icount = 1 then 6 .... 9 else10 .... 12 end if; 13 exception14 when too_many_rows then15 dbms_output.put_line ('Return value more than 1 row'); 16 when others then17 dbms_output.put_line ('error in runbyparmeters process! '); 18 end; 19 process call method 1 declare 2 realsal EMP. sal % type; 3 realname varchar (40); 4 realjob varchar (40); 5 begin 6 realsal: = 1100; 7 realname: = ''; 8 realjob: = 'cler'; 9 runbyparmeters (realsal, realname, realjob); -- the order must be 10 dbms_output.put_line (realname | ''| realjob); 11 end; 12 method 2 1 declare 2 realsal EMP. sal % type; 3 realname varchar (40); 4 realjob varchar (40); 5 begin 6 realsal: = 1100; 7 realname: = ''; 8 realjob: = 'cler'; 9 runbyparmeters (sname => realname, isal => realsal, sjob => realjob ); -- The variable sequence corresponding to the specified value can be 10 dbms_output.put_line (realname | ''| realjob); 11 end; 12