, , ,

Parameters in PL SQL

A parameter is a variable whose value can be defined at execution time and can be exchanged between the procedure and the calling PL/SQL block. Parameter values can be passed in to the procedure from the calling PL/SQL block and can optionally have their values passed back out of the procedure to the calling PL/SQL block upon the completion of the procedure’s execution
Parameters are declared at the top of the procedure within a set of parentheses. Each parameter declaration includes the following:
  • A name, defined by the developer, and adhering to the rules of object names (discussed earlier).
  • The type of parameter, which will either be IN, OUT, or IN OUT. The default is IN.
  • The datatype. Note that no specification or precision is allowed in parameter datatype declarations. To declare something as an alphanumeric string, you can use VARCHAR2, but you cannot use, for example, VARCHAR2(30).
  • Optionally, a parameter may be provided with a default value. This can be done by using the reserved word DEFAULT, followed by a value or expression that is consistent with the declared datatype for the parameter. The DEFAULT value identifies the value the parameter will have if the calling PL/SQL block doesn’t assign a value.

After each parameter declaration, you may place a comma and follow it with another parameter declaration.
The following is an example of a procedure header that uses parameters:
  ( p_start_date IN DATE DEFAULT SYSDATE
   , p_total_days IN NUMBER
   , p_ship_id IN NUMBER
   , p_cruise_name IN VARCHAR2 DEFAULT ‘Island Getaway’)
… code follows …
This procedure declares four parameters. Each parameter is an IN parameter. Each parameter is assigned a datatype. The parameter p_cruise_name is given a datatype of VARCHAR2; the length cannot be specified in a parameter datatype declaration.
Two of the parameters are assigned default values. The first, p_start_date, uses the Oracle pseudocolumn SYSDATE, and the second, p_cruise_name, is assigned the string, ‘Island Getaway’.
Functions parameters
Functions take parameters, just like procedures do, and just like procedures, a parameter for a function can be an IN, OUT, or an IN OUT parameter. The default parameter type is an IN parameter.
However, unlike a procedure, a function always returns a value through its unique RETURN statement, and this value replaces the original call to the function in the expression that calls the function. Given this, functions are not generally used to pass OUT or IN OUT parameters. Furthermore, the OUT and IN OUT parameter will not work with function calls that are made from SQL statements. For example, consider the following function:
(p_order_amount IN OUT NUMBER)
  p_order_amount := p_order_amount * 1.05;
  RETURN p_order_amount * .05;

This function has an IN OUT parameter. The parameter comes IN as some dollar amount representing an order; it goes OUT with tax added. The function RETURNS the amount of the tax itself, as a NUMBER datatype

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *