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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- sql access and ibm drda jim gray
- nonstop sql a distributed high performance high
- pl sql and its role in the oracle world
- sql tutorial
- solutions chapter 1 sql and data test your thinking
- database standard review checklist
- sql create database statement
- sql is a standard computer language for accessing and
- accessing and manipulating sql server data
Related searches
- government s role in the economy
- health and wellness programs in the workplace
- health and wellness ideas in the workplace
- who is in the new world order
- government role in the economy
- students role in the classroom
- oracle pl sql case example
- pl sql top 10
- oracle pl sql case
- pl sql select top 1
- oracle pl sql tutorial
- pl sql examples