Page 184
CAUTION |
Under Windows 3.x, while SQL*Plus is running any SQL or PL/SQL, your entire Windows environment is effectively locked up. Be sure to save any text changes before running something in SQL*Plus. If you find you need to kill a long-running query, you'll feel better knowing you're not in peril of losing any work. |
Oracle has thoughtfully provided some standard runtime libraries to perform certain valuable activities, such as standard I/O, timing, data exchange between active sessions, dynamic SQL creation, and other sophisticated low-level operations. As your programming needs increase in complexity, you will use these supplied packages more frequently.
NOTE |
See Chapter 12, "Using Supplied Oracle Database Packages," for a description of these packages. |
The data dictionary is exposed to the different users through various views. These views have the same name with different prefixes. These views can be broken down into the following categories (in no particular order):
For the privileged DBA user, the most appropriate views to use are prefixed with DBA_. These views provide information about every database object in all schemas, generally organized by schema owner. For everybody else, there are the ALL_ views and the USER_ views. Additionally, the performance views (beginning with V$) are publicly accessible.
Objects in Your SchemaThe USER_ views display only those database objects owned by the currently connected user (you). If you created it, you'll find it listed here. For example, the view USER_SOURCE contains the source code for your stored subprograms and packages.
Objects in Someone Else's SchemaThe ALL_ views display only those database objects either owned by you or those objects in other schemas which have been granted access to you. In the latter case, these privileges may have been granted either directly or via a role; however,
Page 185
only those objects granted privileges directly to you will be accessible from within your PL/SQL code. For example, ALL_TABLES contains all of your tables, plus anyone else's tables for which you've been granted access.
For example, the view ALL_SOURCE contains all of your source code, as well as just the package headers of someone else's stored packages that were granted the EXECUTE privilege to you. The reason for this distinction is that you only need to know how to correctly call someone else's packaged subprograms; you can't modify them anyway, so you don't need to see the implementation details.
Objects in the DBA's SchemaThe DBA is privileged; he or she has access to every database object within all schemas in the data dictionary. Stored subprograms are a suitable way to expose this information to all users, without giving them anything approaching DBA privileges. Provided, of course, that the DBA compiles these subprograms. For example, the view DBA_OBJECTS contains information about every database object (including those owned by SYSTEM and SYS) of all types of objects (such as tables, indexes, sequences, and so on). By writing and compiling a few simple PL/SQL stored procedures, the DBA can make this information available to those who need it in an easily deployable manner.
The best way to learn a new language is to jump right in! The following section presents PL/SQL in a tutorial manner, starting with the basics and moving swiftly through the various language features. If you're familiar with any other computer language like Pascal, FORTRAN, C, or COBOL, you won't have any trouble. It is strongly recommended that you try out the code that's presented here and on the accompanying CD as you follow along.
Mark Twain once wrote, "The nice thing about making a mistake is that you recognize it when you make it again." In this section you will take his advice and make plenty of mistakes. You'll look at common compilation and runtime errors, and the (often terse) error messages the Server hands back. You will deliberately run some buggy code here and there just so you can have the pleasure of debugging it. Then, when you're on your own, and an error message pops up, you won't be totally confused.
PL/SQL is not case sensitive; all text is converted to uppercase (except those in literal strings). Therefore, you cannot use case to distinguish variables and other user-defined elements. For readability, all code in this section has been standardized to follow some simple casing rules in order to distinguish these elements in the source code:
Page 186
You can follow these or whatever conventions you desire. Just be consistent. Code in this section is documented heavily with inline comments, indents are carefully placed (usually two spaces at a time, never tabs), and whitespace is used to align statements more cleanly and keep lines of text under 80 characters.
PL/SQL is a free-form textual language; any amount of white space, such as spaces, newlines, and tabs, act as delimiters and are otherwise ignored. One or more statements can be on one line, or a statement can be broken up on several lines. In general, statements are ended with a semicolon (;). There is no limit placed on the number of characters per line, or the number of lines per module. However, there are operating system_dependent restrictions on the overall size of a source code module.
CAUTION |
The Oracle Server imposes limitations on the size of a PL/SQL module, depending on the operating system. On NetWare 3.x it is limited to 32KB. For most flavors of UNIX and Windows NT, the module size is restricted to 64KB. Violating this limit can crash your database server or the server machine itself. |
Some characters have particular meanings in a PL/SQL program. Most are obvious, such as math symbols and relational operators. Others, such as Association and Host Variable Indicator, are less obvious. These will be explored in later sections. Table 10.1 lists these characters (in some cases, pairs) and a brief description.
Table 10.1 Special Characters Organized by Type
Type | Character | Description |
Arithmetic Operators | + | Addition and Unary Positive |
- | Subtraction and Unary Negation | |
* | Multiplication | |
/ | Division | |
** | Exponentiation | |
Relational Operators (used in Boolean expressions) | = | Equivalence |
< | Less Than | |
> | Greater Than | |
<> | Not Equal | |
!= | Not Equal (alternate) | |
~= | Not Equal (alternate) | |
^= | Not Equal (alternate) | |
<= | Less Than or Equal | |
>= | Greater Than or Equal |