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
BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

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

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.

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.