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
Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

FOR-LOOP While the number of iterations through a WHILE loop is unknown till the loop completes, then the number of iterations through a FOR loop is known before the loop is ent

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Define basic operators of relational algebra with an example each

Project Description: I have two types of data sources. One that is a list in SharePoint and another that is an access desktop database. The access desktop database is fairly com