Subprograms, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Subprograms

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa...

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Cursor variables, Cursor Variables Similar to a cursor, cursor variable...

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Check constraints in sql, CHECK Constraints in SQL A CHECK constraint ...

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

I need a ppd-cpa or filesharing site, PPD , CPA, Filesharing Site Projec...

PPD , CPA, Filesharing Site Project Description: This is very easy I need a PPD , CPA, Filesharing Site. [PPD] stands for (pay per download) example hotsharecash [Files

Write an anonymous block that contains a pl/sql function, a. Write an anon...

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Data types in sql - decimal, Data Types in SQL - Decimal DECIMAL, NUME...

Data Types in SQL - Decimal DECIMAL, NUMERIC, REAL, FLOAT and various other terms for various sets of rational numbers. When these key words are specified for the declared typ

Organic evolution, Organi c Evolution Evolution is a slow continuou...

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

Using for update, Using FOR UPDATE If you declare a cursor which will ...

Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

Like operator-comparison operators, LIKE Operator You use the LIKE opera...

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd