PL/SQL and its Role in the Oracle World



PL/SQL and its Role in the Oracle World

By now, you have been exposed to the relational model, RDBMS, and the associated Structured Query Language (SQL). You've learned that there is an ANSI standard as promulgated by a committee[1], and that SQL is based on a fairly rigorous mathematical model, with set orientation[2] as supported by such operators as UNION, UNION ALL, INTERSECT, and MINUS.

Some Perspective

Although there is a standard SQL flavor, in a competitive world vendors of RDBMS products want to differentiate their offerings from those of their competitors, so we find that vendors compete on features and extensions, not a new story, and a trend which tends to benefit IT shops and customers in general (as long as such implementations are robust). Oracle Corporation, in designing their RDBMS product, has provided a procedural language extension to SQL, which is what "PL/SQL" means[3].

Significant Benefits

PL/SQL comes as a welcome addition to those who:

1. are accustomed to conditional processing, as is found in procedural languages

2. would like to be able to create an actual 'program', as opposed to just running one-at-a-time queries, with no 'chaining' between the queries (no 'narrative' or 'memory' of what has gone before in the current session)

3. want to integrate the database server with application programs written with other Oracle tools, such as Oracle Forms, Oracle Reports, others

4. would like to hold the results of DML queries in local variables for subsequent reuse

5. want to store scripts and/or program units for reuse

6. would like to provide more powerful forms of business rule enforcement by installing database triggers (much more powerful than ANSI-compliant constraints such as FOREIGN KEY, CHECK, UNIQUE, others)

7. want the ability to 'walk' through a result set on a row-by-row basis, with the ability to launch additional procedures from within the context of the current row in a result set (rather much like the 'open/read/close' type of processing to which mainframe programmers have become accustomed).

Caveats

It may not matter for your particular situation, but PL/SQL is not a portable skill, in the sense that knowledge of how to program on an Oracle server will not be immediately transferable to MS SQL Server, Sybase, Informix or DB2, even though all those products are based on the same SQL standard. The general understanding, however, that one can extend the set orientation of SQL with a more complete programmatic approach is a transferable mindset.

Some perspective on when to use PL/SQL

If I could adopt the role of the kindly, world-weary uncle, I would like to suggest that you have a good grounding in SQL queries first, so that you don't use procedural approaches to solving problems as your first resort. Use the power of the backend to do the heavy lifting by using queries first, for a number of reasons. When you cannot satisfy the requirement with a query, then 'resort' to a programmatic solution. The careerist in you should have mastery of the most portable part of this area, namely the SQL language itself.

Block Orientation

PL/SQL is a block-structured language. A block consists of up to three sections:

• A syntactically optional declarative section

• An executable section

• A syntactically optional (but strongly recommended) exception-handling section

Blocks and SQL*Plus

Please understand that PL/SQL blocks are independent of any particular tool, but that most beginners are introduced to blocks using SQL*Plus. PL/SQL blocks are found in other environments, and you should distinguish between SQL*Plus-specific features such as substitution variables and the features of PL/SQL blocks.

Block Types

Blocks can be independent or nested within each other. Blocks found within stored program units may also be invoked from within any other block, supporting a modular style of programming. Blocks consist of:

Anonymous Blocks (not stored under an object name in the server) These types of program units are embedded within an application in the form of triggers of various types, as found in database triggers, and in Oracle Forms and Oracle Reports. We will not see much usage in this course, but understand the general usefulness. Note also that anonymous blocks in the form of triggers can take advantage of a special type of communications area known as the bind variable, which is identified by a colon in front of the variable name

Subprograms (stored program units which undergo a compilation and are in turn further subdivided into functions and procedures)

Packages and Libraries

When/if you get to working with Oracle tools such as Reports, Forms, and Developer, then you may be introduced to the value of libraries, which can be shared among applications. Even if you never use any Oracle front-end tools, you should understand the benefits of Oracle packages, which permit one to associate related procedures, functions and other constructs such as cursors, and moreover to have them share a context and scope, so that much richer cooperative processing between program units can be promoted. Novices are usually introduced to the DBMS_OUTPUT package of Oracle, but note that there are a lot of 'built-in' packages which may be installed on your particular copy of Oracle. Some packages of note are UTL_FILE, DBMS_JOB, DBMS_JAVA, DBMS_SQL, DBMS_ALERT, DBMS_DEBUG, DBMS_PIPE, and others.

Cursors

