概念第08章服务器端编程plsql和java

1、any question please contact martin.tianView PDFView PDFPrevious NextPrevious Next8 Server-Side Program Java: PL/SQL and第 8 章 服务器端编程：PL/SQL 和 JavaChapter 7, SQL explains the Structured Query Language (SQL) language and how the database processes SQL statements. This chapter explains how Procedural La

2、nguage/SQL (PL/SQL) or Java programs stored in the database can use SQL.This chapter includes the following topics: Introduction to Server-Side Program Overview of PL/SQLo PL/SQL Subprogramso PL/SQL Packageso PL/SQL Anonymous Blockso PL/SQL Language Constructso PL/SQL Collections and Recordso How PL

3、/SQL Runs Overview of Java in Oracle Databaseo Overview of the Java Virtual Machine (JVM)第 7 章，SQL语句。本章使用 SQL。了结构化语言 (SQL) ，以及数据库如何处理 SQL在数据库中过程化语言（PL/SQL） 或Java 程序如何本章包含以下：服务器端编程简介PL/SQL 概述PL/SQL 子程序PL/SQL 包PL/SQLPL/SQL 语言结构PL/SQL 集合和 PL/SQL 如何运行o o o o ooOracle 数据库中的 Java 概述Java 虚拟机 (JVM) 的概述Java

4、编程环境ooo Java Program Environment Overview of Triggers触发器概述Advantages of Triggers Types of Triggers for Triggers Creation of Triggers Execution of Triggers Storage of Triggerso o o o oo触发器的优势 触发器类型触发时间创建触发器执行触发器触发器 o o o o ooSee Also:Chapter 7, SQLIntroduction to Server-Side Program另见：第 7 章, SQL服务器端编

5、程any question please contact martin.tianIn a nonprocedural language such as SQL, the set of data to be operated on is specified, but not the operations to be performed or the manner in which they are to be carried out. In a procedural language program, most statement execution depends on previous or

6、 subsequent statements and on control structures, such as loops or conditional branches, that are not available in SQL.For an illustration of the difference between procedural and nonprocedural languages, suppose that the following SQL statement queries the employees table:SELECT employee_id, depart

7、ment_id, last_name, salary FROM employees;The preceding statement requests data, but does not apply logic to the data. However, suppose you want an application to determine whether each employee in the data set deserves a raise based on salary and department performance. A necessary condition of a r

8、aise is that the employee did not receive more than three raises in the last five years. If a raise is called for, then the application must adjust the salary andthe manager; otherwise, the application must update a report.The problem is how procedural database applications requiring conditional log

9、ic and program flow control can use SQL. The basic development approaches are as follows:在 SQL 之类的非过程化语言中，指定的是要操作的数据集，而不是要执行什么操作或该操作应如何执行。在过程化语言，大多数语句的执行取前面或后面的语句及结构，如循环或条件分支，而这在 SQL 中决是不可用的。为演示过程化语言与非过程化语言之间的区别，假设如下的 SQL 语句employees 表：SELECT employee_id, department_id, last_name, salary FROM employ

10、ees;前面的语句只是请求数据，但并在数据上应用逻辑。但是，假设您希望应用程序能够确定在数据集中的每个员工是否应该基于工资和部门绩效来提高待遇。加薪的必要条件是，在过去五年中，加薪没有超过三次以上的雇员。如果要加薪，应用程序必须调整工资并发送该应用程序必须更新一份报告。邮件给经理 ；否则，问题是，需要条件逻辑和程序流的过程化数据库应用程序，如何才能使用 SQL。基本的开发如下所示：Use-side programto embed SQL statements in使用客户端编程，将 SQL 语句嵌入到由过程化语言如 C、 C+、 或Java 等编写的应用applications written

11、 in procedural languages such as C, C+, or JavaYou can place SQL statements in source code and submit it to a precompiler or Java translator before compilation. Alternatively, you can eliminate the precompilation step and use an API such as Java可以将 SQL 语句置入到源代码中，并在编译之前将其提交给预编译器或 Java 翻译器。或者，您可以消除预编译

