Procedures in pl/sql, PL-SQL Programming

Procedures 

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]

BEGIN

executable statements

[EXCEPTION

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:

DECLARE

temp CHAR(5);

SUBTYPE Char5 IS temp%TYPE;

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;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;

ELSE

UPDATE emp SET sal = sal + amount

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

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:

DECLARE

emp_id NUMBER;

amount REAL;

BEGIN

...

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
Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Effects of NULL The numeric variable X, perhaps of type INTEGER, might be assigned NULL. In that case the result of evaluating X + 1 is NULL, and so SET Y = X + 1 assigns NULL

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

Parameter and Keyword Description: select_item: This select_item is a value returned by the SELECT statement, and then assigned to the equivalent variable or field in the

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS