简介
一、pl/sql 是什么
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。
pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。
二、为什么要学pl/sql
1.提高应用程序的运行性能
2.模块化的设计思想(分页的过程，订单的过程，转账的过程。。)
3.减少网络传输量
4.提高安全性(sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会)
三、Oracle为什么在PL/SQL developer执行很快,用c# oracleclient执行就慢
因为PL/SQL这门语言是专门用于在各种环境下对Oracle数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
而c#语言是微软的产品，它在连接ORACLE的时候先存到“连接池”中，所以第一次会慢点，但是当你的Web程序没有重起的时候，以后的速度就不会慢了。
四、使用pl/sql的缺点
移植性不好(换数据库就用不了)
五、pl/sql理解
1)、存储过程、函数、触发器是pl/sql编写的
2)、存储过程、函数、触发器是存在oracle中的
3)、pl/sql是非常强大的数据库过程语言
4)、存储过程、函数可以在java中调用
六、编写一个存储过程，该过程可以向某表中添加记录。
1、创建一张简单的表 CREATE TABLE mytest( username VARCHAR2(30), pwd VARCHAR2(30) ); 2、创建过程(replace:表示如果有insert_proc，就替换) CREATE OR REPLACE PROCEDURE insert_proc IS BEGIN INSERT INTO mytest VALUES('林计钦', '123456'); END; / 3、如何查看错误信息：show error; 注意要在命令窗口执行 4、如何调用该过程：exec 过程名(参数值1，参数值2...); eg、exec insert_proc; 注意要在命令窗口执行
基础
一、pl/sql developer开发工具
pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。
二、pl/sql介绍
开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。
三、pl/sql可以做什么
可以用来编写存储过程、函数、触发器、包等
四、编写规范
五、pl/sql块介绍
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。
六、块结构示意图
pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。
如下所示：
declare
/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分——要执行的pl/sql 语句和sql 语句*/
exception
/*例外处理部分——处理运行的各种错误*/
end;
说明：
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
例外处理部分是从exception开始的，该部分是可选的。
可以和java编程结构做一个简单的比较。
七、pl/sql块的实例一
实例一 只包括执行部分的pl/sql块
set serveroutput on; --打开输出选项 begin dbms_output.put_line('hello world'); end; / --执行
相关说明：
dbms_output是oracle所提供的包(类似java 的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。
八、pl/sql块的实例二
实例二 包含定义部分和执行部分的pl/sql块
set serveroutput on; --打开输出选项 DECLARE --定义字符串变量 v_ename varchar2(10); BEGIN --执行部分 select ename into v_ename from emp where empno=&empno; --& 表示要接收从控制台输入的变量
--在控制台显示雇员名 dbms_output.put_line('雇员名：'||v_ename); end; /
九、pl/sql块的实例三
实例三 包含定义部分，执行部分和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。
1.比如在实例二中，如果输入了不存在的雇员号，应当做例外处理。
2.有时出现异常，希望用另外的逻辑处理，我们看看如何完成1的要求。
相关说明：oracle事先预定义了一些例外，no_data_found就是找不到数据的例外
--打开输出选项 set serveroutput on; DECLARE --定义字符串变量 v_ename varchar2(10); v_sal NUMBER(7,2); BEGIN --执行部分 select ename, sal into v_ename, v_sal from emp where empno=&empno; dbms_output.put_line('雇员名：'||v_ename||'，薪水：'||v_sal); EXCEPTION --异常处理 WHEN no_data_found THEN dbms_output.put_line('朋友，您的编号输入有误！'); end; /
存储过程
存储过程用于执行特定的操作，当建立存储过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下：
1.请考虑编写一个存储过程，可以输入雇员名，新工资，用来修改雇员的工资
--根据雇员名去修改工资 CREATE PROCEDURE sp_update(uname VARCHAR2, newsal NUMBER) IS BEGIN update emp set sal=newsal where ename=uname; END; /
2.如何调用存储过程有两种方法：exec、call
--使用exec调用存储过程 SQL> exec sp_update('zhangsan', 888); SQL> commit;
3.如何在java程序中调用一个存储过程
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; /** * 演示java程序调用oracle的存储过程案例 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call sp_update(?,?)}"); // 4.给?赋值 cs.setString(1, "SMITH"); cs.setInt(2, 4444); // 5.执行 cs.execute(); // 关闭 cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
问题：如何使用过程返回值？
特别说明：对于存储过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。
函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数。
1）、接下来通过一个案例来模拟函数的用法
--输入雇员的姓名，返回该雇员的年薪 CREATE FUNCTION annual_incomec(uname VARCHAR2) RETURN NUMBER IS annual_salazy NUMBER(7,2); BEGIN SELECT a.sal*13 INTO annual_salazy FROM emp a WHERE a.ename=uname; RETURN annual_salazy; END; /
2）、在sqlplus中调用函数
SQL> var income NUMBER; SQL> call annual_incomec('SCOTT') into:income; SQL> print income;
3）、在java程序中调用oracle函数：select annual_incomec('SCOTT') income from dual;
package junit.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 演示java程序调用oracle的函数案例 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建PreparedStatement PreparedStatement ps = ct.prepareStatement("select annual_incomec('SCOTT') annual from dual"); // 4.执行 ResultSet rs=ps.executeQuery(); if(rs.next()){ Float annual=rs.getFloat("annual"); System.out.println(annual); } //5、关闭 rs.close(); ps.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1)、我们可以使用create package命令来创建包，如：
i、创建一个包sp_package
ii、声明该包有一个过程update_sal
iii、声明该包有一个函数annual_income
--声明该包有一个存储过程和一个函数 create package sp_package is procedure update_sal(name varchar2, newsal number); function annual_income(name varchar2) return number; end;
2)、建立包体可以使用create package body命令
给包sp_package实现包体
CREATE OR REPLACE PACKAGE BODY SP_PACKAGE IS --存储过程 PROCEDURE UPDATE_SAL(NAME VARCHAR2, NEWSAL NUMBER) IS BEGIN UPDATE EMP SET SAL = NEWSAL WHERE ENAME = NAME; COMMIT; END; --函数 FUNCTION ANNUAL_INCOME(NAME VARCHAR2) RETURN NUMBER IS ANNUAL_SALARY NUMBER; BEGIN SELECT SAL * 12 + NVL(COMM, 0) INTO ANNUAL_SALARY FROM EMP WHERE ENAME = NAME; RETURN ANNUAL_SALARY; END; END; /
3)、如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。如：
--调用存储过程 SQL> exec sp_package.update_sal('SCOTT', 8888); --调用函数 var income NUMBER; CALL sp_package.ANNUAL_INCOME('SCOTT') INTO:income; print income;
特别说明：包是pl/sql 中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力呵呵。
触发器
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件insert,update,delete 语句，而触发操作实际就是一个pl/sql 块。可以使用create trigger 来建立触发器。
特别说明：我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。
变量
一、变量介绍
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1)、标量类型(scalar)
2)、复合类型(composite) --用于操作单条记录
3)、参照类型(reference) --用于操作多条记录
4)、lob(large object)
二、标量(scalar)——常用类型
1)、在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。
2)、标量定义的案例
1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围-9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4，:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;
三、标量(scalar)——使用标量
在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=)。
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。
set serveroutput on; --打开输出选项 DECLARE --税率为0.03 C_TAX_RATE NUMBER(3, 2) :=0.03; --雇员姓名 V_ENAME VARCHAR2(5); --工资 V_SAL NUMBER(7, 2); --个人所得税 V_TAX_SAL NUMBER(7, 2); BEGIN --执行 SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7369 --计算所得税 V_TAX_SAL := V_SAL * C_TAX_RATE; --输出 DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL); END; /
四、标量(scalar)——使用%type类型
对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有“ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
我们看看这个怎么使用：标识符名 表名.列名%type;
比如上例的v_ename，这样定义： v_ename emp.ename%type;
set serveroutput on; --打开输出选项 DECLARE --税率为0.03 C_TAX_RATE NUMBER(3, 2) :=0.03; --雇员姓名 V_ENAME emp.ename%TYPE;--推荐使用%type类型 --工资 V_SAL NUMBER(7, 2); --个人所得税 V_TAX_SAL NUMBER(7, 2); BEGIN --执行 SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7777 --计算所得税 V_TAX_SAL := V_SAL * C_TAX_RATE; --输出 DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL); END; /
五、复合变量(composite)——介绍
用于存放多个值的变量。主要包括这几种：
1)、pl/sql记录
2)、pl/sql表
3)、嵌套表
4)、varray
六、复合类型——pl/sql记录
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：
set serveroutput on; --打开输出选项 DECLARE --定义一个pl/sql记录类型emp_record_type， --类型包含3个数据NAME, SALARY, TITLE。说白了，就是一个类型可以存放3个数据，主要是为了方便管理 TYPE EMP_RECORD_TYPE IS RECORD( NAME EMP.ENAME%TYPE, SALARY EMP.SAL%TYPE, TITLE EMP.JOB%TYPE); --定义了一个sp_record变量，这个变量的类型是emp_record_type SP_RECORD EMP_RECORD_TYPE; BEGIN SELECT ENAME, SAL, JOB INTO SP_RECORD FROM EMP WHERE EMPNO = 7788; DBMS_OUTPUT.PUT_LINE('员工名:' || SP_RECORD.NAME || '工资：' || SP_RECORD.SALARY); END; /
七、复合类型——pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：
方法一(推荐)： set serveroutput on; --打开输出选项 DECLARE --定义了一个pl/sql表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE --INDEX BY VARCHAR2(20)表示下标是字符串 TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(20); --定义了一个sp_table变量，这个变量的类型是sp_table_type SP_TABLE SP_TABLE_TYPE; BEGIN SELECT ENAME, sal INTO SP_TABLE('ename'), SP_TABLE('sal') FROM EMP WHERE EMPNO = 7788; DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE('ename')||'工资：'||SP_TABLE('sal')); END; / 方法二： set serveroutput on; --打开输出选项 DECLARE --定义了一个pl/sql 表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE --index by binary_integer表示下标是整数 TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; --注意binary_integer如果换为integer就会报错，知道的朋友欢迎告诉我下 --定义了一个sp_table变量，这个变量的类型是sp_table_type SP_TABLE SP_TABLE_TYPE; BEGIN SELECT ENAME,sal INTO SP_TABLE(-1),SP_TABLE(-2) FROM EMP WHERE EMPNO = 7788; DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE(-1)||'工资：'||SP_TABLE(-2)); END; /
说明：
sp_table_type是pl/sql表类型
emp.ename%type 指定了表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788；变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下：ORA-01422:实际返回的行数超出请求的行数
解决方法是：使用参照变量(这里不讲)
八、复合变量——嵌套表(nested table)
复合变量——变长数组(varray)
省略
九、参照变量——介绍
参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。推荐使用游标变量。
十、参照变量——ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open 时）需要指定select语句，这样一个游标与一个select语句结合了。实例如下：
1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
2.在1的基础上，如果某个员工的工资低于200元，就添加100元。
SET serveroutput ON; DECLARE --定义游标 TYPE sp_emp_cursor IS REF CURSOR; --定义一个游标变量 sp sp_emp_cursor; --定义变量 v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; BEGIN OPEN sp FOR SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10; --方法一 loop循环 /* LOOP FETCH sp INTO v_ename, v_sal; EXIT WHEN sp%NOTFOUND; DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL); END LOOP;*/ --方法二 while循环 /* WHILE 1=1 LOOP FETCH sp INTO v_ename, v_sal; EXIT WHEN sp%NOTFOUND; DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL); END LOOP;*/ --方法三 for循环 FOR cur IN (SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10) LOOP DBMS_OUTPUT.PUT_LINE('名字：' || cur.ename || ' 工资：' || cur.sal); END LOOP; END; /
控制结构
一、pl/sql的进阶--控制结构
在任何计算机语言(c，java，pascal)都有各种控制语句(条件语句，循环结构，顺序控制结构...)，在pl/sql中也存在这样的控制结构。
在本部分学习完成后，希望大家达到：
1.使用各种if语句
2.使用循环语句
3.使用控制语句——goto和null(goto语句不推荐使用)；
二、条件分支语句
pl/sql中提供了三种条件分支语句if—then，if–then–else，if–then–else if–then。
这里我们可以和java语句进行一个比较。
1)、简单的条件判断if–then
问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。
SET serveroutput ON; CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS --定义 V_SAL EMP.SAL%TYPE; BEGIN --执行 SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = SPNAME; --判断 IF V_SAL < 2000 THEN UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE ENAME = SPNAME; COMMIT; END IF; END; / --调用存储过程 exec SP_PRO6('ALLEN');
2)、二重条件分支 if–then–else
问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200；
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS --定义 V_COMM EMP.COMM%TYPE; BEGIN --执行 SELECT COMM INTO V_COMM FROM EMP WHERE ENAME = SPNAME; --判断 IF V_COMM <> 0 THEN UPDATE EMP SET COMM = COMM + 100 WHERE ENAME = SPNAME; ELSE UPDATE EMP SET COMM = COMM + 200 WHERE ENAME = SPNAME; END IF; COMMIT; END; / --调用存储过程 exec SP_PRO6('ALLEN');
3)、多重条件分支 if–then–ELSIF–then
问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER 就给他的工资增加500，其它职位的雇员工资增加200。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS --定义 V_JOB EMP.JOB%TYPE; BEGIN --执行 SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO = SPNO; IF V_JOB = 'PRESIDENT' THEN UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO; ELSIF V_JOB = 'MANAGER' THEN UPDATE EMP SET SAL = SAL + 500 WHERE EMPNO = SPNO; ELSE UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO; END IF; COMMIT; END; / --调用存储过程 exec SP_PRO6(7499);
三、循环语句–loop
是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
CREATE TABLE USERS( vid NUMBER(5), uname VARCHAR2(30) );
请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS --定义 :=表示赋值 V_NUM NUMBER := 1; BEGIN LOOP INSERT INTO USERS VALUES (V_NUM, SPNAME); --判断是否要退出循环 EXIT WHEN V_NUM = 10; --自增 V_NUM := V_NUM + 1; END LOOP; COMMIT; END; / --调用存储过程 EXEC SP_PRO6('ALLEN');
四、循环语句–while循环
基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop 结束。
案例：现有一张表users，表结构如下：
用户vid | 用户名 uname
问题：请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS --定义 :=表示赋值 V_NUM NUMBER := 11; BEGIN WHILE V_NUM <= 20 LOOP --执行 INSERT INTO USERS VALUES (V_NUM, SPNAME); V_NUM := V_NUM + 1; END LOOP; COMMIT; END; / --调用存储过程 EXEC SP_PRO6('ALLEN');
五、循环语句–for循环
基本for循环的基本结构如下
CREATE OR REPLACE PROCEDURE SP_PRO6 IS--注意如果无参记得不要加() BEGIN FOR I IN REVERSE 1 .. 10 LOOP --REVERSE反转函数，表示I从10到1递减，去掉REVERSE表示I从1到10递增 INSERT INTO USERS VALUES (I, 'shunping'); END LOOP; END; / --调用存储过程 EXEC SP_PRO6;
我们可以看到控制变量i，在隐含中就在不停地增加。
六、顺序控制语句–goto、null
1)、goto语句
goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。
基本语法如下goto lable，其中lable是已经定义好的标号名
set serveroutput on; DECLARE I INT := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('输出i=' || I); IF I = 1 THEN GOTO END_LOOP; END IF; I := I + 1; END LOOP; <<END_LOOP>> DBMS_OUTPUT.PUT_LINE('循环结束'); END; /
2)、null语句
null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。
SET serveroutput ON; DECLARE V_SAL EMP.SAL%TYPE; V_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = &NO; IF V_SAL < 3000 THEN UPDATE EMP SET COMM = SAL * 0.1 WHERE ENAME = V_ENAME; dbms_output.put_line('1111'); ELSE NULL; dbms_output.put_line('2222');--不会被执行 END IF; END; /
分页
一、无返回值的存储过程
古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程。 案例：现有一张表book，表结构如下：书号、书名、出版社。
CREATE TABLE book( ID NUMBER(4), book_name VARCHAR2(30), publishing VARCHAR2(30) );
请写一个过程，可以向book表添加书，要求通过java程序调用该过程。
--注意：in->表示这是一个输入参数，默认为in --out->表示一个输出参数 CREATE OR REPLACE PROCEDURE ADD_BOOK(ID IN NUMBER, NAME IN VARCHAR2, PUBLISHING IN VARCHAR2) IS BEGIN INSERT INTO BOOK VALUES (ID, NAME, PUBLISHING); COMMIT; END; /
java程序调用该存储过程的代码
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; /** * 调用一个无返回值的存储过程 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("call ADD_BOOK(?,?,?)"); //给?赋值 cs.setInt(1, 1); cs.setString(2, "java"); cs.setString(3, "java出版社"); // 4.执行 cs.execute(); //5、关闭 cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
二、有返回值的存储过程(非列表)
案例：编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名。
--输入和输出的存储过程 CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, SPNAME OUT VARCHAR2) IS BEGIN SELECT ENAME INTO SPNAME FROM EMP WHERE EMPNO = SPNO; END; /
java程序调用该存储过程的代码
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; /** * 调用一个无返回值的存储过程 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}"); //给第一个?赋值 cs.setInt(1,7788); //给第二个?赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //4、执行 cs.execute(); //取出返回值,要注意？的顺序 String name=cs.getString(2); System.out.println("编号7788的名字："+name); //5、关闭 cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。
--输入和输出的存储过程 CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, SPNAME OUT VARCHAR2, SPSAL OUT NUMBER, SPJOB OUT VARCHAR2) IS BEGIN SELECT ENAME, SAL, JOB INTO SPNAME, SPSAL, SPJOB FROM EMP WHERE EMPNO = SPNO; END; /
java程序调用该存储过程的代码
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; /** * 调用一个无返回值的存储过程 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call sp_proc(?,?,?,?)}"); //给第一个?赋值 cs.setInt(1,7788); //给第二个?赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //给第三个？赋值 cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE); //给第四个？赋值 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR); //4、执行 cs.execute(); //取出返回值,要注意？的顺序 String name=cs.getString(2); double sal=cs.getDouble(3); String job=cs.getString(4); System.out.println("编号7788的名字："+name+"，职位："+job+"，薪水："+sal+""); //5、关闭 cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
三、有返回值的存储过程(列表[结果集])
案例：编写一个存储过程，输入部门号，返回该部门所有雇员信息。
该题分析如下：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：
1)、建立一个包，在该包中我们定义类型test_cursor，它是个游标。
CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR; END TESTPACKAGE; /
2)、建立存储过程。
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER, P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS BEGIN OPEN P_CURSOR FOR SELECT * FROM EMP WHERE DEPTNO = SPNO; END SP_PROC; /
3)、如何在java 程序中调用该过程
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; /** * 调用一个无返回值的存储过程 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call sp_proc(?,?)}"); //给第一个?赋值 cs.setInt(1,10); //给第二个?赋值 cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); //4、执行 cs.execute(); //得到结果集 ResultSet rs = (ResultSet) cs.getObject(2); while (rs.next()) { System.out.println(rs.getInt(1) + " " + rs.getString(2)); } //5、关闭 rs.close(); cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
四、编写分页过程
有了上面的基础，相信大家可以完成分页存储过程了。
要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。
--ROWNUM用法 SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10; ----oracle分页sql语句；在分页时，大家可以把下面的sql语句当做一个模板使用 SELECT * FROM (SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10) WHERE RN >= 6;
1)、开发一个包
建立一个包，在该包中定义类型为test_cursor的游标。
--建立一个包 CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR; END TESTPACKAGE; / --开始编写分页的过程 CREATE OR REPLACE PROCEDURE FENYE(TABLENAME IN VARCHAR2, PAGESIZE IN NUMBER, --每页显示记录数 PAGENOW IN NUMBER, --页数 MYROWS OUT NUMBER, --总记录数 MYPAGECOUNT OUT NUMBER, --总页数 P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS --返回的记录集 --定义部分 --定义sql语句字符串 V_SQL VARCHAR2(1000); --定义两个整数 V_BEGIN NUMBER := (PAGENOW - 1) * PAGESIZE + 1; V_END NUMBER := PAGENOW * PAGESIZE; BEGIN --执行部分 V_SQL := 'select * from (select t1.*, rownum rn from (select * from ' || TABLENAME || ') t1 where rownum<=' || V_END || ') where rn>=' || V_BEGIN; --把游标和sql关联 OPEN P_CURSOR FOR V_SQL; --计算myrows和myPageCount --组织一个sql语句 V_SQL := 'select count(*) from ' || TABLENAME; --执行sql,并把返回的值，赋给myrows； EXECUTE ImMEDIATE V_SQL INTO MYROWS; --它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前， --EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码. --尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。 --计算myPageCount --if myrows%Pagesize=0 then 这样写是错的 IF MOD(MYROWS, PAGESIZE) = 0 THEN MYPAGECOUNT := MYROWS/PAGESIZE; ELSE MYPAGECOUNT := MYROWS/PAGESIZE + 1; END IF; --关闭游标 --CLOSE P_CURSOR; --不要关闭，否则java调用该存储过程会报错 END; /
java调用分页代码
package junit.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; /** * 调用一个无返回值的存储过程 * * @author jiqinlin * */ public class ProcedureTest { public static void main(String[] args) { try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct = DriverManager.getConnection( "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle"); // 3.创建CallableStatement CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}"); cs.setString(1, "emp"); //表名 cs.setInt(2, 5); //每页显示记录数 cs.setInt(3, 1);//页数 // 注册总记录数 cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); //总记录数 // 注册总页数 cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); //总页数 // 注册返回的结果集 cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //返回的记录集 // 4、执行 cs.execute(); // 得到结果集 // 取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的 int rowNum = cs.getInt(4); int pageCount = cs.getInt(5); ResultSet rs = (ResultSet) cs.getObject(6); // 显示一下，看看对不对 System.out.println("rowNum=" + rowNum); System.out.println("总页数=" + pageCount); while (rs.next()) { System.out.println("编号：" + rs.getInt(1) + " 名字：" + rs.getString(2) + " 工资：" + rs.getFloat(6)); } // 5、关闭 //rs.close(); cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } } }
例外
一、例外分类
oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
1)、预定义例外用于处理常见的oracle错误。
2)、非预定义例外用于处理预定义例外不能处理的例外。
3)、自定义例外用于处理与oracle错误无关的其它情况。
下面通过一个小案例演示如果不处理例外看会出现什么情况？
编写一个存储过程，可接收雇员的编号，并显示该雇员的姓名。
sql代码如下：
SET SERVEROUTPUT ON; DECLARE V_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO; DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME); END; /
随便输入不存在的编号，回车，会抛出如下异常：
ORA-01403: 未找到数据
ORA-06512: 在line 6
例外捕获的sql代码如下：
SET SERVEROUTPUT ON; DECLARE V_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = &GNO; DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('编号未找到！'); END; /
随便输入不存在的编号，回车，会友情提示：编号未找到！
二、处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。
1)、case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外：
SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS V_SAL EMP.SAL%TYPE; BEGIN SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO = SPNO; CASE WHEN V_SAL < 1000 THEN UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO = SPNO; WHEN V_SAL < 2000 THEN UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO; END CASE; EXCEPTION WHEN CASE_NOT_FOUND THEN DBMS_OUTPUT.PUT_LINE('case语句没有与' || V_SAL || '相匹配的条件'); END; / --调用存储过程 SQL> EXEC SP_PRO6(7369); case语句没有与4444相匹配的条件
2)、cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外
DECLARE CURSOR EMP_CURSOR IS SELECT ENAME, SAL FROM EMP; BEGIN OPEN EMP_CURSOR; --声明时游标已打开，所以没必要再次打开 FOR EMP_RECORD1 IN EMP_CURSOR LOOP DBMS_OUTPUT.PUT_LINE(EMP_RECORD1.ENAME); END LOOP; EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN DBMS_OUTPUT.PUT_LINE('游标已经打开'); END; /
3)、dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
BEGIN INSERT INTO DEPT VALUES (10, '公关部', '北京'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('在deptno列上不能出现重复值'); END; /
4)、invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
DECLARE CURSOR EMP_CURSOR IS SELECT ENAME, SAL FROM EMP; EMP_RECORD EMP_CURSOR%ROWTYPE; BEGIN --open emp_cursor; --打开游标 FETCH EMP_CURSOR INTO EMP_RECORD; DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME); CLOSE EMP_CURSOR; EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE('请检测游标是否打开'); END; /
5)、invalid_number预定义例外
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外
SET SERVEROUTPUT ON; BEGIN UPDATE EMP SET SAL = SAL + 'AAA'; EXCEPTION WHEN INVALID_NUMBER THEN DBMS_OUTPUT.PUT_LINE('输入的数字不正确'); END; /
6)、no_data_found预定义例外
下面是一个pl/sql 块，当执行select into没有返回行，就会触发该例外
SET serveroutput ON; DECLARE V_SAL EMP.SAL%TYPE; BEGIN SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = 'ljq'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('不存在该员工'); END; /
7)、too_many_rows预定义例外
当执行select into语句时，如果返回超过了一行，则会触发该例外。
DECLARE V_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO V_ENAME FROM EMP; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('返回了多行'); END; /
8)、zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9)、value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)
1、login_denied
当用户非法登录时，会触发该例外
2、not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
3、storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
4、timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
三、非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle 错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。
四、处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle 错误会隐含的触发相应的例外；而自定义例外与oracle 错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql 块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS BEGIN UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO; END; / --调用存储过程， EXEC EX_TEST(56);
这里，编号为56 是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？ 修改代码，如下：
--自定义例外 CREATE OR REPLACE PROCEDURE EX_TEST(SPNO NUMBER) IS --定义一个例外 MYEX EXCEPTION; BEGIN --更新用户sal UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO; --sql%notfound 这是表示没有update --raise myex;触发myex IF SQL%NOTFOUND THEN RAISE MYEX; END IF; EXCEPTION WHEN MYEX THEN DBMS_OUTPUT.PUT_LINE('没有更新任何用户'); END; /
现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户
扫一扫
562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
