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
Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

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

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

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

what is the use of declare keyword

which operation is used if we are interested in only certain columns of a table?