Oracle PL/SQL 概述

原创

mb61bc2c8929091 2021-12-31 14:02:24 ©著作权

©著作权归作者所有：来自51CTO博客作者mb61bc2c8929091的原创作品，请联系作者获取转载授权，否则将追究法律责任

一.PL/SQL 定义

PL/SQL is theOracle Database procedural extension to SQL. PL/SQL is integrated with thedatabase, supporting all Oracle SQL statements, functions, and datatypes. Applications written in database APIs can invoke PL/SQL storedsubprograms and send PL/SQL code blocks to the database for execution.

PL/SQL providesa server-side, stored procedural language that is easy-to-use, seamless withSQL, robust, portable, and secure. You can access and manipulate database datausing procedural schemaobjects called PL/SQL program units.

PL/SQL program unitsgenerally are categorized as follows:

（1）A subprogram is a PL/SQLblock that is stored in the database and can be called by name from anapplication. When you create a subprogram, the database parses the subprogramand stores its parsed representation in the database. You can declare asubprogram as a procedure or a function.

（2）An anonymous block is aPL/SQL block that appears in your application and is not named or stored in thedatabase. In many applications, PL/SQL blocks can appear wherever SQLstatements can appear.

The PL/SQLcompiler and interpreter are embedded in Oracle SQL Developer, givingdevelopers a consistent and leveraged development model on both client andserver. Also, PL/SQL storedprocedures can be called from several database clients, such as Pro*C,JDBC, ODBC, or OCI, and from Oracle Reports and Oracle Forms.

二.PL/SQL 架构

2.1 PL/SQL Engine

The PL/SQL compilation and run-time systemis an engine that compiles and runs PL/SQL units. The engine can be installedin the database or in an application development tool, such as Oracle Forms.

--PL/SQL 编译和run-time 系统是一个引擎，其用来编译和执行PL/SQL 单元。这个引擎可以安装在数据库中或者在应用开发工具里，如Oracle Forms。

In eitherenvironment, the PL/SQL engine accepts as input any valid PL/SQL unit. Theengine runs procedural statements, but sends SQL statements to the SQL enginein the database, as shown in Figure1-1.

--PL/SQL 引擎接收任何有效的PL/SQL 单元，然后执行里面的过程语句，但是对于SQL 语句会丢给SQL引擎处理。

Typically, thedatabase processes PL/SQL units.

When an applicationdevelopment tool processes PL/SQL units, it passes them to its local PL/SQLengine. If a PL/SQL unit contains no SQL statements, the local engine processesthe entire PL/SQL unit. This is useful if the application development tool canbenefit from conditional and iterative control.

For example,Oracle Forms applications frequently use SQL statements to test the values offield entries and do simple computations. By using PL/SQL instead of SQL, theseapplications can avoid calls to the database.

2.2 PL/SQL Unitsand Compilation Parameters

A PL/SQL unit isone of these:

PL/SQL 单元有如下类型：

(1) PL/SQL anonymous block

(2) FUNCTION

(3) LIBRARY

(4) PACKAGE

(5) PACKAGE BODY

(6) PROCEDURE

(7) TRIGGER

(8) TYPE

(9) TYPE BODY

PL/SQL units areaffected by PL/SQL compilation parameters (a categoryof database initializationparameters). Different PL/SQL units—for example, a package specification andits body—can have different compilation parameter settings.

--PL/SQL 单元受PL/SQL 编译参数的影响，不同的单元可以设置不同的编译参数。

Table1-2 summarizes the PL/SQL compilation parameters. To display thevalues of these parameters for specified or all PL/SQL units, query the staticdata dictionary view ALL_PLSQL_OBJECT_SETTINGS. For information about thisview, see OracleDatabase Reference.

可以通过 ALL_PLSQL_OBJECT_SETTINGS视图查看所有PL/SQL 单元对象信息：

SQL> SELECT distinct type FROMALL_PLSQL_OBJECT_SETTINGS;

TYPE

------------

PROCEDURE

PACKAGE

PACKAGE BODY

TYPE BODY

TRIGGER

FUNCTION

TYPE

7 rows selected.

Table 1-2 PL/SQL Compilation Parameters

