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:
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.
Comments