Oracle study notes 18-stored functions and stored procedures (PL/SQL subprograms)

PL/SQL subprograms include functions and procedures. The function here refers to the user-defined function, which is different from the system function. A subroutine is a PL/SQL block that completes a specific function. It is universal and can be called multiple times by different applications. Oracle provides the ability to store PL/SQL programs in databases and run them anywhere. This is called a stored procedure or function. The only difference between a process and a function is that a function always returns data to the caller, while a process does not return data.

Function

If you need to perform some operations frequently and return specific data, you can construct these operations into a function.

You can use SQL statements to define functions.

Basic Syntax:

Create or replace function fun_name (argument [in | out | in out] datatype ...)
Return datatype
Is |
-- Variables, record types, and cursors to be defined
Begin
-- Function execution body
Exception
-- Handle exceptions
End;

Return datatype is the type of data to be returned. The IN parameter mark indicates that the value passed to the function does not change during function execution. The OUT mark indicates that a value is calculated by the function and passed to the call statement through this parameter; the in out mark indicates that the value passed to the function can be changed and passed to the call statement.

For example, define a function that returns hello.

Create or replace function hello
Return varchar2
Is
Begin
Return 'Hello world ';
End;

Run Function created. The Function is created successfully.

Function calls are the same as those of Oracle built-in functions. You can use select hello from dual; or PL/SQL statements to call these functions:

Begin

Dbms_output.put_line (hello );

End;

Example: Create a helloworld function with Parameters

Create or replace function helloworld (str varchar2)
Return varchar2
Is
Begin
Return 'hello' | '_' | str;
End;

To call a function, add a parameter after the function name, that is, select helloworld ('World') from dual; To call a Pl/SQL statement, except for adding parameters, it is the same as above.

Example: calculate the total number of employees' salaries in a department

Create or replace function get_sal (dept number)
Return number
Is
V_sum number (10): = 0;
Cursor sal_cursor is select sal from emp where deptno = dept;
Begin
For c in sal_cursor loop
V_sum: = v_sum + c. sal;
End loop;
Return v_sum;
End;

Stored Procedure

Stored procedures can be called by multiple applications, or pass parameters to stored procedures, and return parameters to stored procedures.

Basic syntax

Create or replace procedure pro_name (argument [in | out | in out] datatype ...)
Is |
-- Variables, record types, and cursors to be defined
Begin
-- Function execution body
Exception
-- Handle exceptions
End;

For example, the stored procedure is used to calculate the total salary of a department.

Create or replace procedure get_sal1 (dept number, sumsal out number)
Is
Cursor sal_cursor is select sal from emp where deptno = dept;
Begin
Sumsal: = 0;
For c in sal_cursor loop
Sumsal: = sumsal + c. sal;
End loop;
Dbms_output.put_line (sumsal );
End;

Stored Procedure Call:

Declare
V_sum number (10): = 0;
Begin
Get_sal1 (30, v_sum );
End;

Call format:

CALL | EXCEUTE procedure_name (arg_list );

You can use the show error command to prompt the error location of the source code. Use the user_error data dictionary to view the error location of each stored procedure.

Delete process and Function

Deletion Process

Syntax:

Drop procedure [USER.] procedure_name;

Delete A Function

Syntax:

Drop function [USER.] function_name;