Oracle应用开发实训指导.doc

Oracle 应用开发实训指导书第一章 PL/SQL概述为什么使用PL/SQL?一、PL/SQL 将SQL语言（4GL）的强大性和灵活性与3GL的过程性结构融为一体。PL/SQL是Procedural Language/SQL的缩写。正如其名字所示，PL/SQL通过增加了用在其它过程性语言中的结构对SQL进行了扩展，例如：变量和类型（包括预定义和用户定义）。控制结构，如-if-then-else语句和循环。过程和函数。对象类型和方法。过程性结构与Oracle SQL无缝地集成在一起，这便产生了一种结构化的强有力的语言。例如，假设我们想修改一个学生纪录的主修课。如果这个学生不存在，我们就想创建一个新纪录。那么，我们可以通过以下的PL/SQL代码实现这个目的：DECLARE /* Declare variables which will be used in SQL statements */ v_NewMajor VARCHAR2(10) := History; v_FirstName VARCHAR2(10) := Scott; v_LastName VARCHAR2(10) := Urman;BEGIN /* Update the students table. */ UPDATE students SET major = v_NewMajor WHERE first_name = v_FirstName AND last_name = v_LastName; /* Check to see if the record was found. If not, then we need to insert this record. */ IF SQL%NOTFOUND THEN INSERT INTO students (ID, first_name, last_name, major) VALUES (student_sequence.NEXTVAL, v_FirstName, v_LastName, v_NewMajor); END IF;END;这个例子包括了两个不同的SQL语句（Update和Insert），这是4GL的结构，同时还有3GL的结构（变量声明和IF条件语句）。PL/SQL是独一无二的，它融合了灵活的SQL和强大的并可具有可配置性的3GL。同时，可以使用该语言集成的过程性结构和数据库访问接口。这些特性使该语言更加健壮和功能强大，非常适合设计复杂的应用程序。二、PL/SQL和网络流量： 许多数据库应用程序的构建都使用客户机/服务器模型或者三层模型。在客户机/服务器中，程序本身在客户机上，它将请求发往数据库服务器，等待所需的信息。这些请求使用SQL语言实现。通常，这样做会产生许多次网络传输交互，每个SQL语言都有单独的传输交互。 如果使用了PL/SQL，几条SQL语句可被绑定在一个PL/SQL语句块中，作为一个单独的单元发往服务器。这样做网络流量会减少，使应用程序执行得更快。即使客户机服务器在同一台主机，这样也可以提高整个系统的性能，它减少了数据库对数据库的调用次数。PL/SQL打包的优点同样适用于B/S三层模型结构。在这种情况下，客户机（通常运行在HTML浏览器中）和应用程序服务器进行交互，而应用程序服务器随后将同数据库进行交互。后一种交互充分体现了PL/SQL的优点。PL/SQL的功能特性：一、PL/SQL的基本单元式语句块。所有的PL/SQL都是由语句块构成的，他们之间可以相互嵌套。通常，每一语句块负责完成程序中某单元的工作，这样每一语句块就可以分担不同的任务了。语句块拥有以下结构。DECLARE /* Declare variables which will be used in SQL statements */BEGIN END;只有可执行部分是必需的，声明部分和异常部分是可选的。可执行部分必须至少包括一条可执行语句。语句块的不同部分将分派DECLARE /* Declare variables which will be used in SQL statements */ v_NewMajor VARCHAR2(10) := History; v_FirstName VARCHAR2(10) := Scott; v_LastName VARCHAR2(10) := Urman;BEGIN /* Update the students table. */ UPDATE students SET major = v_NewMajor WHERE first_name = v_FirstName AND last_name = v_LastName; /* Check to see if the record was found. If not, then we need to insert this record. */ IF SQL%NOTFOUND THEN INSERT INTO students (ID, first_name, last_name, major) VALUES (student_sequence.NEXTVAL, v_FirstName, v_LastName, v_NewMajor); END IF;END;程序的不同处理功能。二、错误处理语句块的异常处理部分用于响应程序所遇到的运行时错误。通过将错误处理代码与程序主体分离可以使程序本身的结构变得清晰。例如，下面的PL/SQL语句块将进行这样的异常处理，它将错误发生的当前时间和遇到错误的用户都纪录下来。REM Error.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This block illustrates some of the error-handling featuresREM of PL/SQL.DECLARE v_ErrorCode NUMBER; - Code for the error v_ErrorMsg VARCHAR2(200); - Message text for the error v_CurrentUser VARCHAR2(8); - Current database user v_Information VARCHAR2(100); - Information about the errorBEGIN /* Code that processes some data here */EXCEPTION WHEN OTHERS THEN - Assign values to the log variables, using built-in - functions. v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_CurrentUser := USER; v_Information := Error encountered on | TO_CHAR(SYSDATE) | by database user | v_CurrentUser; - Insert the log message into log_table. INSERT INTO log_table (code, message, info) VALUES (v_ErrorCode, v_ErrorMsg, v_Information);END;/三、变量和类型在PL/SQL和数据库之间是通过变量传递消息的。一个变量是一个存储单元，程序可读取它，或给它赋值。在前面的例子，v_CurrentUser、v_ErrorCode和v_Information都是变量。变量是语句块的声明部分的。每一个变量都有与之相关的明确类型。类型定义了该变量能够存储任何类型的信息。PL/SQL变量可以使数据拥有的类型：Declarev_StudentName VARCHAR2(20);v_CurrentDate DATE;v_NumberCredits NUMBER(3);或者其它类型：DECLAREv_loopCounter BINARY_INTEGER;v_CurrentlyRegistered BOOLEAN;PL/SQL也支持用户定义类型：表和纪录。用户定义类型允许用户制定自己程序中要处理的数据结构。DECLARETYPE t_StudentRecord IS RECORD（FirstName VARCHAR2(10),LastName VARCHAR2(10),CurrentCredits NUMBER(3),);v_STUDENT t_studentRecord;四、条件语句PL/SQL程序可根据测试结果有条件地执行代码的不同部分。这样的语句称为条件语句。其主要结构就是IF语句。例如，下面的语句块将查询数据库以得到学生总数，并相应地向tem_table表中插入不同的信息：REM Collections.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This block demonstrates some PL/SQL collections.DECLARE TYPE t_IndexBy IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_Nested IS TABLE OF NUMBER; TYPE t_Varray IS VARRAY(10) OF NUMBER; v_IndexBy t_IndexBy; v_Nested t_Nested; v_Varray t_Varray;BEGIN v_IndexBy(1) := 1; v_IndexBy(2) := 2; v_Nested := t_Nested(1, 2, 3, 4, 5); v_Varray := t_Varray(1, 2);END;/五、循环结构PL/SQL支持不同类型的循环。循环用来反复执行相同的语句序列。REM SimpleLoop.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This block contains a simple loop.DECLARE v_LoopCounter BINARY_INTEGER := 1;BEGIN LOOP INSERT INTO temp_table (num_col) VALUES (v_LoopCounter); v_LoopCounter := v_LoopCounter + 1; EXIT WHEN v_LoopCounter 50; END LOOP;END;/六、过程和函数过程和函数是一种特殊类型的PL/SQL语句块，它以编译好的形式存储在数据库中，可被后来的语句块调用。例如，下面的语句创建一个过程PrintStudents,它利用DBMS_OUTPUT包在屏幕上显示出主修指定课程的所有学生的名和性：REM PrintStudents.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This script illustrates a stored procedure.set serveroutput onCREATE OR REPLACE PROCEDURE PrintStudents( p_Major IN students.major%TYPE) AS CURSOR c_Students IS SELECT first_name, last_name FROM students WHERE major = p_Major;BEGIN FOR v_StudentRec IN c_Students LOOP DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name | | v_StudentRec.last_name); END LOOP;END;/BEGIN PrintStudents(Computer Science);END;/七、游标游标用来处理从数据库中检索的多行纪律（使用Select 语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个纪录集。例如，下面的语句块检索出数据所有学生的名和性。REM CursorLoop.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This block illustrates a cursor fetch loop.DECLARE v_FirstName VARCHAR2(20); v_LastName VARCHAR2(20); - Cursor declaration. This defines the SQL statement to - return the rows. CURSOR c_Students IS SELECT first_name, last_name FROM students;BEGIN - Begin cursor processing. OPEN c_Students; LOOP - Retrieve one row. FETCH c_Students INTO v_FirstName, v_LastName; - Exit the loop after all rows have been retrieved. EXIT WHEN c_Students%NOTFOUND; /* Process data here */ END LOOP; - End processing. CLOSE c_Students;END;/八、包子程序、变量和类型可以分组进一个包中。包有两部分：规范和主体。总体来说，他们将相关对象一起存储在数据库中。例如，RoomPkg包就包含了如下过程，其中这些过程可以在rooms表中插入新教室纪律，以及从中删除教室纪录。九、集会PL/SQL集合十分类似于其他3GL中的数组。PL/SQL提供三种不同类型的集合：索引表、嵌套表和可变数组。以下的例子说明了集合的不同类型：REM Collections.sqlREM Chapter 1, Oracle9i PL/SQL Programming by Scott UrmanREM This block demonstrates some PL/SQL collections.DECLARE TYPE t_IndexBy IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_Nested IS TABLE OF NUMBER; TYPE t_Varray IS VARRAY(10) OF NUMBER; v_IndexBy t_IndexBy; v_Nested t_Nested; v_Varray t_Varray;BEGIN v_IndexBy(1) := 1; v_IndexBy(2) := 2; v_Nested := t_Nested(1, 2, 3, 4, 5); v_Varray := t_Varray(1, 2);END;/ num_copies NUMBER, num_out NUMBER, checked_out StudentList) NESTED TABLE checked_out STORE AS co_tab;第二章 开发及运行环境一、 应用程序模型和PL/SQL数据库应用程序可分为三个部分:用户界面 负责应用程序的外观。这一层处理用户输入和显示程序输出。应用逻辑层 控制应用程序所实现的功能。数据库 持久可靠地存储应用程序数据。为了编译并运行PL/SQL语句块，需要将PL/SQL语句块提交给PL/SQL引擎。同java虚拟机类似，PL/SQL引擎包括编译器和运行系统。二、PL/SQL位置1、服务器端PL/SQL这是PL/SQL引擎的最初位置。SQL*PLUS就是这样一个客户端应用程序范例。其将SQL语句和PL/SQL语句块提交给服务器。2、客户端PL/SQLPL/SQL引擎运行在客户端，PL/SQL语句块被包含在用这些工具编写的客户端应用程序中，例如，PL/SQL Forms,Reports. 三、开发工具：SQL*PLUS、Rapid SQL, DBParter, SQL Navigator, SQL-Programmer, PL/SQL Developer等等01、 SQL*PLUS连接数据库；执行SQL和PL/SQLREM File.sqlREM Chapter 2, Oracle9i PL/SQL Programming by Scott UrmanREM This file contains commands which can be executed from SQL*Plus.BEGIN FOR v_Count IN 1.10 LOOP INSERT INTO temp_table (num_col, char_col) VALUES (v_Count, Hello World!); END LOOP;END;/SELECT * FROM temp_table;2、 PL/SQL DeveloperGUI开发工具，由All round Automations 出品。自动化PL/SQL和SQL语句。支持调试PL/SQL过程。数据库浏览器。代码模板；支持第三方版本控制；通过一个外部插件；支持直接使用许多的SQL*PLUS命令；第三章 PL/SQL基本特性在Oracle中有两种PL/SQL：一种是数据库引擎（engine）的组成部分，另一种是嵌入到许多Oracle工具中得独立引擎。将它们分别称为数据库PL/SQL和工具PL/SQL。两者非常相似，都具有相同得编程结构、语法和逻辑机制，工具PL/SQL另外增加了用于支持工具需求得句法。例如：在窗体上设置下压式按扭，以定位到屏幕的底部，此动作可用Oracle Forms系统中的PL/SQL来编程实现。1PL/SQL字符集和所有其他程序设计语言一样，PL/SQL也有一字符集。读者能从键盘上输入的字符集是PL/SQL的字符。此外，在某些场合，还有使用某些字符的规定。我们将要详细介绍：l 用PL/SQL编程时可能使用的字符l 算术运算符l 关系运算符l 杂符号1）、合法字符用PL/SQL程序时，允许使用下列字符：l 所有大、小写字母l 数字0到9l 符号：（）+-*/ =！；：. %，“# $ & _ | ？ 其中一些字符用于编程，另一些用作算术运算操作符（除法、加法、幂等）及关系运算符（等于和不等于）。例如：在通信应用程序中，开发人员可能会使用变量名“area_code“存放客户的呼叫区号。相应的变量名area_code要符合本章“变量”一节所给出的变量命名规则。2）、算术运算符下面列出了PL/SQL中常用的算术运算符。如果读者使用过其他高级程序设计语言，想必不会陌生：运算符意义+加法-减法*乘法/除法*幂关系运算符下面列出了PL/SQL中常用的关系运算符。如果读者有使用其他程序设计语言的经验，一定见过这些符号：运算符意义不等于!=不等于=不等于大于=等于3）、杂符号PL/SQL为支持编程，还使用下述符号。下面列出了部分符号，它们是最常用的，也是使用PL/SQL的所有读者都必须了解的。表6-1 杂符号符号意义样例( )列表分隔(Jones,Roy,Abramson);语句结束Procedure_name(arg1,arg2).项分隔（在例子中，用分隔account与table_name）Select * from account.table_name;,字符串界定符if var1 = SANDRA:=赋值Rec_read := rec_read+1|并置Full_name:=Nahtan|Yebba-注释符-This is a comment/*与*/注释定界符/*This too is a comment */4）、变量变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称：l 变量必须以字母（AZ）开头。l 其后跟可选的一个或多个字母，数字（09）或特殊字符$、# 或_。l 变量长度不超过30个字符。l 变量名中不能有空格。我们来看几个实例。表6-2给出了样例变量名并评价了其合法性。表6-2 合法及不合法的变量名变量名合法否理由23_skidoo不合法必须以字母起头Nature_trali合 法Nature-trali不合法特殊字符只能是$#_love boat不合法不能含空格a_very_insignificant_variable_name不合法长于30个字符me_and$you合 法lots_of_$合 法23不合法未用字母起头5）、保留字保留字可视为PL/SQL版权所有的字符串。在定义变量名时，读者不能使用这些保留字。例如，词“loop”在PL/SQL中有特殊含义，因此下列代码是非法的：declareemployee varchar2(30);loop number;保留字不能用作变量名。尽管我们不推荐，但如果读者坚持，也可以连接两个保留字形成变量名（如loop_varchar2）。完整的PL/SQL保留字表可以在Oracle8文档中找到。2、常用数据类型到目前为止，我们讨论了在PL/SQL中编程时允许使用的字符，变量的命名和保留字。下面着重讨论数据类型。PL/SQL程序用于处理和显示多种类型的数据。和所有计算机软件一样，Oracle也将数据类型分成大量的子类。例如：数可分为整型（不允许有小数）和小数（有一位小数或多位小数）类。PL/SQL支持多种数据类型，本节讨论代码中最常用并且最实用的数据类型。l Varchar2l Numberl Datel Boolean1）、Varchar2Varchar2为可变长的字母数字数据类型。在PL/SQL中，varchar2的最大长度为32767字节。在declare部分其定义以分号（；）结束，所有varchar2变量的定义类似于下定义Variable_name varchar2(max_length);括号中的长度值为本变量的最大长度且必须是正整数，如：vc_field varchar2(10);在定义变量时，可以同时对其进行初始化，格式为：vc_field varchar2(10) :=STARTVALUE ;2）、numbernumber型数据可用来表示所有的数值数据，说明格式为：num_field number(precision, scale );其中，precision 可以有1到38个数字位，而scale表示在precision位数字中小数点后的数字位数，下面的定义：num_field number(12,2);表示num_field是一个整数部分最多10位，小数部分最多2位的变量。3）、date此数据类型用于保存固定长度的日期值，date变量的说明为：date_field date;在缺省时，Oracle以DD-MON-YY格式显示日期。4）、Boolean这种数据类型只有两个值：TRUE或FALSE。在使用boolean变量时，如果测试结果为TRUE，则做某事，否则做另外的事。例：如果试图考察某公司是否分发了一个10KB的2000年年度预算表，可用boolean变量，若该公司分发了此表，则该变量值为TRUE。3PL/SQL的组件下面讨论PL/SQL是如何构成的。PL/SQL提供了一组标准的自计算机出现以来开发人员一直在使用的程序化了的技术：逻辑、循环以及错误处理机制。我们将详细讨论下列内容：l 块结构编码；l 变量声明；l 控制结构。包括程序控制，if逻辑结构以及循环结构；l 异常处理(exception)；1）、块结构(Block structure)PL/SQL程序是由独立的变量声明、执行代码和异常处理等部分代码块写成的。PL/SQL可以作为一个命名的子程序存放在数据库中，或直接在SQL*Plus窗口中作为一个匿名的块编码。当在数据库中存储PL/SQL时，子程序包括存储单元命名的头部分，程序类型的声明；以及可选的in, out 和in out参数的定义。只是可执行部分定义为begin 和end语句是固定的。Declare 和exception部分是可选的。u 基本块结构：所有的语句块都包含三个基本部分：声明部分、执行部分、和异常处理。只有执行部分是必需的，其他两个部分是可选的。例如：DECLARE /* Start of declarative section */ v_StudentID NUMBER(5) := 10000; - Numeric variable initialized - to 10,000 v_FirstName VARCHAR2(20); - Variable length character string - with maximum length of 20BEGIN /* Start of executable section */ - Retrieve first name of student with ID 10,000 SELECT first_name INTO v_FirstName FROM students WHERE id = v_StudentID;EXCEPTION /* Start of exception section */ WHEN NO_DATA_FOUND THEN - Handle the error condition INSERT INTO log_table (info) VALUES (Student 10,000 does not exist!);END;下面在一个无名块上和一个存储过程的例子。u -无名块declarebeginend;例子：DECLARE /* Declare variables to be used in this block. */ v_Num1 NUMBER := 1; v_Num2 NUMBER := 2; v_String1 VARCHAR2(50) := Hello World!; v_String2 VARCHAR2(50) := - This message brought to you by PL/SQL!; v_OutputStr VARCHAR2(50);BEGIN /* First, insert two rows into temp_table, using the values of the variables. */ INSERT INTO temp_table (num_col, char_col) VALUES (v_Num1, v_String1); INSERT INTO temp_table (num_col, char_col) VALUES (v_Num2, v_String2); /* Now query temp_table for the two rows we just inserted, and output them to the screen using the DBMS_OUTPUT package. */ SELECT char_col INTO v_OutputStrFROM temp_tableWHERE num_col = v_Num1; DBMS_OUTPUT.PUT_LINE(v_OutputStr); SELECT char_col INTO v_OutputStrFROM temp_tableWHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr); /* Rollback our changes */ ROLLBACK;END;/u -存储过程create or replace precodure_nameas-声明部分自动跟着语句而不需要编码。beginexceptionend;/例子：CREATE OR REPLACE PROCEDURE InsertIntoTemp IS /* Declare variables to be used in this block. */ v_Num1 NUMBER := 1; v_Num2 NUMBER := 2; v_String1 VARCHAR2(50) := Hello World!; v_String2 VARCHAR2(50) := - This message brought to you by PL/SQL!; v_OutputStr VARCHAR2(50);BEGIN /* First, insert two rows into temp_table, using the values of the variables. */ INSERT INTO temp_table (num_col, char_col) VALUES (v_Num1, v_String1); INSERT INTO temp_table (num_col, char_col) VALUES (v_Num2, v_String2); /* Now query temp_table for the two rows we just inserted, and output them to the screen using the DBMS_OUTPUT package. */ SELECT char_col INTO v_OutputStrFROM temp_tableWHERE num_col = v_Num1; DBMS_OUTPUT.PUT_LINE(v_OutputStr); SELECT char_col INTO v_OutputStrFROM temp_tableWHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr); /* Rollback our changes */ ROLLBACK;END InsertIntoTemp;/BEGIN InsertIntoTemp;END;/u 触发器：create or replac