Subprograms, PL-SQL Programming

Subprograms

The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subprogram is like a miniature program, start with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:

PROCEDURE award_bonus (emp_id NUMBER) IS

bonus REAL;

comm_missing EXCEPTION;

BEGIN

SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

IF bonus IS NULL THEN

RAISE comm_missing;

ELSE

UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN comm_missing THEN

...

END award_bonus;

If called, this procedure accepts an employee number. And it uses the number to select the employee's payment from a database table and, at the similar time, calculate a 15% bonus. Now, it checks the bonus amount. If the bonus is zero or null, an exception is raised; or else, the employee's payroll record is updated.

Posted Date: 10/2/2012 1:47:50 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
Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur

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


RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

FORALL Statement The FORALL statements instruct the PL/SQL engine to bulk-bind the input collections before sending them to the SQL engine. Though the FORALL statement consists

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Advantages of Exceptions Using the exceptions for the error handling has many benefits. Without an exception handling, every time you issue a command, you should ensure for th