oracle pl/sql基础，存储过程，函数，触发器和包

知识点：

异常：

 others捕获所有异常。

     异常分很多种类，如NO_FOUND。others处本应该写异常名称，如果不想把异常分得那麼细，可以笼统一点用others来捕获，即所有异常均用others来捕获。

raise抛出异常：

  比如下面一个订单输入的例子，若当订单小于库存数量，则抛出异常，并且捕获该异常，处理异常
　

　DECLARE　　inventory_too_low EXCEPTION;
　　　　---其他声明语句
　　BEGIN
　　IF order_rec.qty>inventory_rec.qty THEN
　　RAISE inventory_too_low;
　　END IF
　　EXCEPTION　　WHEN inventory_too_low THEN
　　order_rec.staus:='backordered';
　　END;

pl/sql实验例子：（基于oracle数据库的scott用户的emp进行的操作）

1.在屏幕上打印出来“HELLO ORACLE” 

set serveroutput on
declare
BEGIN
 DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

2 .判断两个输入的数值型变量n、m的大小（大于、小于、等于），并输出比较结果 

set serveroutput on
declare
n NUMBER(11);
m NUMBER(11);
begin
n :='&n';  --使用这种表达式让用户输入变量n和m
m :='&m';  
dbms_output.put_line('输入的n和m为:'||n||','||m);
if n >= m then
dbms_output.put_line('n>=m');
else
dbms_output.put_line('n<m');
end if;
end;
/

//解决 dbms_output.put_line的输出的中文是乱码的问题：新建环境变量：设置变量名：NLS_LANG，变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK（然后重启电脑）

3.定义一个PL/SQL代码块，计算两个整数的和与这个两个整数的差的商并输出，对除数为零的可能性要进行异常处理

 

set serveroutput on
declare
n NUMBER(11);
m NUMBER(11);
result NUMBER(11);
begin
n :='&n';  --使用这种表达式让用户输入变量n和m
m :='&m';  
dbms_output.put_line('输入的n和m为:'||n||','||m);
result := (n+m)/(n-m);
exception 
  when zero_divide then 
  dbms_output.put_line('除数不能为0');
end;
/

4

1. 根据输入的员工编号输出员工的姓名和薪水，输出格式为如下：

7369号员工姓名为：……,薪水为……

set serveroutput on
declare
id NUMBER(11);
type a is record(ename CHAR(11),sal NUMBER(11));
ntn a;
begin
id :='&id';
select ename,sal INTO ntn FROM EMP where EMPNO=id;
dbms_output.put_line('姓名和薪水为: '||ntn.ename||','||ntn.sal);
exception 
  when zero_divide then 
  dbms_output.put_line('除数不能为0');
end;
/

4.2将EMP表中员工的平均工资赋值给v_sal变量，并打印显示此变量的值

set serveroutput on
declare
type a is record(v_sal NUMBER(11));
ntn a;
begin
select "AVG"(sal) as v_sal INTO ntn FROM EMP;
dbms_output.put_line('平均薪水为: '||ntn.v_sal);
end;
/

4.3从EMP表中获取员工的工资，如果工资小于EMP表中的平均员工工资，则将此员工工资增加500。

set serveroutput on
declare
cursor sal_cursor IS
select sal from "scottview" where sal<(select "AVG"(sal) from "scottview")
for update of sal nowait;
newsal number(11);
begin
  for current_cursor in sal_cursor
  loop
  newsal:=current_cursor.sal+500;
  
   update "scottview" set sal=newsal where current of sal_cursor;
   end loop; 
end;
/
 

4.4

1. 根据雇员工资分级显示税金。(3000以上为8%，1500以上为6%，其它为4%)，并根据用户输入的员工输出该员工的税金。

set serveroutput on
declare
cursor sal_cursor IS
select sal,empno from "scottview";
newsal number(11);
begin
  for current_cursor in sal_cursor
  loop
  if current_cursor.sal>3000 then 
       newsal :=current_cursor.sal * 0.08;
  else 
    if current_cursor.sal>1500 then 
        newsal :=current_cursor.sal * 0.06;
     else
       newsal :=current_cursor.sal * 0.06;
    end if;
  end if;
  dbms_output.put_line(current_cursor.empno||'号员工的税金为: '||newsal);
   end loop; 
end;
/

 

4.5

根据员工的工资，修改scott.emp表中的奖金（comm）值，使用case语句判断comm值的修改，满足：如果sal大于1000，comm改为100，若sal大于2000，comm值为80，sal大于6000，comm改为50，其他情况下comm改为30。

DECLARE
  CURSOR mysor IS select ename,sal,empno FROM "scottview" FOR UPDATE;
  b  mysor%rowtype;
