ORACLE PL/SQL编程详解，转

DECLARE

--声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数

BEGIN

-- 执行部分: 过程及SQL 语句 , 即程序的主要部分

EXCEPTION

-- 执行异常部分: 错误处理

END;

DECLARE

ename varchar2(20) :='EricHu';

BEGIN

DELETE FROM scott.emp WHERE ename=ename;

END;

标识符

命名规则

例子

程序变量

V_name

V_name

程序常量

C_Name

C_company_name

游标变量

Cursor_Name

Cursor_Emp

异常标识

E_name

E_too_many

表类型

Name_table_type

Emp_record_type

表

Name_table

Emp

记录类型

Name_record

Emp_record

SQL*Plus 替代变量

P_name

P_sal

绑定变量

G_name

G_year_sal

类型

子类

说 明

范 围

ORACLE限制

CHAR

Character

String

Rowid

Nchar

定长字符串

民族语言字符集

0à32767

可选,确省=1

2000

VARCHAR2

Varchar, String

NVARCHAR2

可变字符串

民族语言字符集

0à32767

4000

4000

BINARY_INTEGER

带符号整数,为整数计算优化性能

NUMBER(p,s)

Dec

Double precision

Integer

Int

Numeric

Real

Small int

小数, NUMBER 的子类型

高精度实数

整数, NUMBER 的子类型

整数, NUMBER 的子类型

与NUMBER等价

与NUMBER等价

整数, 比 integer 小

LONG

变长字符串

0->2147483647

32,767字节

DATE

日期型

公元前4712年1月1日至公元后4712年12月31日

BOOLEAN

布尔型

TRUE, FALSE,NULL

不使用

ROWID

存放数据库行号

UROWID

通用行标识符，字符类型

DECLARE

Row_id ROWID;

info VARCHAR2(40);

BEGIN

INSERT INTO scott.dept VALUES (90, '财务室', '海口')

RETURNING rowid, dname||':'||to_char(deptno)||':'||loc

INTO row_id, info;

DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);

DBMS_OUTPUT.PUT_LINE(info);

END;

DECLARE

Row_id ROWID;

info VARCHAR2(40);

BEGIN

UPDATE dept SET deptno=100 WHERE DNAME='财务室'

RETURNING rowid, dname||':'||to_char(deptno)||':'||loc

INTO row_id, info;

DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);

DBMS_OUTPUT.PUT_LINE(info);

END;

DECLARE

Row_id ROWID;

info VARCHAR2(40);

BEGIN

DELETE dept WHERE DNAME='办公室'

RETURNING rowid, dname||':'||to_char(deptno)||':'||loc

INTO row_id, info;

DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);

DBMS_OUTPUT.PUT_LINE(info);

END;

TYPE record_name IS RECORD(

v1 data_type1 [NOT NULL] [:= default_value ],

v2 data_type2 [NOT NULL] [:= default_value ],

......

vn data_typen [NOT NULL] [:= default_value ] );

DECLARE

TYPE test_rec IS RECORD(

Name VARCHAR2(30) NOT NULL := '胡勇',

Info VARCHAR2(100));

rec_book test_rec;

BEGIN

rec_book.Name :='胡勇';

rec_book.Info :='谈PL/SQL编程;';

DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info);

END;

DECLARE

--定义与hr.employees表中的这几个列相同的记录数据类型

TYPE RECORD_TYPE_EMPLOYEES IS RECORD(

f_name hr.employees.first_name%TYPE,

h_date hr.employees.hire_date%TYPE,

j_id hr.employees.job_id%TYPE);

--声明一个该记录数据类型的记录变量

v_emp_record RECORD_TYPE_EMPLOYEES;

BEGIN

SELECT first_name, hire_date, job_id INTO v_emp_record

FROM employees

WHERE employee_id = &emp_id;

DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name

||' 雇佣日期：'||v_emp_record.h_date

||' 岗位：'||v_emp_record.j_id);

END;

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

DECLARE

--定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型

TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);

--声明一个该VARRAY数据类型的变量

v_reg_varray REG_VARRAY_TYPE;

BEGIN

--用构造函数语法赋予初值

v_reg_varray := reg_varray_type

('中国', '美国', '英国', '日本', '法国');

DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、'

||v_reg_varray(2)||'、'

||v_reg_varray(3)||'、'

||v_reg_varray(4));

DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5));

--用构造函数语法赋予初值后就可以这样对成员赋值

v_reg_varray(5) := '法国';

DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5));

END;

DECLARE

-- 用%TYPE 类型定义与表相配的字段

TYPE T_Record IS RECORD(

T_no emp.empno%TYPE,

T_name emp.ename%TYPE,

T_sal emp.sal%TYPE );

-- 声明接收数据的变量

v_emp T_Record;

BEGIN

SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788;

DBMS_OUTPUT.PUT_LINE

(TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||' ' || TO_CHAR(v_emp.t_sal));

END;

DECLARE

v_empno emp.empno%TYPE :=&no;

Type t_record is record (

v_name emp.ename%TYPE,

v_sal emp.sal%TYPE,

v_date emp.hiredate%TYPE);

Rec t_record;

BEGIN

SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;

DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);

END;

DECLARE

v_empno emp.empno%TYPE :=&no;

rec emp%ROWTYPE;

BEGIN

SELECT * INTO rec FROM emp WHERE empno=v_empno;

DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);

END;

VARIABLE return_code NUMBER

VARIABLE return_msg VARCHAR2(20)

PRINT return_code

PRINT return_msg

VARIABLE result NUMBER;

BEGIN

SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp

WHERE empno=7844;

END;

--然后再执行

PRINT result

TYPE table_name IS TABLE OF element_type [NOT NULL]

INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

方法

描述

EXISTS(n)

如果集合的第n个成员存在，则返回true

COUNT

返回已经分配了存储空间即赋值了的成员数量

FIRST

LAST

FIRST：返回成员的最低下标值

LAST： 返回成员的最高下标值

PRIOR(n)

返回下标为n的成员的前一个成员的下标。如果没有则返回NULL

NEXT(N)

返回下标为n的成员的后一个成员的下标。如果没有则返回NULL

TRIM

TRIM：删除末尾一个成员

TRIM(n) ：删除末尾n个成员

DELETE

DELETE：删除所有成员

DELETE(n) ：删除第n个成员

DELETE(m, n) ：删除从n到m的成员

EXTEND

EXTEND：添加一个null成员

EXTEND(n)：添加n个null成员

EXTEND(n,i)：添加n个成员，其值与第i个成员相同

LIMIT

返回在varray类型变量中出现的最高下标值

DECLARE

TYPE dept_table_type IS TABLE OF

dept%ROWTYPE INDEX BY BINARY_INTEGER;

my_dname_table dept_table_type;

v_count number(2) :=4;

BEGIN

FOR int IN 1 .. v_count LOOP

SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10;

END LOOP;

FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP

DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno);

DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);

END LOOP;

END;

DECLARE

--定义记录表数据类型

TYPE reg_table_type IS TABLE OF varchar2(25)

INDEX BY BINARY_INTEGER;

--声明记录表数据类型的变量

v_reg_table REG_TABLE_TYPE;

BEGIN

v_reg_table(1) := 'Europe';

v_reg_table(2) := 'Americas';

v_reg_table(3) := 'Asia';

v_reg_table(4) := 'Middle East and Africa';

v_reg_table(5) := 'NULL';

DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、'

||v_reg_table (2)||'、'

||v_reg_table (3)||'、'

||v_reg_table (4));

DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5));

END;

DECLARE

--定义记录表数据类型

TYPE emp_table_type IS TABLE OF employees%ROWTYPE

INDEX BY BINARY_INTEGER;

--声明记录表数据类型的变量

v_emp_table EMP_TABLE_TYPE;

BEGIN

SELECT first_name, hire_date, job_id INTO

v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id

FROM employees WHERE employee_id = 177;

SELECT first_name, hire_date, job_id INTO

v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id

FROM employees WHERE employee_id = 178;

DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name

||' 雇佣日期：'||v_emp_table(1).hire_date

||' 岗位：'||v_emp_table(1).job_id);

DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name

||' 雇佣日期：'||v_emp_table(2).hire_date

||' 岗位：'||v_emp_table(2).job_id);

END;

运算符

意义

=

等于

<> , != , ~= , ^=

不等于

<

小于

>

大于

<=

小于或等于

>=

大于或等于

运算符

意义

+

加号

-

减号

*

乘号

/

除号

:=

赋值号

=>

关系号

..

范围运算符

||

字符连接符

运算符

意义

IS NULL

是空值

BETWEEN AND

介于两者之间

IN

在一列值中间

AND

逻辑与

OR

逻辑或

NOT

取返,如IS NOT NULL, NOT IN

variable := expression ;

DECLARE

bDone BOOLEAN;

BEGIN

bDone := FALSE;

WHILE NOT bDone LOOP

Null;

END LOOP;

END;

DECLARE

emp_id emp.empno%TYPE :=7788;

emp_name emp.ename%TYPE;

wages emp.sal%TYPE;

BEGIN

SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages

FROM emp WHERE empno = emp_id;

DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages));

END;

v_total := TO_NUMBER('100.0') + sal;

v_comm := TO_CHAR('123.45') || '元' ;

v_date := TO_DATE('2001.07.03','yyyy.mm.dd');

v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

DECLARE

Emess char(80);

BEGIN

DECLARE

V1 NUMBER(4);

BEGIN

SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president';

DBMS_OUTPUT.PUT_LINE(V1);

EXCEPTION

When TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ('More than one president');

END;

DECLARE

V1 NUMBER(4);

BEGIN

SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager';

EXCEPTION

When TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ('More than one manager');

END;

EXCEPTION

When others THEN

Emess:=substr(SQLERRM,1,80);

DBMS_OUTPUT.PUT_LINE(emess);

END;

/***********************************************/

/* 文件名： test.sql */

/* 说 明:

一个简单的插入测试，无实际应用。*/

/* 作 者： kenny */

/* 时 间： 2011-5-9 */

/***********************************************/

DECLARE

v_ename VARCHAR2(20) := 'Bill';

v_sal NUMBER(7,2) :=1234.56;

v_deptno NUMBER(2) := 10;

v_empno NUMBER(4) := 8888;

BEGIN

INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )

VALUES (v_empno, v_ename, 'Manager', v_sal, v_deptno,

TO_DATE('1954.06.09','yyyy.mm.dd') );

COMMIT;

END;

/***********************************************/

/* 文件名： test_deletedata.sql */

/* 说 明:

简单的删除例子，不是实际应用。 */

/* 作 者： kenny */

/* 时 间： 2011-5-9 */

/***********************************************/

DECLARE

v_ename VARCHAR2(20) := 'Bill';

v_sal NUMBER(7,2) :=1234.56;

v_deptno NUMBER(2) := 10;

v_empno NUMBER(4) := 8888;

BEGIN

INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )

VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,

TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );

COMMIT;

END;

DECLARE

v_empno number(4) := 8888;

BEGIN

DELETE FROM emp WHERE empno=v_empno;

COMMIT;

END;

IF <布尔表达式> THEN

PL/SQL 和 SQL语句

END IF;

-----------------------

IF <布尔表达式> THEN

PL/SQL 和 SQL语句

ELSE

其它语句

END IF;

-----------------------

IF <布尔表达式> THEN

PL/SQL 和 SQL语句

ELSIF < 其它布尔表达式> THEN

其它语句

ELSIF < 其它布尔表达式> THEN

其它语句

ELSE

其它语句

END IF;

DECLARE

v_empno employees.employee_id%TYPE :=&empno;

V_salary employees.salary%TYPE;

V_comment VARCHAR2(35);

BEGIN

SELECT salary INTO v_salary FROM employees

WHERE employee_id = v_empno;

IF v_salary < 1500 THEN

V_comment:= '太少了,加点吧~!';

ELSIF v_salary <3000 THEN

V_comment:= '多了点,少点吧~!';

ELSE

V_comment:= '没有薪水~!';

END IF;

DBMS_OUTPUT.PUT_LINE(V_comment);

exception

when no_data_found then

DBMS_OUTPUT.PUT_LINE('没有数据~!');

when others then

DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm);

END;

DECLARE

v_first_name VARCHAR2(20);

v_salary NUMBER(7,2);

BEGIN

SELECT first_name, salary INTO v_first_name, v_salary FROM employees

WHERE employee_id = &emp_id;

DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary);

IF v_salary < 10000 THEN

DBMS_OUTPUT.PUT_LINE('工资低于10000');

ELSE

IF 10000 <= v_salary AND v_salary < 20000 THEN

DBMS_OUTPUT.PUT_LINE('工资在10000到20000之间');

ELSE

DBMS_OUTPUT.PUT_LINE('工资高于20000');

END IF;

END IF;

END;

DECLARE

v_first_name VARCHAR2(20);

v_hire_date DATE;

v_bonus NUMBER(6,2);

BEGIN

SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees

WHERE employee_id = &emp_id;

IF v_hire_date > TO_DATE('01-1月-90') THEN

v_bonus := 800;

ELSIF v_hire_date > TO_DATE('01-1月-88') THEN

v_bonus := 1600;

ELSE

v_bonus := 2400;

END IF;

DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date

||'、奖金是'||v_bonus);

END;

CASE 条件表达式

WHEN 条件表达式结果1 THEN

语句段1

WHEN 条件表达式结果2 THEN

语句段2

......

WHEN 条件表达式结果n THEN

语句段n

[ELSE 条件表达式结果]

END;

CASE

WHEN 条件表达式1 THEN

语句段1

WHEN 条件表达式2 THEN

语句段2

......

WHEN 条件表达式n THEN

语句段n

[ELSE 语句段]

END;

DECLARE

V_grade char(1) := UPPER('&p_grade');

V_appraisal VARCHAR2(20);

BEGIN

V_appraisal :=

CASE v_grade

WHEN 'A' THEN 'Excellent'

WHEN 'B' THEN 'Very Good'

WHEN 'C' THEN 'Good'

ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal);

END;

DECLARE

v_first_name employees.first_name%TYPE;

v_job_id employees.job_id%TYPE;

v_salary employees.salary%TYPE;

v_sal_raise NUMBER(3,2);

BEGIN

SELECT first_name, job_id, salary INTO

v_first_name, v_job_id, v_salary

FROM employees WHERE employee_id = &emp_id;

CASE

WHEN v_job_id = 'PU_CLERK' THEN

IF v_salary < 3000 THEN v_sal_raise := .08;

ELSE v_sal_raise := .07;

END IF;

WHEN v_job_id = 'SH_CLERK' THEN

IF v_salary < 4000 THEN v_sal_raise := .06;

ELSE v_sal_raise := .05;

END IF;

WHEN v_job_id = 'ST_CLERK' THEN

IF v_salary < 3500 THEN v_sal_raise := .04;

ELSE v_sal_raise := .03;

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id);

END CASE;

DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id

||'、的工资是'||v_salary

||'、工资涨幅是'||v_sal_raise);

END;

LOOP

要执行的语句;

EXIT WHEN <条件语句> --条件满足，退出循环语句

END LOOP;

DECLARE

int NUMBER(2) :=0;

BEGIN

LOOP

int := int + 1;

DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);

EXIT WHEN int =10;

END LOOP;

END;

WHILE <布尔表达式> LOOP

要执行的语句;

END LOOP;

DECLARE

x NUMBER :=1;

BEGIN

WHILE x<=10 LOOP

DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);

x:= x+1;

END LOOP;

END;

[<<循环标签>>]

FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP

要执行的语句;

END LOOP [循环标签];

BEGIN

FOR int in 1..10 LOOP

DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);

END LOOP;

END;

CREATE TABLE temp_table(num_col NUMBER);

DECLARE

V_counter NUMBER := 10;

BEGIN

INSERT INTO temp_table(num_col) VALUES (v_counter );

FOR v_counter IN 20 .. 25 LOOP

INSERT INTO temp_table (num_col ) VALUES ( v_counter );

END LOOP;

INSERT INTO temp_table(num_col) VALUES (v_counter );

FOR v_counter IN REVERSE 20 .. 25 LOOP

INSERT INTO temp_table (num_col ) VALUES ( v_counter );

END LOOP;

END ;

DROP TABLE temp_table;

DECLARE

TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型

v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量

v_howmany NUMBER; --声明一个变量来保存雇员的数量

BEGIN

--用某些job_id值初始化数组

v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN');

--用FOR...LOOP...END LOOP循环使用每个数组成员的值

FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP

--针对数组中的每个岗位，决定该岗位的雇员的数量

SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i);

DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)||

'总共有'|| TO_CHAR(v_howmany) || '个雇员');

END LOOP;

END;

/*求100至110之间的素数*/

DECLARE

v_m NUMBER := 101;

v_i NUMBER;

v_n NUMBER := 0;

BEGIN

WHILE v_m < 110 LOOP

v_i := 2;

LOOP

IF mod(v_m, v_i) = 0 THEN

v_i := 0;

EXIT;

END IF;

v_i := v_i + 1;

EXIT WHEN v_i > v_m - 1;

END LOOP;

IF v_i > 0 THEN

v_n := v_n + 1;

DBMS_OUTPUT.PUT_LINE('第'|| v_n || '个素数是' || v_m);

END IF;

v_m := v_m + 2;

END LOOP;

END;

DECLARE

V_counter NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);

V_counter := v_counter + 1;

IF v_counter > 10 THEN

GOTO labelOffLOOP;

END IF;

END LOOP;

<<labelOffLOOP>>

DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);

END;

DECLARE

v_i NUMBER := 0;

v_s NUMBER := 0;

BEGIN

<<label_1>>

v_i := v_i + 1;

IF v_i <= 1000 THEN

v_s := v_s + v_i;

GOTO label_1;

END IF;

DBMS_OUTPUT.PUT_LINE(v_s);

END;

DECLARE

...

BEGIN

...

IF v_num IS NULL THEN

GOTO labelPrint;

END IF;

…

<<labelPrint>>

NULL; --不需要处理任何数据。

END;

DECLARE

v_emp_id employees.employee_id%TYPE;

v_first_name employees.first_name%TYPE;

v_salary employees.salary%TYPE;

v_sal_raise NUMBER(3,2);

BEGIN

v_emp_id := &emp_id;

SELECT first_name, salary INTO v_first_name, v_salary

FROM employees WHERE employee_id = v_emp_id;

IF v_salary <= 3000 THEN

v_sal_raise := .10;

DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary

||'、工资涨幅是'||v_sal_raise);

ELSE

NULL;

END IF;

END;

SQL语句

游标

非查询语句

隐式的

结果是单行的查询语句

隐式的或显示的

结果是多行的查询语句

显示的

CURSOR cursor_name[(parameter[, parameter]…)]

[RETURN datatype]

IS

select_statement;

parameter_name [IN] datatype [{:= | DEFAULT} expression]

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

FETCH cursor_name INTO {variable_list | record_variable };

CLOSE cursor_name;

DECLARE

CURSOR c_cursor

IS SELECT first_name || last_name, Salary

FROM EMPLOYEES

WHERE rownum<11;

v_ename EMPLOYEES.first_name%TYPE;

v_sal EMPLOYEES.Salary%TYPE;

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;

DECLARE

DeptRec DEPARTMENTS%ROWTYPE;

Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE;

Dept_loc DEPARTMENTS.LOCATION_ID%TYPE;

CURSOR c1 IS

SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS

WHERE DEPARTMENT_ID <= 30;

CURSOR c2(dept_no NUMBER DEFAULT 10) IS

SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS

WHERE DEPARTMENT_ID <= dept_no;

CURSOR c3(dept_no NUMBER DEFAULT 10) IS

SELECT * FROM DEPARTMENTS

WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO dept_name, dept_loc;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);

END LOOP;

CLOSE c1;

OPEN c2;

LOOP

FETCH c2 INTO dept_name, dept_loc;

EXIT WHEN c2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);

END LOOP;

CLOSE c2;

OPEN c3(dept_no =>20);

LOOP

FETCH c3 INTO deptrec;

EXIT WHEN c3%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID);

END LOOP;

CLOSE c3;

END;

游标属性

属性作用

Cursor_name%FOUND

布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

Cursor_name%NOTFOUND

布尔型属性，与%FOUND相反；

Cursor_name%ISOPEN

布尔型属性，当游标已打开时返回 TRUE；

