欢迎来到study-hard-forever的博客

oracle数据库实验报告三

下面为数据库实验的一些报告（oracle）（第十周之后）

可直接下载资源：https://download.csdn.net/download/qq_42785226/14622190

这些报告每次大概是一节课的时间，由于课程原因，很多内容都简化了，知识内容也可能相对片面一些，并不全面，只作练习参考使用。

csdn不允许文字长度过长，这里没办法只好拆成三份。

数据库实验报告

数据库实验报告 1

数据库实验第四周 5

一、准备工作： 5

SQL*Plus创建公共用户C##scott: 5

二、实验阶段（SQL题目练习）： 7

1、列出至少有一个员工的所有部门。 8

2、列出薪金比“SMITH”多的所有员工。 8

3、列出所有员工的姓名及其直接上级的姓名。 9

4、列出受雇日期早于其直接上级的所有员工。 10

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 10

6、列出所有“CLERK”(办事员)的姓名及其部门名称。 11

7、列出最低薪金大于1500的各种工作。 12

8、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。 12

9、列出薪金高于公司平均薪金的所有员工。 12

10、列出与“SCOTT”从事相同工作的所有员工。 13

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 13

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13

14、列出所有员工的姓名、部门名称和工资。 16

15、列出所有部门的详细信息和部门人数。 17

16、列出各种工作的最低工资。 17

17、列出各个部门的MANAGER(经理)的最低薪金。 17

数据库实验第五周 18

Dual操作（DESC，查询内容，日期（格式），作计算器等）： 18

临时变量&： 19

模拟merge并查看结果： 20

扩展update（Returning）： 21

Select ..as..： 23

Dual查询user、日期（带特定格式）、生成随机数： 24

定义（declare）（常量、变量）、赋值（:=）操作练习： 24

IF、 ELSIF、 ENDIF练习（注意为：ELSIF）： 25

Case语句练习： 26

循环语句（LOOP——EXIT;）练习： 26

数据库实验第六周 27

导入表：SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp; 27

黑屏下查询归档文件信息：SQL> conn /as sysdba; SQL> ArCHIVE Log list; 28

查询表信息：SQL> select * from cat; 28

查询用户信息：SQL> select * from dba_users; 33

查询数据文件信息dba_data_files:select * from dba_data_files; 36

select * from v$datafile; 37

查询归档文件信息：select name,log_mode from v$database; 38

查询控制文件信息：select * from v$controlfile; 38

查询日志文件信息：select * from v$log; 38

select * from v$logfile; 39

按组添加日志文件LOGFILE并作出相关查询：ALTER DATABASE ADD LOGFILE GROUP 4('log1a.rdo','log2a.rdo')size 5000k; 39

按成员MEMBER添加日志文件LOGFILE到组GROUP并作出相关查询：ALTER DATABASE ADD LOGFILE MEMBER 'log3a.log' TO Group 4; 40

按成员删除日志文件：ALTER DATABASE DROP LOGFILE MEMBER 'log3a.log'; 40

按组删除日志文件：ALTER DATABASE DROP LOGFILE Group 4; 41

创建表空间： 41

数据库实验第八周 42

查询数据块大小：SHOW PARAMETER db_block_size; 42

查询用户分区信息：select * from user_extents; 42

归档模式与非归档模式： 42

ARCHIVE LOG LIST;黑屏模式下进行归档模式查询 42

SQL> SELECT name,log_mode FROM v$database; 43

以下四步将数据库从非归档模式转化为归档模式（黑屏下）： 43

可能出现的错误： 43

数据库实验第九周 45

/*分支结构1*/ 45

/*分支结构2*/ 45

/*带临时变量的多分支结构*/ 46

/*多分支case语句*/ 47

1.简单型case 47

2.搜索性case 47

3.嵌入到select语句执行复杂任务的case 47

4.嵌入到PL/SQL程序语句（如赋值语句）的case 47

/*简单型case*/ 47

/*等值比较的case语句*/ 48

/*搜索case表达式*/ 48

/*嵌入到select语句执行复杂任务的case*/ 48

/*将上述例子的结果以表的形式保存起来*/ 49

/*case的select练习*/ 49

/*对于学生借阅的图书信息，检验图书是否过期 49

3.没有借阅图书*/ 49

--日期简单练习： 49

select to_char(months_between(sysdate,to_date('20151001','yyyymmdd'))) from dual; 50

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 50

select trunc(sysdate-to_date('20181001','yyyymmdd'))天数 from dual; 50

/*嵌入到pl/sql程序语句（如赋值语句）的case*/ 50

数据库实验第十周 57

创建分区表： 57

查询分区表： 58

SQL> select * from part_book1; 58

SQL> select * from part_book1 partition(part1); 58

SQL> select * from part_book1 partition(part2); 58

SQL>select * from dba_part_tables; 58

SQL> select * from dba_part_tables where table_name='PART_BOOK1'; 59

修改分区表： 60

SQL> Alter table part_book1 add partition part4 values('北京邮电出版社') tablespace system; 60

查询图书是否过期及应缴金额（select--case查询） 60

查询优良等级（select--case查询） 61

循环练习——exit when+for逆序循环（for count in reserve count_1..count_10） 61

while和for循环练习（1+2+3=6） 62

数据库实验第十一周 68

创建表空间： 68

查询数据文件： 70

在删除时将表空间中的内容和数据文件全部删除： 74

创建临时表空间tmptbs： 74

创建大文件表空间： 74

创建撤销表空间： 74

表空间和数据文件的维护： 74

将表空间test1设置为脱机状态： 74

更改表空间名字： 75

在现有表空间基础上添加一数据文件： 75

对已创建的表空间中已有的数据文件的管理： 75

移动表空间中的数据文件： 75

读写状态修改： 76

将表空间table2设置为只读表空间 76

将表空间table2设置为可读写状态： 76

数据库实验第十二周 76

不带参数的显式游标举例： 76

游标的%isopen 属性练习： 77

带 return和参数传递的游标： 77

一、利用while循环检索游标 79

二、利用for循环检索游标 79

使用游标分别遍历xs表中的xh,zxf： 80

利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资，若平均工资大于2000,则输出“该部门平均工资较高。” 81

利用FOR循环统计并输出各个部门的平均工资。 82

数据库实验第十三周 83

带update的游标，loop EXIT WHEN -end loop;Scott.emp表，利用游标，给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新” 83

