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到10declare  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  其它400declare  --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.不能除以04).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;/