(Continued from previous part...)

Can a function take OUT parameters. If not why?

yes, IN, OUT or IN OUT.

Can the default values be assigned to actual parameters?

Yes. In such case you don’t need to specify any value and the actual parameter will take the default value provided in the function definition.

What is difference between a formal and an actual parameter?

The formal parameters are the names that are declared in the parameter list of the header of a module. The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.

What are different modes of parameters used in functions and procedures?

There are three different modes of parameters: IN, OUT, and IN OUT.

IN - The IN parameter allows you to pass values in to the module, but will not pass anything out of the module and back to the calling PL/SQL block. In other words, for the purposes of the program, its IN parameters function like constants. Just like constants, the value of the formal IN parameter cannot be changed within the program. You cannot assign values to the IN parameter or in any other way modify its value.

IN is the default mode for parameters. IN parameters can be given default values in the program header.

OUT - An OUT parameter is the opposite of the IN parameter. Use the OUT parameter to pass a value back from the program to the calling PL/SQL block. An OUT parameter is like the return value for a function, but it appears in the parameter list and you can, of course, have as many OUT parameters as you like.

Inside the program, an OUT parameter acts like a variable that has not been initialised. In fact, the OUT parameter has no value at all until the program terminates successfully (without raising an exception, that is). During the execution of the program, any assignments to an OUT parameter are actually made to an internal copy of the OUT parameter. When the program terminates successfully and returns control to the calling block, the value in that local copy is then transferred to the actual OUT parameter. That value is then available in the calling PL/SQL block.

IN OUT - With an IN OUT parameter, you can pass values into the program and return a value back to the calling program (either the original, unchanged value or a new value set within the program). The IN OUT parameter shares two restrictions with the OUT parameter:

An IN OUT parameter cannot have a default value.

An IN OUT actual parameter or argument must be a variable. It cannot be a constant, literal, or expression, since these formats do not provide a receptacle in which PL/SQL can place the outgoing value.

Difference between procedure and function.

A function always returns a value, while a procedure does not. When you call a function you must always assign its value to a variable.

Can cursor variables be stored in PL/SQL tables. If yes how. If not why?

Yes. Create a cursor type - REF CURSOR and declare a cursor variable of that type.
DECLARE
/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */
company_curvar company_curtype;

/* Declare a record with same structure as cursor variable. */
company_rec company%ROWTYPE;
BEGIN
/* Open the cursor variable, associating with it a SQL statement. */
OPEN company_curvar FOR SELECT * FROM company;

/* Fetch from the cursor variable. */
FETCH company_curvar INTO company_rec;

/* Close the cursor object associated with variable. */
CLOSE company_curvar;
END;

(Continued on next part...)