Page 181
You can use PL/SQL in stored procedures and packages to build better applications. A concerted effort to design and build reusable PL/SQL modules will reap long term benefits. With some thoughtful design and planning, you can:
Using PL/SQL is easy and fun. It has a relatively short learning curve as languages go. Experiment to determine what works well or at all. If you follow the steps detailed in this section, you should become productive in just a few days and, after only a few months, master PL/SQL sufficiently enough to achieve some sophisticated results.
This section details some things the DBA must do before you can do much with PL/SQL. The architecture of the application environment in which you'll be working will also be explored.
Before you can really get into writing PL/SQL programs, you or your DBA must first do the following:
Page 182
Once these things are in place, you're ready to start developing PL/SQL programs!
In abstract terms, a schema is a logical collection of related database objects, such as tables, indexes, and views. A schema is a developer's-eye view of the database objects of interest, such as are shown on an Entity-Relationship Diagram (or ERD).
In an Oracle database, a schema is a logical collection of objects stored within one or more tablespaces. Because it is a logical collection, several applications can share schemas, or cross schema boundaries into other schemas. The relational organization of schema objects is strictly as the DBA and developers see it, which is why a "roadmap," such as an ER diagram, is critical to the success of any database-related software development. A tablespace, in turn, is a logical storage area that maps to one or more physical files out on disk. Figure 10.6 illustrates this organization.
FIG. 10.6
Applications, schemas,
tablespaces, and
database files are
organized hierarchically.
Typically, a schema has exactly one owner (that is, an Oracle user account) responsible for creating, altering, and dropping these objects. This owner then grants access privileges to other users, either directly or via roles. If you're not the owner of these objects, you qualify references to the objects by specifying which tablespace they are in using dot notation, as with SELECT CUST_NAME FROM MKG.LEADS.
Managing Your PL/SQL Code After they have been thoroughly tested and validated in your own development schema, your stored PL/SQL programs are likely to become objects managed by the schema owner. In a complex development environment with many developers, it becomes critical to determine who is the owner of
Page 183
the source code and where it is kept. When a module is being worked on, it is in a state of flux. The developer can make significant changes to a private copy, some of which may be backed out later. It's strongly recommended that you use a Code Management System (CMS) to control the one official version of the source code. When the module has been thoroughly tested and accepted for Production, the new version is placed in the CMS, and the schema owner can compile the new version. The quickest way to shoot yourself in the foot is to lose track of which copy is the one known good version. In the long run, just keeping different subdirectories for different versions is a poor code management strategy.
There is no correspondence between schemas and tablespaces; one or more schemas may be stored in a single tablespace, or one schema may be broken up into separate tablespaces. In practice, it is generally a good idea to store a schema in its own set of tablespaces, along with a good set of documentation. For example, tables may be stored in one tablespace and indexes in another. Conceptually, both tablespaces are included in the same schema.
TIP |
PL/SQL developers should have their own default tablespaces to play around in, preferably on a non-Production database. You can create your own private tables and indexes with which to experiment. This way, if you inadvertently corrupt a temporary, local copy of a Production table, who cares? Whereas the alternative could result in an abrupt end in your career (at least as far as your current workplace goes). Additionally, any stored programs or packages you compile are strictly local to within your schema (stored in the data dictionary alongside subprograms belonging to other schemas), so you can make experimental changes to Production code in a safe development environment. |
CAUTION |
The caveat to this scheme is to take care with object references. If it's in your default tablespace, you don't need to qualify the reference with the tablespace name; otherwise, you must qualify it in order for it to be properly found. If an object by the same name is in more than one tablespace, qualify the reference. You can use synonyms for unqualified objects in PL/SQL code to provide the qualifier. |
As a pragmatic consideration, some applications may easily cross schema boundaries, whereas core applications might focus on one schema. For example, a marketing application may have been developed to automate this one department. Later, upper management may request a high-level view of the entire enterprise, requiring a new application to obtain data from several schemas. The work you do in one application may be reused in another, so always keep an eye toward making PL/SQL modules generic enough to be used in multiple contexts.
PL/SQL code is developed using your favorite text editor and the Oracle-supplied SQL*Plus application, or one of the several fine third-party development toolkits. If you're in a UNIX environment, it's a good idea to have a couple of sessions upone running a text editor such as vi or emacs, and another session running SQL*Plus. If you're running Windows, you can use Notepad (or a real programmer's editor) and have a SQL*Plus session up.