Oracle知识概括@TOC Oracle简介 Oracle 简介： Oracle介绍： ①Oracle 是殷墟出土的甲

@TOC

Oracle简介

Oracle 简介：

Oracle介绍： ①Oracle 是殷墟出土的甲骨文(oracle bone inscriptions)的英文翻译的第一个单词。 ②Oracle 公司是全球最大的信息管理软件及服务供应商，成立于1977年，总部位于美国加州 Redwood shore。 ③Oracle 公司因其复杂的关系数据库产品而闻名。Oracle的关系数据库是世界第一个支持SQL语言的数据库。
Oracle概述： ①Oracle数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置) ②服务器与客户机是软件概念, 它们与计算机硬件不存在一一对应的关系. 即: 同一台计算机既可以充当服务器又可以充当客户机, 或者, 一台计算机只充当服务器或只充当充当客户机.

Oracle SQL Developer 和PL SQL Developer区别：

Oracle SQL Developer是Oracle公司出品的一个免费的集成开发环境。是一个免费非开源的用以开发数据库应用程序的图形化工具，使用 SQL Developer 可以浏览数据库对象、运行 SQL 语句和脚本、编辑和调试 PL/SQL语句。另外还可以创建执行和保存报表。该工具可以连接任何 Oracle 9.2.0.1 或者以上版本的 Oracle 数据库，支持Windows、Linux 和 Mac OS X 系统。
PL/SQL Developer是一个集成开发环境，由Allround Automations公司开发，专门面向Oracle数据库存储的程序单元的开发。有越来越多的商业逻辑和应用逻辑转向了Oracle Server，因此，PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL Developer侧重于易用性、代码品质和生产力，充分发挥Oracle应用程序开发过程中的主要优势的。

Oracle数据库的安装：

安装步骤： ①下载oracle 19c安装包，WINDOWS.X64_193000_db_home.zip，解压文件 ②以管理员身份运行setup.exe,注意解压和安装路径中都不不要有中文 ③选择以下任意安装选项：创建并配置【单实例数据库】。此选项用于创建启动数据库。 ④桌面类就是客户端，服务器类是数据库服务，根据需求选择，可考虑到后面深入学习Oracle数据库，所以选择安装【服务器类】的，当然对运行的系统资源的开销也大。 ⑤安装类型：有典型安装和高级安装，考虑到学习需要因此选择【高级安装】。 ⑥数据库版本：有企业版本和标准版本，考虑到学习需要因此选择【企业版本】。 ⑦Oracle主目录用户：这里可以选择使用【虚拟账户】或者指定【标准windows账户】。 ⑧安装位置：填写Oracle基目录：存放地址 ⑨配置类型：选择要创建的数据库类型，有一般用途/事务处理和数据库仓库两种选择，这里我们选择【一般用途/事务处理】。 ⑩数据库标识符：这里需要配置【全局数据库名】和【Oracle系统标识符(SID)】。统一设置为orcl。注意：如果不想在安装进度 42% 处等待半小时，请不要勾选创建为容器数据库选项 ⑪配置选项：此处主要配置数据库使用的内存，字符集以及示例方案，字符集方面我选择的是【UTF－８】编码。 ⑫数据库存储：选择【文件系统】，并默认指定了数据库文件存储位置。 ⑬管理选项：默认即可。 ⑭恢复选项：学习阶段可选择不启用。 ⑮方案口令：选择【对所有账户使用相同的口令】，然后输入口令和确认口令。注意：设置密码后会出现下面这个提示框，选择“是”即可，上面设置的密码只是为了方便使用 ⑯先决条件检查：检查安装条件。 ⑰概要：当安装环境检查完成后，会进入下面这个对话框，点击安装会启动安装程序。 ⑱安装产品：安装数据库。
验证：win + r 打开dos窗口，再命令行输入“ cmd ”回车，然后输入指令：sqlplus/nolog。
用户名与密码： ①用户名：system，密码：默认为创建的时候填的口令，SID：创建数据库时的数据库名，没更改的话，默认为orcl。

Oracle 数据库体系结构简介：

平常所说的 Oracle 或 Oracle 数据库指的是 Oracle 数据库管理系统. Oracle数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system). 它由 Oracle 数据库和Oracle 实例(instance)构成.
Oracle 数据库: 一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合,这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库。Oracle 用它来存储和管理相关的信息.Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。
Oracle 实例: 位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问。 ①Oracle 用它们来管理数据库访问.用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过Oracle实例才能实现, 不能直接读取硬盘上的文件。 ②实际上, Oracle 实例就是平常所说的数据库服务(service) 。
区别：实例可以操作数据库；在任何时刻一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）。

Oracle 数据库的启动：

Oracle 数据库是一个庞大的软件. 启动它会占有大量的内存和 CPU 资源. 如果不想让 Oracle 数据库自动启动可做如下设置： ①我的电脑-管理-服务和应用程序-服务： ②将ServiceORCL和Listener设置为手动，其它禁用

Oracle卸载：

windows服务中将Oracle所有服务全部停掉
选中Oracle - OraDb10g_home2->Oracle Installation Products->Universal Installer
以上只是简单的将Oracle卸载掉了，还学要对注册表进行修改： ①修改注册表，在开始-运行中执行regedit命令，进入注册表，对注册表中的键值进行修改 <1>将HKEY_CLASS_ROOT下所有以ORACLE或者ORAL开头的注册表项删除 <2>将HKEY_LOCAL_MACHINE\SOFTWARE下ORACLE注册表项删除 <3>将HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service下的以Oracle开头的注册表项删除
重新启动计算机
删除 c:\Program Files\Oracle目录
通过以上步骤才正真的完成了Oracle的卸载，此时可以安装新的Oracle

Oracle 的(资源限制)概要文件：

为了控制系统资源的使用, 可以利用资源限制概要文件.
资源限制概要文件是 Oracle 安全策略的重要组成部分, 利用资源限制概要文件可以对数据库用户进行基本的资源限制,而且还可以对用户的口令进行管理.
使用资源限制概要文件可以限制下列资源的使用： ①每个会话或每个语句的 CPU 时间(以百分之一秒计) ②每个用户的并发数据库会话 ③每个会话的最大链接事件和空闲时间(以分计) ④可供多线程服务器会话使用的最大的服务器内存.
使用资源限制概要文件可以对每个指定此概要文件的用户账号进行一下设置： ①允许用户连续输入错误口令的次数, 在此之后 Oracle 将锁定账户 ②口令的过期时间(以天计) ③允许用户使用一个到期口令的天数, 这之后 Oracle 将锁定账号 ④是否检查一个账号口令的复杂性, 以防止账号使用明显的口令
Oracle 数据库的默认概要文件： ①每个 Oracle 数据库都有一个默认的资源概要文件, 名为 DEFAULT ②当创建一个新的数据库用户且不对用户分配一个特定的概要文件时, Oracle 自动给用户分配数据库的 DEFAULT 概要文件. 默认时,数据库 DEFAULT 概要文件的所有资源限制设置为无限制的.
可以利用企业管理器查看概要文件和创建概要文件。

Oracle模式(schema)：

模式: 组织相关数据库对象的一个逻辑概念, 与数据库对象的物理存储无关. 一个模式只能属于一个数据库用户,而且模式的名称与用户的名称相同.
Oracle 数据库的每个用户都拥有唯一的模式. 默认情况下, 用户所创建的所有模式对象都保存在自己的模式中.在 Oracle数据库中模式与用户账号为一一对应的关系
如果要从一个模式中引用另一个模式中的对象, 可以使用点表示法. 不同模式中的对象名可以重复.

 george要访问 scott 用户的 emp 表, 
   scott.emp

模式对象和非模式对象： ①能包含在模式中的对象称为模式对象. ②Oracle 数据库中有许多类型的对象, 但不是所有的对象都可以组织在模式中. 可以组织在模式中的对象有: 表, 索引, 触发器等. ③有一些不属于任何模式的数据库对象, 称为非模式对象. 如: 表空间, 用户账号, 角色, 概要文件等.

Oracle用户表空间:

用户的默认表空间： ①表空间是数据库的逻辑存储设备, 它把数据库信息组织成物理存储空间. ②表空间由数据文件组成.用户的各种模式对象(如表, 索引, 过程, 触发器等) 都是放在表空间中. ③对每个数据库用户, 都可以设置一个默认表空间. 当用户创建一个新的数据库对象(如表), 并且不明确地为此对象指定表空间时, Oracle 会把所创建的这个新数据库对象存放到用户默认的表空间中. ④如果不给用户指定默认表空间, 则用户的默认表空间为 USERS 表空间.
用户的临时表空间： ①一般, SQL 语句在完成任务时需要临时工作空间. 例如：一个用来连接和排序大量的查询需要临时工作空间来存放结果. 除非另外指定, 一般情况下, 用户的临时表空间是 TEMP 表空间. ②若数据库中没有创建 TEMP 表空间, 则用户的临时表空间为 SYSTEM 表空间. ③因为 SYSTEM 表空间是用来保存数据库系统信息(数据库自身信息的内部系统表和视图 ---- 数据字典; 所有 PL/SQL 程序的源代码 ---- 包括函数, 触发器等)的. 如果用户大量使用此表空间存储自己的数据, 将会影响系统的执行效率. 因此一般不建议用户使用 SYSTEM 表空间

Oracle SQL

SQL语句分为以下三种类型：

DML: Data Manipulation Language 数据操纵语言，DML用于查询与修改数据记录，包括如下SQL语句： ①INSERT：添加数据到数据库中 ②UPDATE：修改数据库中的数据 ③DELETE：删除数据库中的数据 ④SELECT：选择（查询）数据 ⑤SELECT是SQL语言的基础，最为重要。
DDL: Data Definition Language 数据定义语言，DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句： ①CREATE TABLE：创建数据库表 ②ALTER TABLE：更改表结构、添加、删除、修改列长度 ③DROP TABLE：删除表 ④CREATE INDEX：在表上建立索引 ⑤DROP INDEX：删除索引
DCL: Data Control Language 数据控制语言，DCL用来控制数据库的访问，包括如下SQL语句： ①GRANT：授予访问权限 ②REVOKE：撤销访问权限 ③COMMIT：提交事务处理 ④ROLLBACK：事务处理回退 ⑤SAVEPOINT：设置保存点 ⑥LOCK：对数据库的特定部分进行锁定

基本SQL SELECT语句：

语句：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
注意： ①SQL 语言大小写不敏感。 ②SQL 可以写在一行或者多行 ③关键字不能被缩写也不能分行 ④各子句一般要分行写。 ⑤使用缩进提高语句的可读性。
操作符优先级： ①乘除的优先级高于加减。 ②同一优先级运算符从左向右执行。 ③括号内的运算先执行。
定义空值： ①空值是无效的，未指定的，未知的或不可预知的值。 ②空值不是空格或者0。 ③包含空值的数学表达式的值都为空值。
列的别名： ①重命名一个列。 ②便于计算。 ③紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
连接符: ①把列与列，列与字符连接在一起。 ②用 ‘||’表示。 ③可以用来‘合成’列。
字符串： ①字符串可以是 SELECT 列表中的一个字符,数字,日期。 ②日期和字符只能在单引号中出现。 ③每当返回一行时，字符串被输出一次。
重复行： ①默认情况下，查询会返回全部行，包括重复行。 ②在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行。
显示表结构：DESCRIBE(DESC) 表名;

SQL 和 SQL*Plus：

SQL： ①一种语言 ②ANSI 标准 ③关键字不能缩写 ④使用语句控制数据库中的表的定义信息和表中的数据
SQL*Plus： ①一种环境 ②Oracle 的特性之一 ③关键字可以缩写 ④命令不能改变数据库中的数据的值 ⑤集中运行
使用SQL*Plus可以： ①描述表结构。 ②编辑 SQL 语句。 ③执行 SQL语句。 ④将 SQL 保存在文件中并将SQL语句执行结果保存在文件中。 ⑤在保存的文件中执行语句。 ⑥将文本文件装入 SQL*Plus编辑窗口。

过滤和排序数据：

使用WHERE 子句，将不满足条件的行过滤掉。
语句：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];
字符和日期： ①字符和日期要包含在单引号中。 ②字符大小写敏感，日期格式敏感。 ③默认的日期格式是 DD-MON月-RR。
比较运算：

操作符	含义
=	等于 (不是 ==)
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<>	不等于 (也可以是 !=)
:=	赋值

其它比较运算： ①使用 BETWEEN 运算来显示在一个区间内的值。 ②使用 IN运算显示列表中的值。 ③使用 LIKE 运算选择类似的值，选择条件可以包含字符或数字: 1、% 代表零个或多个字符(任意个字符)。 2、_ 代表一个字符。 3、'%'和'_'可以同时使用。 4、可以使用 ESCAPE 标识符选择‘%’和 ‘_’ 符号。 5、回避特殊符号的：使用转义符。例如：将[%]转为[%]、[_]转为[_]，然后再加上[ESCAPE ‘\’] 即可。 ④使用 IS (NOT) NULL 判断空值。

操作符	含义
BETWEEN...AND...	在两个值之间 (包含边界)
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS NULL	空值

逻辑运算： ①AND 要求并的关系为真。 ②OR 要求或关系为真。

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

ORDER BY子句： ①使用 ORDER BY 子句排序 <1>ASC（ascend）: 升序 <2>DESC（descend）: 降序 ②ORDER BY 子句在SELECT语句的结尾。 ③按别名排序：SELECT salary*12 annsal FROM employees ORDER BY annsal; ④多个列排序：可以使用不在SELECT 列表中的列排序。

单行函数：

单行函数: ①操作数据对象 ②接受参数返回一个结果 ③只对一行进行变换 ④每行返回一个结果 ⑤可以转换数据类型 ⑥可以嵌套 ⑦参数可以是一列或一个值 ⑧function_name [(arg1, arg2,...)]
字符函数： ①大小写控制函数： <1>LOWER <2>UPPER <3>INITCAP ②字符控制函数 <1>CONCAT <2>SUBSTR <3>LENGTH <4>INSTR <5>LPAD | RPAD <6>TRIM <7>REPLACE
数字函数： ①ROUND: 四舍五入 ②TRUNC:截断 ③MOD: 求余
日期函数：Oracle 中的日期型数据实际含有两个值: 日期和时间。 ①函数SYSDATE 返回：日期和时间。 ②日期的数学运算： <1>在日期上加上或减去一个数字结果仍为日期。 <2>两个日期相减返回日期之间相差的天数。日期不允许做加法运算，无意义。 <3>可以用数字除24来向日期中加上或减去天数。 ③mysql文档中对于dual表的解释：你可以在没有表的情况下指定一个虚拟的表名。DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。

函数	描述
MONTHS_BETWEEN	两个日期相差的月数
ADD_MONTHS	向指定日期中加上若干月数
NEXT_DAY	指定日期的下一个星期 * 对应的日期
LAST_DAY	本月的最后一天
ROUND	日期四舍五入
TRUNC	日期截断

转换函数：数据类型转换分为隐性和显性。 ①Oracle 自动完成下列转换： ②显式数据类型转换： ③函数： <1>TO_CHAR函数对日期和数字的转换 <2>TO_DATE 函数对字符的转换 <3>TO_NUMBER 函数对字符的转换

日期格式的元素	-
YYYY	2004
YEAR	TWO THOUSAND AND FOUR
MM	02
MONTH	JULY
MON	JUL
DY	MON
DAY	MONDAY
DD	02

数字格式的元素	-
9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
,	千位符

通用函数：这些函数适用于任何数据类型，同时也适用于空值： ①NVL (expr1, expr2)：将空值转换成一个已知的值，可以使用的数据类型有日期、字符、数字。 ②NVL2 (expr1, expr2, expr3)：expr1不为NULL，返回expr2；为NULL，返回expr3。 ③NULLIF (expr1, expr2)：相等返回NULL，不等返回expr1 。 ④COALESCE (expr1, expr2, ..., exprn)：COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。 ⑤SYS_GUID()：该函数用于生产类型为RAW的16字节的唯一标识符，每次调用该函数都会发生不同的RAW数据。
条件表达式：在 SQL 语句中使用IF-THEN-ELSE 逻辑

一、CASE 表达式：
CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

二、DECODE 函数
DECODE(col|expression, search1, result1 ,
      			   [, search2, result2,...,]
      			   [, default])

嵌套函数： ①单行函数可以嵌套。 ②嵌套函数的执行顺序是由内到外。

多表查询：

