适配 12.1.0.2 标准版

[Yehnn]

作业自动化

实验介绍

实验内容

在使用数据库的过程中，通常会有一些重复的事情，比如备份操作，检查错误，启动数据库等等。如果每次都手动操作，不仅有可能忘记而且很麻烦，如果创建一个作业，让系统自动处理会很方便。本节实验主要介绍两个用来实现作业自动化的程序：

• Oracle Scheduler ：这个是 Oracle 提供的。
• cron ：这个是 Linux 系统本身就有的。

实验知识点

• 使用 Oracle Scheduler 实现作业自动化
• 使用 crontab 实现作业自动化

Oracle Scheduler

这里以定期备份表空间 system 为例。

创建脚本

创建一个 bash 脚本，执行作业的时候会执行这个脚本，以实现备份。

$ vi /home/oracle/backup_system.sh

输入如下内容：

#!/bin/bash

rman target / <<EOF
backup tablespace system;
EOF
exit 0

连接实例

$ sqlplus system/Syl12345

创建作业

创建作业需要用到 DBMS_SCHEDULER 软件包的 CREATE_JOB 过程，它需要的参数可以使用 desc DBMS_SCHEDULER 查看到，参数详情可以参阅 DBMS_SCHEDULER 。

下面就创建一个作业，实现在每周的星期五备份表空间 system。

注：下面命令在 SQL 命令行输入

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'BACKUP_SYSTEM',
    job_type => 'EXECUTABLE',
    job_action => '/home/oracle/backup_system.sh',
    repeat_interval => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=4',
    start_date => to_date('04-03-2018','dd-mm-yyyy'),
    job_class => '"DEFAULT_JOB_CLASS"',
    auto_drop => FALSE,
    comments => 'backup system tablespace',
    enabled => TRUE
);
END;
/

=> 右边的值会传入左边的参数。

参数解释：

参数	说明
job_name	作业名。
job_type	作业类型。这里是调用的 bash 脚本，所以为 EXECUTABLE 。它还有其他值，可以调用 sql 脚本，plsql 语句块等等。
job_action	作业执行的动作。这里是执行 backup_system.sh 这个 bash 脚本。
repeat_interval	这里指在每周五早上 4 点执行备份操作。
start_date	开始日期。
job_class	作业类。这里使用的是默认的作业类。
auto_drop	这里设为 FALSE ，表示不会在作业完成后自动删除。
comments	这个作业的描述。
enabled	指示作业是否应在创建后立即启用。这里是立即启用。

创建好了作业，我们可以通过如下命令查询到作业：

SQL> select job_name,repeat_interval from dba_scheduler_jobs where job_name='BACKUP_SYSTEM';

有关创建作业更多内容可参考 CREATE_JOB 。

查看作业执行的历史记录

作业执行时，会生成一条执行作业的历史记录，我们可以通过如下命令查询：

SQL> select job_name,log_date,operation,status from dba_scheduler_job_log where job_name='BACKUP_SYSTEM';

这里如果查询没有结果，说明作业还没有执行过。

日志纪录的默认保留天数是 30 天。这个时间是可以修改，例如把时间修改为 29 天：

SQL> exec dbms_scheduler.set_scheduler_attribute('log_history',29);

修改作业

可以调用 DBMS_SCHEDULER 包的一些过程实现修改作业，启动，暂停，停止作业等操作。

例：把作业的执行时间更改为每天一次。

exec DBMS_SCHEDULER.set_attribute(-
    name => 'BACKUP_SYSTEM',-
    attribute => 'repeat_interval',-
    value => 'FREQ=DAILY' -
);

还有一些其他过程：

• run_job ：启动作业
• enable ：启动暂停的作业
• disable ：暂停作业
• copy_job ：复制作业

注：你在使用上面几个过程的时候可能会报权限的错误，那是由于实验环境的原因，默认是采用 shiyanlou 用户去执行了，自己的环境操作时不会遇此问题。 不止列举的这些，还有很多其他可调用的过程，可参阅 DBMS_SCHEDULER 。

删除作业

例如删除我们新建的 BACKUP_SYSTEM 这个作业。

exec DBMS_SCHEDULER.drop_job(job_name='BACKUP_SYSTEM');

crontab

crontab 命令常见于 Unix 和类 Unix 的操作系统之中（Linux 就属于类 Unix 操作系统），用于设置周期性被执行的指令。它通过守护进程 cron 使得任务能够按照固定的时间间隔在后台自动运行。cron 利用的是一个被称为 “cron 表”（cron table）的文件，这个文件中存储了需要执行的脚本或命令的调度列表以及执行时间。

当使用者使用 crontab 后，该项工作会被记录到/var/spool/cron/ 里。不同用户执行的任务记录在不同用户的文件中。

通过 crontab 命令，我们可以在固定的间隔时间或指定时间执行指定的系统指令或脚本。时间间隔的单位可以是分钟、小时、日、月、周的任意组合。

这里我们看一看 crontab 的格式

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * command to be executed

其中特殊字符的意义：

特殊字符	意义
*	任何时刻
,	分隔时段，例如0 7,9 * * * command代表7:00和9:00
-	时间范围，例如30 7-9 * * * command 代表7点到9点之间每小时的30分
/n	每隔n单位间隔，例如*/10 * * * * 每10分钟

crontab 准备

crontab 在本实验环境中需要做一些特殊的准备，首先我们会启动 rsyslog，以便我们可以通过日志中的信息来了解我们的任务是否真正的被执行了（在本实验环境中需要手动启动，而在自己本地中 Ubuntu 会默认自行启动不需要手动启动）

sudo service rsyslog start

在本实验环境中 crontab 也是不被默认启动的，同时不能在后台由 upstart 来管理，所以需要我们手动启动它（同样在本实验环境中需要手动启动，自己的本地 Ubuntu 的环境中也不需要手动启动）

sudo cron －f &

crontab 使用

使用 crontab 的基本语法如下：

crontab [-u username] [-l|-e|-r]

其常用的参数有：

选项	意思
-u	只有root才能进行这个任务，帮其他使用者创建/移除crontab工作调度
-e	编辑crontab工作内容
-l	列出crontab工作内容
-r	移除所有的crontab工作内容

我们这里还是以定期备份表空间为例。首先执行如下命令以添加一个任务计划：

$ crontab -e

第一次启动会出现这样一个画面，这是让我们选择编辑的工具，选择第一个基本的 vim 就可以了

选择后我们会进入一个添加计划的界面，按 i 键便可编辑文档，在文档的最后一行加上这样一行命令，实现每周日 9 点执行备份操作。

00 09 * * 0 /home/oracle/BACKUP_SYSTEM.sh

输入完成后按 esc 再输入 :wq 保存并退出。

添加成功后我们会得到 installing new crontab 的一个提示 。

为了确保我们任务添加的正确与否，我们会查看添加的任务详情：

$ crontab -l

虽然我们添加了任务，但是如果 cron 的守护进程并没有启动，当然也就不会帮我们执行，我们可以通过以下 2 种方式来确定我们的 cron 是否成功的在后台启动，若是没有则需要启动一次。

$ ps aux | grep cron

#或者使用下面

$ pgrep cron

另外，可以通过如下命令查看执行任务命令之后在日志中的信息反馈：

$ sudo tail -f /var/log/syslog

当我们并不需要某个任务的时候我们可以通过 -e 参数去配置文件中删除相关命令，若是我们需要清除所有的计划任务，我们可以使用这么一个命令去删除任务：

$ crontab -r

总结

[Yehnn]

备份恢复

实验介绍

实验内容

本节实验主要讲解了 Oracle 的备份与恢复，包括了逻辑和物理备份与恢复，完整和增量备份，以及完全恢复和不完全恢复。

实验知识点

• 概述

• 逻辑备份与恢复

• 物理备份与恢复

• 完整备份

• 增量备份

• 完全恢复

• 不完全恢复

概述

备份包括逻辑备份和物理备份：

• 逻辑备份是一个导出的操作，它会去查询数据库对象（比如表，用户，存储过程等），然后将创建数据对象和数据的命令写到一个导出转储文件中，要恢复时直接执行导入，就回去读取转储文件中的命令并执行。
• 物理备份是转储的实际文件，比如控制文件，数据文件等等。

逻辑备份与恢复

逻辑备份与恢复使用的是 Data Pump Export 和 Data Pump Import 工具。

这里以导出表 student 和导入表 student 为例。

前期准备

在开始进行导出导入之前，我们需要做如下准备：

• 创建一个目录用来存放日志和转储文件。（也可不创建，直接使用这个目录 $ORACLE_BASE/admin/<database_name>/dpdump ）
• 在 Oracle 内创建一个指向该目录的指针。

首先修改目录 /u01 的所有者：

$ sudo chown -R oracle.dba /u01

然后用 sqlplus 登录进 system 用户：

$ sqlplus system/Syl12345

然后创建一个指向该目录的指针：

SQL> create directory dpd as '<oracle_base>/admin/xe/dpdump';

上面的 <oracle_base> 替换成自己的 ORACLE_BASE 目录。可以在终端使用命令 echo $ORACLE_BASE 查看。

创建好后可以用一下命令查询到：

SQL> select * from dba_directories where directory_name='DPD';

导出操作

接下来使用 expdp 备份，注意这个命令是在 linux 命令行执行的：

$ expdp system/Syl12345 tables=student directory=dpd dumpfile=exp_student.dmp

• system/Syl12345 是执行备份的用户名和密码。

• tables 是指定要备份的表。

• directory 是指定备份存放的位置。如果不指定参数，则会存放到默认位置 DATA_PUMP_DIR ，可以使用 select * from dba_directories where directory_name='DATA_PUMP_DIR'; 查询到。

• dumpfile 是指定备份的文件名。

除了可以指定上述的参数外，还有很多其他的参数，可参见 expdp 命令行输入参数 。

导入操作

将 student 表导入并更名为 studentbak 。

$ impdp system/Syl12345 remap_table=student:studentbak directory=dpd dumpfile=exp_student.dmp

• remap_table 指定在导入时更改表名。冒号左边的是旧表名，冒号右边的是新表名。如果你想直接覆盖 student 表的话可以使用 TABLE_EXISTS_ACTION=REPLACE 。
• dumpfile 指定要导入备份的文件名。

除了可以指定上述的参数外，还有很多其他的参数，可参见 impdp 命令行输入参数。

物理备份与恢复

物理备份包含脱机备份和联机备份：

• 脱机备份：在关闭数据库后对数据库文件进行备份
• 联机备份：在数据库启动的情况下对数据库文件进行备份。此时数据库处于归档日志模式（也就是 ACHIVELOG ）模式。

我们通常使用 RMAN 工具在归档日志模式下进行备份。

RMAN 简介

RMAN （Recovery Manage）是一个备份工具。它有两种跟踪备份的方法：

• 通过备份数据库的控制文件
• 通过另一个数据库的恢复目录

通常我们将执行备份和还原操作的数据库称为目标数据库。更多有关 RMAN 的介绍可参考 RMAN Backup Concepts 。

在开始备份之前，先执行我们的准备工作。

前期准备

更改时间格式

更改 NLS_DATE_FORMAT 参数。这个步骤主要是为了让 RMAN 显示输出信息中的时间包含小时，分钟和秒，以便于能够获取执行命令更精确的时间信息。因为默认是不包含小时，分钟和秒的，所以需要更改。

$ export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'

切换到 ACHIVELOG 模式

数据库默认是在非归档日志模式（NOARCHIVELOG）下。我们可以使用下面的方法进行查看。

首先以 sys 用户登入：

$ sqlplus sys/Syl12345 as sysdba

然后即可查询当前归档模式：

SQL> select name,log_mode from v$database;

NAME               LOG_MODE
------------------ ------------------------
XE               NOARCHIVELOG

想要切换到归档日志模式下，有以下几个主要步骤：

• 关闭数据库
• 启动数据库到 MOUNT 阶段
• 切换为归档日志模式
• 打开数据库
• 查看归档模式

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

这样就切换到归档模式了，归档程序进程 ARCn 会启动，归档日志会写入快速恢复区，它所在的目录可以通过如下命令查询到：

SQL> select dest_name,destination from v$ARCHIVE_DEST where dest_name='LOG_ARCHIVE_DEST_1';

SQL> show parameter db_recovery;

登入目标数据库

使用如下语句可以登入本地数据库实例：

$ rman target /

是以 sysdba 权限的用户身份登入的。

更多和连接目标数据库相关内容可参考 Starting RMAN and Connecting to a Database 。

登入进去过后也可以执行 sql 语句，比如查询表空间有哪些：

注意：前面的 RMAN> 不用输入，只是用来代表命令是在 RMAN 命令行输入的。

RMAN> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC     CON_ID
---------- ------------------------------ --- --- --- --- ----------
         1 SYSAUX                         YES NO  YES              0
         0 SYSTEM                         YES NO  YES              0
         2 UNDOTBS1                       YES NO  YES              0
         4 USERS                          YES NO  YES              0
         3 TEMP                           NO  NO  YES              0
         5 SYLTP1                         YES NO  YES              0
         9 TMP_SP1                        NO  NO  YES              0

RMAN 有许多可用命令，参见 About RMAN Commands 。

配置 RMAN

在执行备份前，我们最好配置一下 RMAN 。我们可以通过如下命令查看它的默认配置：

RMAN> show all;

注：它的配置很多，不止下面将会用到的配置，想了解更多配置可参考 RMAN 配置 。

下面将会做这些配置步骤：

• 配置保留备份的时间
• 配置并行度
• 配置 RMAN 备份文件的存储位置
• 配置自动删除归档重做日志

配置保留备份的时间

如下命令配置了 RMAN 不会废弃 3 天内的数据文件和归档重做日志备份：

RMAN> configure retention policy to recovery window of 3 days;

压缩备份

RMAN> configure device type disk backup type to compressed backupset;

compressed 代表压缩备份集，可节省空间。 在企业版中还可以通过命令 configure device type disk parallelism n backup type to compressed backupset; 设置以 n 个通道并行执行。（n 代表一个数字）。

配置 RMAN 备份文件的存储位置

RMAN> configure channel device type disk format '/u01/rman_%U';

%U 是动态字符串。这样每次 RMAN 存储的备份文件的文件名不重复。

配置自动删除归档重做日志

下面的命令配置当归档重做日志在磁盘中至少备份两次时，归档重做日志可自动被删除。

RMAN> configure archivelog deletion policy to backed up 2 times to disk;

完成了上述的准备和配置操作后，就可以开始进入到我们备份操作的实验中了。

完整备份

下面以备份数据库和表空间为例。

备份数据库

直接执行下面的命令就可以备份数据库了：

RMAN> backup database;

备份完成过后，在我们开始设置的备份文件的存储位置可以看到我们有我们的备份文件。

备份表空间

这里就以备份我们之前在安全性管理的实验中创建的 syltp1 表空间为例。

RMAN> backup tablespace syltp1;

备份完成后，在我们配置的位置就可以看到该备份文件。

增量备份

我们上面的备份操作都是将整个数据库或者整个表空间备份下来。增量备份就是先创建一个初始备份文件，这个创建操作被称为 0 级备份，以后的备份操作（ 1 级备份）只是在初始备份文件的基础上备份了改变了的数据块。这样的话节省了很多时间和空间，因为 1 级备份的备份文件只是包含了改变了的数据块。

下面以增量备份 syltp1 表空间为例。

首先要进行我们的 0 级备份操作：

RMAN> backup incremental level 0 tablespace syltp1;

这个时候就创建了初始备份文件。以后备份的时候可以使用 1 级备份，如下所示：

RMAN> backup incremental level 1 tablespace syltp1;

完全恢复

注意完全恢复并不是对应的完整备份。它只是指这个恢复是完全的，没有丢失数据。它在数据库打开状态即可进行，根据我们上面备份操作产生的备份文件进行还原和恢复。

恢复表空间

假设表空间 syltp1 现在已经损坏了，那么我们就需要使用之前的 syltp1 表空间的备份来恢复该表空间。接下来的恢复操作有一下几个主要步骤：

