Subprograms, PL-SQL Programming

Subprograms

The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subprogram is like a miniature program, start with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:

PROCEDURE award_bonus (emp_id NUMBER) IS

bonus REAL;

comm_missing EXCEPTION;

BEGIN

SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

IF bonus IS NULL THEN

RAISE comm_missing;

ELSE

UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN comm_missing THEN

...

END award_bonus;

If called, this procedure accepts an employee number. And it uses the number to select the employee's payment from a database table and, at the similar time, calculate a 15% bonus. Now, it checks the bonus amount. If the bonus is zero or null, an exception is raised; or else, the employee's payroll record is updated.

Posted Date: 10/2/2012 1:47:50 AM | Location : United States







Related Discussions:- Subprograms, Assignment Help, Ask Question on Subprograms, Get Answer, Expert's Help, Subprograms Discussions

Write discussion on Subprograms
Your posts are moderated
Related Questions
Synonyms You can create the synonyms to provide location transparency for the remote schema objects like tables, views, sequences, stand-alone subprograms, and packages. Though,

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

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

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into