Oracle8 and Oracle8i

Overview of PL/SQL

We've shown you a number of techniques available  to connect to an Oracle database. Now we shall provide a quick overview of Oracle's own procedural extensions to SQL.

This section doesn't aim to be a PL/SQL bible. Instead, we'll cover some of the main differences between PL/SQL and standard ANSI SQL .

The "PL" in PL/SQL is short for Procedural Language. It is an extension to SQL that allows you to create PL/SQL programs that contain standard programming features such as error handling, flow-of-control structures, and variables, all allowing you to manipulate Oracle data. By itself, SQL does not support these concepts .

Block Structure

A PL/SQL program consists of any number of blocks or sections of code. In our ASP scripts we can create any number of chunks of code to execute on the server using the <%...%> tags. This is
similar to PL/SQL, where a set of statements can be grouped logically together as part of a larger
set of instructions :

DECLARE TotalSal NUMBER(5);
BEGIN
   SELECT SUM(Sal) INTO TotalSal
     FROM emp
    WHERE ename LIKE 'S%';
   dbms_output.put_line('totalSalary=' || TotalSal );
   IF TotalSal < 10000 THEN
      UPDATE emp SET
        Sal = Sal * 1.1
      WHERE ename LIKE 'S%';
   END IF;

   COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('No records found.');
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
END;

