Use of database stored procedures and stored functions in Oracle

Stored Procedures and stored functions refer to the stored procedures and stored functions that are stored in the database and called by all user programs..
The stored procedure does not return values. Stored functions return values

Create a stored procedure
Use the create procedure command to CREATE a stored PROCEDURE and function.

Syntax:
Create [or replace] PROCEDURE process name (parameter list)
AS
PLSQL subprogram body;

Example of Stored Procedure: 10% of the original salary of the specified employee
 
/*
Pay 10% of the original salary for the specified employee, 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 current employee salary
Begin
-- Query the employee's salary
Select sal into pSal from emp where empno = empid;
-- Raise the employee's salary
Update emp set sal = sal * 1.1 where empno = empid;
-- Print the salary before and after the increase
Dbms_output.put_line ('employee ID: '| empid |' before salary increase
'| Psal | 'salary increase' | psal * 1.1 );
End;
1/

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

Employee No.: 7369 before salary increase
800 880 after salary increase

Method 2
Set serveroutput on
Begin
Raisesalary (7369 );
End;
/

PL/SQL procedure successfully completed

 
Storage Functions
A Function is a named storage program that can contain parameters and return a calculated value. The structure of a function is similar to that of a process, but a RETURN clause must be provided to RETURN the function value. Function Description specifies the function name, result value type, and parameter type.

Syntax for creating a storage function:

CREATE [or replace] FUNCTION name (parameter list)
RETURN function value type
AS
PLSQL subprogram 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; -- defines the variable to save the employee's salary
PComm number; -- defines variables to save employee bonuses
Begin
Select sal, comm into psal, pcomm from emp where empno = empid;
Return psal * 12 + nvl (pcomm, 0 );
End;
/

Function created

L function call

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 ('sale is: '| queryEmpSalary (7934 ));
End;
/

Salary is: 15600

PL/SQL procedure successfully completed

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

Trigger type
Statement-level triggers
The statement is executed once before or after the specified operation, regardless of the number of rows affected by the statement.

ROW-Level Trigger (for each row)
Each record that triggers a statement is triggered. Use old and new pseudo-record variables in row-level triggers to identify the status of values.

Create a trigger
CREATE [or REPLACE] TRIGGER name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column name]}
ON Table Name
[For each row [WHEN (condition)]
PLSQL Block

Example 1: insert data to the database during non-work hours
SQL> create or replace
Trigger securityEmp
Before insert on emp
Declare
Begin
If to_char (sysdate, 'day') in ('thurs', 'satur', 'sunday ')
Or to_number (to_char (sysdate, 'hh24') not between 8 and 18 then
Raise_application_error (-20001, 'data cannot be inserted during non-work hours. ');
End if;
End;
/

Trigger created

Trigger statement and pseudo record variable value

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)

Example2: Confirm data (CheckEmpTableSalThe modification value of 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, 'salary after update is lower than before Update ');
End if;
End;
/

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

Update emp set sal = 260 where empno = 7499

ORA-20001: salary after update is smaller than before update
ORA-06512: In "SCOTT. CHECKSAL", line 4
ORA-04088: An error occurred while executing the trigger 'Scott. CHECKSAL'

Trigger Summary
Triggers can be used
• Data Validation
• Implement complex security checks
• Perform audits and track data operations performed on tables.

Query triggers, processes, and functions
• Select * from user_triggers;
• Select * from user_source;