Once you understand the power of SQL, and its more exotic possibilities, you realize that you can do a lot to generate useful result sets. Joins, Sub-queries, Aggregates and set operations can get very complex, but can be very rewarding. Imagine being able to generate an ad hoc set of data then process that data 'row-by-row'. This is where a cursor comes in. You declare your SQL SELECT query in the block (guess where?) then within the executable section, using one of two styles of looping you can fetch (read) in one row of that result set into variables which you can then process at length. This powerful possibility makes it possible to break up a result set and zoom in on rows, and therefore addresses one of the perceived limitations of the non-procedural language SQL.

Variables

Like most programming languages, variables are available for temporary storage of data, manipulation of stored values, reuse of the value elsewhere in the block, and ease of maintenance. Variables have data types, which is not surprising, but also note that the special %TYPE and %ROWTYPE data types provide a more flexible way to declare the date types of variables which are associated with tables, views and/or cursors.

Variables provide a necessary way to communicate with other processes from within the scope of the PL/SQL block, as PL/SQL does not inherent I/O capability[4].

Variables have these types:

1. SCALAR

2. COMPOSITE

3. REFERENCE (pointers)

4. LOB (locators to large objects that are stored 'out of line')

Variables also have datatypes, where you will find some overlap with the datatypes used to define columns in DDL. There are additional datatypes in PL/SQL, such as Boolean Binary_integer and PLS_integer.

Declaring Variables

The syntax template for declaration of variables is:

identifier [CONSTANT] datatype [NOT NULL] [:= value | DEFAULT expr];

More content would follow here for variables and the declaration section of a block, to include a discussion of cursors

Executable Section Notes

DML statements (other than SELECT) are fairly easy to understand, since the only real difference between their use in queries and within a PL/SQL block is that variables can be mixed into the DML where constants would normally be used in the query[5]. When we come to the area of the SELECT, then we find a different story. Those of you who may know about how to do this sort of work on Sybase or Microsoft will be surprised to know that even the most simple type of SELECT cannot stand alone within even the most simple type of PL/SQL block. In order to do a SELECT within a block, you must either select the returned values into predefined variables (more later) or you must go through the exertion of setting up a cursor to receive the incoming values. If you are not careful with the first approach, you will likely generate a TOO_MANY_ROWS type exception, which is why when you look at some code in installed apps you may think that the programmer was engaged in massive overkill, creating a cursor when the cursor only returns one row.

Here's an example of a simple block which illustrates how to do a SELECT INTO and also illustrates one of several types of looping constructs:

declare

v_max number := 0; v_min number := 0; v_count number := 0;

begin

select min(instructor_sid)

into v_min

from instructor_course;

select max(instructor_sid)

into v_max

from instructor_course;

dbms_output.put_line ( 'Have loaded variables; will now begin loop');

dbms_output.put ( CHR(10) ); /* This gives me a SINGLE blank line */

for i in v_min..v_max loop

dbms_output.put_line ( 'now processing ' || i );

end loop;

end;

A cursor within an Anonymous Block

Anonymous Blocks are sent as one unit to the server. Multi-line operations such as the one illustrated below cannot run outside of a block. This example shows the commands and the feedback from SQL*Plus.

SQL> create table frequent_students

2 ( student_sid number(3) PRIMARY KEY,

3 last_name varchar2(20) NOT NULL,

4 first_name varchar2(20) NOT NULL,

5 change_date date DEFAULT SYSDATE );

Table created.

DECLARE

v_student_sid NUMBER(3);

v_fname VARCHAR2(20);

v_lname VARCHAR2(20);

v_count NUMBER;

CURSOR student_cursor IS

SELECT student_sid, first_name, last_name

FROM student;

BEGIN

OPEN student_cursor;

-- This puts the 'pointer' at top of 'file' but above first 'record'

LOOP

FETCH student_cursor INTO v_student_sid, v_fname, v_lname;

EXIT WHEN student_cursor%NOTFOUND;

-- Until we run out of rows in the cursor:

SELECT COUNT(*)

INTO v_count

FROM student_class_schedule

WHERE student_sid = v_student_sid;

IF v_count > 3 THEN

INSERT INTO frequent_students ( student_sid, last_name, first_name )

VALUES ( v_student_sid, v_lname, v_fname );

END IF;

END LOOP;

CLOSE student_cursor;

COMMIT;

END;

PL/SQL procedure successfully completed.

SQL> select count(*) from frequent_students;

COUNT(*)

----------

50

Example of procedure

create or replace procedure AddInstr(

first instructor.first_name%type,

last instructor.last_name%type,

birth_date instructor.birth_date%type,

manager_id instructor.manager_sid%type,

site1 instructor.home_site_code%type,

site2 instructor.home_site_code%type)