• 预览该表空间的备份文件的概要信息
• 检测该表空间的备份文件的数据块完整性
• 将该表空间脱机
• 还原该表空间
• 应用归档和联机重做日志文件
• 使该表空间联机

预览 syltp 1 表空间的备份文件的概要信息

RMAN> restore tablespace syltp1 preview summary;

检测该syltp1 表空间的备份文件的数据块完整性

RMAN> restore tablespace syltp1 validate;

将 syltp1 表空间脱机

RMAN> alter tablespace syltp1 offline immediate;

脱机后该表空间就不可访问了。

还原 syltp1 表空间

RMAN> restore tablespace syltp1;

应用归档和联机重做日志文件

RMAN> recover tablespace syltp1;

使 syltp1 表空间联机

RMAN> alter tablespace syltp1 online;

至此，恢复 syltp1 表空间的操作就完成了。

不完全恢复

同样，不完全恢复并不是对应的增量备份。有时候，我们在一段时间内执行了很多错误的修改，比如说误删了很多东西，这个时候就可以用不完全备份将数据库恢复到过去的某个时刻的状态。它要在数据库装载模式下进行。并且要重建联机重做日志文件。

下面的实验将学习如何进行不完全恢复，不完全恢复主要有以下几个步骤：

• 创建还原点
• 关闭数据库
• 装载数据库
• 恢复到还原点
• 打开数据库

查询当前 SCN：

RMAN> select current_scn from v$database;

注：SCN：系统变更编号。 SCN的值是对数据库进行更改的逻辑时间点。它有点像一个版本号的概念。

创建一个测试表。

RMAN> create table syl_res (id number);

我们下面来基于 SCN 创建一个还原点。

创建还原点

RMAN> create restore point syl_res_scn;

查询我们创建的还原点：

RMAN> select name,scn from v$restore_point;

此操作查询出还原点的名称和 SCN。

还原点有一定的保存时间，由初始参数 CONTROL_FILE_RECORD_KEEP_TIME 控制的。可以使用如下命令查看其值：

RMAN> select name,value from v$parameter where name='control_file_record_keep_time';

创建好还原点后，我们删除表 syl_res ：

RMAN> drop table syl_res;
RMAN> select * from tab where tname='syl_res';

后来我们发现这个表是误删，想要回到之前的还原点，就可以执行如下操作：

关闭数据库

RMAN> shutdown immediate;

装载数据库

RMAN> startup mount;

恢复到还原点

RMAN> restore database until restore point syl_res_scn;
RMAN> recover database until restore point syl_res_scn;

使用 resetlogs 打开数据库，重建联机重做日志

RMAN> alter database open resetlogs;

再查询表 syl_res 会发现这个表已经被恢复了：

RMAN> select * from tab where tname='SYL_RES';

更多有关 RMAN 备份还原的内容可参考 Getting Started withRMAN 。

总结

[Yehnn]

存储过程

实验介绍

实验内容

本节实验主要讲解了 Oracle 的存储过程的相关操作，包括了如何创建、删除存储过程，和在存储过程的操作。

实验知识点

• 存储过程简介

• 创建存储过程

• 删除存储过程

• 存储过程中使用 DDL 语句

• 存储过程中使用游标

• 存储过程中使用事务

• 函数

存储过程简介

一个过程是可以通过名字调用的一组 PL/SQL 语句。它将复杂的业务规则从应用程序中分离出来交给数据库，应用程序只需要调用存储过程获取返回的结果就行了。这种方式将复杂的数据处理交给了服务器处理，大大提高了效率，并且后期维护会很容易，因为不用在大量的应用程序代码中修改，而只需要修改存储过程。

创建存储过程

创建存储过程必须拥有 CREATE PROCEDURE 系统权限或者 CREATE ANY PROCEDURE 系统权限。后者用于在其他用户模式中创建存储过程。

创建存储过程的具体语法可参见 创建存储过程 。我们可以创建无参和有参存储过程，下面从实践中去体会它们的实际运用。

创建无参存储过程

接下来我们来创建一个简单的存储过程，实现打印 hello world 。

首先确定 SERVEROUTPUT 是否打开：

SQL> show serveroutput;

--如果显示未打开，则使用如下方式打开
SQL> set serveroutput on;

接着创建一个名为 pro1 的存储过程：

CREATE OR REPLACE PROCEDURE pro1     --创建无参存储过程
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello world');
END;
/

我们可以查看是否创建成功：

select * from user_source where name='PRO1';

user_source 是查询当前用户的存储过程。如果要查看所有的存储过程使用 all_source 。

OR REPLACE 表示如果过程已经存在，则同名的存储过程。

下面执行一下我们创建的这个存储过程：

exec pro1;

--或者使用如下方式执行
BEGIN
pro1;
END;
/

输出结果：

PL/SQL 过程已成功完成。
hello world

创建有参存储过程

创建有参的存储过程的概念就像其他程序语言的创建带参函数，以便于数据的输入输出。创建存储过程中定义的参数有输入，输出，输入输出三种参数。下面通过实践分别学习创建带各种参数的存储过程。

输入类型参数

我们之前有写过一个根据键盘输入的学生编号查找学生姓名的 PL/SQL 程序。在这里我们调用存储过程，向其传入学生编号，返回学生姓名的方式实现姓名查询。

CREATE PROCEDURE PRO_NAME(arg_id IN NUMBER)
AS
  v_sname VARCHAR2(20);    --接收学生姓名
BEGIN
  SELECT s_name INTO v_sname FROM student WHERE s_id=arg_id; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
END;
/

IN 代表定义的参数是输入参数，是默认的，可以省略 IN。NUMBER 是输入参数的类型。

AS 后面声明了一个变量用来接收学生姓名，用于打印出查询出来的学生姓名。

BEGIN 和 END 之间是执行的查询操作。

查询学生编号为 1001 的学生姓名：

exec PRO_NAME(1001);

输出结果：

PL/SQL 过程已成功完成。
student's name is : shiyanlou1001

输出类型参数

如果有其他存储过程想使用查询出来的学生姓名怎么办呢。这个时候就需要在被调用的存储过程中定义输出参数，将接收学生姓名的参数传出来供其他存储过程使用。首先我们创建一个被调用的存储过程，它能根据传入的学生编号，将学生姓名传出。

CREATE PROCEDURE PRO_NAME2(arg_id IN NUMBER,arg_name OUT VARCHAR2)
AS
BEGIN
  SELECT s_name INTO arg_name FROM student WHERE s_id=arg_id;   --把查询出来的值给输入参数 arg_name
END;
/

OUT 代表定义的参数是输出参数。

然后创建一个存储过程，它调用上面的存储过程，然后将学生姓名打印出来：

CREATE PROCEDURE PRO_GETNAME
AS
  v_name student.s_name%TYPE;   --接收学生姓名
BEGIN
  PRO_NAME2(1001,v_name);     --调用存储过程 PRO_NAME2，将传出的学生姓名给变量 v_name
  DBMS_OUTPUT.put_line('student''s name is : ' || v_name);  --打印出变量 v_name 的值
END;
/

执行存储过程 PRO_GETNAME 及其输出结果：

SQL> exec pro_getname;

PL/SQL 过程已成功完成。
student's name is : shiyanlou1001

输入输出类型参数

顾名思义，也就是既可以输入也可以输出。例如我们根据学生编号查询对应的学生年龄。首先创建被调用的存储过程：

CREATE PROCEDURE PRO_NAME3(arg_id_age IN OUT NUMBER)  --定义输入输出参数，它既可以传入学生编号，又可以传出学生年龄
AS
BEGIN
    --根据学生编号查询学生年龄
  SELECT s_age INTO arg_id_age FROM student WHERE s_id=arg_id_age; 
END;
/

然后创建一个存储过程调用上面的存储过程，查询学生编号为 1001 的学生年龄。

CREATE PROCEDURE PRO_GETAGE
AS
  v_id_age student.s_age%TYPE;  
BEGIN
  v_id_age:=1001;     --学生编号为 1001
  PRO_NAME3(v_id_age);  --调用 PRO_NAME3 存储过程，查询出 1001 学生的年龄，再给变量 v_id_age
  DBMS_OUTPUT.put_line('student''s age is : ' || v_id_age);  --打印出学生年龄
END;
/

执行存储过程以及输出结果：

SQL> exec pro_getage;

PL/SQL 过程已成功完成。
student's age is : 10

注：编译存储过程时难免遇到各种错误，此时可以使用 show errors procedure <procedure_name> 查看更详细的错误信息。

想了解更多有关创建存储过程内容可参考创建存储过程 。

删除存储过程

例：删除存储过程 pro1 。

SQL> drop procedure pro1;

--查询可发现已经没有 pro1 这个存储过程。
SQL> select * from user_source where name='PRO1';

存储过程中使用 DDL 语句

上面创建的存储过程中使用的都是 DML 语句，如果要使用 DDL 语句，比如创建表等，不能像使用 DML 语句一样直接使用，而需要加上 EXECUTE IMMEDIATE 。

例如创建一个表，其中包含学生姓名和学生平均成绩。

CREATE PROCEDURE PRO_GRADE
AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE stu_grade AS select s_name,avg(grade) avg_grade from student join sc using(s_id) group by s_name order by s_name';   --创建表
END;

执行存储过程以及查询表 stu_grade 中的内容：

exec pro_grade;
select * from stu_grade;

存储过程中使用游标

之前我们编写的存储过程中的查询基本都是返回一个结果或者一行结果。如果想要返回查询出来的多个结果的话，就可以使用游标。游标就像一个游动的指针，它可以获取结果集中的任意行，这样的话，只要搭配循环语句，一行一行地返回，就可以打印出整个结果集的内容。我们用如下实例来具体理解。

将会使用我们在 SQL 一节学习中创建的 student 示例表。例如我们要打印出不同性别的学生编号，学生名称，学生年龄，并计算不同性别的学生数量。

思路：

1. 首先从表中查出有哪些性别。
2. 用游标和循环每次获取一个性别给一个变量。
3. 根据获取的性别，查询出对应的学生。
4. 用 for 循环遍历，输出每个学生信息。
5. 计数就是 count 函数。

代码：

CREATE OR REPLACE PROCEDURE PRO_SEX    --创建存储过程
AS
v_count NUMBER;         --变量，用于接收学生数量
v_sex student.s_sex%TYPE;   --变量，用户接收学生性别
CURSOR cur_sex      --创建游标
IS
SELECT s_sex FROM student GROUP BY s_sex;  --与游标关联的sql语句，此sql语句是查询出有哪些性别
BEGIN
OPEN cur_sex;      --打开游标

--第一个循环开始，用来循环性别
LOOP
FETCH cur_sex INTO v_sex;    --获取游标当前指向的数据给变量 v_sex，第一次 v_sex 被赋值为 woman，第二次 v_sex 被赋值为 man
EXIT WHEN cur_sex%NOTFOUND;    --当获取不到数据时退出
DBMS_OUTPUT.PUT_LINE('===================');
DBMS_OUTPUT.PUT_LINE(v_sex || ' :');

--第二个循环开始，用来循环查询出的结果集，获取每行数据
FOR row_student IN     --FOR 循环遍历结果中每行数据给变量 row_student 
(
  SELECT * FROM student WHERE s_sex=v_sex  --查询当前获取的性别有哪些学生
)
LOOP
  DBMS_OUTPUT.PUT_LINE(
  'student id: ' || row_student.s_id ||      --打印学生编号
  ' student name: '|| row_student.s_name ||  --打印学生姓名
  ' student age: ' || row_student.s_age      --打印学生年龄
  );
END LOOP;  --第二个循环结束

SELECT count(s_id) INTO v_count FROM student WHERE s_sex=v_sex;   --查询出当前获取的性别的学生数量
IF SQL%FOUND THEN     --如果此查询有结果就执行下面的语句
  DBMS_OUTPUT.PUT_LINE('count: ' || v_count);   --打印数量
END IF;

END LOOP;  --第一个循环结束
CLOSE cur_sex;    --关闭游标
END;
/

执行以及输出结果：

SQL> exec pro_sex;

PL/SQL 过程已成功完成。
===================
woman :
student id: 1002 student name: shiyanlou1002 student age: 20
student id: 1004 student name: shiyanlou1004 student age: 40
count: 2
===================
man :
student id: 1001 student name: shiyanlou1001 student age: 10
student id: 1003 student name: shiyanlou1003 student age: 18
student id: 1005 student name: shiyanlou1005 student age: 17
count: 3

补充：

• CURSOR cur_sex
  IS
  SELECT s_sex FROM student GROUP BY s_sex;

  cur_sex 是创建的游标名称。

  IS 后面是关联的查询语句，游标可以从此查询的结果集中获取某行的数据。注意不能是 SELECT INTO 。

  用这种方式创建的游标被称为显式游标，需要手动打开，手动关闭。

• cur_sex%NOTFOUND

  这里是使用的显式游标的属性。%NOTFOUND 表示当获取不到数据时返回 TRUE ，反之返回 FALSE 。

  显示游标还有其他属性 %FOUND ，%ISOPEN ，%ROWCOUNT 。可参见 游标属性 。

• SQL%FOUND 

  这里是用了一个隐式游标，SQL 是隐式游标的默认名称，%FOUND 是它的属性，表示 DML 操作是否对数据有影响，比如查询语句是否返回有结果，插入语句是否成功等等。如果有影响则返回 TURE ，反之返回 FALSE 。

  隐式游标也有和显式游标相同的属性，只是 %ISOPEN 永远为 FALSE 。

存储过程中使用事务

事务是用来保证数据的一致性。一个很常见的例子就是银行转账，转出账户减少金额，转入账户增加金额，肯定不允许有转出成功，转入却失败或者转出失败，转入成功的情况发生。这里整个交易过程就是一个事务，在这个事务中任意一个操作失败就都失败，所有的操作成功，整个交易过程才算成功。如下示例更好地理解事务。

新建一个像表 student 插入两条数据的存储过程：

CREATE OR REPLACE PROCEDURE PRO_TRAN
AS
BEGIN
  INSERT INTO student VALUES(1006,'shiyanlou1006','woman',22);
  COMMIT;         --提交事务
  INSERT INTO student VALUES(1007,'shiyanlou1007','woman',22);
END;
/

然后查询表中数据：

SQL> select * from student;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20
      1003 shiyanlou1003        man                          18
      1004 shiyanlou1004        woman                        40
      1005 shiyanlou1005        man                          17
      1006 shiyanlou1006        woman                        22
      1007 shiyanlou1007        woman                        22

可以看到数据已经插入进去。但是如果我们进入一个新的会话，登入同样的账户查询你会发现少了 1007 那条数据：

因为我们是字符界面下，所以使用 tmux 新开一个会话：

$ tmux new -s sqlplus2
$ tmux a -t sqlplus2

1. 然后执行如下语句：

$ sqlplus system/Syl12345

2. 进入 sql 命令行，然后执行如下查询语句：

SQL> select * from student;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20
      1003 shiyanlou1003        man                          18
      1004 shiyanlou1004        woman                        40
      1005 shiyanlou1005        man                          17
      1006 shiyanlou1006        woman                        22

这是因为我们只提交了第一条插入语句的修改结果。我们新开的这个会话只能查询到已经提交的数据。

下面我们再用一个实例说明数据的一致性。首先退出当前的会话，回到第一个会话：

$ tmux detach

然后将开始插入的数据两条数据删除：

SQL> delete from student where s_id=1006;
SQL> delete from student where s_id=1007;
SQL> commit;

然后编写如下存储过程：

CREATE OR REPLACE PROCEDURE PRO_TRAN
AS
BEGIN
  INSERT INTO student VALUES(1006,'shiyanlou1006','woman',22);
  INSERT INTO student VALUES(1006,'shiyanlou1007','woman',22);
  COMMIT;
EXCEPTION WHEN OTHERS THEN
  rollback;     --如果出现错误则回滚
END;
/

这里的 COMMIT 表示两条数据都插入成功才提交。

这里故意制造了一个主键错误，我们插入的学生的学生编号相同了。所以都不会插入成功。我们来查询下结果是否如此：

SQL> select * from student;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20
      1003 shiyanlou1003        man                          18
      1004 shiyanlou1004        woman                        40
      1005 shiyanlou1005        man                          17

