PLSQL程序设计(Oracle)

Hello World

set serveroutput on;

declare
  --说明部分
  begin
  --程序
  dbms_output.put_line('Hello World');
end;
/
什么是PL/SQL?
  • PL/SQL是Oracle对sql语言的过程化扩展
  • 指在SQL命令语言中增加了过程处理语句(如分子、循环等),使SQL语言具有过程处理能力
 
把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,PLSQL面向过程但比过程语言简单、高效、灵活和实用。
 
PL/SQL程序结构
declare
       说明部分   (变量说明,光标申明,例外说明)
begin
       语句序列   (DML语句)...
exception
       例外处理语句
end/

 

变量说明(char,varchar2,date,number,boolean,long)
varl                char(15); //说明变量名,数据类型和长度后用分号结束说明语句
married         boolean :=true;
psal               number(7,2);
my_name      emp.ename%type; //引用型变量,即my_name的类型与emp表中ename列的类型一样
emp_rec        emp%rowtype; //记录型变量
 
引用型变量:
--查询7839姓名和薪水
set serveroutput on 
declare
  --定义变量保存姓名和薪水
  --pname varcher2(20);
  --psal   number;
  pname emp.ename%type;
  psal  emp.sal%type;
begin 
    --into 赋值   注意查询与赋值顺序
    select ename,sal into pname,psal from emp where empno=7839;

    --打印
    dbms_output.put_line(pname||'的薪水是:'||psal);
end;
/

记录型变量

--查询7839姓名和薪水
set serveroutput on 
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语句

if 条件 then 语句;
elsif 语句 then 语句;
end if
判断用户从键盘输入的数字:

 

--接收键盘输入
set serveroutput on
--num 是一个地址值,在该地址上保存了输入的值的地址
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');
      else dbms_output.put_line('其他数字');
    end if;
end;
/

循环

loop
exit[when 条件]
...
end loop;
for i in 1..3
loop
语句序列;
end loop;

打印1~10

--打印1~10
set serveroutput on

declare
    pnum number :=1;
begin
  loop
      exit when pnum>10;
      SYS.dbms_output.put_line(pnum);
      --pnum 每次+1   不能使用++
      pnum := pnum + 1 ;
  end loop;
end;
/
光标(cursor)==resultset
说明光标语法:
cursor 光标[ (参数名 数据类型[,参数名 数据类型]...)]
    is select 语句 ;
 
用于存储一个查询返回的多行数据
    cursor c1 is select ename from emp ;
 
打开光标: open c1 ;(打开光标执行查询)
取一行光标的值: fetch c1 into pename;(取一行到变量中)
关闭光标:close c1;(关闭光标释放资源)
注意:上面的pename必须与emp表中的ename列类型一致;
--查询并打印员工的姓名和薪水
/*
  1、光标属性
    %isopen   %rowcount(影响的行数)
    %found    %notfound
*/
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;
      SYS.dbms_output.put_line(pename||'的薪水是:'||psal);
  end loop;
  close cemp;
end;
/

给员工涨薪水:总裁PRESIDENT 涨1000,经理MANAGER涨800,普通员工涨400

--给员工涨薪水:总裁PRESIDENT 涨1000,经理MANAGER涨800,普通员工涨400

set serveroutput on

declare
  cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob   emp.empjob%type;

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

end;
/

查询某个部门的员工

--查找某个部门员工
set serveroutput on

declare
  --定义光标保存某个部门的员工姓名
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pname emp.ename%type;

begin
  --光标传实参部门id为10
  open cemp(10);
  loop
    fetch cemp into pname;
    exit when cemp%notfound;

    SYS.dbms_output.put_line(pname);
  end loop;
  close cemp;

end;
/
例外:
java例外机制:向上抛,不处理,最终抛给虚拟机。
 
Oracel 的异常处理
系统定义例外
no_data_found :没有找到数据
too_many_rows :select ...into语句匹配多个行
zero_divide:算术或转换错误
timeout_on_resource:在等待资源时,发生超时
 
PLSQL中应该处理所有例外
--被0除
set serveroutput on

declare
  pnum number;

begin 
  pnum :=1/0;

exception
  when zero_divide then dbms_output.put_line('0不能被整除');
  when others then dbms_output.put_line('其他例外');
end;
/

自定义例外

--查询50号部门的员工姓名

set serveroutput on

declare
  cursor cemp is select ename from emp where deptno=50;
  pname emp.ename%type;
  --自定义例外
  no_emp_found exception;

begin
  open cemp;
  fetch cemp into pname;
  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;
/


统计每年入职的员工人数

--使用PLSQL统计每年入职的员工
/*
SQL语句:select to_char(hiredate,'yyyy') from emp
--> 光标--->循环  --->退出条件,notfound

变量:1、初始值;2、如何得到?
count80 number :=0;
....
*/

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 count81 := count82+1;
        else count87 := count87+1;
      end if;
  end loop;
  close cemp;
  --输出
  SYS.dbms_output.put_line('1980:'||count80);
  SYS.dbms_output.put_line('1981:'||count81);
  SYS.dbms_output.put_line('1982:'||count82);
  SYS.dbms_output.put_line('1987:'||count87);
end;
/

 

使用PLSQL根据部门号统计各个部门员工薪水在3000以下、3000~6000、6000以上的人数,并计算各部门工资总额。将结果存入msg表中。

 

/*
SQL语句
部门:select deptno from dept;  --> 光标
部门中员工的薪水: select sal from emp where deptno=??  --> 带参数的光标

变量:1. 初始值  2. 如何得到
每个段的人数
count1 number; count2 number; count3 number;

部门的工资总额: salTotal number;
1. select sum(sal) into salTotal from emp where deptno=??  
2. 累加
*/
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;

    --保存结果
    insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));

  end loop;
  close cdept;

  commit;
  dbms_output.put_line('完成');

end;
/

 

 
 
 
 
 
posted @ 2016-06-20 09:57 woonu 阅读(...) 评论(...) 编辑 收藏