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