注意：我们在上面提交的都是 DML 操作，而 DDL 操作（比如创建表等）是自动提交的。

实际上 DML 语句也能自动提交，它跟 AUTOCOMMIT 这个参数有关。首先我们来查询一下 AUTOCOMMIT 的值：

SQL> show autocommit;
autocommit OFF

可以看到是关闭的。执行如下语句可以开启它，也就是当我们执行 DML 语句时会自动提交了。

SQL> set autocommit on;

接下来编写如下的存储过程来测试一下：

CREATE OR REPLACE PROCEDURE PRO_TRAN
AS
BEGIN
  INSERT INTO student VALUES(1006,'shiyanlou1006','woman',22);
  INSERT INTO student VALUES(1007,'shiyanlou1007','woman',22);
END;
/

然后执行存储过程：

SQL> exec pro_tran;

到第二个会话中去查询 student 表中的数据：

$ tmux a -t sqlplus2

SQL> select * from student;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20
      1003 shiyanlou1003        man                          18
      1004 shiyanlou1004        woman                        40
      1005 shiyanlou1005        man                          17
      1006 shiyanlou1006        woman                        22
      1007 shiyanlou1007        woman                        22

从结果中你会发现已经自动提交了。

上面的事务都比较短，如果一个事务很长，我们想要回滚的时候能够回滚到某一段的话，可以设置不同的保存点。如下示例将学习如何设置保存点以及回滚到某一个保存点。

首先我们将之前插入的数据删除：

SQL> delete from student where s_id=1006;
SQL> delete from student where s_id=1007;
SQL> commit;

然后回到第一个 会话窗口：

$ tmux detach

编写如下存储过程：

CREATE OR REPLACE PROCEDURE PRO_TRAN
AS
BEGIN
  INSERT INTO student VALUES(1006,'shiyanlou1006','woman',22);
  SAVEPOINT SP1;   --设置第一个保存点
  INSERT INTO student VALUES(1007,'shiyanlou1007','woman',22);
  SAVEPOINT SP2;   --设置第二个保存点
  rollback to SP1;   --回滚到第一个保存点
END;
/

执行以及输出结果：

SQL> select * from student;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20
      1003 shiyanlou1003        man                          18
      1004 shiyanlou1004        woman                        40
      1005 shiyanlou1005        man                          17
      1006 shiyanlou1006        woman                        22

从结果中可以看到只插入了 1006 那行数据。

最后，设置 AUTOCOMMIT 的值为 off，并且删除上面插入的数据。

SQL> set autocommit off;
SQL> delete from student where s_id=1006;

总结

[Yehnn]

表空间及数据文件

实验介绍

实验内容

本节实验主要讲解了 Oracle 的表空间及数据文件的内容，实现对表空间和对数据文件的管理。

实验知识点

• 默认表空间

• 表空间的管理

• 临时表空间的管理

• 数据文件管理

默认表空间

Oracle 安装时会自动创建几个默认表空间，可以在 dba_tablespaces 这张表查看到默认表空间：

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

• SYSTEM ：管理任何其他表空间。它包含数据字典，有关数据库管理信息的表和视图，编译的存储对象（如触发器，过程等）。
• SYSAUX ：辅助表空间。
• UNDOTBS1 ：撤销表空间。存储的是撤销信息，可以用于恢复操作。
• TEMP ：临时表空间。可用于排序操作等。
• USERS ：存储用户的表和索引数据。

使用 dba_users 这张表可以查询用户的默认表空间。

SQL> select default_tablespace,username from dba_users;

从结果中可以发现，一个用户只能分配一个表空间，一个表空间可以被多个用户使用。

如果想查询 SYS 和 SYSTEM 这两个用户的默认表空间可以使用 where 限定查询条件：

SQL> select default_tablespace,username from dba_users where username='&u1' or username='&u2';
Enter value for u1: SYS
Enter value for u2: SYSTEM
old   1: select default_tablespace,username from dba_users where username='&u1' or username='&u2'
new   1: select default_tablespace,username from dba_users where username='SYS' or username='SYSTEM'

DEFAULT_TABLESPACE         USERNAME
-------------------- ---------------
SYSTEM                 SYS
SYSTEM                 SYSTEM

可以看到 SYS 和 SYSTEM 这两个用户的默认表空间都是 SYSTEM 表空间。

表空间的管理

创建表空间

表空间的类型有如下三种：

• 永久表空间：永久表空间包含持久性模式对象。永久表空间中的对象存储在数据文件中。
• 撤销表空间：撤消表空间是在自动撤消管理模式下运行数据库时，管理撤消数据的一种永久性表空间。
• 临时表空间：临时表空间仅包含会话期间的模式对象。临时表空间中的对象存储在临时文件中。

（此段来自 创建表空间-官方文档 ）

下面我们来创建一个最简单的表空间：

SQL> create tablespace tp1
datafile 'tp1.dbf'
size 1M;

第一行的 tp1 是表空间的名字。

第二行是表空间的数据文件是 tp1.dbf 。

第三行是表空间大小为 1M 。

默认情况下，创建的表空间不会自动扩展，如果我们想要在数据文件充满时进行自动扩展，可以创建一个自动扩展的表空间，当数据文件装满后，它会自动增加数据文件的尺寸。

SQL> create smallfile tablespace tp2
datafile 'tp2.dbf'
size 10M autoextend on next 1M maxsize 20M
extent management local autoallocate
segment space management auto;

代码详细解释：

行数	解释
1	表空间是 smallfile ，也就是小文件表空间，它可以包含多个数据文件或临时文件。默认下就是 smallfile ，可以省略。替换项是 bigfile ，只包含一个数据文件或临时文件。
2	数据文件的名称
3	创建的数据文件的大小是 10M，当数据文件充满时，会自动扩大 1M，最大为 20M。
4	表空间使用位图来分配区间，其大小会自动设置。这是默认的，可以不写。
5	表空间中的段使用位图来跟踪块的使用情况。这是默认的，可以不写。

想了解更多有关 bigfile 和 smallfile 内容可以参考 bigfile|smallfile 。

更改表空间

重命名表空间

例如我们想要把 tp2 这个表空间重命名为 syl_tp ，可以使用如下语句：

SQL> alter tablespace tp2 rename to syl_tp;

注意：重命名表空间不会重命名与之关联的任何数据文件。

设置表空间的读写状态

表空间在创建时默认是读写状态，我们可以将其设置为只读状态。

SQL> alter tablespace tp1 read only;

如果要改回读写状态，可以使用下面的语句：

SQL> alter tablespace tp1 read write;

设置表空间的可用状态

可用状态指的是表空间的两种状态：

• 联机状态 ONLINE：表空间可用，可以被使用。
• 脱机状态 OFFLINE：表空间和其数据文件不可用。脱机状态还包括三种方式，即
  • NORMAL （正常状态）：将表空间中的所有数据文件中的所有块刷新到系统全局区域（SGA）之外。这是默认的方式。
  • TEMPORARY （临时状态）：Oracle 数据库会为表空间中的所有联机数据文件执行检查点，但不能确保可以写入所有文件。
  • IMMEDIATE （立即状态）：立即使表空间和数据文件脱机，不会确保表空间文件可用，并且不执行检查点。可能丢失未提交的更改。

我们下面将表空间 tp1 以 normal 方式脱机：

SQL> alter tablespace tp1 offline normal;

注意：不能把一个临时表空间脱机。

再将 tp1 设置成联机状态：

SQL> alter tablespace tp1 online;

调整表空间的大小

在创建表空间时，如果使用了 autoextend ，则可以自动调整数据文件的大小。如果没有使用，就需要我们手动去调整。有两种调整方式可供选择：

• 调整现有数据文件的大小

下面我们将表空间 tp1 的尺寸更改为 2M ：

SQL> alter database datafile 'tp1.dbf' resize 2m;

• 向表空间添加数据文件

SQL> alter tablespace tp1 add datafile 'tp1_02.dbf' size 1m;

使用 v$tablespace 和 v$datafile 这两个视图可以查看到表空间的数据文件和大小：

SQL> select t.name tname,d.name dname,d.bytes from v$tablespace t join v$datafile d using(ts#) where t.name like 'TP1';

ts# ：tablespace number。

删除表空间

例如我们要删除 tp1 这个表空间及其数据文件：

SQL> drop tablespace tp1 including contents and datafiles;

如果表空间包含的表与另一个表空间的表存在外键关系，就会删除失败，这个时候我们可以使用 cascade constraints 将表空间中的完整性也删除：

SQL> drop tablespace syl_tp including contents cascade constraints;

使用下面的命令查看表空间会发现表空间已经被删除：

SQL> select tablespace_name from dba_data_files;

想了解更多删除表空间内容可参考 删除表空间

临时表空间的管理

临时表空间一般用于在进行数据库操作时，内存不够用了，就会写入到临时表空间，操作完成后，临时表空间的内容就会自动清空。oracle 默认创建的 TEMP 表空间就是临时表空间。

创建临时表空间

例如创建一个临时表空间 tmp_sp ：

SQL> create temporary tablespace tmp_sp1 tempfile 'tmp_sp1.dbf' size 10M;

用 dba_temp_files 表可以查询到我们已经创建好了临时表空间：

SQL> select tablespace_name from dba_temp_files;

设置默认表空间

例如我们把临时表空间 tmp_sp1 设置成默认表空间：

SQL> alter database default temporary tablespace tmp_sp1;

用如下命令查看默认临时表空间可发现已经更改：

SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

下面把 TEMP 临时表空间设置成默认表空间：

SQL> alter database default temporary tablespace TEMP;

创建临时表空间组

临时表空组至少包含一个临时表空间。下面我们来创建一个临时表空间组 tmpgroup ，并把上面创建的临时表空间 tmp_sp1 放入其中。

SQL> alter tablespace tmp_sp1 tablespace group tmpgroups;

使用如下命令可以查看到临时空间组已经创建：

SQL> select * from dba_tablespace_groups;

另外，可以在创建临时表空间的时候创建临时表空间组，如下所示：

SQL> create temporary tablespace tmp_sp2 tempfile 'tmp_sp2.dbf' size 3M tablespace group tmpgroup2;

删除临时表空间组

当把一个临时表空间组里的所有临时表空间删除了，临时表空间组也被删除了。

例如我们删除临时表空间 tmp_sp2 ：

SQL> drop tablespace tmp_sp2 including contents and datafiles;

因为临时表空间组 tmpgroup2 中只有一个临时表空间 tmp_sp2 ，当我们删除过后，临时表空间组 tmpgroup2 也被删除了。

注意：不能删除默认的临时表空间。

数据文件管理

oracle 12c 支持在数据文件联机状态时对其进行重命名和移动。其他版本需要先把数据文件脱机才能进行重命名和移动。

重命名以及移动数据文件

例如我们新建一个表空间，包含两个数据文件 mvdata.dbf 和 mvdata2.dbf ，然后将其更名为 syl_mvdata.dbf ：

SQL> create tablespace mvdata datafile 'mvdata.dbf' size 5m;
SQL> alter tablespace mvdata add datafile 'mvdata2.dbf' size 5m;
SQL> alter tablespace move datafile 'mvdata.dbf' to 'syl_mvdata.dbf';

移动数据文件跟重命名类似，只是将 to 后面的字符串改成你的目的位置即可。

可以使用如下命令查看当前有的数据文件：

SQL> select name from v$datafile;

删除数据文件

比如我们删除 mvdata2.dbf 这个数据文件：

SQL> alter tablespace mvdata drop datafile 'mvdata2.dbf';

再次查看数据文件会发现已经删除。

注意：删除数据文件是有一些先决条件的：

• 数据文件中不存在数据。
• 数据文件不是表空间中唯一或第一个数据文件。
• 数据文件或数据文件所在的表空间不是只读状态。

总结

[Yehnn]

表

实验介绍

实验内容

本节实验主要讲解了 Oracle 的表操作，如何实现对表进行创建、重重命名、复制、删除和修改等。

实验知识点

• 常用数据类型
• 表的创建
• 表的重命名
• 截断表
• 表的复制
• 表的删除
• 修改表的结构

常用数据类型

以下是介绍一些我们最常用的数据类型：

• 字符串：使用 VARCHAR2 描述（其他数据库用 VARCHAR）。例如姓名，地址等。
• 数字：使用 NUMBER 描述。如果要描述小数则使用 NUMBER(p,s) ，其中 s 是小数位（scale），p-s 是整数位，例如 number(4,2) 代表存储小数位数为 2，总长度最多为 4 的浮点数。也可以用 INT 描述整数，FLOAT 描述小数。
• 日期：使用 DATE 描述。Oracle 里的 DATE 包含有日期时间。其他数据库一般 DATE 只是日期，DATETIME 才表示日期时间。
• 大文本数据：使用 GLOB 描述。最多可以保存 4G 的文字信息。

想了解更多数据类型可参考Oracle SQL 数据类型

创建表

普通创建

语法：

CREATE TABLE 表名称(
    列名称     类型      [DEFAULT 默认值]
    列名称     类型      [DEFAULT 默认值]
    ...
)

例如：

注：前面的 SQL> 不用输入，只是为了说明是在 SQL 命令行输入内容。

SQL> CREATE TABLE student(
  id        NUMBER,
  name      VARCHAR2(20),
  age       number(3),
  birthday  DATE    DEFAULT SYSDATE,
  note      CLOB
);

查看是否已经创建好 student 表：

SQL> select * from tab where tname='STUDENT';

查看 student 表结构：

SQL> desc student;

我们可以向其中插入数据：

SQL> INSERT INTO student(id,name,age,birthday,note) VALUES (1,'syl',19,TO_DATE('1999-01-01','yyyy-mm-dd'),'note test');
SQL> INSERT INTO student(id,name,age,birthday,note) VALUES (2,'lou',21,TO_DATE('1997-01-01','yyyy-mm-dd'),'note test');

查看我们插入的数据：

SQL> select * from student;

通过查询结果创建

例如我们查询出 student 表中 name 字段值为 syl 的记录，通过查询结果创建一个 名叫 syl_stu 的表。

SQL> create table syl_stu as select * from student where name='syl';
SQL> select * from syl_stu;

表的重命名

SQL> RENAME student TO stu;

我们使用数据字典查询一个用户全部的数据表，可以发现表已经被更名：

SQL> select * from user_tables;

列出数据库表对象的全部信息内容，例如名称，存储情况等。

数据字典：记录所有对象的信息。

• 用户级别：user_*，指一个用户可以使用的数据字典。
• 管理员级别：dba_*，指由数据库管理员使用的数据字典。
• 全部级别：all_* ，指不管是用户还是管理员都可以使用。

修改表结构

首先我们使用 desc 来查看表的结构。

SQL> desc stu;
名称       空值 类型           
-------- -- ------------ 
ID          NUMBER       
NAME        VARCHAR2(20) 
AGE         NUMBER(3)    
BIRTHDAY    DATE         
NOTE        CLOB      

接下来我们来修改表的结构。

增加列 （ADD）

例一：为 stu 表增加一列 address ，设默认值为 none。

SQL> alter table stu add(address varchar2(50) default 'none');

然后查看表中数据会发现多了 address 列，并且值为 none：

SQL> select * from stu;

例二：为 stu 表增加一列 email ，不设默认值。

SQL> alter table stu add(email varchar2(50));

再次查看表，会发现多了 email 列，并且为 null。

SQL> select * from stu;

修改表中的数据列 （MODIFY）

例一：修改 stu 表的 name 列的类型为 varchar2(30) 。

SQL> alter table stu modify (name varchar2(30));

例二：修改 stu 表的 email 列的默认值为 no email 。

SQL> alter table stu modify (email default 'no email');

注意：虽然这里我们设置了默认值，但是以前插入的数据的 email 列的值仍然是 null，只有我们新插入的数据的 email 列的值才默认为 no email ，如下所示：

SQL> select * from stu;
SQL> INSERT INTO stu(id,name,age,birthday,note) VALUES (3,'plus',19,TO_DATE('1999-01-01','yyyy-mm-dd'),'note test');
SQL> select * from stu;

重命名列（RENAME COLUMN）

例：将 address 列的名称改为 saddress 。

SQL> alter table stu rename column address to saddress;

重命名列还有一个作用就是在要删除一个列的时候，用来确认是否有用户或应用程序正在使用此列，如果有的话，就会报错。

删除列 （DROP）

例：将 saddress 列删除。

SQL> alter table stu drop (saddress);

或者使用
SQL> alter table stu drop column saddress;

在删除一列的时候如果数据太多，通常需要一定时间，如果想快速使用户或应用程序不能访问某些列的时候，可以使用 unused 标记列。例如下面我们标记 email 列：

SQL> alter table stu set unused (email);

标记过后使用 desc stu 查看会发现没有显示 email 列，但是它并没有真正被删除，执行下面的语句可以删除所有被 unused 标记的列。

SQL> alter table stu drop unused columns;

复制表结构

复制表结构类似于用查询创建表，只是修改了 where 条件。例如，我们要复制 stu 的表结构。

SQL> create table stu_cp as select * from stu where 1=2;

该语句只是复制了结构，并没有复制其数据：

SQL> desc stu_cp;
SQL> select * from stu_cp;

添加约束

有时候我们存储的数据需要满足某些条件，例如：假设我们定义一个学生表，表中每个学生的学号和身份证号码是不可以重复的，学生的学号不能为空等等。这就是约束，数据库上为了保证数据表中的数据完整性而增加了约束。

约束一共有六种：数据类型，非空约束，唯一约束，主键约束，检查约束，外键约束。

注意：约束太多，会导致更新速度缓慢，有些验证操作应交给应用程序去做。

非空约束 NOT NULL

非空约束（NK）指表中的某一个字段的内容不允许为空。使用 not null 声明。

例：创建一个名叫 tech 的表，表中有 id 和 name 两个字段。设置 name 字段不为空。

SQL> create table tech(id number,name varchar2(20) not null);

下面我们就来插入一条没有 name 的数据。

SQL> insert into tech(id) values (2);

你会发现报错了 ORA-01400: 无法将 NULL 插入 ("SYSTEM"."TECH"."NAME") 。

唯一约束 UNIQUE

唯一约束（UK）指表中某一字段内容不允许重复。使用 unique 。

例：为表 tech 增加一列 email ，不允许重复。

SQL> alter table tech add(email varchar2(30) unique);

下面来插入两条 email 重复的数据：

SQL> insert into tech(id,name,email) values (2,'syl2','syl2@qq.com');
SQL> insert into tech(id,name,email) values (3,'syl3','syl2@qq.com');

报错 SQL 错误: ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C007385) 。这个 SYS_c007385 实际上就是约束的对象信息（约束也是数据库对象），可以从数据字典中查到。

