#####pl/sql#####
目的：1.通过后面的学习了解到pl/sql是oracle的扩展，执行速度比jdbc快
2.为了学习存储过程和触发器做准备。
优点：支持sql，支持面向过程编程，更好的性能，可移植性，安全性
##1.2pl/sql块
pl/sql是一种块结构的语言，，面向过程，一个pl/sql程序包含了一个或者多个逻辑快，逻辑块中
可以声明变量，变量在使用之前必须先声明，出来正常的执行程序外，pl/sql还提供了专门
的异常处理部分进行异常处理，每个逻辑快分为三个部分，语法是：
declare
声明部分；
begin
执行部分；
exception
处理异常部分(可选)；
end;
##1.3pl/sql中特殊符号说明
类型 符号 说明
赋值运算符 := Java 和 C#中都是等号，PL/SQL 的赋值是：=
特殊字符
|| 字符串连接操作符。
-- PL/SQL 中的单行注释。
/*,*/ PL/SQL 中的多行注释，多行注释不能嵌套。
<<,>> 标签分隔符。只为了标识程序特殊位置。
.. 范围操作符，比如：1..5 标识从１到５
算术运算符
+，-，*，/ 基本算术运算符。
** 求幂操作，比如：3**2=9
关系运算符
>，<,>=,<=,= 基本关系运算符，=表示相等关系，不是赋值。
<>,!= 不等关系。
逻辑运算符 AND,OR,NOT 逻辑运算符。
例如：
sql>declare
2 sname varchar2(32):='joy';
3 begin
4 sname:=sname||'and tom';
5 dbms_output.put_line(sname); -- 控制台输出语句（package调用程序包）
6 end;
7 /
set serveroutput on -- 设置输出到sqlplus上
sql>declare
pi constant number:=3.14; -- constant 声明常量
r number default 3;
area number;
begin
area:=pi*r*r;
dbms_output.put_line(area);
end;
变量和常量的说明：
var1 char(15);
married boolean:=true;
psal number(7,2);
my_name emp.ename%type; 将emp.ename类型作为my_name的类型
将查询的结果赋值到变量中使用into
例如：declare
pename emp.ename%type;
psal emp.sal%type;
begin
select ename,sal into pename, psal from emp where empno=7369;
dbms_output.put_line(pename);
end;
emp_rec emp%rowtype; 记录型变量，将表中一行的类型作为类型，可以当作数组
记录型变量
例如：declare
emp_r emp%rowtype;
begin
select * into emp_r from emp where empno=7839;
dbms_output.put_line(emp_r.ename);
end;
常量是在类型前加：constant 关键字 例如：pi constant number(5,3) := 3.14;
另外：accept num prompt '请输入一个数字:';在输入框中输入数据
SQL> accept num prompt '请输入一个数字:'
请输入一个数字:12
SQL> declare
2 pnum number := #
3 begin
4 dbms_output.put_line(pnum);
5 end;
6 /
原值 2: pnum number := #
新值 2: pnum number := 12;
PL/SQL 过程已成功完成。 IF语句：
if pnum = 0 then dbms_output.put_line('0');
elsif pnum=1 then dbms_output.put_line('1');
elsif pnum=2 and pnum=3 then dbms_output.put_line('2');
else dbms_output.put_line('3333');
end if;
循环语句：
例如：
declare
pnum number:=1;
begin
loop --开始循环
exit when pnum>10; --退出条件
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop; --结束循环
end;
游标：使用光标作为集合
例如： /*
光标的属性 ：
%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 cemp%notfound; --没有取到记录
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close cemp; --关闭光标
end;
例如：
-- 根据 级别涨工资
select * from emp;
declare
cursor pemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
open pemp;
loop
fetch pemp into pempno,pjob;
exit when pemp%notfound;
if pjob ='president' then update emp set sal=sal+100 where empno=pempno;
elsif pjob='manager' then update emp set sal=sal+50 where empno=pempno;
else update emp set sal=sal+10 where empno=pempno;
end if;
end loop;
close pemp;
commit;--提交
dbms_output.put_line('完成');
end;
-- 带参数的光标
declare
pename emp.ename%type;
pempno emp.empno%type;
cursor pemp(pid emp.deptno%type) is select ename,empno from emp where deptno=pid;
begin
open pemp(10);
loop
fetch pemp into pename,pempno;
exit when pemp%notfound;
dbms_output.put_line(pename);
end loop;
close pemp;
dbms_output.put_line('完成');
end;
异常处理
异常类型
1.No_data_found(没有找到数据)
2.Too_many_rows
3.zero_divide(被零除)
4.value_erro(算术或转换错误)
5.timeout_on_resource(在等待资源时发生超时)
例如：declare
pnum number;
begin
pnum:=1/0;
exception
when zero_divide then dbms_output.put_line('0不能整除');
when value_error then dbms_output.put_line('2'); --捕捉所有的异常
when others then dbms_output.put_line('3');
end;
自定义异常
declare
no_data exception; --自定义异常
my_job char(10);
v_sal emp.sal%type;
cursor c1 is select distinct job from emp where deptno=50;
begin
open c1 ;
fetch c1 into my_job; --取出第一条数据
if c1%notfound then
-- 抛出异常
raise no_data;
end if;
--pmon进程 自动启动清理缓存，释放资源
close c1;
exception
when no_data then dbms_output.put_line('没有找到');
when others then dbms_output.put_line('333');
end;
pl/sql的扩展例1：
declare
cursor pyear is select to_char(hiredate,'yyyy') from emp;
phiredate varchar2(24);
count1 number:=0;
count2 number:=0;
count3 number:=0;
count4 number:=0;
count5 number:=0;
begin
open pyear;
loop
fetch pyear into phiredate;
exit when pyear%notfound;
if phiredate ='1980' then count1:=count1+1;
elsif phiredate='1981' then count2:=count2+1;
elsif phiredate='1982'then count3:=count3+1;
else count4 := count4+1;
end if;
end loop;
close pyear;
dbms_output.put_line(count1);
dbms_output.put_line(count2);
dbms_output.put_line(count3);
dbms_output.put_line(count4);
end;
例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;
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;
--保存结果
dbms_output.put_line('pdeptno:'||pdeptno||',count1:'||count1||',count2"'||count2||',count3:'||count3);
--insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
end loop;
close cdept;
commit;
dbms_output.put_line('完成');
end;
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
