PL/SQL

投简历好多要求会存储过程、触发器的，虽然工作中没有用到过，但是学一下吧，以备不时之需。记性不好，在此记录一下，有错误的希望批评指正。

（SQL Server的扩展叫Transact-SQL）

先看一Hello World例子：用oracle的sql plus（最好以管理员身份运行）用scott/tiger用户连接上oracle，先随便执行个select，再输入ed，会打开Oracle自带的一个afiedt.buf的编辑器，这个好处就是当你的多行sql有错误时，可以直接再编辑所有的sql，编辑完ctrl+s保存，到命令窗口直接输入 / （/表示执行上一条sql或者PLSQL程序）就能执行编辑器的sql：

declare
    --变量的说明
begin
    --程序体
    --调用内置程序包，打印
    dbms_output.put_line('Hello World');
end;
/

2 如果需要在屏幕上输出信息，需要将serveroutput开关打开,，默认是off的

            Set   serveroutput   on   

3，说明变量，类型有以下

变量和常量：

说明变量（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;    -- 记录型变量，取emp表一行的类型作为类型，类似于java的类   取一行的一列：emp_rec.ename=’ADMIN’

例子：

 查询并7369的姓名和薪水：

用引用型变量实现：

declare
           --定义变量
    pname  emp.ename%type;
    psal   emp.sal%type;
begin
           --查询
    select ename,sal  into  pname,psal  from  emp  where empno=7369;
           --打印
    dbms_output.put_line(pname||' 的薪水是 '|| psal);
end;
/

用记录型变量实现：

declare
    emp_rec emp%rowtype;
begin
    select * into emp_rec from emp where empno=7369;
    dbms_output.put_line(emp_rec.ename||' 的薪水是 '|| emp_rec.sal);
end;
/

IF语句：

一：

　　IF条件  THEN 语句1；

　　语句2；

　　END IF;

二：

　　IF  条件  THEN  语句序列1；

        ELSE  语句序列2；

        END  IF；

三：　　

　　IF  条件  THEN  语句；

     　　 ELSIF  语句  THEN 语句；

     　　 ELSE  语句；

      END IF;

例子：

declare 
    pnum number:= 45;
begin
    --判断 
    if pnum <=20 then 
        dbms_output.put_line('青少年');
    elsif pnum <=30  then
        dbms_output.put_line('青年');
    elsif pnum <= 50 then
        dbms_output.put_line('中年');
    else 
        dbms_output.put_line('中老年');
    end if;
end;
/

循环语句：

 一：while循环

　　While  total <= 1000

　　LOOP

　　    Total := total + salary;

　　End loop;

二：do  while循环，至少执行一次

　　Loop

　　　　Exit[when  条件];

　　　　……

　　End  loop;

三：

　　For  I in  1..3

　　Loop

　　　　语句序列；

　　End  loop;

　　///可以这么用：循环14次

　　Fro  pename in (select  ename  from emp)

　　Loop

　　End   loop;

循环例子：--打印1-10

While循环：

--打印1-10
declare 
    pnum  number := 1;
begin
    while pnum <=10
    loop
         dbms_output.put_line(pnum);
       pnum := pnum+1;
    end loop;
end;
/

Loop循环：

declare 
    pnum  number := 1;
begin
    loop
       --条件成立退出
       exit when pnum >10;
       

       --隐式转换 put_line 参数是varchar
         dbms_output.put_line(pnum);

       pnum := pnum+1;

    end loop;
end;
/

Fro循环：

--打印1-10
declare 
    pnum  number := 1;
begin
    for pnum in 1..10
    loop
         dbms_output.put_line(pnum);
    end loop;
end;
/

光标：cursor,相当于jdbc的ResultSet

光标的属性：

　　%isopen  是否被打开 boolean

　　%rowcount行数

　　%notfound  是否有值

 光标打开不关闭会造成内存溢出，所以oracle做了限制，默认情况只能打开300个光标

例子：使用游标查询员工姓名和工资，并打印：

--光标: 使用游标查询员工姓名和工资，并打印

/*
光标的属性：
%isopen 是否被打开
%rowcount 行数
%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;
    
    dbms_output.put_line(pename||'的薪水是'||psal);

  end loop;
  close cemp;
end;
/

给员工涨工资: 总裁涨1000  经理涨800  其他人涨400：（emp2是我把emp  copy了一份）

--给员工涨工资  总裁1000 经理800 其他400

set serveroutput on
/*
SQL> show parameters cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20

*/

