第 14 章 PL/SQL 与 JDBC 程序设计

第14章PL/SQL与JDBC程序设计

本章简介 • 本章介绍Oracle PL/SQL程序设计语言。 • 通过示例，介绍在JSP中使用JDBC调用PL/SQL过程和函数的方法和步骤。

14.1 PL/SQL程序设计 14.1.1 程序结构一般地，PL/SQL块具有以下语法结构： [DECLARE declaration_statements ] BEGIN executable_statements [EXCEPTION exception_handing_statements ] END;

（1）declaration_statement —— 被包含在声明块中，声明了在块的其余部分中使用的变量。这些变量是块的局部变量，所以不能在块外引用它们。声明总是放在块的最前面。（2）executable_statement —— 块的可执行语句，包括执行循环、条件逻辑等语句。（3）exception_handing_statement —— 处理执行语句可能发生的错误。（4）每个语句都以分号（；）结尾，块使用END关键字结尾。

示例：计算一个圆的面积。 启动SQL * Plus，输入并执行下面PL/SQL程序： SQL> set serveroutput on SQL> DECLARE 2 r INTEGER:=6; 3 area NUMBER(5,2); 4 begin 5 area:=3.14*r*r; 6 DBMS_OUTPUT.PUT_LINE('圆的面积 = ' || area); 7 END; 8 / 圆的面积 = 113.04 PL/SQL 过程已成功完成。

14.1.2 变量和类型 <变量名> <数据类型> [NOT NULL][:= | DEFAULT <初始值>]; <常量名> CONSTANT <数据类型> [NOT NULL][:= | DEFAULT <初始值>]; 下面是变量声明的示例： DECLARE n_var1 NUMBER(2):=30; c_var2 CHAR(12) DEFAULT 'Hello World'; 下面是常量声明的示例： DECLARE s_score CONSTANT NUMBER(3):=500;

表14.1 常用的Oracle数据类型

14.1.3 条件逻辑 • 条件逻辑的一般语法： IF condition1 THEN statements1 ELSEIF condition2 THEN statement2 ELSE Statement3 END IF;

示例：求3个数中最大的数。 SQL> set serveroutput on; SQL> DECLARE 2 x NUMBER:=10; 3 y NUMBER:=6; 4 z NUMBER:=32; 5 BEGIN 6 IF x>y THEN 7 IF x>z THEN 8 DBMS_Output.Put_Line('最大的数为：'||To_Char(x)); 9 ELSE 10 DBMS_Output.Put_Line('最大的数为：'||To_Char(z));

11 END IF; 12 ELSE IF y>z THEN 13 DBMS_Output.Put_Line('最大的数为：'||To_Char(y)); 14 ELSE 15 DBMS_Output.Put_Line('最大的数为：'||TO_CHAR(z)); 16 END IF; 17 END IF; 18 END; 19 / 最大的数为：32 PL/SQL 过程已成功完成

14.1.4 循环 1．简单循环简单循环一直运行到显式地结束循环为止。其一般语法如下： LOOP statements END LOOP;

示例：计算1+2+3+…+100的值。 SQL> set serveroutput on; SQL> DECLARE 2 n1 NUMBER:=0; 3 n2 NUMBER:=1; 4 BEGIN 5 LOOP 6 n1:=n1+n2; 7 n2:=n2+1;

8 IF n2>100 THEN 9 EXIT; 10 END IF; 11 END LOOP; 12 DBMS_Output.Put_Line(n1); 13 END; 14 / 5050 PL/SQL 过程已成功完成。

2．WHILE循环 WHILE循环一直运行到出现指定的条件为止。WHILE循环的一般语法如下： WHILE condition LOOP statements END LOOP;

示例：计算1+2+3+…+100的值。 SQL> set serveroutput on; SQL> DECLARE 2 n1 NUMBER:=0; 3 n2 NUMBER:=1; 4 BEGIN 5 WHILE N2<=100 LOOP 6 n1:=n1+n2; 7 n2:=n2+1; 8 END LOOP; 9 DBMS_Output.Put_Line(n1); 10 END; 11 / 5050 PL/SQL 过程已成功完成。

3．FOR循环 FOR循环的一般语法如下： FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP statements END LOOP;

示例：求1 ～ 6！。 SQL> set serveroutput on; SQL> DECLARE 2 v_factorial NUMBER:=1; 3 BEGIN 4 FOR v_loopcounter IN 1..6 LOOP 5 v_factorial:=v_factorial*v_loopcounter; 6 DBMS_Output.Put_Line(v_loopcounter || ' factorial value is :' || v_factorial) 7 END LOOP; 8 END; 9 / 1 factorial value is :1 2 factorial value is :2 3 factorial value is :6 4 factorial value is :24 5 factorial value is :120 6 factorial value is :720 PL/SQL 过程已成功完成。