Cursor_name%ROWCOUNT

数字型属性，返回已从游标中读取的记录数。

DECLARE

v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;

v_sal EMPLOYEES.Salary%TYPE;

CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;

BEGIN

OPEN c_cursor;

LOOP

FETCH c_cursor INTO v_empno, v_sal;

EXIT WHEN c_cursor%NOTFOUND;

IF v_sal<=1200 THEN

UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_filtered"> DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');

END IF;

DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT);

END LOOP;

CLOSE c_cursor;

END;

DECLARE

v_f_name employees.first_name%TYPE;

v_j_id employees.job_id%TYPE;

CURSOR c1 --声明游标,没有参数没有返回值

IS

SELECT first_name, job_id FROM employees

WHERE department_id = 20;

BEGIN

OPEN c1; --打开游标

LOOP

FETCH c1 INTO v_f_name, v_j_id; --提取游标

IF c1%FOUND THEN

DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id);

ELSE

DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');

EXIT;

END IF;

END LOOP;

CLOSE c1; --关闭游标

END;

DECLARE

v_f_name employees.first_name%TYPE;

v_h_date employees.hire_date%TYPE;

CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值

IS

SELECT first_name, hire_date FROM employees

WHERE department_id = dept_id AND job_id = j_id;

BEGIN

OPEN c2(90, 'AD_VP'); --打开游标,传递参数值

LOOP

FETCH c2 INTO v_f_name, v_h_date; --提取游标

IF c2%FOUND THEN

DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date);

ELSE

DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');

EXIT;

END IF;

END LOOP;

CLOSE c2; --关闭游标

END;

DECLARE

TYPE emp_record_type IS RECORD(

f_name employees.first_name%TYPE,

h_date employees.hire_date%TYPE);

v_emp_record EMP_RECORD_TYPE;

CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值

RETURN EMP_RECORD_TYPE

IS

SELECT first_name, hire_date FROM employees

WHERE department_id = dept_id AND job_id = j_id;

BEGIN

OPEN c3(j_id => 'AD_VP', dept_id => 90); --打开游标,传递参数值

LOOP

FETCH c3 INTO v_emp_record; --提取游标

IF c3%FOUND THEN

DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'

||v_emp_record.h_date);

ELSE

DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');

EXIT;

END IF;

END LOOP;

CLOSE c3; --关闭游标

END;

DECLARE

CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值

IS

SELECT first_name f_name, hire_date FROM employees

WHERE department_id = dept_id AND job_id = j_id;

--基于游标定义记录变量，比声明记录类型变量要方便，不容易出错

v_emp_record c4%ROWTYPE;

BEGIN

OPEN c4(90, 'AD_VP'); --打开游标,传递参数值

LOOP

FETCH c4 INTO v_emp_record; --提取游标

IF c4%FOUND THEN

DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'

||v_emp_record.hire_date);

ELSE

DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');

EXIT;

END IF;

END LOOP;

CLOSE c4; --关闭游标

END;

FOR index_variable IN cursor_name[(value[, value]…)] LOOP

-- 游标数据处理代码

END LOOP;

DECLARE

CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary

FROM employees ;

BEGIN

--隐含打开游标

FOR v_sal IN c_sal LOOP

--隐含执行一个FETCH语句

DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;

--隐含监测c_sal%NOTFOUND

END LOOP;

--隐含关闭游标

END;

DECLARE

CURSOR c_cursor(dept_no NUMBER DEFAULT 10)

IS

SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;

BEGIN

DBMS_OUTPUT.PUT_LINE('当dept_no参数值为30：');

FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);

END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：');

FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);

END LOOP;

END;

BEGIN

FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);

END LOOP;

END;

属性

值

SELECT

INSERT

UPDATE

DELETE

SQL%ISOPEN

FALSE

FALSE

FALSE

FALSE

SQL%FOUND

TRUE

有结果

成功

成功

SQL%FOUND

FALSE

没结果

失败

失败

SQL%NOTFUOND

TRUE

没结果

失败

失败

SQL%NOTFOUND

FALSE

有结果

成功

失败

SQL%ROWCOUNT

返回行数，只为1

插入的行数

修改的行数

删除的行数

DECLARE

V_deptno department_id%TYPE :=&p_deptno;

BEGIN

DELETE FROM employees WHERE department_filtered"> IF SQL%NOTFOUND THEN

DELETE FROM departments WHERE department_filtered"> END IF;

END;

DECLARE

v_rows NUMBER;

BEGIN

--更新数据

UPDATE employees SET salary = 30000

WHERE department_id = 90 AND job_id = 'AD_VP';

--获取默认游标的属性值

v_rows := SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');

--回退更新，以便使数据库的数据保持原样

ROLLBACK;

END;

SELECT column_list FROM table_list FOR UPDATE [OF column[, column]…] [NOWAIT]

ORA-0054 ：resource busy and acquire with nowait specified.

DECLARE

V_deptno employees.department_id%TYPE :=&p_deptno;

CURSOR emp_cursor

IS

SELECT employees.employee_id, employees.salary

FROM employees WHERE employees.department_filtered"> FOR UPDATE NOWAIT;

BEGIN

FOR emp_record IN emp_cursor LOOP

IF emp_record.salary < 1500 THEN

UPDATE employees SET salary=1500

WHERE CURRENT OF emp_cursor;

END IF;

END LOOP;

-- COMMIT;

END;

DECLARE

v_emp_record employees%ROWTYPE;

CURSOR c1

IS

SELECT * FROM employees FOR UPDATE;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_emp_record;

EXIT WHEN c1%NOTFOUND;

IF v_emp_record.department_id = 90 AND

v_emp_record.job_id = 'AD_VP'

THEN

UPDATE employees SET salary = 20000

WHERE CURRENT OF c1; --更新当前游标行对应的数据行

END IF;

END LOOP;

COMMIT; --提交已经修改的数据

CLOSE c1;

END;

TYPE ref_type_name IS REF CURSOR

[ RETURN return_type];

DECLARE

TYPE deptrecord IS RECORD(

Deptno departments.department_id%TYPE,

Dname departments.department_name%TYPE,

Loc departments.location_id%TYPE

);

TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;

TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;

TYPE curtype IS REF CURSOR;

Dept_c1 deptcurtype;

Dept_c2 deptcurtyp1;

Cv curtype;

OPEN {cursor_variable_name | :host_cursor_variable_name}

FOR select_statement;

FETCH {cursor_variable_name | :host_cursor_variable_name}

INTO {variable [, variable]…| record_variable};

CLOSE {cursor_variable_name | :host_cursor_variable_name}

DECLARE

TYPE emp_job_rec IS RECORD(

Employee_id employees.employee_id%TYPE,

Employee_name employees.first_name%TYPE,

Job_title employees.job_id%TYPE

);

TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;

Emp_refcur emp_job_refcur_type ;

Emp_job emp_job_rec;

BEGIN

OPEN emp_refcur FOR

SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id

FROM employees

ORDER BY employees.department_id;

FETCH emp_refcur INTO emp_job;

WHILE emp_refcur%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);

FETCH emp_refcur INTO emp_job;

END LOOP;

END;

PROMPT

PROMPT 'What table would you like to see?'

ACCEPT tab PROMPT '(D)epartment, or (E)mployees:'

DECLARE

Type refcur_t IS REF CURSOR;

Refcur refcur_t;

TYPE sample_rec_type IS RECORD (

Id number,

Description VARCHAR2 (30)

);

sample sample_rec_type;

selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));

BEGIN

IF selection='D' THEN

OPEN refcur FOR

SELECT departments.department_id, departments.department_name FROM departments;

DBMS_OUTPUT.PUT_LINE('Department data');

ELSIF selection='E' THEN

OPEN refcur FOR

SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;

DBMS_OUTPUT.PUT_LINE('Employee data');

ELSE

DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');

RETURN;

END IF;

DBMS_OUTPUT.PUT_LINE('----------------------');

FETCH refcur INTO sample;

WHILE refcur%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);

FETCH refcur INTO sample;

END LOOP;

CLOSE refcur;

END;

DECLARE

--定义一个游标数据类型

TYPE emp_cursor_type IS REF CURSOR;

--声明一个游标变量

c1 EMP_CURSOR_TYPE;

--声明两个记录变量

v_emp_record employees%ROWTYPE;

v_reg_record regions%ROWTYPE;

BEGIN

OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;

LOOP

FETCH c1 INTO v_emp_record;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'

||v_emp_record.hire_date);

END LOOP;

--将同一个游标变量对应到另一个SELECT语句

OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;

LOOP

FETCH c1 INTO v_reg_record;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'

||v_reg_record.region_name);

END LOOP;

CLOSE c1;

END;

DECLARE

--定义一个与employees表中的这几个列相同的记录数据类型

TYPE emp_record_type IS RECORD(

f_name employees.first_name%TYPE,

h_date employees.hire_date%TYPE,

j_id employees.job_id%TYPE);

--声明一个该记录数据类型的记录变量

v_emp_record EMP_RECORD_TYPE;

--定义一个游标数据类型

TYPE emp_cursor_type IS REF CURSOR

RETURN EMP_RECORD_TYPE;

--声明一个游标变量

c1 EMP_CURSOR_TYPE;

BEGIN

OPEN c1 FOR SELECT first_name, hire_date, job_id

FROM employees WHERE department_id = 20;

LOOP

FETCH c1 INTO v_emp_record;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name

||' 雇佣日期：'||v_emp_record.h_date

||' 岗位：'||v_emp_record.j_id);

END LOOP;

CLOSE c1;

END;

EXCEPTION

WHEN first_exception THEN <code to handle first exception >

WHEN second_exception THEN <code to handle second exception >

WHEN OTHERS THEN <code to handle others exception >

END;

错误号

异常错误信息名称

说明

ORA-0001

Dup_val_on_index

违反了唯一性限制

ORA-0051

Timeout-on-resource

在等待资源时发生超时

ORA-0061

Transaction-backed-out

由于发生死锁事务被撤消

ORA-1001

Invalid-CURSOR

试图使用一个无效的游标

ORA-1012

Not-logged-on

没有连接到ORACLE

ORA-1017

Login-denied

无效的用户名/口令

ORA-1403

No_data_found

SELECT INTO没有找到数据

ORA-1422