declare
  --光标代表员工
  cursor cemp is select empno,job from emp2;
  pempno emp2.empno%type;
  pjob   emp2.empjob%type;
begin
  --rollback;
  open cemp;
  loop
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    --判断
    if pjob = 'PRESIDENT' then update emp2 set sal=sal+1000 where empno=pempno;
      elsif pjob = 'MANAGER' then update emp2 set sal=sal+800 where empno=pempno;
      else update emp2 set sal=sal+400 where empno=pempno;
    end if;
  end loop;
  close cemp;
  --提交： 隔离级别
  commit;
  dbms_output.put_line('完成');
end;
/

总结光标书写顺序：

上边说的是不带参数的光标，下边说说带参数的光标用法：

带参数的光标：

类似于形参、实参的概念，open光标的时候传进去实参，声明的时候是形参

--带参数的光标：查询某个部门的员工姓名 

set serveroutput on

declare
  cursor cemp(pdno number) is  select ename from emp where deptno=pdno;
  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;
/

例外、异常：

Oracle的命名规则，如dbms_output.put_line,单词之间使用下划线分割，变量使用v_name,  游标可以使用c_emp, 等等

例子：除数是0：

/*
zero_divide  被零除
*/
declare
    v_num number;
begin 
    v_num := 1/0;
exception
    --then 后可以有多条语句
    when  zero_divide  then dbms_output.put_line('1:除数不能是零');
                dbms_output.put_line('2:除数不能是零');
    when  others  then dbms_output.put_line('其他例外');
end;
/

自定义例外：

 把自定义例外当做变量，在declare里声明，抛出用raise

注意：oracle 会自动关闭光标，为保险起见，手动关闭更好，在catch到异常后，判断光标是否关闭，这就用到了游标的 %isopen属性：

if cemp%isopen then

    close no_emp_found;

  end if;

 例子：自定义例外： 查询50号部门的员工姓名

--自定义例外： 查询50号部门的员工姓名

set serveroutput on

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;

/*
  if cemp%isopen then 
    close 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：统计每年入职的员工个数。

/*
实例1：统计每年入职的员工个数。

可能SQL：
select to_char(hiredate,'yyyy') from emp;
*/
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;
/

 实例2：

为员工长工资。从最低工资调起每人长10％，但工资总额不能超过5万元,
请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

/*
为员工长工资。从最低工资调起每人长10％，但工资总额不能超过5万元,
请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

可能的SQL:
员工:  select empno,sal from emp order by sal;
长工资后的工资总额:1. 对sal进行累加: 新的工资总额=旧的工资 + sal*0.1;
                2. sum(sal): 查询数据库
练习： 工资不能超过5w
*/
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;
--涨工资
    update emp set sal=sal*1.1 where empno=pempno;
    exit when salTotal > 50000;
    --人数
    countEmp := countEmp +1;
    --工资总额
    salTotal := salTotal + psal * 0.1;
  end loop;
  close cemp;
  
  commit;

  --输出
  dbms_output.put_line('长工资的人数:'|| countEmp);
  dbms_output.put_line('工资总额:'|| salTotal);

end;
/

实例3：操作2张表

分析，先从deptno表取出有哪些deptno，再从emp表根据deptno取每个部门的信息，两层循环。

/*
用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)
统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)

SQL语句：
部门:  select deptno from dept;
员工的工资: select sal from emp where deptno=???
工资总额:  select sum(sal) from emp where deptno=???  
*/
set serveroutput on
declare
  --部门 
  cursor cdept is select deptno from dept;
  pdno 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 pdno;
    exit when cdept%notfound;
    
    --初始化
    count1 :=0;count2:=0;count3:=0;
    select sum(sal) into salTotal  from emp where deptno=pdno;
    
    --取部门中的员工
    open cemp(pdno);
    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 msg1 values(pdno,count1,count2,count3,nvl(salTotal,0));
    
  end loop;
  close cdept;
  
  commit;
  dbms_output.put_line('完成');



end;
/

create table msg1
(deptno number,
emp_num1 number,
emp_num2 number,
emp_num3 number,
sum_sal number);
create tablel msg1