测试数据来源:http://blog.csdn.net/ochangwen/article/details/51297893
PL/SQL(Procedure Language/SQL),是Oracle对sql语言的过程化扩展。
过程化扩展指在SQL命令语言中增加了过程处理语句(如分支、循环),使SQL语言具有过程处理能力。
pl/sql是面向过程的语言,操作oracle数据库效率最高
打开输出开关
set serveroutput on
一、PL/SQl基础语法
PL/SQL 程序结构
declare
说明部分(变量说明、光标申明、例外说明)
begin
语句序列(DML语句)
exception
例外处理语句
end;
/
DML、DDL、DCL的区别:http://blog.csdn.net/ochangwen/article/details/51170639
1-1.基本变量类型
基本变量:char,varchar2,date,number,boolean,long,...
declare
--定义基本变量类型
pnumber number(7,2);
pname varchar2(10);
pdate date;
begin
pnumber:=1;
pname:='Tom';
pdate:=sysdate;
dbms_output.put_line(pnumber||','||pname||','||pdate);
end;
/
1-2.引用类型变量和记录型变量
1)引用类型变量
如: my_name emp.ename%type;
declare
--引用型变量:查询并打印员工4姓名和薪水
--pename varchar2(20);
--psal number;
pename emp.ename%type;
psal emp.sal%type;
begin
--into赋值,与前面的字段要对应
select ename,sal into pename, psal from emp where empno='4';
dbms_output.put_line(pename||'的薪水是:'||psal);
end;
/
2).记录型变量
如: emp_rec emp%rowtype;
其中rowtype是行类型,也就是说记录型变量是行变量。
记录型变量分量的引用:emp_rec.ename:='Tom';
declare
--定义记录型变量:注意代表一行
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno='4';
dbms_output.put_line(emp_rec.ename||'的薪水是:'||emp_rec.sal);
end;
/
1-3.if语句的使用
1).if 条件 then语句1;...
end if;
2).if 条件 then
语句1;
else
语句2;
end if;
3).if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;
--接收一个键盘输入
--num: 地址值,含义是:在该地址上保存了输入的值
accept num prompt '请输入一个数据';
/*
判断用户从键盘输入的数字
1.如何使用if语句
2.接收一个键盘输入(字符串)
*/
declare
--定义变量,保存用户从键盘输入的数字
pnum number := #
begin
if pnum=0 then
dbms_output.put_line('您输入的数字是0');
elsif pnum=1 then
dbms_output.put_line('您输入的数字是1');
else
dbms_output.put_line('您输入的数字是:');
end if;
end;
1-4.循环语句的使用
1).while total <= 200 loop...
tatal := tatal+salary;
end loop;
--打印1到10
declare
pnum number :=1;
begin
while pnum<=10 loop
dbms_output.put_line(pnum);
--不能写pnum++
pnum := pnum+1;
end loop;
end;
2).loop
exit [when 条件];
...
end loop;
条件成立时退出循环
declare
pnum number :=1;
begin
loop
-- 退出条件
exit when pnum >10;
dbms_output.put_line(pnum);
--不能写pnum++
pnum := pnum+1;
end loop;
end;
3).for I in 1..3 loop
语句;
end loop;
declare
pnum number :=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
二、光标
光标就是一个结果集(ResultSet)下面的Pl/slq是错误的,原因是ptitle是一个变量,而查找的job是一个集合,需要使用光标
declare
ptitle varchar2(20);
begin
select job into ptitle from emp;
end;
/
--定义光标:
cursor c1 is select ename from emp;
从光标中取值
-- 打开光标
open c1;(打开光标执行查询_
-- 关闭光标
close c1;(关闭游标释放资源)
-- 取一行光标的值
fetch c1 into pename;(取一行到变量中)
实例1:
使用光标查询员工姓名和工资,并打印。
/*
1.光标的属性
%found %notfound
*/
declare
-- 定义一个光标
cursor cemp is select ename ,sal from emp;
-- 为光标定义对应的变量
pename emp.ename%type;
psal emp.sal%type;
begin
open cemp;
loop
--into后面的字段顺序一定要和定义光标的字段一样
fetch cemp into pename,psal;
-- fetch不一定能取到记录
exit when cemp%notfound;
dbms_output.put_line(pename||' 的薪水是'||psal);
end loop;
close cemp;
end;
/
实例2:
--给员工涨工资,manager 1000 cleck 800 其它400
declare
--job是关键字,可以修改成empjob
--alter table "SCOTT"."EMP" rename column "JOB" to empjob;
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='MANAGER' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='CLECK' 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;
-- 对于oracle,默认的事务隔离级别 read committed
-- 事务的ACID
commit;
dbms_output.put_line('更新完成');
end;
/
2-1.光标的属性和限制
1).光标的属性%found
%notfound
%isopen 判断光标是否打开
%rowcount 影响到行数
2).光标的限制
默认情况下,oracle数据库只允许在同一个会话中,打开300个光标。
2-3.带参数的光标
就是定义和打开光标和不带参数不一样-- 查询某个部门中员工的姓名
declare
-- 定义带参数的光标
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
--打开
open cemp(3);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
三、例外
例外是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。Oracle中的例外
3-1.系统例外
1).No_data_found(没有找到数据)declare
pename emp.ename%type;
begin
select ename into pename from emp where empno='1234';
exception
--异常最好捕获,不要往外抛
when no_data_found then dbms_output.put_line('没有找到该员工');
when others then dbms_output.put_line('其他例外');
-- 还有其他异常,在写when就行了
end;
2).Too_many_rows(select...into语句匹配多个行)
declare
pename emp.ename%type;
begin
select ename into pename from emp where deptno=3;
exception
when too_many_rows then dbms_output.put_line('select into 匹配了多行');
when others then dbms_output.put_line('其他例外');
end;
/
3).Zero_Divide(被零除)
declare
pnum number :=2 ;
begin
pnum := 1/0;
exception
when zero_divide then
dbms_output.put_line('1.不能除以0');
dbms_output.put_line('2.不能除以0');
when others then dbms_output.put_line('其他例外');
end;
/
1.不能除以0 2.不能除以0 |
declare
pnum number;
begin
pnum :='abc';
exception
when value_error then dbms_output.put_line('算术或转换错误');
when others then dbms_output.put_line('其他例外');
end;
/
5).Timeout_on_resource(在等待资源时发生超时)
如果一个数据库访问另一个数据库,如果访问网络断开,就会出现这个异常,一般是在分布式。
3_2.自定义例外
定义变量,类型是exception。并使用raise抛出自定义例外
declare
cursor cemp is select ename from emp where deptno=10;
pename emp.ename%type;
--自定义异常
no_emp_found exception;
begin
open cemp;
fetch cemp into pename;
if cemp%notfound then
--热刺异常
raise no_emp_found;
end if;
close cemp;
exception
--在控制台输出后关掉就没有记录,插入到数据库就好了。
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put_line('其他例外');
end;
/
四、案例
1).为员工涨工资。从最低工资涨起每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额,并输出涨工资人数及工资总额。
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--涨工资人数
countEmp number :=0;
totalSal number;
begin
select sum(sal) into totalSal from emp;
open cemp;
loop
exit when totalSal > 50000;
fetch cemp into pempno, psal;
exit when cemp%notfound ;
update emp set sal=sal*1.1 where empno=pempno;
countEmp := countEmp+1;
totalSal := totalSal + psal*0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('人数:'||countEmp||',涨后的工资总额:'||totalSal);
end;
/
2).涉及两张表的员工涨工资问题(但依然是单表查询)。用PL/SQL语言编写一程序,实现按部门分段(6000以上、(6000,3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)
create table msg
(deptno number,
count1 number, --3000元以下
count2 number, --(6000,3000)
count3 number, --6000以上
saltotal number);
---------------------------------------------------------
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 :=0;
count2 number :=0;
count3 number :=0;
--每个部门的工资总额
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;
--保存当前部门的结果
insert into msg values(pdeptno,count1,count2,count3,saltotal);
end loop;
close cdept;
commit;
dbms_output.put_line('统计完成');
end;
/
--------------------------------------
select * from msg;
3).用PLSQL语言编写一个程序。按系名分段统计(成绩小于60分,60-85分,85分以上)"大学物理"课程各分段的学生人数,及各系学生的平均成绩。
-- !!这个plsql没有成功执行
declare
--系的光标
cursor cdep is select dno,dname from dep;
pdno dep.dno%type;
pdname dep.dname%type;
--成绩光标
cursor cgrade(coursename varchar2,depno number)
is select grade from sc where cno=(select cno from course where cname=coursename)
and sno in(select sno from student where dno=depno);
pgrage sc.grade%type;
--每个分数段的人数
count1 number; count2 number; count3 number;
-- 每个系选修了”大学物理“学生的平均成绩
avggrade number;
-- 课程名称
pcourseName varchar2 :='大学物理';
begin
--打开光标
open cdep;
loop
fetch cdep into pdno,pdname;
exit when cdep%notfound;
count1 :=0; count2 :=0; count3 :=0; avggrade:=0;
--系的平均成绩
select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName)
and sno in(select sno from student where dno= pdno);
--打开光标
open cgrade(pcourseName,pdno);
loop
fetch cgrade into pgrade;
exit when cgrade%notfound;
if pgrade<60 then count1:=count1+1;
elsif pgrade>=60 and pgrade<85 then count2:=count2+1;
else count3:=count3+1;
end if;
avggrade:=avggrade+sgrade;
end loop;
close cdep;
insert into msg1 values(pcourseName,pdno,count1,count2,count3,avggrade);
end loop;
close cdep;
commit;
dbms_output.put_line('完成');
end;
/