Parameter default values, PL-SQL Programming

Parameter Default Values

As the illustration below shows, you can initialize the IN parameters to the default values.

In that way, you can pass various numbers of actual parameters to a subprogram, accommodating or overriding the default values as you please. Furthermore, you can add new proper parameters without having to change every call to the subprogram.

PROCEDURE create_dept (

new_dname CHAR DEFAULT 'TEMP',

new_loc CHAR DEFAULT 'TEMP') IS

BEGIN

INSERT INTO dept

VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);

...

END;

If an actual parameter is not passed, then the default value of its corresponding formal

parameter is used. Consider the calls below to create_dept:

create_dept;

create_dept('MARKETING');

create_dept('MARKETING', 'NEW YORK');

The first call passes no actual parameters; therefore both the default values are used. The second call passes only an actual parameter; therefore the default value for new_loc is used.

The third call passes the two actual parameters, so neither of the default value is used. Typically, you can use the positional notation to override the default values of proper parameters. Though, you cannot skip a formal parameter by leaving out its actual parameter. For illustration, the call below incorrectly relates the actual parameter 'NEW YORK' with the formal parameter new_dname:

create_dept('NEW YORK'); -- incorrect

You cannot answer the problem by leaving a placeholder for the actual parameter. For illustration below, the call is illegal:

create_dept(, 'NEW YORK'); -- illegal

In such cases, you must use named notation, which is as shown below:

create_dept(new_loc => 'NEW YORK');

You also not assign a null to an uninitialized formal parameter by leaving out its real parameter. For illustration, given the declaration

DECLARE

FUNCTION gross_pay (

emp_id IN NUMBER,

st_hours IN NUMBER DEFAULT 40,

ot_hours IN NUMBER) RETURN REAL IS

BEGIN

...

END;

The function call below does not assign a null to ot_hours:

IF gross_pay(emp_num) > max_pay THEN ... - illegal

Rather, you should pass the null explicitly, as in

IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...

Or you can initialize ot_hours to NULL, which is as shown:

ot_hours IN NUMBER DEFAULT NULL;

Finally, if creating a stored subprogram, you cannot use the host variables in the DEFAULT clause. The SQL Plus below illustrates causes a bad bind variable error as at the time of creation, num is merely a placeholder whose value may change:

SQL> VARIABLE num NUMBER

SQL> CREATE FUNCTION gross_pay (emp_id IN NUMBER DEFAULT :num, ...

Posted Date: 10/5/2012 5:35:34 AM | Location : United States







Related Discussions:- Parameter default values, Assignment Help, Ask Question on Parameter default values, Get Answer, Expert's Help, Parameter default values Discussions

Write discussion on Parameter default values
Your posts are moderated
Related Questions
Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Avoid the NOT NULL Constraint In the PL/SQL, using the NOT NULL constraint incur a performance cost. Consider the illustration as shown below: PROCEDURE calc_m IS m NUMB

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Due to an increase in overhead costs, the buying price of all items needs to be increased. Management wants to see a report before deciding how much each product will go up. Add to

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

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

Updating Objects: To change the attributes of objects in an object table, you can use the UPDATE statement, as the illustration below shows: BEGIN UPDATE persons p SET p

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam