数据开发－Oracle PL/SQL

--[3]// Oracle PL/SQL 编程
---------------------------------------------------------------------------------------------//
--创建错误信息表
CREATE TABLE ErrInfo
(
 ErrCode NUMBER(4) NOT NULL,
 ErrWord VARCHAR2(20),
 ErrDate VARCHAR2(20)
);

--IF...ELSIF...ELSE...END IF;--------------------------------------//
DECLARE
 var1 NUMBER:=8;
BEGIN
 IF var1=1 THEN
  DBMS_OUTPUT.PUT_LINE('This NUMBER is ' || var1);
 ELSIF var1=2 THEN
  DBMS_OUTPUT.PUT_LINE('This NUMBER is ' || var1);
 ELSE
  DBMS_OUTPUT.PUT_LINE('Unknown the number');
 END IF;
END;
/
--CASE variable WHEN expression1 THEN value1;ELSE...END CASE;------//
--%ROWTYPE是指定义一个变量和数据库中某个表的一条记录数据类型一样，它指向该表的某一条记录
DECLARE 
 rec emp%ROWTYPE;
BEGIN
 SELECT  * INTO rec FROM emp WHERE ename='SCOTT';
 CASE  rec.sal
 WHEN 3000 THEN
  DBMS_OUTPUT.PUT_LINE('More than 3000');
 WHEN  2000 THEN
  DBMS_OUTPUT.PUT_LINE('More than 2000');
 ELSE
  DBMS_OUTPUT.PUT_LINE('Less than 2000');
 END CASE ;
END;
/
--LOOP...expression...EXIT WHEN expression...END LOOP--------------//
DECLARE
 var1 NUMBER:=1;
BEGIN
 LOOP
  DBMS_OUTPUT.PUT_LINE(var1);
  var1:=var1+1;
  EXIT WHEN var1=11;  --退出条件
 END LOOP;
END;
/
--WHILE...condition...LOOP...sentence...END LOOP;------------------//
DECLARE
 var1 NUMBER:=10;
BEGIN
 WHILE var1>=1  --退出条件
 LOOP
  DBMS_OUTPUT.PUT_LINE(var1);
  var1:=var1-1;
 END LOOP;
END;
/
--FOR...variable IN 1...10 LOOP...END LOOP;------------------------//
DECLARE

BEGIN
 FOR i IN 1..10 LOOP  --此条语句如变为 FOR i IN REVERSE 1..10 LOOP ，输出将为降序
  DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
/

--GOTO Label...;...;<<Label>>...;----------------------------------//
BEGIN
 GOTO a;  --程序将在一开始跳到标签a
 FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
 END LOOP;
 <<a>>
  DBMS_OUTPUT.PUT_LINE('This is new line');
END;
/
--嵌套实现9*9乘法表------------------------------------------------//
DECLARE
 i NUMBER:=0;
BEGIN
 WHILE i<9
 LOOP
  i:=i+1;
  DECLARE
   j NUMBER:=0;
  BEGIN
   WHILE j<i
   LOOP
    j:=j+1;
    DBMS_OUTPUT.PUT(j || '*' || i || '=' || i*j || ' ');
   END LOOP;
  END;
 DBMS_OUTPUT.PUT_LINE('');
 END LOOP;
END;
/

--异常处理---------------------------------------------------------//
--预定义异常
 --NO_DATA_FOUND  没有数据满足查询要求
 --ZERO_DIVIDE  试图用0去除一个数
 --INVALID_NUMBER 在要求数据的地方使用了非数据
 --NOT_LOGGED_ON  没有连接上Oracle
 --TOO_MANY_ROWS  SELECT INTO 返回多行记录
 --VALUE_ERROR  遇到算术的、转换的、截去的或约束错误
 --CURSOR_ALREADY_OPEN 试图打开一个已经打开的游标
 --DUP_VAL_ON_INDEX 试图插入一个已经存在的唯一约束值
 --LOGIN_DENIED  要求进入系统的请求被拒绝
 --TIMEOUT_ON_RESOURCE 等待的系统时间已经超时
 --OTHERS  其它异常

--NO_DATA_FOUND 异常-----------------------------------------------//
--%TYPE是指定义一个变量和数据库中某个表的某个字段的数据类型一样
DECLARE
 v_name emp.ename%TYPE;
BEGIN
 SELECT ename INTO v_name FROM emp WHERE empno='1234';  --异常发生，empno 中没有1234
 DBMS_OUTPUT.PUT_LINE('Name is : ' || v_name);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No data found');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--ZERO_DIVIDE 异常-------------------------------------------------//
BEGIN
 DBMS_OUTPUT.PUT_LINE(5/0);  --异常发生，不能被0除
EXCEPTION
 WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('Can not divide zero');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--TOO_MANY_ROWS 异常-----------------------------------------------//
DECLARE
 no emp.empno%TYPE;
 name emp.ename%TYPE;
 dept emp.deptno%TYPE;
BEGIN
 SELECT empno,ename,deptno INTO no,name,dept FROM emp WHERE empno = 7369;
 DBMS_OUTPUT.PUT_LINE('员工号 ：' || no);
 DBMS_OUTPUT.PUT_LINE('员工名 ：' || name);
 DBMS_OUTPUT.PUT_LINE('部门号 ：' || dept);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('7369雇员不存在');  --未找到数据
 WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('有多个学员号是7369');  --返回多行
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--DUP_VAL_ON_INDEX 异常--------------------------------------------//
BEGIN
 INSERT INTO emp
 VALUES('7788','LaoZhao','Soft','7689','03-12月-81','2600','0','20');
EXCEPTION
 WHEN DUP_VAL_ON_INDEX THEN
  DBMS_OUTPUT.PUT_LINE('违反了PRIMARY KEY约束：7788雇员已经存在');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
-------------------------------------------------------------------//
--如果员工7788的工资小于3000，则更新为3000，如未找到该雇员，则利用异常处理打印相应信息
DECLARE
 salary emp.sal%TYPE;
BEGIN
 SELECT sal INTO salary FROM emp WHERE empno='7788';
 IF salary < 3000 THEN
  UPDATE emp SET sal = '3000' WHERE empno = '7788';
  DBMS_OUTPUT.PUT_LINE('记录已更新');
 END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('7788雇员不存在');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/

--用户自定义异常---------------------------------------------------//
--001
DECLARE
 v_num1 NUMBER:=0;
 v_num2 NUMBER:=5;
 MyException EXCEPTION;
BEGIN
 DBMS_OUTPUT.PUT_LINE(v_num1/v_num2); 
  RAISE MyException;  --引发自定义异常
 
 DBMS_OUTPUT.PUT_LINE('OK');  --此句永远不会执行，因为发生异常后程序将直接跳到EXCEPTION段
EXCEPTION
 WHEN MyException THEN
  DBMS_OUTPUT.PUT_LINE('Cannot divide zero');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--002
DECLARE
 id score.id%TYPE;
 java score.java%TYPE;
 MyException EXCEPTION;
BEGIN
 SELECT id,java INTO id,java FROM score WHERE id = 4;
 IF java < 0 OR java > 100 THEN
  RAISE MyException;
 ELSE
  DBMS_OUTPUT.PUT_LINE('OK');
 END IF;

EXCEPTION
 WHEN MyException THEN
  UPDATE score SET java=0 WHERE id = 4;
  DBMS_OUTPUT.PUT_LINE('分数不能大于100或小于0，现已经清0');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--003
DECLARE
 v_date emp.hiredate%TYPE;
 MyException EXCEPTION;
BEGIN
 INSERT INTO emp(empno,hiredate)
  VALUES(1111,TO_DATE('2006-12-25','yyyy-mm-dd'));
 COMMIT;
 SELECT hiredate INTO v_date FROM emp WHERE empno=1111;
 IF v_date > SYSDATE THEN
  RAISE MyException;
 END IF;
 DBMS_OUTPUT.PUT_LINE('执行完毕');
EXCEPTION
 WHEN MyException THEN
  DBMS_OUTPUT.PUT_LINE('雇用日期不能大于当前日期');
  --raise_application_error(-20002,'雇用日期不能大于当前日期');
  UPDATE emp SET hiredate = SYSDATE WHERE empno = 1111;
  DBMS_OUTPUT.PUT_LINE('日期已更新为当前日期');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/

--将定义好的异常与标准的ORACLE错误联系起来-------------------------//
--001
DECLARE
 MyException EXCEPTION;
 PRAGMA EXCEPTION_INIT(MyException,-00001);  --关联语句
BEGIN
 INSERT INTO emp(empno) VALUES(1234);  --发生异常，企图插入有Check约束的重复值
 COMMIT;  --插入后提交
 DBMS_OUTPUT.PUT_LINE('插入正常');
EXCEPTION
 WHEN MyException THEN
  DBMS_OUTPUT.PUT_LINE('ORA-00001: 违反了唯一约束');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--002
CREATE TABLE a
(
 id INT NOT NULL,
 name VARCHAR2(20) NOT NULL
);
DECLARE
 MyException EXCEPTION;
 PRAGMA EXCEPTION_INIT(MyException,-1400);  --关联语句
BEGIN
 INSERT INTO a(id) VALUES(1);  --发生异常，违反了NOT NULL约束
EXCEPTION
 WHEN MyException THEN
  DBMS_OUTPUT.PUT_LINE('错误代号：-1400');
  DBMS_OUTPUT.PUT_LINE('错误描述：违反了NOT NULL约束');
  DBMS_OUTPUT.PUT_LINE('错误发生时间：' || 
     TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
  INSERT INTO errinfo 
      VALUES('-1400','违反了 NOT NULL约束',TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
  DBMS_OUTPUT.PUT_LINE('已经将错误记录在ErrInfo表');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--003
DECLARE
 MyException EXCEPTION;
 salary emp.sal%TYPE;
BEGIN
 SELECT sal INTO salary FROM emp WHERE empno = '7788';
 IF salary > 2000 THEN
  RAISE MyException;
 END IF;
EXCEPTION
 WHEN MyException THEN
  RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR异常发生',FALSE); --TRUE
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--课后练习---------------------------------------------------------//
--001
DECLARE
 gz emp.sal%TYPE;
 MyExc EXCEPTION;
BEGIN
 SELECT sal INTO gz FROM emp WHERE ename='SCOTT';
 DBMS_OUTPUT.PUT_LINE('原工资 : ' || gz);
 IF gz < 1000 THEN
  RAISE MyExc;
 END IF;
EXCEPTION
 WHEN MyExc THEN
  UPDATE emp SET sal = 1000 WHERE ename='SCOTT';
  DBMS_OUTPUT.PUT_LINE('记录已经更新');
  SELECT sal INTO gz FROM emp WHERE ename='SCOTT';
  DBMS_OUTPUT.PUT_LINE('更新后工资 : ' || gz);
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/
--002
DECLARE
 lo salgrade.losal%TYPE;
 hi salgrade.hisal%TYPE;
 MyExc EXCEPTION;
BEGIN
 INSERT INTO salgrade VALUES(6,2600,2400);
 COMMIT;
 SELECT losal,hisal INTO lo,hi FROM salgrade WHERE grade = 6;
 IF lo > hi THEN
  RAISE MyExc;
 ELSE
  DBMS_OUTPUT.PUT_LINE('记录已经插入');
 END IF;
EXCEPTION
 WHEN MyExc THEN
  --删除插入的记录并打印相应信息
  delete salgrade WHERE grade = 6;
  DBMS_OUTPUT.PUT_LINE('记录插入失败 LOSAL 必须小于 HISAL');
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Unknown error');
END;
/

--附注-------------------------------------------------------------//
--如需在PL/SQL中进行值输入，
DECLARE
  num NUMBER(4);
BEGIN
  num := '&num';
  DBMS_OUTPUT.PUT_LINE('Input value is : ' || num);
END;
/
------------------------------------------------------------------------------------------End//