Oracle 开发 - 3 博客分类： 开发 OracleSQL编程

--[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//