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

Forall statement - syntax, FORALL Statement The FORALL statements instr...

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

Assignment statement in pl sql, Assignment Statement: The assignment s...

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Parameter and keyword description - packages, Parameter and Keyword Descrip...

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Forward declarations - subprograms, Forward Declarations The PL/SQL ne...

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

Using raise_application_error - user-defined exceptions, Using raise_applic...

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Using aggregation on nested tables - sql, Using Aggregation on Nested Table...

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

S, Question 1. Update stock levels when the order is cancelled At times, c...

Question 1. Update stock levels when the order is cancelled At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a

Package utl file in pl/sql, UTL_FILE: The Package UTL_FILE permits you...

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Built-in functions-comparison operators, Built-In Functions The PL/SQL p...

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

Select query, Query: SELECT * FROM EMPLOYEE1; Select 5 columns and ...

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

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