下面为数据库实验的一些报告(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

%rowtype: 22

%type: 23

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

Command: 45

/*分支结构1*/ 45

/*分支结构2*/ 45

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

/*多分支case语句*/ 47

/* 47

1.简单型case 47

2.搜索性case 47

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

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

*/ 47

/*简单型case*/ 47

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

/*搜索case表达式*/ 48

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

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

/*case的select练习*/ 49

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

1.过期 49

2.没过期 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

实现: 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

Command: 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

实现: 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

 

数据库实验第八周

查询数据块大小:SHOW PARAMETER db_block_size;

 

SQL> SHOW PARAMETER db_block_size;

 

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

 

SQL>

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

内容太多,略。

归档模式与非归档模式:

非归档模式,不记归档日志,没有办法进行数据恢复。当然如果数据库的量比较大,变换的频率比较高,这个归档的模式也要合理规划的,否则归档日志的空间量是很可观的!

 

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 10月 23 16:30:37 2020

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> SHOW USER;

USER 为 "SYS"

 

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

 

SQL> ARCHIVE LOG LIST;

数据库日志模式             非存档模式

自动存档             禁用

存档终点            USE_DB_RECOVERY_FILE_DEST

最早的联机日志序列     13

当前日志序列           15

 

 

SQL> SELECT name,log_mode FROM v$database;

 

NAME      LOG_MODE

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

ORCL      NOARCHIVELOG

 

 

以下四步将数据库从非归档模式转化为归档模式(黑屏下):

shutdown immediate;

startup nomount;

alter database mount;

alter database archivelog;

 

可能出现的错误:

//出现错误(ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务)   监听配置出错,修改listener.ora和tnsnames.ora配置文件后得以解决

 

SQL> Shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> Startup nomount;

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

SQL> Alter database mount;

ERROR:

ORA-03114: 未连接到 ORALCE

 

 

 

SQL*Plus: Release 10.2.0.3.0 - Production on 星期五 10月 23 16:31:44 2020

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> SHOW USER;

USER 为 "SYSTEM"

SQL> conn /as sysdba;

已连接。

SQL> show user;

USER 为 "SYS"

 

//重新操作时oracle重新运行了,应该先关闭

 

SQL> startup nomount;

ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup nomount;

ORACLE 例程已经启动。

 

Total System Global Area  612368384 bytes

Fixed Size                  1292036 bytes

Variable Size             234883324 bytes

Database Buffers          369098752 bytes

Redo Buffers                7094272 bytes

SQL> alter database mount;

 

数据库已更改。

 

SQL> alter database archivelog;

 

数据库已更改。

 

SQL>

 

数据库实验第九周

Command:

/*分支结构1*/

SET SERVEROUTPUT ON;

DECLARE

v_xm varchar2(8):='jame';

v_zym varchar2(10):='计算机';

v_zxf number(2):=45; /*定义变量类型*/

BEGIN

  INSERT INTO XS(XH,XM,ZYM,ZXF)

  VALUES('007',v_xm,v_zym,v_zxf);

  IF SQL%FOUND THEN

    DBMS_OUTPUT.PUT_LINE('操作成功');

  ELSE

    DBMS_OUTPUT.PUT_LINE('没有插入该人');

  END IF;

END;

/

 

/*分支结构2*/

DECLARE

v_sal scott.emp.sal%type;

v_tax scott.emp.sal%type;

BEGIN

  select sal into v_sal from scott.emp where empno=7788;  /*%type返回一条记录中某一列*/

  if v_sal>=3000 then

    v_tax:=v_sal*0.08;

  elsif v_sal>=1500 then

    v_tax:=v_sal*0.06;

  else

    v_tax:=v_sal*0.04;

  end if;

  DBMS_OUTPUT.PUT_LINE('应交税金:'||v_tax);  /*||连接字符串*/

END;

/

    

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

declare

   v_deptno  scott.emp.deptno%type;

   v_zl  scott.emp.sal%type;

begin

   select deptno into v_deptno  from  scott.emp  where empno=&&a;

   if v_deptno=10 then

       v_zl:=100;

     elsif   v_deptno=20 then

        v_zl:=150;

          elsif   v_deptno=30 then

          v_zl:=200;

     else

     v_zl:=300;

     end if;

     

     UPDATE scott.emp SET sal=sal+v_zl  WHERE empno=&a;

     end;

/

 

/*上一步操作临时变量输入的是7934号员工,执行查询之后获得该7934号员工的deptno为100,增加100后更新到数据表中,结果可参见两次查询结果对比(1300->1400)*/

 

/*注意临时变量&&a和&a的使用

(&&是输入临时变量(并保存),&是获取上次输入过的临时变量,若没有输入过则需要进行输入(只&过的是不保存历史的临时变量,每次都要重新输入)*/

/*例:

输入a1:

SQL> SELECT &&a1+1 FROM DUAL;

 

       3+1

----------

         4

 

检测到历史a1,无需输入:

SQL> SELECT &a1+1 FROM DUAL;

 

       3+1

----------

         4

输入a2:

SQL> SELECT &a2+1 FROM DUAL;

 

       2+1

----------

         3

 

输入a2,上次的a2是&而不是&&,故需要重新输入:

SQL> SELECT &a2+1 FROM DUAL;

 

       5+1

----------

         6

 

*/

 

/*多分支case语句*/

/*

1.简单型case

2.搜索性case

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

4.嵌入到PL/SQL程序语句(如赋值语句)的case

*/

 

/*简单型case*/

declare

grade varchar2(4):='良好';

begin

  CASE grade

    when '优秀' then dbms_output.put_line('大于等于90');

    when '良好' then dbms_output.put_line('大于等于80,小于等于90');

    when '及格' then dbms_output.put_line('大于等于60,小于等于80');

    ELSE dbms_output.put_line('不及格');

    END CASE;

end;

/

 

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

DECLARE

v_deptno emp.deptno%type;

v_increment number(4);

v_empno emp.empno%type;

BEGIN

  v_empno:=&x;

  select deptno into v_deptno from emp where empno=v_empno;

  CASE v_deptno

  WHEN 10 THEN v_increment:=100;

  WHEN 20 THEN v_increment:=150;

  WHEN 30 THEN v_increment:=200;

  ELSE v_increment:=300;

  END CASE;

  update emp set sal=sal+v_increment where empno=v_empno;

END;

/

 

/*搜索case表达式*/

SET SERVEROUTPUT ON;

declare

score int:=91;

begin

  case

    when score>=90 then dbms_output.put_line('优秀');

    when score>=80 then dbms_output.put_line('良好');

    when score>=60 then dbms_output.put_line('及格');

    else dbms_output.put_line('不及格');

  end case;

end;

/

 

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

/*与if语句不同,case语句可以用在select语句中,用于在检索数据的同时对数据进行判断并返回判断结果*/

 

select xh,xm,zxf,

(case

       when zxf>50 then '高'

         when zxf>=40 then '中'

           else '学分不够,需继续努力'

end) as 获得学分情况

from xs;

 

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

create table t01 as

select xh,xm,zxf,

(case

       when zxf>50 then '高'

         when zxf>=40 then '中'

           else '学分不够,需继续努力'

end) as 获得学分情况

from xs;

 

/*case的select练习*/

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

1.过期

2.没过期

3.没有借阅图书*/

 

--日期简单练习:

-- https://www.cnblogs.com/jxtx92/p/7905339.html

-- https://blog.csdn.net/w405722907/article/details/75519156

-- https://blog.csdn.net/zoucui/article/details/79347286

 

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

 

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

 

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

 

select empno,ename,job,hiredate,

(case

       when trunc(sysdate-hiredate)>360 then '过期'

         when hiredate is null then '没借书'

           else '没过期'

end)

as 是否过期 from scott.emp;

 

/*嵌入到pl/sql程序语句(如赋值语句)的case*/

 

实现:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as system

 

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 kc;

 

KCH KCM              KKXQ  XS XF

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

001 001                 1  11  1

001 001                 1  11  1

 

SQL> select * from xs_kc;

 

XH     KCH                                         CJ  XF

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

061101 101                                         80

061101 102                                         78

061101 206                                         76

061103 101                                         82

061103 102                                         82

061103 206                                         83

061104 101                                         90

061107 101                                         98

061107 102                                         80

 

9 rows selected

 

SQL> /*分支结构1*/

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

  2  v_xm varchar2(8):='jame';

  3  v_zym varchar2(10):='计算机';

  4  v_zxf number(2):=45; /*定义变量类型*/

  5  BEGIN

  6    INSERT INTO XS(XH,XM,ZYM,ZXF)

  7    VALUES('007',v_xm,v_zym,v_zxf);

  8    IF SQL%FOUND THEN

  9      DBMS_OUTPUT.PUT_LINE('操作成功');

 10    ELSE

 11      DBMS_OUTPUT.PUT_LINE('没有插入该人');

 12    END IF;

 13  END;

 14  /

 

操作成功

 

PL/SQL procedure successfully completed

 

SQL> SELECT * FROM XS;

 

XH     XM     ZYM    XB CSSJ        ZXF BZ

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

007    jame   计算机                 45

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

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

001    张琼   计算机                 45 三好学生

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

 

 

SQL>

SQL> /*分支结构2*/

  2  DECLARE

  3  v_sal scott.emp.sal%type;

  4  v_tax scott.emp.sal%type;

  5  BEGIN

  6    select sal into v_sal from scott.emp where empno=7788;  /*%type返回一条记录中某一列*/

  7    if v_sal>=3000 then

  8      v_tax:=v_sal*0.08;

  9    elsif v_sal>=1500 then

 10      v_tax:=v_sal*0.06;

 11    else

 12      v_tax:=v_sal*0.04;

 13    end if;

 14    DBMS_OUTPUT.PUT_LINE('应交税金:'||v_tax);  /*||连接字符串*/

 15  END;

 16  /

 

应交税金:240

 

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>

SQL> declare

  2     v_deptno  scott.emp.deptno%type;

  3     v_zl  scott.emp.sal%type;

  4  begin

  5     select deptno into v_deptno  from  scott.emp  where empno=&&a;

  6     if v_deptno=10 then

  7         v_zl:=100;

  8       elsif   v_deptno=20 then

  9          v_zl:=150;

 10            elsif   v_deptno=30 then

 11            v_zl:=200;

 12       else

 13       v_zl:=300;

 14       end if;

 15  

 16       UPDATE scott.emp SET sal=sal+v_zl  WHERE empno=&a;

 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     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     1400.00               10

 

14 rows selected

 

 

SQL> SELECT &&a1+1 FROM DUAL;

 

       3+1

----------

         4

 

SQL> SELECT &a1+1 FROM DUAL;

 

       3+1

----------

         4

 

SQL> SELECT &a2+1 FROM DUAL;

 

       2+1

----------

         3

 

SQL> SELECT &a2+1 FROM DUAL;

 

       5+1

----------

         6

 

 

 

SQL>

 

 

SQL> SET SERVEROUTPUT ON;

SQL> declare

  2  score int:=91;

  3  begin

  4    case

  5      when score>=90 then dbms_output.put_line('优秀');

  6      when score>=80 then dbms_output.put_line('良好');

  7      when score>=60 then dbms_output.put_line('及格');

  8      else dbms_output.put_line('不及格');

  9    end case;

 10  end;

 11  /

 

优秀

 

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>

SQL> select xh,xm,zxf,

  2  (case

  3         when zxf>50 then '高'

  4           when zxf>=40 then '中'

  5             else '学分不够,需继续努力'

  6  end) as 获得学分情况

  7  from xs;

 

XH     XM     ZXF 获得学分情况

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

061101 王林    50 中

101112 李明    36 学分不够,需继续努力

001    张琼    45 中

121112 王小二  36 学分不够,需继续努力

 

SQL>

SQL> create table t01 as

  2  select xh,xm,zxf,

  3  (case

  4         when zxf>50 then '高'

  5           when zxf>=40 then '中'

  6             else '学分不够,需继续努力'

  7  end) as 获得学分情况

  8  from xs;

 

Table created

 

SQL> select * from t01;

 

XH     XM     ZXF 获得学分情况

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

061101 王林    50 中

101112 李明    36 学分不够,需继续努力

001    张琼    45 中

121112 王小二  36 学分不够,需继续努力

 

SQL> select months_between(sysdate,to_date('20151001','yyyymmdd')) from dual;

 

MONTHS_BETWEEN(SYSDATE,TO_DATE

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

              61.4093977747909

 

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

 

    MONTHS

----------

         1

 

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

 

        天数

----------

       774

 

SQL>

SQL> select empno,ename,job,hiredate,

  2  (case

  3         when trunc(sysdate-hiredate)>360 then '过期'

  4           when hiredate is null then '没借书'

  5             else '没过期'

  6  end)

  7  as 是否过期 from scott.emp;

 

EMPNO ENAME      JOB       HIREDATE    是否过期

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

 7369 SMITH      CLERK     1980/12/17  过期

 7499 ALLEN      SALESMAN  1981/2/20   过期

 7521 WARD       SALESMAN  1981/2/22   过期

 7566 JONES      MANAGER   1981/4/2    过期

 7654 MARTIN     SALESMAN  1981/9/28   过期

 7698 BLAKE      MANAGER   1981/5/1    过期

 7782 CLARK      MANAGER   1981/6/9    过期

 7788 SCOTT      ANALYST   1987/4/19   过期

 7839 KING       PRESIDENT 1981/11/17  过期

 7844 TURNER     SALESMAN  1981/9/8    过期

 7876 ADAMS      CLERK     1987/5/23   过期

 7900 JAMES      CLERK     1981/12/3   过期

 7902 FORD       ANALYST   1981/12/3   过期

 7934 MILLER     CLERK     1982/1/23   过期

 

14 rows selected

 

SQL>

数据库实验第十周

创建分区表:

SQL> create table part_book1

  2        (  bid number(4),

  3           bookname VARCHAR2(20),

  4           bookpress VARCHAR2(30),

  5            booktime date)

  6  partition by list(bookpress)

  7    (partition part1 values('清华大学出版社') tablespace system,

  8     partition part2 values('教育出版社') tablespace users);

 

create table part_book1

      (  bid number(4),

         bookname VARCHAR2(20),

         bookpress VARCHAR2(30),

          booktime date)

partition by list(bookpress)

  (partition part1 values('清华大学出版社') tablespace system,

   partition part2 values('教育出版社') tablespace users)

 

ORA-00955: 名称已由现有对象使用

 

SQL>

SQL> create table part_book_test1

  2        (  bid number(4),

  3           bookname VARCHAR2(20),

  4           bookpress VARCHAR2(30),

  5            booktime date)

  6  partition by list(bookpress)

  7    (partition part1 values('清华大学出版社') tablespace system,

  8     partition part2 values('教育出版社') tablespace users);

 

Table created

 

查询分区表:

SQL> select * from part_book1;

 

  BID BOOKNAME             BOOKPRESS                      BOOKTIME

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

    1 oralce               清华大学出版社                 2011/1/2

    1 oracle               清华大学出版社                 2011/1/2

    2 音乐基础欣赏         教育出版社                     2012/1/2

 

SQL> select * from part_book1 partition(part1);

 

  BID BOOKNAME             BOOKPRESS                      BOOKTIME

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

    1 oralce               清华大学出版社                 2011/1/2

    1 oracle               清华大学出版社                 2011/1/2

 

SQL> select * from part_book1 partition(part2);

 

  BID BOOKNAME             BOOKPRESS                      BOOKTIME

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

    2 音乐基础欣赏         教育出版社                     2012/1/2

 

SQL>select * from dba_part_tables;

内容太多,略。

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

 

OWNER                          TABLE_NAME                     PARTITIONING_TYPE SUBPARTITIONING_TYPE PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT SUBPARTITIONING_KEY_COUNT STATUS   DEF_TABLESPACE_NAME            DEF_PCT_FREE DEF_PCT_USED DEF_INI_TRANS DEF_MAX_TRANS DEF_INITIAL_EXTENT                       DEF_NEXT_EXTENT                          DEF_MIN_EXTENTS                          DEF_MAX_EXTENTS                          DEF_PCT_INCREASE                         DEF_FREELISTS DEF_FREELIST_GROUPS DEF_LOGGING DEF_COMPRESSION DEF_BUFFER_POOL

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

SYSTEM                         PART_BOOK1                     LIST              NONE                               3                      0                      1                         0 VALID    SYSTEM                                   10           40             1           255 DEFAULT                                  DEFAULT                                  DEFAULT                                  DEFAULT                                  DEFAULT                                              0                   0 NONE        NONE            DEFAULT

 

SQL> select * from dba_tab_partitions where table_name='PART_BOOK1';

 

TABLE_OWNER                    TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS

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

SYSTEM                         PART_BOOK1                     NO        PART1                                           0 '清华大学出版社'                                                                                16                  1 SYSTEM                                 10         40          1        255          65536                      1 2147483645                       1               1 YES     DISABLED                                                                                                   DEFAULT     NO           NO

SYSTEM                         PART_BOOK1                     NO        PART2                                           0 '教育出版社'                                                                                    12                  2 USERS                                  10                     1        255          65536                      1 2147483645                                         YES     DISABLED                                                                                                   DEFAULT     NO           NO

SYSTEM                         PART_BOOK1                     NO        PART3                                           0 default                                                                                          7                  3 SYSTEM                                 10         40          1        255          65536                      1 2147483645                       1               1 YES     DISABLED                                                                                                   DEFAULT     NO           NO

 

修改分区表:

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

 

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

 

ORA-14323: 在 DEFAULT 分区已存在时无法添加分区

 

Command:

查询图书是否过期及应缴金额(select--case查询)

select empno,ename,job,hiredate,

(case

    when trunc(sysdate-HIREDATE)>360  then '过期'

    when hiredate is null then '没借书'

     else  '没过期'

    end)

   as 是否过期,

(case

    when trunc(sysdate-HIREDATE)>360  then trunc((sysdate-HIREDATE)*0.1)

    when hiredate is null then 0

     else  0

    end)

   as 应缴金额

   from scott.emp;

 

查询优良等级(select--case查询)

select xh,kch,cj,

    (case

          when cj>=90 then '优'

          when cj>=80 then '良'

           else ''

      end)

          as 级别

          from xs_kc;   

循环练习——exit when+for逆序循环(for count in reserve count_1..count_10)

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;

/

 

DECLARE

     s NUMBER:=1;

   n NUMBER:=2;

 begin

  for n in reverse 1..10

loop

      s:=s*n;

      end loop;

dbms_output.put_line(to_char(s));

END;

/

while和for循环练习(1+2+3=6)

DECLARE

          v_Num INTEGER := 1;

          v_Sum INTEGER :=0;

BEGIN

          WHILE v_Num <= 3

            LOOP

              v_Sum := v_Sum + v_Num;

              dbms_output.put_line(v_Num);

              IF v_Num<3 THEN

                dbms_output.put_line('+');

              END IF;

              v_Num := v_Num+1;

            END LOOP;

            dbms_output.put_line('=');

            dbms_output.put_line(v_Sum);

END;

/

 

DECLARE

          v_Num INTEGER;

          v_Sum INTEGER :=0;

BEGIN

          FOR v_Num In 1..3

            LOOP

              v_Sum := v_Sum + v_Num;

              dbms_output.put_line(v_Num);

              IF v_Num<3 THEN

                dbms_output.put_line('+');

              END IF;

            END LOOP;

            dbms_output.put_line('=');

            dbms_output.put_line(v_Sum);

END;

/

 

实现:

 

SQL> select  xh,xm,zxf,

  2  (case

  3      when zxf>50 then 'gao'

  4        when zxf>=40 then 'zhong'

  5         else '学分不够,需继续'

  6  end) as

  7  获得学分情况  from xs;

 

XH     XM     ZXF 获得学分情况

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

061101 王林    50 zhong

101112 李明    36 学分不够,需继续

001    张琼    45 zhong

121112 王小二  36 学分不够,需继续

 

SQL>

SQL> select empno,ename,job,hiredate,

  2  (case

  3      when trunc(sysdate-HIREDATE)>360  then '过期'

  4      when hiredate is null then '没借书'

  5       else  '没过期'

  6      end)

  7     as 是否过期 from scott.emp;

 

EMPNO ENAME      JOB       HIREDATE    是否过期

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

 7369 SMITH      CLERK     1980/12/17  过期

 7499 ALLEN      SALESMAN  1981/2/20   过期

 7521 WARD       SALESMAN  1981/2/22   过期

 7566 JONES      MANAGER   1981/4/2    过期

 7654 MARTIN     SALESMAN  1981/9/28   过期

 7698 BLAKE      MANAGER   1981/5/1    过期

 7782 CLARK      MANAGER   1981/6/9    过期

 7788 SCOTT      ANALYST   1987/4/19   过期

 7839 KING       PRESIDENT 1981/11/17  过期

 7844 TURNER     SALESMAN  1981/9/8    过期

 7876 ADAMS      CLERK     1987/5/23   过期

 7900 JAMES      CLERK     1981/12/3   过期

 7902 FORD       ANALYST   1981/12/3   过期

 7934 MILLER     CLERK     1982/1/23   过期

 

14 rows selected

 

SQL>

SQL> select empno,ename,job,hiredate,

  2  (case

  3      when trunc(sysdate-HIREDATE)>360  then '过期'

  4      when hiredate is null then '没借书'

  5       else  '没过期'

  6      end)

  7     as 是否过期

  8  (case

  9      when trunc(sysdate-HIREDATE)>360  then trunc((sysdate-HIREDATE)*0.1)

 10      when hiredate is null then 0

 11       else  0

 12      end)

 13     as 应缴金额

 14     from scott.emp;

 

SQL>

SQL> select empno,ename,job,hiredate,

  2  (case

  3      when trunc(sysdate-HIREDATE)>360  then '过期'

  4      when hiredate is null then '没借书'

  5       else  '没过期'

  6      end)

  7     as 是否过期,

  8  (case

  9      when trunc(sysdate-HIREDATE)>360  then trunc((sysdate-HIREDATE)*0.1)

 10      when hiredate is null then 0

 11       else  0

 12      end)

 13     as 应缴金额

 14     from scott.emp;

 

EMPNO ENAME      JOB       HIREDATE    是否过期       应缴金额

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

 7369 SMITH      CLERK     1980/12/17  过期           1453

 7499 ALLEN      SALESMAN  1981/2/20   过期           1446

 7521 WARD       SALESMAN  1981/2/22   过期           1446

 7566 JONES      MANAGER   1981/4/2    过期           1442

 7654 MARTIN     SALESMAN  1981/9/28   过期           1424

 7698 BLAKE      MANAGER   1981/5/1    过期           1439

 7782 CLARK      MANAGER   1981/6/9    过期           1435

 7788 SCOTT      ANALYST   1987/4/19   过期           1221

 7839 KING       PRESIDENT 1981/11/17  过期           1419

 7844 TURNER     SALESMAN  1981/9/8    过期           1426

 7876 ADAMS      CLERK     1987/5/23   过期           1218

 7900 JAMES      CLERK     1981/12/3   过期           1418

 7902 FORD       ANALYST   1981/12/3   过期           1418

 7934 MILLER     CLERK     1982/1/23   过期           1413

 

14 rows selected

 

SQL>

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

 

SQL>

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        end loop;

  9   dbms_output.put_line(to_char(s));

 10   END;

 11  /

 

3628800

 

PL/SQL procedure successfully completed

 

SQL> select xh,kch,cj,

  2      (case

  3            when cj>=90 then '优'

  4            when cj>=80 then '良'

  5             else ''

  6        end)

  7            as 级别

  8            from xs_kc;

 

XH     KCH                                         CJ 级别

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

061101 101                                         80 良

061101 102                                         78

061101 206                                         76

061103 101                                         82 良

061103 102                                         82 良

061103 206                                         83 良

061104 101                                         90 优

061107 101                                         98 优

061107 102                                         80 良

 

9 rows selected

 

SQL>

SQL> DECLARE

  2            v_Num INTERGER := 1;

  3            v_Sum INTERGER :=0;

  4  BEGIN

  5            WHILE v_Num <= 3

  6              LOOP

  7                v_Sum := v_Sum + v_Num;

  8                dbms_output.put_line(v_Num);

  9                IF v_Num<3 THEN

 10                  dbms_output.put_line('+');

 11                END IF;

 12                v_Num := v_Num+1;

 13              END LOOP;

 14              dbms_output.put_line('=');

 15              dbms_output.put_line(v_Sum);

 16  END;

 17  /

 

 

SQL>

SQL> DECLARE

  2            v_Num INTEGER := 1;

  3            v_Sum INTEGER :=0;

  4  BEGIN

  5            WHILE v_Num <= 3

  6              LOOP

  7                v_Sum := v_Sum + v_Num;

  8                dbms_output.put_line(v_Num);

  9                IF v_Num<3 THEN

 10                  dbms_output.put_line('+');

 11                END IF;

 12                v_Num := v_Num+1;

 13              END LOOP;

 14              dbms_output.put_line('=');

 15              dbms_output.put_line(v_Sum);

 16  END;

 17  /

 

1

+

2

+

3

=

6

 

PL/SQL procedure successfully completed

 

 

 

 

SQL>

SQL> DECLARE

  2            v_Num INTEGER;

  3            v_Sum INTEGER :=0;

  4  BEGIN

  5            FOR v_Num In 1..3

  6              LOOP

  7                v_Sum := v_Sum + v_Num;

  8                dbms_output.put_line(v_Num);

  9                IF v_Num<3 THEN

 10                  dbms_output.put_line('+');

 11                END IF;

 12              END LOOP;

 13              dbms_output.put_line('=');

 14              dbms_output.put_line(v_Sum);

 15  END;

 16  /

 

1

+

2

+

3

=

6

 

PL/SQL procedure successfully completed

 

SQL>