【期望目标】 ①理解oracle的pl/sql概念 ②掌握pl/sql编程技术(包括编写存储过程、函数，触发器，包...)
【pl/sql是什么】 pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许 嵌入sql语言，还可以定义[变量]和[常量]，允许使用[条件语句]和[循环语句]，允许[使用 例外处理各种错误]，这样使得他的功能变得更加强大。 ①过程、函数、触发器是pl/sql编写的 ②过程、函数、触发器实在oracle中的【减少对项目的成本：对复杂的一块业务，由专人负责】 【分页的过程：表名，第几页，每页几条记录】 【订单的过程】 【转账的过程】 ③pl/sql是非常强大的数据库过程语言 ④过程，函数可以在java程序中调用
【pl/sql学习的必要性】 ①提高应用程序的运行性能【一个程序员的成长是需要过程的，把程序做的有灵魂】 传统的操作数据库的方法是：java程序运行的机器不一定和数据库在一起，即使在一起 他也有这样一个过程：得到一个数据库的连接(不管是用jdbc还是连接池的方式)，首先 要传递给数据库sql语句，数据库拿到sql语句之后首先要进行编译(以形成数据库能 识别的代码)，然后才会执行并查询客户端所请求的数据。编译究竟花多少时间？一次 编译花的时间并不多，但是每次sql语句来了之后都要编译的话，那花费的代价就很大 了...为什么用pl/sql编写过程、函数、触发器以后他的速度就会提高呢？客户端发过来 的sql语句就能尽可能地少!对于一个过程来说，他已经提前被编译好了。客户端的java 程序调用服务器端的过程时(过程已经被编译为能够直接运行的代码，因此就少了编译 的过程)，只需要写上过程名再传递一些必要的参数就完事儿了。而且，我们可以轻一些 高人，技术强的人来专门写这个过程，比如订单处理模块的存储过程，分页的，转账的 存储过程，专门维护他就行了。为什么oracle程序员一定要求会pl/sql的道理，他对写 过程、写函数的要求是很高的，而且确实能够提高整系统的性能。如果系统跑得慢，可以 有以下几种方法解决：1.如果是传统方法的话，那就优化sql语句；2.如果优化sql语句性能 提升有限的话，还可以把sql语句做成模块写成一个个的存储过程，然后调用存储过程就 行了。 ②模块化的设计思想 ③减少网络传输量
【pl/sql不好的地方】 移植性不好。如果换了数据库的话，存储过程就要全部重写! 如果是用hibernate的hql语句的话，数据库就可以算便换~ 一般在哪种情况下才换数据库？项目用这个数据库实在是不能跑了才会换，客户不会关心 后台用什么数据库的！
【用什么编写 pl/sql】 ① sqlplus 开发工具 sqlplus 是 oracle 公司提供的一个工具，这个因为我们在以前介绍过的： 举一个简单案例：编写一个存储过程，该过程可以向某表中添加记录。
1.创建一张简单的表 create table mytest(name varchar2(30),password varchar2(30));
2.创建存储过程【bruce_pro1 是过程名，不能有两个过程同名】 -------------------------------------------------------- create [or replace] procedure bruce_pro1 is begin --执行部分 insert into mytest('杨长伟','123abc!'); end; -------------------------------------------------------- 【回车过后你会看到它仍然没有执行，加一个/, /就代表让oracle去创建这个存储过程，之后在回车】 【[or replace]：如果有的话，bruce_pro1 就替换，没有就新建~】 【如报错“名称已由现有对象使用”：改名即可】 【报错：“创建的过程带有编译错误”，修改存储过程为如下】 -------------------------------------------------------- create or replace procedure bruce_pro1 is begin --执行部分 insert into mytest values('杨长伟','123abc!'); end; -------------------------------------------------------- 【如何查看错误信息：show error; 即可】 【如返回“过程已创建”，则表示创建成功】 -------------------------------------------------------- SQL> create or replace procedure bruce_pro1 is 2 begin 3 --执行部分 4 insert into mytest values('杨长伟','123abc!'); 5 end; 6 / Procedure created --------------------------------------------------------
【如何调用该过程？】 1.exec 过程名(参数值1, 参数值2...); 2.call 过程名(参数值1, 参数值2...);
=========================================================
②pl/sql developer开发工具 【SQL Server 2000也可以进行pl/sql编程，不过关于这个业界没有标准】 pl/sql developer是用于开发pl/sql块地集成开发环境(ide)，他是一个独立的产品，而不是 oracle的一个附带品。 举一个简单案例：编写一个存储过程，该过程可以删除某表记录。 -------------------------------------------------------- create or replace procedure bruce_pro2 is begin --执行部分 delete from mytest where name='杨长伟'; end; --------------------------------------------------------
【pl/sql基础介绍】 开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握 pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非 常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过 程模块..而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询要求。
【简单分类(基本上单位的要求也就这些，足够了)】 |-----过程(存储过程) | |-----函数 块(编程)--------| |-----触发器 | |-----包
【编写规范】 ①注释 单行注释 -- select * from emp where empno=7788;--取得员工信息 多行注释 /*...*/ 来划分
②表示符号的命名规范 1.当定义变量时，建议用 v_ 作为前缀 v_sal 2.当定义常量时，建议用 c_ 作为前缀 c_rate 3.当定义游标时，建议用 _cursor 作为后缀 emp_cursor; 4.当定义例外时，建议用 e_ 作为前缀 e_error
【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编程做一个简单的比较】
【关于抛出异常】 java程序结果 public static void main(String[] args) { int a = 1; try { a++; // System.out 是java中的包 System.out.println("fasdfa"); } catch(Exception e) { // 得到异常 } }
【pl/sql块的实例】 【实例1-只包括执行部分的pl/sql块】
set serveroutput on -- 打开输出选项 begin dbms_output.put_line('hello'); end;
相关说明：dbms_output是oracle所提供的包(类似java的开发包)，该包包含 一些过程，put_line就是dbms_output包的一个过程。
【实例2-包含定义部分和执行部分的pl/sql块】
declare v_ename varchar2(5); -- 定义字符串变量 begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('雇员名:'||v_ename); end; /
相关说明： ①& 表示要接收从控制台输入的变量， ②|| 表示 pl/sql 语句的连接符(有点儿类似于java中的字符串拼接) ③&no 是pl/sql developer 所特有的，作用是弹出一个对话框让输入参数 ④pl/sql编程中字符串是用单引号括起来的，用双引号的话则会报错。 ⑤& 表示要接收从控制台输入的变量
【实例3-在实例2的基础上把雇员的薪水也显示出来】 最直接的想法： declare v_ename varchar2(5); v_sal number(7,2); begin select ename into v_ename from emp where empno=&aa; select sal into v_sal from emp where empno=&aa; dbms_output.put_line('雇员名是:'||v_ename||' 薪水是：'||v_sal); end; /
优化【ename,sal对应的顺序不能错】： declare v_ename varchar2(5); v_sal number(7,2); begin select ename,sal into v_ename,v_sal from emp where empno=&aa; dbms_output.put_line('测试异常是否终止程序执行！'); dbms_output.put_line('雇员名是:'||v_ename||' 薪水是：'||v_sal); end; /
【实例3-包含定义部分、执行部分和例外处理部分】 为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理， 这个很有必要： ①比如在实例2中，如果输入了不存在的雇员号，应当要做例外处理。 declare v_ename varchar2(5); v_sal number(7,2); begin select ename,sal into v_ename,v_sal from emp where empno=&aa; dbms_output.put_line('雇员名是:'||v_ename||' 薪水是：'||v_sal); exception when no_data_found then dbms_output.put_line('朋友，您的编号输入有误!'); end; /
②有时出现异常，希望用另外的逻辑处理【有了异常不捕获的程序就会终止不继续往下执行】 相关说明：oracle实现预定义了一些例外，no_data_found就是找不大数据的例外
【过程】 过程用于执行特定的操作。当建立过程时，既可以指定输入参数(in)，也可以指定输出 参数(out)。通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数 ，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令 来建立过程。 实例如下： ①请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资 ②如何调用过程有两种方法： ③如何在java程序中调用一个存储过程 try { // 1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); // 2.得到连接 Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123"); // 3.创建 CallableStatement CallableStatement cs = ct.prepareCall("{call bruce_pro3(?, ?)}"); // 4.给？赋值 cs.setString(1, "SMITH"); cs.setInt(2, 10); // 把 SMITH 的工资降为 321 // 5.执行 cs.execute(); // 6.关闭连接 cs.close(); ct.close(); } catch (Exception e) { e.printStackTrace(); } 特别说明：
【案例4-接收输入参数，参数只需指明类型，不需指定大小，如number(3,2)不必要】
create procedure bruce_pro3(name varchar2, newSal number) is begin --执行部分，根据用户名去修改工资 update emp set sal=newSal where ename=name; end; / 【如何调用该存储过程：call bruce_pro3('SCOTT',3456)、exec ...】 【存储过程主要还是给java程序去调用的，在pl/sql developer中执行顶多是测试~ 】 【每一种语言，比如java、C、C++、delphi，都是可以调用存储过程的~ 】
【函数】 函数用于返回特定的数据，当监理函数时，在函数头部必须包含return子句，而在函数体内 必须包含return语句返回的数据。我们还可以使用create function 来建立函数，实际案例： 【年薪本身是数字类型的，所以返回number】 【number(7,2)表名具体的类型是什么】
--函数案例 --输入雇员的姓名，返回该雇员的年薪 create or replace function bruce_fun1(spName varchar2) return number is yearSal number(7,2); begin --执行部分 select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName; return yearSal; end; / 【竟然会有错误，如果想知道错误时什么的话，老规矩，show error;】
【如何调用函数】 在sqlplus中调用函数 var yearSal number call bruce_fun1('SCOTT') into:yearSal; print yearSal;
同样我们可以在 java 程序中调用该函数 select annual_income('SCOTT') from dual; // 这样 可以通过rs.getInt(1)得到返回的结果
【包-和java的包其实还是很相似的(做复杂模块的时候)】 包用于在逻辑上组合过程和函数，他由包规范和包体两部分组成 ①我们可以使用create package 命令来创建包【相当于一个函数有了但是没有实现】： 实例：
--创建包 --创建一个包 bruce_package --声明该包有一个过程 bruce_pro3 --声明该包有一个函数 bruce_fun1 create or replace package bruce_package is procedure bruce_pro3(name varchar2, newSal number); function bruce_fun1(spName varchar2) return number; end; /
----------------------------------------------------------- SQL> --创建包 SQL> --创建一个包 bruce_package SQL> --声明该包有一个过程 bruce_pro3 SQL> --声明该包有一个函数 bruce_fun1 SQL> create package bruce_package is 2 procedure bruce_pro3(name varchar2, newSal number); 3 function bruce_fun1(spName varchar2) return number; 4 end; 5 / Package created -----------------------------------------------------------
包的规范值包含了过程和函数的说明，但是没有过程和函数的实现代码。 包体用于实现包规范中的过程和函数。 ②建立包体可以使用 create package body 命令。 create package body bruce_package is --过程的具体实现 procedure bruce_pro3(name varchar2, newSal number) is begin update emp set sal=newSal where ename=name; end; --函数的具体实现 function bruce_fun1(spName varchar2) return number is yearSal number(7,2); begin select sal*12+nvl(comm,0) into yearSal from emp where ename=spName; return yearSal; end; end; / ----------------------------------------------------------- SQL> create package body bruce_package is 2 --过程的具体实现 3 procedure bruce_pro3(name varchar2, newSal number) is 4 begin 5 update emp set sal=newSal where ename=name; 6 end; 7 --函数的具体实现 8 function bruce_fun1(spName varchar2) 9 return number is yearSal number(7,2); 10 begin 11 select sal*12+nvl(comm,0) into yearSal from emp where ename=spName; 12 return yearSal; 13 end; 14 end; 15 / Package body created -----------------------------------------------------------
【如何调用包的过程或是函数】 当调用包的过程或是函数时，在过程和函数前需要带上包名，如果要访问替他方案的包， 还需要在包名前加上方案名。 如： call bruce_package.bruce_pro3('SCOTT', 1500); exec bruce_package.bruce_pro3('SCOTT', 3000); 特别说明： 包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验到他的威力。
【触发器】 触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定出发的事件和出发的操作， 常用的出发事件包括insert,update,delete语句，而出发操作时机就是一个pl/sql块。可以 使用create trigger 来建立触发器。 特别说明： 我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可以维护数据库的 安全和一致性。
【定义并使用变量】 在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有： ①标量类型(scalar) ②复合类型(composite) ③参照类型(reference) ④lob(large object)
【标量(scalar)-常用类型】 在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。 pl/sql中定义标量和常量的语法如下： identifier [constant] datatype [not null] [:=| default expr] identifier: 名称 constant：指定常量。需要指定他的初始值，且其值是不能改变的 datatype：数据类型 not null：指定变量值不能为null := 给变量或是常量指定初始值 default：用于指定初始值 expr：指定初始值的pl/sql表达式，可是文本值、其他变量、函数等
【标量定义的案例】 ①定义个一个边长字符串： v_ename varchar2(10); ②定义一个小数(范围 -9999.99~9999.99) v_sal number(6,2) ③定义一个小数并给一个初始值为5.4:=是pl/sql的赋值号 v_sal number(6.2):=5.4 ④定义一个日期类型的数据 v_hiredate date; ⑤定义一个布尔变量，不能为空，初始值为false v_valid boolean not null default false;
【标量(scalar)- 使用标量】 【标量说白了就是存储最普通、最简单的变量，仅仅这个是不够用的~】 在定义号变量后，就可以使用这些变量。这里需要说明的是pl/sql块为 变量赋值不同于其他的编程语言，需要在等号前面加冒号(:=)
下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)。 说明变量的使用，看看如何编写。【number(3,2)表示有3位，2位小数】
declare --定义其间要使用到的变量和常量 c_tax_rate number(3,2):=0.03; --用户名 v_ename varchar2(5);--这个地方有漏洞，如果查到的姓名超过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=&no; --在pl/sql中完全可以用加减乘除来进行计算 --计算所得税 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字符的话，就会有错误，为了降低pl/sql程序的维护工作， 可以使用%type属性定义变量，这样他会按照数据库列来确定你定义的变量的类型和长度 用法如下： 标示符名 表名.列名%type 上个案例的改进版【解决“character string buffer too small” 的问题】：
declare --定义其间要使用到的变量和常量 c_tax_rate number(3,2):=0.03; --用户名 v_ename emp.ename%type;--这个地方有漏洞，如果查到的姓名超过5个字符，则溢出 v_sal emp.sal%type; v_tax_sal number(7,2); begin --执行 select ename,sal into v_ename,v_sal from emp where empno=&no; --在pl/sql中完全可以用加减乘除来进行计算 --计算所得税 v_tax_sal:=v_sal*c_tax_rate; --输出一下 dbms_output.put_line('姓名是'||v_ename||'工资:'||v_sal||'交税:'||v_tax_sal); end; /
【复合变量(composite)- 介绍】 用于存放多个值的变量。主要包括这几种： ①pl/sql记录 ②pl/sql表 ③嵌套表 ④varray
【复合类型-pl/sql记录】 类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时， 必须要加记录变量作为前缀(记录变量。记录成员)如下：
--pl/sql记录实例 declare --定义了一个 pl/sql 记录类型 emp_record_type,类型包含三个数据name,sal,job 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||sp_record.title); end; / --------------------------------------------------------------- SQL> --pl/sql记录实例 SQL> declare 2 --定义了一个 pl/sql 记录类型 emp_record_type,类型包含三个数据name,sal,job 3 type emp_record_type is record( 4 name emp.ename%type, 5 salary emp.sal%type, 6 title emp.job%type 7 ); 8 --定义了一个 sp_record 变量，这个变量的类型是 emp_record_type 9 sp_record emp_record_type; 10 begin 11 select ename,sal,job into sp_record from emp where empno=7788; 12 dbms_output.put_line('员工:'||sp_record.name||sp_record.salary||sp_record.title); 13 end; 14 / 员工:SCOTT3000ANALYST PL/SQL procedure successfully completed ---------------------------------------------------------------
【复合类型-pl/sql表 - 相当于高级语言中的数组】 【如果把where条件去掉会怎么样呢？】 但是需要注意的是在高级语言中数组的下表不能为负数， 而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：
--pl/sql表实例 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; -- 定义了一个 sp_table 变量，这个变量的类型是 sp_table_type sp_table sp_table_type; begin -- 爱怎么放怎么放，table(1000)、table(-1) 也行！ -- 如果返回来的是一堆数据的话肯定会爆 select ename into sp_table(100) from emp where empno=7788; dbms_output.put_line('员工名:'|| sp_table(100)); end; /
说明： sp_table_type 是pl/sql表类型 emp.ename%type 指定了表的元素的类型和长度 sp_table 为pl/sql表变量 sp_table(0) 则表示下标为 0 的元素
【怎么解决下面的问题?--应该使用参照变量！】 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; -- 定义了一个 sp_table 变量，这个变量的类型是 sp_table_type sp_table sp_table_type; begin -- 爱怎么放怎么放，table(1000)、table(-1) 也行！ -- 如果返回来的是一堆数据的话肯定会爆 select ename into sp_table(100) from emp; dbms_output.put_line('员工名:'|| sp_table(100)); end; /
【参照变量-介绍】 参照变量是指用于存放数值指针的变量。通过使用参照变量。 可以使得应用程序共享相同对象，从而降低占用的空间。在编写 pl/sql程序时，可以使用【游标变量(ref cursor)】和对象类型变量 (ref obj_type) 两种参照变量类型。
【参照变量-ref cursor游标变量，使用非常广泛、普遍】 使用游标时，当定义游标时不需要指定相应的select语句，但是当使用 游标时(open时)需要指定select语句，这样一个游标就与一个select语句 结合了。实例如下： ①请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。
declare --定义游标 type sp_emp_cursor is ref cursor; --定义一个游标变量 test_cursor sp_emp_cursor; --定义变量 v_ename emp.ename%type; v_sal emp.sal%type; begin --执行 --把test_cursor 和一个 select 结合 --把test_cursor 游标指向结果集 open test_cursor for select ename,sal from emp where deptno=&no; --循环取出 loop fetch test_cursor into v_ename,v_sal; --判断工资高低，决定是否更新 --判断是否 test_cursor 为空(如果没有这句的话，就是死循环) exit when test_cursor%notfound; dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal); end loop; --关闭游标 close test_cursor; end; /
②在①的基础上，如果某个员工的工资低于200员，就增加100员。