14.2 从JSP调用PL/SQL过程 • 本节首先介绍PL/SQL过程，然后通过一个示例，说明在JSP页面中使用JDBC调用PL/SQL过程的方法和步骤。

14.2.1 创建PL/SQL过程 • 过程是根据逻辑分组的SQL和PL/SQL的语句集，用于执行特定的任务。 • 存储过程是存储在数据库中的编译后的过程，一旦存储，这个过程就称为一个模式对象（既特定的数据库对象）。

过程的主要优点是： （1）过程是模块化的，能够将程序分割成定义清晰、易于管理的单元。（2）由于过程是被储存在数据库中，所以可以重用。而且过程经过验证后，可以反复使用，无需重新编译并通过网络分发。（3）通过让用户只能通过存储过程访问数据库，可以限制对数据库的访问。（4）过程利用了共享内存资源。

PL/SQL过程的一般语法如下 CREATE TABLE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN|OUT|IN OUT] type [, …])] {IS | AS} { body };

示例： • 创建一个表products，其创建命令如下： CREATE TABLE products ( id INTEGER PRIMARY KEY, name VARCHAR2(30) NOT NULL, description VARCHAR2(50), price NUMBER(5, 2) );

PL/SQL过程update_price( )的定义如下所示： CREATE PROCEDURE update_price( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) AS product_count INTEGER; BEGIN UPDATE products SET price = price * p_factor WHERE id = p_product_id; COMMIT; END update_price; /

14.2.2 使用JDBC调用PL/SQL过程 （1）创建一个CallableStatement对象。 • 第一步，需要创建一个CallableStatement对象，这其中包含了对PL/SQL过程的调用。例如，下面的语句： CallableStatement myCS = myConnection.prepareCall("{ call update_price(?,?) }");

（2）如果需要，为PL/SQL过程提供参数 • 第二步，使用set方法为CallableStatement对象的占位符提供一个值。 • 例如，下面所示的第一个语句为第一个占位符产品id提供一个值1，第二个语句为第二个占位符产品价格的乘数因子提供一个值1.2。 myCS.setInt(1,1); myCS.setDouble(2,1.2);

（3）执行execute( )方法 • 第三步，执行CallableStatement对象的execute( )方法，即执行PL/SQL过程。 • 例如，下面的语句： myCS.execute( ); • 在使用完CallableStatement对象后，应该用close( )方法关闭它。 • 例如，下面的语句： myCS.close( );

14.3 从JSP调用PL/SQL函数 本节首先介绍PL/SQL函数，然后通过一个示例，说明在JSP页面中使用JDBC调用PL/SQL函数的方法和步骤。

14.3.1 创建PL/SQL函数 PL/SQL函数的一般语法如下 CREATE TABLE [OR REPLACE] FUNCTION function_name [(parameter_name [IN|OUT|IN OUT] type [, …])] RETURN type {IS | AS} { body };

示例：PL/SQL函数的定义如下所示： CREATE FUNCTION update_price_func( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) RETURN INTEGER AS BEGIN UPDATE products SET price = price * p_factor WHERE id = p_product_id; COMMIT; RETURN 1; END update_price_func; /

14.3.2 使用JDBC调用PL/SQL函数 （1）创建一个CallableStatement对象。 • 第一步，需要创建一个CallableStatement对象，这其中包含了对PL/SQL函数的调用。 • 例如，下面的语句： CallableStatement myCS=myConnection.prepareCall("{?=call update_price_func(?,?)}"); （2）注册输出参数。 • 第二步，使用registerOutParameter( )方法注册PL/SQL函数的输出参数。 • 例如，下面的语句注册PL/SQL函数返回的整型参数： myCS.registerOutParameter(1,java.sql.Types.INTEGER);

（3）如果需要，为PL/SQL函数提供参数。 • 第三步，使用set方法为CallableStatement对象的占位符提供一个值。 • 例如，第一个语句为第二个占位符产品id提供一个值3，第二个语句为第三个占位符产品价格的乘数因子提供一个值1.2。 myCS.setInt(2,3); myCS.setDouble(3,1.2);

（4）执行execute( )方法。 • 第四步，执行CallableStatement对象的execute( )方法，也就是执行PL/SQL函数。例如，下面的语句： myCS.execute( ); （5）如果需要，读取函数的返回值 • 第五步，使用get方法读取PL/SQL函数的返回值。例如，下面的语句： int result = myCS.getInt(1); • 在使用完CallableStatement对象后，应该用close( )方法关闭它。例如，下面的语句： myCS.close( );

14.4 本章小结 • 通过使用JDBC调用PL/SQL过程和函数，根本性地改变了把数据库仅仅视作数据存储空间的观念。 • 实际上现在的数据库已经成为构成商务应用程序层的一个重要组成部分。而且由于PL/SQL在数据类型上的优势，使得开发人员需要处理更复杂的数据类型时有了一个更好地选择。

第 14 章 PL/SQL 与 JDBC 程序设计