笛卡尔集： ①笛卡尔集会在下面条件下产生: <1>省略连接条件 <2>连接条件无效 <3>所有表中的所有行互相连接 ②为了避免笛卡尔集，可以在 WHERE 加入有效的连接条件。
Oracle 连接： ①使用连接在多个表中查询数据。 ②SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; ③在 WHERE 子句中写入连接条件。 ④在表中有相同列时，在列名之前加上表名前缀。
等值连接：

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

区分重复的列名： ①使用表名前缀在多个表中区分相同的列。 ②在不同表中具有相同列名的列可以用表的别名加以区分。
表的别名： ①使用别名可以简化查询。 ②使用表名前缀可以提高执行效率。
非等值连接：

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary 
       BETWEEN j.lowest_sal AND j.highest_sal;

内连接和外连接： ①内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。 ②外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+)。 ③使用外连接可以查询不满足连接条件的数据。 ④外连接的符号是 (+)。

一、右外连接：
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

二、左外连接：
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

自连接：

SELECT worker.last_name || ' works for ' 
       || manager.last_name
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

使用SQL: 1999 语法连接：

SELECT	table1.column, table2.column
FROM	table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 
  ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name = table2.column_name)];

叉集（了解）: ①使用CROSS JOIN 子句使连接的表产生叉集。 ②叉集和笛卡尔集是相同的。
自然连接： ①NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。 ②在表中查询满足等值条件的数据。 ③如果只是列名相同而数据类型不同，则会产生错误。 ④返回的是，两个表中具有相同名字的列的“且、交集”，而非“或，并集”。即：比如employee类和department类都有department_id和manager_id，返回二者都相同的结果。

SELECT department_id, department_name,
       location_id, city
FROM   departments
NATURAL JOIN locations ;

使用 USING 子句创建连接： ①在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。 ②使用 USING 可以在有多个列满足条件时进行选择。 ③不要给选中的列中加上表名前缀或别名。 ④JOIN 和 USING 子句经常同时使用。

SELECT e.employee_id, e.last_name, d.location_id
FROM   employees e JOIN departments d
USING (department_id) ;

使用ON 子句创建连接（常用）： ①自然连接中是以具有相同名字的列为连接条件的。 ②可以使用 ON 子句指定额外的连接条件。 ③这个连接条件是与其它条件分开的。 ④ON 子句使语句具有更高的易读性。

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

内连接和外连接(SQL1999)： ①在SQL: 1999中，内连接只返回满足连接条件的数据 ②两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。 ③两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外连接。

一、左外连接
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

二、右外连接
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

三、满外连接
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

分组函数：

分组函数作用于一组数据，并对一组数据返回一个值。
组函数类型: ①AVG ②COUNT ③MAX ④MIN ⑤STDDEV ⑥SUM
组函数语法：

SELECT	[column,] group_function(column), ...
FROM		table
[WHERE	condition]
[GROUP BY	column]
[ORDER BY	column];

AVG（平均值）和 SUM （合计）函数：可以对数值型数据使用AVG 和 SUM 函数。
MIN（最小值）和 MAX（最大值）函数：可以对任意数据类型的数据使用 MIN 和 MAX 函数。
COUNT（计数）函数： ①COUNT(*) 返回表中记录总数,适用于任意数据类型。 ②COUNT(expr) 返回expr不为空的记录总数。
组函数与空值： ①组函数忽略空值。 ②在组函数中使用NVL函数：NVL函数使分组函数无法忽略空值。

SELECT AVG(NVL(commission_pct, 0)) FROM   employees;

DISTINCT 关键字：COUNT(DISTINCT expr)返回expr非空且不重复的记录总数
GROUP BY 子句： ①在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。 ②包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。

一、
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

二、
SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

非法使用组函数： ①所有包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。 ②不能在 WHERE 子句中使用组函数。 ③可以在 HAVING 子句中使用组函数。
过滤分组： HAVING 子句： ①使用 HAVING 过滤分组: <1>行已经被分组。 <2>使用了组函数。 <3>满足HAVING 子句中条件的分组将被显示。
嵌套组函数：

SELECT   MAX(AVG(salary))
FROM     employees
GROUP BY department_id;

子查询：

子查询 (内查询) 在主查询之前一次执行完成。子查询的结果被主查询(外查询)使用。

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list
		        FROM		table);

注意事项： ①子查询要包含在括号内。 ②将子查询放在比较条件的右侧。 ③单行操作符对应单行子查询，多行操作符对应多行子查询。
子查询类型： ①单行子查询 ②多行子查询
单行子查询： ①只返回一行。使用单行比较操作符。 ②执行单行子查询 <1>在子查询中使用组函数 <2>子查询中的 HAVING 子句

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

多行子查询： ①返回多行。 ②使用多行比较操作符。

操作符	含义
IN	等于列表中的任意一个
ANY	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

DCL与DDL

常见的数据库对象:

对象	描述
表	基本的数据存储集合，由行和列组成。
视图	从表中抽出的逻辑上相关的数据集合。
序列	提供有规律的数值。
索引	提高查询的效率
同义词	给对象起别名

Oracle 数据库中的表:

用户定义的表: ①用户自己创建并维护的一组表 ②包含了用户所需的信息。如：SELECT * FROM user_tables;查看用户创建的表。
数据字典: ①由 Oracle Server 自动创建的一组表 ②包含数据库信息

查询数据字典：

查看用户定义的表： ①SELECT table_name FROM user_tables ;
查看用户定义的各种数据库对象： ①SELECT DISTINCT object_type FROM user_objects ;
查看用户定义的表, 视图, 同义词和序列： ①SELECT * FROM user_catalog ;

命名规则：

表名和列名: ①必须以字母开头 ②必须在 1–30 个字符之间 ③必须只能包含 A–Z, a–z, 0–9, _, $, 和 # ④必须不能和用户定义的其他对象重名 ⑤必须不能是Oracle 的保留字

CREATE TABLE 语句：

必须具备: ①CREATE TABLE权限 ②存储空间

CREATE TABLE [schema.]table
	    (column datatype [DEFAULT expr][, ...]);

必须指定: ①表名 ②列名, 数据类型, 尺寸
使用子查询创建表： ①使用 AS subquery 选项，将创建表和插入数据结合起来。 ②指定的列和子查询中的列要一一对应 ③通过列名和默认值定义列

CREATE TABLE table
  	  [(column, column...)]
AS subquery;

临时表：

临时表只在Oracle 8i 以及以上产品中支持。ORACLE数据库除了可以保存永久表外，还可以建立临时表temporarytables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。
会话级的临时表创建方法：

SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (<column specification>)

ON COMMIT PRESERVE ROWS;

事务级临时表的创建方法：

SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (<column specification>)

ON COMMIT DELETE ROWS;

数据类型：

数据类型	描述
VARCHAR2(size)	可变长字符数据
CHAR(size)	定长字符数据
NUMBER(p,s)	可变长数值数据
DATE	日期型数据
LONG	可变长字符数据，最大可达到2G
CLOB	字符数据，最大可达到4G
RAW (LONG RAW)	原始的二进制数据
BLOB	二进制数据，最大可达到4G
BFILE	存储外部文件的二进制数据，最大可达到4G
ROWID	行地址

SQL数据类型	JDBC类型	标准的Java类型	Oracle扩展的Java类型
		1.0标准的JDBC类型:
CHAR	java.sql.Types.CHAR	java.lang.String	oracle.sql.CHAR
VARCHAR2	java.sql.Types.VARCHAR	java.lang.String	oracle.sql.CHAR
LONG	java.sql.Types.LONGVARCHAR	java.lang.String	oracle.sql.CHAR
NUMBER	java.sql.Types.NUMERIC	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.DECIMAL	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIT	boolean	oracle.sql.NUMBER
NUMBER	java.sql.Types.TINYINT	byte	oracle.sql.NUMBER
NUMBER	java.sql.Types.SMALLINT	short	oracle.sql.NUMBER
NUMBER	java.sql.Types.INTEGER	int	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIGINT	long	oracle.sql.NUMBER
NUMBER	java.sql.Types.REAL	float	oracle.sql.NUMBER
NUMBER	java.sql.Types.FLOAT	double	oracle.sql.NUMBER
NUMBER	java.sql.Types.DOUBLE	double	oracle.sql.NUMBER
RAW	java.sql.Types.BINARY	byte[]	oracle.sql.RAW
RAW	java.sql.Types.VARBINARY	byte[]	oracle.sql.RAW
LONGRAW	java.sql.Types.LONGVARBINARY	byte[]	oracle.sql.RAW
DATE	java.sql.Types.DATE	java.sql.Date	oracle.sql.DATE
DATE	java.sql.Types.TIME	java.sql.Time	oracle.sql.DATE
TIMESTAMP	java.sql.Types.TIMESTAMP	javal.sql.Timestamp	oracle.sql.TIMESTAMP
		2.0标准的JDBC类型:
BLOB	java.sql.Types.BLOB	java.sql.Blob	oracle.sql.BLOB
CLOB	java.sql.Types.CLOB	java.sql.Clob	oracle.sql.CLOB
用户定义的对象	java.sql.Types.STRUCT	java.sql.Struct	oracle.sql.STRUCT
用户定义的参考	java.sql.Types.REF	java.sql.Ref	oracle.sql.REF
用户定义的集合	java.sql.Types.ARRAY	java.sql.Array	oracle.sql.ARRAY
		Oracle扩展:
BFILE	oracle.jdbc.OracleTypes.BFILE	N/A	oracle.sql.BFILE
ROWID	oracle.jdbc.OracleTypes.ROWID	N/A	oracle.sql.ROWID
REF CURSOR	oracle.jdbc.OracleTypes.CURSOR	java.sql.ResultSet	oracle.jdbc.OracleResultSet
TIMESTAMP	oracle.jdbc.OracleTypes.TIMESTAMP	java.sql.Timestamp	oracle.sql.TIMESTAMP
TIMESTAMP WITH TIME ZONE	oracle.jdbc.OracleTypes.TIMESTAMPTZ	java.sql.Timestamp	oracle.sql.TIMESTAMPTZ
TIMESTAMP WITH LOCAL TIME ZONE	oracle.jdbc.OracleTypes.TIMESTAMPLTZ	java.sql.Timestamp	oracle.sql.TIMESTAMPLTZ

ALTER TABLE 语句:

使用 ALTER TABLE 语句可以: ①追加新的列 ②修改现有的列 ③为新追加的列定义默认值 ④删除一个列 ⑤重命名表的一个列名
使用 ALTER TABLE 语句追加, 修改, 或删除列的语法：

一、追加一个新列：
ALTER TABLE table
ADD		   (column datatype [DEFAULT expr]
		   [, column datatype]...);

二、修改一个列：
ALTER TABLE table
MODIFY	   (column datatype [DEFAULT expr]
		   [, column datatype]...);

三、删除一个列：
ALTER TABLE table
DROP COLUMN  column_name;

四、重命名一个列：
ALTER TABLE table_name RENAME COLUMM old_column_name 
TO new_column_name

删除表:

数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚

DROP TABLE dept80;

清空表:

TRUNCATE TABLE 语句: ①删除表中所有的数据 ②释放表的存储空间

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据,可以回滚

改变对象的名称:

执行RENAME语句改变表, 视图, 序列, 或同义词的名称

RENAME dept TO detail_dept;

必须是对象的拥有者。

数据操纵语言：

DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行: ①向表中插入数据 ②修改现存数据 ③删除现存数据
事务是由完成若干项工作的DML语句组成的

INSERT 语句语法：

使用 INSERT 语句向表中插入数据。

INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);

使用这种语法一次只能向表中插入一条数据。
注意： ①为每一列添加一个新值。 ②按列的默认顺序列出各个列的值。 ③在 INSERT 子句中随意列出列名和他们的值。 ④字符和日期型数据应包含在单引号中。
向表中插入空值：

一、隐式方式: 在列名表中省略该列的值
INSERT INTO	departments (department_id, 
                          department_name    )
VALUES		(30, 'Purchasing');

二、显示方式: 在VALUES 子句中指定空值
INSERT INTO	departments
VALUES		(100, 'Finance', NULL, NULL);

创建脚本： ①在SQL 语句中使用 & 变量指定列值。 ②& 变量放在VALUES子句中。
从其它表中拷贝数据： ①在 INSERT 语句中加入子查询。 ②不必书写 VALUES 子句。 ③子查询中的值列表应与 INSERT 子句中的列名对应

UPDATE 语句语法：

使用 UPDATE 语句更新数据。

UPDATE		table
SET		column = value [, column = value, ...]
[WHERE 		condition];

可以一次更新多条数据。
使用 WHERE 子句指定需要更新的数据。如果省略 WHERE 子句，则表中的所有数据都将被更新。

DELETE 语句：

使用 DELETE 语句从表中删除数据。

DELETE FROM	  table [WHERE	  condition];

使用 WHERE 子句删除指定的记录。如果省略 WHERE 子句，则表中的全部数据将被删除。
在 DELETE 中使用子查询，使删除基于另一个表中的数据。

数据库事务：

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成: ①一个或多个DML 语句 ②一个 DDL(Data Definition Language – 数据定义语言) 语句 ③一个 DCL(Data Control Language – 数据控制语言) 语句
数据库事务以第一个 DML 语句的执行作为开始。以下面的其中之一作为结束： ①COMMIT 或 ROLLBACK 语句 ②DDL 语句（自动提交） ③用户会话正常结束 ④系统异常终止
COMMIT和ROLLBACK语句的优点： ①使用COMMIT 和 ROLLBACK语句,我们可以确保数据完整性。 ②数据改变被提交之前预览。 ③将逻辑上相关的操作分组。
回滚到保留点： ①使用 SAVEPOINT 语句在当前事务中创建保存点。 ②使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
事务进程： ①自动提交在以下情况中执行: <1>DDL 语句。 <2>DCL 语句。 <3>不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。 ②会话异常结束或系统异常会导致自动回滚。
提交或回滚前的数据状态： ①改变前的数据状态是可以恢复的 ②执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正 ③其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 ④DML语句所涉及到的行被锁定，其他用户不能操作。
提交后的数据状态： ①数据的改变已经被保存到数据库中。 ②改变前的数据已经丢失。 ③所有用户可以看到结果。 ④锁被释放,其他用户可以操作涉及到的数据。 ⑤所有保存点被释放。
数据回滚后的状态： ①使用 ROLLBACK 语句可使数据变化失效: ②数据改变被取消。 ③修改前的数据状态被恢复。 ④锁被释放。

约束简介：

约束是表级的强制规定
有以下五种约束: ①NOT NULL ②UNIQUE ③PRIMARY KEY ④FOREIGN KEY ⑤CHECK
注意事项： ①如果不指定约束名，Oracle server 自动按照 SYS_Cn 的格式指定约束名。 ②创建和修改约束: <1>建表的同时 <2>建表之后 ③可以在表级或列级定义约束 ④可以通过数据字典视图查看约束
表级约束和列级约束： ①作用范围： <1>列级约束只能作用在一个列上 <2>表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上） ②定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。 ③非空(not null) 约束只能定义在列上
定义约束：

一、定义约束：
CREATE TABLE [schema.]table
	    (column datatype [DEFAULT expr]
		[column_constraint],
		...
		[table_constraint][,...]);

二、列级
column [CONSTRAINT constraint_name] constraint_type,

三、表级
column,...
  [CONSTRAINT constraint_name] constraint_type
  (column, ...),

NOT NULL 约束： ①保证列值不能为空。 ②只能定义在列级。
UNIQUE 约束： ①唯一约束，允许出现多个空值：NULL。 ②可以定义在表级或列级。
PRIMARY KEY 约束： ①主键 ②可以定义在表级或列级。
FOREIGN KEY 约束： ①外键 ②可以定义在表级或列级。 ③FOREIGN KEY 约束的关键字： <1>REFERENCES: 标示在父表中的列 <2>ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除 <3>ON DELETE SET NULL(级联置空): 子表中相应的列置空
CHECK 约束： ①定义每一行必须满足的条件

salary	NUMBER(2) CONSTRAINT emp_salary_min  CHECK (salary > 0)

添加约束的语法:使用 ALTER TABLE 语句 ①添加或删除约束,但是不能修改约束 ②有效化或无效化约束 ③添加 NOT NULL 约束要使用 MODIFY 语句

ALTER TABLE	 table ADD [CONSTRAINT constraint] type (column);

删除约束：

ALTER TABLE  employees  DROP CONSTRAINT  emp_manager_fk;

