PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

To check all the procedures, packages, triggers and functions in the database.


SQL> select count(*),object_type from dba_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER') group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
161 PROCEDURE
1320 PACKAGE
627 TRIGGER
305 FUNCTION

Procedure:

procedures are used to perform specific actions
pass values in and out by using an argument list
can be called from within other programs using the CALL command.

Example:

create or replace procedure test_procedure(dept number, name varchar2, loc varchar2)
as
begin
insert into scott.dept values(dept,name,loc);
end;
/

SQL> select * from scott.dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
50 FINANCE        INDIA

How to execute a procedure ?

exec test_procedure(50,'FINANCE','INDIA');

Query to see all procedures:

select object_name from dba_objects where object_type='PROCEDURE';


Packages:

packages are collections of functions and procedures
each package should consist of two objects:
*package specification
*package body

Built-in packages:

Oracle database comes with several built-in PL/SQL packages that provide:

-administration and maintain utilities
-extended functionality
-use to DESCRIBE command to view subprograms
-DBMS_STAT: gathering, viewing, and modifying optimizer statistics
-DBMS_OUTPUT:generating output from PL/SQL
-DBMS_SESSION:accessing the ALTER SESSION and SET ROLE statements
-DBMS_SHARED_POOL:managing the shared pool (for example flusing it)
-DBMS_UTILITY:getting time, CPU time and version information
-DBMS_SCHEDULER:scheduling functions and procedures that are callable from PL/SQL
-DBMS_REDEFINITION:redefining objects online
-UTL_FILE:reading and writing to operating system files from PL/SQL.

SQL> set serveroutput on --------To check the description or any error message
SQL> exec dbms_output.PUT_LINE('This is mqm message');
This is mqm message

How to execute to packages ?

SQL> set serveroutput on 
SQL> exec dbms_output.PUT_LINE('This is mqm message');

Query to see all packages?

select object_name from dba_objects where object_type='PACKAGES';


Triggers:

triggers are PL/SQL code objects that are stored in the database and that automatically run or "fire" when something happens. The oracle database allows many actions to serve as triggering events including an insert into a table, a user logging in to the database and someone trying to drop a table or change audit settings.


Function:

create or replace function compute_tax (salary number)
return number
as begin
if salary<5000 then
return salary*.15;
else
return salary*.33;
end if;
end;
/

How to execute a function ?

select sysdate from dual;
select compute_tax(4000) from dual;

Query to see to functions?

select object_name from dba_objects where object_type='FUNCTION';