Storedproc

1

Stored Procedures in PL/SQL

Many modern databases support a more procedural approach to databases—they allow you to write procedural code to work with data. Usually, it takes the form of SQL interweaved with the more familiar IF statements, etc. Note that this has nothing to do with accessing the database. You can access any database from virtually any language. What we’re talking aboutis the code that is executed by the database server. While there are many various ‘database’ languages, we will only talk about the primary two: T-SQL, which is supported by SQL Server and Sybase, and PL/SQL, which is supported by Oracle. Many other languages may be supported. For example, Oracle allows you to write stored procedures and triggers in Java, etc.

2

PL/SQL

Besides plainvanilla SQL, Oracle supports PL/SQL. The PL stands for Procedural Language, which means you can have things like IF statements, loops, variables, and other procedural things along with declarative SQL statements. PL/SQL

2.1

Variables

Just as most procedural languages, PL/SQL has some sort of variables. The types of variables are plain SQL column types that you’re all used to. You can alsorefer to a type of a particular column explicitly by specifying the fully qualified column name (tablename.columname) followed by %TYPE. For example: PRODUCT.PRICE%TYPE. Similarly, we can refer to a particular row as a single type. Again, you declare it by referring to a database table directly: PRODUCT%ROWTYPE. This would refer to a single record stored in the PRODUCT table. Along with the abovementioned, some common types are: BOOLEAN, DATE, NUMBER, CHAR, and VARCHAR2. We declare variables of these types similarly to specifying columns in tables. First, we list the name of the variable, then the type we want it to have. For example, to declare a price variable of a fixed point NUMBER, we might do something like this: PRICE NUMBER(6,2);

2.2

PL/SQL Program Blocks

PL/SQL programsare structured in blocks and have the following format: DECLARE variable_declarations BEGIN procedural_code 1

EXCEPTION error_handling END; 2.2.1 Declare

The declare part is where variable declaration goes. All used variables must be declared in this section. This is also the place where other more exotic variable types are declared, like cursors and exceptions. 2.2.2 Begin

This is thepart we’re most interested in. This is where the bulk of your programs shall be placed. Here, you can have IF statements, loops, etc. 2.2.3 Exceptions

The exception section is where we place error handling code. We will talk about it more depth in subsequent lessons. 2.2.4 End

The end signifies the end of this program block.

2.3

Operators

PL/SQL supports several operators to dovarious things. Table 1 lists some of the more common operators.

2.4

Hello World and a bit Beyond...

Well, let’s start with the PL/SQL hello world example. Before we write it however, there are a couple of things that need to be setup. First, start “SQL*Plus” (Oracle), login, and type: SET SERVEROUTPUT ON What this does is enable you to view output in SQL*Plus window whenever your programsattempts to write some output to the screen. Now, let’s get on with the show. Type the following into the SQL*Plus window as is: BEGIN DBMS_OUTPUT.PUT_LINE(’Hello World’); END; You’ll notice that it doesn’t run as an average SQL statement. To make it run, you must type the ’/’ character on a line by itself. And you’ll notice: 2

SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE(’Hello World’); 3 END; 4 / HelloWorld PL/SQL procedure successfully completed. You can think of DBMS_OUTPUT.PUT_LINE() as sort of a printf() in C language. It writes output to the console; but it only works for strings (or data that can be implicitly converted to a string). You can also do more complicated things, like access global variables like SYSDATE. For example: SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE(’The time now is: ’); 3...