PLSQL和ORACLE学习笔记

一、PLSQL简介
1、什么是PLSQL
Procedure Language SQL，过程化SQL语言（结构化查询语言）。
它是Oracle数据库“特有”的编程语法，可以实现一些复杂的运算及功能。
它是在SQL语言基础上加入了循环、判断等元素，而提出的数据库编程方式。
PLSQL存储并运行在Oracle数据上。
说明：
批处理和plsql的区别:
--批处理是接收外界的数据，进行批量处理。
--plsql是内部数据。
2、PLSQL的作用
在数据库上直接运行，效率高。
适合于处理大批量基于数据库数据的运算。
3、优缺点
优点：执行效率高
缺点：非面向对象语言，编写及维护难度大。
开发效率低，测试效率低。

二、PLSQL的结构
1、基本结构
//匿名块，不能存在oracle中，执行一次就行了。
declare
--变量声明区
begin
--业务处理区（主处理）
exception
--异常处理区
end;
/ --最终的结束，而不是;。
2、最简结构
begin
--业务处理区
end;
/
3、说明
1）以上代码结构，只是定义一个匿名代码块，
可以在Oracle中编译并运行一次，不会存储
在Oracle上。如果希望存储，则需要定义成
存储过程、函数、触发器。
2）PLSQL以"/"结束。
4、练习1：PLSQL之HelloWorld
1）开启输出显示
set serveroutput on; --只适合一个连接。
可以在SqlDeveloper控制台输出一些内容。
2）在Oracle控制台打印消息输出
dbms_output.put_line('xxx');

三、PLSQL的使用
1、注释
1）单行注释
--注释内容
2）多行注释
/*注释内容*/
*2、声明变量
1）语法
declare
变量名1 类型;
变量名2 类型:=值;
2）注意
--变量名定义在declare下
--给变量赋值用":="
--如： age number(3):=20;
msg varchar2(50);
flag boolean:=true;
--boolean类型打印输出，不支持。
--判断相等用"="
--如果声明变量时没有赋值，则变量值为null。
null值与其他数值运算时结果还是null。
3）练习2
声明3个变量a=3、b=5、c，
然后计算c=a+b，输出c。
*3、判断
1）语法
if 条件1 then
...
elsif 条件2 then
...
else
...
end if;
2）注意
--是elsif，而不是elseif
--最后的else后面没有条件和then
--判断以end if;结束
如：
declare
sal number(7,2):=6000;
begin
if sal = 0 then
dbms_output.put_line('待业');
elsif sal<3000 then
dbms_output.put_line('低保户');
elsif sal<8000 then
dbms_output.put_line('屌丝');
else
dbms_output.put_line('高级屌丝');
end if;
end;
/
3）练习3
判断变量sex，如果值为M则输出男，
如果值为F则输出女，否则输出人妖。
4）

练习4，综合练习
声明2个整型变量m,n，并赋值。
判断m,n的大小
若m>n，则输出1；
若m若m=n，则输出0；
*4、循环（3种方式）
1）loop循环
a、语法
loop --开始循环
--循环内部的处理逻辑
--循环变量递增
exit when 结束循环条件;
end loop; --结束循环
b、练习5，使用loop循环输出1-10
declare
i number(3):=1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when i>10;
end loop;
end;
/
c、练习6，使用loop循环从1累加到100
2）while循环
a、语法
while 条件 loop
--处理业务逻辑
--变量自增
end loop;
b、练习7，使用while循环输出1-10
declare
i number(3):=1;
begin
while i<=10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/
3）for循环
a、语法
for 变量 in 集合 loop
--处理业务逻辑
end loop;
b、练习8，使用for循环输出1-10，循环10次
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/
c、说明
--for循环中的集合要求是“数字型集合”，或者是游标。
--变量自动定义。
--有局限型。
5、使用DML和TCL语句
1）DML和TCL语句，可以直接写在PLSQL中， 没有任何特殊要求。
2）练习9，对部门表进行增、删、改操作
如：
begin
for i in 1..10 loop
insert into emp(id,name) values(i,'scott'||i);
end loop;
commit;
end;
/
6、使用DDL语句
1）语法
execute immediate 'DDL语句';
--DDL必须采用动态编译执行方法执行。
--编译时候是字符串，执行的时候是命令。
2）练习10，创建一张表，并插入一条数据
begin
execute immediate 'drop table p001';
end;
/
--immediate 立即的，直接的。
3）注意
在PLSQL中使用DDL建表后，不能直接写SQL来使用这张表。原因是编译时通不过，编译时先编译建表语句，
仅仅是对其语法进行了验证，并没有真正执行建表语句。因此后面直接使用表时由于表不存在，导致编译失败。
如果需要这样做的话，后面的SQL语句需要按照如下方式来写
execute immediate 'SQL';
--如下会出错：
begin
execute immediate 'create table p002(id number(3))';
insert into p002 values(1);
end;
/
--因为在编译时，第一句是字符串，没问题；但是编译第二句时，找不到p002
--可以将修改为execute immediate 'insert into p002 values(1)';
*7、使用DQL语句
PLSQL中调用查询的目的不是为了返回结果显示数据，因为直接查询表或者查询视图可以很好的解决显示问题。
PLSQL中的查询目的是获取数

