Stored Procedures and functions of PL/SQL programs

1. Stored Procedures and functions
2. A subroutine stored in a database for calls by all user programs is called a stored procedure and a stored function.
3. Create a stored procedure:
4. UseCREATE PROCEDURECommand to create a stored procedure and a stored function.
5. Syntax:
6. Create[Or Replace]PROCEDUREProcess name (parameter list)
7. AS
8. PLSQL subprogram body;
9. Note: stored procedures are generally used in highly secure systems.
11. Example 1: increase the salary of all employees by 10%
12. Create a stored procedure
13. Create Or Replace ProcedureUpdateSal
14. As
15. -- Plsql Block
16. -- The Stored Procedure cannot have declare
17. Create Or Replace ProcedureUpdateSal
18. As
19. CursorC1Is SelectSal, empnoFromEmp;
20. Emp_sal emp. sal % type;
21. Emp_no emp. empno % type;
22. Begin
23. OpenC1;
24. Loop
25. FetchC1IntoEmp_sal, emp_no;
26. ExitWhenC1 % notfound;
27. UpdateEmpSetSal = emp_sal * 1.1WhereEmpno = emp_no;
28. EndLoop;
29. CloseC1;
30. End;
31. Call Stored Procedure
32. Method 1:
33. SQL>Begin
34. 2 updateSal;
35. 3End;
36. 4/
37. Method 2:
38. SQL>ExecUpdateSal;
39. After the call is complete, submit it manually.
40. 【
41. CommitStatement: ends the current transaction, so that all the modifications executed by the current transaction are permanent.
42. ]
44. Storage Functions
45. Function (Function) Is a named bucket that can contain parameters and return a calculated value. The structure of a function and a process is similar, but there must beRETURNClause to return the function value. Function Description specifies the function name, result value type, and parameter type.
46. Syntax for creating a storage function:
47. CREATE[OR REPLACE]FUNCTIONFunction Name (parameter list)
48. RETURNFunction value type
49. AS
50. PLSQL subprogram body;
52. Example 2: Obtain the annual income of a specified employee
53. Create Or Replace FunctionSumSal (emp_no number)-- Function (the parameter value must have a type)
54. -- Return Value Type
55. ReturnNumber-- The return value must exist.
56. As
57. -- Declare Variables
58. Emp_sal emp. sal % type;
59. Emp_comm emp. comm % type;
60. Total emp. sal % type;
61. Begin
62. SelectSal, commIntoEmp_sal, emp_commFromEmpWhereEmpno = emp_no;
63. Total: = emp_sal * 12 + nvl (emp_comm, 0 );
64. ReturnTotal;-- The return value must be of the same type.
65. End;
66. Call Method
67. Method 1:
68. SQL>Begin
69. 2 dbms_output.put_line (sumSal (7369 ));
70. 3End;
71. 4/
72. Method 2:
73. Declare
74. V_sal number;
75. Begin
76. V_sal: = sumSal (1, 7369 );
77. Dbms_output.put_line (v_sal );
78. End;
79. Result
80. 12777.6