12、步骤，而使用如 Java数据库连接 () 或 Oracle 调用接口 （OCI） 这样的 API ，使应Database Connectivity () or Oracle Call Interface (OCI) to用程序能够与数据库进行交互。enable the application to interact with the database.Use server-side programin the databaseto develop data logic that resides使用服务器端编程，开发驻留在数据库中的数据逻辑应用程序可以显式地调用以 PL/SQL (发音为 P L

13、 sequel) 或 Java 编写的子程序（过程和函数）。您还可以创建一个触发器，它是一An application can explicitly invoke stored subprograms (procedures and functions), written in PL/SQL (pronounced P L sequel) or Java. You can also create a trigger, which is named program unit that is stored in the database and invoked in response to a s

14、pecified个在数据库中名程序单元，在响应指定的时调用。any question please contact martin.tianevent.This chapter explains the second approach. The principal benefit of server-本章了第二种。服务器端编程的主要好处，是内置于数据库的功能side programis that functionality built into the database can be可以在任意位置部署。由数据库（而不是由应用程序）确定在给定的操作系统上执行任务的最佳方式。此外，通过在服务器上集中

15、应用程序处理，子程序增加可伸缩性，使客户端能够重用代码。由于子程序调用快速而高效，仅deployed anywhere. The database and not the application determines the best way to perform tasks on a given operating system. Also, subprograms increase scalability by centralizing application processing on仅一个调用就可以启动一个计算密集型的子程序，减少了网络流量。the server, enablings

16、to reuse code. Because subprogram calls arequick and efficient, a single call can start a compute-intensive stored subprogram, reducing network traffic.You can use the following languages to store data logic in Oracle Database:在 Oracle 数据库中可以使用以下语言来数据逻辑：PL/SQLPL/SQLPL/SQL is the Oracle Database proc

17、edural extension to SQL. PL/SQL is integrated with the database, supporting all Oracle SQL statements, functions, and data types. Applications written in database APIs can invoke PL/SQL stored subprograms and send PL/SQL code blocks to the database for execution.JavaPL/SQL 是 Oracle 数据库SQL 的过程化扩展。PL/

18、SQL 与数据库集成，支持所有 Oracle SQL 语句、 函数、和数据类型。由数据库API 编写的应用程序，可以调用 PL/SQL代码块到数据库以被执行。子程序，或发送 PL/SQLJavaOracle Database also provides support for develo, storing, andOracle 数据库还对开发、和部署 Java 应用程序提供支持。deploying Java applications. Java stored subprograms run in theJava序。Java 口。子程序在数据库中运行，并于在中间层上运行的程database a

19、nd are independent of programs that run in the middle tier. Java stored subprograms interface with SQL using a similar子程序使用与 PL/SQL 类似的运行模型来与 SQL 接execution mSee Also:to PL/SQL.另见：-Side Database Program to learn about embedding SQL客户端数据库编程了解如何在预编译器和 API 中嵌入 SQLwith precompilers and APIsOracle Databa

20、se 2 Day Developers Guide for an introduction toOracle 数据库 2 日开发指南 有关数据库应用程序开发的简介Oracle Database application developmentOracle Database Advanced Application Developers Guide to learnOracle 数据库高级应用开发指南了解如何选择一个编程环境how to choose a programenvironmentOverview of PL/SQLPL/SQL 概述PL/SQL 提供了一种服务器端的，PL/SQL pr

21、ovides a server-side, stored procedural language that is easy-to-的过程化语言，它易于使用并与 SQLany question please contact martin.tianuse, seamless with SQL, robust, portable, and secure. You can access and manipulate database data using procedural schema objects called PL/SQL program units.PL/SQL program units

22、 generally are categorized as follows: A subprogram is a PL/SQL block that is stored in the database and can be called by name from an application. When you create a subprogram, the database parses the subprogram and stores its parsed representation in the database. You can declare a subprogram as a

23、 procedure or a function. An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.The PL/SQL compiler and interpreter are embedded in Oracle SQL Developer, givi

