Chapter 5. PLSQL

Chapter 5. PL/SQL

If you know ADA, Oracle PL/SQL will seem very familiar. If you don't, don't be put off by the comparison with ADA. First of all, PL/SQL is a much smaller language than ADA, and you'll quickly get used to the language features that may make it look strange at first.

What sets PL/SQL apart from other languages, however, is that it is well integrated with Oracle and SQL. In fact, you can generally include SQL statements directly in PL/SQL code. These SQL statements can include PL/SQL variables for either input or output. One important limitation, however, is that SELECT statements returning multiple rows need to be handled differently as we will see later.

The basic unit of programming in PL/SQL, corresponding to a method in Java, is the block. There are three types of blocks:

  • Anonymous blocks Blocks that we execute immediately, usually interactively from SQL*Plus at an sqlplus prompt. They can also be executed in other contexts (such as from a Java program), but their distinguishing feature is that they are not stored in the database.

  • Functions Blocks that are compiled and stored in the database and can be executed from either the sqlplus prompt or as part of an SQL statement especially in select lists, WHERE clauses and ORDER BY clauses. Functions differ from procedures in that functions return a value, but procedures don't.

  • Procedures Blocks that are compiled and stored in the database and can be executed from either the sqlplus prompt or other programs. They are similar to functions except they don't return a value. A special type of procedure is the database trigger which can be executed automatically when a table is changed.

Also in this chapter we will see Java stored procedures. By providing a call specification that maps a Java method's parameters and return type to PL/SQL, we can implement database functions and procedures in Java.



Java Oracle Database Development
Java Oracle Database Development
ISBN: 0130462187
EAN: 2147483647
Year: 2002
Pages: 71

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net