无效化约束：

ALTER TABLE	employees  DISABLE CONSTRAINT	emp_emp_id_pk;

激活约束：ENABLE 子句可将当前无效的约束激活。 ①当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引。

ALTER TABLE	employees ENABLE CONSTRAINT	emp_emp_id_pk;

查询约束：查询数据字典视图 USER_CONSTRAINTS。

SELECT	constraint_name, constraint_type,search_condition
FROM	user_constraints
WHERE	table_name = 'EMPLOYEES';

查询定义约束的列：查询数据字典视图 USER_CONS_COLUMNS。

SELECT	constraint_name, column_name
FROM	user_cons_columns
WHERE	table_name = 'EMPLOYEES';

其它数据库对象

视图简介：

简介： ①视图是一种虚表。 ②视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。 ③向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句. ④视图向用户提供基表数据的另一种表现形式
为什么使用视图： ①控制数据访问 ②简化查询 ③避免重复访问相同的数据
简单视图和复杂视图：

特性	简单视图	复杂视图
表的数量	一个	一个或多个
函数	没有	有
分组	没有	有
DML 操作	可以	有时可以

创建视图： ①在 CREATE VIEW 语句中嵌入子查询 ②子查询可以是复杂的 SELECT 语句

一、简单视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  [(alias[, alias]...)]
 AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

二、复杂视图
create or replace view empview 
as 
select employee_id emp_id,last_name name,department_name
from employees e,departments d
Where e.department_id = d.department_id

修改视图： ①使用CREATE OR REPLACE VIEW 子句修改视图 ②CREATE VIEW 子句中各列的别名应和子查询中各列相对应
视图中使用DML的规定： ①可以在简单视图中执行 DML 操作 ②当视图定义中包含以下元素之一时不能使用delete: <1>组函数 <2>GROUP BY 子句 <3>DISTINCT 关键字 <4>ROWNUM 伪列 ③当视图定义中包含以下元素之一时不能使用update: <1>组函数 <2>GROUP BY子句 <3>DISTINCT 关键字 <4>ROWNUM 伪列 <5>列的定义为表达式 ④当视图定义中包含以下元素之一时不能使insert: <1>组函数 <2>GROUP BY 子句 <3>DISTINCT 关键字 <4>ROWNUM 伪列 <5>列的定义为表达式 <6>表中非空的列在视图定义中未包括
屏蔽 DML 操作： ①可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作 ②任何 DML 操作都会返回一个Oracle server 错误