for update写法： 84

for循环 FOR v_emp IN c_emp LOOP -END LOOP;修改scott.emp表员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。 85

修改emp表的工资，工资不足1000的，调整为1500，工资高于1000的，调整为原来工资的1.5倍，调整后，若工资〉10000，则设其为10000。 86

游标变量： 87

数据库实验第十四周 88

存储过程样例： 88

存储过程练习： 89

数据库实验第十五周 92

创建触发器（在删除xs表中的数据时进行备份）： 92

将对xs表的操作记录（insert，delete，update）在sql_info表中记录下来： 92

创建一个触发器，输出对scott.emp表的操作（插入后员工号和员工名，更新后员工工资，删除的员工号和员工名。）： 93

当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门剩余的人数(游标)： 94

数据库实验第十六周 95

创建一个存储过程，以部门号为该存储过程的in类型参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历游标)： 95

用存储过程进行模糊查找，如查找ename中包含L的雇员信息： 96

rownum练习： 96

补充：见存储过程需要注意的问题，异常处理与触发器补充 97

补充内容 98

循环结构： 98

求10的阶乘： 98

水仙花数： 102

触发器补充： 103

设置系统触发器： 103

创建logon触发器，在登录的时候进行记录： 103

创建logoff触发器，在退出登录的时候进行记录（注意此时的触发器时间上不要采用after，因为采用after客户机注销进程终止，不能进行其他操作）: 105

设置触发器：作用为禁止在休息日（周六、周天）改变scott.emp雇员信息（包括添加删除和修改）。 107

启动和关闭触发器 108

存储过程需要注意的一些问题 108

异常处理（系统异常处理以及自定义异常处理） 113

数据库实验第十一周

创建表空间：

一个表空间带多个数据文件，test1.dbf不支持自动扩展test2.dbf支持自动扩展，每次增加1000k，表空间区管理为本地管理，指定大小1M，段设置为自动管理。

SQL> create tablespace table1

2 datafile 'c:test1.dbf' size 2M autoextend off,

3 'c:test2.dbf' size 5M autoextend on next 1000k

4 extent management local

5 uniform size 1M

6 segment space management auto;

Tablespace created

一个表空间带多个数据文件，test3.dbf和test4.dbf，表空间区管理为本地管理管理，段设置为自动管理。

SQL> create tablespace table2

2 datafile 'c:test3.dbf' size 2M,

3 'c:test4.dbf' size 5M

4 extent management local autoallocate

5 segment space management auto;

Tablespace created

SQL> select * from v$datafile;

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

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

1 11 2007/4/17 3:3 0 1 SYSTEM READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 503316480 61440 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0

2 519918 2007/4/17 6:0 1 2 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 73400320 8960 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0

3 5554 2007/4/17 3:3 2 3 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 272629760 33280 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0

4 9202 2007/4/17 3:3 4 4 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 5242880 640 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0

5 546572 2020/9/6 11:0 6 5 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 0 0 104857600 12800 104857600 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE 0

6 1021166 2020/11/13 17 7 6 ONLINE READ WRITE 1021170 2020/11/13 17:0 0 0 0 2097152 256 2097152 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1.DBF 0 8192 NONE 0

7 1021169 2020/11/13 17 7 7 ONLINE READ WRITE 1021170 2020/11/13 17:0 0 0 0 5242880 640 5242880 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2.DBF 0 8192 NONE 0

8 1021775 2020/11/13 17 8 8 ONLINE READ WRITE 1021779 2020/11/13 17:2 0 0 0 2097152 256 2097152 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST3.DBF 0 8192 NONE 0

9 1021778 2020/11/13 17 8 9 ONLINE READ WRITE 1021779 2020/11/13 17:2 0 0 0 5242880 640 5242880 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST4.DBF 0 8192 NONE 0

9 rows selected

SQL>

SQL> create tablespace table3

2 datafile 'c:test5.dbf' size 2M,

3 'c:test6.dbf' size 5M

4 extent management local autoallocate

5 segment space management auto;

Tablespace created

查询数据文件：

SQL> select * from v$datafile;

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME

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

1 11 2007/4/17 3:3 0 1 SYSTEM READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 503316480 61440 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0

2 519918 2007/4/17 6:0 1 2 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 73400320 8960 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0

3 5554 2007/4/17 3:3 2 3 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 272629760 33280 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0

4 9202 2007/4/17 3:3 4 4 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 521802 521803 2020/9/6 11 5242880 640 0 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0

5 546572 2020/9/6 11:0 6 5 ONLINE READ WRITE 1014748 2020/11/13 15:4 0 0 0 104857600 12800 104857600 8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE 0

6 1021166 2020/11/13 17 7 6 ONLINE READ WRITE 1021170 2020/11/13 17:0 0 0 0 2097152 256 2097152 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST1.DBF 0 8192 NONE 0

7 1021169 2020/11/13 17 7 7 ONLINE READ WRITE 1021170 2020/11/13 17:0 0 0 0 5242880 640 5242880 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST2.DBF 0 8192 NONE 0

8 1021775 2020/11/13 17 8 8 ONLINE READ WRITE 1021779 2020/11/13 17:2 0 0 0 2097152 256 2097152 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST3.DBF 0 8192 NONE 0

9 1021778 2020/11/13 17 8 9 ONLINE READ WRITE 1021779 2020/11/13 17:2 0 0 0 5242880 640 5242880 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST4.DBF 0 8192 NONE 0

10 1021995 2020/11/13 17 9 10 ONLINE READ WRITE 1021999 2020/11/13 17:3 0 0 0 2097152 256 2097152 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST5.DBF 0 8192 NONE 0

11 1021998 2020/11/13 17 9 11 ONLINE READ WRITE 1021999 2020/11/13 17:3 0 0 0 5242880 640 5242880 8192 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TEST6.DBF 0 8192 NONE 0

11 rows selected

SQL>

SQL> create tablespace table4

2 datafile 'c:/test5.dbf' size 2M,

3 'c:/test6.dbf' size 5M

4 extent management local autoallocate

5 segment space management auto;

Tablespace created

经过上述一系列操作后表空间创建结果如下：

（注意在创建表空间时，我们所规定的dbf文件所在位置的不同：如果我们路径名位置写错了（找不到路径），将会存在默认的路径位置。例如：

datafile 'c:test5.dbf' size 2M,

