oracle:oracle学习笔记(四)循环、光标、异常、瀑布模型

oracle学习笔记：循环、光标、异常

---

打印Hello World

• 第一案例：打印Hello World

declare
	--(没有声明可以不写)
	--说明变量(变量声明，光标)
begin
	--程序体
  	dbms_output.put_line('Hello World');
end;
--（/表示执行）
/  

--查询输出函数
desc dbms_output

• PLSQL是对SQL的扩展

定义基本变量

类型：char，varchr2,date,number,boolean,long
举例：

var1 char(15) --（名字+类型）
married boolean:=true -- ":="表示赋值
psal number(7,2)

DECLARE
--定义基本变量类型
--基本变量类型
pnumber NUMBER(7,2);
--字符串变量
pname VARCHAR2(20);
--日期变量
pdate DATE;
BEGIN
  pnumber:=1;
  pname:='TOM';
  pdate:=SYSDATE;
  DBMS_OUTPUT.put_line(pnumber);
END;
/
  

引用型变量(单行)my_name emp.ename%type

• 案例

 declare
 --定义引用型变量
 --pename varchar2(20)
 --psal number(20)
 pename emp.ename%type;
 psal emp.sal%type;
 begin
 --得到7782的姓名和薪水
 select ename,sal into pename,psal from emp 
 where empno=7839;
 --打印
 dbms_output.put_line(pename);
 dbms_output.put_line(psal);
 end;
 /

记录型变量(多行) emp_rec emp%rowtype

记录型变量分量的赋值，emp_rec.ename:=‘ADAMS’；

declare
--定义记录型变量
emp_rec emp%rowtype;
begin
--7839
select * into emp_rec from emp where empno=7839;
--打印
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/

面向过程语句

IF语句

1. IF 条件 then 语句1；语句2；end if;
2. IF 条件 then 语句序列1；else 语句序列2；end if;
3. IF 条件 then 语句序列1；elsif 语句 then 语句；else 语句end if;

案例

案例：判断用户从键盘输入的数字
1.如何使用if 语句
2.接收一个键盘输入(都是字符串)

---

注：
在PLSQL中出现ORA_0131错误是：grant debug connect session to scott;

---

--打开输入开关
set serveroutput on 
--接受键盘输入
--num是一个地址值，含义是在该地址上保存了输入的值
--prompt表示提示
accept num prompt'请输入一个数字';

declare
	-- 定义变量保存用户从键盘输入的数字
	-- 地址
	pnum number:= #
BEGIN
	--执行if语句
	dbms_output.put_line(pnum);
	if pnum=0 then dbms_output.put_line('您输入的数字是0');
		elsif pnum=1 then dbms_output.put_line('您输入的数字是1');
		elsif pnum=2 then dbms_output.put_line('您输入的数字是2');
		else dbms_output.put_line('您输入的数字是其它');
	end if;
end;
/

循环语句

语法：while total <=25000 LOOP 循环体 end LOOP;

案例：

--使用while循环打印数字1~10
declare
	pnum number:=1;
begin
	while pnum<=10 loop
	dbms_output.put_line(pnum);
	--使变量+1
	pnum:=pnum+1;
	end loop;
end;
/

LOOP循环

语法：LOOP exit [when 条件]； .... End LOOP;

案例

--使用loop循环
declare
pnum number:=1;
begin
loop
--退出条件：循环变量大于10
exit when pnum>10;
--打印该变量
dbms_output.put_line(pnum);
--循环变量+1
pnum:=pnum+1;
end loop;
end;
/

For循环

语法：..表示连续区间 For I in 1..3 LOOP end LOOP;

案例：

--使用for循环打印1-10
declare
--定义循环变量
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
/

推荐使用第二种循环(LOOP)，便于控制光标！！！

光标

光标(游标)：产生一个结果集(result set)

基础语法

语法cursor 光标名【（参数名 数据类型）】(可以带参数，也可以不带参数) Is select 语句；

简单案例：

cursor c1 is select ename from emp;(集合)
--打开光标
-open c1;（打开光标执行查询）
-close c1;(关闭游标释放资源)
fetch c1 into pename；(取一行到变量中)

***案例：使用光标查询员工姓名和工资，并打印

光标的属性

标记	表示
%found	fetch可以取到为True,否则False;
%notfound	与上面刚好相反
%isopen;	判断光标是否打开
%rowcount;	影响的行数

光标数的限制

默认情dao况下只允许在同一个会话中，打开300个光标

--切换到管理员
show parameter cursor
--修改光标数
alter system set open_cursors=400 scope=both;
scope取值：both、memory(值更改实例)，spflie(只更改当前参数文件)

案例

--查询打印员工姓名和工资
set serveroutput on
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;
		--思考：1循环什么时候退出？2.fetch语句不一定能取到？没有取到记录
		exit when cemp%notfound;
		--打印
		dbms_output.put_line(pename||'--------------的薪水是------------>>>'||psal);
	end loop;
	--关闭光标
	close cemp;
end;
/

案例：给员工涨工资