注意：这个对象信息可能跟你的报错不一样，实际查询时，要改为你自己实际看到的对象信息才可以查询到。

-- 格式化
SQL> col owner for a10;
SQL> col constraint_name for a20;
SQL> col table_name for a15;
SQL> col column_name for a20;
-- 查询
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where constraint_name='SYS_C007385';

输出结果如下：

OWNER      CONSTRAINT_NAME      TABLE_NAME      COLUMN_NAME
---------- -------------------- --------------- --------------------
SYSTEM     SYS_C007385          TECH            EMAIL

可以看到这个约束是在 TECH 表的 EMAIL 列。

这样去数据字典中查还是比较麻烦，我们可以使用下面的方式创建唯一约束，这样能够直接根据报错知道是哪一个字段违反了约束条件。例如我们增加 cid 字段，添加唯一约束。

SQL> alter table tech add(cid varchar2(30),constraint uk_email unique(email));

注意：设置的约束名称（这里是 uk_email ）不可以重复。

向里面插入数据：

SQL> insert into tech(id,name,email,cid) values (3,'syl3','syl3@qq.com',1);
SQL> insert into tech(id,name,email,cid) values (4,'syl4','syl4@qq.com',1);

报错 SQL 错误: ORA-00001: 违反唯一约束条件 (SYSTEM.UK_EMAIL) 。

主键约束 PRIMARY

主键约束（PK）= 非空约束 + 唯一约束 。主键约束在每一个数据表中只有一个，但是一个主键约束可以由数据表中多个列组成。一般我们只把一个字段定义成主键。

例：为表 tech 添加主键约束

SQL> alter table tech add constraints pk_id primary key(id);

检查约束 CHECK

检查约束（CK）限定数据的正确性。

例一：设定性别只能为 man 或者 female 。

SQL> alter table tech add (sex varchar2(10),constraints chk_sex check(sex='man' or sex='female'));

例二：设定年龄在 5 到 200 岁。

SQL> alter table tech add (age number(3),constraints chk_age check(age between 5 and 200));

或者
SQL> alter table tech add (age number(3),constraints chk_age check(age>=5 and age<=200));

外键约束 FOREIGN

比如我们现在有两张表，实验楼的课程表和课程类别表。一个课程是属于一个类别的。我们在课程表中增加了一个字段来描述课程的类别。这个字段的值来自于课程类别表的类别 id 。为了不允许插入类别表中类别 id 以外的值，我们就定义一个外键约束。

-- 类别表
SQL> create table syl_category(
  cid number(5) primary key,
  name varchar2(30)
);
-- 课程表
SQL> create table syl_course(
  id number(10) primary key,
  name varchar2(30),
  cid number(5),
  constraint fk_cate foreign key(cid) references syl_category(cid) on delete cascade
);

上面的 syl_category 表就叫父表 ，syl_course 表就叫子表 。

on delete cascade 表示当 syl_category 表中的 cid 被删除时，对应的 syl_cource 表中的 cid 也会被删除。

向其中插入数据：

SQL> insert into syl_category(cid,name) values (1,'cate1');
SQL> insert into syl_course(id,name,cid) values (1,'course1',1);
SQL> insert into syl_course(id,name,cid) values (2,'course1',2);

会发现第三句报错 SQL 错误: ORA-02291: 违反完整约束条件 (SYSTEM.FK_CATE) - 未找到父项关键字

删除约束

语法：

SQL> alter table <table_name> drop constraint <constraint_name>;

例如我们删除表 tech 的 sex 字段的检查约束 chk_sex 。

SQL> alter table tech drop constraint chk_sex;

删除表内容

首先我们来进行普通的删除操作。

SQL> commit; 
SQL> delete from stu;

查看表内容会发现已经被清空。但是在我们执行 rollback 回滚操作过后会发现数据又被恢复：

SQL> rollback;
SQL> select * from stu;

rollback 会恢复到上一次 commit 的数据。

上面的删除操作并没有立即将表所占的资源（约束，索引等）立即释放掉，如果想要完全删除，就需要使用 truncate ，也叫做截断表的操作。例如：

SQL> truncate table stu;

这个时候再执行回滚操作，会发现数据无法恢复：

SQL> rollback;
SQL> select * from stu;

表的删除

例如我们要删除 syl_stu 这张表，可以使用如下命令：

SQL> drop table syl_stu;

注意：如果删除的表是含有约束的父表的话，将会报错。此时需要使用 drop table <table_name> cascade constraints 删除。

总结

[Yehnn]

SQL

实验介绍

实验内容

本节实验主要讲解了 SQL 的一些基础知识。

实验知识点

• SQL 简介

• SQL

• 创建示例表

• 简单查询

• 通配符

• 函数

• 分组排序

• 限制返回的行数

• 子查询

• 表的连接

• 视图

SQL 简介

SQL 是 Structured Query Language 的首字母缩写，意为结构化查询语言，它可以告诉 Oracle 对哪些信息进行选择，插入，更新和删除。相信大家已经很熟悉，在 mysql 和 sqlserver 中我们也经常使用。

SQL

创建示例表

首先，试着用前面学到的内容创建如下 3 个示例表：

学生表（student）：

字段	类型	约束	默认值
s_id	number	主键
s_name	varchar2(20)	not null
s_sex	varchar2(10)	为 man 或者 woman	man
s_age	number	not null

插入如下数据：

学号(s_id)	姓名(s_name)	性别(s_sex)	年龄(s_age)
1001	shiyanlou1001	man	10
1002	shiyanlou1002	woman	20
1003	shiyanlou1003	man	18
1004	shiyanlou1004	woman	40
1005	shiyanlou1005	man	17

课程表（course）：

字段	类型	约束
c_id	number	主键
c_name	varchar(20)	not null，唯一
c_time	number

插入如下数据：

课程号(c_id)	课程名(c_name)	课时(c_time)
1	java	13
2	python	12
3	c	10
4	spark	15

选课表（sc）:

字段	类型	约束
s_id	number	主键，外键（来自 student 表的 s_id）
c_id	number	主键，外键（来自 cource 表的 c_id）
grade	number

插入如下数据：

学号(s_id)	课程号(c_id)	成绩(grade)
1001	3	70
1001	1	20
1002	1	100
1001	4	96
1002	2	80
1003	3	75
1002	4	80

以下是创建示例表的脚本：

CREATE TABLE student(
    s_id number,
    s_name VARCHAR2(20) NOT NULL,
    s_sex VARCHAR2(10) DEFAULT 'man',
    s_age NUMBER NOT NULL,
    CONSTRAINT pk_sid PRIMARY KEY (s_id)
);

CREATE TABLE course(
    c_id NUMBER,
    c_name VARCHAR2(20) NOT NULL,
    c_time NUMBER,
    CONSTRAINT pk_cid PRIMARY KEY (c_id), 
    CONSTRAINT uk_cname UNIQUE (c_name)
);

CREATE TABLE sc(
    s_id NUMBER,
    c_id NUMBER,
    grade NUMBER,
    CONSTRAINT pk_scid PRIMARY KEY (s_id, c_id),
    CONSTRAINT fk_sid FOREIGN KEY (s_id) REFERENCES student(s_id),
    CONSTRAINT fk_cid FOREIGN KEY (c_id) REFERENCES course(c_id)
);

desc student;
desc course;
desc sc;

INSERT INTO student VALUES(1001, 'shiyanlou1001', 'man', 10);
INSERT INTO student VALUES(1002, 'shiyanlou1002', 'woman', 20);
INSERT INTO student VALUES(1003, 'shiyanlou1003', 'man', 18);
INSERT INTO student VALUES(1004, 'shiyanlou1004', 'woman', 40);
INSERT INTO student VALUES(1005, 'shiyanlou1005', 'man', 17);

INSERT INTO course VALUES(1, 'java', 13);
INSERT INTO course VALUES(2, 'python', 12);
INSERT INTO course VALUES(3, 'c', 10);
INSERT INTO course VALUES(4, 'spark', 15);

INSERT INTO sc VALUES(1001, 3, 70);
INSERT INTO sc VALUES(1001, 1, 20);
INSERT INTO sc VALUES(1002, 1, 100);
INSERT INTO sc VALUES(1001, 4, 96);
INSERT INTO sc VALUES(1002, 2, 80);
INSERT INTO sc VALUES(1003, 3, 75);
INSERT INTO sc VALUES(1002, 4, 80);

commit;

简单查询

查询年龄在 20-50 岁的学生：

SQL> col s_name for a20
SQL> select * from student where s_age between 20 and 50;

输出结果如下：

       S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1002 shiyanlou1002        woman                        20
      1004 shiyanlou1004        woman                        40

除了上面的 between ... and ... ，还有一些其他的运算符：

操作符	释义
=	等于
<>	不等于
!=	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于
BETWEEN ... AND...	检查值的范围
IN	检查是否在一组值中
NOT IN	检查一个值是否不在一组值中
IS {TRUE|FALSE}	判断 bool 值
IS NULL	NULL 值测试
IS NOT NULL	NOT NULL 值测试
LIKE	模式匹配
NOT LIKE	否定匹配

另外，我们还可以使用多个表达式进行逻辑运算。逻辑运算符如下表：

逻辑运算符	释义
OR, ||	或
AND, &&	与
NOT, !	非

例如，我们也可以通过 AND 查找年龄在 20~50 岁的学生。

SQL> SELECT * FROM student WHERE s_age>=20 AND s_age<=50;

除了上述所列举的运算符和表达式之外，我们还可以进行一些数学的计算操作，例如加减乘除等，如下示例，我们将 student 表中的 s_id 和 s_age 分别进行加减乘除操作。

SQL> SELECT s_id,s_age,s_id+s_age,s_id-s_age,s_id*s_age,s_id/s_age FROM student;

      S_ID      S_AGE S_ID+S_AGE S_ID-S_AGE S_ID*S_AGE S_ID/S_AGE
---------- ---------- ---------- ---------- ---------- ----------
      1001         10       1011        991      10010      100.1
      1002         20       1022        982      20040       50.1
      1003         18       1021        985      18054 55.7222222
      1004         40       1044        964      40160       25.1
      1005         17       1022        988      17085 59.1176471

通配符

在上述内容中，我们有提到 LIKE，它是一个字符串比较函数，用于 LIKE 的通配符有两个：

• % 百分号，匹配任意数量的字符

• _ 下划线，匹配一个字符

例如，我们查看学生表中 s_name 以 2 结尾的学生信息：

SQL> SELECT * FROM student WHERE s_name LIKE '%2';

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1002 shiyanlou1002        woman                        20

函数

MAX，MIN

查找列的最大值和最小值。例如查找学生表中的年龄的最大值和最小值：

SQL> SELECT max(s_age),min(s_age) FROM student;

MAX(S_AGE) MIN(S_AGE)
---------- ----------
        40         10

SUM，AVG

SUM 和 AVG 分别可以用来求和以及求平均值。

例如，查找选课表中，s_id=1001 学生成绩的总分及平均值：

SQL> SELECT avg(grade),sum(grade) FROM sc WHERE s_id='1001';

AVG(GRADE) SUM(GRADE)
---------- ----------
        62        186

除此之外，我们还可以使用 DISTINCT 修饰符指定从结果集中删除重复的行，对应的是 ALL ，为默认项。通过如下示例来了解：

SQL> SELECT grade FROM sc;

     GRADE
----------
        70
        20
       100
        96
        80
        75
        80
  已选择 7 行。

# 重复的 80 的记录会被删除
SQL> SELECT DISTINCT grade FROM sc;

     GRADE
----------
       100
        70
        20
        96
        75
        80

已选择 6 行。

COUNT

COUNT 函数用于计数。

例如，我们统计选课表中 s_id=1001 有多少条记录，就可以使用 count

SQL> SELECT count(s_id) FROM sc WHERE s_id=1001;

COUNT(S_ID)
-----------
          3

CONCAT

CONCAT 是一个字符串函数。用于连接字符串。语法如下：

CONCAT(char1,char2);

意为连接字符串 char1 和 char2 。

例如：把 student 表中 s_id=1001 对应的 s_name 和 s_sex 字段连接。