24、ng developers a consistent and leveraged development无缝集成，而且健壮、可移植、且安全。您可以使用被称为 PL/SQL 程序单元的过程化模式对象，来和操作数据库数据。PL/SQL 程序分为如下几类：PL/SQL 子程序，它在数据库中，可以从应用程序按名称调用。当你创建一个子程序时，数据库该子程序，并将其已的表示形式在数据库中。您可以将一个子程序为一个过程或函数。PL/SQL块，它出现在您的应用，没有名字，也不在，PL/SQL 块可以出现在任何 SQL数据库中。在很多的应用语句可以出现的地方。Oracle SQL Developer 中嵌入了

25、PL/SQL 编译器和解释器，给与开发一个在客户端和服务器上一致和有效的开发模式。此外，PL/SQL过程可mon bothand server. Also, PL/SQL stored procedures can be从多种数据库客户端调用，比如 Pro*C、Oracle Reports 和 Oracle Forms 等。、 ODBC 、或 OCI，以及called from several databases, such as Pro*C, ODBC, or OCI,and from Oracle Reports and Oracle Forms.See Also: Tools for D

26、atabase Developers另见：数据库开发工具 Oracle Database PL/SQL Language Reference for complete information about PL/SQL, including packagesPL/SQL SubprogramsA PL/SQL subprogram is a named PL/SQL block that permits the caller to supply parameters that can be input only, output only, or input and output values.

27、A subprogram solves a specific problem or performs related tasks and serves as a building block for modular, maintainable database applications.A subprogram is either a procedure or a function. Procedures and functions are identical except that functions always return a single value to the caller, w

28、hereas procedures do not. The term procedure in this chapter means procedure or function.See Also: Pro*C/C+ Programmers Guide and Pro*COBOL ProgrammersOracle 数据库 PL/SQL 语言参考有关 PL/SQL 及其包的完整PL/SQL 子程序PL/SQL 子程序是一个命名的 PL/SQL 块，调用方提供参数（可以是输入参数、 输出参数、或输入输出参数值）。子程序解决某一具体问题，或执行相关的任务，并作为模块化的、 可维护的数据库应用程序的构

29、造块。子程序是一个过程或函数。过程和函数基本相同，区别在于函数始终向调用者返回单个值，而过程不返回任何值。在本章中的过程一词，可能代表过程或函数。另见： Pro*C/C + + 程序员指南和Pro*COBOL 程序员指南了解这any question please contact martin.tianGuide to learn about stored procedures in these languages些语言中的过程 Oracle Database PL/SQL Language Reference Oracle 数据库 PL/SQL 语言参考Advantages of PL/SQ

30、L SubprogramsAs explained in Introduction to Server-Side ProgramPL/SQL 子程序的优势如服务器端编程所述，服务器端编程相对于客户端编程有很多的好处。PL/SQL 子程序提供了以下优势：, server-sideprogramhas many advantages over-side program. PL/SQLsubprograms provide the following advantages:Improved performance提高性能The amount of information that an applic

31、ation musd相比发出一个个 SQL 语句，或将整个 PL/SQL 块的文本发送ooover a network is small compared with issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle Database, because the information is sent only once and thereafter invoked when it is used.到数据库中，应用程序必须通过网络发送的的量是很小的，因为只发送一次，并随

32、后在使用时才调用它。The compiled form of a procedure isily available in the已编译形式的过程在数据库中是随时可用的，所以在执行时不需要编译。oodatabase, so no compilation is required at execution time.If the procedure is present in the shared pool of the SGA, then the database need not retrieve it from disk and can begin execution immediately

33、.如果该过程已在 SGA 的共享索它，就可以立即开始执行。，则数据库不需要从磁盘检ooMemory allocation内存分配Because stored procedures take advantage of the shared memory capabilities of Oracle Database, it must load only a single copy of the procedure into memory for execution by multiple users. Sharing code among users results in a substal r

