Create a Procedure:
A
procedure is created with the CREATE OR REPLACE PROCEDURE statement. The
simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body
>
END procedure_name;
Where,
- procedure-name specifies the name of the procedure.
- [OR REPLACE] option allows modifying an existing procedure.
- The optional parameter list contains name, mode and types of the parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
- procedure-body contains the executable part.
- The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Example:
The following example creates a
simple procedure that displays the string 'Hello World!' on the screen when
executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
When above code is executed using
SQL prompt, it will produce the following result:
Procedure created.
Executing
a Procedure
A procedure can be called in two
ways:
- Using the EXECUTE keyword
- Calling the name of the procedure from a PL/SQL block
The above procedure named
'greetings' can be called with the EXECUTE keyword as:
EXECUTE greetings;
The above call would display:
Hello World
PL/SQL procedure successfully completed.
The procedure can also be called
from another PL/SQL block:
BEGIN
greetings;
END;
/
The above call would display:
Hello World
PL/SQL procedure successfully completed.
Deleting
a Procedure:
A procedure is deleted with the DROP
PROCEDURE statement. Syntax for deleting a procedure is:
DROP PROCEDURE procedure-name;
So you can drop greetings
procedure by using the following statement:
BEGIN
DROP PROCEDURE
greetings;
END;
/
Creating a Function:
A function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows:CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Where,
·
function-name specifies the name
of the function.
·
[OR REPLACE] option allows modifying an existing
function.
·
The optional parameter list contains name, mode
and types of the parameters. IN represents that value will be passed from
outside and OUT represents that this parameter will be used to return a value
outside of the procedure.
·
The function must contain a return
statement.
·
RETURN clause specifies that data type
you are going to return from the function.
·
function-body contains the
executable part.
·
The AS keyword is used instead of the IS keyword
for creating a standalone function.
Example:
The following example illustrates creating and calling a standalone function. This function returns the total number of CUSTOMERS in the customers table. We will use the CUSTOMERS table which we had created in PL/SQL Variables chapter:Select * from emp;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION empall
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM emp;
RETURN total;
END;
/
Package:
A package will have two mandatory parts:
·
Package specification
·
Package body or definition
Package Specification:
The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.
The following code snippet shows a package specification having a single procedure. You can have many global variables defined and multiple procedures or functions inside a package.
CREATE PACKAGE emp_sal AS
PROCEDURE find_sal(empid emp.id%type);
END emp_sal;
/
When the above code is executed at SQL prompt, it produces following result:Package created.
Package Body:
The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in PL/SQL - Variables chapter.
CREATE OR REPLACE PACKAGE BODY emp_sal AS
PROCEDURE find_sal(empid emp.id%TYPE) IS
emp_sal emp.salary%TYPE;
BEGIN
SELECT salary INTO emp_sal
FROM emp
WHERE id = emp_id;
dbms_output.put_line('Salary: '|| emp_sal);
END find_sal;
END emp_sal;
/
When the above code is executed at SQL prompt, it produces following result:Package body created.
Using the Package Elements
The package elements ( variables, procedures or functions) are accessed with the following syntax:package_name.element_name;Consider, we already have created above package in our database schema, the following program uses the find_sal method of the cust_sal package:
DECLARE
code emp.id%type := &emp_id;
BEGIN
cust_sal.find_sal(code);
END;
/
When the above code is executed at SQL prompt, it prompt to enter customer
ID and when you enter an ID, it displays corresponding salary as follows:Enter value for emp_id: 1
Salary: 3000
PL/SQL procedure successfully completed.
Creating Triggers
The Syntax for creating a trigger is: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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;Where,
·
CREATE [OR REPLACE] TRIGGER trigger_name :
Creates or replace an existing trigger with the trigger_name.
·
{BEFORE | AFTER | INSTEAD OF} : This specifies
when the trigger would be executed. The INSTEAD OF clause is used for creating
trigger on a view.
·
{INSERT [OR] | UPDATE [OR] | DELETE}: This
specifies the DML operation.
·
[OF col_name]: This specifies the column name
that would be updated.
·
[ON table_name]: This specifies the name of the
table associated with the trigger.
·
[REFERENCING OLD AS o NEW AS n]: This allows you
to refer new and old values for various DML statements, like INSERT, UPDATE,
and DELETE.
·
[FOR EACH ROW]: This specifies a row level
trigger, i.e., the trigger would be executed for each row being affected.
Otherwise the trigger will execute just once when the SQL statement is
executed, which is called a table level trigger.
·
WHEN (condition): This provides a condition for
rows for which the trigger would fire. This clause is valid only for row level
triggers.
Example:
To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters: Select * from emp;+----+----------+-----+-----------+----------+
| ID | NAME| AGE | ADDRESS| SALARY|
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
The following program creates a row level trigger for the customers table that would fire for INSERT or
UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary
difference between
the old values and new values:
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
When the above code is executed at SQL prompt, it produces the following result:
Trigger created.Here following two points are important and should be noted carefully:
·
OLD and NEW references are not available for
statement level triggers, rather you can use them for record level triggers.
·
If you want to query the table in the same
trigger, then you should use the AFTER keyword, because triggers can query the
table or change it again only after the initial changes are applied and the
table is back in a consistent state.
·
Above trigger has been written in such a way
that it will fire before any DELETE or INSERT or UPDATE operation on the table,
but you can write your trigger on a single or multiple operations, for example
BEFORE DELETE, which will fire whenever a record will be deleted using DELETE
operation on the table.
About
Tags
Popular
