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
Data Abstraction The Data abstraction extracts the important properties of data while ignoring the not necessary details. Once you design a data structure, you can fail to reme

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

#quesWrite a cursor to open an employee database and fetch the employee record whose age is greater than 45.tion..

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders that have not been shipped within a day of the order

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

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

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

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