'c:test6.dbf' size 5M…

与datafile 'c:/test5.dbf' size 2M,

'c:/test6.dbf' size 5M

上面那个路径没有加/导致路径出错，放在默认位置

删除表空间（先创建后删除）：

SQL>create table t1

2 (no int)

3 tablespace ts1;

Table created

在删除时将表空间中的内容和数据文件全部删除：

SQL> drop tablespace ts1 including contents and datafiles;

Tablespace dropped

创建临时表空间tmptbs：

SQL> create temporary tablespace tmptbs

2 tempfile 'c:\tmptbs.dbf'

3 size 2m reuse

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;

Tablespace created

创建大文件表空间：

（由于文件过大，不在机器上实验，以免造成空间浪费或者资源调度空间不够导致宕机）：

create bigfile tablespace bigtbs

datafile 'c:\bigtbs.dbf' size 10G;

创建撤销表空间：

SQL> create undo tablespace undotbs01

2 datafile 'c:\undotbs02.dbf' size 2m reuse;

Tablespace created

表空间和数据文件的维护：

将表空间test1设置为脱机状态：

SQL> Alter tablespace test1 OFFLINE;

Tablespace altered

更改表空间名字：

有的时候在online状态下是不允许改名的，显示该文件正在使用，这时就可以先将表空间状态修改为offline状态之后再进行改名：

SQL> alter tablespace test1 rename to t2;

Tablespace altered

在现有表空间基础上添加一数据文件：

SQL> Alter tablespace t2

2 add datafile 'c:\101.dbf' size 1m

3 reuse;

Tablespace altered

对已创建的表空间中已有的数据文件的管理：

SQL> Alter database orcl datafile 'c:\101.dbf' resize 2m;

Database altered

移动表空间中的数据文件：

（四步：修改表空间状态为offline，物理方式手动移动，将表空间中文件的原名称和路径修改为新的路径和名称（在这里可以实现改名），修改表空间的状态为online）。

SQL> alter tablespace table2 offline ;

Tablespace altered

物理方式手动移动数据文件。

SQL> alter tablespace table2 rename datafile 'c:\test3.dbf' to 'd:\t3.dbf';

Tablespace altered

SQL> alter tablespace table2 online ;

Tablespace altered

读写状态修改：

将表空间table2设置为只读表空间

SQL> alter tablespace table2 read only;

Tablespace altered

将表空间table2设置为可读写状态：

在设置之前table2应该为只读状态，否则此时无法设置为读写。

SQL> alter tablespace table2 read write;

Tablespace altered

数据库实验第十二周

不带参数的显式游标举例：

SQL> declare

2 cursor my_cursor

3 is select xh from xs;

4 v_xh xs.xh%type;

5 begin

6 open my_cursor;

7 fetch my_cursor into v_xh;

8 dbms_output.put_line(v_xh);

9 dbms_output.put_line(my_cursor%rowcount);

10 Close my_cursor;

11 exception

12 when others then

13 dbms_output.put_line(sqlcode||sqlerrm);

14 end;

15 /

061101

1

PL/SQL procedure successfully completed

SQL> select xh from xs;

XH

------

061101

101112

001

121112

游标的%isopen 属性练习：

SQL> declare

2 cursor c_1 is select * from xs;

3 v_1 c_1%rowtype;

4 begin

5 if c_1%isopen=false then

6 open c_1;

7 end if;

8 fetch c_1 into v_1;

9 dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);

10 close c_1;

11 end;

12 /

061101王林50

PL/SQL procedure successfully completed

SQL> select * from xs;

XH XM ZYM XB CSSJ ZXF BZ

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

061101 王林计算机男 1986/2/10 50

101112 李明计算机男 1986/1/30 36

001 张琼计算机 45 三好学生

121112 王小二计算机男 1986/1/30 36

SQL>

带 return和参数传递的游标：

2 DECLARE

3 TYPE emp_record_type IS RECORD(

4 f_name scott.emp.ename%TYPE,

5 h_date scott.emp.hiredate%TYPE);

6 v_1 EMP_RECORD_TYPE;

7 CURSOR c3(v_deptno NUMBER,v_job VARCHAR2)

8 --声明游标,有参数有返回值

9 RETURN EMP_RECORD_TYPE

10 IS

11 SELECT ename, hiredate FROM scott.emp

12 WHERE deptno=v_deptno AND job =v_job;

13 BEGIN

14 OPEN c3(v_job=>'MANAGER', v_deptno=>10);

15 --打开游标,传递参数值

16 LOOP

17 FETCH c3 INTO v_1; --提取游标

18 IF c3%FOUND THEN

19 DBMS_OUTPUT.PUT_LINE(v_1.f_name||'的雇佣日期是' ||v_1.h_date);

20 ELSE

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

22 EXIT;

23 END IF;

24 END LOOP;

25 CLOSE c3; --关闭游标

26 END;

27 /

CLARK的雇佣日期是09-6月 -81

已经处理完结果集了

PL/SQL procedure successfully completed

SQL> SELECT ename, hiredate FROM scott.emp WHERE deptno>10 AND job='MANAGER';

ENAME HIREDATE

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

JONES 1981/4/2

BLAKE 1981/5/1

SQL> SELECT ename, hiredate FROM scott.emp WHERE deptno>=10 AND job>='MANAGER';

ENAME HIREDATE

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

ALLEN 1981/2/20

WARD 1981/2/22

JONES 1981/4/2

MARTIN 1981/9/28

BLAKE 1981/5/1

CLARK 1981/6/9

KING 1981/11/17

TURNER 1981/9/8

8 rows selected

/*

一、利用while循环检索游标

DECLARE

CURSOR cursor_name IS SELECT…;

BEGIN

OPEN cursor_name;

FETCH…INTO…;

WHILE cursor_name%FOUND

LOOP

……

FETCH…INTO…;

END LOOP;

CLOSE cursor;

END;

注：在打开游标后用fetch语句先取一行到变量，然后再用while对该游标进行判断，而不是打开后就立即用while进行判断。

*/

/*

二、利用for循环检索游标

系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。

系统自动打开游标，不用显式地使用OPEN语句打开；

系统重复地自动从游标工作区中fetch数据并放入计数器变量中。

系统自动进行%FOUND属性检查以确定是否有数据

当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。

DECLARE

CURSOR cursor_name IS SELECT…;

BEGIN

FOR loop_variable IN 游标名称

LOOP

……

END LOOP;

END;

*/

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as system@ORCL