34、eduction in database memory requirements for applications.Improved productivity因为过程利用了数据库的共享内存功能，由对多个用户执行的同一过程，数据库只须将该过程加载一次到内存中。在多个用户间共享代码，使应用程序对数据库内存的要求大幅减少。提高生产率Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundan

35、t coding. For example, you can write procedures to manipulate rows in the employees table. Any application can call these procedures without requiring SQL statements to be rewritten.过程增加了开发生产率。通过一组通用的过程来设计应用程序，您可以避免冗余编码。例如，您可以编写过程，以处理employees 表中的行。任何应用程序可以调用这些过程，而无需重写 SQL 语句。如果数据管理改变了，则只须修改过程，而不必修改

36、使用过程的应用程序。any question please contact martin.tianIf the methods of data management change, then only the procedures must be modified, not the applications that use the procedures.Stored procedures are perhaps the best way to achieve code reuse.过程也许是实现代码重用的最好方式。因为连接到数据库的任何Because anyapplication writt

37、en in any language that客户端应用程序，无论它是用何种语言编写的，都可以调用程，过程提供了在所有环境中最大的代码重用。过connects to the database can invoke stored procedures, theyprovide Integrityum code reuse in all environments.完整性Stored procedures improve the integrity and consistency of your过程能改进您的应用程序完整性和一致性。通过过程开发应用程序，您可以减少编码错误的可能性。一组公用的appl

38、ications. By develoapplications around a common group ofprocedures, you reduce the likelihood of coding errors.For example, you can test a subprogram to guarantee that it returns an accurate result and, after it is verified, reuse it in any number of applications without retesting. If the data struc

39、tures referenced by the procedure are altered, then you must only recompile the procedure. Applications that call the procedure do not necessarily require modifications.Security with definers rights procedures例如，您可以测试一个子程序，来保证它将返回一个准确的结果，验证后，可以在任意数量的应用重用它，而无需重新测试。如果该过程所的数据结构改变了，则只须重新编译该过程。调用该过程的应用程序

40、不需要修改。定义者权限程序的安全性Stored procedures can help enforce data security (see Overview of Database Security). A definers rights procedure executes with the privileges of its owner, not its current user. Thus, you can restrict the database operations that users perform by allowing them to access data only t

41、hrough procedures and functions that run with the definers privileges.For example, you can grant users access to a procedure that updates a table but not grant access to the table itself. When a user invokes the procedure, it runs with the privileges of its owner. Users who have only the privilege t

42、o run the procedure (but not privileges to query, update, or delete from the underlying tables) can invoke the procedure but not manipulate table data in any other way.Inherited privileges and schema context with invokers rights procedures过程有助于强制执行数据安全 （请参见数据库安全性概述）。定义者权限过程按其所有者的权限来执行，而不是按当前用户的权限。因此

43、，您可以通过让用户只通过按定义者权限运行的过程和函数来数据，以限制用户执行的数据库操作。例如，您可以只授予用户更新表的过程的权限，而不授予其表本身的权限。当用户调用该过程时，它按其所有者的权限运行。只运行该过程（但对其基础表不具有、 更新、或删除权限）的用户可以调用该过程，但不能以除此之外的任何其他方式操作表数据。继承权限与调用者权限过程的模式上下文any question please contact martin.tianAn invokers rights procedure executes in the current users schema with the current us

44、ers privileges. In other words, an invokers rights procedure is not tied to a particular user or schema. Invokers rights procedures make it easy for application developers to centralize application logic, even when the underlying data is divided among user schemas.For example, an hr_manager user who

45、 runs an update procedure on the hr.employees table can update salaries, whereas an hr_clerk who runs the same procedure is restricted to updating address data.See Also:调用者权限过程以当前用户的权限在当前用户的模式中执行。换句话说，调用者权限过程不依赖于特定用户或模式。调用者权限过程调用使应用程序开发更易于集中处理应用程序逻辑，但是其基础数据可能会分散到多个用户模式。例如，一个 hr_manager 用户可以运行更新过程来更新