--给员工涨工资，总裁1000，经理800，其它400
set serveroutput on
declare
--定义光标代表哪些员工涨工资
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
	rollback;
	--打开光标
	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;
	--对于oracle,默认的事务隔离级别是read committed
	--事务的ACID,性质：原子性、一致性、隔离性、持久性
	--提交
	commit;
	dbms_output.put_line('涨工资完成!');
end;
/
--再次查询
select ename,sal from emp;

案例：判断光标是否打开

declare
--定义光表
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
	--打开光标
	open cemp;
	if cemp%isopen then 
	dbms_output.put_line('光标已经打开');
	else
	dbms_output.put_line('光标没有打开');
	end if;
	close cemp;
end;
/

案例：光标的属性%rowcount

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;
--打印
dbms_output.put_line('rowcount'||cemp%rowcount);
end loop;
close cemp;
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;
/

例外

定义：程序设计语言提供的一种功能，用来增强程序的容错性。

系统例外

符号	描述
No_data_found	没有找到数据
Too_many_rows	select…into语句匹配到多个行
Zero_Divide	被零除
Value_error	算术或转换错误
Timeout_on_resource	在等待资源时发生超时

案例：No_data_found

declare
pename emp.ename%type;
begin
--查询员工号为123456的员工姓名
select ename into pename from emp where empno=123456;
exception
when no_data_found then dbms_output.put_line('没有找到该员工');
when others then dbms_output.put_line('其它例外');
end;
/

案例：too_many_rows

set serveroutput on 
declare
  --定义变量
  pename emp.ename%type;
begin
--查询所有10号部门的员工姓名
select ename into pename from emp where deptno=10;
exception
when too_many_rows then dbms_output.put_line('select into 匹配了多个行');
when others then dbms_output.put_line('其它问题');
end;
/

案例：zero_divide

declare
--定义一个变量
pnum number;
begin
pnum:=1/0;
exception
when zero_divide then dbms_output.put_line('1被零除');
					dbms_output.put_line('2被零除');
when others then dbms_output.put_line('其它问题');
end;
/

案例：value_error

declare
--定义一个变量
pnum number;
begin
pnum:='abc';
exception
when value_error then dbms_output.put_line('算数或数字转换错误');
when others then dbms_output.put_line('其他例外');
end;
/

自定义例外

定义变量。类型是exception，使用raise抛出自定义例外

自定义例外；查询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 no_emp_found;
end if;

--关闭光标
--oracle 自动启动pmon(process monitor )

close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put('其它例外');
end;
/

程序设计

瀑布模型需求分析-->设计(概要、详细)-->编码-->测试-->测试

SQL语句
变量：
初始值；如何得到

案例：统计每一年入职的员工人数

/*
select to_char(hiredate,'yyyy') from emp;
-->光标-->循环--》退出条件：notfound

count80 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;
/

案例：为员工涨工资

案例：为员工涨工资。从最低工资涨起每人涨10%，但工资
总额不能超过5万元，请计算装工资的人数和涨工资后的工资
总额，并输出涨工资的人数及工资总额。

/*
分析：
select empno，sal from emp order by sal ; 排序
从工资最低的涨起
-->光标-->循环-->退出条件：1.工资总额>5W;2.%notfound
变量：
1.初始值；2.如何得到
涨工资的人数
countEmp number:=0
涨后的工资总额
1.salTotal number;(select sum(sal) from emp)
2.涨后的工资总额=涨前的工资总额+sal*0.1
原则：尽量不操作数据库
*/

set serveroutput on 
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.工资总额大于50000
exit when salTotal>50000;
fetch cemp into pempno,psal;
exit when cemp%notfound;
--取一个员工的涨工资
--涨工资
update emp set sal=psal*1.1 where empno=pempno;
--人数+1
countEmp:=countEmp+1;
--2.涨后的工资总额=涨前的工资总额+sal*0.1
salTotal:=salTotal+psal*0.1;
end loop;
--关闭光标
close cemp;
commit;
--打印结果
dbms_output.put_line('涨工资的人数'||countEmp||'涨后的工资总额：'||salTotal);
end;
/

分部门统计工资

案例3：
实现按部门分段(6000以上、(6000,3000)，3000元以下)统计各工资段的员工人数；以及各部门的工资总额(不包括奖金)

--创建空表
create table msg(
deptno number，
count1 number，
count2 number，
count3 number，
saltotal number
);


/*
分析：
SQL语句：
有哪些部门？
select deptno from dept;-->光标-->循环-->推出条件;notfpound
2.
统计部门中员工的薪水
select sal from where deptno=? --》光标(参数)--循环--退出条件：1

变量：
1.初始值；2 如何得到？
每个段的员工人数
count1 number;
count number;
count3 number;
每个部门的工资总额：
salTotal number;
1 . select sum(sal) into saltotal from emp where deptno=???;
2.累加 #第2种的效果要好于第1种
*/

set serveroutput on
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;
		--保存当前部门的结果
		--nvl 的作用是当salTotal为空时，赋值为0
		insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
	end loop;
	--提交
	dbms_output.put_line('统计完成！');
	close cdept;
end;
/