--PL/SQL 相关的编译参数：

Parameter	Description
PLSCOPE_SETTINGS	Controls the compile-time collection, cross-reference, and storage of PL/SQL source text identifier data. Used by the PL/Scope tool (see Oracle Database Advanced Application Developer's Guide). For more information about `PLSCOPE_SETTINGS`, see Oracle Database Reference.
PLSQL_CCFLAGS	Enables you to control conditional compilation of each PL/SQL unit independently. For more information about `PLSQL_CCFLAGS`, see "How Conditional Compilation Works" and Oracle Database Reference.
PLSQL_CODE_TYPE	Specifies the compilation mode for PL/SQL units—`INTERPRETED` (the default) or `NATIVE`. For information about which mode to use, see"Determining Whether to Use PL/SQL Native Compilation". Oracle PL/SQL 优化与调整 – PL/SQL Native Compilation 说明 If the optimization level (set by `PLSQL_OPTIMIZE_LEVEL`) is less than 2: （1）The compiler generates interpreted code, regardless of PLSQL_CODE_TYPE. （2）If you specify NATIVE, the compiler warns you that NATIVE was ignored. For more information about `PLSQL_CODE_TYPE`, see Oracle Database Reference.
PLSQL_OPTIMIZE_LEVEL	Specifies the optimization level at which to compile PL/SQL units (the higher the level, the more optimizations the compiler tries to make). `PLSQL_OPTIMIZE_LEVEL=1` instructs the PL/SQL compiler to generate and store code for use by the PL/SQL debugger. For more information about `PLSQL_OPTIMIZE_LEVEL`, see "PL/SQL Optimizer" and Oracle Database Reference.
PLSQL_WARNINGS	Enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors. For more information about `PLSQL_WARNINGS`, see "Compile-Time Warnings" and Oracle Database Reference.
NLS_LENGTH_SEMANTICS	Enables you to create `CHAR` and `VARCHAR2` columns using either byte-length or character-length semantics. For more information about byte and character length semantics, see "CHAR and VARCHAR2 Variables". For more information about `NLS_LENGTH_SEMANTICS`, see Oracle Database Reference.

Note:

The compilerparameter PLSQL_DEBUG, which specifies whether to compile PL/SQL units fordebugging, is deprecated. To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1.

--PLSQL_DEBUG 参数已经过期，在建议使用，如果启用debug，可以设置PLSQL_OPTIMIZE_LEVEL为1.

The compile-timevalues of the parameters in Table1-2 are stored with the metadata of each stored PL/SQL unit, whichmeans that you can reuse those values when you explicitly recompile the unit.(A stored PL/SQL unit is created with one ofthe "CREATE[OR REPLACE] Statements". An anonymous block is not a stored PL/SQLunit.)

参数的值保存在每个PL/SQL 单元的元数据里，所以可以重用这些值。

To explicitlyrecompile a stored PL/SQL unit and reuse its parameter values, you must usean ALTER statement with both the COMPILE clause andthe REUSE SETTINGS clause. For moreinformation about REUSESETTINGS, see "compiler_parameters_clause".(All ALTER statements have this clause. For a listof ALTER statements, see "ALTERStatements".)

--显示的recompile PL/SQL 单元，并重用之前的参数，必须使用alter 命令，如：ALTERFUNCTION oe.get_bal COMPILE REUSE SETTINGS;

三.PL/SQL 优势

PL/SQL has these advantages:

(1) TightIntegration with SQL

(2) HighPerformance

(3) HighProductivity

(4) Portability

(5) Scalability

(6) Manageability

(7) Supportfor Object-Oriented Programming

(8) Supportfor Developing Web Applications

(9) Supportfor Developing Server Pages

3.1 Tight Integration with SQL --与SQL的紧密结合

PL/SQL is tightly integrated with SQL, themost widely used database manipulation language. For example:

（1）PL/SQL letsyou use all SQL data manipulation, cursor control, and transaction controlstatements, and all SQL functions, operators, and pseudocolumns.

（2）PL/SQL fullysupports SQL data types.

You need notconvert between PL/SQL and SQL data types. For example, if your PL/SQL programretrieves a value from a column of the SQL type VARCHAR2, it can storethat value in a PL/SQL variable of the type VARCHAR2.

You can give aPL/SQL data item the data type of a column or row of a database table withoutexplicitly specifying that data type (see "%TYPEAttribute" and "%ROWTYPEAttribute").

（3）PL/SQL letsyou run a SQL query and process the rows of the result set one at a time(see "Processinga Query Result Set One Row at a Time").

PL/SQL supportsboth static and dynamic SQL. Static SQL is SQL whose full text isknown at compilation time. Dynamic SQL is SQL whose full text is notknown until run time. Dynamic SQL enables you to make your applications moreflexible and versatile. For more information, see Chapter6, "PL/SQL Static SQL" and Chapter7, "PL/SQL Dynamic SQL".

--PL/SQL 支持静态和动态的SQL。

3.2 HighPerformance -- 高性能

PL/SQL lets you send a block of statements to the database,significantly reducing traffic between the application and the database.

--PL/SQL 发送一个语句块到DB，这样就显著的减少了应用和DB之间的信息传递。

3.2.1 Bind Variables

When you embed aSQL INSERT, UPDATE, DELETE, or SELECT statementdirectly in your PL/SQL code, the PL/SQL compiler turns the variables inthe WHERE and VALUES clauses into bind variables (fordetails, see"Resolutionof Names in Static SQL Statements"). Oracle Database can reuse theseSQL statements each time the same code runs, which improves performance.

--可以直接将SQL 语句签入PL/SQL 代码块里，PL/SQL 编译器会将where和values值写入绑定变量。

PL/SQL does notcreate bind variables automatically when you use dynamic SQL, but you can usethem with dynamic SQL by specifying them explicitly (for details, see "EXECUTEIMMEDIATE Statement").

--当使用动态绑定时，不会自动创建绑定变量。

3.2.2 Subprograms

PL/SQLsubprograms are stored in executable form, which can be invoked repeatedly.Because stored subprograms run in the database server, a single invocation overthe network can start a large job. This division of work reduces networktraffic and improves response times. Stored subprograms are cached and sharedamong users, which lowers memory requirements and invocation overhead. For moreinformation about subprograms, see "Subprograms".

--PL/SQL subprograms 是一个PL/SQL block，PL/SQL 有两种类型的subprograms：存储过程和函数，可以被重复调用。 Subprograms 在db server 上运行，在server端有cache，可以被多个用户共享，从而可以降低内存需求，较少网络的传输。

3.2.3 Optimizer

The PL/SQLcompiler has an optimizer that can rearrange code for better performance. Formore information about the optimizer, see "PL/SQLOptimizer".

3.3 HighProductivity –高效率

PL/SQL lets you write compact code for manipulating data. Just as ascripting language like PERL can read, transform, and write data in files,PL/SQL can query, transform, and update data in a database.

PL/SQL has manyfeatures that save designing and debugging time, and it is the same in allenvironments. If you learn to use PL/SQL with one Oracle tool, you can transferyour knowledge to other Oracle tools. For example, you can create a PL/SQLblock in SQL Developer and then use it in an Oracle Forms trigger. For anoverview of PL/SQL features, see "MainFeatures of PL/SQL".

3.4 Portability –便携性

You can run PL/SQL applications on anyoperating system and platform where Oracle Database runs.

3.5 Scalability –扩展性

PL/SQL stored subprograms increasescalability by centralizing application processing on the database server. Theshared memory facilities of the shared server enable Oracle Database to supportthousands of concurrent users on a single node. For more information aboutsubprograms, see "Subprograms".

PL/SQL subprograms通过集中在DB sever 上申请进程来增加扩展性，在单节点上，shared memory 最大支持数千个并发的用户。

For furtherscalability, you can use Oracle Connection Manager to multiplex networkconnections. For information about Oracle Connection Manager, see OracleDatabase Net Services Reference.

3.6 Manageability –易管理性

PL/SQL stored subprograms increasemanageability because you can maintain only one copy of a subprogram, on thedatabase server, rather than one copy on each client system. Any number ofapplications can use the subprograms, and you can change the subprogramswithout affecting the applications that invoke them. For more information aboutsubprograms, see "Subprograms".

3.7 Support forObject-Oriented Programming

PL/SQL supports object-oriented programmingwith "AbstractData Types".

3.8 Supportfor Developing Web Applications

PL/SQL lets youcreate applications that generate web pages directly from the database,allowing you to make your database available on the Web and make back-officedata accessible on the intranet.

The program flowof a PL/SQL Web application is similar to that in a CGI PERL script. Developersoften use CGI scripts to produce web pages dynamically, but such scripts areoften not optimal for accessing the database. Delivering Web content withPL/SQL stored subprograms provides the power and flexibility of databaseprocessing. For example, you can use DML statements, dynamic SQL, and cursors.You also eliminate the process overhead of forking a new CGI process to handleeach HTTP request.

You canimplement a Web browser-based application entirely in PL/SQL with PL/SQLGateway and the PL/SQL Web Toolkit.

PL/SQL Gatewayenables a Web browser to invoke a PL/SQL stored subprogram through an HTTPlistener. mod_plsql, one implementation of the PL/SQL Gateway, is aplug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL storedsubprograms.

PL/SQL WebToolkit is a set of PL/SQL packages that provides a generic interface to usestored subprograms invoked by mod_plsql at run time. For informationabout packages, see "Packages".

3.9 Supportfor Developing Server Pages

PL/SQL ServerPages (PSPs) let you develop web pages with dynamic content. PSPs are analternative to coding a stored subprogram that writes the HTML code for a webpage one line at a time.

Special tags letyou embed PL/SQL scripts into HTML source text. The scripts run when Webclients, such as browsers, request the pages. A script can accept parameters,query or update the database, and then display a customized page showing theresults.

During development, PSPs can act like templates, with a static part for page layoutand a dynamic part for content. You can design the layouts using your favoriteHTML authoring tools, leaving placeholders for the dynamic content. Then, youcan write the PL/SQL scripts that generate the content. When finished, yousimply load the resulting PSP files into the database as stored subprograms.

四．PL/SQL 主要特点

PL/SQL combines the data-manipulating power of SQL with theprocessing power of procedural languages.

When you cansolve a problem with SQL, you can issue SQL statements from your PL/SQLprogram, without learning new APIs.

Like otherprocedural programming languages, PL/SQL lets you declare constants andvariables, control program flow, define subprograms, and trap run-time errors.

You can breakcomplex problems into easily understandable subprograms, which you can reuse inmultiple applications.

Topics

(1) ErrorHandling

(2) Blocks

(3) Variablesand Constants

(4) Subprograms

(5) Packages

(6) Triggers

(7) Inputand Output

(8) DataAbstraction

(9) ControlStatements

(10) ConditionalCompilation

(11) Processinga Query Result Set One Row at a Time

4.1 Error Handling

PL/SQL makes iteasy to detect and handle errors. When an error occurs, PL/SQL raises anexception. Normal execution stops and control transfers to theexception-handling part of the PL/SQL block. You do not have to check everyoperation to ensure that it succeeded, as in a C program. For more information,see Chapter11, "PL/SQL Error Handling".

4.2 Blocks

The basic unitof a PL/SQL source program is the block, which groups related declarationsand statements.

A PL/SQL blockis defined by the keywords DECLARE, BEGIN, EXCEPTION,and END. These keywords divide the block into a declarative part, anexecutable part, and an exception-handling part. Only the executable part isrequired. A block can have a label.

--PL/SQL block 由关键字:declare,begin,exception,end 定义。

Example1-1 shows the basic structure of a PL/SQL block. For syntax details,see "Block".

Example1-1 PL/SQL Block Structure

<< label >> (optional)

DECLARE -- Declarative part (optional)

--Declarations of local types, variables, & subprograms

BEGIN -- Executable part (required)

--Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part(optional)

--Exception handlers for exceptions (errors) raised in executable part]

