oracle 3
来源:互联网 发布:乐视2用不了移动数据 编辑:程序博客网 时间:2024/06/06 04:49
- --[3]
- -------------------------------------------------------------------------------------
- --创建错误信息表
- 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