据，并利用这些数据参与运算，因此需要将查询结果封装到变量中，使用变量运算。
1）返回一条结果
a、使用select into 变量
将查询到的这条数据中的字段值，封装到一些变量中
b、语法
--将查询到的字段1,字段2存入变量1,变量2中
select 字段1，字段2，...
into 变量1，变量2，...
from 表 where 条件
--提示：
--该语句仅适合于有且只有一条记录的情况。
--如果没有记录会出现异常。（****）
--如果返回多条记录也会出现异常。（****）no data found
c、练习11，查询一条dept数据，并输出
declare
v_name varchar2(20);
begin
select name into v_name from dept where id=6;
dbms_output.put_line(v_name);
end;
/
d、使用%type声明变量类型（******）
--指定变量的类型与表中的某字段一致
变量名 表.字段%type
如：
v_name https://www.360docs.net/doc/f413355829.html,%type;
--v_name的类型和name的一致，如果name的类型变了，v_name的类型也变了。
--缺点：
如果一个表有多个字段，向取出一条记录的所有字段值，那么需要定义很多变量接收。
这样显着代码臃肿，可以使用%rowtype。
declare
v_name https://www.360docs.net/doc/f413355829.html,%type;
v_sal emp.sally%type;
begin
select name,salary insert into v_name,v_sal from emp where id=2;
dbms_output.put_line(v_name);
dbms_output.put_line(v_sal);
end;
/
e、%ROWType
--声明一个变量，变量类型为记录类型。可以存储一行记录信息(变量名和字段名一致)。
--v_emp emp%rowtype;
declare
v_emp emp%Rowtype;
begin
select * into v_emp from emp where id=2;
dbms_output.put_line(v_https://www.360docs.net/doc/f413355829.html,);
dbms_output.put_line(v_emp.salary);
end;
/

f、练习12，将练习11的变量声明方式改为%type
2）返回多条结果
a、使用游标cursor
将查询到的多条数据封装到一个游标对象中，这个游标类似于ResultSet
b、使用loop遍历游标
语法：
declare
--声明游标，封装的是select查询的所有结果
cursor 游标名 is select语句;
begin
open 游标名; --开启游标
loop
--移动游标指针，让当前指针对应的行数据，存入变量中
fetch 游标名 into 变量;
exit when 退出条件;
--处理业务逻辑
end loop;
close 游标名; --关闭游标
end;
/
练习13:
查询多行dept，并使用游标遍历输出
declare
cursor c_p001 is select name from p001;
begin
open c_p001;
loop
fetch c_p001 into v_name;
exit when c_p001%NOTFOUND;--notfound属性变为true时，说明找不到记录就结束了
dbms_output.put_line(v_name);

end loop;
close c_p001;
end;
/
游标活动过程：
--初始时，游标指针指向第一行数据的
上方。
--每次fetch时，游标指针向下移动一个
位置，如果移动成功，则当前游标的
状态为found。
--当游标指针指向最后一个位置，再次
fetch时，游标无法再次向下移动了，
它会停留在最后的位置，但游标的状态
变为notfound。
c、使用while遍历游标
语法：
declare
cursor 游标名 is
select 语句;
begin
open 游标名;
fetch 游标名 info 变量;
while 游标名%found loop
--处理业务逻辑
fetch 游标名 info 变量;
end loop;
close 游标名;
end;
/
练习14：
使用while循环遍历游标
定义行变量：
--声明一个行变量，其结构与指定的表
一样，可以封装这个表中一行数据。
表里有几个字段，这个行变量中就有
及格字段，对这些字段的访问方式为
变量名.字段名
变量名 表%rowtype;
练习15
修改练习14，将变量的声明改为%rowtype
的方式。
*d、使用for遍历游标
语法：
--声明变量，其类型为游标中行数据的类型
for 变量名 in 游标 loop
--处理业务逻辑
end loop;
练习16：
使用for循环遍历游标


===============day02==================
昨天讲的内容都是匿名代码块，只能编译并运行一次，
不能存储在Oracle上。若想存储并复用代码，需要创建
存储过程、函数、触发器。

一、存储过程
1、什么是存储过程
封装了一段PLSQL程序，并提供参数允许输入/输出值，这样的有名称的程序称之为存储过程。
一般情况下，希望使用PLSQL对数据库进行增删改的操作时考虑使用存储过程。
如果仅仅是希望使用PLSQL进行查询的话，考虑使用函数或试图。
2、语法
1）创建存储过程
create [or replace] procedure 过程名
[(参数1 类型，参数2 类型，...)]
is|as
--声明变量区
begin
--逻辑处理区
end;
/
--类型不要指定大小。
--一般参数名和字段名不一样。
2）调用存储过程
--直接调用
call 过程名(参数1，参数2，...);
--PLSQL中调用
过程名(参数1，参数2，...);
--Java调用
后面讲
3、练习1，存储过程之HelloWorld
4、练习2，创建存储过程，传入年龄，统计工资合计
5、练习3，根据员工ID输出员工名和薪资
create or replace procedure show_pro(v_id number)
is
v_name https://www.360docs.net/doc/f413355829.html,%type;
v_salary emp.salary%type;
begin
select name,salary into v_name,v_salary from emp where id=v_id;
dbms_output.put_line(v_name||','||v_sala

ry);
end;
/
6、存储过程如何返回结果
1）存储过程使用参数来返回结果，需要在定义参数时指定参数的属性
2）语法
参数名 [in|out|in out] 类型
3）属性的含义
--in(默认)
输入参数，只能通过该参数传入内容，不能通过参数返回结果。
默认的情况下，参数的属性是in。
--out
输出参数，只能通过该参数传出内容，不能
通过参数输入内容。
--in out
既是输入参数，又是输出参数。
如：
create or replace procedure add(a number,b number,c out number)
is
begin
c:=(a+b)*(a-b);
end;
/
调用：
--可以在java中调用。
--在数据库中调用，模拟一下
create or replace procedure outPro()
is
r number;
begin
add(5,10,r);
dbms_output.put_line(r);
end;
/
7、练习4，传入2个整数，传出其合计值和差值
8、练习5，自动将临时表数据同步至service表

二、函数
1、Oracle本身预置了很多的函数，如日期函数、
字符串函数、数值函数等，当这些预置的函数无法
满足业务要求时，我们可以自定义函数来解决问题。
2、语法
create [or replace] function 函数名
(参数1 类型，参数2 类型，...)
return 类型
is
--变量声明区
begin
--业务处理区
return 值;
end;
/
3、练习6，山寨sign函数
select sign(值) from dual;
--如果值>0返回1；
--如果值=0返回0；
--如果值<0返回-1；
4、练习7，比较2个员工薪资高低

5、练习8，根据年龄计算员工薪资合计

6、函数的参数也有in、out、in out属性，其规则
与存储过程一样。

7、练习9，计算2个数字的和，并返回这两个数字

8、如何选择存储过程或函数
1）如果要进行DML操作，选择存储过程
2）如果没有DML操作，考虑选择函数

三、触发器
1、什么是触发器
是Oracle在发生某些事件时，可以自动触发并调用
的PLSQL程序。可以定义在事件上，由事件自动
触发。
2、触发器的分类
1）系统触发器
由系统事件触发的PLSQL程序，比如登录Oracle、
登出Oracle。
*2）DML触发器
由DML语句触发的PLSQL程序，如增删改。
3、DML触发器
1）语句级触发器
a、如果执行完一条DML语句后，希望对整张表
的数据进行预算，使用语句级触发器，这种
触发器是在DML执行前/后自动触发的。
b、语法
create [or replace] trigger 触发器名
before|after insert|update|delete on 表
declare
--声明变量
begin
--处理业务
end;
/
注：触发器自动调用，在DML语句执行之前
或之后。
c、练习10，在进行任何增删改操作后，计算出

员工数、员工薪资合计、员工平均工资。
2）行级触发器
a、如果在执行DML时，希望对当前操作的数据
进行处理，那么可以使用行级触发器。行级
触发器也是在执行DML之前/之后自动触发
的，在行级触发器中可以使用行变量引用到
DML所操作的数据，这个行变量是内置的，
可以直接使用。
b、行变量
--:new
表示引用的是新增后的行数据
--:old
表示引用的是修改/删除前的行数据
--规则
insert语句只有:new，表示插入后的新数据。
delete语句只有:old，表示删除前的旧数据。
update语句既有:new也有:old，其中
:new表示修改后的数据，:old表示修改
前的数据。
c、语法
create or replace trigger 触发器名
before|after insert|update|delete on 表
for each row
declare
--声明变量
begin
--处理业务
end;
/
d、练习11，删除emp之前，将删除记录存入备份表

四、异常处理
1、Oracle底层定义了很多异常，每个异常都有一个唯一
的编码。这些异常之中，有一些是比较常见的，Oracle
给这些异常定义了名称，可以直接使用，其他没有
名称只有编码的不能直接使用。
2、异常的分类
1）预定义异常
既有编码又有名称的异常是预定义异常，此类
异常可以直接使用。
2）非预定义异常
有编码但没有名称的异常是非预定义异常，此类
异常不能直接使用，要想使用需要先声明一个
异常名与编码绑定。
3）自定义异常
既没有编码又没有名称的异常是自定义异常，
此类异常需要我们自己定义。
3、异常的使用
1）预定义异常的使用
a、语法
exception
when 异常名 then
--异常处理
b、练习12，使用too_many_rows预定义异常
2）非预定义异常的使用
3）自定义异常的使用

五、Java调用存储过程




================day04====================
===========账单管理=============
1、整体需求
账单管理模块只有查询功能，查询的是月底自动计算
的用户账单信息，该信息是通过存储过程自动计算
出来。

2、详细需求
1）涉及的表
a、账单主表bill
记录的是一个用户的account账号，本月应该
收费的情况。
b、账单子表bill_item
记录的是用户的account账号下，每一个登录
UNIX服务器的service账号的本月收费情况。
2）这两张表的数据来源
a、用户登录登出Unix服务器，在登出时DMS自动
采集用户登录登出的信息，并将其存入数据库的
业务详单表service_detail。
注意：采集过程只关注时长，费用不关注，因此
采集到的数据中费用cost为null。
b

、计算详单表的费用cost
--采用触发器自动计算，在插入service_detail
数据之后自动计算。
--最终计算业务账号的费用公式是
包月1
总费用=基本费用
套餐2
总费用=套餐基本费+单价*超出时长
时长是不断累计直到超出的，因此为了
方便取累计时长，我们每次计算完都将
本次时长进行累计，存入month_duration。
计时3
总费用=单价*时长
--详单表中的费用，指的是超出时长的费用，
并非是总费用。
c、计算账单数据
--每个月月底自动调用存储过程进行计算
--计算的数据来源是service_detail
3、任务
1）写一个触发器，在插入一条service_detail后，
自动计算出这条数据的费用
2）写一个存储过程，根据service_detail中的数据，
计算出账单数据bill,bill_item
3）设置月底自动调用存储过程
a、在Oracle数据库上设置任务
b、使用开源组件quartz，开启任务线程，
实现自动调用存储过程。
4）开发账单查询功能

4、实现
1）参考PLSQL任务.sql
2）参考PLSQL任务.sql
3）使用Oracle任务实现自动调用存储过程
由于项目的存储过程比较复杂，不好直观的
看出是否执行成功。另外项目要求这个存储
过程在月底调用，频率太低不方便观察。因此
我提供一个简单的存储过程，并且设置每10s
调用一次，来演示Oracle设置任务。
a、写一个简单的存储过程，并运行
create or replace procedure proc1
is
begin
insert into employee
values(employee_seq.nextval,'aaa',2000.0);
end;
/
b、在SQL Developer中，调度任务下，将此存储
过程定义成一个程序
--右键程序，在弹出列表中点击新建程序
--在弹出框中，设置该程序的名称，并在程序
类型中选择存储过程，然后在过程下拉列表
中选择创建的proc1。
--点击应用，在程序下会出现刚才创建的一个
程序元素（对象）。
c、在SQL Developer中，调度任务下，为定义好
的程序设置触发时机
--右键作业，在弹出列表中点击新建作业
--在弹出框中，设置改作业的名称，并在作业
类型中选择指定程序，然后在程序下拉选中
选择刚才创建好的程序
--在何时执行作业中选择存储过程运行的
开始日期和结束日期，结束如期如果不写，
将无限制循环下去
--选择重复选项，在重复时间间隔上，设置
按年/月/日/周/小时/分钟/秒进行循环。