END;

Declarations arelocal to the block and cease to exist when the block completes execution,helping to avoid cluttered namespaces for variables and subprograms.

Blocks can benested: Because a block is an executable statement, it can appear in anotherblock wherever an executable statement is allowed.

You can submit ablock to an interactive tool (such as SQL*Plus or Enterprise Manager) or embedit in an Oracle Precompiler or OCI program. The interactive tool or programruns the block one time. The block is not stored in the database, and for thatreason, it is called an anonymousblock (even if it has a label).

An anonymousblock is compiled each time it is loaded into memory, and its compilation hasthree stages:

--匿名块每次装载到内存时都需要进行编译，其编译过程如下：

(1) Syntax checking: PL/SQL syntaxis checked, and a parse tree is generated.

(2) Semantic checking: Typechecking and further processing on the parse tree.

(3) Code generation

Note:

An anonymousblock is a SQL statement.

4.3 Variables andConstants

PL/SQL lets youdeclare variables and constants, and then use them wherever you can use anexpression. As the program runs, the values of variables can change, but thevalues of constants cannot. For more information, see "Declarations" and "AssigningValues to Variables".

4.4 Subprograms

A PL/SQL subprogram is a named PL/SQL block thatcan be invoked repeatedly. If the subprogram hasparameters, their values can differ for each invocation. PL/SQL has two typesof subprograms, procedures and functions. A function returns a result. For moreinformation about PL/SQL subprograms, see Chapter8, "PL/SQL Subprograms."

