PLSQL

--变量声明
 declare 
 -- 程序体
  begin
     dbms_output.put_line('Hello World');
  end;

 

/*
if语句： 判断用户键盘输入的数字
*/
set serveroutput on
--接收键盘输入
accept num prompt '请输入一个数字:';

declare
   --通过&取输入的值
   pnum number := #
begin
  if pnum = 0 then  dbms_output.put_line('您输入的是0');
  end if;
  
  if pnum = 1 then  dbms_output.put_line('您输入的是1');
  end if;
  
end;
/

 

/*
带参数的光标: 查询并打印10号部门的员工的姓名和薪水
select ename,sal from emp where deptno=?
*/
set serveroutput on
declare
  cursor cemp10(pdno number) is select ename,sal from emp where deptno=pdno;
  pename emp.ename%type;
  psal   emp.sal%type;
begin
  open cemp10(20);

  loop
    fetch cemp10 into pename,psal;
    exit when cemp10%notfound;
    
    dbms_output.put_line(pename||'的工资是'||psal);
    
  end loop;
  close cemp10;
end;
/

 

/*
被0除
catch(***Exception ex){
    ...
}
*/
set serveroutput on
declare
   --定义数值变量，用于保存1/0结果
   pnum number;
begin
  --赋值
  pnum := 1/0;
  
exception
  when zero_divide then dbms_output.put_line('被0除');
end;
/

 

/*
给员工涨工资，按照工种涨：总裁涨1000 经理800 其他人员400
可能的sql:
select empjob,empno from emp;
update emp set sal=sal+? where empno=?
*/
set serveroutput on
declare 
  --光标保存员工的编号和工作
  cursor cemp is select empno,empjob from emp;
  --对应的变量
  pempno emp.empno%type;
  pempjob emp.empjob%type;
begin 
  open cemp;
  LOOP
    --取出一个员工
    fetch cemp into pempno,pempjob;
    --退出条件
    exit when cemp%notfound;
    --判断
    if pempjob = 'PRESIDENT' then update emp set sal=sal+1000  where empno=pempno;
      elsif pempjob= '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;
  
  --提交
  commit;
  
  dbms_output.put_line('完成');
  
end;
/

 

/*
使用游标查询员工姓名和工资，并打印
可能的sql：
select ename,sal from emp
*/
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;
/

 

/*
引用型变量和记录型变量
1. 引用型变量: 查询7839员工姓名和薪水 select ename, sal from emp where empno=7839;
2. 记录型变量: 查询7839员工所有信息  select * from emp where empno=7839;
*/

set serveroutput on
declare
  --申明引用型变量
  pename emp.ename%type; --记录姓名
  psal   emp.sal%type;--记录薪水
  --记录型变量
  emprec emp%rowtype;
begin
  --执行查询
  --select ename, sal into pename,psal from emp where empno=7839;
  
  select * into emprec from emp where empno=7839;
  
  --打印
  --dbms_output.put_line(pename||'的薪水是'||psal);
  dbms_output.put_line(emprec.ename||'的薪水是'||emprec.sal);
end;
/

 

/*
自定义例外
*/
set serveroutput on
declare
  cursor cemp(pdno number) is select ename from emp where deptno=pdno;
  --变量
  pename emp.ename%type;
  --例外
  no_emp_data exception;
begin
  open cemp(70);
  
  --取值
  fetch cemp into pename;
  
  if cemp%notfound then raise no_emp_data;
  end if; 

  close cemp;
exception
  when no_emp_data then dbms_output.put_line('没有70号部门的员工');
end;
/

 

/*
打印1~10
*/
set serveroutput on
declare 
  pnum number :=1;
begin
  loop
    --退出条件
    exit when pnum > 10;
    
    --打印
    dbms_output.put_line(pnum);
    
    pnum :=  pnum  + 1;
    
  end loop;
end;
/

  

/*
实例1：统计每年入职的员工个数。
select count(*) "Total", sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980"
from emp;

可能的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('总数' || (count80+count81+count82+count87));
  dbms_output.put_line('80年'|| count80);
  dbms_output.put_line('81年'|| count81);
  dbms_output.put_line('82年'|| count82);
  dbms_output.put_line('87年'|| count87);
end;
/

 

/*
实例2：
为员工长工资。从最低工资调起每人长10％，但工资总额不能超过6万元,
请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。
可能用到的sql:
select empno from emp order by sal;
涨工资:update emp set sal=sal*1.1 where empno=?
工资总额： select sum(sal) from emp;
*/
set serveroutput on
declare
  --查询员工，按照工资升序排列
  cursor cemp is select empno from emp order by sal;
  pempno emp.empno%type;--记录员工的编号
  
  --变量保存工资的总额
  psalTotal number;
  --计数器保存涨工资的人数
  empcount number :=0;
begin
  --初始化工资的总额
  select sum(sal) into psalTotal from emp;
  --涨工资
  open cemp;
  loop
    --第一个退出条件
    exit when psalTotal > 60000;
    --取出一个员工涨工资
    fetch cemp into pempno;
    --第二个退出条件
    exit when cemp%notfound;
    --涨工资
    update emp set sal=sal*1.1 where empno=pempno;
    --人数加一
    empcount := empcount + 1;
    
    --重新计算工资总额
     select sum(sal) into psalTotal from emp;    
  end loop;
  close cemp;
  
  --提交
  commit;
  
  --输出结果
  dbms_output.put_line('工资总额:' || psaltotal);
  dbms_output.put_line('涨工资的人数:' || empcount);
   dbms_output.put_line('-----------------------');
end;
/

 

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

可能的sql：
查询部门: select distinct deptno from dept;
取出该部门的员工: select sal from emp where deptno=?
计算该部门的工资总额: select sum(sal) from emp where deptno=?
*/
set serveroutput on
declare
  --查询部门
  cursor cdept is select distinct deptno from dept;
  pdeptno dept.deptno%type;
  
  --查询该部门中的员工
  cursor cemp(pdno number) is select sal from emp where deptno=pdno;
  psal emp.sal%type;
  
  --计数器
  count1 number;-->6000
  count2 number;--3000~6000
  count3 number;--<3000
  
  --工资总额
  psalTotal number;
begin
  --获取部门
  open cdept;
  loop
    --取一个部门
    fetch cdept into pdeptno;
    --退出
    exit when cdept%notfound;
    --初始化计数器
    count1 := 0;count2 := 0;count3 := 0;
    --得到该部门的工资总额
    select sum(sal) into psalTotal from emp where deptno=pdeptno;
    --得到该部门的员工的工资
    open cemp(pdeptno);
    loop
      --取一个员工的工资
      fetch cemp into psal;
      exit when cemp%notfound;
      
      --判断
      if psal >= 6000 then count1 := count1 +1;
        elsif psal < 6000 and psal >=3000 then count2:=count2+1;
        else count3:=count3+1;
      end if;
    end loop;
    close cemp;  
    
    --保存结果
    insert into msg1 values(pdeptno,count3,count2,count1,psalTotal);
    commit;
    
  end loop;  
  close cdept;
  
  dbms_output.put_line('完成');
end;
/