pl/sql-04

procedures, functions, and packages
procedures, functions, and packages are named blocks of a PL/SQL statement block, and procedures and functions are collectively referred to as subroutines.
Procedures and functions are very similar and have the following characteristics:
1, both have a name, can receive incoming or outgoing parameters
2, all have the declaration part, the execution part, the exception processing part
3, will be compiled and stored in the database before use
4, the biggest difference between functions and procedures is that the function has a return value, and the procedure does not

Create or Replace procedure Addempsalary (p_ratio number, p_empno number)
As
Begin
If P_ratio > 0
Then
Update scott.emp Set sal = Sal * (1 + p_ratio) where empno = P_empno;
End If;
Dbms_output.put_line (' The pay rise is successful! ‘);
End;

A package is a logical unit, and PL/SQL allows developers to put logically related types, variables, cursors, and subroutines inside a package, which is clearer and easier to understand

Package Specification section: The Package Specification section defines the interface of the application, which declares variables, constants, types, exceptions, cursors, and so on.
Package Body part: The package body part is used to implement the subroutine and cursor that the package specification partially declares.
The creation of the package specification uses the Create PACKAGE statement, and the creation of the packet body using the Create Packages body statement.

/* Package Specification Definition */
Create or Replace package empsalary
As
--Perform the actual wage increase action
Procedure Addempsalary (p_ratio number, p_empno number);
--Use IF-ELSIF statement to get salary increase ratio
function Change_sal (p_job varchar2) return number;
--use case statement to get salary increase ratio
function Getaddsalaryratiocase (p_job varchar2) return number;
End Empsalary;

/* Package Body definition */

Create or replace package body Empsalary
As
--Process raises
Procedure Addempsalary (p_ratio number, p_empno number)
As
Begin
If P_ratio > 0
Then
Update scott.emp Set sal = Sal * (1 + p_ratio) where empno = P_empno;
End If;
Dbms_output.put_line (' The pay rise is successful! ‘);
End;
--Use IF-ELSIF statement to get salary increase ratio
function Change_sal (p_job varchar2)
return number
As V_result number (7,2);
Begin
If p_job = ' clerk ' then v_result: = 0.1;
elsif p_job = ' salesman ' then v_result: = 0.15;
elsif p_job = ' MANAGER ' then v_result: = 0.2;
End If;
return v_result;
End

--use case statement to get salary increase ratio

function Getaddsalaryratiocase (p_job varchar2) return number
As V_result number (7,2);
Begin
Case P_job
When ' clerk ' then v_result: = 0.10;
When ' salesman ' then v_result: = 0.15;
When the ' MANAGER ' then v_result: = 0.20;
End case;
return v_result;
Exception when others
Then Dbms_output.put_line (' Produce an exception: ');

End;
End Empsalary;

pl/sql-04