oracle基础知识2----pl/sql基础(基本语法、光标、例外、应用)

测试数据来源：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

4).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;
/

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