--PL/SQL 有两种类型的subpgograms：存储过程和函数。

PL/SQL also letsyou invoke external programs written in other languages. For more information,see "ExternalSubprograms".

4.5 Packages

A package isa schema object that groups logically related PL/SQL types, variables,constants, subprograms, cursors, and exceptions. A package is compiled andstored in the database, where many applications can share its contents. You canthink of a package as an application.

You can writeyour own packages—for details, see Chapter10, "PL/SQL Packages." You can also use the manyproduct-specific packages that Oracle Database supplies. For information aboutthese, see OracleDatabase PL/SQL Packages and Types Reference.

4.6 Triggers

A trigger isa named PL/SQL unit that is stored in the database and run in response to anevent that occurs in the database. You can specify the event, whether thetrigger fires before or after the event, and whether the trigger runs for eachevent or for each row affected by the event. For example, you can create atrigger that runs every time an INSERT statement affectsthe EMPLOYEES table.

For moreinformation about triggers, see Chapter9, "PL/SQL Triggers."

4.7 Input and Output

MostPL/SQL input and output(I/O) is done with SQL statements that store data in database tables or querythose tables. For information about SQL statements, see OracleDatabase SQL Language Reference.

All other PL/SQLI/O is done with PL/SQL packages that Oracle Database supplies, which Table1-1 summarizes.