CREATE OR REPLACE VIEW empvu10
    (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
   FROM     employees
   WHERE    department_id = 10
   WITH READ ONLY;

删除视图：删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW view;

Top-N 分析： ①Top-N 分析查询一个列中最大或最小的 n 个值。 ②最大和最小的值的集合是 Top-N 分析所关心的。 ③注意：对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

序列简介：

序列: 可供多个用户用来产生唯一数值的数据库对象 ①自动提供唯一的数值 ②共享对象 ③主要用于提供主键值 ④将序列值装入内存可以提高访问效率
CREATE SEQUENCE 语句：定义序列

CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

查询序列： ①查询数据字典视图 USER_SEQUENCES 获取序列定义信息。 ②如果指定NOCACHE 选项，则列LAST_NUMBER 显示序列中下一个有效的值。

SELECT	sequence_name, min_value, max_value, increment_by, last_number
FROM	user_sequences;

NEXTVAL 和 CURRVAL 伪列： ①NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用 ②CURRVAL 中存放序列的当前值 ③NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。
使用序列： ①将序列值装入内存可提高访问效率 ②序列在下列情况下出现裂缝： <1>回滚 <2>系统异常 <3>多个表同时使用同一序列 ③如果不将序列的值装入内存(NOCACHE), 可使用表USER_SEQUENCES查看序列当前的有效值
修改序列：修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存。 ①修改序列的注意事项： <1>必须是序列的拥有者或对序列有 ALTER 权限 <2>只有将来的序列值会被改变 <3>改变序列的初始值只能通过删除序列之后重建序列的方法实现

ALTER SEQUENCE dept_deptid_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;

删除序列： ①使用 DROP SEQUENCE 语句删除序列 ②删除之后，序列不能再次被引用

DROP SEQUENCE dept_deptid_seq;

索引简介：

索引简介： ①一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中。 ②索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度。 ③索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引 ④在删除一个表时,所有基于该表的索引会自动被删除 ⑤通过指针加速 Oracle 服务器的查询速度 ⑥通过快速定位数据的方法，减少磁盘 I/O
创建索引： ①自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引 ②手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询 <1>在一个或多个列上创建索引

CREATE INDEX index ON table (column[, column]...);

什么时候创建索引： ①列中数据值分布范围很广 ②列经常在 WHERE 子句或连接条件中出现 ③表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
什么时候不要创建索引： ①表很小 ②列不经常作为连接条件或出现在WHERE子句中 ③查询的数据大于2%到4% ④表经常更新
查询索引： ①可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息。

SELECT	ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM	user_indexes ix, user_ind_columns ic
WHERE	ic.index_name = ix.index_name
AND	ic.table_name = 'EMPLOYEES';

删除索引： ①使用DROP INDEX 命令删除索引 ②只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引 ③删除操作是不可回滚的

DROP INDEX index;

同义词简介：

使用同义词访问相同的对象： ①方便访问其它用户的对象 ②缩短对象名字的长度

CREATE [PUBLIC] SYNONYM synonym FOR    object;

创建和删除同义词：

一、为视图DEPT_SUM_VU 创建同义词
CREATE SYNONYM  d_sum FOR  dept_sum_vu;

二、删除同义词
DROP SYNONYM d_sum;

控制用户权限

权限简介：

数据库安全性: ①系统安全性 ②数据安全性
系统权限: 对于数据库的权限 ①超过一百多种有效的权限 ②数据库管理员具有高级权限以完成管理任务，例如: <1>创建新用户 <2>删除用户 <3>删除表 <4>备份表
对象权限: 操作数据库对象的权限

用户简介：

创建用户：DBA 使用 CREATE USER 语句创建用户

CREATE USER user  IDENTIFIED BY  password;

用户的系统权限：用户创建之后, DBA 会赋予用户一些系统权限 ①以应用程序开发者为例, 一般具有下列系统权限： <1>CREATE SESSION（创建会话） <2>CREATE TABLE（创建表） <3>CREATE SEQUENCE（创建序列） <4>CREATE VIEW（创建视图） <5>CREATE PROCEDURE（创建过程） ②DBA 可以赋予用户特定的权限

GRANT privilege [, privilege...]  TO user [, user| role, PUBLIC...];

举例：
GRANT  create session, create table, 
       create sequence, create view
TO     scott;

创建用户表空间：用户拥有create table权限之外，还需要分配相应的表空间才可开辟存储空间用于创建的表。

ALTER USER atguigu01 QUOTA UNLIMITED  ON users;

角色简介： 在这里插入图片描述

创建角色：

CREATE ROLE manager;

为角色赋予权限：

GRANT create table,create view TO manager;

将角色赋予用户：

GRANT manager TO DEHAAN, KOCHHAR;

修改密码：

DBA 可以创建用户和修改密码
用户本人可以使用 ALTER USER 语句修改密码

ALTER USER scott IDENTIFIED BY lion;

对象权限：

不同的对象具有不同的对象权限
对象的拥有者拥有所有权限
对象的拥有者可以向外分配权限

GRANT	object_priv [(columns)]
 ON		object
 TO		{user|role|PUBLIC}
 [WITH GRANT OPTION];

一、分配表 EMPLOYEES 的查询权限:
GRANT  select
ON     employees
TO     sue, rich;

二、分配表中各个列的更新权限:
GRANT  update 
ON     scott.departments
TO     atguigu;

WITH GRANT OPTION和PUBLIC关键字：

一、WITH GRANT OPTION 使用户同样具有分配权限的权利:
GRANT  select, insert
ON     departments
TO     scott
WITH   GRANT OPTION;

二、向数据库中所有用户分配权限：
GRANT  select
ON	  alice.departments
TO	  PUBLIC;

收回对象权限： ①使用 REVOKE 语句收回权限 ②使用 WITH GRANT OPTION 子句所分配的权限同样被收回

REVOKE {privilege [, privilege...]|ALL}
ON	  object
FROM   {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

查询权限分配情况： 在这里插入图片描述

SET 运算符

SET 操作符简介：

UNION 操作符：UNION 操作符返回两个查询的结果集的并集
UNION ALL 操作符：UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
INTERSECT 操作符：INTERSECT 操作符返回两个结果集的交集
MINUS 操作符：MINUS操作符返回两个结果集的差集

使用 SET 操作符注意事项：

在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句： ①只能在语句的最后出现 ②可以使用第一个查询中的列名, 别名或相对位置
除 UNION ALL之外，系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

高级子查询

子查询简介：

子查询是嵌套在 SQL 语句中的另一个SELECT 语句
子查询 (内查询) 在主查询执行之前执行
主查询(外查询)使用子查询的结果

多列子查询简介：

主查询与子查询返回的多个列进行比较
多列子查询中的比较分为两种： ①成对比较 ②不成对比较

一、成对比较
SELECT	employee_id, manager_id, department_id
FROM	employees
WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
AND	employee_id NOT IN (141,174);

二、不成对比较
SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN                   (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN                   (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND	employee_id NOT IN(174,141);

单列子查询简介：

单列子查询表达式是在一行中只返回一列的子查询
Oracle8i 只在下列情况下可以使用, 例如: ①SELECT 语句 (FROM 和 WHERE 子句) ②INSERT 语句中的VALUES列表中
Oracle9i中单列子查询表达式可在下列情况下使用: ①DECODE 和 CASE ②SELECT 中除 GROUP BY 子句以外的所有子句中

相关子查询简介：

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
子查询中使用主查询中的列：

一、EXISTS
SELECT column1, column2, ...
 FROM   table1 outer
 WHERE  column1 operator 
			  (SELECT  colum1, column2
                       FROM    table2
                       WHERE   expr1 = 	outer.expr2);

二、NOT EXISTS
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id 
                         = d.department_id);

EXISTS 操作符：

EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行: ①不在子查询中继续查找 ②条件返回 TRUE
如果在子查询中不存在满足条件的行: ①条件返回 FALSE ②继续在子查询中查找

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

相关更新与删除：

使用相关子查询依据一个表中的数据更新另一个表的数据：

UPDATE table1 alias1
SET    column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column =    
                        alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据：

DELETE FROM table1 alias1
 WHERE  column operator 
	(SELECT expression
 	 FROM   table2 alias2
 	 WHERE  alias1.column = alias2.column);

WITH 子句：

使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率

WITH dept_costs  AS (
   SELECT  d.department_name, SUM(e.salary) AS dept_total
   FROM    employees e, departments d
   WHERE   e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost    AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM   dept_costs)
SELECT * 
FROM   dept_costs 
WHERE  dept_total >
        (SELECT dept_avg 
         FROM avg_cost)
ORDER BY department_name;

Oracle其他

表字段的数据类型：

mysql： ①数值型： tinyint 超小整数 smallint 小整数 mediumint 中整数 int整数 bigint大整数 float 单精度浮点型 double 双精度浮点型 ②字符型： char定长字符串 varchar变长字符串 blob二进制长文本 text长文本 longblob二进制超长文本 longtext超长文本 ③日期类型： date日期 time时间 year年份 datetime时期加时间 timestamp时间戳　
oracle： ①数值型： number(m,n) 表示数据长度，n表示小数点位数； ②字符型： char(n) 用于标识固定长度的字符串； varchar2(n) 可变长度的字符串类型，最长为4000，不可以存储空字符串""，没有数据时为null blob 相当于mysql的longblob clob 相当于mysql的longtexts ③日期类型： date 相当于mysql的dateTime； timestamp 相当于mysql的timestamp

其它sql语法差异：

组函数用法规则： ①mysql中组函数在select语句中可以随意使用，但在oracle中如果查询语句中有组函数，那其他列名必须是组函数处理过的，或者是group by子句中的列否则报错

eg：
select name,count(money) from user；这个放在mysql中没有问题在oracle中就有问题了。
而select name,count(money) from user group by name或者select max(name),count(money) from user；
在oracle就不会报错，同样这两种情况在mysql也不会报错

自动增长的数据类型处理： ①MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。 ②CREATE SEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE; ③其中最大的值按字段的长度来定，如果定义的自动增长的序列号NUMBER(6)，最大值为999999 ④INSERT语句插入这个字段值为：序列号的名称.NEXTVAL
单引号的处理： ①MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。
翻页的SQL语句的处理： ①MYSQL 处理翻页的SQL语句比较简单，用LIMIT开始位置，记录个数；PHP里还可以用SEEK定位到结果集的位置。ORACLE处理翻页的 SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置，并且只能用ROWNUM<100，不能用ROWNUM>80。 ②以下是经过分析后较好的两种ORACLE翻页SQL语句(ID是唯一关键字的字段名)：语句一： SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3; 语句二： SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;
长字符串的处理： ①长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节，如果要插入更长的字符串，请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告，返回上次操作。
日期字段的处理： ①MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为 SYSDATE，精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式，可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’) ②日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用DATE_FIELD_NAME > SUBDATE(NOW()，INTERVAL 7 DAY)ORACLE找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7; ③MYSQL中插入当前时间的几个函数是：NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以’YYYY-MM-DD’的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以’HH:MM:SS’的格式返回当前的时间，可以直接存到TIME字段中。例：insert into tablename (fieldname) values (now()) ④而oracle中当前时间是sysdate
空字符的处理： ①MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构，导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。
字符串的模糊比较： ①MYSQL里用字段名like%‘字符串%’，ORACLE里也可以用字段名like%‘字符串%’但这种方法不能使用索引，速度不快，用字符串比较函数instr(字段名，‘字符串’)>0会得到更精确的查找结果。
程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。

PL/SQL

PL/SQL简介：

PL/SQL 是 Procedure Language & Structured Query Language 的缩写。ORACLE 的SQL 是支持 ANSI(American national Standards Institute)和 ISO92(International Standards Organization)标准的产品。PL/SQL 是对 SQL 语言存储过程语言的扩展。从 ORACLE6 以后，ORACLE 的 RDBMS 附带了PL/SQL。它现在已经成为一种过程处理语言，简称 PL/SQL。
目前的 PL/SQL 包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如 C 语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库 PL/SQL 和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具 PL/SQL 另外还增加了用于支持工具（如 ORACLE Forms）的句法，如：在窗体上设置按钮等。
PL/SQL 的好处： ①有利于客户/服务器环境应用的运行：对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用 PL/SQL 进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。 ②适合于客户环境：PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。对于客户端来说，PL/SQL 可以嵌套到相应的工具中，客户端程序可以执行本地包含 PL/SQL 部分，也可以向服务发 SQL 命令或激活服务器端的 PL/SQL 程序运行。
PL/SQL 可用的 SQL 语句： ①PL/SQL 是 ORACLE 系统的核心语言，现在 ORACLE 的许多部件都是由 PL/SQL 写成。在 PL/SQL 中可以使用的 SQL 语句有：INSERT，UPDATE，DELETE，SELECT … INTO，COMMIT，ROLLBACK，SAVEPOINT。 ②提示：在 PL/SQL 中只能用 SQL 语句中的 DML 部分，不能用 DDL 部分，如果要在 PL/SQL 中使用 DDL(如CREATE table 等)的话，只能以动态的方式来使用。 <1>ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。 <2>PL/SQL 可以在 SQL*PLUS 中使用。 <3>PL/SQL 可以在高级语言中使用。 <4>PL/SQL 可以在 ORACLE 的开发工具中使用。 <5>其它开发工具也可以调用 PL/SQL 编写的过程和函数，如 Power Builder 等都可以调用服务器端的PL/SQL 过程。
运行 PL/SQL 程序：PL/SQL 程序的运行是通过 ORACLE 中的一个引擎来进行的。这个引擎可能在 ORACLE 的服务器端，也可能在 ORACLE 应用开发的客户端。引擎执行 PL/SQL 中的过程性语句，然后将 SQL 语句发送给数据库服务器来执行。再将结果返回给执行端。

PL/SQL 块结构和组成元素：

PL/SQL 程序由三个块组成，即声明部分、执行部分、异常处理部分
PL/SQL 块的结构如下：其中执行部分是必须的。

DECLARE 
/* 声明部分: 在此声明 PL/SQL 用到的变量,类型及游标，以及局部的存储过程和函数 */
BEGIN
 /* 执行部分: 过程及 SQL 语句 , 即程序的主要部分 */
EXCEPTION
 /* 执行异常部分: 错误处理 */
END;

PL/SQL 块可以分为三类： ①无名块：动态构造，只能执行一次。 ②子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。 ③触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
PL/SQL 结构： ①PL/SQL 块中可以包含子块； ②子块可以位于 PL/SQL 中的任何部分； ③子块也即 PL/SQL 中的一条命令；

标识符：

PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有： ①标识符名不能超过 30 字符； ②第一个字符必须为字母； ③不分大小写； ④不能用’-‘(减号); ⑤不能是 SQL 保留字。
提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：

标识符	命名规则	例子
程序变量	V_name	V_name
程序常量	C_Name	C_company_name
游标变量	Name_cursor	Emp_cursor
异常标识	E_name	E_too_many
表类型	Name_table_type	Emp_record_type
表	Name_table	Emp
记录类型	Name_record	Emp_record
SQL*Plus 替代变量	P_name	P_sal
绑定变量	G_name	G_year_sal

PL/SQL 变量类型：

在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表是 ORACLE 类型和 PL/SQL 中的变量类型的合法使用列表：
变量类型：在 ORACLE8i 中可以使用的变量类型有：
复合类型： ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表。 ①记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。 ②PL/SQL 表(嵌套表)：PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表.

记录类型：

定义记录类型语法如下：

TYPE record_type IS RECORD(
 Field1 type1 [NOT NULL] [:= exp1 ],
 Field2 type2 [NOT NULL] [:= exp2 ],
 . . . . . .
 Fieldn typen [NOT NULL] [:= expn ] ) ;

使用%TYPE：定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。使用%TYPE 特性的优点在于： ①所引用的数据库列的数据类型可以不必知道； ②所引用的数据库列的数据类型可以实时改变。
使用%ROWTYPE：PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。使用%ROWTYPE 特性的优点在于： ①所引用的数据库中列的个数和数据类型可以不必知道； ②所引用的数据库中列的个数和数据类型可以实时改变。

PL/SQL 表(嵌套表)：

声明嵌套表类型的一般语法如下:

TYPE type_name IS TABLE OF
{datatype | {variable | table.column} % type | table%rowtype};

说明: ①在使用嵌套表之前必须先使用该集合的构造器初始化它. PL/SQL 自动提供一个带有相同名字的构造器作为集合类型.
②嵌套表可以有任意数量的行. 表的大小在必要时可动态地增加或减少: extend(x) 方法添加 x 个空元素到集合末尾; trim(x)方法为去掉集合末尾的 x 个元素.

数据库赋值：

数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与 SELECT中的列名要一一对应。
提示：不能将SELECT语句中的列赋值给布尔变量。

可转换的类型赋值：

CHAR 转换为 NUMBER： ①使用 TO_NUMBER 函数来完成字符到数字的转换，如： ②v_total := TO_NUMBER(‘100.0’) + sal;
NUMBER 转换为 CHAR： ①使用 TO_CHAR 函数可以实现数字到字符的转换，如： ②v_comm := TO_CHAR(‘123.45’) || ’元’ ;
字符转换为日期： ①使用 TO_DATE 函数可以实现字符到日期的转换，如： ②v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
日期转换为字符： ①使用 TO_CHAR 函数可以实现日期到字符的转换，如： ②v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

变量作用范围及可见性：

在 PL/SQL 编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类似，PL/SQL 的变量作用范围特点是： ①变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。 ②一个变量（标识）只能在你所引用的块内是可见的。 ③当一个变量超出了作用范围，PL/SQL 引擎就释放用来存放该变量的空间（因为它可能不用了）。 ④在子块中重新定义该变量后，它的作用仅在该块内。

注释：

在PL/SQL里，可以使用两种符号来写注释。
使用双‘-‘( 减号) 加注释，PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。
使用 /* */ 来加一行或多行注释，提示：被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉。

PL/SQL 流程控制语句：

介绍 PL/SQL 的流程控制语句, 包括如下三类
控制语句: IF 语句
循环语句: LOOP 语句, EXIT 语句
顺序语句: GOTO 语句, NULL 语句

一、条件语句
IF <布尔表达式> THEN
	PL/SQL 和 SQL 语句;
END IF;

IF <布尔表达式> THEN
	PL/SQL 和 SQL 语句;
ELSE
	其它语句;
END IF;
	
IF <布尔表达式> THEN
	PL/SQL 和 SQL 语句;
ELSIF < 其它布尔表达式> THEN
	其它语句;
ELSIF < 其它布尔表达式> THEN
	其它语句;
ELSE
	其它语句;
END IF;

二、CASE 表达式
CASE selector
	WHEN expression1 THEN result1
	WHEN expression2 THEN result2
	
	WHEN expressionN THEN resultN
	[ ELSE resultN+1]
END;

三、简单循环
LOOP
 	要执行的语句;
 	EXIT WHEN <条件语句> ; /*条件满足，退出循环语句*/
END LOOP;

四、WHILE 循环(相较 1，推荐使用 2)
WHILE <布尔表达式> LOOP
 	要执行的语句;
END LOOP;

五、数字式循环
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
 	要执行的语句;
END LOOP;

每循环一次，循环变量自动加 1；使用关键字 REVERSE，循环变量自动减 1。跟在 IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用 EXIT 退出循环。

六、标号和 GOTO 
PL/SQL 中 GOTO 语句是无条件跳转到指定的标号去的意思。语法如下：
	GOTO label;
	. . . . . .
<<label>> /*标号是用<< >>括起来的标识符 */

七、NULL 语句
在 PL/SQL 程序中，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性。如：
DECLARE
	. . .
BEGIN
	…
	IF v_num IS NULL THEN
		GOTO print1;
	END IF;
	…
	<<print1>>
	NULL; -- 不需要处理任何数据。
END;

游标的使用：

在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。
游标概念：为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL 可以控制上下文区和处理语句时上下文区会发生些什么事情。对于不同的 SQL 语句，游标的使用情况不同：

SQL语句	游标
非查询语句	隐式的
结果是单行的查询语句	隐式的或显示的
结果是多行的查询语句	显示的

处理显式游标：

显式游标处理需四个 PL/SQL 步骤:
定义游标：就是定义一个游标名，以及与其相对应的 SELECT 语句。格式：CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement; ①游标参数只能为输入参数，其格式为：parameter_name [IN] datatype [{:= | DEFAULT} expression]在指定数据类型时，不能使用长度约束。如 NUMBER(4)、CHAR(10) 等都是错误的。
打开游标：就是执行游标所对应的 SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有 FOR UPDATE 选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。格式：OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)]; ①在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用 OPEN 语句重复打开一个游标。
提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。格式：FETCH cursor_name INTO {variable_list | record_variable };
对该记录进行处理；
继续处理，直到活动集合中没有记录；
关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。关闭后的游标可以使用 OPEN 语句重新打开。格式：CLOSE cursor_name;
注：定义的游标不能有 INTO 子句。