IS

home instructor.home_site_code%type;

id instructor.instructor_sid%type;

BEGIN

/* find home site with fewer instructors; note the use of a user-defined function */

home:=which_has_fewer(site1, site2);

/* do the insert */

insert into instructor (first_name, last_name,instructor_sid, hire_date,manager_sid, birth_date, home_site_code)

values(first, last, instructor_seq.nextval, sysdate, manager_id,birth_date, home);

END;

/

Example of function

/* function accepts two training site codes and returns the site code that has fewer instructors. Tie goes to site_1 */

create or replace function which_has_fewer(

site_1 instructor.home_site_code%type,

site_2 instructor.home_site_code%type)

return instructor.home_site_code%type IS

first number:=0;

second number:=0;

site instructor.home_site_code%type;

BEGIN

select count(*)

into first

from instructor

where home_site_code = site_1;

select count(*)

into second

from instructor

where home_site_code = site_2;

if first DECLARE

2 Myvar1 integer;

3 Myvar2 integer;

4 Value_error EXCEPTION ;

5 -- Looks like a variable and datatype declaration, doesn't it?

6 PRAGMA EXCEPTION_INIT( Value_error, -6502);

7 BEGIN

8 Myvar1 := 1;

9 BEGIN

10 Myvar1 := 'hey';

11 DBMS_OUTPUT.PUT_LINE(Myvar1);

12 END;

13 EXCEPTION

14 WHEN Value_error THEN

15 DBMS_OUTPUT.PUT_LINE('Inappropriate data type used');

16 WHEN OTHERS THEN

17 DBMS_OUTPUT.PUT_LINE('There was a problem');

18 -- This is still the 'catch all'

19 END;

20 /

Inappropriate data type used

PL/SQL procedure successfully completed.

Oracle PL/SQL tips:

It is a good idea to use exception handling in your stored procedures, but during testing the use of exception, handling code may obscure what the problem is. What I mean is that the line of the program which is cited during error display is the line of the program where the exception handling code resides. What you should consider is commenting out the exception handling portion temporarily, then running the proc from SQL*Plus, and noting the line number there.

Another related tip relates to cursors within program units. If you can successfully compile a program which uses cursors, do not expect cursor definition errors to be uncovered there. The error will be surfaced only when you run the procedure. Again, if you have exception handling code which uses the OTHERS clause, it may not be obvious what the problem is. Always copy and paste the SELECT which defines the cursor into SQL*Plus and isolate it there (replacing variables with appropriate constant values in the SQL*Plus query of course.)

One of the first things (and sometimes the only thing) which novices learn about exception handlers is to use the 'catch all' exception handler of PL/SQL, which is:

WHEN OTHERS THEN … as we have seen

Please don't just use this default handler, but if you are trying to get something compiled and want to at least honor the value of this section of a block, then at least start with that one.

-----------------------

[1] The ANSI SQL3 standard supports object-oriented extensions to the core standard, enhancing SQL into a computationally complete language for the definition and management of persistent, complex objects. This includes: generalization and specialization hierarchies, multiple inheritance, user defined data types, triggers and assertions, support for knowledge based systems, recursive query expressions, and additional data administration tools, among others, but this author typically sees just 'SQL 1' in use on the job.

[2] Note that Oracle's implementation of SQL offers more set operators than its competitors. MINUS and INTERSECT, for example, were not found in Sybase and Microsoft SQL Servers.

[3] Competing equivalent languages in Sybase and Microsoft are called "Transact-SQL" or "T-SQL", but the fact that these two vendors have a similar language does NOT imply that their language is more 'standard'; rather, it reflects the fact that originally Microsoft licensed a version of Sybase's SQL Server.

[4] You will generally see starting examples of PL/SQL blocks (especially when hosted in a SQL*Plus session) use the Oracle DBMS_OUTPUT 'package' to 'print' lines of output. Note that DBMS_OUTPUT, and its PUT_LINE method, finally are limited by maximum buffer size and in fact, documentation states that DBMS_OUTPUT.PUT_LINE is more typically used in enterprise situations to help debug programs.

[5] For the sake of perspective, remember that nested queries or sub-queries as they are also called can provide a very powerful way of processing, where the value used in a single query is not known at runtime. Subqueries can also inform UPDATES and DELETES.

[6] (Remember that the server rules on whether an operation is permitted, no matter how we attempt to do the work, one reason why the RDBMS technology is so powerful and popular).

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download