46、在hr.employees 表中的薪水，而一个 hr_clerk 用户运行相同的过程却只能更新地址数据。另见：Oracle Database PL/SQL Language Reference for an overview of PL/SQL subprogramsOracle Database Security Guide to learn more about definers and invokers rightsOracle 数据库 PL/SQL 语言参考关于 PL/SQL 子程序的概述Oracle 数据库安全指南了解有关定义者和调用者权限Creation of PL/SQL Sub

47、programsPL/SQL 子程序的创建在模式级别使用 CREATE PROCEDURE 或 CREATE FUNCTION 语句创A subprogram created at the schema level with the CREATE PROCEDURE or CREATE FUNCTION statement is a standalone stored subprogram. Subprograms defined in a package are called package subprograms and are considered a part of the packa

48、ge. The database stores subprograms in the data dictionary as schema objects.A subprogram has a specification, which includes descriptions of any parameters, and a body. Example 8-1 shows part of a creation statement建的一个子程序叫做子程序。在一个定义的子程序称为程序，并被认为是包的一部分。数据库将子程序作为模式对象中。在数据字典一些参数说明和一个程序体。例如，示例 8-1每个子程

49、序有一个规范，PL/SQL 过程 hire_employees 的语句的一部分。该过程向显示了创建for the standalone PL/SQL proceduire_employees. The procedureemployees 表中一行。inserts a row into the employees table.Example 8-1 PL/SQL Procedure示例 8 1 PL/SQL 过程any question please contact martin.tianCREATE PROCEDUire_employees ISCREATE PROCEDUire_emplo

50、yees IS(p_last_name VARCHAR2, p_job_id VARCHAR2,(p_last_name VARCHAR2, p_job_id VARCHAR2,p_manager_id NUMBER, p_hire_date DATE,p_manager_id NUMBER, p_hire_date DATE,p_salary NUMBER, p_commission_pct NUMBER,p_salary NUMBER, p_commission_pct NUMBER,p_department_id NUMBER)p_department_id NUMBER)BEGIN.I

51、NSERT INTO employees (employee_id, last_name, job_id, manager_id, hire_date,salary, commission_pct, department_id) VALUES (emp_sequence.NEXTVAL, p_last_name,p_job_id, p_manager_id, p_hire_date,p_salary, p_commission_pct, p_department_id);. END;See Also:BEGIN.INSERT INTO employees (employee_id, last_

52、name, job_id, manager_id, hire_date,salary, commission_pct, department_id) VALUES (emp_sequence.NEXTVAL, p_last_name,p_job_id, p_manager_id, p_hire_date,p_salary, p_commission_pct, p_department_id);. END;另见：Oracle Database 2 Day Developers Guide to learn how to create subprogramsOracle Database PL/S

53、QL Language Reference to learn about theOracle 数据库 2 日开发指南 了解如何创建子程序Oracle 数据库 PL/SQL 语言参考了解 CREATE PROCEDURE 命令dCREATE PROCEDUREExecution of PL/SQL SubprogramsUsers can execute a subprogram interactively by: Using an Oracle tool, such as SQL*Plus or SQL Developer (see Tools for Database Developers)

54、PL/SQL 子程序的执行用户可以通过以下方式交互地执行一个子程序：使用 Oracle 工具，如 SQL * Plus 或 SQL Developer （请参见数据库开发的工具） Calling it explicitly in the code of a database application, such as an在一个数据库应用程序（如 Oracle Forms 或预编译器应用程序）代码中显式调用它 （请参见“客户端数据库编程”）Oracle Forms or precompiler application (see -Side DatabaseProgram)any question

55、 please contact martin.tian Calling it explicitly in the code of another procedure or trigger 在另一个过程或触发器的代码中显式调用它Figure 8-1 shows different database applications callinghire_employees.Figure 8-1 Calling a PL/SQL Stored Procedure图 8-1 显示了调用 hire_employees 的不同数据库应用程序。图 8-1 调用 PL/SQL过程Description of Figure 8-1 Calling a PL/SQL Stored ProcedureDescription of Fig