A PL/SQL block has three distinct sections:

  • Declarations
  • Statements
  • Handlers
  • They are defined in the following way:

    [DECLARE declarations]
    BEGIN
       statements
       [EXCEPTION handlers]
    END;

    The declarations section contains any variables or constants that are going to be used within the statements section. You can have any number of statements to execute, but if an error occurs in
    any of them, processing will stop and execution will move to the exception section for trapping, if
    any are defined.

    In above example we declare TotalSal as a variable in the declarations section:

    DECLARE TotalSal NUMBER(5);

    All of the remaining code up to the EXCEPTION line forms the statements section, followed by two exception handlers: NO_DATA_FOUND and OTHERS.

    When you declare an exception handler you must tell Oracle which one of the in-built exceptions you want to trap , such as ZERO_DIVIDE. In our case we've trapped NO_DATA_FOUND, which is raised when an empty result set is retrieved, and OTHERS, which is a catch-all handler that will trap any other exceptions that you have not explicitly named. You can have any number of exception handlers and you can also set up your own exception types, but that is beyond the scope of this chapter.

    Once an exception has been trapped you cannot issue the equivalent to a VBScript RESUME NEXT as the PL/SQL program will exit at the last line in the exception handler. This is somewhat different to the operation of SQL Server's T-SQL  in which you can check the value of @@Error after any statement, provided that the error was of a trappable nature.

    The dbms_output.put_line('No records found.'); statement allows us to briefly mention PL/SQL debugging. dbms_output is a built-in Oracle Package (a package is a way to group together collections of stored procedures) that can be used to send messages to the console. In order to actually see these messages you must execute the SET SERVEROUTPUT ON; statement from within the SQL*Plus SQL editor. Each call to dbms_output.put_line will write out the string message passed to it.


    Oracle uses the / character to mark the end of a block of SQL to execute within SQL*Plus .

    Variable Declaration

    At the start of a PL/SQL block you must define any variables that are to be used, after the DECLARE statement. You can use any of the standard Oracle data-types such as NUMBER, VARCHAR2 or any PL/SQL data-type, such as BOOLEAN. It is just a case  of defining the variable name followed by the data-type and using a semi-colon between multiple declarations:

    DECLARE TotalBonus  NUMBER(6);
            BonusPaid   BOOLEAN;

    For a full list of Oracle data-types check out http://technet.us.oracle.com/doc/server.804/a58227/ch6.htm#649

    Assigning Values to Variables

    In ASP we use the = statement to assign a value to one of our variables. In PL/SQL it is slightly different, in that we must use :=.

    SalePrice := (ProductPrice / 100)  * SalesTax;

    If we are returning a value from a database table or system function, then we use the INTO statement:

    SELECT SUM(Quantity) INTO ItemsOrdered FROM OrderBasket;

    Conditional Flow of Control

    We use the If...Then...Else construct to control  the execution flow of our ASP scripts. PL/SQL also supports this construct in a similar format:

    IF SaleCount > 10 AND SaleCount < 20 THEN
       UPDATE emp SET sal = sal * 0.3;
    ELSIF SaleCount = 5 THEN
       UPDATE emp SET sal = sal * 0.2;
    ELSE
       UPDATE emp SET sal = sal * 0.1;
    END IF;

    Surprisingly, PL/SQL doesn't yet provide support for the CASE statement.

    Looping Flow Control

    To loop through a section of code, PL/SQL supports a number of LOOP statements . The first is similar to the VBScript For...Next statement:

    FOR countervar IN start..end LOOP
       statements to execute
    END LOOP;

    Where countervar is the counter variable, start is the initial starting value and end is the final value. For example:

    FOR intCounter IN 1..5 LOOP
       INSERT INTO OrderLine(ID)
          VALUES(OrderLineID.NEXTVAL);
    END LOOP;

    The equivalent loop in VBScript would be:

    FOR intCounter = 1 To 5
       Response.Write "Value=" & intCounter
    NEXT

    The WHILE...LOOP allows us to execute a section of code until a certain condition is true, just as we do with the Do...Loop structure in ASP:

    WHILE TotalBonus < 10000 LOOP
       SELECT Bonus, EmpID INTO EmpBonus, MyEmpID
          FROM emp
          WHERE EmpID <> MyEmpID;
       Totalbonus := TotalBonus + Bonus;
       RecordCount := RecordCount + 1;
    END LOOP;

    Of course, there's a lot more to PL/SQL than that. PL/SQL is like any programming language with many constructs, statements and functions, but these are the typical building blocks that you will come across in any PL/SQL program.

    Oracle Packages

    We covered stored procedures a few chapters ago, so now we'll take a quick look at Oracle Packages . An Oracle package serves as a way to group procedures and functions into common groups typically based upon their functionality. A package has two sections: the specification that contains a definition of any objects that can be referenced outside of the package, and a body that contains the implementation of the objects. The specification section must be declared first:

    PACKAGE package_name
    IS
       {variable and type declarations }
       {cursor declarations}
       [module specifications]
    END {package_name};

    For example:

    CREATE OR REPLACE PACKAGE Employee_pkg
    AS
       PROCEDURE GetEmployeeName(i_empno    IN   NUMBER,
                                o_ename    OUT  VARCHAR2);
    END Employee_Pkg;

    This defines a package called Employee_pkg that contains a single stored procedure called GetEmployeeAge.


    The package body contains the actual implementation of the procedures within the package.
    This effectively allows us to hide procedures inside the package by not declaring  them in the
    package specification:

    PACKAGE BODY package_name IS   {variable and type declarations}   {cursor specifications - SELECT statements}   [module specifications] BEGIN   [procedure bodies] END {package_name};

    The specification for our Employee_pkg could look like this:

    CREATE OR REPLACE PACKAGE BODY Employee_pkg
    AS
       PROCEDURE GetEmployeeName(i_empno    IN   NUMBER,
                                o_ename    OUT  VARCHAR2)
       IS
       BEGIN
          SELECT ename
             INTO o_ename
             FROM emp
          WHERE empno = i_empno;
       END GetEmployeeName;


    END Employee_pkg;

    To call the GetEmployeeName procedure within Employee_pkg from ASP we use must prefix the stored procedure name with the package name. We'll be covering the execution of stored procedures in much more detail in the next section :

       With objCommand
          .CommandText = "{call Employee_pkg.GetEmployeeName(?, ?)}"
          .CommandType = adCmdText
          .Parameters(0).Direction = adParamInput
          .Parameters(0).Value = varEmpNo
          .Parameters(1).Direction = adParamOutput
          .Execute
          Response.Write "Name=" & .Parameters(1).Value
       End With

    Now that we've had a brief look at Oracle packages we can use some of their features in the final section in this chapter, when we come to retrieving ADO resultsets from an Oracle stored procedure. Before we do that, let's create a sample application that uses a number of stored procedures to perform common data-entry actions.

    You might also like...

    Comments

    Contribute

    Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

    Our tools

    We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

    “Brevity is the soul of wit” - Shakespeare