SQL> set serveroutput on;

SQL> select * from xs;

XH XM ZYM XB CSSJ ZXF BZ

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

061101 王林计算机男 1986/2/10 50

101112 李明计算机男 1986/1/30 36

001 张琼计算机 45 三好学生

121112 王小二计算机男 1986/1/30 36

使用游标分别遍历xs表中的xh,zxf：

2 DECLARE

3 v_xh char(6);

4 v_zxf number(2);

5 /* 定义游标is select from */

6 CURSOR XS_CUR3

7 IS SELECT XH,ZXF FROM XS;

8 BEGIN

9 /* 打开游标 */

10 OPEN XS_CUR3;

11 /* 游标赋值fetch into */

12 FETCH XS_CUR3 INTO v_xh,v_zxf;

13 /*游标遍历while found条件*/

14 WHILE XS_CUR3%FOUND

15 LOOP

16 dbms_output.put_line('学号： '||v_xh||' 总学分：'||v_zxf);

17 FETCH XS_CUR3 INTO v_xh,v_zxf;

18 END LOOP;

19 /* 遍历执行完毕，关闭游标 */

20 CLOSE XS_CUR3;

21 END;

22 /

学号： 061101 总学分：50

学号： 101112 总学分：36

学号： 001 总学分：45

学号： 121112 总学分：36

PL/SQL procedure successfully completed

利用游标WHILE循环统计并输出scott.emp表各个部门的平均工资 ， 若平均工资大于2000,则输出“该部门平均工资较高。”

3 DECLARE

4 CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

5 v_dept c_dept_stat%ROWTYPE;

6 BEGIN

7 OPEN c_dept_stat;

8 FETCH c_dept_stat INTO v_dept;

9 WHILE c_dept_stat%FOUND LOOP

