Oracle 开发 - 3

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

--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);
IF i=5 THEN
GOTO a;
END IF;
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('记录已更新');
ELSE
DBMS_OUTPUT.PUT_LINE('IF 条件不成立');
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:=5;
v_num1 NUMBER:=0;
--v_num2 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 ZERO_DIVIDE THEN
-- DBMS_OUTPUT.PUT_LINE('Can not 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('2010-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 < 2000 THEN
RAISE MyExc;
ELSE
DBMS_OUTPUT.PUT_LINE('工资无需更新');
END IF;
EXCEPTION
WHEN MyExc THEN
UPDATE emp SET sal = 3000 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 LONG;
BEGIN
num := '&num';
DBMS_OUTPUT.PUT_LINE('Input value is : ' || num);
END;
/
--注意：如果要使你在DBMS_OUTPUT.PUT_LINE(...)中显示的语句得以显示，则必须显示的调用
SET SERVEROUTPUT ON;
----------------------------------------------------------------------------------End//


[/code]