Too_many_rows

SELECT INTO 返回多行

ORA-1476

Zero-divide

试图被零除

ORA-1722

Invalid-NUMBER

转换一个数字失败

ORA-6500

Storage-error

内存不够引发的内部错误

ORA-6501

Program-error

内部错误

ORA-6502

Value-error

转换或截断错误

ORA-6504

Rowtype-mismatch

宿主游标变量与 PL/SQL变量有不兼容行类型

ORA-6511

CURSOR-already-OPEN

试图打开一个已处于打开状态的游标

ORA-6530

Access-INTO-null

试图为null 对象的属性赋值

ORA-6531

Collection-is-null

试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上

ORA-6532

Subscript-outside-limit

对嵌套或varray索引得引用超出声明范围以外

ORA-6533

Subscript-beyond-count

对嵌套或varray 索引得引用大于集合中元素的个数.

DECLARE

v_empno employees.employee_id%TYPE := &empno;

v_sal employees.salary%TYPE;

BEGIN

SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;

IF v_sal<=1500 THEN

UPDATE employees SET salary = salary + 100 WHERE employee_filtered"> DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');

ELSE

DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');

WHEN TOO_MANY_ROWS THEN

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

WHEN OTHERS THEN

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

END;

INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');

DECLARE

v_deptno departments.department_id%TYPE := &deptno;

deptno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);

/* -2292 是违反一致性约束的错误代码 */

BEGIN

DELETE FROM departments WHERE department_id = v_deptno;

EXCEPTION

WHEN deptno_remaining THEN

DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');

WHEN OTHERS THEN

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

END;

DECLARE

v_empno employees.employee_id%TYPE :=&empno;

no_result EXCEPTION;

BEGIN

UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;

IF SQL%NOTFOUND THEN

RAISE no_result;

END IF;

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');

WHEN OTHERS THEN

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

END;

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );

CREATE TABLE errlog(

Errcode NUMBER,

Errtext CHAR(40));

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)

RETURN NUMBER

AS

v_sal NUMBER;

BEGIN

IF p_deptno IS NULL THEN

RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’);

ELSIF p_deptno<0 THEN

RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’);

ELSE

SELECT SUM(employees.salary) INTO v_sal FROM employees

WHERE employees.department_filtered"> RETURN v_sal;

END IF;

END;

DECLARE

V_salary NUMBER(7,2);

V_sqlcode NUMBER;

V_sqlerr VARCHAR2(512);

Null_deptno EXCEPTION;

Invalid_deptno EXCEPTION;

PRAGMA EXCEPTION_INIT(null_deptno,-20991);

PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);

BEGIN

V_salary :=get_salary(10);

DBMS_OUTPUT.PUT_LINE('10号部门工资：' || TO_CHAR(V_salary));

BEGIN

V_salary :=get_salary(-10);

EXCEPTION

WHEN invalid_deptno THEN

V_sqlcode :=SQLCODE;

V_sqlerr :=SQLERRM;

INSERT INTO errlog(errcode, errtext)

VALUES(v_sqlcode, v_sqlerr);

COMMIT;

END inner1;

V_salary :=get_salary(20);

DBMS_OUTPUT.PUT_LINE('部门号为20的工资为：'||TO_CHAR(V_salary));

BEGIN

V_salary :=get_salary(NULL);

END inner2;

V_salary := get_salary(30);

DBMS_OUTPUT.PUT_LINE('部门号为30的工资为：'||TO_CHAR(V_salary));

EXCEPTION

WHEN null_deptno THEN

V_sqlcode :=SQLCODE;

V_sqlerr :=SQLERRM;

INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr);

COMMIT;

WHEN OTHERS THEN

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

END outer;

CREATE OR REPLACE TRIGGER tr_insert_emp

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

IF :new.first_name IS NULL OR :new.last_name is null THEN

RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.');

END IF;

END;

DECLARE

name varchar2(12):='EricHu';

其它语句

BEGIN

其它语句

EXCEPTION

WHEN OTHERS THEN

其它语句

END;

BEGIN

DECLARE

name varchar2(12):='EricHu';

其它语句

BEGIN

其它语句

EXCEPTION

WHEN OTHERS THEN

其它语句

END;

EXCEPTION

WHEN OTHERS THEN

其它语句

END;

CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100));

DECLARE

err_msg VARCHAR2(100);

BEGIN

/* 得到所有 ORACLE 错误信息 */

FOR err_num IN -100 .. 0 LOOP

err_msg := SQLERRM(err_num);

INSERT INTO errors VALUES(err_num, err_msg);

END LOOP;

END;

DROP TABLE errors;

BEGIN

INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)

VALUES(2222, 'Eric','Hu', SYSDATE, 20);

DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)

VALUES(2222, '胡','勇', SYSDATE, 20);

DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

EXCEPTION

WHEN OTHERS THEN

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

END;

DECLARE

empno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT(empno_remaining, -1);

/* -1 是违反唯一约束条件的错误代码 */

BEGIN

INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)

VALUES(3333, 'Eric','Hu', SYSDATE, 20);

DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id)

VALUES(3333, '胡','勇',SYSDATE, 20);

DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');

EXCEPTION

WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');

WHEN OTHERS THEN

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

END;

CREATE [OR REPLACE] FUNCTION function_name

(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],

[arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],

......

[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])

[ AUTHID DEFINER | CURRENT_USER ]

RETURN return_type

IS | AS

<类型.变量的声明部分>

BEGIN

执行部分

RETURN expression

EXCEPTION

异常处理部分

END function_name;

--获取某部门的工资总和

CREATE OR REPLACE

FUNCTION get_salary(

Dept_no NUMBER,

Emp_count OUT NUMBER)

RETURN NUMBER

IS

V_sum NUMBER;

BEGIN

SELECT SUM(SALARY), count(*) INTO V_sum, emp_count

FROM EMPLOYEES WHERE DEPARTMENT_filtered"> RETURN v_sum;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

WHEN OTHERS THEN

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

END get_salary;

argument_value1[,argument_value2 …]

DECLARE

V_num NUMBER;

V_sum NUMBER;

BEGIN

V_sum :=get_salary(10, v_num);

DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);

END;

argument => parameter [,…]

DECLARE

V_num NUMBER;

V_sum NUMBER;

BEGIN

V_sum :=get_salary(emp_count => v_num, dept_no => 10);

DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num);

END;

CREATE OR REPLACE FUNCTION demo_fun(

Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似

Age INTEGER,

Sex VARCHAR2)

RETURN VARCHAR2

AS

V_var VARCHAR2(32);

BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;

RETURN v_var;

END;

DECLARE

Var VARCHAR(32);

BEGIN

Var := demo_fun('user1', 30, sex => '男');

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40, sex => '男');

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);

END;

CREATE OR REPLACE FUNCTION demo_fun(

Name VARCHAR2,

Age INTEGER,

Sex VARCHAR2 DEFAULT '男')

RETURN VARCHAR2

AS

V_var VARCHAR2(32);

BEGIN

V_var := name||'：'||TO_CHAR(age)||'岁.'||sex;

RETURN v_var;

END;

DECLARE

var VARCHAR(32);

BEGIN

Var := demo_fun('user1', 30);

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user2', age => 40);

DBMS_OUTPUT.PUT_LINE(var);

Var := demo_fun('user3', sex => '女', age => 20);

DBMS_OUTPUT.PUT_LINE(var);

END;

CREATE [OR REPLACE] PROCEDURE procedure_name

([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],

[arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],

......

[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])

[ AUTHID DEFINER | CURRENT_USER ]

{ IS | AS }

<声明部分>

BEGIN

<执行部分>

EXCEPTION

<可选的异常错误处理程序>

END procedure_name;

CREATE TABLE logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

CREATE OR REPLACE

PROCEDURE DelEmp

(v_empno IN employees.employee_id%TYPE)

AS

No_result EXCEPTION;

BEGIN

DELETE FROM employees WHERE employee_id = v_empno;

IF SQL%NOTFOUND THEN

RAISE no_result;

END IF;

DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN OTHERS THEN

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

END DelEmp;

CREATE OR REPLACE

PROCEDURE InsertEmp(

v_empno in employees.employee_id%TYPE,

v_firstname in employees.first_name%TYPE,

v_lastname in employees.last_name%TYPE,

v_deptno in employees.department_id%TYPE

)

AS

empno_remaining EXCEPTION;

PRAGMA EXCEPTION_INIT(empno_remaining, -1);

/* -1 是违反唯一约束条件的错误代码 */

BEGIN

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)

VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);

DBMS_OUTPUT.PUT_LINE('温馨提示:插入数据记录成功!');

EXCEPTION

WHEN empno_remaining THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');

WHEN OTHERS THEN

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

END InsertEmp;

CREATE OR REPLACE

PROCEDURE insert_dept

(v_dept_id IN departments.department_id%TYPE,

v_dept_name IN departments.department_name%TYPE,

v_mgr_id IN departments.manager_id%TYPE,

v_loc_id IN departments.location_id%TYPE)

IS

ept_null_error EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_null_error, -1400);

ept_no_loc_id EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291);

BEGIN

INSERT INTO departments

(department_id, department_name, manager_id, location_id)

VALUES

(v_dept_id, v_dept_name, v_mgr_id, v_loc_id);

DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功');

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复');

WHEN ept_null_error THEN

RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空');

WHEN ept_no_loc_id THEN

RAISE_APPLICATION_ERROR(-20002, '没有该地点');

END insert_dept;

/*调用实例一:

DECLARE

ept_20000 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20000, -20000);

ept_20001 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20001, -20001);

ept_20002 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20002, -20002);

BEGIN

insert_dept(300, '部门300', 100, 2400);

insert_dept(310, NULL, 100, 2400);

insert_dept(310, '部门310', 100, 900);

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');

END;

调用实例二：

DECLARE

ept_20000 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20000, -20000);

ept_20001 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20001, -20001);

ept_20002 EXCEPTION;

PRAGMA EXCEPTION_INIT(ept_20002, -20002);

BEGIN

insert_dept(v_dept_name => '部门310', v_dept_id => 310,

v_mgr_id => 100, v_loc_id => 2400);

insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900);

EXCEPTION

WHEN ept_20000 THEN

DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');

WHEN ept_20001 THEN

DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');

WHEN ept_20002 THEN

DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');

END;

*/

