??数据库中的一段脚本代码 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。
//语法 declare和exception可以没有
[declare
--声明变量
]
begin
--代码逻辑
[exception
--异常处理
]
end;
//声明变量的语法：数据库中变量名和类型的顺序与java比是反过来的
变量名 类型（长度）；
//变量赋值的语法：是冒号等号(规定，相当于=)oracle没有双等(单等就是双等)
变量名:=变量值
//需求
声明变量水费单价、水费字数、吨数、金额。对水费单价、字数、进行赋值 。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数。输出单价 、数量和金额。
//变量的用法
declare
v_price number(10,2);--水费单价
v_usenum number; --水费字数 默认长度18
v_usenum2 number(10,2);--吨数
v_money number(10,2);--金额
begin
v_price:=2.45;--水费单价
v_usenum:=8012;--字数
--字数换算为吨数
v_usenum2:= round( v_usenum/1000,2);
--计算金额
v_money:=round(v_price*v_usenum2,2);
dbms_output.put_line('单价：'||v_price||'吨
数:'||v_usenum2||'金额：'||v_money);
end;//dbms_output.put_line是内置的存储过程 输出金额在Output中可看
//语法:结果必须是一条记录,有多条记录和没有记录都会报错
select 列名 into 变量名 from 表名 where 条件
//声明变量水费单价、水费字数、上月字数、本月字数、吨数、金额。对水费单价赋值,其余字段通过查询赋值。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数。输出单价 、数量和金额。
declare
v_price number(10,2);--单价
v_usenum number;--水费字数
v_num0 number;--上月字数
v_num1 number;--本月字数
v_usenum2 number(10,2);--使用吨数
v_money number(10,2);--水费金额
begin
//对单价进行赋值
v_price:=3.45;
//变量赋值 select查询字段into赋值给变量from来源表where条件
select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT
where year='2012' and month='01' and owneruuid=1;
v_usenum2:= round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
||v_usenum2||'金额：'||v_money||'上月字数：'||v_num0||'本月
字数'||v_num1);
end;
??%TYPE 引用型-作用：引用某表某列的字段类型-动态编程中常用
//格式 表名.列名%type -表示该表该列的类型
declare
v_price number(10,2);--单价
v_usenum T_ACCOUNT.USENUM%TYPE;--水费字数，引用类型
v_num0 T_ACCOUNT.NUM0%TYPE;--上月字数
v_num1 T_ACCOUNT.NUM1%TYPE;--本月字数
v_usenum2 number(10,2);--使用吨数
v_money number(10,2);--水费金额
begin
//对单价进行赋值
v_price:=3.45;
select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT
where year='2012' and month='01' and owneruuid=1;
//使用吨数
v_usenum2:= round(v_usenum/1000,2);
//计算金额
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
||v_usenum2||'金额：'||v_money||'上月字数：'||v_num0||'本月
字数'||v_num1);
end;
%ROWTYPE 记录型 ,上个例子可以用下面的代码代替作用: 标识某个表的行记录类型
//代表某个表某一行的类型，多个变量都来自某张表的某一行的多个字段的值，此时可以把一行的记录赋给一个变量，该变量可以通过表的记录性变量表示(类似实体类)
declare
v_price number(10,2);--单价
v_account T_ACCOUNT%ROWTYPE;--记录型，代表该表每一行的信息(记录类型)
v_usenum2 number(10,2);--使用吨数
v_money number(10,2);--水费金额
begin
//对单价进行赋值
v_price:=3.45;
//赋值 查询所有赋给v_account记录型
select * into v_account from T_ACCOUNT
where year='2012' and month='01' and owneruuid=1;
//使用吨数 打点调用取出对应字段数据
v_usenum2:= round(v_account.usenum/1000,2);
//计算金额
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('单价：'||v_price||'吨数：'
||v_usenum2||'金额：'||v_money||'上月字数：
'||v_account.num0||'本月字数'||v_account.num1);
end;
??在运行程序时出现的错误叫做异常，发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分异常有两种类型：
??预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发-21个
??用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
//常见
NO_DATA_FOUND 使用 select into 未返回行 (没有行记录可以返回)
TOO_MANY_ROWS 执行 select into 时，结果集超过一行 (行记录不止1行)
//语法
exception
when 异常类型 then
异常处理逻辑
//上例中的代码，添加异常处理部分
//变量的用法
declare
v_price number(10,2);--水费单价
v_usenum T_ACCOUNT.USENUM%type; --水费字数
v_usenum2 number(10,3);--吨数
v_money number(10,2);--金额
begin
v_price:=2.45;--水费单价
select usenum into v_usenum from T_ACCOUNT where
owneruuid=1 and year='2012' and month='01';
//字数换算为吨数
v_usenum2:= round( v_usenum/1000,3);
//计算金额
v_money:=round(v_price*v_usenum2,2);
dbms_output.put_line('单价：'||v_price||'吨
数:'||v_usenum2||'金额：'||v_money);
exception
when NO_DATA_FOUND then
dbms_output.put_line('未找到数据，请核实');
when TOO_MANY_ROWS then
dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;
//基本语法 1 if(){}
if 条件 then
业务逻辑
end if;
//基本语法 2 if(){} else{}
if 条件 then
业务逻辑
else
业务逻辑
end if;
//基本语法 3 if(){} else if{} else if{} else{}
if 条件 then
业务逻辑
elsif 条件 then //就是elsif 规定
业务逻辑
else
业务逻辑
end if;
//设置三个等级的水费 5 吨以下 2.45 元/吨 5 吨到 10 吨部分 3.45 元/吨 ，超过 10 吨部分 4.45,根据使用水费的量来计算阶梯水费
declare
v_price1 number(10,2);--不足 5 吨的单价
v_price2 number(10,2);--超过 5 吨不足 10 吨单价
v_price3 number(10,2);--超过 10 吨单价
v_account T_ACCOUNT%ROWTYPE;--记录型
v_usenum2 number(10,2);--使用吨数
v_money number(10,2);--水费金额
begin
//对单价进行赋值
v_price1:=2.45;
v_price2:=3.45;
v_price3:=4.45;
//赋值
select * into v_account from T_ACCOUNT
where year='2012' and month='01' and owneruuid=1;
//使用吨数
v_usenum2:= round(v_account.usenum/1000,2);
//计算金额(阶梯水费)
if v_usenum2<=5 then--第一个阶梯
v_money:=v_price1*v_usenum2;
elsif v_usenum2>5 and v_usenum2<=10 then --第二个阶梯
v_money:=v_price1*5 + v_price2*( v_usenum2-5);
else --第三个阶梯
v_money:=v_price1*5 +v_price2*5 +
v_price3*( v_usenum2-10 );
end if;
DBMS_OUTPUT.put_line('吨数：'
||v_usenum2||'金额：'||v_money||'上月字数：
'||v_account.num0||'本月字数'||v_account.num1);
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line('没有找到数据');
when TOO_MANY_ROWS then
DBMS_OUTPUT.put_line('返回的数据有多行');
end;
无条件循环
//语法结构
loop
--循环语句
end loop;
//范例：输出从1开始的100个数 没有自增++
declare
v_num number:=1;
begin
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
//退出循环 if v_num>100 then exit; end if;可以不用写了
exit when v_num>100;
end loop;
end ;
有条件循环
//语法结构 条件成立 进入循环
while 条件
loop
end loop;
//范例：输出从1开始的100个数
declare
v_num number:=1;
begin
while v_num<=100 //条件v_num<=100 不用写exit语句
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end ;
for循环-变量自动声明
//语法结构
for 变量 in 起始值..终止值
loop
end loop;
//范例：输出从1开始的100个数
begin
for v_num in 1..100 //变量自动声明
loop
dbms_output.put_line(v_num); //变量只能在loop和end loop;中使用
end loop;
end;
??游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集。(暂时存放查询结果的结果集，该结果集可以循环，可以读取数据(按行)，打开游标操作后记得关闭游标)
//在声明区声明游标，语法如下：(游标需要一个结果集)%notfound是游标的属性
cursor 游标名称 is SQL 语句;
//使用游标语法
open 游标名称 //打开游标
loop //循环
fetch 游标名称 into 变量 //提取游标给变量 每提一条指针向后走一条
exit when 游标名称%notfound //游标走到底 返回一个为真的布尔值
end loop;
close 游标名称 //关闭游标
案例
//打印业主类型为 1 的价格表
declare
v_pricetable T_PRICETABLE%rowtype; //价格行对象
cursor cur_pricetable is select * from T_PRICETABLE where
ownertypeid=1; //定义声明游标
begin
open cur_pricetable //打开游标
loop
fetch cur_pricetable into v_pricetable; //提取游标到变量
exit when cur_pricetable%notfound; //当游标到最后一行下面退出循环
dbms_output.put_line( '价格:'
||v_pricetable.price ||'吨位：
'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
end loop;
close cur_pricetable; //关闭游标
end ;
??带参数的游标-游标sql语句条件有可能变动，可以通过参数传递进来(变量前类型后)我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型，可能是运行时才可以决定
//上述案例
declare
v_pricetable T_PRICETABLE%rowtype;//价格行对象
//定义游标(变量名 类型)where语句后面使用变量名
cursor cur_pricetable(v_ownertypeid number) is select *
from T_PRICETABLE where ownertypeid=v_ownertypeid;
begin
open cur_pricetable(2);//打开有参数游标(括号内是传递的参数)
loop
fetch cur_pricetable into v_pricetable;--提取游标到变量
exit when cur_pricetable%notfound;--当游标到最后一行下面退
出循环
dbms_output.put_line('价格:'||v_pricetable.price ||'吨
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
end loop;
close cur_pricetable;//关闭游标
end ;
??带参数的for循环游标-声明部分相同，不用打开游标 关闭游标 退出循环 提取游标了，需要写 for v_pricetable in cur_pricetable(3)，for循环会自动打开关闭游标
declare
cursor cur_pricetable(v_ownertypeid number) is select *
from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游标
v_pricetable T_PRICETABLE%rowtype; --价格行对象，用for该声明可省略
begin
for v_pricetable in cur_pricetable(3)
loop
dbms_output.put_line('价格:'||v_pricetable.price ||'吨
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
end loop;
end ;
??存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。(函数是oracle内部封装好的)
//创建或修改存储过程的语法如下: 在Functions文件夹中看(绿色没问题)
CREATE [ OR REPLACE ] FUNCTION 函数名称 //OR REPLACE 若存在自动覆盖
（参数名称 参数类型, 参数名称 参数类型, ...） //FUNCTION函数 类型不写长度
RETURN 结果变量数据类型 //声明返回的数据类型
IS
变量声明部分; //需要声明的部分 类似declare
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;
//案例-创建存储函数，根据地址 ID 查询地址名称。 fn_开头
create function fn_getaddress(v_id number)
return varchar2
is
v_name varchar2(30);
begin
//查询地址表
select name into v_name from t_address where id=v_id;
return v_name;
end;
//测试此函数：传入id为3
select fn_getaddress(3) from dual
//查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数来实现。
select id 编号,name 业主名称,fn_getaddress(addressid) 地址
from t_owners //把addressid作为参数传递进来
??存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
??应用程序可以调用存储过程，执行相应的逻辑。存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码
//语法 PROCEDURE-存储过程
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;
//参数只指定类型，不指定长度
过程参数的三种模式：在参数的类型前面加
IN 传入参数（默认可缺省）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数
//案例 最好用pro_开头
//创建不带传出参数的存储过程：添加业主信息
//增加业主信息序列 从11开始
create sequence seq_owners start with 11;
//增加业主信息存储过程
create or replace procedure pro_owners_add
(
v_name varchar2, //默认为in
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_type number
)
is
begin
//ID直接读取序列
insert into T_OWNERS
values( seq_owners.nextval,v_name,v_addressid,v_housenumb
er,v_watermeter,sysdate,v_type );
commit;
end;
//PL/SQL中调用不带传出参数存储过程
call pro_owners_add('赵伟',1,'999-3','132-7',1);
//或者
begin
call pro_owners_add('赵大伟',2,'999-3','132-7',1);
end;
//JDBC 调用存储过程
public class OwnersProDao {
public static void add(Owners owners){
//打开链接
java.sql.Connection conn=null;
//调用存储过程的接口
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
//call中执行的sql必须在{}中 几个参数就几个问号
stmt=conn.prepareCall(
"{call pro_owners_add(?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddress());
stmt.setString(3, owners.getHousernumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.execute();
} catch (
SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
}
//测试
public class Test4 {
public static void main(String[] args) {
Owners owners=new Owners();
owners.setName("许玩攸");
owners.setAddress(2L);
owners.setHousernumber("3-2");
owners.setWatermeter("9149");
owners.setOwnertypeid(1L);
OwnersProDao.add(owners);
System.err.println("ok");
}
}
//增加业主信息存储过程-out-存储过程没有return，给传出参数赋值会自动return
create or replace procedure pro_owners_add
(
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_type number,
v_id out number
)
is
begin
//先查询序列给v_id(对传出参数赋值)
select seq_owners.nextval into v_id from dual;
//新增业主记录，序列在上面用过一次，同一行不能再用(序号会往下走)
insert into T_OWNERS
values( v_id,v_name,v_addressid,v_housenumber,
v_watermeter,sysdate,v_type );
commit; //记得提交
end;
//PL/SQL 调用该存储过程 带传出参数不能用call的方式
declare
v_id number; //定义传出参数的变量,把他放在后面就行
begin
//这一行执行结束，传出参数就有值了
pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;
//JDBC 调用存储过程
public static Long addtake(Owners owners){
Long id=0l; //打开链接
java.sql.Connection conn=null;
//调用存储过程的接口
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
//call中执行的sql必须在{}中 几个参数就几个问号 传出参数也要带问号
stmt=conn.prepareCall(
"{call pro_owners_add(?,?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddress());
stmt.setString(3, owners.getHousernumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
//注册传出参数的类型 (枚举值)
stmt.registerOutParameter(6, OracleTypes.NUMBER);
stmt.execute();//执行后可拿出传出参数的值
id=stmt.getLong(6);//拿出传出参数的值
} catch (
SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
return id;
}
//测试
public static void main(String[] args) {
Owners owners=new Owners();
owners.setName("许c攸");
owners.setAddress(2L);
owners.setHousernumber("3-2");
owners.setWatermeter("9149");
owners.setOwnertypeid(1L);
OwnersProDao.addtake(owners);
System.err.println("ok");
}
??数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列，在Triggers文件夹中看，在表中也可以看(表中也有Triggers)
触发器可用于
??数据确认
??实施复杂的安全性检查
??做审计，跟踪表上所做的数据操作等
??数据的备份和同步
触发器分类
??前置触发器（BEFORE）先执行触发器再执行代码(类似拦截可以修改值)
??后置触发器（AFTER）反过来(不可以修改值)
//创建触发器的语法-TRIGGER
CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER //前置|后置
//选择触发触发器的操作，若想选择多个可通过or连接 OF可指定列多个用逗号分隔
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]//只有of的列会触发
ON 表名 //触发器附属于的表，表删了触发器也没了
//加上后变成行级触发器 不写是语句级触发器 when 开关返回值为真触发器可以触发
[FOR EACH ROW ][WHEN(条件)]//when一般不写
declare
……
begin
PLSQL 块
End;
//FOR EACH ROW作用标注此触发器是行级触发器，每影响一行触发一次(删100行100次)
//语句级触发器 不管删多少行 只要是一句话就只触发一次
//伪记录变量:old相当于修改数据之前的数据(一行数据，数据可通过:old打点列名调用)，:new代表修改之后的，原来叫马三(:old)做修改变马四(:new),不需声明自动就有(同步备份)，语句触发器无法得到伪记录变量
触发语句 :old :new
Insert 所有字段都是空(null) 将要插入的数据
Update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)
//当用户输入本月累计表数后，自动计算出本月使用数-前置before
create or replace trigger tri_account_updatenum1
before
update of num1 //修改名为num1的列
on t_account //修改名为t_account的表
for each row //行级触发器
declare
begin
//用伪记录变量修改usenum字段的值，不需update语句，赋值既可修改，不需commit
:new.usenum:=:new.num1-:new.num0;
end;
//当进入工具修改num1字段的值时，usenum字段的值也随之变(点一下对号就变不用点提交，可以看到实时的效果，之后再点击commit既可)触发器自动commit，存储过程需要commit
//当用户修改了业主信息表的数据时记录修改前与修改后的值-后置after
//在后置触发器中可以读:new的值，不可以改:new的值
//创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
//创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
insert into t_owners_log
values(sysdate,:old.id,:old.name,:new.name);
end;
//测试
//更新数据
update t_owners set name='杨小花' where id=3;
commit;
//查询日志表
select * from t_owners_log;