Release 1 (9.0.1)
Part Number A88856-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter provides an overview of the Structured Query Language (SQL),
PL/SQL, Oracle's procedural extension to SQL, and Java. The chapter includes:
SQL is a database access, nonprocedural language. Users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.
IBM Research developed and defined SQL, and ANSI/ISO has refined SQL as the standard language for relational database management systems.The minimal conformance level for SQL-99 is known as Core. Core SQL-99 is a superset of
SQL-92 Entry Level specification. Oracle9i is broadly compatible with the SQL-99 Core specification.
Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional statements. The Oracle tools SQL*Plus and Oracle Enterprise Manager allow you to execute any ANSI/ISO standard SQL statement against an Oracle database, as well as additional statements or functions that are available for those tools.
Oracle SQLJ allows applications programmers to embed static SQL operations in Java code in a way that is compatible with the Java design philosophy. A SQLJ program is a Java program containing embedded static SQL statements that comply with the ANSI-standard SQLJ Language Reference syntax.
Although some Oracle tools and applications simplify or mask SQL use, all database operations are performed using SQL. Any other data access method circumvents the security built into Oracle and potentially compromise data security and integrity.
|
See Also:
|
All operations performed on the information in an Oracle database are executed using SQL statements. A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.
A SQL statement is a computer program or instruction. The statement must be the equivalent of a complete SQL sentence, as in:
SELECT ename, deptno FROM emp;
Only a complete SQL statement can be executed, whereas a fragment such as the following generates an error indicating that more text is required before a SQL statement can execute:
SELECT ename
Oracle SQL statements are divided into the following categories:
Chapter 18, "Triggers" for more information about using SQL statements in PL/SQL program units
See Also:
Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:
(SELECT); in Oracle9i, Release 1 (9.0.1), fetches can be scrollable (see "Scrollable Cursors")
(INSERT)
(UPDATE)
(MERGE)
(DELETE)
(EXPLAIN PLAN)
(LOCK TABLE)
DML statements are the most frequently used SQL statements. Some examples of DML statements are:
SELECT ename, mgr, comm + sal FROM emp; INSERT INTO emp VALUES (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); DELETE FROM emp WHERE ename IN ('WARD','JONES');
Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:
(CREATE, ALTER, DROP)
(RENAME)
(TRUNCATE)
(GRANT, REVOKE)
(AUDIT, NOAUDIT)
(COMMENT)
DDL statements implicitly commit the preceding and start a new transaction. Some examples of DDL statements are:
CREATE TABLE plants (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); DROP TABLE plants; GRANT SELECT ON emp TO scott; REVOKE DELETE ON emp FROM scott;
|
See Also:
for more information about DDL statements that correspond to database access |
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:
(COMMIT)
(ROLLBACK)
(SAVEPOINT)
(SET TRANSACTION)
Session control statements manage the properties of a particular user's session. For example, they enable you to:
(ALTER SESSION)
(SET ROLE)
System control statements change the properties of the Oracle server instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), kill a session, and perform other tasks.
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to:
(DECLARE CURSOR, OPEN, CLOSE)
(DECLARE DATABASE, CONNECT)
(DECLARE STATEMENT)
(DESCRIBE)
(WHENEVER)
(PREPARE, EXECUTE, EXECUTE IMMEDIATE)
(FETCH)
Oracle provides extensions to the standard SQL Database Language with Integrity Enhancement. The Federal Information Processing Standard for SQL (FIPS 127-2) requires vendors to supply a method for identifying SQL statements that use such extensions. You can identify or flag Oracle extensions in interactive SQL, the Oracle precompilers, or SQL*Module by using the FIPS flagger.
If you are concerned with the portability of your applications to other implementations of SQL, use the FIPS flagger.
When a DDL statement is issued, Oracle implicitly issues recursive SQL statements that modify data dictionary information. Users need not be concerned with the recursive SQL internally performed by Oracle.
A cursor is a handle or name for a private SQL area--an area in memory in which a parsed statement and other information for processing the statement are kept.
Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.
Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initialization parameter.
Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can execute several recursive calls. These recursive cursors also use shared SQL areas.
Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or nonsequentially. Scrollable cursors are cursors in which fetches and DML operations do not need to be forward sequential only. Interfaces exist to fetch previously fetched rows, to fetch the nth row in the result set, and to fetch the nth row from the current position in the result set.
|
See Also:
Oracle Call Interface Programmer's Guide for more information about using scrollable cursors in OCI |
Oracle automatically notices when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared--that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory usage on the database server, thereby increasing system throughput.
In evaluating whether statements are similar or identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.
|
See Also:
Oracle9i Application Developer's Guide - Fundamentals and Oracle9i Database Performance Guide and Reference for more information about shared SQL |
Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:
Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and executes the statement immediately. If not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.
Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be executed repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be executed repeatedly without being reparsed.
Both parse calls and parsing can be expensive relative to execution, so users should perform them as seldom as possible.
This section introduces the basics of SQL processing. Topics include:
Figure 16-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, Oracle can execute these stages in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.
For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, you may find this information useful when writing Oracle applications.
This section provides an example of what happens during the execution of a SQL statement in each stage of DML statement processing.
Assume that you are using a Pro*C program to increase the salary for all employees in a department. Also assume that the program you are using has connected to Oracle and that you are connected to the proper schema to update the EMP table. You can embed the following SQL statement in your program:
EXEC SQL UPDATE emp SET sal = 1.10 * sal WHERE deptno = :dept_number;
DEPT_NUMBER is a program variable containing a value for department number. When the SQL statement is executed, the value of DEPT_NUMBER is used, as provided by the application program.
The following stages are necessary for each type of statement processing:
Optionally, you can include another stage:
Queries (SELECTs) require several additional stages, as shown in Figure 16-1:
A program interface call creates a cursor. The cursor is created independent of any SQL statement: it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.
During parsing, the SQL statement is passed from the user process to Oracle, and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.
Parsing is the process of:
Oracle parses a SQL statement only if a shared SQL area for an similar SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated, and the statement is parsed.
The parse stage includes processing requirements that need to be done only once no matter how many times the statement is executed. Oracle translates each SQL statement only once, reexecuting that parsed statement during subsequent references to the statement.
Although parsing a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can be encountered and reported only during the execution stage.
Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.
Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following statements requires a query as a part of its execution:
INSERT INTO table SELECT... UPDATE table SET x = y WHERE... DELETE FROM table WHERE... CREATE table AS SELECT...
In particular, queries:
The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user.
In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.
In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.
At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to execute the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for DEPT_NUMBER. The process of obtaining these values is called binding variables.
A program must specify the location (memory address) where the value can be found. End users of applications may be unaware that they are specifying bind variables, because the Oracle utility can simply prompt them for a new value.
Because you specify the location (binding by reference), you need not rebind the variable before reexecution. You can change its value and Oracle looks up the value on each execution, using the memory address.
You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle needs to perform datatype conversion.
|
See Also:
for more information about specifying a datatype and length for a value |
Oracle can parallelize queries (SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.
|
See Also:
Chapter 20, "Parallel Execution of SQL Statements" for more information about parallel SQL |
At this point, Oracle has all necessary information and resources, so the statement is executed. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.
For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.
The final stage of processing a SQL statement is closing the cursor.
The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.
Transaction management, session management, and system management SQL statements are processed using the parse and execute stages. To reexecute them, simply perform another execute.
In general, only application designers using the programming interfaces to Oracle are concerned with the types of actions that should be grouped together as one transaction. Transactions must be defined so that work is accomplished in logical units and data is kept consistent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less.
For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.
In addition to determining which types of actions form a transaction, when you design an application you must also determine when it is useful to use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.
The optimizer determines the most efficient way to execute a SQL statement. This is an important step in the processing of any data manipulation language (DML) statement: SELECT, INSERT, UPDATE, MERGE, or DELETE. There are often many different ways to execute a SQL statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect how quickly the statement executes. The optimizer considers many factors among alternative access paths. It can use either a cost-based or a rule-based approach. In general, always use the cost-based approach. The rule-based approach is available for the benefit of existing applications.
You can influence the optimizer's choices by setting the optimizer approach and goal. You can also gather statistics for the cost-based optimizer (CBO), using Oracle9i's PL/SQL package DBMS_STATS.
Sometimes the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to execute a SQL statement. The application designer can use hints in SQL statements to specify how the statement should be executed.
|
See Also:
|
To execute a DML statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.
Stored outlines are abstractions of an execution plan generated by the optimizer at the time the outline was created and are represented primarily as a set of hints. When the outline is subsequently used, these hints are applied at various stages of compilation. Outline data is stored in the OUTLN schema.
Oracle9i, Release 1 (9.0.1), enables users to tune execution plans by editing stored outlines.
The outline is cloned into the user's schema at the onset of the outline editing session. All subsequent editing operations are performed on that clone until the user is satisfied with the edits and chooses to publicize them. In this way, any editing done by the user does not impact the rest of the user community, which would continue to use the public version of the outline until the edits are explicitly saved.
|
See Also:
Oracle9i Database Performance Guide and Reference for details about cloning the outline, editing the outline, validating the edits, and publicizing the edits |
PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages.
PL/SQL program units generally are categorized as anonymous blocks and stored procedures.
An anonymous block is a PL/SQL block that appears within your application and it is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.
A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also allows you to create and store functions (which are similar to procedures) and packages (which are groups of procedures and functions).
The PL/SQL engine, which processes PL/SQL program units, is a component of many Oracle products, including the Oracle server.
Figure 16-2 illustrates the PL/SQL engine contained in Oracle server.
The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.
The following Oracle products contain a PL/SQL engine:
You can call a stored procedure from another PL/SQL block, which can be either an anonymous block or another stored procedure. For example, you can call a stored procedure from Oracle Forms (Version 3 or later).
Also, you can pass anonymous blocks to Oracle from applications developed with these tools:
PL/SQL blocks can include the following PL/SQL language constructs:
This section gives a general description of each construct.
Variables and constants can be declared within a procedure, function, or package. A variable or constant can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed.
Cursors can be declared explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared implicitly (to support other data manipulation actions) by the PL/SQL engine.
PL/SQL allows you to explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as division by zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negative balance).
When an exception is raised (signaled), the normal execution of the PL/SQL code stops, and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.
Oracle also allows you to create and call stored procedures. If your application calls a stored procedure, the parsed representation of the procedure is retrieved from the database and processed by the PL/SQL engine in Oracle.
You can call stored procedures from applications developed using these tools:
You can also call a stored procedure from another PL/SQL block, either an anonymous block or another stored procedure.
|
See Also:
|
PL/SQL can execute dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general purpose procedures. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.
You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL in two ways:
Additionally, you can issue DML or DDL statements using dynamic SQL. This helps solve the problem of not being able to statically embed DDL statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from within a stored procedure by using the EXECUTE IMMEDIATE statement or the PARSE procedure supplied with the DBMS_SQL package.
|
See Also:
|
PL/SQL Server Pages (PSP) are server-side web pages (in HTML or XML) with embedded PL/SQL scripts marked with special tags. To produce dynamic web pages, developers have usually written CGI programs in C or Perl that fetch data and produce the entire web page within the same program. The development and maintenance of such dynamic pages is costly and time-consuming.
Scripting fulfills the demand for rapid development of dynamic web pages. Small scripts can be embedded in HTML pages without changing their basic HTML identity. The scripts contain the logic to produce the dynamic portions of HTML pages and are executed when the pages are requested by the users.
The separation of HTML content from application logic makes script pages easier to develop, debug, and maintain. The simpler development model, along the fact that scripting languages usually demand less programming skill, enables web page writers to develop dynamic web pages.
There are two kinds of embedded scripts in HTML pages: client-side scripts and server-side scripts. Client-side scripts are returned as part of the HTML page and are executed in the browser. They are mainly used for client-side navigation of HTML pages or data validation. Server-side scripts, while also embedded in the HTML pages, are executed on the server side. They fetch and manipulate data and produce HTML content that is returned as part of the page. PSP scripts are server-side scripts.
A PL/SQL gateway receives HTTP requests from an HTTP client, invokes a
PL/SQL stored procedure as specified in the URL, and returns the HTTP output to the client. A PL/SQL Server Page is processed by a PSP compiler, which compiles the page into a PL/SQL stored procedure. When the procedure is executed by the gateway, it generates the web page with dynamic content. PSP is built on one of two existing PL/SQL gateways:
Oracle9i Application Developer's Guide - Fundamentals for more information about PL/SQL Server Pages
See Also:
This section discusses the procedural capabilities of Oracle. It includes the following sections:
Chapter 19, "Dependencies Among Schema Objects" for information about the dependencies of procedures, functions, and packages, and how Oracle manages these dependencies
See Also:
Oracle allows you to access and manipulate database information using procedural schema objects called PL/SQL program units. Procedures, functions, and packages are all examples of PL/SQL program units.
PL/SQL is Oracle's procedural language extension to SQL. It extends SQL with flow control and other statements that make it possible to write complex programs. The PL/SQL engine is the tool you use to define, compile, and execute PL/SQL program units. This engine is a special component of many Oracle products, including the Oracle server.
While many Oracle products have PL/SQL components, this chapter specifically covers the procedures and packages that can be stored in an Oracle database and processed using the Oracle server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's documentation.
Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use. You can execute a procedure or function interactively by:
Figure 16-3 illustrates a simple procedure that is stored in the database and called by several different database applications.
Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, procedure as used in the remainder of this chapter means procedure or function.
The stored procedure in Figure 16-3, which inserts an employee record into the EMP table, is shown in Figure 16-4.
All of the database applications in Figure 16-3 call the HIRE_EMP procedure. Alternatively, a privileged user can use Oracle Enterprise Manager or SQL*Plus to execute the HIRE_EMP procedure using the following statement:
EXECUTE hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, \ 500, NULL, 20);
This statement places a new employee record for TSMITH in the EMP table.
A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.
Figure 16-5 illustrates a package that encapsulates a number of procedures used to manage an employee database.
Database applications explicitly call packaged procedures as necessary. After being granted the privileges for the EMP_MGMT package, a user can explicitly execute any of the procedures contained in it. For example, Oracle Enterprise Manager or SQL*Plus can issue the following statement to execute the HIRE_EMP package procedure:
EXECUTE emp_mgmt.hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);
Packages offer several development and performance advantages over standalone stored procedures.
A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and executed as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions allow you to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.
Procedures provide advantages in the following areas:
Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that execute with the definer's privileges. For example, you can grant users access to a procedure that updates a table but not grant them access to the table itself. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.
An invoker-rights procedure inherits privileges and schema context from the procedure that calls it. In other words, an invoker-rights procedure is not tied to a particular user or schema, and each invocation of an invoker-rights procedure operates in the current user's schema with the current user's privileges. If you are an application developer, invoker-rights procedures make it easy for you to centralize application logic, even when the underlying data is divided among user schemas.
For example, a a user who executes an update procedure on the EMP table as a manager can update salary, whereas a user who executes the same procedure as a clerk can be restricted to updating address data.
Stored procedures can improve database performance in several ways:
Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.
Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.
For example, procedures can be written to insert, update, or delete employee records from the EMP table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.
Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.
For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modifications.
Use the following guidelines when designing stored procedures:
A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.
As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but it does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.
In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.
Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package.
A stored procedure depends on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the definition of a table referenced by a procedure, the procedure must be recompiled to validate that it will continue to work as designed. Usually, Oracle automatically administers such dependency management.
|
See Also:
Chapter 19, "Dependencies Among Schema Objects" for more information about dependency tracking |
A PL/SQL procedure executing on an Oracle server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine executes in a separate address space from that of the Oracle server.
|
See Also:
Oracle9i Application Developer's Guide - Fundamentals for more information about external procedures |
Table functions are defined as functions that can produce a set of rows as output. In other words, table functions return a collection type instance (nested table and VARRAY datatypes). Oracle9i allows users to define table functions.
Oracle9i, Release 1 (9.0.1), allows table functions to pipeline results (return results iteratively) out of the functions. This can be achieved by either providing an implementation of the ODCITable interface, or using native PL/SQL instructions.
Pipelining helps to improving the performance of a number of applications, such as Oracle Warehouse Builder (OWB) and cartridges groups.
The ETL (Extraction-Transformation-Load) process in data warehouse building extracts data from an OLTP system. The extracted data passes through a sequence of transformations (written in procedural languages such as PL/SQL) before it is loaded into a data warehouse.
Oracle9i, Release 1 (9.0.1), allows parallel execution of table and non-table functions. Parallel execution provides the following extensions:
The function developer specifies how the input rows should be partitioned between parallel instances of the function.
Packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the database.
You create a package in two parts: the specification and the body. A package's specification declares all public constructs of the package and the body defines all constructs (public and private) of the package. This separation of the two parts provides the following advantages:
Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the following areas:
Stored packages allow you to encapsulate or group stored procedures, variables, datatypes, and so forth in a single named, stored unit in the database. This strategy provides better organization during the development process.
Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.
The methods of package definition allow you to specify which variables, cursors, and procedures are:
|
Directly accessible to the user of a package |
|
|
Hidden from the user of a package |
For example, a package can contain 10 procedures. You can define the package so that only 3 procedures are public and therefore available for execution by a user of the package. The remainder of the procedures are private and can only be accessed by the procedures within the package.
Do not confuse public and private package variables with grants to PUBLIC.
An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.
A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always through the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.
Java, which was developed at Sun Microsystems, has emerged over the last several years as the object-oriented programming language of choice. It includes the following concepts:
The result is a language easily learned by existing C programmers, but it remains object-oriented and efficient for application-level programs.
This section covers some basic terminology for discussing details of Java application development in the Oracle9i environment.
|
See Also:
Oracle9i Java Stored Procedures Developer's Guide for more information about object-oriented programming and for pointers to additional reference material |
All object-oriented programming languages support the concept of a class. As with a table definition, a class provides a template for objects that share common characteristics. Each class can contain the following:
When you create an object from a class, you are creating an instance of that class. The instance contains the fields of an object, which are known as its data, or state.
When you create an instance, the attributes store individual and private information relevant only to the employee. That is, the information contained within an employee instance is known only for that single employee.
Attributes within an instance are known as fields. Instance fields are analogous to the fields of a relational table row. The class defines the fields, as well as the type of each field. You can declare fields in Java to be static, public, private, protected, or default access.
The language specification defines the rules of visibility of data for all fields. Rules of visibility define under what circumstances you can access the data in these fields.
The class also defines the methods you can invoke on an instance of that class. Methods are written in Java and define the behavior of an object. This bundling of state and behavior is the essence of encapsulation, which is a feature of all object-oriented programming languages. If you define an Employee class, declaring that each employee's id is a private field, other objects can access that private field only if a method returns the field. In this example, an object could retrieve the employee's identifier by invoking the Employee.getId() method.
In addition, with encapsulation, you can declare that the Employee.getId() method is private, or you can decide not to write an Employee.getId() method. Encapsulation helps you write programs that are reusable and not misused. Encapsulation makes public only those features of an object that are declared public; all other fields and methods are private. Private fields and methods can be used for internal object processing.
Java defines classes within a large hierarchy of classes. At the top of the hierarchy is the Object class. All classes in Java inherit from the Object class at some level, as you walk up through the inheritance chain of superclasses. When we say Class B inherits from Class A, each instance of Class B contains all the fields defined in class B, as well as all the fields defined in Class A. You can invoke any method on an instance of Class B that was defined in either Class A or B.
Instances of Class B are substitutable for instances of Class A, which makes inheritance another powerful construct of object-oriented languages for improving code reuse. You can create new classes that define behavior and state where it makes sense in the hierarchy, yet make use of preexisting functionality in class libraries.
Java supports only single inheritance; that is, each class has one and only one class from which it inherits. If you must inherit from more than one source, Java provides the equivalent of multiple inheritance, without the complications and confusion that usually accompany it, through interfaces. Interfaces are similar to classes; however, interfaces define method signatures, not implementations. The methods are implemented in classes declared to implement an interface. Multiple inheritance occurs when a single class simultaneously supports many interfaces.
As with other high-level computer languages, your Java source compiles to low-level machine instructions. In Java, these instructions are known as bytecodes (because their size is uniformly one byte of storage). Most other languages, such as C, compile to machine-specific instructions, such as instructions specific to an Intel or HP processor. Your Java source compiles to a standard, platform-independent set of bytecodes, which interacts with a Java virtual machine (JVM). In Oracle9i, this is known as Aurora. The JVM is a separate program optimized for the specific platform on which you execute your Java code. Your Java source is compiled into bytecodes, which are platform independent. Each platform has installed a JVM that is specific to its operating system. The Java bytecodes from your source get interpreted through the JVM into appropriate platform dependent actions.
When you develop a Java program, you use predefined core class libraries written in the Java language. The Java core class libraries are logically divided into packages that provide commonly-used functionality, such as basic language support (java.lang), I/O (java.io), and network access (java.net). Together, the JVM and core class libraries provide a platform on which Java programmers can develop with the confidence that any hardware and operating system that supports Java will execute their program. This concept is what drives the "write once, run anywhere" idea of Java.
Oracle's Java applications sit on top of the Java core class libraries, which in turn sit on top of the JVM. Because Oracle's Java support system is located within the database, the JVM interacts with the Oracle database libraries, instead of directly with the operating system.
Sun Microsystems furnishes publicly available specifications for both the Java language and the JVM. The Java language specification (JLS) defines things such as syntax and semantics, and the JVM specification defines the necessary low-level behavior for the "machine" that executes the bytecodes. In addition, Sun Microsystems provides a compatibility test suite for JVM implementors to determine if they have complied with the specifications. This test suite is known as the Java Compatibility Kit (JCK). Oracle's JVM implementation complies fully with JCK. Part of the overall Java strategy is that an openly specified standard, together with a simple way to verify compliance with that standard, allows vendors to offer uniform support for Java across all platforms.
When initializing the JServer, the initjvm.sql script creates the PL/SQL package DBMS_JAVA. Some entrypoints of DBMS_JAVA are for your use, and others are only for internal use. The corresponding Java class DbmsJava provides methods for accessing RDBMS functionality from Java.
The DBMS_JAVA package supplies the following entrypoints:
FUNCTION longname (shortname VARCHAR2) RETURN VARCHAR2
Return the full name from a Java schema object. Because Java classes and methods can have names exceeding the maximum SQL identifier length, Aurora uses abbreviated names internally for SQL access. This function simply returns the original Java name for any (potentially) truncated name. An example of this function is to print the fully qualified name of classes that are invalid:
select dbms_java.longname (object_name) from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID'; FUNCTION shortname (longname VARCHAR2) RETURN VARCHAR2
You can specify a full name to the database by using the shortname() routine of the DBMS_JAVA package, which takes a full name as input and returns the corresponding short name. This is useful when verifying that your classes loaded by querying the USER_OBJECTS view.
FUNCTION get_compiler_option(what VARCHAR2, optionName VARCHAR2) PROCEDURE set_compiler_option(what VARCHAR2, optionName VARCHAR2,
value VARCHAR2) PROCEDURE reset_compiler_option(what VARCHAR2, optionName VARCHAR2)
These three entry points control the options of the JServer Java and SQLJ compiler Oracle9i delivers.
|
See Also:
|
PROCEDURE set_output (buffersize NUMBER)
This procedure redirects the output of Java stored procedures and triggers to the DBMS_OUTPUT package.
PROCEDURE loadjava(options varchar2) PROCEDURE loadjava(options varchar2, resolver varchar2) PROCEDURE dropjava(options varchar2)
These procedures allow you to load and drop classes within the database using a call rather than through the loadjava or dropjava command-line tools. To execute within your Java application, do the following:
call dbms_java.loadjava('... options...'); call dbms_java.dropjava('... options...');
The options are identical to those specified for the loadjava and dropjava command-line tools. Separate each option with a blank. Do not separate the options with a comma. The only exception for this is the loadjava -resolver option, which contains blanks. For -resolver, specify all other options first, separate these options by a comma, and then specify the -resolver option with its definition. Do not specify the following options, because they relate to the database connection for the loadjava command-line tool: -thin, -oci8, -user, -password. The output is directed to stderr.
PROCEDURE grant_permission( grantee varchar2,
permission_type varchar2,
permission_name varchar2,
permission_action varchar2 )
PROCEDURE restrict_permission( grantee varchar2,
permission_type varchar2,
permission_name varchar2,
permission_action varchar2)
PROCEDURE grant_policy_permission( grantee varchar2,
permission_schema varchar2,
permission_type varchar2,
permission_name varchar2)
PROCEDURE revoke_permission(permission_schema varchar2,
permission_type varchar2,
permission_name varchar2,
permission_action varchar2)
PROCEDURE disable_permission(key number)
PROCEDURE enable_permission(key number)
PROCEDURE delete_permission(key number)
These entry points control the JVM permissions.
PROCEDURE start_debugging(host varchar2, port number,
timeout number)
PROCEDURE stop_debugging
PROCEDURE restart_debugging(timeout number)
These entry points start and stop the debug agent when debugging.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|