SQL> SELECT CONCAT(CONCAT(s_name,'''s sex is '),s_sex) "sex" FROM student WHERE s_id=1001;
sex
-------------------------------------------------------------------
shiyanlou1001's sex is man

想了解更多有关函数的内容可以参考 SQL 函数

分组排序

关于分组我们会学习到 SELECT 的两个子句，分别为：

• GROUP BY
• HAVING

详细的语法格式对于初学者来说并不友好，下面我们通过实例来讲解相关的内容。

GROUP BY

分组(GROUP BY) 功能，有时也称聚合，一些函数可以对分组数据进行操作，例如我们上述所列的 AVG SUM 都有相关的功能，不过我们并未使用分组，所以默认使用所有的数据进行操作。

我们可以选择上面的一种进行分组，也可以重复多个，或者综合使用。

例一：我们根据字段名 s_id 进行分组并计数：

SQL> SELECT s_id,count(*) FROM sc GROUP BY s_id;

      S_ID   COUNT(*)
---------- ----------
      1001          3
      1002          3
      1003          1

例二：根据 s_id 进行分组后查询学生的总成绩，使用 SUM 函数：

SQL> SELECT s_id,sum(grade) FROM sc GROUP BY s_id;

      S_ID SUM(GRADE)
---------- ----------
      1003         75
      1001        186
      1002        260

另外我们可以使用多个列进行分组。下面我们使用 s_id 以及 grade 进行分组。

SQL> SELECT s_id,grade, sum(grade) FROM sc GROUP BY s_id,grade;

      S_ID      GRADE SUM(GRADE)
---------- ---------- ----------
      1002        100        100
      1003         75         75
      1001         70         70
      1001         96         96
      1002         80        160
      1001         20         20

已选择 6 行。

HAVING

除了可以对数据进行分组之外，我们还可以使用 HAVING 对分组数据进行过滤。

例如：从选课表中筛选出选课总成绩大于 100 分的学生。

SQL> SELECT s_id, sum(grade) FROM sc GROUP BY s_id HAVING sum(grade)>100;

      S_ID SUM(GRADE)
---------- ----------
      1001        186
      1002        260

ORDER BY

ORDER BY 用于对数据进行排序。

下面我们举一个综合示例：将选课表 sc 的数据根据 s_id 进行分组，并计算每组总成绩，然后筛选出 总成绩>100 的分组，输出结果根据总成绩进行降序排列。

SQL> SELECT s_id,sum(grade) AS sum_grade FROM sc GROUP BY s_id HAVING sum(grade)>100 ORDER BY sum(grade) DESC;

      S_ID  SUM_GRADE
---------- ----------
      1002        260
      1001        186

AS 是命别名的意思，可以省略。

DESC 代表降序排列。省略的话则是升序排列。

限制返回的行数

ROWNUM 用来限制查询返回的行数，这是一个伪列，给结果集的每一行编了一个顺序号。和 mysql 中的 limit 作用类似。

例：查询 student 表中的前两行。

SQL> SELECT * FROM student where rownum<3;

      S_ID S_NAME               S_SEX                     S_AGE
---------- -------------------- -------------------- ----------
      1001 shiyanlou1001        man                          10
      1002 shiyanlou1002        woman                        20

注意：并不能使用使用类似如下两种语句：

select * from student where rownum>2;
select * from student where rownum>2 and rownum<5;

子查询

子查询又被称为嵌套查询，如下示例：

我们要查询选修了课程的课程号 c_id 为 1 的学生的年龄：

1. 首先我们需要从选课表sc 中查询，选修课程号 c_id 为 1 的学生的学号：

SQL> SELECT s_id FROM sc WHERE c_id=1;

      S_ID
----------
      1001
      1002

2. 接着我们可以使用获得的学生号去查询年龄字段，从而得到最终的结果:

SQL> SELECT s_id,s_age FROM student WHERE s_id IN (1001,1002);

      S_ID      S_AGE
---------- ----------
      1001         10
      1002         20

上面的查询过程分为两步，而使用子查询我们只需要一步，如下：

SQL> SELECT s_id,s_age FROM student WHERE s_id IN (SELECT s_id FROM sc WHERE c_id=1);

      S_ID      S_AGE
---------- ----------
      1001         10
      1002         20

即将第一步的查询嵌入第二步的操作中，并且将第一步查询的结果用于第二步查询的判断条件中。

类似的操作还有很多，下面给出一个使用子查询的例子，大家可以分析其代表的含义，并且考虑有没有更简单的实现方式：

SELECT  * FROM student WHERE s_id IN (SELECT s_id FROM sc WHERE c_id=(SELECT c_id FROM course WHERE c_time=(SELECT max(c_time) FROM course)));

表的连接

表的连接主要用于多表查询，我们先来看将所有示例表存储在一张表中会是什么样子。

学号	课程号	学生姓名	学生年龄	学生性别	课程名	课时	成绩
1001	3	shiyanlou001	10	man	c	10	70
1001	1	shiyanlou001	10	man	java	13	20
1001	2	shiyanlou001	4	man	spark	15	90
...	...	...	...	...	...	...

大致如上所示，这里我只给出了简单的几条数据，对比将选课的信息，划分为三张表进行存储，我们不用存储更多重复的信息，明显后者要高效的多。

表的连接基于关系表，可以用来关联多个表。

我们可以使用语句实现这个三表关联的操作：

SQL> SELECT sc.s_id,sc.c_id,s_name,c_name,grade,s_age,s_sex,c_time FROM student,course,sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;

执行过后的显示可能会有点乱，可以使用如下命令先调整显示格式，再执行

SQL> col s_name for a20;
SQL> col c_name for a15;
SQL> set linesize 500;

让我们简化一些，只从每张表中列出其它表中关键的信息，如下所示，我们可以得到学生比较直观的选课信息：

SQL> SELECT sc.s_id, sc.c_id, s_name, c_name, grade FROM student, course, sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;

      S_ID       C_ID S_NAME          C_NAME          GRADE
---------- ---------- --------------- ---------- ----------
      1001          3 shiyanlou1001   c                  70
      1001          1 shiyanlou1001   java               20
      1001          4 shiyanlou1001   spark              96
      1002          1 shiyanlou1002   java              100
      1002          2 shiyanlou1002   python             80
      1002          4 shiyanlou1002   spark              80
      1003          3 shiyanlou1003   c                  75

已选择 7 行。

笛卡尔积连接

笛卡儿积连接又叫交叉连接 ，是多个表之间无条件的连接，它所查询出来的结果数量是每个表的记录数量的乘积，所以查询结果非常之大，在实际中要避免笛卡尔积连接。

下面对 student 表和 course 表进行笛卡尔积连接：

SQL> select * from student,sc;
或者
SQL> select * from student cross join sc;
-- 查询结果数量
SQL> select count(*) from student,sc;

从输出结果可以看到一共有 35 条记录，是两表记录数量的乘积。

我们可以对其指定连接条件来避免此种情况：

SQL> select * from student,sc where student.s_id=sc.s_id;

注意：积依然存在，只是不显示了。

内连接

内连接（有时称为简单连接）是两个或多个表的连接，它们只返回满足连接条件的那些行。使用 INNER JOIN .... ON 。

例：将 sc 表和 student 表内连接：

SQL> SELECT sc.s_id, sc.c_id, s.s_name, sc.grade FROM sc INNER JOIN student s ON s.s_id=sc.s_id;

      S_ID       C_ID S_NAME               GRADE
---------- ---------- --------------- ----------
      1001          3 shiyanlou1001           70
      1001          1 shiyanlou1001           20
      1002          1 shiyanlou1002          100
      1001          4 shiyanlou1001           96
      1002          2 shiyanlou1002           80
      1003          3 shiyanlou1003           75
      1002          4 shiyanlou1002           80

已选择 7 行。

inner 可省略。

也可以使用 using 来连接：

SQL> SELECT s_id, sc.c_id, s.s_name, sc.grade FROM sc JOIN student s using(s_id);

外连接

外连接扩展了内连接的结果，将某个连接表中不符合连接条件的记录加入结果集中。外连接分为左外连接、右外连接、全外连接三种。

左外连接

使用 LEFT JOIN 。会返回 LEFT JOIN 左边表查询的所有行，如果 JOIN 右边的表没有相匹配的行，会返回空。

SQL> SELECT student.s_id,s_name,c_id,grade FROM student LEFT JOIN sc ON student.s_id=sc.s_id;

      S_ID S_NAME                C_ID      GRADE
---------- --------------- ---------- ----------
      1001 shiyanlou1001            3         70
      1001 shiyanlou1001            1         20
      1002 shiyanlou1002            1        100
      1001 shiyanlou1001            4         96
      1002 shiyanlou1002            2         80
      1003 shiyanlou1003            3         75
      1002 shiyanlou1002            4         80
      1004 shiyanlou1004
      1005 shiyanlou1005

已选择 9 行。

右外连接

使用 RIGHT JOIN 。会返回 RIGHT JOIN 右边表查询的所有行，如果 JOIN 左边的表没有相匹配的行，会返回空。

SQL> SELECT student.s_id,s_name,c_id,grade FROM student RIGHT JOIN sc ON student.s_id=sc.s_id;

      S_ID S_NAME                C_ID      GRADE
---------- --------------- ---------- ----------
      1001 shiyanlou1001            3         70
      1001 shiyanlou1001            1         20
      1001 shiyanlou1001            4         96
      1002 shiyanlou1002            1        100
      1002 shiyanlou1002            2         80
      1002 shiyanlou1002            4         80
      1003 shiyanlou1003            3         75

已选择 7 行。

全外连接

使用 FULL JOIN ，会返回两表所有行，如果不满足连接条件，会返回空值。

SQL> SELECT student.s_id,s_name,c_id,grade FROM student FULL JOIN sc ON student.s_id=sc.s_id;

      S_ID S_NAME                C_ID      GRADE
---------- --------------- ---------- ----------
      1001 shiyanlou1001            3         70
      1001 shiyanlou1001            1         20
      1002 shiyanlou1002            1        100
      1001 shiyanlou1001            4         96
      1002 shiyanlou1002            2         80
      1003 shiyanlou1003            3         75
      1002 shiyanlou1002            4         80
      1004 shiyanlou1004
      1005 shiyanlou1005

已选择 9 行。

自然连接

自然连接会自动根据两个表中相同数据类型，相同名称的列进行连接。使用 NATURAL JOIN 。

SQL> SELECT * FROM course NATURAL JOIN sc;

      C_ID C_NAME         C_TIME       S_ID      GRADE
---------- ---------- ---------- ---------- ----------
         3 c                  10       1001         70
         1 java               13       1001         20
         1 java               13       1002        100
         4 spark              15       1001         96
         2 python             12       1002         80
         3 c                  10       1003         75
         4 spark              15       1002         80

已选择 7 行。

和下面的内连接语句输出结果一样：

SQL> select * from course inner join sc on course.c_id=sc.c_id;

向了解更多有关表连接的内容可以参考 表的连接

视图

在上面的内容中，我们通过使用联结来获取相关的信息。如果我们需要经常使用上述查询的内容，可以通过定义视图来实现。

视图（View）是从一个或多个表（这里的表指基本表和视图）导出的表。为了区分视图和表，所以表有时又被称为“基本表”。

对于视图来说，数据库中只保存有视图的定义，而通过视图获得的数据，都来自与它相关的基本表，视图本身是没有数据的。因此，如果我们对视图的数据进行操作，其实也就是对基本表的数据进行操作，而这种操作也是有一定的限制。

创建视图

创建视图使用 CREATE VIEW 。例如我们创建一个包含三张表内容的视图 all_info 。

SQL> CREATE VIEW all_info AS SELECT sc.s_id,sc.c_id,s_name,c_name,grade,s_age,s_sex,c_time FROM student,course,sc WHERE student.s_id=sc.s_id AND course.c_id=sc.c_id;

查看 all_info 视图结构以及内容：

SQL> desc all_info;
SQL> select * from all_info;

创建好了过后我们可以在数据字典 user_views 看到它：

SQL> select view_name from user_views where view_name='ALL_INFO';

VIEW_NAME
----------------------
ALL_INFO

想了解更多有关创建视图的内容可参考 创建视图

创建好了这个视图过后，我们可以利用在表中查询数据的操作来对视图内容进行查询。例如查询视图中成绩大于 80 的学生：

SQL> select * from all_info where grade>80;

删除视图

删除视图使用 DROP VIEW 。如下所示，删除视图 all_info 。

SQL> drop view all_info;

总结

在本节内容中，我们简单介绍了 MySQL 数据库的安装，服务的启动，以及怎么连接到数据库和如何修改配置。

[Yehnn]

PL/SQL

实验介绍

实验内容

本节实验主要是对 PL/SQL 的内容进行学习，主要会涉及到对变量、运算符、数据类型、流程控制和异常处理相关的知识点。

实验知识点

• PL/SQL 简介

• 变量的声明与使用

• 运算符

• 数据类型

• 流程控制

• 异常处理

PL/SQL 简介

PL/SQL 是 Oracle 对 SQL 的过程化语言扩展，是一种便携式，高性能的事务处理语言。它有变量和流程控制等概念，将 SQL 的数据操作能力与过程语言的处理能力结合起来。（更多有关 PL/SQL 介绍可参考官方文档 ）

语法结构

PL/SQL 的结构通常如下：

DECLARE      --声明部分。例如定义常量，变量，引用的函数或过程等。
BEGIN        --执行部分。包含变量赋值，过程控制等。
EXCEPTION    --处理异常。包含错误处理语句。
END;         --结束部分。
/            /*添加这个斜杠来执行 PL/SQL 语句块。*/

上面 -- 后面和 /* */ 包围的内容都是注释。这是 PL/SQL 的两种注释方式。

BEGIN ，END 就类似于其他编程语言的 {...} 。

预热

我们先来做几个简单实践大致了解 PL/SQL。

例一：输出 Hello World 。为了方便，后文所述内容除使用 $ 特别标识外，均在 SQL 命令行输入。

SET SERVEROUTPUT ON;   --默认输出显示是关闭的，需要首先打开才会显示
BEGIN
  DBMS_OUTPUT.put_line('Hello World');
END;
/

输出结果如下：

PL/SQL 过程已成功完成。
Hello World

例二：声明一个变量并使用。该语句实现输出 my name is : syl 。

DECLARE
  v_name varchar2(20); --定义变量
BEGIN
  v_name := 'syl';  --为变量赋值
  DBMS_OUTPUT.put_line('my name is : ' || v_name);
END;
/

注意：PL/SQL 中字符串连接用 || 。

例三：从键盘输入学生编号（比如输入 1001 ），查询出对应的学生姓名。输出 student's name is : shiyanlou1001 。

DECLARE
  v_sid NUMBER;            --接收学生编号
  v_sname VARCHAR2(20);    --接收学生姓名
BEGIN
  v_sid := &studentid;     --键盘输入数据
  SELECT s_name INTO v_sname FROM student WHERE s_id=v_sid; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
END;
/

经过上面的实践，相信大家已经对 PL/SQL 有了一个大概的了解。接下来我们正式进入详细的 PL/SQL 学习。

变量的声明与使用

变量的声明

声明为指定数据类型的值分配存储空间，并命名存储位置以便引用它。

必须先声明对象，然后才能引用它们。声明可以出现在任何块，子程序或包的声明部分。

（此段引用自 PLSQL 声明-官方文档 ）

声明的语法：

DECLARE
    变量名称 [CONSTANT] 类型 [NOT NULL] [:=value];

• 变量名称必须遵守如下规定：
  • 由字母，数字，_，$ ，# 组成。
  • 以字母开头，不能是 Oracle 中的关键字
  • 变量的长度最多为 30 个字符。
• CONSTANT 是声明常量。
• :=value 是设置默认值。

例如：如下的变量名是不符合规定的。

me&you
2user
on/off
student id
select

例一：声明一个名叫 v_syl 的变量。

DECLARE
    v_syl VARCHAR2(20);
BEGIN
    NULL;
END;
/

例二：声明有默认值的变量。下列程序实现计算 v_a 和 v_b 的和。

DECLARE
  v_a NUMBER :=1;
  v_b NUMBER; 
BEGIN
  v_B := 2;
  DBMS_OUTPUT.put_line(v_A+v_B);
END;
/

可以发现不区分大小写。

例三：声明一个不为空的变量。

DECLARE
  v_sid NUMBER NOT NULL := 1;
BEGIN
  NULL;
END;
/

注意：声明不为空的话，一定要设置默认值。不然会报错 PLS-00218: 声明为 NOT NULL 的变量必须有初始化赋值 。

除了可以声明变量，还可以声明常量。常量的初始值是其永久值。如下示例声明了两个常量。

DECLARE
  v_num CONSTANT NUMBER := 1;
  v_bool CONSTANT BOOLEAN := FALSE;
BEGIN
  NULL;
END;
/

使用 %TYPE 属性声明

有时候我们想要声明与之前声明的变量或指定数据表中的某列相同数据类型的数据项，但是我们并不知道之前声明的变量的类型，这个时候就可以使用 %TYPE 。引用项目会继承如下内容：

• 数据类型和大小。
• 约束。

注意：

• 引用项目不会继承初始值。
• 如果被引用项目的声明发生变化，则引用项目的声明会相应地改变。

语法：

引用项目名称 被引用项目名称%TYPE;

例：我们改写之前根据学生编号查询学生姓名的代码。让变量 v_sid 和 v_sname 分别引用表 student 的 s_id 和 s_name 的数据类型。

DECLARE
  v_sid student.s_id%TYPE;            --接收学生编号
  v_sname student.s_name%TYPE;    --接收学生姓名
BEGIN
  v_sid := &studentid;     --键盘输入数据
  SELECT s_name INTO v_sname FROM student WHERE s_id=v_sid; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
END;
/

输入学生编号 1001 依然可以查询出对应的姓名。

使用 %ROWTYPE 属性声明

使用 ％ROWTYPE 属性可以声明表示数据库表或视图的全部或部分行的记录。记录字段不会继承相应列的约束和初始值。

例：改写上面根据学生编号查询学生姓名的代码。

DECLARE
  v_row student%ROWTYPE;
BEGIN
  SELECT * INTO v_row FROM student WHERE s_id=1001;
  DBMS_OUTPUT.put_line('1001 student is : ' || v_row.s_name || v_row.s_sex || v_row.s_age);
END;
/

直接将符合条件的记录都赋给 v_row ，然后使用 v_row.字段名 的方式，就可获得想要的值，很方便。

输出结果如下：

1001 student is : shiyanlou1001man10

全局变量和内部变量

全局变量可以在内部语句块中访问，内部变量在语句块外面访问不到。如下示例：

DECLARE
  v_a VARCHAR2(50) := 'this is global';    --全局变量
  v_b VARCHAR2(50) := 'this is second global';
BEGIN
  DECLARE v_a VARCHAR2(50) := 'this is inner';   --内部变量
  BEGIN
    DBMS_OUTPUT.put_line(v_a);
    DBMS_OUTPUT.put_line(v_b);
  END;
  DBMS_OUTPUT.put_line(v_a);
END;
/

上面程序包含在第一个 BEGIN END 中的语句块就是内部程序块。

运行结果：

this is inner
this is second global
this is global

运算符

PL/SQL 中的运算符和 SQL 中的运算符是通用的。

• 赋值运算符 :=
• 连接运算符 ||
• 算术运算符 - 减，+ 加，* 乘，/ 除
• 关系运算符
• 逻辑运算符

赋值，连接，算术运算符很简单，我们都在前面使用过了。下面直接讲关系运算符和逻辑运算符。

关系运算符

下面做一个简单分类。

分类	运算符	说明
简单关系运算符	>， <， >= ，<=， =， !=， <>	大于，小于，大于等于，小于等于，等于。!= 和 <> 都表示不等于。
判断空值	IS NULL ，IS NOT NULL	判断某列内容是否是 NULL
范围查询	BETWEEN 最小值 AND 最大值	在指定的最小值和最大值的范围内查找
范围查询	IN	指定查询的范围
模糊查询	LIKE	模糊查询

如下实例综合使用了某些关系运算符。

DECLARE
  v_a NUMBER :=1;
  v_b NUMBER :=2;
  v_c NUMBER;
  v_d VARCHAR2(20);
BEGIN
  IF v_a<v_b THEN     --判断 v_a 是否小于 v_b
    DBMS_OUTPUT.put_line(v_a || ' < ' || v_b);
  END IF;
  IF v_c IS NULL THEN   --判断 v_c 是否为空
    DBMS_OUTPUT.put_line('v_c is null');
  END IF;
  IF v_b BETWEEN 1 AND 3 THEN    --判断 v_b 是否在 1 到 3 之间
    DBMS_OUTPUT.put_line('v_b is between 1 and 3');
  END IF;
  IF v_b IN(1,2,3) THEN      --判断 v_b 是否在 （1，2，3）里
    DBMS_OUTPUT.put_line('v_b is : ' || v_b);
  END IF;
  IF v_d LIKE 'shi%' THEN     --判断 v_d 是否是 shi 开头
    DBMS_OUTPUT.put_line(v_d);
  END IF;
END;
/

输出结果：

1 < 2
v_c is null
v_b is between 1 and 3
v_b is : 2

逻辑运算符

逻辑运算符 AND ，OR ，NOT 。下面是逻辑真值表。

x	y	x AND y	x OR y	NOT x
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

（此表来自于 逻辑运算符-官方文档 ）

如下实例综合使用了逻辑运算符：

DECLARE
  v_b1 BOOLEAN := TRUE;
  v_b2 BOOLEAN := FALSE;
  v_b3 BOOLEAN := TRUE;
BEGIN
  IF v_b1 AND v_b3 THEN
    DBMS_OUTPUT.put_line('v_b1 AND v_b3 is true');
  END IF;
  IF NOT v_b2 THEN
    DBMS_OUTPUT.put_line('v_2 is false');
  END IF;
  IF v_b1 OR v_b2 THEN
    DBMS_OUTPUT.put_line('v_b1 OR v_b2 is true');
  END IF;
END;
/

输出结果：

v_b1 AND v_b3 is true
v_2 is false
v_b1 OR v_b2 is true

数据类型

每个 PL/SQL 常数，变量，参数，和函数的返回值都具有数据类型，以确定其存储格式以及有效的值和操作。

Oracle 中提供的数据类型有四种：

• 标量类型（scala data type）：用来保存单个值。例如：数字，字符串，布尔值，日期等。
• 复合类型（coposite data type）：保存多种类型数值。例如：索引表，可变数组，嵌套表等。
• 引用类型（reference data type）：用来指向另一个不同的对象。
• LOB 类型：大数据类型，主要用来处理二进制数据，最多可以存储 4G 的信息。

上面只是介绍了数据类型的种类，而我们常用的是标量类型。主要学习标量类型。

注意：PL/SQL 数据类型包括 SQL 数据类型。丹它们的最大尺寸有所不同。可参见 PL/SQL 数据类型-官方文档 。

数值类型

• NUMBER
• BINARY_INTEGER ，PLS_INTEGER
• BINARY_DOUBLE ，BINARY_FLOAT

BINARY_INTEGER ，PLS_INTEGER

BINARY_INTEGER 和 PLS_INTEGER 对比 NUMBER ：

• 需要的存储空间更少。
• 性能更高：因为 NUMBER 以十进制存储，计算的时候会先转换成二进制。而 BINARY_INTEGER 和 PLS_INTEGER 以二进制的补码存储。

BINARY_INTEGER 和 PLS_INTEGER 是相同的。当操作的数值超出定义的范围会抛出异常。如下示例：

DECLARE
  p1 PLS_INTEGER := 2147483647;
  p2 PLS_INTEGER := 1;
  n NUMBER;
BEGIN
  n := p1 + p2;
END;
/

两数想加的结果为 2147483648 超出了 PLS_INTEGER 的范围 -2147483648~2147483647 的整数 。即使我们把结果给了 NUMBER 类型，但仍然会抛出数字溢出的异常。

将 p2 声明为 INTEGER 类型可以正确计算出结果：

DECLARE
  p1 PLS_INTEGER := 2147483647;
  p2 INTEGER := 1;
  n NUMBER;
BEGIN
  n := p1 + p2;
END;
/

BINARY_FLOAT，BINARY_DOUBLE

同样 BINARY_FLOAT，BINARY_DOUBLE 的计算性能比 NUMBER 更高。前者是单精度类型，后者是双精度类型。主要用于科学计算。

下面示例输出这两种类型的值。

DECLARE
  v_float BINARY_FLOAT := 6666.66F;
  v_double BINARY_DOUBLE :=6666.66F;
BEGIN
  DBMS_OUTPUT.put_line(v_float);
  DBMS_OUTPUT.put_line(v_double);
END;
/

输出结果如下：

6.66666016E+003
6.66666015625E+003

它是用科学记数法的方式存储的。

Oracle 中预定义了一些 BINARY_FLOAT 和 BINARY_DOUBLE 常量，比如无穷大，最小绝对数等等。可参见 表3-2 预定义常量-官方文档 。

例如输出 BINARY_FLOAT 最大值，最小值：

BEGIN
  DBMS_OUTPUT.put_line('BINARY_FLOAT_MIN_NORMAL = ' || BINARY_FLOAT_MIN_NORMAL);
  DBMS_OUTPUT.put_line('BINARY_FLOAT_MAX_NORMAL = ' || BINARY_FLOAT_MAX_NORMAL);
END;
/

输出结果：

BINARY_FLOAT_MIN_NORMAL = 1.17549435E-038
BINARY_FLOAT_MAX_NORMAL = 3.40282347E+038

字符型

• CHAR 与 VARCHAR2
• NCHAR 与 NVARCHAR2
• LONG 与 LONG RAW
• ROWID 与 UROWID

CHAR 与 VARCHAR2

• CHAR 以定长方式保存字符串。若赋值长度不足其定义长度，会以空格补充。
• VARCHAR2 变长字符串。若不足定义长度，不会补充内容。

如下实例说明了上述区别。

DECLARE
  v_char CHAR(5);
  v_varchar2 VARCHAR2(5);
BEGIN
  v_char := 'SYL';
  v_varchar2 := 'SYL';
  DBMS_OUTPUT.put_line(v_char || ' length: ' || LENGTH(v_char));
  DBMS_OUTPUT.put_line(v_varchar2 || ' length: ' || LENGTH(v_varchar2));
END;
/

输出结果：

SYL   length: 5
SYL length: 3

NCHAR 与 NVARCHAR2

• 两者的区别与 CHAR 和 VARCHAR2 的区别相同。
• 保存的数据为 UNICODE 编码。

也就是说中文是占一位的。比如我们定义的是 CHAR(3) ，给它赋值为实验楼会报错，因为一个中文占两位。而定义为 NCHAR(3) ，则不会报错。

LONG 与 LONG RAW

• LONG 保存变长字符串。
• LONG RAW 保存变长二进制数据。

用如下实例说明上述区别：

DECLARE
  v_long LONG;
  v_longraw LONG RAW;
BEGIN
  v_long := 'SYL';
  v_longraw := UTL_RAW.cast_to_raw('SYL');    --转换为二进制
  DBMS_OUTPUT.put_line(v_long || ' length: ' || LENGTH(v_long)); 
  DBMS_OUTPUT.put_line(v_longraw || ' length: ' || LENGTH(v_longraw));   
  DBMS_OUTPUT.put_line(UTL_RAW.cast_to_varchar2(v_longraw) || ' length: ' || LENGTH(v_longraw));  --转换为 varchar2
END;
/

UTL_RAW.cast_to_raw 和 UTL_RAW.cast_to_varchar2 是字符转换。

输出结果：

SYL length: 3
53594C length: 6
SYL length: 6

ROWID 与 UROWID

• ROWID 表示一条数据的物理行地址。
• UROWID 除了表示物理行地址，还增加了逻辑行地址。

如下实例输出学生编号为 1001 的那条记录的 ROWID 和 UROWID 。

DECLARE
  v_rowid ROWID;
  v_urowid UROWID;
BEGIN
  SELECT ROWID INTO v_rowid FROM student WHERE s_id=1001;
  SELECT ROWID INTO v_urowid FROM student WHERE s_id=1001;
  DBMS_OUTPUT.put_line('v_rowid = '||v_rowid || '  v_urowid = '||v_urowid);
END;
/

输出结果如下：

v_rowid = AAAR9rAABAAAZAJAAA  v_urowid = AAAR9rAABAAAZAJAAA

日期型

• DATE
• TIMESTAMP
• INTERVAL

DATE

DATE 类型用于存储日期和时间。

范围：公元前 4712 年 1 月 1 日到公元后 9999 年 12 月 31 日

占据空间：7 字节

形式类似于 2017-1-1 06:06:06 。

可以设置其日期和时间的表示格式，由 NLS_DATE_FORMAT 和 NLS_DATE_LANGUAGE 这两个初始参数控制。

如下实例输出当前系统时间。

DECLARE
  v_d1 DATE := SYSDATE;
  v_d2 DATE := SYSTIMESTAMP;
BEGIN
  DBMS_OUTPUT.put_line(TO_CHAR(v_d1,'yyyy-mm-dd hh12:mi:ss'));
  DBMS_OUTPUT.put_line(TO_CHAR(v_d1,'yyyy-mm-dd hh24:mi:ss'));
  DBMS_OUTPUT.put_line(SYSDATE);
  DBMS_OUTPUT.put_line(SYSTIMESTAMP);
END;
/

输出结果：

2018-01-27 05:17:13
2018-01-27 17:17:13
27-1月 -18
27-1月 -18 05.17.13.447000000 下午 +08:00

如下实例设置初始参数来设置日期和时间。

1. 查看当前会话的 NLS 的初始参数设置：

select * from nls_session_parameters;

1. 查看 NLS_DATE_FORMAT 和 NLS_DATE_LANGUAGE初始参数设置：

SHOW PARAMETER NLS_DATE_LANGUAGE;
SHOW PARAMETER NLS_DATE_FORMAT;

输出结果：

NAME              TYPE   VALUE              
----------------- ------ ------------------ 
nls_date_language string SIMPLIFIED CHINESE 
NAME            TYPE   VALUE     
--------------- ------ --------- 
nls_date_format string DD-MON-RR 

1. 设置初始参数

ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
SELECT SYSDATE FROM DUAL;

输出结果：

2018-01-27 17:46:12

想要设置数据库系统的日期，时间格式，用 ALTER SYSTEM 。

TIMESTAMP

TIMESTMP 表示的时间更精确。用 TIMESTMP 声明的变量要用 SYSTIMESTMP 赋值。

TIMESTAMP 有两个扩展的子类型：

• TIME ZONE ：包含与格林威治时间的偏移量。
• LOCAL TIME ZONE：使用当前数据库的时区。

例：将两种类型的值分别输出，以观察它们的区别。

DECLARE
  v_timezone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
  v_localtime TIMESTAMP WITH LOCAL TIME ZONE := SYSTIMESTAMP;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_timezone);
  DBMS_OUTPUT.PUT_LINE(v_localtime);
END;
/

输出结果：

01-2月 -18 05.57.06.444000 下午 +08:00
01-2月 -18 05.57.06.444000 下午

INTERVAL

用来表示两个时间戳之间的时间间隔。它有两种子类型：

• INTERVAL YEAR[(年的精度)] TO MONTHS ：默认年的精度为 2
• INTERVAL DAY[(天的精度)] TO SECOND[(秒的精度)] ：默认天的精度为 2，秒的精度为 6

例一：计算五年零一个月后的日期和时间。

DECLARE
  v_interval INTERVAL YEAR(3) TO MONTH := INTERVAL '5-1' YEAR TO MONTH;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_interval);   --输出时间间隔
  DBMS_OUTPUT.PUT_LINE(v_interval+SYSDATE);  --输出计算后的日期和时间
END;
/

输出结果：

+005-01
2023-03-01 18:22:38

例二：计算 5 天 11 小时 11 分钟 11.1111 秒后的日期和时间。

DECLARE
  v_interval2 INTERVAL DAY(3) TO SECOND := INTERVAL '5 11:11:11.1111' DAY TO SECOND;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_interval2);
  DBMS_OUTPUT.PUT_LINE(v_interval2+SYSDATE);
END;
/

输出结果：

+005 11:11:11.111100
2018-02-07 05:33:49

布尔型

用 BOOLEAN 表示， 主要用于逻辑判断，可以存储 TRUE ，FALSE，NULL。之前已经使用过了。不做过多介绍。

子类型

子类型是基于标量类型的，相当于是一个类型的别名，使之简单化。子类型的创建语法如下：

SUBTYPE 子类型名称 IS 父类型名称[(约束)] [NOT NULL]

例：创建一个子类型并使用。

DECLARE
  SUBTYPE name_subtype IS VARCHAR2(20) NOT NULL;  --定义子类型
  v_name name_subtype := 'syl';  --使用子类型
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

输出结果：

syl

流程控制

条件语句

IF 语句

如果满足某种条件，就执行某种操作。IF 语句有如下几种形式

• IF ...THEN...
• IF ... THEN ... ELSE ...
• IF ... THEN ... ELSIF ...

例：实现判断编号为 1003 这个学生的平均成绩是否大于 60，如果大于 60 则输出 pass ，如果大于 30 小于 60 则输出 lost ，如果小于 30 则输出 fail 。

DECLARE
  v_grade sc.grade%TYPE;
BEGIN
  SELECT AVG(grade) INTO v_grade FROM student s JOIN sc USING(s_id) GROUP BY s_id HAVING s_id=1003;
  IF v_grade >= 60 THEN
    DBMS_OUTPUT.put_line('pass '||v_grade);
  ELSIF v_grade>=30 AND v_grade<60 THEN
    DBMS_OUTPUT.put_line('loss '||v_grade);
  ELSE
    DBMS_OUTPUT.put_line('fail '||v_grade);
  END IF;
END;
/

输出结果：

pass 75

CASE 语句

多条件判断。语法如下：

CASE selector
    WHEN selector_value THEN statements_1;
    ...
END CASE

例：判断编号为 1003 的学生性别，如果为 man 就输出学生姓名和性别。

DECLARE
  v_name student.s_name%TYPE;
  v_sex student.s_sex%TYPE;
BEGIN
  SELECT s_name,s_sex INTO v_name,v_sex FROM student WHERE s_id=1003;
  CASE v_sex
    WHEN 'man' THEN 
      DBMS_OUTPUT.put_line(v_name|| ' is man');
    WHEN 'woman' THEN
      DBMS_OUTPUT.put_line(v_name ||'is woman');
    ELSE
      DBMS_OUTPUT.put_line('dont know');
    END CASE;
END;
/

输出结果：

shiyanlou1003 is man

循环语句

WHILE 循环

语法：

WHILE （循环结束条件）LOOP
    循环执行的语句块;
END LOOP;

例：输出1，2，5 。

DECLARE
  v_i NUMBER := 1;
BEGIN
  WHILE(v_i <= 5) LOOP   --当 v_i <=3 时
    DBMS_OUTPUT.put_line(v_i);
    v_i := v_i+1;    --v_i 加1
  END LOOP;
END;
/

还可以使用如下语句实现相同效果。

DECLARE
  v_i NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.put_line(v_i);
    EXIT WHEN v_i>=5;   --当 v_i>=3 时退出循环
    v_i := v_i+1;
  END LOOP;
END;
/

FOR 循环

语法：

FOR 循环索引 IN [REVERSE] 循环区域下限 循环区域上限 LOOP
    循环执行的语句块;
END LOOP;

例：用 FOR 实现循环输出 1 到 5 。

DECLARE
  v_i NUMBER :=1;
BEGIN
  FOR v_i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line(v_i);
  END LOOP;
END;
/

循环控制

EXIT

EXIT 会直接退出循环。

DECLARE
  v_i NUMBER :=1;
BEGIN
  FOR v_i IN 1 .. 5 LOOP
    IF v_i = 3 THEN
      EXIT;
    END IF;
    DBMS_OUTPUT.put_line(v_i);
  END LOOP;
END;
/

输出结果：

1
2

CONTINUE

CONTINUE 退出当前语句块。

DECLARE
  v_i NUMBER :=1;
BEGIN
  FOR v_i IN 1 .. 5 LOOP
    IF v_i = 3 THEN
      CONTINUE;
    END IF;
    DBMS_OUTPUT.put_line(v_i);
  END LOOP;
END;
/

输出结果：

1
2
4
5

GOTO

无条件跳转到某个地方。不建议使用。

DECLARE
  v_i NUMBER :=1;
BEGIN
  FOR v_i IN 1 .. 5 LOOP
    IF v_i = 3 THEN
      GOTO flag;      --GOTO 跳转
    END IF;
    DBMS_OUTPUT.put_line(v_i);
  END LOOP;
  <<flag>>          --定义了要跳转的地方。
  DBMS_OUTPUT.put_line('goto');
END;
/

输出结果：

1
2
goto

异常处理

当出现异常时，程序会中断执行，如果我们写了异常处理，程序将会捕获异常，在捕获到异常后抛出异常，程序继续执行。

语法：

EXCEPTION
    WHEN 异常类型 | 用户自定义异常 | 异常代码 | OTHERS THEN
        异常处理语句;

使用预定义的异常

例一：捕获除数为 0 的异常。

DECLARE
  v_a NUMBER := 1;
  v_b NUMBER := 0;
BEGIN
  v_a := v_a/v_b;
EXCEPTION
  WHEN ZERO_DIVIDE THEN   --捕获除数为 0 的异常
    DBMS_OUTPUT.put_line('zero divide');
    DBMS_OUTPUT.put_line(SQLCODE);  --输出异常编号
    DBMS_OUTPUT.put_line(SQLERRM);  --输出异常详情
END;
/

上面的 ZERO_DIVIDE 是捕获除数为 0 的异常。这是 Oracle 中预定义的异常名，可以直接使用，还有很多的预定义异常，可参考 表11-3 预定义的异常-官方文档 。

输出结果：

zero divide
-1476
ORA-01476: 除数为 0

例二：从键盘输入学生编号，查询出对应的学生姓名。当我们输入的学生编号在表中不存在时抛出异常。

DECLARE
  v_sid NUMBER;            --接收学生编号
  v_sname VARCHAR2(20);    --接收学生姓名
BEGIN
  v_sid := &studentid;     --键盘输入数据
  SELECT s_name INTO v_sname FROM student WHERE s_id=v_sid; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
EXCEPTION
  WHEN no_data_found THEN    --捕获不存在的异常
    DBMS_OUTPUT.put_line('not found the student');
END;
/

当输入 1006 的时候会抛出此异常。

例三：捕获返回行数过多的异常。比如我们在这里查询年龄大于 10 岁的学生，会返回多个记录。

DECLARE
  v_sname VARCHAR2(20);    --接收学生姓名
BEGIN
  SELECT s_name INTO v_sname FROM student WHERE s_age>10; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN    --抛出返回行数过多的异常
    DBMS_OUTPUT.put_line('too many rows');
END;
/

输出结果：

too many rows

这么多的异常名我们可能很难全部记住，在处理异常的时候可以使用 OTHERS 替代。比如上面的程序我们可以这样写。

DECLARE
  v_sname VARCHAR2(20);    --接收学生姓名
BEGIN
  SELECT s_name INTO v_sname FROM student WHERE s_age>10; --把查询出来的值赋给变量 v_sname
  DBMS_OUTPUT.put_line('student''s name is : ' || v_sname);
EXCEPTION
  WHEN OTHERS THEN     --使用 OTHERS 捕获
    DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
END;
/

上面都是使用的 Oracle 已经定义好的异常名，除此之外，我们仍可以自定义异常 。

自定义异常

声明异常

DECLARE
  v_a NUMBER := 1;
  v_exception EXCEPTION;  --声明异常
BEGIN
  IF v_a = 1 THEN
    RAISE v_exception;  --抛出异常
  END IF;
EXCEPTION 
  WHEN v_exception THEN   --捕获异常
    DBMS_OUTPUT.put_line('exception:is 1');
    DBMS_OUTPUT.put_line(SQLCODE);
    DBMS_OUTPUT.put_line(SQLERRM);
END;
/

上面的异常捕获也可以使用 WHEN OTHERS THEN 。使用 OTHERS 虽然方便，但是会捕获所有的异常一起处理，不能分开处理。

输出结果：

PL/SQL 过程已成功完成。
exception:is 1
1
User-Defined Exception

定义异常编码

上面的 SQLCODE 是 1，我们是可以自定义这个 SQLCODE 的。如下示例自定义 SQLCODE 为 -6666 。（定义的编码可以是已经有的编码）

注意：定义的编码必须要范围在 -20000~-20999 。

DECLARE
  v_a NUMBER := 1;
  v_exception EXCEPTION;  --声明异常
  PRAGMA EXCEPTION_INIT(v_exception,-20666);   --自定义异常代码
BEGIN
  IF v_a = 1 THEN
    RAISE v_exception;  --抛出异常
  END IF;
EXCEPTION 
  WHEN v_exception THEN   --捕获异常
    DBMS_OUTPUT.put_line('exception:is 1');
    DBMS_OUTPUT.put_line(SQLCODE);
    DBMS_OUTPUT.put_line(SQLERRM);
END;
/

输出结果：

exception:is 1
-6666
ORA-06666: 

动态构建异常

直接在程序块中使用 RAISE_APPLICATION_ERROR 抛出对应异常。

DECLARE
  v_a NUMBER := 1;
  v_exception EXCEPTION;  --声明异常
  PRAGMA EXCEPTION_INIT(v_exception,-20666);
BEGIN
  IF v_a = 1 THEN
    RAISE_APPLICATION_ERROR(-20666,'raise application error');  --抛出异常。此编码一定要和定义的编码一样。
  END IF;
EXCEPTION 
  WHEN v_exception THEN   --捕获异常。此名字一定要和声明的异常名一样。
    DBMS_OUTPUT.put_line(SQLCODE);
    DBMS_OUTPUT.put_line(SQLERRM);
END;
/

输出结果：

PL/SQL 过程已成功完成。
-20666
ORA-20666: raise application error

上面的程序实际上可以简化成下面这样，只使用一个 RAISE_APPLICATION_ERROR ，然后用 OTHERS 捕获，省去了声明异常以及设置编号。

DECLARE
  v_a NUMBER := 1;
  --v_exception EXCEPTION;  --声明异常
  --PRAGMA EXCEPTION_INIT(v_exception,-20666);
BEGIN
  IF v_a = 1 THEN
    RAISE_APPLICATION_ERROR(-20666,'raise application error');  --抛出异常
  END IF;
EXCEPTION 
  WHEN OTHERS THEN   --捕获异常
    DBMS_OUTPUT.put_line(SQLCODE);
    DBMS_OUTPUT.put_line(SQLERRM);
END;
/

总结

• PL/SQL 简介
• 变量的声明与使用
• 运算符
• 数据类型
• 流程控制
• 异常处理

[Yehnn]

安全性管理

实验介绍

实验内容

本节实验主要讲解了对 Oracle 的安全性管理，包括了如何进行用户管理、用户授权管理、以及角色、概要文件的相关管理操作。

实验知识点

• 用户管理

• 用户权限管理

• 角色管理

• 概要文件管理

用户管理

我们在之前连接数据库实例的时候，多是使用的 system 这个用户去连接，实际上我们还可以创建其他用户。

创建用户

创建用户的语法参见 CREATE USER-官方文档

在创建用户的时候通常会指定如下信息：

• 用户名
• 验证方式
• 默认表空间
• 临时表空间或临时表空间组
• 当前用户使用表空间的最大值
• 用户配置文件
• 用户状态

例一：如下用最简单的方式创建一个用户名为 syl ，密码为 shiyanlou 的用户，也就是只指定用户名和密码：

SQL> create user syl identified by shiyanlou;

用户已创建。

注意：用户名需要遵守的规定：

• 由字母，数字，_，$ ，# 组成。
• 以字母开头，不能是 Oracle 中的关键字
• 变量的长度最多为 30 个字符。
• 字母区分大小写，但所有字母自动转换为大写。

查看是否创建成功：

SQL> select * from dba_users where lower(username)='syl';

可以看到 syl 用户的默认表空间是 USERS 。使用如下命令可以查看创建时，默认分配的表空间

SQL> select property_name,property_value from database_properties where property_name like '%TABLESPACE%';

例二：创建一个验证方式是外部方式验证的用户 syl2 ，默认表空间设为 SYLTP1。

首先新建一个名叫 syltp1 的表空间：

SQL> create tablespace syltp1 datafile 'syltp1.dbf' size 10M;

然后创建用户：

SQL> create user syl2 identified externally
default tablespace SYLTP1
quota 1m on SYLTP1
TEMPORARY TABLESPACE TMP_SP1
PROFILE default;

代码详细解释：

行数	说明
1	externally 指定验证方式为外部方式，也就是通过操作系统或第三方服务等外部服务进行验证。还有一些其他的验证方式，可参见 CREATE USER-官方文档
2	默认的表空间是 SYLTP1 。这个表空间是之前在表空间学习中创建的。
3	用户在 SYLTP1 表空间中使用的磁盘最大为 1MB 。
4	默认的临时表空间是 TMP_SP1 。这是之前我们在表空间学习中创建的临时表空间。
5	使用的配置文件是 default 。可以使用命令 select * from dba_profiles 查看有哪些配置文件（也叫概要文件）。

查看是否创建成功：

SQL> select * from dba_users where username='SYL2';

修改用户信息

修改用户信息跟创建用户差不多，只是更改为 ALTER USER 。

例一：修改名为 syl 用户的密码。

SQL> alter user syl identified by newsyl;

例二：更改 syl 用户的默认表空间为 SYLTP1 。

SQL> alter user syl default tablespace SYLTP1;

删除用户

使用 DROP USER 即可删除用户，例如删除名为 syl2 的用户：

SQL> drop user syl2 cascade;

cascade 将用户下所有的数据文件也一起删除。

用户权限管理

为了保证数据库的数据安全，必然会控制每个用户的权限。权限分为两种：

• 系统权限：指系统级操作的权限，通常是影响数据字典的操作。
• 对象权限：对数据库中对象的操作，通常是影响数据的操作。

授予以及查询权限

系统权限

系统权限有很多，下表列出了几个常用的系统权限，如果想查看更多的系统权限，可参见 表18-1-授权 。

系统权限	说明
CREATE|RESTRICTED SESSION	允许用户进行连接 | 使用 STARTUP RESTRICT 启动实例后登录。
ALTER DATABASE|SYSTEM	更改数据库 | 允许使用 ALTER SYSTEM 控制参数和内存结构。
CREATE TABLESPACE|TABLE	允许创建表空间 | 允许创建表
CREATE|DROP|SELECT|INSERT|UPDATE|DELETE ANY TABLE	允许对其他用户（除了SYS）的表执行这些 DDL 和 DML 操作。
GRANT ANY OBJECT PRIVILEGE	授予对象所有者被允许授予的任何对象特权。

例一：授予 syl 用户 create session 的系统权限。

--授权
SQL> grant create session to syl;

--查看是否授权成功
SQL> select * from dba_sys_privs where grantee='SYL';

例二：授予用户除 SELECT ANY DICTIONARY ， ALTER DATABASE LINK ， ALTER PUBLIC DATABASE LINK 权限以外的所有系统权限。

SQL> grant all privileges to syl;

例三：让 syl 用户具有授予 create session 的权限。

SQL> grant create session to syl with admin option;

指定 with admin option 让用户具有授予某权限的权限。

对象权限

对象权限也有很多，对象权限是对对象授权，也就是表，视图等。了解全部对象权限可参见表18-2-对象权限 。

例一：授予用户 syl 查询 system 用户的 student 表的权限。

--授权
SQL> grant select on system.student to syl;
--查询
SQL> select * from dba_tab_privs where grantee='SYL';

加上 with grant option 可以使指定用户允许给其他用户对象授权。

使用 grant all on 可以授予用户所有的对象权限。

上面的 system 实际上是模式名，模式名跟用户名是一样的。

撤销权限

撤销系统权限

例：撤销 syl 用户的授予的所有系统权限。

SQL> revoke all privileges from syl;

撤销对象权限

例：撤销 syl 查询 system 用户的 student 表的权限。

SQL> revoke select on system.student from syl;

角色管理

角色不属于用户，它是独立的。角色实际上是一组权限。

当我们要授予很多用户相同的权限，如果对一个一个用户去授权，工作量就会很大，而直接创建一个包含一组权限的角色，给用户赋予这个角色就显得轻松许多。

另一方面，当我们需要改变一个用户权限的时候，使用直接授权的方式，依然很麻烦。而直接更改用户的角色就很容易。就像为一个员工安排职位一样，不同的职位拥有不同的权限，要更改用户的权限，直接改变员工的职位。

创建角色

创建角色的语法参见创建角色-官方文档 。下面我们以之前创建的学生选课的三个表为例。我们肯定不希望任何人都能随意更改几张表的数据，随意创建表等，所以我们创建这样的三种角色。对于学生，我们希望他只能查询。对于管理员，他除了能查询以外还可以对表中数据进行增删改。对于超级管理员，他除了能做管理员能做的事之外，还可以创建删除表。

例一：创建一个能连接实例并且只能对 student 表，sc 表，course 表进行查询的角色 user_sc 。

create role user_sc;
grant create session to user_sc;
grant select on system.student to user_sc;
grant select on system.sc to user_sc;
grant select on system.course to user_sc;

在创建的时候可以如同创建用户一样，给角色设置密码验证等验证方式。

例二：创建一个角色 admin_sc ，它能连接实例并且能对 student 表，sc 表，course 表进行增删改查并且能把 user_sc 角色授权给其他角色 。

create role admin_sc;   --创建角色，角色名为 admin_sc
--将 user_sc 角色的权限授予 admin_sc 并且 admin_sc 角色可以给 user_sc 角色授予权限。
grant user_sc to admin_sc with admin option;
grant delete,insert,update on system.student to admin_sc;
grant delete,insert,update on system.sc to admin_sc;
grant delete,insert,update on system.course to admin_sc;

例三：创建一个超级角色，它可以控制表中所有内容并且能够创建和删除表。

create role super_sc;
grant admin_sc to super_sc with admin option;
grant create any table,drop any table to super_sc;
grant all on system.student to super_sc;
grant all on system.sc to super_sc;
grant all on system.course to super_sc;

角色创建好后可以使用如下命令查看，这里我们查看用户 system （也就是我们现在登录的这个用户）的角色：

SQL> select granted_role,default_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE                   DEFAUL
------------------------------ ------
SUPER_SC                       YES
ADMIN_SC                       YES
USER_SC                        YES
AQ_ADMINISTRATOR_ROLE          YES
DBA                            YES

可以看到我们创建的角色默认授予给了我们创建角色的这个用户。

设置角色

角色创建过后，就可以将角色授予给用户了，并且一个用户可以拥有多个角色。我们下面创建四个用户，分别授予不同的角色。（冒号后面是授予的角色）

• syl_stu1：user_sc
• syl_stu2：user_sc，admin_sc
• syl_admin：admin_sc
• syl_super：super_sc

创建用户的命令如下：

create user syl_stu1 identified by sylstu1;
create user syl_stu2 identified by sylstu2;
create user syl_admin identified by syladmin;
create user syl_super identified by sylsuper;
--查看
select * from dba_users order by created desc;

授予角色的命令如下：

grant user_sc to syl_stu1;
grant user_sc,admin_sc to syl_stu2;
grant admin_sc to syl_admin;
grant super_sc to syl_super;
--查看
select * from dba_role_privs;

接下来我们登录 syl_stu2 这个用户查询，增加和删除 student 表的数据，看能否成功。

--用 syl_stu2 用户连接实例
SQL> conn syl_stu2/sylstu2
已连接。

--查询 student 表数据
SQL> select * from system.student;

      S_ID S_NAME               S_SEX           S_AGE
---------- -------------------- ---------- ----------
      1001 shiyanlou1001        man                10
      1002 shiyanlou1002        woman              20
      1003 shiyanlou1003        man                18
      1004 shiyanlou1004        woman              40
      1005 shiyanlou1005        man                17

--插入一条数据
SQL> insert into system.student values(1006,'shiyanlou1006','woman',22);

已创建 1 行。

--查询表中数据可以看到插入了一条数据
SQL> select * from system.student;

      S_ID S_NAME               S_SEX           S_AGE
---------- -------------------- ---------- ----------
      1001 shiyanlou1001        man                10
      1002 shiyanlou1002        woman              20
      1003 shiyanlou1003        man                18
      1004 shiyanlou1004        woman              40
      1005 shiyanlou1005        man                17
      1006 shiyanlou1006        woman              22

已选择 6 行。

--删除刚才插入的数据
SQL> delete from system.student where s_id=1006;

已删除 1 行。

如果我们想让 syl_stu2 这个用户只能查询数据怎么办。这个时候可以设置 syl_stu2 用户只有 user_sc 角色生效。

--设置只 user_sc 生效
SQL> set role user_sc;

--插入数据报错权限不足
SQL> insert into system.student values(1006,'shiyanlou1006','woman',22);
insert into system.student values(1006,'shiyanlou1006','woman',22)
                   *
第 1 行出现错误:
ORA-01031: 权限不足

设置角色生效和失效还有如下几种方法：

• SET ROLE ALL ：设置用户所有角色都生效。
• SET ROLE ALL EXCEPT <role name> ：除了这个角色以外的所有角色都生效。
• SET ROLE NONE ：设置所有角色都失效。

修改角色

修改角色同用户修改类似。首先我们以 system 用户连接实例，因为我们现在的 syl_stu2 没有修改角色的权限。

SQL> conn system/Syl12345

接下来的几个实例将学习如何修改角色。

例一：给角色设置密码以及撤销密码。

--给 user_sc 角色设置密码 usersc，会报错，因为其被授予了其他角色
SQL> alter role user_sc identified by usersc;

--给 super_sc 角色设置密码 supersc。
SQL> alter role super_sc identified by supersc;

Role SUPER_SC已变更。

--撤销 super_sc 的密码
SQL> alter role super_sc not identified;

Role SUPER_SC已变更。

例二：给 user_sc 角色增加向表 student 插入数据的权限。

SQL> grant insert on system.student to user_sc;

--查看 user_sc 角色拥有的对象权限，可以发现多了 insert 权限
SQL> select * from dba_tab_privs where grantee='USER_SC';

例三：撤销 user_sc 角色的 insert 权限。

SQL> revoke insert on system.student from user_sc;

--查看 user_sc 角色拥有的对象权限，可以发现 insert 权限没有了
SQL> select * from dba_tab_privs where grantee='USER_SC';

想了解更多有关设置角色的内容可参考 设置角色-官方文档

删除角色

删除角色很简单。例如我们要删除 super_sc 这个角色可以执行如下命令：

SQL> drop role super_sc;

--查询 super_sc，可发现已经被删除
SQL> select * from dba_roles where role='SUPER_SC';

概要文件管理

在之前创建用户的例子中，我们使用了 profile 指定了用户的概要文件，如果不指定概要文件，默认是 DEFAULT 。那么概要文件是什么？

概要文件其实是对数据库资源的一组限制，如果将配置文件分配给用户，则该用户不能超出这些限制，比如限制消耗 CPU 的时间，限制会话的时间等等。

创建概要文件

创建概要文件的语法见 创建概要文件-官方文档 。

很多时候一些软件都会限制输入密码的错误次数，如果超过指定的次数，则会将你的账户冻结一段时间。在 Oracle 中同样有这样的功能，可以使用概要文件实现。比如下面我们创建一个概要文件，限制输入口令的连续错误次数为 3 次，如果错误次数超过这个次数就锁定账户 1 天。

create profile pwd_time  --定义概要文件名称为 pwd_time
    limit failed_login_attempts 3   --限制连续错误次数
        password_lock_time 1;       --限制锁定账户天数

--查询概要文件，可以发现已经创建好 pwd_time 这个概要文件
select distinct profile from dba_profiles;

除了可以设置这些限制，还可以设置更多的限制，可参考 创建概要文件

修改概要文件

例如修改 pwd_time 概要文件，给它增加一个限制，限制用户的 CPU 会话并发数量为100 个。

SQL> alter profile pwd_time limit sessions_per_user 100;

--查询可以看到已限制为 100
SQL> select * from dba_profiles where profile='PWD_TIME' and resource_name='SESSIONS_PER_USER';

如果设置为 unlimited 则表示用户可以有任何数量的并发会话。

删除概要文件

例：删除概要文件 pwd_time 。

SQL> drop profile pwd_time cascade;

cascade 表示如果有用户使用了此概要文件，那么将那个用户使用的概要文件撤销。

总结

[Yehnn]

实例管理

实验介绍

实验内容

本节实验主要介绍了相应的连接实例，包括了如何进行初始化参数文件、数据库的启动关闭操作以及如何查看日志和动态性能视图。

实验知识点

• 连接实例

• 初始化参数文件

• 启动和关闭数据库实例

• 查看日志

• 动态性能视图

连接实例

在进行数据库操作时，首先需要了解的就是怎么连接实例，在前面我们已经使用过很多方式连接过，这里总结一下。

在连接实例时会进行身份验证，普通用户登录需要提供用户名和密码，用数据字典进行身份验证。如下使用 system 用户进行登录：

SQL> connect system/Syl12345

如果以 sysdba 权限或者 sysoper 权限连接数据库，在验证身份时不需要打开数据库，它的验证方式是将用户名和所提供的密码散列和外部密码文件中存储的值进行比较，或者使用操作系统身份进行验证。

例：

首先在终端输入如下命令进入 sqlplus。

$sqlplus /nolog

使用 /nolog 是为了进入 sqlplus 时不提示马上输入用户名和密码连接实例。

然后就可以使用如下几种方式连接实例：

--以 sysdba 权限连接，使用密码文件验证方式
SQL> conn system/Syl12345 as sysdba    

--以 sysoper 权限连接，使用密码文件验证方式
SQL> conn system/Syl12345 as sysoper

--以 sysdba 权限连接，使用操作系统身份验证
SQL> conn / as sysdba

--以 sysoper 权限连接，使用操作系统身份验证
SQL> conn / as sysoper

如果最后一句报错，是因为当前操作系统身份没有授予 sysoper 权限。

conn / as sysdba 登入后的用户名为 SYS ，可以使用 show user 查看当前连接实例的用户名。

conn / as sysoper 登入后的用户名为 PUBLIC。

初始化参数文件

实例是根据参数文件中的定义（由 SMON 进程读取）在内存中构建的。参数文件有两类：

• 静态参数文件（pfile或初始文件），客户端文件。可以使用编辑器编辑。
• 动态服务器参数文件（spfile），服务端文件。不可以使用编辑器编辑。

spfile 文件默认是 <ORACLE_HOME>/dbs/spfile<SID>.ora 。用如下命令可以看到 <ORACLE_HOME>/dbs/ 目录下有这个文件。

$ ls $ORACLE_HOME/dbs/

查看参数和值

从 v$parameter 和 v$spparameter 里可以查看到参数及其对应的值。

SQL> select name,value from v$parameter;
SQL> select name,value from v$spparameter;

可以看到两者的结果有所区别。

• v$parameter 中是当前运行的实例中生效的参数值。
• v$spparameter 中是 spfile 中存储的值。

产生这种区别的原因是有些参数可以在实例运行时更改，我们更改了，但并未保存到 spfile 中。

未保存到 spfile 的更改，在实例停止时失效。保存到 spfile 的更改，在下次启动实例时依然生效。

v$parameter 每列的说明可参见 v$parameter

查看有哪些初始化参数可参考 Parameter by Function Category

基本初始化参数

基本初始化参数是应该为每个数据库使用的参数。如下命令可查看基本参数及其当前值：

SQL> select name,value from v$parameter where isbasic='TRUE';

更多有关基本初始化参数可参考 基本初始化参数-官方文档

更改参数

更改参数可以使用：

• ALTER SYSTEM 系统级别的更改
• ALTER SESSION 会话几倍的更改

ALTER SYSTEM 的语法如下：

ALTER SYSTEM SET <name>=<value> SCOPE=MEMORY | SPFILE | BOTH

SCOPE 用于确定实在哪里进行修改。默认为 BOTH ，即应用于运行着的实例并写入 spfile。

• MEMORY ：修改内存中此参数的值。这个修改不是永久的，数据库关闭并重新启动，值会变为默认值。
• SPFILE ：修改 spfile 文件中此参数的值。这个修改是永久的。更改静态参数（也就是启动实例后不可更改，固定下来的参数）必须指定 SCOPE=SPFILE 。

参数可以使用哪种方式修改可参考 修改参数-官方文档 。也可以在 v$parameter 中查看

（参见v$parameter ）。

例一：更改 sql_trace 参数的值 。

首先查看此参数的当前值。

SQL> select name,value from v$parameter where name='sql_trace';

NAME           VALUE
-----------   ----------
sql_trace     FALSE

然后更改其在内存中的值。

--更改为 TRUE
SQL> alter system set sql_trace=TRUE scope=memory;
--查看其值
SQL> select name,value from v$parameter where name='sql_trace';
--更改为 FALSE
SQL> alter system set sql_trace=FALSE scope=memory;

例二：更改 processes 参数值。

更改其值为 400 。

SQL> alter system set processes=400;

会报错 ORA-02095: 无法修改指定的初始化参数 。因为 processes 是静态参数。必须指定 SCOPE=SPFILE 。

如下是正确修改方式：

--修改为 400
SQL> alter system set processes=400 scope=spfile;

--查看其值
SQL> select name,value from v$spparameter where name='processes';
--修改为 300
SQL> alter system set processes=300 scope=spfile;

例三：修改当前会话中 sql_trace 的值。

--查看其值
SQL> show parameter sql_trace;

NAME           TYPE    VALUE
------------ -------- ---------
sql_trace     boolean FALSE

--修改其值为 TRUE
SQL> alter session set sql_trace=TRUE;
--修改其值为 FALSE
SQL> alter session set sql_trace=FALSE;

启动和关闭数据库实例

启动的三个阶段

• SHUTDOWN ：关闭与数据库相关的所有文件，实例不存在。
• NOMOUNT ：根据参数文件的定义在内存中构建实例。实例启动。
• MOUNT ：读取控制文件（由 control_files 参数定位）。此时数据库是关闭的。
• OPEN ：打开数据库。

关闭的过程与启动相反。有关数据库启动和关闭更多内容可参见 Overview of Database Instance Startup and Shutdown

下面来实际操作数据库实例的启动和关闭。

1. 启动和关闭需要 sysdba 权限，所以首先以 sysdba 权限连接实例：

SQL> conn system/Syl12345 as sysdba;

2. 如果此时实例是启动状态，我们先将其关闭：

SQL> shutdown immediate;

3. 需要等待一段时间。待其关闭后执行如下命令只启动实例：

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 3841982464 bytes
Fixed Size                  8753768 bytes
Variable Size             872418712 bytes
Database Buffers         2952790016 bytes
Redo Buffers                8019968 bytes

4. 实例启动后，执行如下命令加载数据库：

SQL> alter database mount;

数据库已更改。

5. 接着打开数据库：

SQL> alter database open;

数据库已更改。

此时，数据库已经打开。我们可以使用如下命令确认数据库是否已经打开：

SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE

READ WRITE 说明已经打开。

以上所有步骤可以使用 startup force 一步完成。上述分阶段启动的好处是可以对不同阶段进行控制，比如控制文件损坏，我们可以停留在 NOMOUNT 阶段，把控制文件修复过后，再继续启动。

查看日志

警报日志

警报日志由 DIAGNOSTIC_DEST 参数确定，它提供数据库消息和错误信息（按时间排序）。下面命令查看警报日志的位置：

SQL> select name,value from v$spparameter where name='diagnostic_dest';

警报日志所在默认目录为 DIAGNOSTIC_DEST/diag/rdms/<dbname>/<instancename>/trace 。

DDL 日志

DDL 日志记录的是 DDL 命令。DDL 日志默认是停止的，首先要设置 enable_ddl_logging 参数值以启用：

--启用
SQL> alter system set enable_ddl_logging=TRUE;
--查看其值
SQL> select name,value from v$parameter where name='enable_ddl_logging';

DDL 日志所在的默认目录为 DIAGNOSTIC_DEST/diag/rdbms/<dbname>/<instancename>/log 下。

动态性能视图

Oracle 包含一组由数据库服务器维护并可供数据库管理员用户 SYS 访问的基础视图。这些视图被称为动态性能视图，因为它们在数据库处于打开和使用状态时不断更新，其内容主要与性能有关。这些视图提供有关内部磁盘结构和内存结构的数据。我们可以从这些视图中进行查询，但不能更新或更改它们。

（此段参考自 About Dynamic Performance Views-官方文档 ）

我们在之前用到了很多 v$ 为前缀的东西去查询一些信息，可能你会以为它是视图，实际上它并不是视图，而是某个视图的同义词。比如 v$parameter 实际上是 v_$parameter 视图的同义词。

Oracle 中有很多动态性能视图，可以参考 动态性能视图-官方文档

也可以用如下命令查看有哪些以 v$ 开头的动态性能视图：

SQL> select * from v$fixed_table where name like 'V$%';

例一：用数据字典和动态性能视图查询数据库包含的表空间和数据文件。

--使用数据字典查询
SQL> select tablespace_name from dba_tablespaces;
--使用动态性能图查询 
SQL> select name from v$tablespace;

例二：查询控制文件位置。

SQL> select * from v$parameter where name='control_files';
或者
SQL> select * from v$controlfile;

总结