Oracle-related syntax-subprograms and packages (package, function, procedure)

A subroutine is a named PL/SQL block that can contain parameters and can be called as needed at any time.
PL/SQL has two types of subprograms: process and function.
A process is used to execute a specific task. A function is used to execute the task and return values.
A package encapsulates related types, variables, constants, cursors, exceptions, processes, and functions.
A package consists of a package specification and a package body.
A package specification is a package interface that contains public objects and their types.
The package subject implements the cursor and subroutine in the package specification. The declaration in the package body is only used in the package.
The definition of the midstream object in the package is divided into two parts: the cursor specification and the cursor subject.

Syntax and example:
1. Stored Procedure
Syntax for creating a stored procedure:
Create [or replace] Procedure procedure_name
[(Parameter_list)]
{Is |}
[Local_declarations]
Begin
Executable_statements
[Exception
Exception_handlers]
End [procedure_name];
Procedure_name indicates the process name.
Parameter_list is the parameter list.
Local_declarations is a partial declaration.
Executable_statements is an executable statement.
Exception_handlers is an exception handling program.
Example 1: demonstrate the creation process (assign a default value to the in parameter in the parameter list, and do not assign the default value to the out and in out parameters)
Create or replace procedure find_emp (emp_no in number: = 7900)
As
Empname varchar2 (20 );
Begin
Select ename into empname from EMP where empno = emp_no;
Dbms_output.put_line ('employee name is '| empname );
Exception
When no_data_found then
Dbms_output.put_line ('employee id not found ');
End find_emp;

Call process: Execute procudure_name (parameters_list );
You can also call it in the process and directly write procudure_name without writing execute.

Example 2: demonstrate the process of creating an out Parameter
Create or replace procedure Test (value1 varchar2, value2 out number)
Is
Identity number;
Begin
Select Sal into identity from EMP where empno = value1;
If identity <2000 then
Value2: = 1000;
Else
Value2: = 500;
End if;
End;

Call the process with the out parameter:
Declare
Value2 number;
Begin
Test ('20140901', value2 );
Dbms_output.put_line (value2 );
End;

Example 3: Create an in out Parameter
Create or replace procedure swap (p1 in out number, P2 in out number)
Is
V_temp number;
Begin
V_temp: = p1;
P1: = P2;
P2: = v_temp;
End;

Call the process with the in out parameter:
Declare
Num1 number: = 100;
Num2 number: = 200;
Begin
Swap (num1, num2 );
Dbms_output.put_line ('num1 = '| num1 );
Dbms_output.put_line ('num2 = '| num2 );
End;

Example 4: grant the execution permission of the process to another user
Grant execute on find_emp to Scott;
Grant execute on swap to public;
Grant the execution permission of the find_emp process to Scott, and grant the execution permission of the SWAp process to all database users.

Delete procedure Syntax: Drop procedure procudure_name;

2. Functions
The syntax for defining a function is as follows:
Create [or replace] function function_name
[(Parameter_list)]
Return datatype
{Is |}
[Local_declarations]
Begin
Executable_statements
[Exception
Exception_handlers]
End [function_name];
Function_name is the name of the function.
Parameter_list is the parameter list.
Local_declarations is a partial declaration.
Executable_statements is an executable statement.
Exception_handlers is an exception handling program.
Note when using functions: the format parameter must only use the database type, rather than the PL/SQL type. The return type of the function must also be the database type.
Functions cannot be executed independently. They can only be called using SQL statements or PL/SQL blocks.
Example 5: demonstrate how to create a function
Create or replace function fun_hello
Return varchar2 is
Begin
Return 'Hello friend ';
End;
Call the function: Select fun_hello from dual;

Function authorization: For details, see Example 4.
Delete function: Drop function function_name

Differences between processes and functions
Process Functions
Called as a part of the expression for PL/SQL statement execution
A return clause is not included in a specification and must contain a return clause.
If no value is returned, a single value must be returned.
It can contain a return statement, but unlike a function, it cannot be used to return a value and must contain at least one return statement.

3. Packages
Syntax for creating a package specification:
Create [or replace] package package_name
Is |
[Public type and item declarations]
[Subprogram specifications]
End [package_name];
Package_name indicates the package name.
Public type and item declarations are declared types, constants, variables, exceptions, and cursors.
Subprogram specifications declare PL/SQL subprograms.

Example 6: demonstrate creating a package specification
Create or replace package pack_op is
Procedure pro_print_ename (ID number );
Procedure pro_print_sal (ID number );
Function fun_re_date (ID number) return date;
End;

Syntax for creating a package subject:
Create [or replace] package body package_name
Is |
[Public type and item declarations]
[Subprogram bodies]
[Begin
Initialization_statements]
End [package_name];
Package_name indicates the package name.
Public type and item declarations are declared types, constants, variables, exceptions, and cursors.
Subprogram bodies is a common and private PL/SQL subroutine defined.

Example 7: Create a package subject
Create or replace package body pack_op is
Procedure pro_print_ename (ID number) is
Name EMP. ename % type;
Begin
Select ename into name from EMP where empno = ID;
Dbms_output.put_line ('employee name: '| Name );
End pro_print_ename;
Procedure pro_print_sal (ID number) is
Salary EMP. Sal % type;
Begin
Select Sal into salary from EMP where empno = ID;
Dbms_output.put_line ('employee salary: '| salary );
End pro_print_sal;
Function fun_re_date (ID number) return date is
Bedate EMP. hiredate % type;
Begin
Select hiredate into bedate from EMP where empno = ID;
Return bedate;
End fun_re_date;
End pack_op;

Example 8: Call the process and function created in the package
Exec pack_op.pro_print_ename (7900 );
Exec pack_op.pro_print_sal (7900 );
Select pack_op.fun_re_date (7900) from dual;

Example 9: demonstrate the cursor in the package
Create a package specification
Create or replace package pack_emp is
Cursor cur_emp return EMP % rowtype;
Procedure pro_cur;
End pack_emp;

Create a package subject
Create or replace package body pack_emp is
Cursor cur_emp return EMP % rowtype is
Select * from EMP;
Procedure pro_cur is
Rec_emp EMP % rowtype;
Begin
Open cur_emp;
Loop
Fetch cur_emp into rec_emp;
Exit when cur_emp % notfound;
If rec_emp.sal <1000 then
Dbms_output.put_line ('employee salary: '| rec_emp.sal |', you need to redouble your efforts to raise the salary ');
Elsif rec_emp.sal> = 1000 and rec_emp.sal <2000 then
Dbms_output.put_line ('employee salary: '| rec_emp.sal |', general salary, strive for a department manager to do it ');
Else
Dbms_output.put_line ('employee salary: '| rec_emp.sal |', the salary is good, and a general manager is required ');
End if;
End loop;
End pro_cur;
End pack_emp;

Call the procedure in the package to call the cursor in the package
Exec pack_emp.pro_cur;

Example 10: The stored procedure returns the cursor's subroutine package (this package returns r_cur cursor)
Create or replace package Scott. pk_wt
Is
Type mytype is ref cursor;
Procedure p_wt (MYCS out mytype );
End;

Create or replace package body Scott. pk_wt
Is
Procedure p_wt (MYCS out mytype)
Is
R_cur mytype;
Begin
Open r_cur for select * from EMP;
MYCS: = r_cur;
End p_wt;
End pk_wt;

Query Information about processes, functions, and packages: user_objects data dictionary View
Column object_name format A18
Select object_name, object_type from user_objects where object_type in ('Procedure ', 'function', 'package', 'package body ');