Parameter default values, PL-SQL Programming

Assignment Help:

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, ...


Related Discussions:- Parameter default values

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA...

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

3cx pbx sync windows service, 3CX PBX sync Windows Service Project Descr...

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Cursors, What is Cursors how to use it in Real time application ?

What is Cursors how to use it in Real time application ?

Fetch statement - syntax, FETCH Statement The FETCH statement retrieve ...

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

Between and not between operator in sql, BETWEEN and NOT BETWEEN Operator i...

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

Oracle 10g new features and differences , Oracle 10G new features:- Au...

Oracle 10G new features:- Automatic Database Diagnostic Monitor System Advancements - these methods will provides several methods for extracting reports through the Automatic

Using lock table, Using LOCK TABLE You use the LOCK TABLE statement to...

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Implicit cursor attributes, Implicit Cursor Attributes The Implicit cur...

Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu

Controlling autonomous transactions, Controlling Autonomous Transactions ...

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

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