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
Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

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

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


IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do