10 DBMS_OUTPUT.PUT_LINE('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));

11 if (v_dept.avgsal>=2000) then

12 dbms_output.put_line(v_dept.deptno||'号部门工资较高');

13 end if;

14 FETCH c_dept_stat INTO v_dept;

15 END LOOP;

16 CLOSE c_dept_stat;

17 END;

18 /

部门号为30 平均工资为1566.6

部门号为20 平均工资为2175

20号部门工资较高

部门号为10 平均工资为2916.6

10号部门工资较高

PL/SQL procedure successfully completed

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

SQL> SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

DEPTNO AVGSAL

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

30 1566.66666

20 2175

10 2916.66666

SQL>

利用FOR循环统计并输出各个部门的平均工资。

2 DECLARE

3 CURSOR c_1 IS SELECT deptno,avg(sal) avgsal FROM scott.emp GROUP BY deptno;

4 V_dept c_1%ROWTYPE;

5 BEGIN

6 FOR v_dept IN c_1

7 LOOP

8 DBMS_OUTPUT.PUT_LINE('部门号： '||v_dept.deptno||' 平均工资： '||v_dept.avgsal);

9 END LOOP;

10 END;

11 /

部门号： 30 平均工资： 1566.666666666666666666666666666666666667

部门号： 20 平均工资： 2175

部门号： 10 平均工资： 2916.666666666666666666666666666666666667

PL/SQL procedure successfully completed

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as system@ORCL

SQL> conn system/test

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as system

SQL> set serveroutput on;

SQL>

数据库实验第十三周

带update的游标，loop EXIT WHEN -end loop;Scott.emp表，利用游标，给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”

4 DECLARE

5 v_empno scott.emp.empno%TYPE;

6 v_sal scott.emp.sal%TYPE;

7 CURSOR c_cursor IS SELECT empno,sal FROM scott.emp;

8 BEGIN

9 --打开游标

10 OPEN c_cursor;

11 --循环遍历操作 loop-end loop;

12 LOOP

13 FETCH c_cursor INTO v_empno, v_sal;

14 --exit when %notfound

15 EXIT WHEN c_cursor%NOTFOUND;

16 --查询条件，满足则执行update操作 if-end if;

17 IF v_sal<=1200 THEN

18 UPDATE scott.emp SET Sal=Sal+50 WHERE empno=v_empno;

19 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');

20 END IF;

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

22 END LOOP;

23 CLOSE c_cursor;

24 END;

25 /

编码为7369工资已更新!

记录数:1

记录数:2

记录数:3

记录数:4

记录数:5

记录数:6

记录数:7

记录数:8

记录数:9

记录数:10

编码为7876工资已更新!

记录数:11

编码为7900工资已更新!

记录数:12

记录数:13

记录数:14

PL/SQL procedure successfully completed

SQL>

for update写法：

SQL> DECLARE

2 v_empno scott.emp.empno%TYPE;

3 v_sal scott.emp.sal%TYPE;

4 --定义游标的时候标明update： for update

5 CURSOR c_cursor IS SELECT empno,sal FROM scott.emp where sal<1200 for update;

6 begin

7 open c_cursor;

8 LOOP

9 FETCH c_cursor INTO v_empno, v_sal;

10 EXIT WHEN c_cursor%NOTFOUND;

11 --不再记录员工编号进行等值查询，而是查找当前游标所在的那一条位置： where current of c_cursor

12 UPDATE scott.emp SET Sal=Sal+50 WHERE current of c_cursor;

13 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');

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

15 END LOOP;

16 CLOSE c_cursor;

17 END;

18 /

编码为7369工资已更新!

记录数:1

编码为7900工资已更新!

记录数:2

PL/SQL procedure successfully completed

SQL>

for循环 FOR v_emp IN c_emp LOOP -END LOOP;修改scott.emp表员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。

3 DECLARE

4 CURSOR c_emp IS SELECT * FROM scott.emp FOR UPDATE;

5 v_zl NUMBER;

6 v_emp c_emp%rowtype;

7 BEGIN

8 FOR v_emp IN c_emp LOOP

9 CASE v_emp.deptno

10 WHEN 10 THEN v_zl:=100;

11 WHEN 20 THEN v_zl:=150;

12 WHEN 30 THEN v_zl:=200;

13 ELSE v_zl:=250;

14 END CASE;

15 UPDATE scott.emp SET sal=sal+v_zl WHERE CURRENT OF c_emp;

16 END LOOP;

17 END;

18 /

PL/SQL procedure successfully completed

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 1150.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1800.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1450.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 3125.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1450.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 3050.00 30

7782 CLARK MANAGER 7839 1981/6/9 2550.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3150.00 20

7839 KING PRESIDENT 1981/11/17 5100.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1700.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1400.00 20

7900 JAMES CLERK 7698 1981/12/3 1350.00 30

7902 FORD ANALYST 7566 1981/12/3 3150.00 20

7934 MILLER CLERK 7782 1982/1/23 1400.00 10

14 rows selected

SQL>

修改emp表的工资，工资不足1000的，调整为1500，工资高于1000的，调整为原来工资的1.5倍，调整后，若工资〉10000，则设其为10000。

3 declare

4 cursor c_1 is

5 select empno,sal from scott.emp

6 for update of sal nowait;

7 v_sal scott.emp.sal%type;

8 begin

9 for cursor_1 in c_1

10 loop

11 if cursor_1.sal<=1000 then

12 v_sal:=1500;

13 else

14 v_sal:=cursor_1.sal*1.5;

15 if v_sal>10000 then

16 v_sal:=10000;

17 end if;

18 end if;

19 update scott.emp set sal=v_sal where current of c_1;

20 end loop;

21 end;

22 /

PL/SQL procedure successfully completed

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 1725.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 2700.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 2175.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 4687.50 20

7654 MARTIN SALESMAN 7698 1981/9/28 2175.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 4575.00 30

7782 CLARK MANAGER 7839 1981/6/9 3825.00 10

7788 SCOTT ANALYST 7566 1987/4/19 4725.00 20

7839 KING PRESIDENT 1981/11/17 7650.00 10

7844 TURNER SALESMAN 7698 1981/9/8 2550.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 2100.00 20

7900 JAMES CLERK 7698 1981/12/3 2025.00 30

7902 FORD ANALYST 7566 1981/12/3 4725.00 20

7934 MILLER CLERK 7782 1982/1/23 2100.00 10

14 rows selected

游标变量：

SQL> declare

2 type t_dept is REF CURSOR return scott.emp%rowtype;

3 c_1 t_dept;

4 v_row scott.emp%rowtype;

5 begin

6 open c_1 for select * from scott.emp where deptno=10;

7 fetch c_1 into v_row;

8 dbms_output.put_line(v_row.empno||' ' ||v_row.job);

9 close c_1;

10

11 open c_1 for select * from scott.emp where sal>=2000;

12 fetch c_1 into v_row;

13 dbms_output.put_line(v_row.deptno||' ' ||v_row.job);

14 close c_1;

15 end;

16 /

7782 MANAGER

30 SALESMAN

PL/SQL procedure successfully completed

SQL>

数据库实验第十四周

存储过程样例：

SQL> CREATE OR REPLACE PROCEDURE HelloWorld3 (

2 p_user_name VARCHAR2,

3 p_val1 VARCHAR2 DEFAULT ' Good Moning,',

4 p_val2 VARCHAR2 DEFAULT ' Nice to Meet you') AS

5 BEGIN

6 dbms_output.put_line('Hello ' || p_user_name || p_val1 || p_val2 || '!');

7 END HelloWorld3;

8 /

Procedure created

SQL>

SQL> BEGIN

2 HelloWorld3('Edward');

3 HelloWorld3('Edward', ' Good Night,');

4 HelloWorld3('Edward', ' Good Night,', 'Bye');

5 END;

6 /

Hello Edward Good Moning, Nice to Meet you!

Hello Edward Good Night, Nice to Meet you!

Hello Edward Good Night,Bye!

PL/SQL procedure successfully completed

SQL>

SQL> BEGIN

2 HelloWorld3('Edward');

3 HelloWorld3('Edward', p_val1 => ' Good Night,');

4 HelloWorld3('Edward', p_val1 => ' Good Night,', p_val2 => 'Bye');

5 HelloWorld3('Edward', p_val2 => ' HeiHei ');

6 END;

7 /

Hello Edward Good Moning, Nice to Meet you!

Hello Edward Good Night, Nice to Meet you!

Hello Edward Good Night,Bye!

Hello Edward Good Moning, HeiHei !

PL/SQL procedure successfully completed

SQL>

存储过程练习：

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Connected as C##scott@ORCL

在存储过程界面添加存储过程：

create or replace procedure update_emp

as

begin

update C##scott.emp set ename='candy1' where empno=7876;

end update_emp;

SQL> begin

2 update_emp;

3 end;

4 /

PL/SQL procedure successfully completed

第一次存储过程执行完毕，查询执行结果发现7876编号名称改为candy：

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

二次单独查询7876编号，发现又被改回去了：

SQL> select * from emp where empno = 7876;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

再次执行存储过程：

SQL>

SQL> begin

2 update_emp;

3 end;

4 /

PL/SQL procedure successfully completed

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

再次执行二次查询：

SQL> select * from emp where empno = 7876;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

删除存储过程：

SQL> DROP PROCEDURE update_emp;

Procedure dropped

数据库实验第十五周

创建触发器（在删除xs表中的数据时进行备份）：

create or replace trigger del_xs

before delete on xs

for each row

begin

insert into xs_2 (xh,xm,zym,xb,cssj,zxf) values (:old.xh,:old.xm, :old.zym, :old.xb, :old.cssj,:old.zxf);

end del_xs;

SQL> select * from xs;

XH XM ZYM XB CSSJ ZXF BZ

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

061101 王林计算机男 1986/2/10 50

101112 李明计算机男 1986/1/30 36

001 张琼计算机 45 三好学生

121112 王小二计算机男 1986/1/30 36

SQL> delete from xs where xh=001;

1 row deleted

此时该行数据将会在新创建的表中找到。

将对xs表的操作记录（insert，delete，update）在sql_info表中记录下来：

SQL> select * from sql_info;

INFO TIME

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

SQL> insert into xs(xh,xm,zym) values ('002','hello','nan');

1 row inserted

SQL> select * from sql_info;

INFO TIME

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

插入 2020/12/11

create or replace trigger t2

after delete or insert or update on xs

for each row

declare

v_info sql_info.info%type;

begin

if inserting then

v_info:='插入';

elsif updating then

v_info:='更新';

else

v_info:='删除';

end if;

insert INTO SQL_INFO VALUES(v_info,sysdate);

end t2;

创建一个触发器，输出对scott.emp表的操作（插入后员工号和员工名，更新后员工工资，删除的员工号和员工名。）：

CREATE OR REPLACE TRIGGER t3

BEFORE INSERT OR UPDATE OR DELETE ON scott.emp

FOR EACH ROW

BEGIN

IF INSERTING THEN

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

ELSIF UPDATING THEN

DBMS_OUTPUT.PUT_LINE(:old.sal||' '||:new.sal);

ELSE

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

END IF;

END t3;

SQL> set serveroutput on;

SQL>

SQL> declare

2 begin

3 update scott.emp set empno=7521 where empno=7522;

4 commit;

5 end;

6 /

更新记录后平均工资为 2073.21

针对Scott.emp表，记录其相应操作的信息，具体如下:

当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门剩余的人数(游标)：

CREATE OR REPLACE TRIGGER t4

AFTER INSERT OR UPDATE OR DELETE

ON scott.emp

declare

v_1 number; v_2 scott.emp.sal%type;

begin

if inserting then

select count(*) into v_1 from scott.emp;

DBMS_OUTPUT.PUT_LINE('添加记录后总人数为'||v_1);

elsif updating then

select avg(sal) into v_2 from scott.emp;

DBMS_OUTPUT.PUT_LINE('更新记录后平均工资为'||' '||v_2);

else

for v_s in (select deptno,count(*) num from scott.emp group by deptno)

loop

DBMS_OUTPUT.PUT_LINE('删除记录后各个部门的部门号和人数为' ||v_s.deptno||' '||v_s.num);

end loop;

end if;

end t4;

SQL> delete from scott.emp where hiredate<=to_date('1980-12-17','yyyy-mm-dd');

7369 SMITH

删除记录后各个部门的部门号和人数为30 6

删除记录后各个部门的部门号和人数为20 4

删除记录后各个部门的部门号和人数为10 3

1 row deleted

数据库实验第十六周

创建一个存储过程，以部门号为该存储过程的in类型参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。(for 循环遍历游标)：

CREATE OR REPLACE PROCEDURE show_emp(
p_deptno emp.deptno%TYPE)
AS
v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;
DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary is:'||v_sal);
FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND sal>v_sal)
LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
END LOOP;
END show_emp;