BEGIN
   OPEN mysor;
   LOOP
      fetch mysor into b;
      EXIT WHEN mysor%NOTFOUND;
      case
	when b.sal>1000 AND b.sal<=2000 THEN
	UPDATE "scottview" SET sal = 100 where EMPNO=b.empno;
        when b.sal>2000 AND b.sal<=6000 THEN
	UPDATE "scottview" SET sal = 80 where EMPNO=b.empno;
        when b.sal>6000 THEN
	UPDATE "scottview" SET sal = 50 where EMPNO=b.empno;
	else UPDATE "scottview" SET sal = 30 where EMPNO=b.empno;
  end case;
   END LOOP;
CLOSE mysor;
EXCEPTION
   WHEN others THEN
      ROLLBACK;
      dbms_output.put_line('occurs errors');
END;
/

4.6？？？？

1. 在PL/SQL中声明一个游标取出员工信息，然后在屏幕上按员工工资的升序排列出员工编号与工资，分别使用loop循环与FOR循环实现。

set serveroutput on
declare
type a is record(ename CHAR(11),
sal NUMBER(11));
ntn a;
dept NUMBER(6);


begin
dept:='&dpt';

cursor mysor IS 
select ename,sal into ntn FROM EMP where deptno=dept ORDER BY SAL DESC;
for b in mysor
loop
dbms_output.put_line('姓名和薪水为: '||b.ename||','||b.sal);
   exception 
     when no_data_found then 
     dbms_output.put_line('该部门不存在');
end loop;
end;
/

4.7带参数的游标查询

set serveroutput on
declare

cursor mysor(dept NUMBER) IS 
select ename,sal FROM EMP where deptno=dept ORDER BY SAL DESC;
b  mysor%rowtype;
begin 
open mysor(&dept);
loop
  fetch mysor into b;
  exit when mysor%notfound;
  dbms_output.put_line('姓名和薪水为: '||b.ename||','||b.sal);
end loop;
close mysor;
end;
/

4.8

使用for循环及游标修改数据：按照输入的部门编号查询emp表中的员工姓名及工资并输出，如果该员工工资低于2000，则将其工资增加10%，并输出“该员工的工资更新为：新工资值”，并最后输出“修改了工资的员工数为：***”

set serveroutput on
declare
cursor mysor(dept NUMBER) IS 
select ename,sal,empno FROM "scottview" where deptno=dept ORDER BY SAL DESC;
b  mysor%rowtype;
countnum number(11);
begin
countnum :=0; 
open mysor(&dept);
loop
  fetch mysor into b;
  exit when mysor%notfound;
  dbms_output.put_line('姓名和薪水为: '||b.ename||','||b.sal);
  IF b.sal<2000 THEN
  UPDATE "scottview" SET sal = sal*(1+0.1) where EMPNO=b.empno;
  countnum:=countnum+sql%ROWCOUNT;
  end if;
end loop;
 dbms_output.put_line('上调工资的员工数量:' || countnum);
close mysor;
end;
/

4.10：

将emp表中销售员（即job为SALESMAN）的工资上调20%，然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量

set serveroutput ON
DECLARE
BEGIN
UPDATE "scottview" SET sal = sal*(1+0.2) WHERE job='SALESMAN';
   IF sql%NOTFOUND THEN
     dbms_output.put_line('no update');
   ELSE
     dbms_output.put_line('上调工资的员工数量:' || sql%ROWCOUNT);
   END IF;
END;
/

4.9：？？？？？？

对指定员工的奖金进行增加操作，要求如下：输入员工号及给该员工进行增加奖金的数目，输出该员工的原来的奖金及增加后的奖金。如果输入员工号不存在，则输出此员工不存在，若该用户没有奖金，则不增加奖金，并输出奖金为空。

set serveroutput on
DECLARE
CURSOR emp_cursor(lid number) IS SELECT ename, sal,comm FROM scott.emp where empno=lid FOR UPDATE;
addsal number(11);
BEGIN
addsal:=('&addsal');
   FOR rec IN emp_cursor(&lid) LOOP
      if emp_cursor%notfound THEN
        dbms_output.put_line('没有该员工');
      end if;
      IF rec.comm=NULL THEN
       dbms_output.put_line('员工奖金为：null');
      END IF;
        dbms_output.put_line('员工原来奖金为：'||rec.comm);
         UPDATE "scottview" SET sal=sal+addsal WHERE CURRENT OF emp_cursor;
   END LOOP;
   COMMIT;
EXCEPTION
    WHEN others THEN
      ROLLBACK;
      dbms_output.put_line('occurs errors!');
END;