SatyaCodes

Procedure

SQL -Procedure

A pl-sql Procedure does not return any value. Procedure has two sections;

  • Declaration of the procedure: Declaration of procedure always start with a keyword create ends with last variable parameters.

  • Body of the procedure: Body of procedure starts with a keyword called is or as and ends with end statement.

Example

create or replace procedure p1(a in number, b out number, c out number, d out number)
is
	begin;
	    select ename, sal, deptno, into b, c, d from emp where empno=a;
	end;

Function

A PL/SQL Function is a self control block which is used to perform some specific task. function must always return a value, but a procedure may or may not return a value.

create or replace function add(a number, b number)
return number
is
number;
begin;
 	a:=10;
  	b:=20;
  	c=a+b;
return c;
end;

Package is a collection of sub-programs that means function or procedure

create package package_name AS
   procedure procedure_name(parameter);
end package_name;

Cursors

A Cursors is a temporary work area created in the system memory when a SQL statement is executed. It is a temporary memory which is used to fetch more than one record at a time from existing table.

  • Implicit cursor cursor is perform by the system internally

  • Explicit cursor This type of cursor is performed by the user manually or programatically

Steps to perform cursor

Steps Syntax
Declare the cursor open cursor_name;
Open the cursor open cursor_name;
Fetch the record from the cursor fetch cursor_name into variables;
Close the cursor close cursor_name;

Declare the cursor

declare
a emp %rowtype;
cursor c is select * from emp where depno=&deptno;
begin
open c;
loop fetch c into a;
if c % found then
dbms_output.put_line(a.empno || ' ' a.ename || ' ' || a.sal);
else
exit;
end if;
end loop;
close c;
end;

Trigger

Trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed

Purpose of Triggers

Triggers can be written for the following purposes:

  • Generating some derived column values automatically

  • Enforcing referential integrity

  • Event logging and storing information on table access Auditing

  • Synchronous replication of tables

  • Imposing security authorizations

  • To avoid invalid transactions

  • To generate the resulting data automatically.

Part of Trigger

A database trigger has 5 parts.

  • Trigger timing

  • Trigger event or statement

  • Trigger level

  • Trigger restriction

  • Trigger body

CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END;

Write a trigger to stop delete operation on emp_table

create or replace trigger mytrigger
before
delete
on emp
begin
raise_application_error(-20000, 'sorry we can not delete any record from this table');
end;

References

Search