PL/SQL Learning notes _03_ storage functions and stored procedures

ORACLE provides the ability to store PL/SQL programs in a database, and can run it from anywhere. This is called a stored procedure or function .

Stored function: There is a return value, after creation is completed, through the Select function () from dual;

Stored procedure: Because there is no return value, after creation is complete, you cannot use the SELECT statement, only the PL/SQL blocks can be used to execute

A. Storage function1. Storage function Syntax format

CREATE [OR REPLACE]  FUNCTIONfunction_name[(argment [{in | in Out}]Type, Argment[{in | Out | in Out}]Type)] --return value type    RETURNReturn_type is       --PL/SQL block variable, record type, cursor declaration (similar to the part of the previous declare)BEGIN      --function body (can be implemented additions and deletions and other operations, return values need return)      returnreturn value; EXCEPTION--Exception CaptureEND;

Note:. in: indicates that the value passed to the function does not change in the execution of the function ;

out: represents a value that is evaluated in a function and passed to the calling statement by this parameter ;

          in out :  . 

if the tag is omitted , the parameter is implied in

"Example 1" without the parameter function

Requirement: HelloWorld of function: Returns a string of "HelloWorld"

Create or Replace function Hello_func return varchar2  is begin       return ' HelloWorld ' ; End;

View Code

Execute function

begin     Dbms_output.put_line (Hello_func ()); End;

View Code

"Example 2" with the parameter function

Required: Returns a "Helloworld:atguigu" string, where Atguigu is entered by the execution of the function.

--Declaration of a function (with arguments written in parentheses)Create or Replace functionHello_func (V_logovarchar2)--return value typereturn varchar2 is --Declaration of PL/SQL block variablesbegin--function Body       return 'HelloWorld'||V_logo;End;

View Code

"Example 3"

Requirements: Gets the sum of the wages for a given department, requiring that the department number is defined as a parameter and the payroll is defined as the return value.

Create or Replace functionSum_sal (dept_id Number)       return  Number        is              cursorSal_cursor is SelectSalary fromEmployeeswheredepartment_id=dept_id; V_sum_sal Number(8) := 0; begin        forCinchSal_cursor loop v_sum_sal:=V_sum_sal+c.salary; EndLoop; --dbms_output.put_line (' Sum salary: ' | | v_sum_sal);       returnv_sum_sal;End;

View Code

Execute function

begin     dbms_output.put_line (sum_sal); End;

View Code

2. Out type parameters

Because a function can have only one return value, a PL/SQL program can have multiple return values through an out-type parameter implementation

"Example 4"

Requirement: Define a function that gets the sum of the payroll for a given department and the total number of employees in that department (defined as an out type parameter). The department number is defined as a parameter, and the payroll is defined as the return value.

Create or Replace functionSum_sal (dept_id Number, Total_count out Number)       return  Number        is              cursorSal_cursor is SelectSalary fromEmployeeswheredepartment_id=dept_id; V_sum_sal Number(8) := 0; beginTotal_count:= 0;  forCinchSal_cursor loop v_sum_sal:=V_sum_sal+c.salary; Total_count:=Total_count+ 1; EndLoop; --dbms_output.put_line (' Sum salary: ' | | v_sum_sal);       returnv_sum_sal;End;

View Code

Execute function

Delare     Number (3):=0; begin     dbms_output.put_line (sum_sal (v_total));    Dbms_output.put_line (v_total); End;

View Code

3. Delete a function

DROP    FUNCTION    [user. ]function_name;

Two. stored procedure 1. Stored Procedure Syntax format

CREATE [OR REPLACE]  PROCEDUREprocedure_name[(argment [{in | in Out}]Type, Argment[{in | Out | in Out}]Type)] is       --PL/SQL block variable, record type, cursor declaration (similar to the part of the previous declare)BEGIN      --function body (can be implemented additions and deletions and other operations, return values need return)EXCEPTION--Exception CaptureEND;

"Example 5"

Requirement: A pay raise for an employee of a given department (as an input parameter), if its time in the company is at (?, 95), for which the pay rise%5

[95, 98)%3
[98,?] %
The following results are returned: How much extra cost is required per month for this pay raise (define an out-of-type output parameter).

Create or Replace procedureAdd_sal_procedure (dept_id Number,TempOut Number) is       cursorSal_cursor is Selectemployee_id ID, hire_date HD, Salary sal fromEmployeeswheredepartment_id=dept_id; A Number(4,2) := 0;begin       Temp:= 0;  forCinchSal_cursor loop A:= 0; ifC.hd<To_date ('1995-1-1','YYYY-MM-DD') ThenA:= 0.05; elsif C.HD<To_date ('1998-1-1','YYYY-MM-DD') ThenA:= 0.03; ElseA:= 0.01; End if; Temp:= Temp +C.sal*A; UpdateEmployeesSetSalary=Salary*(1 +Awhereemployee_id=c.id; EndLoop; End;

View Code

2. Delete a stored procedure

DROP   PROCEDURE   [user. ]procudure_name;

PL/SQL Learning notes _03_ stored functions and stored procedures