SQL> set serverout on;

SQL> begin

2 show_emp(20);

3 end;

4 /

20 average salary is:2175

7566 JONES

7788 SCOTT

7902 FORD

PL/SQL procedure successfully completed

用存储过程进行模糊查找，如查找ename中包含L的雇员信息：

create or replace procedure Tp1
(varEmpName emp.ename%type)
is
--cursor c_1 is select * from scott.emp where ename like '%'||varEmpName||'%';
begin
for v_1 in (select * from emp where ename like '%'||varEmpName||'%')
loop
dbms_output.put_line(v_1.empno||' '||v_1.ename||' '||v_1.job||' '||v_1.deptno);
end loop;
end;

SQL> begin

2 tp1('L');

3 end;

4 /

7499 ALLEN SALESMAN 30

7698 BLAKE MANAGER 30

7782 CLARK MANAGER 10

7934 MILLER CLERK 10

PL/SQL procedure successfully completed

rownum练习：

SQL> select * from xs;

XH XM ZYM XB CSSJ ZXF BZ

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

061101 王林计算机男 1986/2/10 50

101112 李明计算机男 1986/1/30 36

001 张琼计算机 45 三好学生

121112 王小二计算机男 1986/1/30 36

SQL> select * from xs where rownum<=2;

XH XM ZYM XB CSSJ ZXF BZ

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

061101 王林计算机男 1986/2/10 50

101112 李明计算机男 1986/1/30 36

SQL> select deptno,count(*) PersonNum,avg(sal) avgsal from scott.emp

2 group by deptno

3 order by PersonNum desc;

DEPTNO PERSONNUM AVGSAL

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

30 6 1566.66666

20 5 2175

10 3 2916.66666

注意上述查询在使用rownum时不能直接写成如下格式：

SQL> select deptno,count(*) PersonNum,avg(sal) avgsal from scott.emp

2 group by deptno

3 order by PersonNum desc

where rownum<=1;

ORA-00933: SQL 命令未正确结束

否则会报ORA-00933: SQL 命令未正确结束错误。

正确做法：

再写一层查询嵌套内部查询：

SQL> select * from(

2 select deptno,count(*) PersonNum,avg(sal) avgsal from emp

3 group by deptno

4 order by PersonNum desc)

5 where rownum<=1;

DEPTNO PERSONNUM AVGSAL

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

30 6 1566.66666

SQL>

补充：见存储过程需要注意的问题，异常处理与触发器补充

补充内容

循环结构：

求10的阶乘：

一、

LOOP

Exit when...

END LOOP;

二、

WHILE

LOOP

END LOOP;

三、

FOR count IN count_1...count_n

LOOP

END LOPP;

For循环中的逆序：

for n in reverse 1..10

一、

LOOP

Exit when...

END LOOP;

Command：

SEt SERVEROUTPUT on;

DECLARE

s NUMBER:=1;

n NUMBER:=2;

BEGIN

LOOP

s:=s*n;

n:=n+1;

exit when n>10;

END LOOP;

dbms_output.put_line(to_char(s));

END;

/

实现：

SQL> SEt SERVEROUTPUT on;

SQL> DECLARE

2 s NUMBER:=1;

3 n NUMBER:=2;

4 BEGIN

5 LOOP

6 s:=s*n;

7 n:=n+1;

8 exit when n>10;

9 END LOOP;

10 dbms_output.put_line(to_char(s));

11 END;

12 /

3628800

PL/SQL procedure successfully completed

二、

WHILE

LOOP

END LOOP;

Command：

DECLARE

s NUMBER:=1;

n NUMBER:=2;

BEGIN

while n<=10

LOOP

s:=s*n;

n:=n+1;

end LOOP;

dbms_output.put_line(to_char(s));

END;

实现：

SQL> DECLARE

2 s NUMBER:=1;

3 n NUMBER:=2;

4 BEGIN

5 while n<=10

6 LOOP

7 s:=s*n;

8 n:=n+1;

9 end LOOP;

10 dbms_output.put_line(to_char(s));

11 END;

12 /

3628800

PL/SQL procedure successfully completed

三、

FOR count IN count_1...count_n

LOOP

END LOPP;

Command：

DECLARE

s NUMBER:=1;

