Oracle Learning Note 18--storage functions and stored procedures (PL/SQL subroutines)

PL/SQL subroutine it contains functions and procedures. This function refers to a user-defined function. And the system functions are different. subroutines usually complete a specific function PL/SQL block. , can be called multiple times by different applications . Oracle provides the ability to store PL/SQL programs in a database and to execute it no matter where.

This is called a stored procedure or a function.

The only difference between a procedure and a function is that the function always returns data to the caller. The process does not return a number of data .

Function

assume that a user often runs certain operations and needs to return specific data. You can then construct these operations into a function.

Ability to define functions using SQL statements.

The basic syntax:

Create or Replace function fun_name (argument [in | out | on out]datatype ...)
return datatype
is | As
-variables that need to be defined. The record type. Cursor
Begin
--The running body of the function
exception
--Handle the exception that occurs
End;

The return datatype is the type of data to be returned. The in indicator indicates that the value passed to the function does not change while the function is running; The out token indicates that a value is evaluated in the function and passed to the calling statement by that parameter; The in out token indicates that the value passed to the function can vary and be passed to the calling statement.

Example: Define a function that returns hello

create or replace function Hello
return varchar2 
is
Begin
       return ' Hello world ';
End;

run, function created, the functions are created successfully.

function call. Calls to Oracle built-in functions can also be invoked using the select Hello from dual, or by using the PL/SQL statement:

Begin

Dbms_output.put_line (hello);

End;

Example: Creating a HelloWorld function with a number of parameters

Create or Replace function HelloWorld (str varchar2)
return VARCHAR2
Is
Begin
Return ' Hello ' | | ' _ ' | | STR;
End;

The call to the function is preceded by the function name, which is: select HelloWorld (' World ') from dual; the call to use PL/SQL is the same as above, not in the same way as above.

Example: a function that asks for the total wage of an employee in a department

Create or Replace function get_sal (dept number)
return number
Is
V_sum Number (10): = 0;
Cursor Sal_cursor is a 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 Procedures

Stored procedures that can be called by multiple applications. can also pass parameters to the stored procedure. Returns the number of references to the stored procedure.

The basic syntax

Create or Replace procedure pro_name (argument [in | out | on out]datatype ...) 
is | as
--variables that need to be defined, record types, cursors
begin
--The running body of the function
Exception
--Handle the exception that occurred
end;

Example: Using a stored procedure. The sum of the wages of the department

create or replace procedure Get_sal1 (dept number, sumsal out  number)
is
  & nbsp    cursor sal_cursor is a 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;

Call to the stored procedure:

Declare
V_sum Number (10): = 0;
Begin
Get_sal1 (v_sum);
End;

called format:

Call | Exceute procedure_name (arg_list);

The ability to use the show Error command to prompt for the wrong location of the source code.

Use the User_error data dictionary to see the wrong locations for each stored procedure.

Delete Procedures and functions

Delete procedure

The syntax is as follows:

DROP Procedure[user.] procedure_name;

Delete a function

The syntax is as follows:

DROP FUNCTION [USER.] Function_name;

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

Oracle Learning Note 18--storage functions and stored procedures (PL/SQL subroutines)