EXEC[UTE] procedure_name( parameter1, parameter2…);

EXECUTE logexecution;

CREATE OR REPLACE

PROCEDURE QueryEmp

(v_empno IN employees.employee_id%TYPE,

v_ename OUT employees.first_name%TYPE,

v_sal OUT employees.salary%TYPE)

AS

BEGIN

SELECT last_name || last_name, salary INTO v_ename, v_sal

FROM employees

WHERE employee_id = v_empno;

DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!');

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN OTHERS THEN

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

END QueryEmp;

--调用

DECLARE

v1 employees.first_name%TYPE;

v2 employees.salary%TYPE;

BEGIN

QueryEmp(100, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

DBMS_OUTPUT.PUT_LINE('工资:'||v2);

QueryEmp(103, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

DBMS_OUTPUT.PUT_LINE('工资:'||v2);

QueryEmp(104, v1, v2);

DBMS_OUTPUT.PUT_LINE('姓名:'||v1);

DBMS_OUTPUT.PUT_LINE('工资:'||v2);

END;

CREATE OR REPLACE

PROCEDURE proc_demo

(

dept_no NUMBER DEFAULT 10,

sal_sum OUT NUMBER,

emp_count OUT NUMBER

)

IS

BEGIN

SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count

FROM employees WHERE department_id = dept_no;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN OTHERS THEN

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

END proc_demo;

DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num);

Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num);

END;

DECLARE

V_num NUMBER;

V_sum NUMBER(8, 2);

PROCEDURE proc_demo

(

Dept_no NUMBER DEFAULT 10,

Sal_sum OUT NUMBER,

Emp_count OUT NUMBER

)

IS

BEGIN

SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count

FROM employees WHERE department_filtered"> EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');

WHEN OTHERS THEN

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

END proc_demo;

--调用方法：

BEGIN

Proc_demo(30, v_sum, v_num);

DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);

Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);

END;

Connect HR/qaz

DROP TABLE logtable;

CREATE table logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution

AUTHID DEFINER

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT / AS SYSDBA

GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1;

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);

EXECUTE HR.logexecution

CONNECT HR/qaz

SELECT * FROM HR.logtable;

CONNECT HR/qaz

CREATE OR REPLACE PROCEDURE logexecution

AUTHID CURRENT_USER

IS

BEGIN

INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

GRANT EXECUTE ON logexecution TO PUBLIC;

CONNECT testuser1/userpwd1

INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE);

EXECUTE HR.logexecution

DROP TABLE logtable;

CREATE TABLE logtable(

Username varchar2(20),

Dassate_time date,

Mege varchar2(60)

);

CREATE TABLE temp_table( N number );

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO logtable VALUES ( user, sysdate, p_message );

COMMIT;

END log_message;

BEGIN

Log_message (‘About to insert into temp_table‘);

INSERT INTO temp_table VALUES (1);

Log_message (‘Rollback to insert into temp_table‘);

ROLLBACK;

END;

SELECT * FROM logtable;

SELECT * FROM temp_table;

CREATE OR REPLACE PROCEDURE log_message(p_message varchar2)

AS

BEGIN

INSERT INTO logtable VALUES ( user, sysdate, p_message );

COMMIT;

END log_message;

BEGIN

Log_message ('About to insert into temp_table');

INSERT INTO temp_table VALUES (1);

Log_message ('Rollback to insert into temp_table');

ROLLBACK;

END;

SELECT * FROM logtable;

SELECT * FROM temp_table;

GRANT system_privilege | role

TO user | role | PUBLIC [WITH ADMIN OPTION]

GRANT object_privilege | ALL ON schema.object

TO user | role | PUBLIC [WITH GRANT OPTION]

CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

DESC[RIBE] Procedure_name;

DROP PROCEDURE [user.]Procudure_name;

DROP FUNCTION [user.]Function_name;

DROP PROCEDURE logexecution;

DROP PROCEDURE delemp;

DROP PROCEDURE insertemp;

DROP PROCEDURE fireemp;

DROP PROCEDURE queryemp;

DROP PROCEDURE proc_demo;

DROP PROCEDURE log_message;

DROP FUNCTION demo_fun;

DROP FUNCTION get_salary;

CREATE [OR REPLACE] PACKAGE package_name

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

[公有数据类型定义[公有数据类型定义]…]

[公有游标声明[公有游标声明]…]

[公有变量、常量声明[公有变量、常量声明]…]

[公有函数声明[公有函数声明]…]

[公有过程声明[公有过程声明]…]

END [package_name];

CREATE [OR REPLACE] PACKAGE BODY package_name

{IS | AS}

[私有数据类型定义[私有数据类型定义]…]

[私有变量、常量声明[私有变量、常量声明]…]

[私有异常错误声明[私有异常错误声明]…]

[私有函数声明和定义[私有函数声明和定义]…]

[私有函过程声明和定义[私有函过程声明和定义]…]

[公有游标定义[公有游标定义]…]

[公有函数定义[公有函数定义]…]

[公有过程定义[公有过程定义]…]

BEGIN

执行部分(初始化部分)

END package_name;

CREATE OR REPLACE PACKAGE DEMO_PKG

IS

DEPTREC DEPT%ROWTYPE;

--Add dept...

FUNCTION add_dept(

dept_no NUMBER,

dept_name VARCHAR2,

location VARCHAR2)

RETURN NUMBER;

--delete dept...

FUNCTION delete_dept(dept_no NUMBER)

RETURN NUMBER;

--query dept...

PROCEDURE query_dept(dept_no IN NUMBER);

END DEMO_PKG;

CREATE OR REPLACE PACKAGE BODY DEMO_PKG

IS

FUNCTION add_dept

(

dept_no NUMBER,

dept_name VARCHAR2,

location VARCHAR2

)

RETURN NUMBER

IS

empno_remaining EXCEPTION; --自定义异常

PRAGMA EXCEPTION_INIT(empno_remaining, -1);

/* -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 delete_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 delete_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;

BEGIN

Null;

END DEMO_PKG;

DECLARE

Var NUMBER;

BEGIN

Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU');

IF var =-1 THEN

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

ELSIF var =0 THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录已经存在！');

ELSE

DBMS_OUTPUT.PUT_LINE('温馨提示:添加记录成功！');

DEMO_PKG.query_dept(90);

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

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

var := DEMO_PKG.delete_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;

--创建包说明

CREATE OR REPLACE PACKAGE EMP_PKG

IS

TYPE emp_table_type IS TABLE OF emp%ROWTYPE

INDEX BY BINARY_INTEGER;

PROCEDURE read_emp_table (p_emp_table OUT emp_table_type);

END EMP_PKG;

--创建包体

CREATE OR REPLACE PACKAGE BODY EMP_PKG

IS

PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)

IS

I BINARY_INTEGER := 0;

BEGIN

FOR emp_record IN ( SELECT * FROM emp ) LOOP

P_emp_table(i) := emp_record;

I := I + 1;

END LOOP;

END read_emp_table;

END EMP_PKG;

--执行

DECLARE

E_table EMP_PKG.emp_table_type;

BEGIN

EMP_PKG.read_emp_table(e_table);

FOR I IN e_table.FIRST ..e_table.LAST LOOP

DBMS_OUTPUT.PUT_LINE(e_table(i).empno||' '||e_table(i).ename);

END LOOP;

END;

--创建序列从100开始,依次增加1

CREATE SEQUENCE empseq

START WITH 100

INCREMENT BY 1

ORDER NOCYCLE;

--创建序列从100开始,依次增加10

CREATE SEQUENCE deptseq

START WITH 100

INCREMENT BY 10

ORDER NOCYCLE;

-- *******************************************

-- 创建包说明

-- 包 名：MANAGE_EMP_PKG

-- 功能描述：对员工进行管理(新增员工,新增部门

-- ,删除员工,删除部门,增加工资与奖金等)

-- 创建人员：kenny

-- 创建日期：2010-05-19

-- ******************************************

CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG

AS

--增加一名员工

FUNCTION hire_emp

(ename VARCHAR2, job VARCHAR2

, mgr NUMBER, sal NUMBER

, comm NUMBER, deptno NUMBER)

RETURN NUMBER;

--新增一个部门

FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)

RETURN NUMBER;

--删除指定员工

PROCEDURE remove_emp(empno NUMBER);

--删除指定部门

PROCEDURE remove_dept(deptno NUMBER);

--增加指定员工的工资

PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);

--增加指定员工的奖金

PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);

END MANAGE_EMP_PKG;--创建包说明结束

-- *******************************************

-- 创建包体

-- 包 名：MANAGE_EMP_PKG

-- 功能描述：对员工进行管理(新增员工,新增部门

-- ,删除员工,删除部门,增加工资与奖金等)

-- 创建人员：kenny

-- ******************************************

CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG

AS

total_emps NUMBER; --员工数

total_depts NUMBER; --部门数

no_sal EXCEPTION;

no_comm EXCEPTION;

--增加一名员工

FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER,

sal NUMBER, comm NUMBER, deptno NUMBER)

RETURN NUMBER --返回新增加的员工编号

IS

new_empno NUMBER(4);

BEGIN

SELECT empseq.NEXTVAL INTO new_empno FROM dual;

SELECT COUNT(*) INTO total_emps FROM emp;--当前记录总数

INSERT INTO emp

VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno);

total_emps:=total_emps+1;

RETURN(new_empno);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END hire_emp;

--新增一个部门

FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)

RETURN NUMBER

IS

new_deptno NUMBER(4); --部门编号

BEGIN

--得到一个新的自增的员工编号

SELECT deptseq.NEXTVAL INTO new_deptno FROM dual;

SELECT COUNT(*) INTO total_depts FROM dept;--当前部门总数

INSERT INTO dept VALUES (new_deptno, dname, loc);

total_depts:=total_depts;

RETURN(new_deptno);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END add_dept;

--删除指定员工

PROCEDURE remove_emp(empno NUMBER)

IS

no_result EXCEPTION; --自定义异常

BEGIN

DELETE FROM emp WHERE emp.empno=remove_emp.empno;

IF SQL%NOTFOUND THEN

RAISE no_result;

END IF;

total_emps:=total_emps - 1; --总的员工数减1

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END remove_emp;

--删除指定部门

PROCEDURE remove_dept(deptno NUMBER)

IS

no_result EXCEPTION; --自定义异常

exception_deptno_remaining EXCEPTION; --自定义异常

/*-2292 是违反一致性约束的错误代码*/

PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292);

BEGIN

DELETE FROM dept WHERE dept.deptno=remove_dept.deptno;

IF SQL%NOTFOUND THEN

RAISE no_result;

END IF;

total_depts:=total_depts-1; --总的部门数减1

EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN exception_deptno_remaining THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END remove_dept;

--给指定员工增加指定数量的工资

PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER)

IS

curr_sal NUMBER(7, 2); --当前工资

BEGIN

--得到当前工资

SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.empno;

IF curr_sal IS NULL THEN

RAISE no_sal;

ELSE

UPDATE emp SET sal = sal + increase_sal.sal_incr --当前工资加新增的工资

WHERE emp.empno = increase_sal.empno;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN no_sal THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的工资不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END increase_sal;

--给指定员工增加指定数量的奖金

PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER)

IS

curr_comm NUMBER(7,2);

BEGIN

--得到指定员工的当前资金

SELECT comm INTO curr_comm

FROM emp WHERE emp.empno = increase_comm.empno;

IF curr_comm IS NULL THEN

RAISE no_comm;

ELSE

UPDATE emp SET comm = comm + increase_comm.comm_incr

WHERE emp.empno=increase_comm.empno;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

WHEN no_comm THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的奖金不存在!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');

END increase_comm;

END MANAGE_EMP_PKG;--创建包体结束

--调用

SQL> variable empno number

SQL>execute :empno:= manage_emp_pkg.hire_emp('HUYONG',PM,1455,5500,14,10)

PL/SQL procedure successfully completed

empno

---------

105

-- *******************************************

-- 创建包体

-- 包 名：CURROR_VARIBAL_PKG

-- 功能描述：在包中引用游标变量

-- 创建人员：kenny

- 创建日期：2010-05-09

- ******************************************

CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS

TYPE DeptCurType IS REF CURSOR

RETURN dept%ROWTYPE; --强类型定义

TYPE CurType IS REF CURSOR;-- 弱类型定义

PROCEDURE OpenDeptVar(

Cv IN OUT DeptCurType,

Choice INTEGER DEFAULT 0,

Dept_no NUMBER DEFAULT 50,

Dept_name VARCHAR DEFAULT '%');

END;

-- *******************************************

-- 创建包体

-- 包 名：CURROR_VARIBAL_PKG

-- 功能描述：在包中引用游标变量

-- 创建人员：胡勇

-- 创建日期：2010-05-19

-- ******************************************

CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG

AS

PROCEDURE OpenDeptvar(

Cv IN OUT DeptCurType,

Choice INTEGER DEFAULT 0,

Dept_no NUMBER DEFAULT 50,

Dept_name VARCHAR DEFAULT ‘%’)

IS

BEGIN

IF choice =1 THEN

OPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no;

ELSIF choice = 2 THEN

OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_name;

ELSE

OPEN cv FOR SELECT * FROM dept;

END IF;

END OpenDeptvar;

END CURROR_VARIBAL_PKG;

--定义一个过程

CREATE OR REPLACE PROCEDURE UP_OpenCurType(

Cv IN OUT CURROR_VARIBAL_PKG.CurType,

FirstCapInTableName CHAR)

AS

BEGIN

--CURROR_VARIBAL_PKG.CurType采用弱类型定义

--所以可以使用它定义的游标变量打开不同类型的查询语句

IF FirstCapInTableName = 'D' THEN

OPEN cv FOR SELECT * FROM dept;

ELSE

OPEN cv FOR SELECT * FROM emp;

END IF;

END UP_OpenCurType;

--定义一个应用

DECLARE

DeptRec Dept%ROWTYPE;

EmpRec Emp%ROWTYPE;

Cv1 CURROR_VARIBAL_PKG.deptcurtype;

Cv2 CURROR_VARIBAL_PKG.curtype;

BEGIN

DBMS_OUTPUT.PUT_LINE('游标变量强类型定义应用');

CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30);

FETCH cv1 INTO DeptRec;

WHILE cv1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);

FETCH cv1 INTO DeptRec;

END LOOP;

CLOSE cv1;

DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用');

CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name => 'A%');

FETCH cv2 INTO DeptRec;

WHILE cv2%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);

FETCH cv2 INTO DeptRec;

END LOOP;

DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—dept表');

UP_OpenCurType(cv2, 'D');

FETCH cv2 INTO DeptRec;

WHILE cv2%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname);

FETCH cv2 INTO deptrec;

END LOOP;

DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—emp表');

UP_OpenCurType(cv2, 'E');

FETCH cv2 INTO EmpRec;

WHILE cv2%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename);

FETCH cv2 INTO emprec;

END LOOP;

CLOSE cv2;

END;

----------运行结果-------------------

游标变量强类型定义应用

10:ACCOUNTING

20:RESEARCH

30:SALES

游标变量弱类型定义应用

10:ACCOUNTING

游标变量弱类型定义应用—dept表

10:ACCOUNTING

20:RESEARCH

30:SALES

40:OPERATIONS

50:50abc

60:Developer

游标变量弱类型定义应用—emp表

7369:SMITH

7499:ALLEN

7521:WARD

7566:JONES

7654:MARTIN

7698:BLAKE

7782:CLARK

7788:SCOTT

7839:KING

7844:TURNER

7876:ADAMS

7900:JAMES

7902:FORD

7934:MILLER

PL/SQL procedure successfully completed

-- *******************************************

-- 创建包说明

-- 包 名：DEMO_PKG1

-- 功能描述：创建包对子程序重载进行测试

-- 创建人员：kenny

-- 创建日期：2010-05-22

-- ******************************************

CREATE OR REPLACE PACKAGE DEMO_PKG1

IS

DeptRec dept%ROWTYPE;

V_sqlcode NUMBER;

V_sqlerr VARCHAR2(2048);

--两个子程序名字相同,但参数类型不同

FUNCTION query_dept(dept_no IN NUMBER)

RETURN INTEGER;

FUNCTION query_dept(dept_no IN VARCHAR2)

RETURN INTEGER;

END DEMO_PKG1;

-- *******************************************

-- 创建包体

-- 包 名：DEMO_PKG1

-- 功能描述：创建包对子程序重载进行测试

-- 创建人员：kenny

-- 创建日期：2010-05-22

-- ******************************************

CREATE OR REPLACE PACKAGE BODY DEMO_PKG1

IS

FUNCTION check_dept(dept_no NUMBER)

RETURN INTEGER

IS

deptCnt INTEGER; --指定部门号的部门数量

BEGIN

SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_no;

IF deptCnt > 0 THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

END check_dept;

FUNCTION check_dept(dept_no VARCHAR2)

RETURN INTEGER

IS

deptCnt INTEGER;

BEGIN

SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_no;

IF deptCnt > 0 THEN

RETURN 1;

ELSE

RETURN 0;

END IF;

END check_dept;

FUNCTION query_dept(dept_no IN NUMBER)

RETURN INTEGER

IS

BEGIN

IF check_dept(dept_no) =1 THEN

SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;

RETURN 1;

ELSE

RETURN 0;

END IF;

END query_dept;

FUNCTION query_dept(dept_no IN VARCHAR2)

RETURN INTEGER

IS

BEGIN

IF check_dept(dept_no) =1 THEN

SELECT * INTO DeptRec FROM dept WHERE deptno = dept_no;

RETURN 1;

ELSE

RETURN 0;

END IF;

END query_dept;

END DEMO_PKG1;

WRAP INAME=<input_file_name> [ONAME=<output_file_name>]

wrap iname=e:\sample.txt

DROP PACKAGE [BODY] [user.]package_name;

DROP PROCEDURE OpenCurType; --删除存储过程

--删除我们实例中创建的各个包

DROP PACKAGE demo_pack;

DROP PACKAGE demo_pack1;

DROP PACKAGE emp_mgmt;

DROP PACKAGE emp_package;

ALTER PACKAGE package_name COMPILE [PACKAGE|BODY|SPECIFICATION];

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

特性

INSERT

UPDATE

DELETE

OLD

NULL

实际值

实际值

NEW

实际值

实际值

NULL

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER tr_del_emp

BEFORE DELETE --指定触发时机为删除操作前触发

ON scott.emp

FOR EACH ROW --说明创建的是行级触发器

BEGIN

--将修改前数据插入到日志记录表 del_emp ,以供监督使用。

INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;

CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE

ON departments

BEGIN

IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN

RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');

END IF;

END;

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = 80)

BEGIN

CASE

WHEN UPDATING ('salary') THEN

IF :NEW.salary < :old.salary THEN

RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降');

END IF;

WHEN UPDATING ('commission_pct') THEN

IF :NEW.commission_pct < :old.commission_pct THEN

RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降');

END IF;

WHEN DELETING THEN

RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录');

END CASE;

END;

/*

实例：

UPDATE employees SET salary = 8000 WHERE employee_id = 177;

DELETE FROM employees WHERE employee_id in (177,170);

*/

CREATE OR REPLACE TRIGGER tr_reg_cou

AFTER update OF region_id

ON regions

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id

||'、新的region_id值是'||:new.region_id);

UPDATE countries SET region_id = :new.region_id

WHERE region_id = :old.region_id;

END;

CREATE OR REPLACE PROCEDURE add_job_history

( p_emp_id job_history.employee_id%type

, p_start_date job_history.start_date%type

, p_end_date job_history.end_date%type

, p_job_id job_history.job_id%type

, p_department_id job_history.department_id%type

)

IS

BEGIN

INSERT INTO job_history (employee_id, start_date, end_date,

job_id, department_id)

VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);

END add_job_history;

--创建触发器调用存储过程...

CREATE OR REPLACE TRIGGER update_job_history

AFTER UPDATE OF job_id, department_id ON employees

FOR EACH ROW

BEGIN

add_job_history(:old.employee_id, :old.hire_date, sysdate,

:old.job_id, :old.department_id);

END;

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.] view_name --只能定义在视图上

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定

[WHEN condition]

PL/SQL_block | CALL procedure_name;

CREATE OR REPLACE VIEW emp_view AS

