Book Image

Oracle Advanced PL/SQL Developer Professional Guide

By : Saurabh K. Gupta
Book Image

Oracle Advanced PL/SQL Developer Professional Guide

By: Saurabh K. Gupta

Overview of this book

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. Server-side PL/SQL is stored and compiled in the Oracle Database and runs within the Oracle executable. With this guide Oracle developers can work towards accomplishing Oracle 11g Advanced PL/SQL Professional certification, which is the second milestone for developers working at the Associate level. The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers.The book gives a deep insight that will help transform readers from mid-level programmers to professional database developers. It aims to cover the advanced features of PL/SQL for designing and optimizing PL/SQL code.This book starts with an overview of PL/SQL as the programming database language and outlines the benefits and characteristics of the language. The book then covers the advanced features that include PL/SQL code writing using collections, tuning recommendations using result caching, implementing VPD to enforce row level security, and much more. Apart from programming, the book also dives deep into the usage of the development tool SQL Developer, employing best practices in database environments and safeguarding the vulnerable areas in PL/SQL code to avoid code injection.
Table of Contents (22 chapters)
Oracle Advanced PL/SQL Developer Professional Guide
Credits
Foreword
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Index

Functions


Like a procedure, a function is also a derivative of a PL/SQL block structure which is physically stored within a database. Unlike procedures, they are the "workforce" in PL/SQL and meant for calculative and computational activities in the applications.

The characteristics of functions are as follows:

  • Functions can be called from SQL statements (SELECT and DMLs). Such functions must accept only IN parameters of valid SQL types. Alternatively, a function can also be invoked from SELECT statements if the function body obeys the database purity rules.

  • Functions can accept parameters in all three modes (IN, OUT, and IN OUT) and mandatorily return a value. The type of the return value must be a valid SQL data type (not be of BOOLEAN, RECORD, TABLE, or any other PL/SQL data type).

The syntax for a function is as follows:

CREATE [OR REPLACE] FUNCTION [Function Name] [Parameter List]
RETURN [Data type]
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC | PARALLEL_ENABLED | PIPELINES]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements] 
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

The standalone function, F_GET_DOUBLE, accepts a single argument and returns its double:

/*Create the function F_GET_DOUBLE*/
CREATE OR REPLACE FUNCTION F_GET_DOUBLE (P_NUM NUMBER) 
RETURN NUMBER   /*Specify the return data type*/
IS
/*Declare the local variable*/
   L_NUM NUMBER;
BEGIN
/*Calculate the double of the given number*/
   L_NUM := P_NUM * 2;
/*Return the calculated value*/
   RETURN L_NUM;
END;
/

Function created.

Function—execution methods

As a common feature shared among the stored subprograms, functions can be invoked from a SQL*Plus environment and called from a PL/SQL as a procedural statement.

The following code snippet demonstrates the execution of a function from a SQL*Plus environment and its return value have been captured in a session bind variable:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a session variable M_NUM to hold the function output*/
SQL> VARIABLE M_NUM NUMBER;

/*Function is executed and output is assigned to the session variable*/
SQL> EXEC :M_NUM := F_GET_DOUBLE(10);

PL/SQL procedure successfully completed.

/*Print the session variable M_NUM*/
SQL> PRINT M_NUM

M_NUM
----------
20

Now, we will see the function execution from an anonymous PL/SQL block as a procedural statement:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

SQL>DECLARE
      M_NUM NUMBER;
    BEGIN
      M_NUM := F_GET_DOUBLE(10);
      DBMS_OUTPUT.PUT_LINE('Doubled the input value as : '||M_NUM);
    END;
    /
Doubled the input value as : 20

PL/SQL procedure successfully completed.

Restrictions on calling functions from SQL expressions

Unlike procedures, a stored function can be called from SELECT statements; provided it must not violate the database purity levels. These rules are as follows:

  • A function called from a SELECT statement cannot contain DML statements

  • A function called from a UPDATE or DELETE statement on a table cannot query (SELECT) or perform transaction (DMLs) on the same table

  • A function called from SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command

Besides these rules, a standalone user-defined function must qualify the following conditions:

  • The parameters to the stored function, if any, should be passed in "pass by reference" mode that is, IN parameter only. The data type of the parameter must be a valid SQL data type. Also, the parameters must follow positional notation in the list.

  • The return type of the function must be a valid SQL data type.

The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it perfectly respects all the preceding rules:

/*Invoke the function F_GET_DOUBLE from SELECT statement*/
SQL> SELECT F_GET_DOUBLE(10) FROM DUAL;

F_GET_DOUBLE(10)
----------------
              20

In Oracle, DUAL is a table owned by the SYS user, which has a single column, DUMMY, of VARCHAR2 (1) type. It was first designed by Charles Weiss while working with internal views to duplicate a row. The DUAL table is created by default during the creation of data dictionaries with a single row, whose value is X. The users other than SYS, use its public synonym, to select the value of pseudo columns, such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g has considerably improved the performance implications of the DUAL table through a "fast dual" access mechanism.