--oracle database for programming development-stored procedures and storage functions (1)

1. Stored Procedures and storage functions

Description: A subroutine stored in a database that is called by all user programs called stored procedures, stored functions

Difference: The storage function can return the value of a function through the return clause

(1) Stored procedures

Syntax: Create [or replace] PROCEDURE procedure name (parameter list)

As

Plsql Sub-program body;

How the stored procedure is called:

A) Exec/execute process name ();

b) BEGIN

Process name ();

Process name ();

End

/

Stored procedure with parameters:

Example: Increase the salary for a given employee by 100 dollars, and print the pre-and post-rise wages.

Create a stored procedure with parameters in SQL Developer:

Create or replace PROCEDURE raisesalary (Eno in number)  as--Define a variable save the salary before the rise  psal emp.sal%type;begin  -- Get the employee's pre-rise salary  Select Sal into Psal from EMP where Empno=eno;    --Give the employee up to  update emp set sal=sal+100 where Empno=eno;    -Note: Typically not in stored procedures or stored functions, commit and rollback.    --Print  Dbms_output. Put_Line (' Pre-rise: ' | | psal| | ' After the rise: ' | | (psal+100)); end;/

To call the PLSQL program using SQL Developer debugging:

If a permission is missing, you can use the database control to give the current user permission:

(2) Storage function

Grammar:

create [or replace] function name (parameter list)

return function value type

As

Plsql Sub-program body;

Example: Querying an employee's annual income

Create or replace FUNCTION queryempincome (Eno in number) return Numberas  --Define a variable save the employee's salary and bonus  psal emp.sal%type;< C2/>pcomm Emp.comm%type;begin  --Get employee's monthly salary and bonus  select Sal,comm into Psal,pcomm from EMP where Empno=eno;    --Return PSAL*12+NVL (pcomm,0) directly to the annual income  ; end;/

2. Use stored procedures and a simple non-essential principle of using stored functions: If there is only one return value, use the stored function, and if there are multiple return values, the stored procedure is used;

can have multiple out parameters!

Create or replace PROCEDURE queryempinform (Eno in number,                                           pename out varchar2,                                           psal out number,                                           pjob out Varch AR2) Asbegin  --Get employee's name, monthly salary, position  select Ename,sal,job into Pename,psal,pjob from EMP where empno=eno;end;

Problem thinking:

A) If you query all of the field information for someone (and the fields are more)?

b) How can I return more than one result set that matches a condition and the out parameter returns a result set?

--oracle database for programming development-stored procedures and storage functions (1)