Module 02: Creating Functions

Creating Functions

01. What is Function?

Function:

a. Is a named PL/SQL block that returns a value.

b. Can be stored in the database as a schema object for repeated execution

c. Is called as part of an expression or is used to provide a parameter value for another subprogram.

d. Can be grouped into PL/SQL packages Write down the use of Variables.

02. Write down the consideration about the CREATE FUNCTION statement.

CREATE FUNCTION Statement:

a. The REPLACE option indicates that if the function exists, it is dropped and replaced with the new version that is created by the statement.

b. The RETURN data type must not include a size specification.

c. The PL/SQL block starts with a BEGIN after the declaration of any local variables and ends with an END, optionally followed by the function_name.

d. There must be at least one RETURN expression statement.

e. You cannot reference host or bind variables in the PL/SQL block of a stored function. 

 

03. Write down the differences between Procedures and Functions.

Differences:

04. Write down the using different methods for Executing Functions.

Executing Functions:

a. Invoke the Function as an expression or as a parameter value

b. As a PL/SQL expression, get the results using host variables

c. As a PL/SQL expression, get the results using a local variables

d. Use a parameter to another subprogram

e. Use in SQL statements

05. Write down the Advantage of User-defined Functions in SQL statements.

Advantage:

a. Can extend SQL where activities are too complex, too awkward, or unavailable with SQL.

b. Can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application.

c. Can manipulate data values.

06. Write down the Restrictions When Calling Functions from SQL Expressions.

User-defined Functions that are callable from SQL Expressions must: 

a. Be stored in the database.

b. Accept only IN parameters with valid SQL data types, not PL/SQL-specific types .

c. Return valid SQL data types, not PL/SQL-specific types.

When Calling Functions in SQL statements: 

a. You must own the function or have the EXECUTE privilege.

b. You may need to enable the PARALLEL_ENABLE keyword to allow a parallel execution of the SQL statement.

07. Write down the controlling side effects When Calling Functions from SQL Expressions.

Controlling side effects:

a. A SELECT statement cannot contain DML statements.

b. An UPDATE or DELETE statement on a table T cannot query or contain DML on the same table T.

c. SQL statements cannot END transactions (that is, cannot execute COMMIT or ROLLBACK operations).