Oracle常用命令08(pl_sql编程)

【期望目标】
①理解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员。