n NUMBER:=2;

BEGIN

for n in 2..10

Loop

s:=s*n;

end loop;

dbms_output.put_line(to_char(s));

END;

实现：

SQL> DECLARE

2 s NUMBER:=1;

3 n NUMBER:=2;

4 BEGIN

5 for n in 2..10

6 Loop

7 s:=s*n;

8 end loop;

9 dbms_output.put_line(to_char(s));

10 END;

11 /

3628800

PL/SQL procedure successfully completed

For循环中的逆序：

for n in reverse 1..10

Command：

DECLARE

s NUMBER:=1;

n NUMBER:=2;

begin

for n in reverse 1..10

loop

s:=s*n;

dbms_output.put_line(to_char(n));

end loop;

dbms_output.put_line(to_char(s));

END;

实现：

SQL> DECLARE

2 s NUMBER:=1;

3 n NUMBER:=2;

4 begin

5 for n in reverse 1..10

6 loop

7 s:=s*n;

8 dbms_output.put_line(to_char(n));

9 end loop;

10 dbms_output.put_line(to_char(s));

11 END;

12 /

10

9

8

7

6

5

4

3

2

1

3628800

PL/SQL procedure successfully completed

水仙花数：

Command：

declare

i int;a int;b int;c int;

begin

for i in 100..999

loop

a:= trunc(i/100);

b:=trunc(i/10) mod 10;

c:=i mod 10;

-- dbms_output.put_line(a||' '||b||' '||c);

if (i=a*a*a+b*b*b+c*c*c) then

dbms_output.put_line(i);

end if;

end loop;

end;

/

实现：

SQL> set serveroutput on;

SQL> declare

2 i int;a int;b int;c int;

3 begin

4 for i in 100..999

5 loop

6 a:= trunc(i/100);

7 b:=trunc(i/10) mod 10;

8 c:=i mod 10;

9 -- dbms_output.put_line(a||' '||b||' '||c);

10 if (i=a*a*a+b*b*b+c*c*c) then

11 dbms_output.put_line(i);

12 end if;

13 end loop;

14 end;

15 /

153

370

371

407

PL/SQL procedure successfully completed

触发器补充：

设置系统触发器：

创建logon触发器，在登录的时候进行记录：

首先创建保存记录的表：

SQL> create table u_1

2 ( username varchar2(50),

3 activity varchar2(20),

4 time date

5 )

6 ;

Table created

SQL> select username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_1;

USERNAME ACTIVITY TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

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

C##SCOTT LOGON 2020-12-19 23:41

C##SCOTT LOGON 2020-12-19 23:41

C##SCOTT LOGON 2020-12-19 23:41

SYS LOGON 2020-12-19 23:41

SYS LOGON 2020-12-19 23:41

SYSTEM LOGON 2020-12-19 23:42

SYSTEM LOGON 2020-12-19 23:42

SYSTEM LOGON 2020-12-19 23:42

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

SYS LOGON 2020-12-20 10:09

USERNAME ACTIVITY TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

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

SYSTEM LOGON 2020-12-20 10:12

SYSTEM LOGON 2020-12-20 10:12

SYSTEM LOGON 2020-12-20 10:12

23 rows selected

创建logoff触发器，在退出登录的时候进行记录（注意此时的触发器时间上不要采用after，因为采用after客户机注销进程终止，不能进行其他操作）:

首先创建保存记录的表：

SQL> create table u_2

2 ( username varchar2(50),

3 activity varchar2(20),

4 time date

5 )

6 ;

Table created

SQL>

将触发器的after改成before：

create or replace trigger st2

before logoff on database

begin

insert into u_2 values(user,'LOGOFF',sysdate);

END st2;

SQL> select username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_2;

USERNAME ACTIVITY TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

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

退出后查询表：

SQL> select username,activity,to_char(time,'yyyy-MM-dd HH24:mi') from u_2;

USERNAME ACTIVITY TO_CHAR(TIME,'YYYY-MM-DDHH24:MI')

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

SYSTEM LOGOFF 2020-12-20 10:42

SYSTEM LOGOFF 2020-12-20 10:42

SYSTEM LOGOFF 2020-12-20 10:42

查询sysdate并转换为char格式输出：

SQL> Select to_char(sysdate,'yyyy-MM-dd HH24:mi') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI')

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

2020-12-20 11:06

SQL> Select to_char(sysdate, 'DAY') from dual;

TO_CHAR(SYSDATE,'DAY')

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

星期日

SQL>

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Connected as C##scott

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

设置触发器：作用为禁止在休息日（周六、周天）改变scott.emp雇员信息（包括添加删除和修改）。

create or replace trigger tr_sec_emp

before insert or update or delete on emp

begin

if to_char(sysdate, 'DAY') in ('星期六','星期日') then

raise_application_error(-20001,'不能在休息日修改员工信息');

end if;

end;

SQL> update emp set ename='candy' where empno=7876;

update emp set ename='candy' where empno=7876

ORA-20001: 不能在休息日修改员工信息

ORA-06512: 在 "C##SCOTT.TR_SEC_EMP", line 3

ORA-04088: 触发器 'C##SCOTT.TR_SEC_EMP' 执行过程中出错

上述不能执行的同时给出了相关触发器和触发器代码限制的所在行数。

关闭相应触发器：

SQL> Alter trigger tr_sec_emp disable;

Trigger altered

SQL> update emp set ename='candy' where empno=7876;

1 row updated

SQL> select * from emp where empno=7876;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 candy CLERK 7788 0087/5/23 1100.00 20

打开触发器后则又不能在规定范围内进行修改：

SQL> Alter trigger tr_sec_emp enable;

Trigger altered

SQL> update emp set ename='candytest' where empno=7876;

update emp set ename='candytest' where empno=7876

ORA-20001: 不能在休息日修改员工信息

ORA-06512: 在 "C##SCOTT.TR_SEC_EMP", line 3

ORA-04088: 触发器 'C##SCOTT.TR_SEC_EMP' 执行过程中出错

启动和关闭触发器

Alter trigger 触发器名字 disable;

Alter trigger 触发器名字 enable;

关闭某表的所有触发器

alter table 表名字 disable all triggers ;

开启所有触发器

alter table 表名字 enable all triggers ;

存储过程需要注意的一些问题

