Procedures in pl/sql, PL-SQL Programming


The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below:

PROCEDURE name [(parameter[, parameter, ...])] IS

[local declarations]


executable statements


exception handlers]

END [name];

Where the parameter stand for the following syntax:

parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype_name

[{:= | DEFAULT} expression]

The datatype of a parameter cannot be constrained. For illustration, the declaration below of acct_id is illegal as the datatype CHAR is size-constrained:

PROCEDURE reconcile (acct_id CHAR(5)) IS ... - illegal

Though, you can use the workaround below to size-constrain parameter types ultimately:


temp CHAR(5);


PROCEDURE reconcile (acct_id Char5) IS ...

The procedure has 2 parts: the specification and the body. The procedure specification starts with the keyword PROCEDURE and ends with the procedure name or the parameter list. The Parameter declarations are elective. The Procedures that take no parameters are written without the parentheses.

The procedure body starts with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has 3 parts: a declarative section, an executable section, & an optional exception-handling section.

The declarative section contains the local declarations, that are placed between the keywords IS and BEGIN. The keywords DECLARE, that introduces the declarations in an anonymous PL/SQL block, and is not used. The executable section contains statements that are placed between the keywords BEGIN & EXCEPTION (or END). At least one statement should appear in the executable section of a procedure. The NULL statements meet this necessity. The exception-handling section contains the exception handlers that are placed between the keywords EXCEPTION and END. Consider the procedure raise_salary that increases the salary of an employee by a given amount:

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS

current_salary REAL;

salary_missing EXCEPTION;


SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;


UPDATE emp SET sal = sal + amount

WHERE empno = emp_id;




INSERT INTO emp_audit VALUES (emp_id, 'No such number');

WHEN salary_missing THEN

INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END raise_salary;

Whenever called, this procedure accepts an employee number and the salary increase sum. It uses the employee number to select the present salary from the emp database table. When the employee number is not found or if the current salary is null, an exception is raised. Or else, the salary is updated.

The procedure is known as the PL/SQL statement. For illustration, you might call the procedure raise_salary as shown below:


emp_id NUMBER;

amount REAL;



raise_salary(emp_id, amount);

Posted Date: 10/5/2012 5:23:17 AM | Location : United States

Related Discussions:- Procedures in pl/sql, Assignment Help, Ask Question on Procedures in pl/sql, Get Answer, Expert's Help, Procedures in pl/sql Discussions

Write discussion on Procedures in pl/sql
Your posts are moderated
Related Questions
Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T