Table1-1 PL/SQL I/O-Processing Packages

Package	Description	More Information
`DBMS_OUTPUT`	Lets PL/SQL blocks, subprograms, packages, and triggers display output. Especially useful for displaying PL/SQL debugging information.	Oracle Database PL/SQL Packages and Types Reference
`HTF`	Has hypertext functions that generate HTML tags (for example, the `HTF`.`ANCHOR` function generates the HTML anchor tag `<A>`).	Oracle Database PL/SQL Packages and Types Reference
`HTP`	Has hypertext procedures that generate HTML tags.	Oracle Database PL/SQL Packages and Types Reference
`DBMS_PIPE`	Lets two or more sessions in the same instance communicate.	Oracle Database PL/SQL Packages and Types Reference
`UTL_FILE`	Lets PL/SQL programs read and write operating system files.	Oracle Database PL/SQL Packages and Types Reference
`UTL_HTTP`	Lets PL/SQL programs make Hypertext Transfer Protocol (HTTP) callouts, and access data on the Internet over HTTP.	Oracle Database PL/SQL Packages and Types Reference
`UTL_SMTP`	Sends electronic mails (emails) over Simple Mail Transfer Protocol (SMTP) as specified by RFC821.	Oracle Database PL/SQL Packages and Types Reference

To display output passed to DBMS_OUTPUT, you needanother program, such as SQL*Plus. To see DBMS_OUTPUT output withSQL*Plus, you must first issue the SQL*Pluscommand SET SERVEROUTPUT ON.

Some subprogramsin the packages in Table1-1 can both accept input and display output, but they cannot acceptdata directly from the keyboard. To accept data directly from the keyboard, usethe SQL*Plus commands PROMPT and ACCEPT.