存储过程要注意权限问题，很有可能权限不足，比如有时候在system用户下对scott的表进行操作等可能会出现权限问题，这个时候可以退出system用户（或者退出当前用户切换或者重新打开一个oracle进程登录（一台机器可允许多个oracle用户登录）），然后重新编译关于scott的存储过程即可编译成功。（即使有时候在system用户下可以编译运行也不是永久的修改，而只是临时的修改，再次运行程序可能会发现其中的数据并没有修改，只是在存储过程执行的后一次实现了临时性的结果展示，并没有对数据进行永久的替换保存，这一点需要注意。）

例一（权限不足）：

例二（临时性结果展示，未保存到数据库内部）：

在存储过程界面添加存储过程：

create or replace procedure update_emp

as

begin

update C##scott.emp set ename='candy1' where empno=7876;

end update_emp;

SQL> begin

2 update_emp;

3 end;

4 /

PL/SQL procedure successfully completed

第一次存储过程执行完毕，查询执行结果发现7876编号名称改为candy：

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

二次单独查询7876编号，发现又被改回去了：

SQL> select * from emp where empno = 7876;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

再次执行存储过程：

SQL>

SQL> begin

2 update_emp;

3 end;

4 /

PL/SQL procedure successfully completed

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

再次执行二次查询：

SQL> select * from emp where empno = 7876;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 ADAMS CLERK 7788 0087/5/23 1100.00 20

删除存储过程：

SQL> DROP PROCEDURE update_emp;

Procedure dropped

例三（切换到scott用户之后不再会出现上述两个例子的问题，权限OK，编译compiled successful，执行程序的数据永久保存到数据库中，不会再产生临时性的错误）：

SQL> begin

2 update_emp;

3 end;

4 /

PL/SQL procedure successfully completed

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

SQL> select * from emp where ename='candy1';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7876 candy1 CLERK 7788 1987/5/23 1100.00 20

SQL> DROP PROCEDURE update_emp;

Procedure dropped

异常处理（系统异常处理以及自定义异常处理）

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

Connected as system@ORCL

SQL> select * from C##Scott.Emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 0087/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 candy1 CLERK 7788 0087/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

SQL>

SQL> declare

2 v_1 number;

3 begin

4 select empno into v_1 from C##Scott.emp;

5 dbms_output.put_line(v_1);

6 exception

7 when no_data_found then

8 dbms_output.put_line('出现no_data_found异常了');

9 when too_many_rows then

10 dbms_output.put_line('出现too_many_rows异常了');

11 when others then

12 dbms_output.put_line('出现others异常了');

13 end;

14 /

出现too_many_rows异常了

PL/SQL procedure successfully completed

SQL>

SQL> declare

2 v_1 number;

3 begin

4 select empno into v_1 from C##Scott.emp where ename = 'test_no_data_found';

5 dbms_output.put_line(v_1);

6 exception

7 when no_data_found then

8 dbms_output.put_line('出现no_data_found异常了');

9 when too_many_rows then

10 dbms_output.put_line('出现too_many_rows异常了');

11 when others then

12 dbms_output.put_line('出现others异常了');

13 end;

14 /

出现no_data_found异常了

PL/SQL procedure successfully completed

PL/SQL中打印错误信息:dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

SQL> declare

2 v_1 number;

3 begin

4 select empno into v_1 from C##Scott.emp where ename = 'test_no_data_found';

5 dbms_output.put_line(v_1);

6 exception

7 when no_data_found then

8 dbms_output.put_line('出现no_data_found异常了');

9 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

10 when too_many_rows then

11 dbms_output.put_line('出现too_many_rows异常了');

12 when others then

13 dbms_output.put_line('出现others异常了');

14 end;

15 /

出现no_data_found异常了

错误代码： 100 错误信息： ORA-01403: 未找到任何数据

PL/SQL procedure successfully completed

SQL>

SQL> declare

2 v_1 number;

3 begin

4 select empno into v_1 from C##Scott.emp;

5 dbms_output.put_line(v_1);

6 exception

7 when no_data_found then

8 dbms_output.put_line('出现no_data_found异常了');

9 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

10 when too_many_rows then

11 dbms_output.put_line('出现too_many_rows异常了');

12 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

13 when others then

14 dbms_output.put_line('出现others异常了');

15 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

16 end;

17 /

出现too_many_rows异常了

错误代码： -1422 错误信息： ORA-01422: 实际返回的行数超出请求的行数

PL/SQL procedure successfully completed

出现代码异常抛出时不再执行下面的语句，直接跳转到异常处理（其中用户自定义异常错误代码为1，错误信息为User-Defined Exception）：

SQL> declare

2 v_1 number;

3 e_1 exception;

4 e_2 exception;

5 begin

6 select empno into v_1 from C##Scott.emp where ename = 'candy1';

7 dbms_output.put_line(v_1);

8 if v_1 > 7600 then

9 raise e_1;

10 else

11 raise e_2;

12 end if;

13 exception

14 when e_1 then

15 dbms_output.put_line('test自定义异常，这里以raise代替throws，超过7600号');

16 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

17 when e_2 then

18 dbms_output.put_line('test自定义异常，这里以raise代替throws，超过7500号');

19 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

20 when others then

21 dbms_output.put_line('出现others异常了');

22 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

23 end;

24 /

7876

test自定义异常，这里以raise代替throws，超过7600号

错误代码： 1 错误信息： User-Defined Exception

PL/SQL procedure successfully completed

SQL>

SQL> declare

2 v_1 number;

3 e_1 exception;

4 e_2 exception;

5 begin

6 select empno into v_1 from C##Scott.emp where ename = 'WARD';

7 dbms_output.put_line(v_1);

8 if v_1 > 7600 then

9 raise e_1;

10 else

11 raise e_2;

12 end if;

13 exception

14 when e_1 then

15 dbms_output.put_line('test自定义异常，这里以raise代替throws，超过7600号');

16 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

17 when e_2 then

18 dbms_output.put_line('test自定义异常，这里以raise代替throws，超过7500号');

19 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

20 when others then

21 dbms_output.put_line('出现others异常了');

22 dbms_output.put_line('错误代码： '||sqlcode||' 错误信息： '||sqlerrm);

23 end;

24 /

7521

test自定义异常，这里以raise代替throws，超过7500号

错误代码： 1 错误信息： User-Defined Exception

PL/SQL procedure successfully completed

SQL>

posted @ 2021-01-19 00:11 study-hard-forever 阅读(359) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部