什么是PL/SQL
PL/SQL是一种过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
PL/SQL语法
1）hello,world
--打印Hello World
declare
--说明部分
begin
--程序部分
dbms_output.put_line('Hello World'); -- dbms_output相当于java中的类
end;
/
2）定义变量类型
- 引用型变量
--引用型变量: 查询并打印7839的姓名和薪水 declare --定义变量保存姓名和薪水 --pename varchar2(20); -- 声明变量类型的方式1：直接定义变量的类型 --psal number; pename emp.ename%type; -- 声明变量类型的方式2（引用型变量）：与emp表中的ename列的类型相同 psal emp.sal%type; begin --得到7839的姓名和薪水 select ename,sal into pename,psal from emp where empno=7839; -- 使用into为变量赋值 --打印 dbms_output.put_line(pename||'的薪水是'||psal); -- ||是Oracle中的字符串拼接 end; /
- 记录型变量
--记录型变量: 查询并打印7839的姓名和薪水 declare --定义记录型变量：代表emp表中的一行 emp_rec emp%rowtype; begin select * into emp_rec from emp where empno=7839; dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal); end; /
3）if语句
-- 判断用户从键盘输入的数字
--接受键盘输入
--变量num：是一个地址值，在该地址上保存了输入的值
accept num prompt '请输入一个数字';
declare
--定义变量保存输入 的数字
pnum number := #
begin
if pnum = 0 then dbms_output.put_line('您输入的是0');
elsif pnum = 1 then dbms_output.put_line('您输入的是1'); -- 注意：是elsif
elsif pnum = 2 then dbms_output.put_line('您输入的是2');
else dbms_output.put_line('其他数字');
end if;
end;
/
4）循环
普通循环
-- 打印1~10
declare
-- 定义变量
pnum number := 1;
begin
loop
--退出条件
exit when pnum > 10;
--打印
dbms_output.put_line(pnum);
--加一
pnum := pnum + 1;
end loop;
end;
/
While 循环，先判定条件，每次循环时条件都要变化，如果不变化就是死循环
Declare
V1 number(2) :=1;
Begin
While v1<10 Loop
Insert into t1 values(v1);
v1:=v1+1;
End loop;
End;
/
For循环，pl/sql中的最常见的循环，是和游标操作的绝配。方便而直观。
begin
for v1 in 1..9 loop
Insert into t1 values(v1);
end loop;
end;
/
For循环特点
- 步长为1
- 对计数器只能引用。不能做赋值操作
- 计数器的数据类型和上下界的数据类型相同
- 计数器只能在循环体内引用
5）光标
-- 查询并打印员工的姓名和薪水
/*
光标的属性： %isopen（是否打开） %rowcount（影响的行数）
%found（是否有值） %notfound（是否无值）
*/
declare
--定义光标（游标）
cursor cemp is select ename,sal from emp;
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open cemp;
loop
--取当前记录
fetch cemp into pename,psal;
--exit when 没有取到记录;
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
--关闭光标
close cemp;
end;
/
示例：给员工涨工资
-- 给员工涨工资，总裁1000 经理800 其他400
declare
--定义光标
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
--打开光标
open cemp;
loop
--取一个员工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断职位
if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
--关闭光标
close cemp;
--提交 ----> why?: 事务 ACID
commit;
dbms_output.put_line('完成');
end;
/
- 带参数的光标
-- 查询某个部门的员工姓名
declare
--形参
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
--实参
open cemp(20);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
6）例外（异常）
- 系统例外
-- 被0除
declare
pnum number;
begin
pnum := 1/0;
exception
when zero_divide then dbms_output.put_line('1:0不能做分母');
dbms_output.put_line('2:0不能做分母');
when value_error then dbms_output.put_line('算术或者转换错误');
when others then dbms_output.put_line('其他例外');
end;
/
- 自定义例外
-- 查询50号部门（50号部门不存在）的员工
declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
open cemp;
--取第一条记录
fetch cemp into pename;
if cemp%notfound then
--抛出例外，使用raise
raise no_emp_found;
end if;
--进程：pmon进程(proccesss monitor)
close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put_line('其他例外');
end;
/
实例1：统计每年入职的员工个数（使用PL/SQL）
/*
1、SQL语句
select to_char(hiredate,'yyyy') from emp;
---> 集合 ---> 光标 ---> 循环---> 退出: notfound
2、变量：（*）初始值 （*）最终如何得到
每年入职的人数
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
declare
--定义光标
cursor cemp is select to_char(hiredate,'yyyy') from emp;
phiredate varchar2(4);
--每年入职的人数
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
--打开光标
open cemp;
loop
--取一个员工的入职年份
fetch cemp into phiredate;
exit when cemp%notfound;
--判断年份是哪一年
if phiredate = '1980' then count80:=count80+1;
elsif phiredate = '1981' then count81:=count81+1;
elsif phiredate = '1982' then count82:=count82+1;
else count87:=count87+1;
end if;
end loop;
--关闭光标
close cemp;
--输出
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line('1980:'|| count80);
dbms_output.put_line('1981:'|| count81);
dbms_output.put_line('1982:'|| count82);
dbms_output.put_line('1987:'|| count87);
end;
/
实例2：为员工涨工资，总工资最低的人开始涨，没人涨10%，但工资总额不超过万元，请计算长工资的人数和长工资后的工资总额。
/*
1、SQL语句
selet empno,sal from emp order by sal;
---> 光标 ---> 循环 ---> 退出：1. 总额>5w 2. notfound
2、变量：（*）初始值 （*）最终如何得到
涨工资的人数: countEmp number := 0;
涨后的工资总额：salTotal number;
(1)select sum(sal) into salTotal from emp;
(2)涨后=涨前 + sal *0.1
练习： 人数：8 总额:50205.325
*/
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--涨工资的人数:
countEmp number := 0;
--涨后的工资总额：
salTotal number;
begin
--得到工资总额的初始值
select sum(sal) into salTotal from emp;
open cemp;
loop
-- 1. 总额 >5w
exit when salTotal > 50000;
--取一个员工
fetch cemp into pempno,psal;
--2. notfound
exit when cemp%notfound;
--涨工资
update emp set sal=sal*1.1 where empno=pempno;
--人数+1
countEmp := countEmp +1;
--涨后=涨前 + sal *0.1
salTotal := salTotal + psal * 0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('人数：'||countEmp||' 总额:'||salTotal);
end;
/
自认为正确的
-- Created on 2018/7/9 星期一 by X5456
declare
-- 定义光标，存放id和工资
cursor cemp is select empno,sal from emp order by sal;
-- 定义相应的变量
pempno emp.empno%type;
psal emp.sal%type;
--涨工资的人数:
countEmp number := 0;
--涨后的工资总额：
salTotal number;
--循环结束的flag
flag boolean := false;
begin
-- 1.获取总工资
select sum(sal) into salTotal from emp;
dbms_output.put_line('涨后的工资123123:'|| salTotal);
--打开光标
open cemp;
-- 2.循环的涨工资
loop
-- 退出条件，总工资大于5w
exit when flag;
fetch cemp into pempno,psal;
--exit when 没有取到记录;
exit when cemp%notfound;
-- 进行判断，是否涨工资
if salTotal+psal*0.1 > 50000 then flag := true;
else update emp set sal = psal*1.1 where empno = pempno;
salTotal := salTotal+psal*0.1;
countEmp := countEmp+1;
end if;
end loop;
--关闭光标
close cemp;
commit;
dbms_output.put_line('涨后的工资:'|| salTotal);
dbms_output.put_line('涨工资的人数:'|| countEmp);
end;
实例3：
/*
1、SQL语句
部门：select deptno from dept; ---> 光标
部门中员工的薪水: select sal from emp where deptno=?? ---> 带参数的光标
2、变量：（*）初始值 （*）最终如何得到
每个段的人数
count1 number; count2 number; count3 number;
部门的工资总额
salTotal number := 0;
(1)select sum(sal) into salTotal from emp where deptno=??
(2)累加
*/
declare
--部门
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;
--部门中员工的薪水
cursor cemp(dno number) is select sal from emp where deptno=dno;
psal emp.sal%type;
--每个段的人数
count1 number; count2 number; count3 number;
--部门的工资总额
salTotal number := 0;
begin
--部门
open cdept;
loop
--取一个部门
fetch cdept into pdeptno;
exit when cdept%notfound;
--初始化
count1:=0; count2:=0; count3:=0;
--得到部门的工资总额
select sum(sal) into salTotal from emp where deptno=pdeptno;
--取部门的中员工薪水
open cemp(pdeptno);
loop
--取一个员工的薪水
fetch cemp into psal;
exit when cemp%notfound;
--判断
if psal < 3000 then count1:=count1+1;
elsif psal >=3000 and psal<6000 then count2:=count2+1;
else count3:=count3+1;
end if;
end loop;
close cemp;
--保存结果
insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
end loop;
close cdept;
commit;
dbms_output.put_line('完成');
end;
/
存储过程
-- 打印Hello World
/*
调用存储过程的2种方式：
1、exec sayhelloworld(); -- SQLplus命令
2、begin
sayhelloworld();
sayhelloworld();
end;
/
*/
create or replace procedure sayhelloworld
as -- 使用is也可以
--说明部分
begin
dbms_output.put_line('Hello World');
end;
/
- 带参数的存储过程
--给指定的员工涨100，并且打印涨前和涨后的薪水
create or replace procedure raiseSalary(eno in number)
is
--定义变量保存涨前的薪水
psal emp.sal%type;
begin
--得到涨前的薪水
select sal into psal from emp where empno=eno;
--涨100
update emp set sal=sal+100 where empno=eno;
--要不要commit？在存储过程中一般都不commit和rollback，应该交给调用者来操作
dbms_output.put_line('涨前:'||psal||' 涨后：'||(psal+100));
end raiseSalary;
/
-- 调用方法，给员工号为7839,7566涨工资
begin
raiseSalary(7839);
raiseSalary(7566);
commit;
end;
/
- out（返回）参数
--查询某个员工的姓名 薪水和职位
/*
两个问题
1、查询某个员工的所有信息 ---> out参数太多
2、查询某个部门中的所有员工信息 ----> 返回的是集合
*/
create or replace procedure queryEmpInformation(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
is
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end queryEmpInformation;
/
-- 运行
begin
queryEmpInformation(eno => 7839, -- 参数名 => 值 （为指定的参数名赋值）
pename => :pename,
psal => :psal,
pjob => :pjob);
end;
/
有了out，存储函数就多余了，因为之前存储过程不能返回参数，存储函数可以返回一个参数
- 实现返回一个集合（采用package与package body）
--2、查询某个部门中的所有员工信息 ----> 返回的是集合
create or replace package mypackage is
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
end mypackage;
/
create or replace package body mypackage is
procedure queryEmpList(dno in number,empList out empcursor)
as
begin
open empList for select * from emp where deptno=dno;
end;
end mypackage;
/
- java程序调用
@Test
public void testCursor(){
String sql = "{call mypackage.QUERYEMPLIST(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数，赋值
call.setInt(1,20);
//对于out参数，申明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出结果
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//取出一个员工
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(name+" "+sal);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
存储函数
--查询某个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
is
--定义变量保存月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end queryEmpIncome;
/
java连接Oracle操作存储过程/函数
JDBCUtils（建立/断开与Oracle连接）
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.137.129:1521/orcl";
private static String user = "scott";
private static String password = "tiger";
static{
//注册驱动
//DriverManager.registerDriver(driver)
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/*
* 运行Java：
* java -Xms100M -Xmx200M HelloWorld
*
* 技术方向：
* 1、性能优化
* 2、故障诊断：死锁(JDK: ThreadDump)
* Oracle: 自动处理
*/
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null; ///-----> 原因：Java GC（Java的GC不受代码的控制），将引用置为空，就会被gc自动回收释放资源
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
有返回值与没返回值的调用方法
public class TestOracle {
/* 有返回值
* create or replace procedure queryEmpInformation(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/
@Test
public void testProcedure(){
//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call queryEmpInformation(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数，赋值
call.setInt(1,7839);
//对于out参数，申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//执行
call.execute();
//输出
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+" "+sal+" "+job);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
/* 无返回值
* create or replace function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){
//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{?=call queryEmpIncome(?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);
//执行
call.execute();
//取出年收入
double income = call.getDouble(1);
System.out.println(income);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
}
触发器
数据库触发器是一个与表相关联，存储PL/SQL的程序。每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动的执行触发器。
-- 每当成功插入新员工后，自动打印“成功插入新员工”
create trigger firsttrigger
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新员工');
end firsttrigger;
/
触发器的类型
语句级触发器：在指定的操作语句之前或之后执行一次，不管这条语句影响了多少行。
行级触发器（FOR EACH ROW）：触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量来识别值的状态
触发器的格式
create [or replace] trigger 触发器名
{after|before} {insert|delete|update[of 列名]}
on 表名
[for each row [when(条件)]] -- 行级触发器
-- PLSQL块
declare
begin
dbms_output.put_line('成功插入新员工');
end;
/
触发器作用
- 1.数据确认
- 2.实施复杂的安全性检查
- 3.做审计，跟踪表上所做的数据操作等
- 4.数据的备份和同步
语句级触发器Demo
/*
实施复杂的安全性检查（作用2）
禁止在非工作时间 插入新员工
1、周末: to_char(sysdate,'day') in ('星期六','星期日')
2、上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17
*/
create or replace trigger securityemp
before insert
on emp
begin
-- SQL的单行函数可以在PLSQL中直接使用
if to_char(sysdate,'day') in ('星期六','星期日','星期五') or
to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
--禁止insert
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end securityemp;
/
行级触发器Demo
/*
数据的确认（作用1）
涨后的薪水不能少于涨前的薪水
*/
create or replace trigger checksalary
before update
on emp
for each row
begin
--if 涨后的薪水 < 涨前的薪水 then
if :new.sal < :old.sal then
raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水。涨前:'||:old.sal||' 涨后:'||:new.sal);
end if;
end checksalary;
/
练习题（学会：new的使用）
create or replace trigger limitempcount
before insert
on emp
for each row
declare
-- local variables here
count_emp number;
begin
select count(*) into count_emp from emp where deptno=:new.deptno;
if count_emp >= 5 then
raise_application_error('20001','部门号：'||:new.deptno||'已经有'||count_emp||'人');
end if;
end limitempcount;