4.8 Data Abstraction

Data abstraction lets you work with the essential properties of datawithout being too involved with details. You can design a data structure first,and then design algorithms that manipulate it.

Topics

(1) Cursors

(2) CompositeVariables

(3) %ROWTYPEAttribute

(4) %TYPEAttribute

(5) AbstractData Types

4.8.1 Cursors

A cursor isa pointer to a private SQL area that stores information about processing aspecific SQL statement or PL/SQL SELECT INTO statement. You canuse the cursor to retrieve the rows of the result set one at a time. You canuse cursor attributes to get information about the state of the cursor—forexample, how many rows the statement has affected so far. For more informationabout cursors, see "Cursors".

4.8.2 Composite Variables

A compositevariable has internal components, which you can access individually. Youcan pass entire composite variables to subprograms as parameters. PL/SQL hastwo kinds of composite variables, collections and records.

--PL/SQL 有两种类型的组合变量：collections 和 records。

In a collection, the internal components are always of the same data type,and are called elements. You access each element by its unique index.Lists and arrays are classic examples of collections.

Ina record, the internal components can be of different data types, and arecalled fields. You access each field by its name. A record variable canhold a table row, or some columns from a table row.

For moreinformation about composite variables, see Chapter5, "PL/SQL Collections and Records."

4.8.3 %ROWTYPEAttribute

The %ROWTYPE attributelets you declare a record that represents either a full or partial row of adatabase table or view. For every column of the full or partial row, the recordhas a field with the same name and data type. If the structure of the rowchanges, then the structure of the record changes accordingly. For moreinformation about %ROWTYPE, see "%ROWTYPEAttribute".

4.8.4 %TYPEAttribute

The %TYPE attributelets you declare a data item of the same data type as a previously declaredvariable or column (without knowing what that type is). If the declaration ofthe referenced item changes, then the declaration of the referencing itemchanges accordingly. The %TYPE attribute is particularly useful whendeclaring variables to hold database values. For more information about %TYPE,see "%TYPEAttribute".

4.8.5 AbstractData Types

An Abstract Data Type (ADT) consists of a data structureand subprograms that manipulate the data. The variables that form the datastructure are called attributes. The subprograms that manipulate theattributes are called methods.

ADTs are storedin the database. Instances of ADTs can be stored in tables and used as PL/SQLvariables.

ADTs let youreduce complexity by separating a large system into logical components, whichyou can reuse.

In the staticdata dictionary view *_OBJECTS, the OBJECT_TYPE of an ADT is TYPE.In the static data dictionary view *_TYPES, the TYPECODE of anADT is OBJECT.

For more information about ADTs, see "CREATETYPE Statement".

Note:

ADTs are alsocalled user-defined types and object types.

4.9 ControlStatements

Control statements are the most importantPL/SQL extension to SQL.

PL/SQL has three categories of controlstatements:

(1)Conditional selection statements, whichlet you run different statements for different data values.

For more information, see "ConditionalSelection Statements".

(2) Loop statements, which let you repeatthe same statements with a series of different data values.

For more information, see "LOOPStatements".

(3)Sequential control statements, which allowyou to go to a specified, labeled statement, or to do nothing.

For more information, see "SequentialControl Statements".

4.10 Conditional Compilation

Conditionalcompilation lets you customize the functionality in a PL/SQL applicationwithout removing source text. For example, you can:

（1）Use newfeatures with the latest database release, and disable them when running theapplication in an older database release.

（2）Activatedebugging or tracing statements in the development environment, and hide themwhen running the application at a production site.

For moreinformation, see "ConditionalCompilation".

4.11 Processing a Query Result Set One Row at a Time

PL/SQL lets youissue a SQL query and process the rows of the result set one at a time. You canuse a basic loop, as in Example1-2, or you can control the process precisely by using individualstatements to run the query, retrieve the results, and finish processing.

Example 1-2 Processing Query Result Rows One at a Time

BEGIN

FORsomeone IN (

SELECT * FROM employees

WHERE employee_id < 120

ORDER BY employee_id

)

LOOP

DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||

', Last name = ' ||someone.last_name);

ENDLOOP;

END;