游标属性：

%FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；
%NOTFOUND 布尔型属性，与%FOUND 相反；
%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

游标的 FOR 循环：

PL/SQL 语言提供了游标 FOR 循环语句，自动执行游标的 OPEN、FETCH、CLOSE 语句和循环语句的功能；当进入循环时，游标 FOR 循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标 FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：

FOR index_variable IN cursor_name[value[, value]…] LOOP
	-- 游标数据处理代码
END LOOP;

其中：index_variable 为游标 FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable 中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标 FOR 循环语句中的索引变量来访问这些列数据。
注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制 FOR 循环的记录。

处理隐式游标:

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。
格式调用为： SQL%
隐式游标属性： ①SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE； ②SQL%NOTFOUND 布尔型属性,与%FOUND 相反； ③SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数； ④SQL %ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。

关于 NO_DATA_FOUND 和 %NOTFOUND 的区别:

SELECT … INTO 语句触发 NO_DATA_FOUND；
当一个显式游标的 WHERE 子句未找到时触发%NOTFOUND；
当 UPDATE 或 DELETE 语句的 WHERE 子句未找到时触发 SQL%NOTFOUND；
在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND.

游标修改和删除操作：

游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用 FOR UPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。
为了对正在处理(查询)的行不被另外的用户改动，ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE 锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。
语法：