SELECT deptno, count(*) total_employeer, sum(sal) total_salary

FROM emp GROUP BY deptno;

SQL>DELETE FROM emp_view WHERE deptno=10;

DELETE FROM emp_view WHERE deptno=10

CREATE OR REPLACE TRIGGER emp_view_delete

INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete;

DELETE FROM emp_view WHERE deptno=10;

DROP TRIGGER emp_view_delete;

DROP VIEW emp_view;

CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")

AS

SELECT r.region_id,

r.region_name,

c.country_id,

c.country_name

FROM regions r,

countries c

WHERE r.region_id = c.region_id;

CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF

INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;

IF v_count = 0 THEN

INSERT INTO regions

(region_id, region_name

) VALUES

(:new.r_id, :new.r_name

);

END IF;

SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;

IF v_count = 0 THEN

INSERT

INTO countries

(

country_id,

country_name,

region_id

)

VALUES

(

:new.c_id,

:new.c_name,

:new.r_id

);

END IF;

END;

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER}

{ddl_event_list | database_event_list}

ON { DATABASE | [schema.]SCHEMA }

[WHEN condition]

PL/SQL_block | CALL procedure_name;

事件

允许的时机

说明

STARTUP

AFTER

启动数据库实例之后触发

SHUTDOWN

BEFORE

关闭数据库实例之前触发（非正常关闭不触发）

SERVERERROR

AFTER

数据库服务器发生错误之后触发

LOGON

AFTER

成功登录连接到数据库后触发

LOGOFF

BEFORE

开始断开数据库连接之前触发

CREATE

BEFORE，AFTER

在执行CREATE语句创建数据库对象之前、之后触发

DROP

BEFORE，AFTER

在执行DROP语句删除数据库对象之前、之后触发

ALTER

BEFORE，AFTER

在执行ALTER语句更新数据库对象之前、之后触发

DDL

BEFORE，AFTER

在执行大多数DDL语句之前、之后触发

GRANT

BEFORE，AFTER

执行GRANT语句授予权限之前、之后触发

REVOKE

BEFORE，AFTER

执行REVOKE语句收权限之前、之后触犯发

RENAME

BEFORE，AFTER

执行RENAME语句更改数据库对象名称之前、之后触犯发

AUDIT / NOAUDIT

BEFORE，AFTER

执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

事件属性\事件

Startup/Shutdown

Servererror

Logon/Logoff

DDL

DML

事件名称

＊

＊

＊

＊

＊

数据库名称

＊

数据库实例号

＊

错误号

＊

用户名

＊

＊

模式对象类型

＊

＊

模式对象名称

＊

＊

列

＊

函数名称

数据类型

说 明

Ora_sysevent

VARCHAR2（20）

激活触发器的事件名称

Instance_num

NUMBER

数据库实例名

Ora_database_name

VARCHAR2（50）

数据库名称

Server_error(posi)

NUMBER

错误信息栈中posi指定位置中的错误号

Is_servererror(err_number)

BOOLEAN

检查err_number指定的错误号是否在错误信息栈中，如果在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误。

Login_user

VARCHAR2(30)

登陆或注销的用户名称

Dictionary_obj_type

VARCHAR2(20)

DDL语句所操作的数据库对象类型

Dictionary_obj_name

VARCHAR2(30)

DDL语句所操作的数据库对象名称

Dictionary_obj_owner

VARCHAR2(30)

DDL语句所操作的数据库对象所有者名称

Des_encrypted_password

VARCHAR2(2)

正在创建或修改的经过DES算法加密的用户口令

DESC ora_sysevent

DESC ora_login_user

--创建用于记录事件用的表

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

event_name VARCHAR2(20),

user_name VARCHAR2(10),

obj_type VARCHAR2(20),

obj_name VARCHAR2(20));

--创建触犯发器

CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON SCHEMA

BEGIN

INSERT INTO ddl_event VALUES

(systimestamp,ora_sysevent, ora_login_user,

ora_dict_obj_type, ora_dict_obj_name);

END tr_ddl;

CREATE TABLE log_event

(user_name VARCHAR2(10),

address VARCHAR2(20),

logon_date timestamp,

logoff_date timestamp);

--创建登录触发器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logon_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--创建退出触发器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

INSERT INTO log_event (user_name, address, logoff_date)

VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logoff;

谓词

行为

INSERTING

如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE

UPDATING

如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE

DELETING

如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

DROP TRIGGER trigger_name;

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

ALTER TRIGGER emp_view_delete DISABLE;

ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

ALTER TABLE emp DISABLE ALL TRIGGERS;

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,

TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,

STATUS, ACTION_TYPE

FROM user_triggers;

CREATE TABLE dept_summary(

Deptno NUMBER(2),

Sal_sum NUMBER(9, 2),

Emp_count NUMBER);

INSERT INTO dept_summary(deptno, sal_sum, emp_count)

SELECT deptno, SUM(sal), COUNT(*)

FROM emp

GROUP BY deptno;

--创建一个PL/SQL过程disp_dept_summary

--在触发器中调用该过程显示dept_summary标中的数据。

CREATE OR REPLACE PROCEDURE disp_dept_summary

IS

Rec dept_summary%ROWTYPE;

CURSOR c1 IS SELECT * FROM dept_summary;

BEGIN

OPEN c1;

FETCH c1 INTO REC;

DBMS_OUTPUT.PUT_LINE('deptno sal_sum emp_count');

DBMS_OUTPUT.PUT_LINE('-------------------------------------');

WHILE c1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)||

To_char(rec.sal_sum, '$999,999.99')||

LPAD(rec.emp_count, 13));

FETCH c1 INTO rec;

END LOOP;

CLOSE c1;

END;

BEGIN

DBMS_OUTPUT.PUT_LINE('插入前');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig1

AFTER INSERT OR DELETE OR UPDATE OF sal ON emp

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…'');

DELETE FROM dept_summary;

INSERT INTO dept_summary(deptno, sal_sum, emp_count)

SELECT deptno, SUM(sal), COUNT(*)

FROM emp GROUP BY deptno;

END;

');

INSERT INTO dept(deptno, dname, loc)

VALUES(90, ‘demo_dept’, ‘none_loc’);

INSERT INTO emp(ename, deptno, empno, sal)

VALUES(USER, 90, 9999, 3000);

DBMS_OUTPUT.PUT_LINE('插入后');

Disp_dept_summary();

UPDATE emp SET sal=1000 WHERE empno=9999;

DBMS_OUTPUT.PUT_LINE('修改后');

Disp_dept_summary();

DELETE FROM emp WHERE empno=9999;

DELETE FROM dept WHERE deptno=90;

DBMS_OUTPUT.PUT_LINE('删除后');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’);

EXCEPTION

WHEN OTHERS THEN

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

END;

BEGIN

DBMS_OUTPUT.PUT_LINE('插入前');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT(

'CREATE OR REPLACE TRIGGER trig2_update

AFTER UPDATE OF sal ON emp

REFERENCING OLD AS old_emp NEW AS new_emp

FOR EACH ROW

WHEN (old_emp.sal != new_emp.sal)

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…'');

DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal);

DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

UPDATE dept_summary

SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

WHERE deptno = :new_emp.deptno;

END;'

);

DBMS_UTILITY.EXEC_DDL_STATEMENT(

'CREATE OR REPLACE TRIGGER trig2_insert

AFTER INSERT ON emp

REFERENCING NEW AS new_emp

FOR EACH ROW

DECLARE

I NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…'');

SELECT COUNT(*) INTO I

FROM dept_summary WHERE deptno = :new_emp.deptno;

IF I > 0 THEN

UPDATE dept_summary

SET sal_sum=sal_sum+:new_emp.sal,

Emp_count=emp_count+1

WHERE deptno = :new_emp.deptno;

ELSE

INSERT INTO dept_summary

VALUES (:new_emp.deptno, :new_emp.sal, 1);

END IF;

END;'

);

DBMS_UTILITY.EXEC_DDL_STATEMENT(

'CREATE OR REPLACE TRIGGER trig2_delete

AFTER DELETE ON emp

REFERENCING OLD AS old_emp

FOR EACH ROW

DECLARE

I NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…'');

SELECT emp_count INTO I

FROM dept_summary WHERE deptno = :old_emp.deptno;

IF I >1 THEN

UPDATE dept_summary

SET sal_sum=sal_sum - :old_emp.sal,

Emp_count=emp_count - 1

WHERE deptno = :old_emp.deptno;

ELSE

DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;

END IF;

END;'

);

INSERT INTO dept(deptno, dname, loc)

VALUES(90, 'demo_dept', 'none_loc');

INSERT INTO emp(ename, deptno, empno, sal)

VALUES(USER, 90, 9999, 3000);

INSERT INTO emp(ename, deptno, empno, sal)

VALUES(USER, 90, 9998, 2000);

DBMS_OUTPUT.PUT_LINE('插入后');

Disp_dept_summary();

UPDATE emp SET sal = sal*1.1 WHERE deptno=90;

DBMS_OUTPUT.PUT_LINE('修改后');

Disp_dept_summary();

DELETE FROM emp WHERE deptno=90;

DELETE FROM dept WHERE deptno=90;

DBMS_OUTPUT.PUT_LINE('删除后');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update');

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert');

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete');

EXCEPTION

WHEN OTHERS THEN

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

END;

BEGIN

DBMS_OUTPUT.PUT_LINE('插入前');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT(

'CREATE OR REPLACE TRIGGER trig2

AFTER INSERT OR DELETE OR UPDATE OF sal

ON emp

REFERENCING OLD AS old_emp NEW AS new_emp

FOR EACH ROW

DECLARE

I NUMBER;

BEGIN

IF UPDATING AND :old_emp.sal != :new_emp.sal THEN

DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…'');

DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal);

DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal);

UPDATE dept_summary

SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal

WHERE deptno = :new_emp.deptno;

ELSIF INSERTING THEN

DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');

SELECT COUNT(*) INTO I

FROM dept_summary

WHERE deptno = :new_emp.deptno;

IF I > 0 THEN

UPDATE dept_summary

SET sal_sum=sal_sum+:new_emp.sal,

Emp_count=emp_count+1

WHERE deptno = :new_emp.deptno;

ELSE

INSERT INTO dept_summary

