CREATE OR REPLACE TRIGGER check_emp -------创建触发器

BEFORE update OR insert OR delete ON emp

REFERENCING new AS nn old AS oo

FOR EACH ROW

WHEN (nn.sal > 2000)

BEGIN

IF INSERTING THEN

dbms_output.put_line('THE OPERATION IS INSERT');

ELSIF UPDATING THEN

dbms_output.put_line('THE OPERATION IS UPDATE');

ELSIF DELETING THEN

dbms_output.put_line('THE OPERATION IS DELETE');

ELSE

dbms_output.put_line('OTHERS OPERATION');

END IF;

END;

update emp set sal=800 where empno=7369; --------更新语句

insert into emp(empno,ename,job,sal,mgr,comm,deptno,se) -------插入语句

values(7560,'王晨','tao',6895,4589,200,20,01)

rollback;                                                  ---------------回滚

drop trigger check_e2                                               -------------------删除触发器

 

 

 

create package demo_pack ---------------创建一个包

is

deptRec dept%ROWTYPE;

FUNCTION add_dept( ---------------声明一个方法

dept_no NUMBER, dept_name VARCHAR2,

location VARCHAR2)

RETURN NUMBER;

FUNCTION remove_dept(dept_no NUMBER) -------------声明方法

RETURN NUMBER;

PROCEDURE query_dept(dept_no IN NUMBER);

END demo_pack;

CREATE PACKAGE BODY demo_pack -------------创建一个包体

IS

FUNCTION add_dept

(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)

RETURN NUMBER

IS

empno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT(empno_remaining, -1);

BEGIN

INSERT INTO dept VALUES(dept_no, dept_name, location); ------------实现方法

IF SQL%FOUND THEN

RETURN 1;

END IF;

EXCEPTION

WHEN empno_remaining THEN

RETURN 0;

WHEN OTHERS THEN

RETURN -1;

END add_dept;

FUNCTION remove_dept(dept_no NUMBER)

RETURN NUMBER

IS

BEGIN

DELETE FROM dept WHERE deptno=dept_no;

IF SQL%FOUND THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

EXCEPTION

WHEN OTHERS THEN

RETURN -1;

END remove_dept;

PROCEDURE query_dept

(dept_no IN NUMBER)

IS

BEGIN

SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||dept_no||'的部门');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);

END query_dept;

END demo_pack;

drop package demo_pack;

DECLARE ------------调用包方法

Var NUMBER;

BEGIN

Var := demo_pack.add_dept(90,'Administration', 'Beijing');

IF var =-1 THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);

ELSIF var =0 THEN

DBMS_OUTPUT.PUT_LINE('该部门记录已经存在！');

ELSE

DBMS_OUTPUT.PUT_LINE('添加记录成功！');

Demo_pack.query_dept(90);

DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||'---'||

demo_pack.DeptRec.dname||'---'||demo_pack.DeptRec.loc);

var := demo_pack.remove_dept(90);

IF var =-1 THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);

ELSIF var=0 THEN

DBMS_OUTPUT.PUT_LINE('该部门记录不存在！');

ELSE

DBMS_OUTPUT.PUT_LINE('删除记录成功！');

END IF;

END IF;

END;

 

 

 

 

 

DECLARE --------游标应用

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

CURSOR c_cursor IS SELECT ename, sal FROM emp WHERE rownum<11;

BEGIN

OPEN c_cursor; ----------打开游标

FETCH c_cursor INTO v_ename, v_sal; ------------获取值

WHILE c_cursor %FOUND LOOP

DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );

FETCH c_cursor INTO v_ename, v_sal;

END LOOP;

CLOSE c_cursor; ----------关闭游标

END;