SELECT . . . FROM … FOR UPDATE [OF column[, column]…] [NOWAIT]

如果另一个会话已对活动集中的行加了锁，那么 SELECT FOR UPDATE 操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上 NOWAIT 子句时，如果这些行真的被另一个会话锁定，则 OPEN 立即返回并给出：ORA-0054 ：resource busy and acquire with nowait specified.
如果使用 FOR UPDATE 声明游标，则可在 DELETE 和 UPDATE 语句中使用 WHERE CURRENT OF cursor_name 子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。

异常错误处理:

一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE 提供异常情况 (EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。
异常处理概念：异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于 PL/SQL 程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。
有三种类型的异常错误： ①预定义 ( Predefined )错误：ORACLE 预定义的异常情况大约有 24 个。对这种异常情况的处理，无需在程序中定义，由 ORACLE 自动将其引发。 ② 非预定义 ( Predefined )错误：即其他标准的 ORACLE 错误。对这种异常情况的处理，需要用户在程序中定义，然后由 ORACLE 自动将其引发。 ③用户定义(User_define) 错误：程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:

EXCEPTION
 WHEN first_exception THEN <code to handle first exception >
 WHEN second_exception THEN <code to handle second exception >
 WHEN OTHERS THEN <code to handle others exception >
END;

异常处理可以按任意次序排列,但 OTHERS 必须放在最后.

预定义的异常处理：

预定义说明的部分 ORACLE 异常错误：

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	试图破坏一个唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到 ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found SELECT INTO	没有找到数据
ORA-1422	Too_many_rows SELECT INTO	返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与 PL/SQL 变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已存在的游标
ORA-6530	Access-INTO-null	试图为 null 对象的属性赋值
ORA-6531	Collection-is-null	试图将 Exists 以外的集合( collection)方法应用于一个 null pl/sql 表上或 varray 上
ORA-6532	Subscript-outside-limit	对嵌套或 varray 索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或 varray 索引得引用大于集合中元素的个数.

对这种异常情况的处理，只需在 PL/SQL 块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。

非预定义的异常处理：

对于这类异常情况的处理，首先必须对非定义的 ORACLE 错误进行定义。步骤如下： ①在 PL/SQL 块的定义部分定义异常情况：<异常情况> EXCEPTION; ②将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 PRAGMA EXCEPTION_INIT 语句：PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>); ③在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

用户自定义的异常处理:

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION 块异常错误部分，执行错误处理代码。
对于这类异常情况的处理，步骤如下： ① 在 PL/SQL 块的定义部分定义异常情况： <异常情况> EXCEPTION; ②RAISE <异常情况>； ③在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

在 PL/SQL 中使用 SQLCODE, SQLERRM：

SQLCODE 返回错误代码数字
SQLERRM 返回错误信息

存储函数和过程：

ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

创建函数：

建立内嵌函数：

CREATE [OR REPLACE] FUNCTION function_name
	[ (argment [ { IN | IN OUT }] Type,
	 argment [ { IN | OUT | IN OUT } ] Type ]
	[ AUTHID DEFINER | CURRENT_USER ]
	RETURN return_type 
	{ IS | AS }
	<类型.变量的说明>
BEGIN
	FUNCTION_body
EXCEPTION
	其它语句
END;

说明: ①OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突 ②函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数隐含为 IN。 ③因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型

内嵌函数的调用：

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：
第一种参数传递格式称为位置表示法，格式为：argument_value1[,argument_value2 …]
第二种参数传递格式称为名称表示法，格式为：argument => parameter [,…]其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。
第三种参数传递格式称为混合表示法：即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。
无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。
传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE 将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。

参数默认值:

在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。
具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

存储过程：

在 ORACLE SERVER 上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.
创建过程语法:

CREATE [OR REPLACE] PROCEDURE Procedure_name
	[ (argment [ { IN | IN OUT }] Type,
	 argment [ { IN | OUT | IN OUT } ] Type ]
	[ AUTHID DEFINER | CURRENT_USER ]
	{ IS | AS }
	<类型.变量的说明>
BEGIN
	<执行部分>
EXCEPTION
	<可选的异常错误处理程序>
END;

调用存储过程：ORACLE 使用 EXECUTE 语句来实现对存储过程的调用：

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

斜杠(/)：就是让服务器执行前面所写的sql脚本。如果是普通的select语句，一个分号，就可以执行了。但是如果是存储过程，那么遇到分号，就不能马上执行了。这个时候，就需要通过斜杠(/)来执行。

开发存储过程步骤：

使用文字编辑处理软件编辑存储过程源码，需将源码存为文本格式。
在 SQLPLUS 或用调试工具将存储过程程序进行解释 ①在 SQL>下调试，可用 START 或 GET 等 ORACLE 命令来启动解释。如：SQL>START c:\stat1.sql
调试源码直到正确： ①我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。 ②在 SQLPLUS 下来调式主要用的方法是： <1>使用 SHOW ERROR 命令来提示源码的错误位置； <2>使用 user_errors 数据字典来查看各存储过程的错误位置。
授权执行权给相关的用户或角色： ①如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在 SQL*PLUS 下可以用 GRANT 命令来进行存储过程的运行授权。 ②GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION
与过程相关数据字典：USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS。 ①相关的权限: CREATE ANY PROCEDURE，DROP ANY PROCEDURE。 ②在 SQL*PLUS 中，可以用 DESCRIBE 命令查看过程的名字及其参数表。 ③DESCRIBE Procedure_name;

删除过程和函数：

删除过程： ①可以使用 DROP PROCEDURE 命令对不需要的过程进行删除，语法如下：DROP PROCEDURE [user.]Procudure_name;
删除函数： ①可以使用 DROP FUNCTION 命令对不需要的函数进行删除，语法如下：DROP FUNCTION [user.]Function_name;

触发器：

触发器是许多关系数据库系统都提供的一项技术。在 ORACLE 系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的 PL/SQL块。
触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE 事件指的是对数据库的表进行的INSERT、UPDATE 及 DELETE 操作或对视图进行类似的操作。ORACLE 将触发器的功能扩展到了触发 ORACLE，如数据库的启动与关闭等。 ①DML 触发器：ORACLE 可以在 DML 语句进行触发，可以在 DML 操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。 ② 替代触发器：由于在 ORACLE 里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。 ③ 替代触发器：由于在 ORACLE 里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

触发器组成:

触发事件：即在何种情况下触发 TRIGGER; 例如：INSERT, UPDATE, DELETE。
触发时间：即该 TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该 TRIGGER的操作顺序。
触发器本身：即该 TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL 块。
触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 ①语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次； ②行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

创建触发器：

CREATE [OR REPLACE] TRIGGER trigger_name
	{BEFORE | AFTER }
	{INSERT | DELETE | UPDATE [OF column [, column …]]}
	ON [schema.] table_name 
	[FOR EACH ROW ]
	[WHEN condition]
	trigger_body;

其中： ①BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。 ②FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个 DML 语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器，而 INSTEAD OF 触发器则为行触发器。 ③WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用 PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器中，不能用在 INSTEAD OF 行触发器和其它类型的触发器中。 ④当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。 ⑤每张表最多可建立 12 种类型的触发器，它们是: BEFORE INSERT BEFORE INSERT FOR EACH ROW AFTER INSERT AFTER INSERT FOR EACH ROW BEFORE UPDATE BEFORE UPDATE FOR EACH ROW AFTER UPDATE AFTER UPDATE FOR EACH ROW BEFORE DELETE BEFORE DELETE FOR EACH ROW AFTER DELETE AFTER DELETE FOR EACH ROW

触发器触发次序：

执行 BEFORE 语句级触发器;
对与受语句影响的每一行： ①执行 BEFORE 行级触发器 ②执行 DML 语句 ③执行 AFTER 行级触发器
执行 AFTER 语句级触发器

创建 DML 触发器：

触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。

触发器的限制：

CREATE TRIGGER 语句文本的字符长度不能超过 32KB；
触发器体内的 SELECT 语句只能为 SELECT … INTO …结构，或者为定义游标所使用的 SELECT 语句。
触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
由触发器所调用的过程或函数也不能使用数据库事务控制语句；
问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.实现: :NEW 修饰符访问操作完成后列的值 :OLD 修饰符访问操作完成前列的值

特性	INSERT	UPDATE	DELETE
OLD	NULL	有效	有效
NEW	有效	有效	NULL