VALUES (:new_emp.deptno, :new_emp.sal, 1);

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');

SELECT emp_count INTO I

FROM dept_summary WHERE deptno = :old_emp.deptno;

IF I > 1 THEN

UPDATE dept_summary

SET sal_sum=sal_sum - :old_emp.sal,

Emp_count=emp_count - 1

WHERE deptno = :old_emp.deptno;

ELSE

DELETE FROM dept_summary

WHERE deptno = :old_emp.deptno;

END IF;

END IF;

END;'

);

INSERT INTO dept(deptno, dname, loc)

VALUES(90, 'demo_dept', 'none_loc');

INSERT INTO emp(ename, deptno, empno, sal)

VALUES(USER, 90, 9999, 3000);

INSERT INTO emp(ename, deptno, empno, sal)

VALUES(USER, 90, 9998, 2000);

DBMS_OUTPUT.PUT_LINE('插入后');

Disp_dept_summary();

UPDATE emp SET sal = sal*1.1 WHERE deptno=90;

DBMS_OUTPUT.PUT_LINE('修改后');

Disp_dept_summary();

DELETE FROM emp WHERE deptno=90;

DELETE FROM dept WHERE deptno=90;

DBMS_OUTPUT.PUT_LINE('删除后');

Disp_dept_summary();

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2');

EXCEPTION

WHEN OTHERS THEN

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

END;

DECLARE

No NUMBER;

Name VARCHAR2(20);

BEGIN

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE VIEW myview AS

SELECT empno, ename, ''E'' type FROM emp

UNION

SELECT dept.deptno, dname, ''D'' FROM dept

');

-- 创建INSTEAD OF 触发器trigger3;

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig3

INSTEAD OF INSERT ON myview

REFERENCING NEW n

FOR EACH ROW

DECLARE

Rows INTEGER;

BEGIN

DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…'');

IF :n.type = ''D'' THEN

SELECT COUNT(*) INTO rows

FROM dept WHERE deptno = :n.empno;

IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…'');

INSERT INTO dept(deptno, dname, loc)

VALUES (:n.empno, :n.ename, ''none’’);

ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

''的部门已存在，插入操作失败！'');

END IF;

ELSE

SELECT COUNT(*) INTO rows

FROM emp WHERE empno = :n.empno;

IF rows = 0 THEN

DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’);

INSERT INTO emp(empno, ename)

VALUES(:n.empno, :n.ename);

ELSE

DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||

''的人员已存在，插入操作失败!'');

END IF;

END IF;

END;

');

INSERT INTO myview VALUES (70, 'demo', 'D');

INSERT INTO myview VALUES (9999, USER, 'E');

SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;

DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name);

SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;

DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name);

DELETE FROM emp WHERE empno=9999;

DELETE FROM dept WHERE deptno=70;

DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');

END;

BEGIN

-- 创建用于记录事件日志的数据表

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE TABLE eventlog(

Eventname VARCHAR2(20) NOT NULL,

Eventdate date default sysdate,

Inst_num NUMBER NULL,

Db_name VARCHAR2(50) NULL,

Srv_error NUMBER NULL,

Username VARCHAR2(30) NULL,

Obj_type VARCHAR2(20) NULL,

Obj_name VARCHAR2(30) NULL,

Obj_owner VARCHAR2(30) NULL

)

');

-- 创建DDL触发器trig4_ddl

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig4_ddl

AFTER CREATE OR ALTER OR DROP

ON DATABASE

DECLARE

Event VARCHAR2(20);

Typ VARCHAR2(20);

Name VARCHAR2(30);

Owner VARCHAR2(30);

BEGIN

-- 读取DDL事件属性

Event := SYSEVENT;

Typ := DICTIONARY_OBJ_TYPE;

Name := DICTIONARY_OBJ_NAME;

Owner := DICTIONARY_OBJ_OWNER;

--将事件属性插入到事件日志表中

INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)

VALUES(event, typ, name, owner);

END;

');

-- 创建LOGON、STARTUP和SERVERERROR 事件触发器

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig4_after

AFTER LOGON OR STARTUP OR SERVERERROR

ON DATABASE

DECLARE

Event VARCHAR2(20);

Instance NUMBER;

Err_num NUMBER;

Dbname VARCHAR2(50);

User VARCHAR2(30);

BEGIN

Event := SYSEVENT;

IF event = ''LOGON'' THEN

User := LOGIN_USER;

INSERT INTO eventlog(eventname, username)

VALUES(event, user);

ELSIF event = ''SERVERERROR'' THEN

Err_num := SERVER_ERROR(1);

INSERT INTO eventlog(eventname, srv_error)

VALUES(event, err_num);

ELSE

Instance := INSTANCE_NUM;

Dbname := DATABASE_NAME;

INSERT INTO eventlog(eventname, inst_num, db_name)

VALUES(event, instance, dbname);

END IF;

END;

');

-- 创建LOGOFF和SHUTDOWN 事件触发器

DBMS_UTILITY.EXEC_DDL_STATEMENT('

CREATE OR REPLACE TRIGGER trig4_before

BEFORE LOGOFF OR SHUTDOWN

ON DATABASE

DECLARE

Event VARCHAR2(20);

Instance NUMBER;

Dbname VARCHAR2(50);

User VARCHAR2(30);

BEGIN

Event := SYSEVENT;

IF event = ''LOGOFF'' THEN

User := LOGIN_USER;

INSERT INTO eventlog(eventname, username)

VALUES(event, user);

ELSE

Instance := INSTANCE_NUM;

Dbname := DATABASE_NAME;

INSERT INTO eventlog(eventname, inst_num, db_name)

VALUES(event, instance, dbname);

END IF;

END;

');

END;

CREATE TABLE mydata(mydate NUMBER);

CONNECT SCOTT/TIGER

COL eventname FORMAT A10

COL eventdate FORMAT A12

COL username FORMAT A10

COL obj_type FORMAT A15

COL obj_name FORMAT A15

COL obj_owner FORMAT A10

SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error

FROM eventlog;

DROP TRIGGER trig4_ddl;

DROP TRIGGER trig4_before;

DROP TRIGGER trig4_after;

DROP TABLE eventlog;

DROP TABLE mydata;

CREATE TABLE audit_table(

Audit_id NUMBER,

User_name VARCHAR2(20),

Now_time DATE,

Terminal_name VARCHAR2(10),

Table_name VARCHAR2(10),

Action_name VARCHAR2(10),

Emp_id NUMBER(4));

CREATE TABLE audit_table_val(

Audit_id NUMBER,

Column_name VARCHAR2(10),

Old_val NUMBER(7,2),

New_val NUMBER(7,2));

CREATE SEQUENCE audit_seq

START WITH 1000

INCREMENT BY 1

NOMAXVALUE

NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

DECLARE

Time_now DATE;

Terminal CHAR(10);

BEGIN

Time_now:=sysdate;

Terminal:=USERENV('TERMINAL');

IF INSERTING THEN

INSERT INTO audit_table

VALUES(audit_seq.NEXTVAL, user, time_now,

terminal, 'EMP', 'INSERT', :new.empno);

ELSIF DELETING THEN

INSERT INTO audit_table

VALUES(audit_seq.NEXTVAL, user, time_now,

terminal, 'EMP', 'DELETE', :old.empno);

ELSE

INSERT INTO audit_table

VALUES(audit_seq.NEXTVAL, user, time_now,

terminal, 'EMP', 'UPDATE', :old.empno);

IF UPDATING('SAL') THEN

INSERT INTO audit_table_val

VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal);

ELSE UPDATING('DEPTNO')

INSERT INTO audit_table_val

VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno);

END IF;

END IF;

END;

CREATE SEQUENCE update_sequence

INCREMENT BY 1

START WITH 1000

MAXVALUE 5000 CYCLE;

ALTER TABLE emp

ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS

Updateseq NUMBER;

END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS

END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1

BEFORE UPDATE OF deptno ON dept

DECLARE

Dummy NUMBER;

BEGIN

SELECT update_sequence.NEXTVAL INTO dummy FROM dual;

Integritypackage.updateseq:=dummy;

END;

CREATE OR REPLACE TRIGGER dept_cascade2

AFTER DELETE OR UPDATE OF deptno ON dept

FOR EACH ROW

BEGIN

IF UPDATING THEN

UPDATE emp SET deptno=:new.deptno,

update_id=integritypackage.updateseq

WHERE emp.deptno=:old.deptno AND update_id IS NULL;

END IF;

IF DELETING THEN

DELETE FROM emp

WHERE emp.deptno=:old.deptno;

END IF;

END;

CREATE OR REPLACE TRIGGER dept_cascade3

AFTER UPDATE OF deptno ON dept

BEGIN

UPDATE emp SET update_id=NULL

WHERE update_id=integritypackage.updateseq;

END;

SELECT * FROM EMP ORDER BY DEPTNO;

UPDATE dept SET deptno=25 WHERE deptno=20;

CREATE TABLE company_holidays(day DATE);

INSERT INTO company_holidays

VALUES(sysdate);

INSERT INTO company_holidays

VALUES(TO_DATE('21-10月-01', 'DD-MON-YY'));

CREATE OR REPLACE TRIGGER emp_permit_change

BEFORE INSERT OR DELETE OR UPDATE ON emp

DECLARE

Dummy NUMBER;

Not_on_weekends EXCEPTION;

Not_on_holidays EXCEPTION;

Not_working_hours EXCEPTION;

BEGIN

/* check for weekends */

IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六', '星期日') THEN

RAISE not_on_weekends;

END IF;

/* check for company holidays */

SELECT COUNT(*) INTO dummy FROM company_holidays

WHERE TRUNC(day)=TRUNC(SYSDATE);

IF dummy >0 THEN

RAISE not_on_holidays;

END IF;

/* check for work hours(8:00 AM to 18:00 PM */

IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18) THEN

RAISE not_working_hours;

END IF;

EXCEPTION

WHEN not_on_weekends THEN

RAISE_APPLICATION_ERROR(-20324,

'May not change employee table during the weekends');

WHEN not_on_holidays THEN

RAISE_APPLICATION_ERROR(-20325,

'May not change employee table during a holiday');

WHEN not_working_hours THEN

RAISE_APPLICATION_ERROR(-20326,

'May not change employee table during no_working hours');

END;