Oracle-PL/SQL

介绍

1. PL:Process Language

2. PL/SQL是oracle对sql语言的过程化扩展

3. 在SQL语言中添加了过程处理语句（如分支，循环），使SQL语言具有过程处理能力

程序结构

Declare
说明部分（变量、光标、例外声明）
Begin
语句序列(DML)...
Exception
例外处理语句

declare 
-- Local variables here
i integer;
begin
-- Test statements here
DBMS_OUTPUT.PUT_LINE('hello word');
end;

打开输出

set serveroutput on

变量声明

可以声明的变量类型char,varchar2,boolean,date,number,long

说明变量

char,varchar2,boolean,date,number,long
var1       char(15)
married    boolean :=true
psal        number(7,2) 

引用变量

引用表中的字段类型，用into赋值

--查找7839的员工信息
--打开输出
set serveroutput on
declare
pename emp.ename%type;--名字变量
psal emp.sal%type;--薪水变量
begin
select ename,sal into pename,psal from emp where empno=7839;
--输出
DBMS_OUTPUT.PUT_LINE(pename||'的薪水是'||psal);
end;

记录型变量

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

if语句

declare
  --定义记录型变量：代表一行
  emp_rec emp%rowtype;
  pnum NUMBER :=8;
begin
  dbms_output.put_line(pnum);
  IF  pnum=2 THEN dbms_output.put_line('你输入的是0');
  ELSIF pnum = 1 then dbms_output.put_line('你输入的是1');
  ELSE dbms_output.put_line('其它数字');
  END IF;
end;

循环

语法

declare 
-- 定义变量
pnum number := 1;
begin
loop
--退休条件
exit when pnum > 10;
--打印
dbms_output.put_line(pnum);
--加一
pnum := pnum + 1;
end loop;
end;

光标

• 光标（cursor）也被称为游标。Oracle 使用两种光标：显示光标和隐式光标。
• 不管语句返回多少条纪录，PL/SQL 为使用的每一条 UPDATE 、 DELETE 和 INSERT 等 SQL 命令隐式的声明一个光标。

光标的作用

• 当 PL/SQL 光标查询返回多行数据时，这些记录组被称为活动集。 Oracle 将这种活动集存储在您创建的显示定义的已命名的光标中。Oracle 光标是一种用于轻松的处理多行数据的机制，没有光标， Oracle 开发人员必须单独地、显示地取回并管理光标查询选择的每一条记录。
• 光标的另一项功能事，它包含一个跟踪当前访问的记录的指针，这使您的程序能够一次处理多条记录。

配置

默认，一个会话中只能打开300个光标
show parameter cursor【只能系统管理员可查看】
alter system set open_cursors=400; 【修改】 
遍历员工薪水
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;
    exit when cemp%notfound;
    dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  --关闭光标
  close cemp;
end; 

涨工资

总裁1000 经理800 其他400

-- 给员工涨工资，总裁1000 经理800 其他400
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;

--提交  ----> why?: 事务 ACID
commit;

dbms_output.put_line('完成');
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;

异常

 系统例外

1. No_data_found【没有找到数据】

2. Too_many_row【select ...into语句匹配多个行】

3. Zero_Divide【被零除】

4. Value_error 【算术或转换错误】

5. Timeout_on_resource【在等待资源时发生超时】

系统异常

declare
pnum number;
begin
pnum := 1/0;

exception

when zero_divide then dbms_output.put_line('1:0不能做分母');

dbms_output.put_line('2:0不能做分母');

when value_error then dbms_output.put_line('算术或者转换错误'); 

when others then dbms_output.put_line('其他例外');

end;

自定义异常

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;

--进程：pmon进程(proccesss monitor)
close cemp;

exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put_line('其他例外');
end;

案例

统计每年入职的员工数

set serveroutput on
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%，但总额不能超过5W，请计算涨工资的人数和长工资后的工资总额

1.为员工涨工资。从最低工资起，每人涨10%，

2.但总额不能超过5W，请计算涨工资的人数和长工资后的工资总额

1.声明变量

涨工资的人数 countEmp number:0;

涨工资的总额 salTotal number;

[select sum(sal) into salTotal from emp];

2.得到初始的工资总和

3.光标遍历

4.如果总额大于5000就不加工资

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
    --大于5W不在加工资
    exit when salTotal > 50000;
    fetch cemp into pempno,psal;
    --没有光标，无下条记录
    exit when cemp%notfound;
    --涨工资
    update emp set sal = sal*1.1 where empno=pempno;
    
    --涨工资人数
    countEmp :=countEmp+1;
    --涨后金额
    salTotal :=salTotal + psal*0.1; 
  end loop;
  close cemp;
  commit;--提交事务
  DBMS_OUTPUT.PUT_LINE('涨工资人数'||countEmp);
  DBMS_OUTPUT.PUT_LINE('涨后工资总额'||salTotal);
end;

实现按部门分段(6000以上),(6000,3000),3000元以下统计各工资段的职工人数，以及各部门的工资总和

1.部门 遍历打印部门编号

2.部门员工遍历,薪水

3.声明三个区间的变量

4.声明部门总工资变量

set serveroutput on
declare
  --部门光标
  cursor cdept is select deptno from dept;
  --部门ID变量
  pdeptno dept.deptno%type;
  
  --员工光标
  cursor cemp(dNo number) is select sal from emp where deptno=dNo;
  psal emp.sal%type;
  
  --每个区间的员工个数
  count1 number;-->6000
  count2 number;--<3000
  count3 number;--3000~600
  
  totalSal number;--部门上总工资
begin
  --打开光标
  open cdept;
  --1.遍历部门
  loop
    count1:=0;count2:=0;count3:=0;
    
    fetch cdept into pdeptno;
    exit when cdept%notfound;
    
    select sum(sal) into totalSal from emp where deptno = pdeptno;
    --打印部门编号
    DBMS_OUTPUT.PUT_LINE ('部门编号'||pdeptno ||'总工资'||nvl(totalSal,0));
    
    --2.根据部门编号遍历员工
    open cemp(pdeptno);
    loop
      fetch cemp into psal;
      exit when cemp%notfound;
      --打印工资
      --DBMS_OUTPUT.PUT_LINE ('--'||psal);
      --3.遍历员工工资区间
      if psal>6000 then count1 :=count1+1;
      elsif psal<3000 then count2 :=count2+1;
      else count3 :=count3+1;
      end if;
    end loop;
    close cemp;
   
    --打印工资区间人数
    DBMS_OUTPUT.PUT_LINE ('工资>6000:'||count1||' 工资<3000:'||count2||' 工资3000~6000:'||count3);
    DBMS_OUTPUT.PUT_LINE ('=========================================');
  --结束循环  
  end loop;
  --关闭光标
  close cdept;
end;