异常、游标、触发器作业

/*1、异常
获取系统预定义的异常

编写 PL/SQL，查询并打印
员工工资最大的
那个员工信息，包括：
员工编号，姓名，职位，上级经理编号，薪水，雇用日期，部门编号。
要注意有可能发生的异常，并做相应的异常处理。*/

declare
V_EMPNO NUMBER(10);
V_ENAME VARCHAR2(10);
V_JOB VARCHAR2(10);
V_MGR NUMBER(10);
V_SAL NUMBER(10);
V_HIREDATE DATE；
V_DEPTNO NUMBER(10);
begin
select empno,ename,job,mgr,sal,hiredate,deptno into
v_empno, v_ename,v_job,v_mgr,v_sal,v_hiredate,v_deptno
from emp
where sal= (select max(sal) from emp;
dbms_output.put_line(
v_empno||’ ‘|| v_ename||’ ‘||v_job||’ ‘||v_mgr||’ ‘||v_sal||’ ‘||v_hiredate||’ '||v_deptno );
exception
when too_many_rows then dbms_output.put_line(‘返回数据行太多！’);
when no_data_found then dbms_output.put_line(‘没有数据！’);
when INVALID_NUMBER then dbms_output.put_line(‘非法数字！’);
end;

/*2、游标3-5 ，分别使用三种循环完成

(3)编写 PL/SQL，查询出经理的名称，然后查询出经理下员工的个数*/
DECLARE
CURSOR MGR_NUM IS
SELECT T2.ENAME,COUNT(*) AS NUM
FROM EMP T1 , --员工 确定每个员工的经理编号 T1.MGR
EMP T2 --经理
WHERE T1.MGR = T2.EMPNO
GROUP BY T1.MGR ,T2.ENAME ;
V_ENAME VARCHAR2(10);
V_NUM NUMBER(10);
BEGIN
OPEN MGR_NUM;
LOOP
FETCH MGR_NUM INTO V_ENAME ,V_NUM ;
EXIT WHEN MGR_NUM%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ENAME||‘员工个数’||V_NUM);
END LOOP;
CLOSE MGR_NUM;
END ;

(4)
DECLAR
CURSOR EMP_INFO IS
SELECT T1.EMPNO,T1.ENAME ,T1.DEPTNO , T2.DNAME ,
T1.MGR , T3.ENAME ,T3.DEPTNO , T4.DNAME
FROM EMP T1
LEFT JOIN EMP T3 ON T1.MGR = T3.EMPNO
LEFT JOIN DEPT T4 ON T3.DEPTNO = T4.DEPTNO
,DEPT T2
WHERE T1.DEPTNO = T2.DEPTNO ;
V_EMPNO NUMBER(10);
V_ENAME VARCHAR2(10);
V_DEPTNO NUMBER(10);
V_DNAME VARCHAR2(10);
M_EMPNO NUMBER(10);
M_ENAME VARCHAR2(10);
M_DEPTNO NUMBER(10);
M_DNAME VARCHAR2(10);
BEGIN
OPEN EMP_INFO;
FETCH EMP_INFO INTO V_EMPNO,V_ENAME,V_DEPTNO,V_DNAME,M_EMPNO,M_ENAME,M_DEPTNO,M_DNAME;
WHILE EMP_INFO%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(V_EMPNO||’ ‘||V_ENAME||’ ‘||V_DEPTNO||’ ‘||V_DNAME||’ ‘||M_EMPNO||’ ‘||M_ENAME||’ ‘||M_DEPTNO||’ '||M_DNAME);
FETCH EMP_INFO INTO V_EMPNO,V_ENAME,V_DEPTNO,V_DNAME,M_EMPNO,M_ENAME,M_DEPTNO,M_DNAME;
END LOOP;
CLOSE EMP_INFO;
END;

(5)
DECLARE
CURSOR EMP_INFO IS SELECT * FROM EMP ;
BEGIN
FOR X IN EMP_INFO LOOP
IF X.SAL <= 1000 THEN
UPDATE EMP SET SAL = SAL * 1.5 WHERE EMPNO = X.EMPNO ;
ELSIF X.SAL >1000 AND X.SAL <= 2000 THEN
UPDATE EMP SET SAL = SAL * 1.3 WHERE EMPNO = X.EMPNO ;
ELSIF X.SAL >2000 AND X.SAL <= 3000 THEN
UPDATE EMP SET SAL = SAL * 1.1 WHERE EMPNO = X.EMPNO ;
END IF ;
COMMIT;
END LOOP;
END ;

/*3、触发器 */

CREATE TABLE USERS
(
USERID NUMBER(10);
USERNAME VARCHAR2(10)
);

CREATE TABLE USER_ACCOUNT
(
USERID NUMBER(10);
ACCOUNTID NUMBER(10);
ACCOUNT_BAL NUMBER(10) DEFAULT 0
);

CREATE SEQUENCE USERACCOUNT_ID
;

CREATE OR REPLACE TRIGGER TR_INSER_USERACCOUNT
AFTER
INSERT
ON USERS
FOR EACH ROW
BEGIN
INSERT INTO USER_ACCOUNT
(
USERID,
ACCOUNTID,
ACCOUNT_BAL
)
VALUES
(
:NEW.USERID,
USERACCOUNT_ID.NEXTVAL,
0
);
END;

INSERT INTO USERS
(
USERID,
USERNAME
)
VALUES
(
1000,
‘SMITH’
);
COMMIT;