Oracle学习总结4-PL/SQL
一 PL/SQL
1.语法：
declare
begin
exception
end
2.变量类型：
char,varchar2,date,number,boolean,long,
%type：引用类型
%rowtype:记录
3.if语句
if 条件 then 语句1；
elsif 条件 then 语句2；
else 语句3
end if;
4.循环语句
loop
exit when 条件；
执行语句；
end loop;
5.游标（cursor）:用来过程化处理一条或者多条记录
定义：cursor pc is select * from emp;
定义想要接收的值：pemp emp%rowtype;
打开游标：open pc；
接收游标的值：fetch pc into pemp;
游标结束方式：exit when pc%notfound
光标的属性：%isopen %rowcount(影响的行数)
%found %notfound
6.例外
使用：
exception
when 异常名1 then 处理语句1；
when 异常名2 then 处理语句2；
系统定义的例外：
no_data_found(没有找到数据)
too_many_rows(into值时匹配到多行数据)
zero_divide(被0除)
value_error(算术或者转换错误)
timeout_on_resource(等待资源发生超时，分布式系统中会遇到)
自定义异常：
异常定义：no_emp_found exception;
调用自定义异常：if 条件 then raise 自定义异常名；
自定义异常处理：
exception
when 自定义异常名 then 处理语句；
二 PL/SQL操作
在cmd命令窗口显示：set serveroutput on
1.hello world
declare begin dbms_output.put_line('Hello world'); end;
2.--得到7839的姓名和薪水
declare pename emp.ename%type; pesal emp.sal%type; begin select ename,sal into pename,pesal from emp where empno='7839'; dbms_output.put_line(pename||'薪水是'||pesal); end;
或者
declare pemp emp%rowtype; begin select * into pemp from emp where empno='7839'; dbms_output.put_line(pemp.ename||'薪水是'||pemp.sal); end;
3.-- 判断用户从键盘输入的数字（PL/SQL中会报错，sqldeveloper不会）
accept num prompt '请输入一个数字'; declare pnum number := # begin 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;
4．输出一个1-10的循环
declare pnum number:=1; begin loop exit when pnum>10; dbms_output.put_line(pnum); pnum:=pnum+1; end loop; end;
5. -- 查询并打印员工的姓名和薪水 注意连接符||,不能用+连接
declare cursor pc is select * from emp; pemp emp%rowtype; begin open pc; loop fetch pc into pemp; exit when pc%notfound; dbms_output.put_line(pemp.ename||':'||pemp.sal); end loop; close pc; end;
6. -- 给员工涨工资，总裁1000 经理800 其他400
declare cursor pc is select empno,job from emp; pempno emp.empno%type; pjob emp.job%type; begin rollback; open pc; loop --取数据 fetch pc into pempno,pjob; exit when pc%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 pc; commit; end;
7. -- 查询某个部门的员工姓名
declare cursor pc(dno number) is select ename from emp where deptno=dno; pename emp.ename%type; begin open pc(20);--在这里传参 loop fetch pc into pename; exit when pc%notfound; dbms_output.put_line(pename); end loop; close pc; end;
8. -- 系统例外
declare i number; begin i:=1/0; exception when zero_divide then dbms_output.put_line('1:0不能做分母'); end;
9. -- 自定义例外查询50号部门的员工姓名
declare cursor pc is select ename from emp where deptno=50; pename emp.ename%type; no_emp_found exception; begin open pc; fetch pc into pename; if pc%notfound then raise no_emp_found; end if; close pc; exception when no_emp_found then dbms_output.put_line('没有记录'); when others then dbms_output.put_line('其它异常'); end;
10.瀑布模型
需求分析-概要设计-详细设计-编程-测试-上线
11．-- 1980、1981、1982、1987入职员工的个数
--思路，计数器，遍历的时候，判断年份，是哪一年就给哪一年的计数加1
--sql语句
--变量
declare cursor pc is select to_char(hiredate,'yyyy') from emp; theyear varchar2(20); count80 number:=0; count81 number:=0; count82 number:=0; count87 number:=0; begin open pc; loop fetch pc into theyear; exit when pc%notfound; if theyear='1980' then count80:=count80+1; elsif theyear='1981' then count81:=count81+1; elsif theyear='1982' then count82:=count82+1; else count87:=count87+1; end if; end loop; close pc; dbms_output.put_line(count80+count81+count82+count87||'---'||count80||'---'||count81||'---'||count82||'---'||count87); end;
12. -- 工资从低往高，每个员工涨10%工资，但所有员工总工资不能超过5万元,输出涨工资的人数和涨后的工资总额
--遍历的sql语句：select * from emp order by sal;
--退出遍历条件：1结束2总工资超过5W
--变量 遍历变量工资员工no 工资总额涨工资总人数
declare cursor pc is select empno,sal from emp order by sal; pempno emp.empno%type; psal emp.sal%type; countNum number:=0; totalSal number; begin rollback; select sum(sal) into totalSal from emp; open pc; loop exit when totalSal>50000; fetch pc into pempno,psal; exit when pc%notfound; if totalSal<50000 then if totalSal+psal*0.1<50000 then update emp set sal=sal*1.1 where empno=pempno; totalSal:=totalSal+psal*0.1; countNum:=countNum+1; end if; end if; end loop; close pc; dbms_output.put_line('涨薪人数'||countNum||'，总工资'||totalSal); commit; end;
13.
--sql：select deptno from dept
--sql: select sal from emp where deptno=dno;
--变量：count0 count3 count6 totalSal
declare cursor pc0 is select deptno from dept; cursor pc1(dno number) is select sal from emp where deptno=dno; dno dept.deptno%type; psal emp.sal%type; count0 number; count3 number; count6 number; totalSal number; begin --外循环确定遍历的部门号 open pc0; loop fetch pc0 into dno; --重置变量 count0:=0; count3:=0; count6:=0; totalSal:=0; exit when pc0%notfound; open pc1(dno); loop--内循环根据条件设置或统计值 fetch pc1 into psal; exit when pc1%notfound; totalSal:=totalSal+psal; if psal<3000 then count0:=count0+1; elsif psal<6000 then count3:=count3+1; else count6:=count6+1; end if; end loop; close pc1; --在新的一张表中，存这些数据 insert into msg values(dno,count0,count3,count6,totalSal); end loop; close pc0; commit; end;
