The Use of Database Stored Procedures and Stored Functions in Oracle

  • 2021-07-24 11:55:16
  • OfStack

Stored procedures and stored functions refer to subroutines stored in the database for all user programs to call, called stored procedures and stored functions.
The stored procedure did not return a value. Stored function has return value

Create a stored procedure
Create stored procedures and stored functions with the CREATE PROCEDURE command.

Syntax:
create [or replace] PROCEDURE Procedure Name (Parameter List)
AS
PLSQL subroutine body;

Example of stored procedure: Increasing the salary of a specified worker by 10% based on the original salary

/*
Increase the salary by 10% on the basis of the original salary for the designated employees, and print the salary before and after the salary
*/
SQL > create or replace procedure raiseSalary(empid in number)
as
pSal emp. sal% type; --Save the employee's current salary
begin
--Query the employee's salary
select sal into pSal from emp where empno=empid;
-Give the employee a salary increase
update emp set sal = sal*1.1 where empno=empid;
-Print the salary before and after the salary increase
dbms_output.put_line ('Employee number:' empid 'before salary increase
'psal*1. 1 after' psal 'salary increase);
end;
1 /

Procedure created
--Stored procedure calls
-Method 1
SQL > set serveroutput on
SQL > exec raisesalary(7369);

Employee number: 7369 before salary increase
880 after 800 salary increase

Method 2
set serveroutput on
begin
raisesalary(7369);
end;
/

PL/SQL procedure successfully completed


Storage function
Function (Function) is a stored program named for 1, which can take parameters and return a calculated value of 1. Functions and procedures are similarly structured, but must have an RETURN clause to return the function value. Function description specifies the function name, the type of the result value, and the parameter type.

Establish syntax for storing functions:

CREATE [OR REPLACE] FUNCTION function name (argument list)
RETURN function value type
AS
PLSQL subroutine body;


Example: Query the annual income of an employee.
SQL > /**/
/*
Query the total income of an employee
*/
create or replace function queryEmpSalary(empid in number)
return number
as
pSal number; --Define variables to hold employees' salaries
pComm number; --Define variables to hold employee bonuses
begin
select sal,comm into psal,pcomm from emp where empno = empid;
return psal*12+nvl(pcomm,0);
end;
/

Function created

Call of l function

SQL > declare
v_sal number;
begin
v_sal:=queryEmpSalary(7934);
dbms_output.put_line('salary is:'|| v_sal);
end;
/

salary is:15600

PL/SQL procedure successfully completed

SQL > begin
dbms_output.put_line('salary is:'|| queryEmpSalary(7934));
end;
/

salary is:15600

PL/SQL procedure successfully completed


Trigger
A database trigger is a stored PL/SQL program associated with a table. Whenever a specific data manipulation statement (Insert, update, delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

Type of trigger
Statement level trigger
Executes once before or after the specified action statement, regardless of how many lines the statement affects.

Row Level Trigger (FOR EACH ROW)
Every record that the trigger statement acts on is triggered. old and new pseudo-record variables are used in row-level triggers to identify the status of values.

Create Triggers
CREATE [or REPLACE] TRIGGER Trigger Name
{BEFORE | AFTER}
{DELETE INSERT UPDATE [OF column name]}
ON table name
[FOR EACH ROW [WHEN (condition)]]
PLSQL Block

Example 1: Limiting non-working hours to insert data into the database
SQL > create or replace
trigger securityEmp
before insert on emp
declare
begin
if to_char (sysdate, 'day') in ('Thursday', 'Saturday', 'Sunday')
or to_number(to_char(sysdate,'hh24'))not between 8 and 18 then
raise_application_error (-20001, 'Cannot insert data during non-working hours. ');
end if;
end;
/

Trigger created

Trigger statement and the value of pseudo-record variable

Trigger statement

:old

:new

Insert

All fields are empty (null)

Data to be inserted

Update

Update the value of the previous row

Updated value

delete

Delete the value of the previous row

All fields are empty (null)

Example 2: Confirm data (check that the modified value of sal in the emp table is not lower than the original value)
SQL > create or replace trigger checkSal
before update of sal on emp
for each row
declare
begin
if :new.sal < :old.sal then
raise_application_error (-20001, 'Updated salary is smaller than before');
end if;
end;
/

Trigger created
Results after running:
SQL > update emp set sal=260 where empno=7499;

update emp set sal=260 where empno=7499

ORA-20001: Updated salary is smaller than before
ORA-06512: In "SCOTT. CHECKSAL", line 4
ORA-04088: Error during execution of trigger 'SCOTT. CHECKSAL'

Trigger summary
Triggers can be used for
Data validation
Implement complex security checks
Do audits, track data operations on tables, etc.

Query Triggers, Procedures, and Functions
• Select * from user_triggers;
• Select * from user_source;


Related articles: