Subprograms, PL-SQL Programming

What Are Subprograms?

The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedures and functions. Normally, you use a procedure to perform an action and a function to calculate a value. Similar to unnamed or anonymous PL/SQL blocks, the subprograms have a declarative section, an executable part, and an optional exception-handling part. The declarative part contains the declarations of types, constants, cursors, variables, exceptions, & nested subprograms. These items are cease to exist and local whenever you exit the subprogram. The executable section contains statements which assign control execution, values, and manipulate Oracle data. The exception-handling section contains exception handlers that deal with the exceptions raised during the execution.

Consider the procedure below named debit_account that debits a bank account:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS

old_balance REAL;

new_balance REAL;

overdrawn EXCEPTION;

BEGIN

SELECT bal INTO old_balance FROM accts

WHERE acct_no = acct_id;

new_balance := old_balance - amount;

IF new_balance < 0 THEN

RAISE overdrawn;

ELSE

UPDATE accts SET bal = new_balance

WHERE acct_no = acct_id;

END IF;

EXCEPTION

WHEN overdrawn THEN

...

END debit_account;

When called or invoked, this procedures accept an account number and a debit sum. It uses the account number to select the account balance from the accounts database table. Then, it uses the debit quantity to calculate a new balance. If the new balance is less than 0, an exception is raised; or else, the bank account is updated.

Posted Date: 10/5/2012 5:21:31 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
Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

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

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars