oracle pl/sql

来源：互联网发布：花无涯网络黑白编辑：程序博客网时间：2024/05/01 00:20

简介

一、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 ISBEGIN   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) ISBEGIN   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) ISBEGIN  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) ISBEGIN  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) ISBEGIN  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) ISBEGIN  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) ISBEGIN  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--更新用户salUPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;--sql%notfound 这是表示没有update--raise myex;触发myexIF SQL%NOTFOUND THEN RAISE MYEX;END IF;EXCEPTIONWHEN MYEX THEN DBMS_OUTPUT.PUT_LINE('没有更新任何用户');END;/

现在再测试一次：
SQL> exec ex_test(56);
没有更新任何用户

阅读全文

1 0