oracle sql学习笔记

最新推荐文章于 2022-12-08 16:59:13 发布

Norni

最新推荐文章于 2022-12-08 16:59:13 发布

阅读量284

点赞数

文章标签：数据库 entity sync java pig

本文链接：https://blog.csdn.net/Norni/article/details/115714595

版权

1.oracle数据库体系机构简介
2.SQL语句分类
- 1.sql语句分为以下四种类型
3.SELECT语句
4.过滤和排序数据
5.单行函数
6.多表查询
7.分组函数
8.子查询
9.创建和管理表
10.数据处理
11.约束
12.视图
13.其它数据库对象
14.控制用户权限
15.SET运算符
16.高级子查询
17.分析函数
- 1.分析函数的定义
- 2.理解over()函数
18.PL/SQL基础语法

1.oracle数据库体系机构简介

日常所说的Oracle或Oracle数据库指的是Oracle数据库管理系统
Oracle数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system),它由Oracle数据库和Oracle实例(instance)构成.
Oracle数据库

一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合，这些文件组织在一起，成为一个逻辑整体，即为Oracle数据库
- 说明
  
  Oracle用它来存储和管理相关的信息，Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。
Oracle实例

位于物理内存中的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成，共享的内存池可以被所有进程访问
- Oracle用它们来管理数据库访问
  
  用户如果要存储数据库(也就是硬盘上的文件)里的数据，必须通过Oracle实例才能实现，不能直接读取硬盘上的文件.
- Oracle实例就是平常所说的数据库服务(database service)
Oracle数据库和Oracle实例的区别
1. 实例可以操作数据库
2. 在任何时刻一个实例只能与一个数据库关联，访问一个数据库
3. 同一个数据库可由多个实例访问

2.SQL语句分类

1.sql语句分为以下四种类型

DML(Data Manipulation Language)数据操纵语言

DML供用户实现对数据的追加、删除、更新、查询等操作
- SELECT 从数据库表中获取数据
- UPDATE 更新数据库表中的数据
- DELETE 从数据库表中删除数据
- INSERT INTO 向数据库表中插入数据
DDL(Data Definition Language)数据定义语言

DDL使我们有能力创建或删除表格，也可以定义索引(键),规定表之间的链接，以及施加表间的约束
- CREATE DATABASE 创建新数据库
- ALTER DATABASE 修改数据库
- CREATE TABLE 创建新表
- ALTER TABLE 变更(改变)数据库表
- DROP TABLE 删除表
- CREATE INDEX 创建索引(搜索键)
DCL(Data Control Language)数据控制语言
- GRANT 授权
- REVOKE 撤销授权
- DENY 拒绝授权
- LOCK 对数据库的特定部门进行锁定
TCL(Transaction Control Language)事物控制语言
- SAVEPOINT 设置保存点
- ROLLBACK 回滚
- COMMIT 提交

3.SELECT语句

语法
```
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
```
- SELECT 标识选择哪些列
- FROM 标识从哪个表中选择
说明
- SQL语言大小写不敏感
- SQL可以写在一行或者多行
- 关键字不能被缩写也不能分行
- 各子句一般要分行写
- 使用缩进提高语句的可读性
算数运算符

数字和日期使用的算术运算符

SELECT ENAME,SAL,SAL+10
FROM EMP;

操作符的优先级

乘除的优先级高于加减
同一优先级运算符从左向右执行
括号内的运算先执行
定义空值
- 空值是无效的，未指定的，未知的或不可预知的值
- 空值不是空格或者0
```
SELECT ENAME,SAL,COMM
FROM EMP;
```
- 空值在数学运算中的使用
  
  包含空值的数学表达式的值都为空值
列的别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字‘AS’，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
```
SELECT ENAME "name", SAL AS "salary"
FROM EMP;
```
连接符
- 把列与列，列与字符连接在一起
- 用‘||’表示
- 可以用来‘合成’列
```
SELECT ENAME||"的工资是"||SAL
FROM EMP;
```
字符串
- 字符串可以是SELECT列表中的一个字符，数字，日期
- 日期和字符只能在单引号中出现
- 每当返回一行时，字符串被输出一次
重复行
默认情况下，查询会返回全部行，包括重复行
删除重复行

在SELECT子句中使用关键字‘DISTINCT’删除重复行
```
SELECT DISTINCT DEPTNO
FROM EMP;
```
显示表结构

使用DESCRIBE命令，表示表结构
```
DESC[RIBE] tablename
```

4.过滤和排序数据

过滤

使用WHERE子句，将不满足条件的行过滤掉
WHERE子句紧随FROM子句

SELECT	*|{[DISTINCT] column|expression [alias],...}
FROM	table
[WHERE	condition(s)];

SELECT ENAME,DEPTNO
FROM EMP
WHERE DEPTNO=30;

字符和日期
- 字符和日期要包含在单引号中
- 字符大小写敏感，日期格式敏感
- 默认的日期格式是DD-MON月-RR
```
SELECT ENAME, JOB, DEPTNO
FROM   EMP
WHERE  ENAME = 'Whalen';
```
```
SELECT ENAME, JOB, DEPTNO
FROM   EMP
WHERE  HIREDATE = '7-6月-1994'
```
比较运算符
- 赋值使用:=符号
其他比较运算

BETWEEN…AND…

SELECT ENAME,SAL
FROM EMP
WHERE SAL BETWEEN 2500 ADN 3500;

SELECT EMPNO,ENAME,SAL,MGR
FROM EMP
WHERE MGR IN (7902,7839,7698);

LIKE
- 使用LIKE运算选择类似的值
- 选择条件可以包含字符或数字
- %代表零个或多个字符(任意个字符)
- _代表一个字符
- %和_可以同时使用
```
SELECT ENAME
FROM EMP
WHRER ENAME LIKE 'S%'
```
- ESCAPE
  
  回避特殊符号的：使用转义符
  
  例如：将[%]转为[%]、[_]转为[_]，然后再加上[ESCAPE ‘\’] 即可。
```
SELECT JOB_NAME
FROM JOBS
WHERE JOB_NAME LIKE 'IT\_%' ESCAPE '\';
```

NULL

使用IS (NOT) NULL判断空值

SELECT ENAME,MGR
FROM EMP
WHERE MGR IS NULL;

逻辑运算
- AND要求并的关系为真
- OR要求或关系为真
优先级

使用括号可以改变优先级顺序
ORDER BY子句
- 使用ORDER BY子句排序
  - ASC(ascend)：升序
  - DESC(descend)：降序
- ORDER BY子句在SELECT语句的结尾
- 能够按照ORDER BY列表的顺序排序
```
SEELCT ENAME,DEPTNO,SAL
FROM MEP
ORDER BY DENPNO,SAL DESC;
```
  可以使用不在SELECT列表中的列排序

5.单行函数

定义

单行函数能够：
1. 操作数据对象
2. 接收参数返回一个结果
3. 只对一行进行转换
4. 每行返回一个结果
5. 可以转换数据类型
6. 可以嵌套
7. 参数可以是一列或一个值
function_name[(arg1,arg2,...)]

单行函数有：通用函数，字符函数，数值函数，转换函数，日期函数

字符函数
- 大小写控制函数
  - LOWER() 将字符转换成小写
  - UPPER() 将字符转换成大写
  - INITCAP() 将字符首字母大写
- 字符控制函数
  - CONCAT(x, y)把字符x和y，拼接到一起
```
SELECT CONCAT('Hello', 'World') FROM DUAL;
--->>> HelloWorld
```
  - **SUBSTR(X, START,N) **字符截取函数，把字符x从start开始截取，如果N省略，默认截取到最后一位
```
SELECT SUBSTR('SDASDAD',2,1) FROM DUAL;
```
  - LENGTH() 获取字符长度
  - INSTR(X,Y,START,N)在字符X中从第START个字符开始查找字符Y第N次出现的位置，N省略，则默认是第一次
  - TRIM() 取出左右两边的空格
    - LTRIM()
    - RTRIM()
  - VM_CONCAT(column_name) 该函数可以把列值以‘，’号分隔起来，并显示成一行
  - REPLACE(object,old, new) 将object中的old字符替换为new字符
  - LPAD(str, len, padstr) LPAD(要查询的字段，长度，用来填充的字段)
  - RPAD
```
SELECT LPAD('1234', 10, '*') FROM DUAL;
SELECT RPAD('1234', 10, '*') FROM DUAL;
```
**数字函数 **
- ROUND(x[,fmt]) 四舍五入,fmt不写默认到整数位
- TRUNC(x[,fmt]) 截断，fmt不写默认到整数位
- MOD(x, y) 求余

日期函数

Oracle中的日期型数据实际含有两个值：日期和时间
函数SYSDATE()返回日期和时间
```
SELECT SYSDATE FROM DUAL;
```

日期的数学运算

在日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数

日期不允许做加法运算，无意义
可以用数字除24来向日期中加上或减去天数

其他

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

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
--->>>19.6774194

ADD_MONTHS ('11-JAN-94',6)
--->>>'11-JUL-94'

NEXT_DAY ('01-SEP-95','FRIDAY') 
--->>>'08-SEP-95'

LAST_DAY('01-FEB-95')
--->>>'28-FEB-95'

ROUND('25-JUL-95'，'MONTH')

转换函数
- 隐性
- 显性
  - TO_CHAR
    
    语法：TO_CHAR(date, 'format_model')
    
    格式：1.必须包含在单引号中而且大小写敏感
    
    2.可以包含任意的有效的日期格式
    
    3.日期之间用逗号隔开
    
    用例：SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh:mi:ss') FROM DUAL;
    - 日期格式的元素
```
SELECT ENAME,HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'yyyy-mm-dd')='2045-05-18';
```
    - TO_CHAR函数对数字的转换
      
      TO_CHAR(number,'format_mode')
```
SELECT TO_CHAR(SAL,'￥99，999，00') 
FROM EMP
WHERE ENAME='Ernst'
```
  - TO_DATE函数对字符的转换
    - 使用TO_DATE
      
      TO_DATE(char[,'format_model'])
    - 使用TO_DATE函数将字符转换成数字
  - TO_NUMBER函数对字符的转换
    
    使用TO_NUMBER函数将字符转换成日期
    
    TO_NUMBER(char[,'format_model'])
    
    使用TO_NUMBER
    
    TO_NUMBER('$1,123,567,890.00','L999,999,999,999.99') FROM DUAL;
通用函数
- NVL(expr1, expr2)
  
  将空值转换成一个已知的值，可以使用的数据类型有日期、字符、数字
- NVL2(expr1,expr2,expr3)
  
  expr1不为NULL,返回expr2；为NULL,返回expr3
- NULLIF(expr1,expr2,…,exprn)
  
  相等返回NULL,不等返回expr1
- COALESCE函数
  
  COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值
  
  如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE

条件表达式

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])

伪列
- ROWID 插入数据的时候生成,记录的是该行的物理地址(用作去重)
- ROWNUM 查询数据的时候生成,返回的是序号(用作分页)
  
  ROWNUM 查询只能是小于或小于等于某个值(不能直接等于或者大于或者不是从1开始的某个区间段)

6.多表查询

笛卡尔积

笛卡尔积会在下面条件下产生：
1. 省略连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接
为避免笛卡尔积，可以在WHERE加入有效的连接条件
使用连接在多个表中查询数据
```
SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;
```
在WHERE子句中写入连接条件

在表中有相同列时，在列名之前加上表名前缀

使用表名前缀在多个表中区分相同的列

在不同表中具有相同列名的列可以用表的别名加以区分
连接多个表

连接n个表，至少需要n-1个连接条件
全外连接|左外连接,右外连接，内连接
```
SELECT	table1.column, table2.column
FROM	table1
FULL|LEFT|RIGHT|INNER JOIN table2
ON table1.column1=table2.column2
```
- 内连接
  
  合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接
  
  两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ，这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(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(+);
```
叉集

使用CROSS JOIN子句使连接的表产生叉集

叉集和笛卡尔集是相同的
```
SELECT ENAME,DNAME
FROM MEP
CROSS JOIN DEPT;
```

7.分组函数

定义

分组函数作用于一组数据，并对一组数据返回一个值。

语法

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

组函数类型
- AVG 求平均值
- **COUNT **
  
  COUNT(*)返回表中记录总数，适用于任意数据类型
- MAX
- MIN
- STDDEV
- SUM
组函数于空值

组函数忽略空值

在组函数中使用NVL函数，NVL函数使分组函数无法忽略空值

SELECT AVG(NVL(COMM,0)) FROM EMP;

COUNT(DISTINCT expr) 返回expr非空且不重复的记录总数

SELECT COUNT(DISTINCT DEPTNO) FROM EMP
分组数据

GROUP BY子句将表中的数据分成若干组
```
SELECT	column, group_function(column)
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[ORDER BY	column];
--WHERE一定放在FROM后面
```
- 说明
  1. 包含在GROUP BY子句中的列不必包含在SELECT列表中
  2. 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
  3. 所有包含于SELECT列表中，而未包含于组函数中的列都必须包含于GROUP BY子句中
  4. 不能在WHERE子句中使用组函数
  5. 可以在HAVING子句中使用组函数
过滤分组：HAVING子句
- 使用HAVING过滤分组的前提：
  1. 行已经被分组
  2. 使用了组函数
  3. 满足HAVING子句中条件的分组将被显示
```
SELECT	column, group_function
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[HAVING	group_condition]
[ORDER BY	column];
```

8.子查询

子查询语法
- 子查询(内查询)在主查询之前一次执行完成
- 子查询的结果被主查询(外查询)使用
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
子查询中的空值问题

当子查询未空值时，子查询不返回任何行。

多行子查询

返回多行
使用多行比较操作符

--题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';


--题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

9.创建和管理表

常见的数据库对象

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

Oracle数据库中的表
- 用户定义的表
  - 用户自己创建并维护的一组表
  - 包含了用户所需的信息
  SELECT * FROM USER_TABLES;查看用户创建的表
- 数据字典
  - 由oracle server自动创建的一组表
  - 包含数据库信息
查询数据字典
- 查看用户定义的表
  
  SELECT * 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]][,...]);

CREATE TABLE DEPT(
	DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCAHR2(13)
)

添加字段注释

通过comment属性来添加字段或列的注释
- 给表添加注释
  
  COMMENT ON TABLE table_name is ‘表的注释内容’
- 给表的字段添加注释
  
  COMMENT ON COLUMN table_name.field_name is ‘字段注释’
数据类型
使用子查询创建表
- 使用AS subquery选项，将创建表和插入数据结合起来
- 指定的列和子查询中的列要一一对应
- 通过列名和默认值定义列
```
CREATE TABLE TABLE
	[(column, column...)]
AS subquery;
```
```
CREATE TABLE EMP1 AS SELECT * FROM EMP;
CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;
--创建的emp2是空表
```

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 EMP
MODIFY (SAL NUMBER(9,2) DEFAULT 1000);
```

删除一个列

ALTER TABLE table
DROP COLUMN column_name;

重命名表的一个列名

ALTER TABLE table
RENAME COLUMN old_column_name TO new_column_name

删除表
- 数据和结构都被删除
- 所有正在运行的相关事务被提交
- 所有相关索引被删除
- DROP TABLE语句不能回滚
```
DROP TABLE table_name;
```
清空表
- 删除表中所有的数据，保留表结构
- 释放表的存储空间
- TRUNCATE语句不能回滚
```
TRUNCATE TABLE DEPT;
```
改变对象的名称
- 执行RENAME语句改变表，视图，序列，或同义词的名称
- 必须是对象的拥有者
```
RENAME DEPT TO DEPT_1;
```

10.数据处理

DML(数据操纵语言)可以在下列条件下执行：
- 向表中插入数据
- 修改现存数据
- 删除现存数据
事务是由完成若干项工作的DML语句组成
INSERT语句语法
- 使用INSERT语句向表中插入数据
- 使用这种语法一次只能向表中插入一条数据
```
INSERT INTO table[(column [, column...])]
VALUES (value [, value...]);
```
插入数据
- 为每一列添加一个新值
- 按列的默认顺序列出各个列的值
- 字符和日期型数据应包含在单引号中
向表中插入空值
- 隐式方式：在列名表中省略该列的值
```
INSERT INTO DEPT (DEPTNO,EDEPT)
VALUES (30,'Purchasing');
```
- 显示方式：在VALUES子句中指定空值
```
INSERT INTO DEPT
VALUES (100,'Finance'，NULL, NULL);
```

插入指定的值

--SYSDATE记录当前系统的日期和时间
INSERT INTO EMP(HIREDATE)
VALUES(SYSDATE)

INSERT ALL…、INSERT FIRST…

INSERT ALL
WHEN DEPTNO<20 THEN--相当于复制
  INTO EMP1
  WHEN DEPTNO<40 THEN --取小于40的所有的
    INTO EMP2
    SELECT * FROM EMP
    ------------------------------
INSERT FIRST 
WHEN DEPTNO<20 THEN  相当于剪切走小于20的  剩下的表只能从>=20的里面取数据
INTO EMP1
WHEN DEPTNO<40 THEN--只能取20~40之间的
    INTO EMP2
    SELECT * FROM EMP

创建脚本
- 在SQL语句中使用‘&’变量指定列值
- ‘&’变量放在VALUES子句中
```
INSERT INTO DEPT (DEPTNO,DNAME)
VALUES (&DEPTNO,&DNAME);
```
从其他表中拷贝数据
- 不必书写VALUES子句
- 子查询中的值列表应与INSERT子句中的列名对应
```
INSERT INTO EMP1(ID,NAME,SAL,COMM)
SELECT ID,NAME,SAL,COMM FROM EMP
FROM EMP
WHERE JOB LIKE '%REP%';
```
UPDATE语句语法
- 使用UPDATE语句更新数据
- 可以一次更新多条数据
- 使用WHERE子句指定需要更新的数据
- 如果省略WHERE子句，则表中的所有数据都将被更新
```
UPDATE EMP
SET SAL=7000
[WHERE EMPNO=122];
```
在UPDATE语句中使用子查询
删除数据

使用DELETE语句从表中删除数据
- 使用WHERE子句删除指定的记录
- 如果省略WHERE子句，则表中的全部数据都将被删除
```
DELETE FROM table
[WHERE condition];
```
```
DELETE FROM DEPT
WHERE DNAME='Finance';
```
数据库事物
- 事务：一组逻辑操作单元，使数据从一种状态变换成另一种状态
- 数据库事务由以下的部分组成
  1. 一个或多个DML语句
  2. 一个DDL语句
  3. 一个DCL语句
- 以第一个DML语句的执行作为开始
- 以下面其中之一作为结束
  - COMMIT或ROLLBACK语句
  - DDL语句(自动提交)
  - 用户绘画正常结束
  - 系统异常终止
COMMIT和ROLLBACK语句的优点
1. 确保数据完整性
2. 数据改变被提交之间预览
3. 将逻辑上相关的操作分组
控制事务
回滚到保留点
- 使用SAVEPOINT语句在当前事务中创建保存点
- 使用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点
```
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
```
事务进程
- 自动提交在以下情况中执行：
  - DDL语句
  - DCL语句
  - 不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话
- 会话异常结束或系统异常会导致自动回滚
提交或回滚前的数据状态
- 改变前的数据状态时可以恢复的
- 执行DML操作的用户可以通过SELECT语句查询之前的修正
- 其他用户不能看到当前用户所作的改变，直到当前用户结束事务
- DML语句所涉及到的行被锁定，其他用户不能操作
提交后的数据状态
- 数据的改变已经保存到数据库中
- 改变前的数据已经丢失
- 所有用户可以看到结果
- 锁被释放，其他用户可以操作涉及到的数据
- 所有保存点被释放
提交数据
- 通过insert或update改变数据
- 通过commit提交数据
数据回滚后的状态
- 使用ROLLBACK语句可使数据变化失效
  - 数据改变被取消
  - 修改前的数据状态被恢复
  - 锁被释放

11.约束

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

定义约束

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

CREATE TABLE EMP(
		EMPNO NUMBER(6),
		ENAME VARCHAR2(20),
		...
		JOBID VARCHAR2(10) NOT NULL,
    	CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPNO)
)

列级

column [CONSTRAINT constraint_name] constraint_type,

表级

column,...
	[CONSTRAINT constraint_name] constraint_type
	(column, ...),

NOT NULL约束(tablename_columnname_nn)
- 保证列值不能为空
- 只能定义在列级
UNIQUE约束(tablename_columnname_uk)
- 唯一约束，允许出现多个空值：NULL
- 可以定义在表级或列级
PRIMARY KEY约束(tablename_columnname_pk)
- 不允许空值
- 可以定义在表级或列级
FOREIGN KEY约束(subtablename_maintablename_fk)
- 在主表中，该键值必须存在
- 可以定义在表级或列级
- FOREIGN KEY:在表级指定子表中的列
- REFERENCES:标示在父表中的列
- ON DELETE CASCADE(级联删除)：当父表中的列被删除时，子表中相对应的列也被删除
- ON DELETE SET NULL(级联置空)：当父表中的列被删除时，子表中相应的列置空
```
CREATE TABLE EMP(
	EMPNO NUMBER(6),
    ENAME VARCHAR2(25) NOT NULL,
    EMAIL VARHCAR2(25),
    HIREDATE DATE NOT NULL,
    DEPTNO NUMEBR(4),
    CONSTRAINT EMP_DEPT_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO),
    CONSTRAINT EMP_EMIAL_UK UNIQUE(EMAIL)
);
```

CHECK约束

定义每一行必须要满足的条件

...,SAL NUMBER(2),
CONSTRAINT EMP_SAL_CK CHECK(SAL>0),...

添加约束的语法

使用ALTER TABLE语句：

添加或删除约束，但是不能修改约束
有效化或无效化约束
添加NOT NULL约束要使用MODIFY语句

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

ALTER TABLE EMP MODIFY (ENAME VARCHAR2(30) NOT NULL)

ALTER TABLE EMP
ADD CONSTARIN EMP_MGR_FK
	FOREIGN KEY(MRG)
	REFERENCES EMP(EMPNO)''

删除约束

ALTER TABLE EMP
DROP CONSTARINT EMP_MGR_FK;

无效化约束

在ALTER TABLE 语句中使用DISABLE子句将约束无效化
```
ALTER TABLE EMP
DISABLE CONSTRAINT EMP_EMP_ID_PK;
```
激活约束
- ENABLE子句可将当前无效的约束激活
- 当定义或激活UNIQUE或PRIMARY KEY约束时系统会自动创建UNIQUE或PRIMARY KEY索引
```
ALTER TABLE EMP
ENABLE CONSTARINT EMP_EMP_ID_PK;
```

查询约束

查询数据字典视图USER_CONSTRAINTS

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

查询定义约束的列

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

12.视图

视图的定义
- 视图是指从表中抽出的逻辑上相关的数据集合
- 视图是一种虚表
- 视图建立在已有表的基础上，视图赖以建立的这些表称为基表
- 向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
- 视图向用户提供基表数据的另一种表现形式
视图的优点
- 控制数据访问
- 简化查询
- 避免重复访问相同的数据
创建视图
- 在CREATE VIEW语句中嵌入子查询
```
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
```
- 描述视图结构
  
  DESCRIBE view_new_create
- 创建视图时在子查询中给列定义别名
- 在选择视图中的列时应使用别名
查询视图
```
SELECT * FROM view_new_create
```

修改视图

--使用CREATE OR REPLACE VIEW 子句 修改视图

CREATE OR REPLACE VIEW empvu80
  (id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
   FROM    employees
   WHERE   department_id = 80;

CREATE VIEW子句中各列的别名应和子查询中各列相对应

视图中使用DML的规定
- 可以在简单视图中执行DML操作
- 当视图定义中包含以下元素之一时不能使用delete:
  - 组函数
  - GROUP BY 子句
  - DISTINCT 关键字
  - ROWNUM 伪列
- 当视图定义中包含以下元素之一时不能使用update:
  - 组函数
  - GROUP BY子句
  - DISTINCT关键字
  - ROWNUM伪列
  - 列的定义为表达式
- 当视图定义中包含以下元素之一时不能使用insert:
  - 组函数
  - GROUP BY子句
  - DISTINCT关键字
  - ROWNUM伪列
  - 列的定义为表达式
  - 表中非空的列在视图定义中未包括
- 屏蔽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分析：
```
SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;
```
注意:对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。

13.其它数据库对象

序列
- 定义：可供多个用户用来产生唯一数值的数据库对象
- 作用：
  - 自动提供唯一的数值
  - 共享对象
  - 主要用于提供主键值
  - 将序列值装入内存可以提高访问效率
- CREATE SEQUENCE语句
```
--定义序列
CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录
```
```
--创建序列 DEPT_DEPTID_SEQ为表DEPT提供主键
--不使用CYCLE选项
CREATE SEQUENCE DEPT_DEPTID_SEQ
				INCREMENT BY 10
				START WITH 120
				MAXVALUE 9999
				NOCACHE
				NOCYCLE;
```
- 查询序列
  - 查询数据字典视图USER_SEQUENCES获取序列定义信息
```
SELECT sequence_name,min_value,max_value,increment_by,last_number
FROM user_sequences;
--如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值
```
- 序列有两个属性
  - sequence_name.currval
    
    currval中存放序列的当前值
  - sequence_name.nextval
    
    nextval返回序列中下一个有效的值，任何用户都可以引用
  currval要使用，必须先使用nextval,这样currval才会有值，否则会报currval尚未在此会话中定义的错误
- 使用序列
  - 将序列值装入内存可提高访问效率
  - 序列在下列情况下出现裂缝
    - 回滚
    - 系统异常
    - 多个表同时使用同一序列
  - 如果不将序列的值装入内存(NOCACHE)，可使用表USER_SEQUENCES查看序列当前的有效值
- 修改序列
  
  修改序列的增量，最大值，最小值，循环选项，或是否装入内存
```
ALTER SEQUENCE DEPT_DEPTID_SEQ
			INCREMENT BY 20
			MAXVALUE 999999
			NOCACHE
			NOCYCLE;
```
- 修改序列的注意事项
  - 必须是序列的拥有者或对序列有ALTER权限
  - 只有将来的序列值会被改变
  - 改变序列值的初始值只能通过删除序列之后重建序列的方式实现
- 删除序列
  - 使用DROP SEQUENCE语句删除序列
  - 删除之后，序列不能再次被引用
```
DROP SEQUENCE DEPT_DEPTID_SEQ;
```
索引
- 索引定义
  - 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
  - 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
  - 索引一旦建立，Oracle管理系统会对其进行自动维护，而且有Oracle管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
  - 在删除一个表时，所有基于该表的索引会自动被删除
  - 通过指针加速Oracle服务器的查询速度
  - 通过快速定位数据的方法，减少磁盘I/O
- 创建索引
  - 自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引
  - 手动创建：用户可以在其他列上创建非唯一的索引，以加速查询
    - 在一个或多个列上创建索引
```
CREATE INDEX index
ON table (column[, column]...);
```
    - 在表EMP的列ENAME上创建索引
```
CREATE INDEX emp_ename_idx
ON EMP(ENAME)
```
- 创建索引的时机
  - 列中数据值分布范围很广
  - 列经常在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 INDEX index;
```
  - 删除索引UPPER_LAST_NAME_IDX
```
DROP INDEX UPPER_LAST_NAME_IDX;
```
  - 只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引
  - 删除操作时不可回滚的
同义词——synonym
- 使用同义词访问相同的对象
  - 方便访问其他用户的对象
  - 缩短对象名字的长度
```
CREATE [PUBLIC] SYNONYM synonym
FOR object;
```
```
CREATE SYNONYM E FOR EMP;
SELECT * FROM E;
```
- 创建和删除同义词
  - 为视图DEPT_SUM_VU创建同义词
```
CREATE SYNONYM D_SUM
FOR DEPT_SUM_VU;
```
  - 删除同义词
```
DROP SYNONYM D_SUM;
```

14.控制用户权限

权限
- 数据库安全性：
  - 系统安全性
  - 数据安全性
- 系统权限：对于数据库的权限
  - 超过一百多有效的权限
  - 数据库管理员具有高级权限以完成管理任务，例如--创建新用户，--删除用户，--删除表，--备份表…
- 对象权限：操作数据库对象的权限

创建用户

DBA使用CREATE USER语句创建用户

CREATE USER user
IDENTIFIED BY passsword;

CREATE USER scott
IDENTIFIED BY tiger;

用户的系统权限
- 用户创建之后，DBA会赋予用户一些系统权限
```
GRANT privilege [,privilege]
TO user [,user| role, PUBLIC...];
```
- 以应用程序开发者为例，一般具有下列系统权限：
  - CREATE SESSION(创建会话)
  - CREATE TABLE(创建表)
  - CREATE SEQUENCE(创建序列)
  - CREATE VIEW(创建视图)
  - CREATE PROCEDURE(创建过程)
- 赋予系统权限
```
GRANT create session,create table,
	  create sequence,create view
TO scott;
```
创建用户表空间

用户拥有create table权限之外，还需要分配相应的表空间才可以开辟存储空间用于创建的表
```
ALTER USER scott QUOTA UNLIMITED
ON users;
```
角色
- 创建角色并赋予权限
  - 创建角色
```
CREATE ROLE manager;
```
  - 为角色赋予权限
```
GRANT create table,create view
TO manager;
```
  - 将角色赋予用户
```
GRANT manager TO scott;
```
修改密码
- DBA可以创建用户和修改密码
- 用户本人可以使用ALTER USER语句修改密码
```
ALTER USER scott
IDENTIFIED BY lion;
```
对象权限
- 不同的对象具有不同的对象权限
- 对象拥有者拥有所有权限
- 对象的拥有者可以向外分配权限
```
GRANT object_priv[(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
```
分配对象权限
- 分配表EMP的查询权限
```
GRANT select
ON EMP
TO sue,rich;
```
- 分配表中各个列的更新权限
```
GRANT update
ON scott.DEPT
TO my_user;
```
WITH GRANT OPTION和PUBLIC关键字
- WITH GRANT OPTION使用户同样具有分配权限的权力
```
GRANT select,insert
ON DEPT
TO scott
WITH GRANT OPTION;
```
- 向数据库中所有用户分配权限
```
GRANT select
ON alice.DEPT
TO PUBLIC;
```
查询权限分配情况

收回对象权限

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

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

REVOKE select,insert
ON DEPT
FROM scott;

15.SET运算符

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

16.高级子查询

子查询

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

多列子查询

主查询与子查询返回的多个列进行比较

成对比较

--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id  

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

在FROM子句中使用子查询

--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资


-- 方法一
select last_name,department_id,salary,
(select avg(salary)from employees e3 
where e1.department_id = e3.department_id 
group by department_id) avg_salary
from employees e1
where salary > 
         (select avg(salary)
          from employees e2  
          where e1.department_id = e2.department_id
          group by department_id
          )
          
          
--方法二
SELECT  a.last_name, a.salary, 
        a.department_id, b.salavg
FROM    employees a, (SELECT   department_id, 
                      AVG(salary) salavg
                      FROM     employees
                      GROUP BY department_id) b
WHERE   a.department_id = b.department_id
AND     a.salary > b.salavg;

单列子查询表达式

单列子查询表达式是在一行中只返回一列的子查询

17.分析函数

1.分析函数的定义

分析函数用于计算基于组的某种聚合值，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一个组的每一行都可以返回一个统计值

分析函数和聚合函数的不同之处
- 普通的聚合函数用GROUP BY分组，每个分组返回一个统计值
- 分析函数采用PARTITION BY分组，并且每组每行都可以返回一个统计值
分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句：
- 分组(partition by)
- 排序(order by)
- 窗口(rows)
分析函数的使用形式：over(partition by ... order by ... rows between...and...)

2.理解over()函数

2.1 两个order by的执行时机

分析函数(以及与其配合的开窗函数over())是在整个SQL查询结束之后在进行的操作，SQL语句中的order by的执行比较特殊，所以SQL语句中order by会影响分析函数的执行结果：

分析函数中的order by 和SQL语句中的order by，如果两者的内容一样，即两者对数据分析时要求的排序一致，那么SQL语句中的排序将先执行，分析函数在分析时就不必再排序了。
如果不一致，那么分析函数执行完毕后，SQL语句中的order by开始执行排序。

2.2 开窗函数over()中的分组/排序/窗口

开窗函数over()包含三个分析子句：分组子句(partition by)，排序子句(order by)，窗口子句(rows)

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

窗口子句

窗口作为分析函数分析时要处理的数据范围，以SUM()函数为例，它作为SUM()函数窗口中的记录而不是整个分组中的记录。

如果想要得到某个栏位的累计值，需要把窗口指定到该分组中的第一行数据到当前行。

当指定的窗口从该分组中的第一行到最后一行，那么该组中的每个SUM值都会一样，即为整个组的总和。

窗口子句中经常用到指定第一行，当前行，最后一行这样的三个属性：
- 第一行 unbounded preceding
- 当前行 current row
- 最后一行 unbounded following
注意
- 当开窗函数over()出现分组(partition by)子句时，unbounded preceding即第一行是指表中一个分组里的第一行， unbounded following即最后一行是指表中一个分组里的最后一行
- 当开窗函数over()省略了分组(partition by)子句时， unbounded preceding即第一行是指表中的第一行， unbounded following即最后一行是指表中的最后一行
- 窗口子句不能单独出现，必须有order by子句时才能出现

2.3 常见分析函数详解

first_value()与last_value()：求最值对应的其他属性
rank(),dense_rank()与row_number()：求排序

rank、dense_rank、row_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数

这3个函数的唯一区别在于当碰到相同数据时的排名策略
- row_number:
  
  row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
- dense_rank
  
  dense_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
- rank
  
  rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
lag()与lead()：求之前或之后的第N行

lag()和lead()函数可以在一次查询中取出同一字段的前N行的数据和后N行的数据，比使用对相同表的表连接来实现，有用更高的效率。
- lag(exp_str,offset,defval)
  - 第一个参数是列名，即要做对比的字段
  - 第二个参数是偏移的offset,如果没有显式指定，默认为1
  - 第三个参数是超出记录窗口时的默认值，如果在LAG()函数中没有显式设置default,defval参数的默认值为空值NULL,函数返回NULL
  - lag()函数的返回值为在表中从当前行位置向前数N行的那一行上exp_str字段的值,当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值
- lead(exp_str,offset,defval)
max(),min(),sum(),avg():求最大值，最小值，总和，平均值
rollup()与cube()：排列组合分组
- group by rollup(a, b, c)：
  
  首先会对(a、b、c)进行group by，
  
  然后再对(a、b)进行group by，
  
  其后再对(a)进行group by，
  
  最后对全表进行汇总操作。
- group by cube(a, b, c)：
  
  则首先会对(a、b、c)进行group by，
  
  然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
  
  最后对全表进行汇总操作。
Nulls first和nulls last
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
使用语法如下：
--将nulls始终放在最前
select * from zl_cbqc order by cb_ld nulls first

--将nulls始终放在最后
select * from zl_cbqc order by cb_ld desc nulls last

2.4.应用

分析函数语法格式

FUNCTION_NAME(<参数>,…) OVER (<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC DESC> )

应用

跟聚合函数一起用

MAX/MIN/SUM/AVG/COUNT --有ORDER BY 不仅仅是排序 ,还具有累计求和的功能

SELECT E.*,AVG(SAL) OVER (PARTITION BY DEPTNO) 平均工资
FROM EMO E;

SELECT E.* AVG(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL)
FROM EMP E;

--每个部门的总工资
SELECT E.*,SUM(SAL) OVER (PARTITION BY DEPTNO)
FROM EMP E;
--每个部门的累计工资
SELECT E.*,SUM(SAL) OVER (APRTITION BY DEPTNO ORDER BY SAL)
FROM EMP E;

跟排序函数一起用

ROW_NUMBER()   1 2 3 4..
DENSE_RANK()   1 2 2 3 4...
RANK()         1 2 2 4 5...

SELECT E.ENAME,E.SAL,ROW_NUMBER()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E;

SELECT E.ENAME,E.SAL,DENSE_RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E;

SELECT E.ENAME,E.SAL,RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E;

跟位移函数一起用--->>>一般用来求同比环比

SELECT E.ENAME,E.HIREDATE,LEAD(E.HIREDATE,1)OVER(ORDER BY E.HIREDATE)  FROM EMP E;

SELECT E.ENAME,E.HIREDATE,LEAD(E.HIREDATE,1,DATE'1980-12-17')OVER(ORDER BY E.HIREDATE)  FROM EMP E;

SELECT E.ENAME,E.HIREDATE,
LEAD(E.HIREDATE,1)OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE)  FROM EMP E;

同其他排序函数一起使用

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);--总计

SELECT  ENAME,COMM FROM EMP ORDER BY COMM NULLS FIRST;
SELECT  ENAME,COMM FROM EMP ORDER BY COMM DESC NULLS LAST;

18.PL/SQL基础语法

1.PL/SQL基础语法结构

PL/SQL语法结构

DECLARE
     --declaration statements]  --声明部分:变量  常量 游标  如果不需要声明  可以省略
BEGIN
     --executable statements  --增删改查
END;

--输出一个 HELLO WORLD 

BEGIN
  DBMS_OUTPUT.put_line('HELLO WORLD');
END;
-----------
DECLARE
V_SQL VARCHAR2(200):='HELLO WORLD';
BEGIN
  DBMS_OUTPUT.put_line(V_SQL);
END;

DBMS_OUTPUT.put_line() 输出内容

v_name emp.ename%type; 复制表中某个字段的数据类型

SELECT ... INTO ... FROM 将查询到的数据赋值给变量

V_EMP EMP%ROWTYPE; 把整张表的字段和数量类型复制给变量

SELECT ... INTO ... FROM 表 ---单行查询
--输入一个员工编号，打印出其姓名
DECLARE
V_ENAME EMP.ENAME%TYPE;--复制表中某个字段的数据类型
BEGIN
  SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=7369;
  DBMS_OUTPUT.put_line(V_ENAME);
END;

DECLARE
V_EMP EMP%ROWTYPE;--把整张表的字段和数量类型复制给变量
BEGIN
  SELECT ENAME,JOB INTO V_EMP.ENAME,V_EMP.JOB FROM EMP WHERE EMPNO=7369;
   DBMS_OUTPUT.put_line(V_EMP.ENAME||'--'||V_EMP.JOB);
END;

建议的命名方法

标识符	命名规则	例子
程序变量	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

2.判断

IF 条件 THEN
     --条件结构体
END IF;	
---------------------


IF 条件 THEN
     --条件成立结构体
ELSE
     --条件不成立结构体
END IF;
---------
IF 条件1 THEN 
    ....
    ELSIF 条件2 THEN 
    ....
    ELSIF 条件3 THEN 
    ....
    ELSE--    如果前面都列举完了，可以不写ELSE
      ...
END IF;

3.循环

LOOP     无条件循环--死循环，必须加一个退出条件  EXIT WHEN .../IF .. THEN EXIT;
WHILE    当满足条件的时候才执行
FOR      
---------------------------
--输出15行 ‘HELLO WORLD’
--LOOP
DECLARE
   V_SQL VARCHAR2(30):='HELLO WORLD';
   V_NUM VARCHAR2(10):=0;
BEGIN
   LOOP   
       V_NUM:=V_NUM+1;
       DBMS_OUTPUT.put_line(V_NUM||V_SQL);
       EXIT WHEN V_NUM=15;--跳出循环
   END LOOP;
END ;


DECLARE
   V_SQL VARCHAR2(30):='HELLO WORLD';
   V_NUM VARCHAR2(10):=0;
BEGIN
   LOOP   
       V_NUM:=V_NUM+1;
       DBMS_OUTPUT.put_line(V_NUM||V_SQL);
       IF V_NUM=15 THEN 
         EXIT;
       END IF;
       --跳出循环
   END LOOP;
END ;
--WHILE 
DECLARE
  V_SQL VARCHAR2(30) := 'HELLO WORLD';
  V_NUM VARCHAR2(10) := 0;
BEGIN
  WHILE V_NUM < 15 LOOP
    V_NUM := V_NUM + 1;
    DBMS_OUTPUT.put_line(V_NUM || V_SQL);
  END LOOP;
END;


--------------
DECLARE
  V_SQL VARCHAR2(30) := 'HELLO WORLD';
  V_NUM VARCHAR2(10) := 0;
BEGIN
  WHILE V_NUM < 15 LOOP
    V_NUM := V_NUM + 1;
    EXIT WHEN V_NUM=10;--强制退出
    DBMS_OUTPUT.put_line(V_NUM || V_SQL);
  END LOOP;
END;
-------------
--FOR 
DECLARE
  V_SQL VARCHAR2(30) := 'HELLO WORLD';
BEGIN
  FOR I IN 1..15 LOOP
    DBMS_OUTPUT.put_line(I|| V_SQL);
  END LOOP;
END;

标号和GOTO

说明

PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思

语法

GOTO label; /*跳转*/
... ...
<<label>>  /*标号是用<< >>括起来的标识符*/

DECLARE
 V_counter NUMBER := 1;
BEGIN
 LOOP
 DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);
V_counter := v_counter + 1;
IF v_counter > 10 THEN
 GOTO l_ENDofLOOP;
END IF;
 END LOOP;
<<l_ENDofLOOP>>
 DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);
END ;

4.记录类型

记录类型的定义

记录类型是把逻辑相关的数据作为一个单元存储起来。称作PL/SQL RECORD 的域(FIELD)
记录类型的作用

存放互补相同但逻辑相关的信息

记录类型语法

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

用法

DECLARE
--声明一个记录类型
	TYPE emp_record IS RECORD(
    	v_sal EMP.SAL%TYPE,
        V_email EMP.EMAIL%TYPE
    )
--定义一个记录类型的成员变量
	v_emp_record emp_record;
BEGIN
	SELECT SAL,EMAIL INTO v_emp_record FROM EMP WHERE EMPNP='100'
	DBMS.OUTPUT.PUL_LINE(v_emp_record.v_sal,v_emp_record.v_email)
END;

%TYPE和%ROWTYPE
- %TYPE
  - 含义
    
    定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这是可以使用%TYPE
  - 优点
    - 所引用的数据库列的数据类型可以不必知道
    - 所引用的数据列的数据类型可以实时改变
- %ROWTYPE
  - 含义
    
    PL/SQL提供%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致
  - 优点
    - 所引用的数据库中的个数和数据类型可以不必知道
    - 所引用的数据库中列的个数和数据类型可以实时改变
```
DECLARE
	v_emp EMP%ROWTYPE;
BEGIN
	SELECT * INTO v_emp FROM EMP WHERE EMPNO=200;
	DBMS_OUTPUT.PUT_LINE(v_emp.*)
END;
```

5.游标

游标的定义

游标是一个指向上下文的句柄(handle)或指针。
处理显示游标
- 方式一(四个步骤)
  - 定义游标
    
    定义一个游标名，以及与其相对应的SELECT语句
    
    格式：CURSOR cursor_name[(parameter[,paramete]...)] IS SELECT_STATEMENT;
  - 打开游标
    
    执行游标所对应的SELECT语句，将其查询结果放入工作，并且指针指向工作区的首部，标识游标结果集合。
    
    格式:OPEN cursor_name
    
    说明：程序不能用OPEN语句重复打开一个游标
  - 提取游标数据
    
    检索结果集合中的数据行，放入制定的输出变量中
    
    格式：FETCH cursor_name INTO {variable_list|record_variable}
  - 处理数据，直到活动集合中没有记录
  - 关闭游标
    
    当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中的数据。
```
DECLARE
  CURSOR EMP_CURSOR IS SELECT EMP.ENAME,EMP.SAL FROM EMP WHERE EMP.JOB='MANAGER';
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN 
  OPEN EMP_CURSOR;
  LOOP
    FETCH EMP_CURSOR INTO V_ENAME,V_SAL;
    EXIT WHEN EMP_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_ENAME||'--'||V_SAL);
  END LOOP;
  CLOSE EMP_CURSOR;
END;
```
- 方式二(FOR X IN CURSOR_NAME LOOP...END LOOP)
  
  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循环语句中的索引变量来访问这些列数据。
```
DECLARE
  CURSOR EMP_CURSOR IS SELECT EMP.ENAME,EMP.SAL FROM EMP WHERE EMP.JOB='MANAGER';
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN 
  FOR X IN EMP_CURSOR LOOP 
    DBMS_OUTPUT.PUT_LINE(X.ENAME||'--->>>'||X.SAL);
  END LOOP;
END;
```
处理隐式游标
- 对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标成为隐式游标。
- 隐式游标的名字为SQL，格式调用为：SQL%
- 对于隐式游标的操作，如定义，打开，取值及关闭操作，由ORACLE系统自动地完成，无需用户进行处理
- 用户只能通过隐式游标的相关属性，来完成相应的操作。
隐式游标属性
- SQL%FOUND
  
  布尔型属性，当最近一次读记录时成功返回，则值为TRUE
- SQL%NOTFOUND
  
  布尔值属性，与%FOUND相反
- SQL%ROWCOUNT
  
  字符型属性，返回已从游标中读取的记录数
- SQL%ISOPEN
  
  布尔型属性，取值总是FALSE
  
  SQL命令执行完毕立即关闭隐式游标
游标修改和删除操作
- 定义
  
  游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。
- 说明
  - 要求游标查询语句中必须使用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
```

6.异常错误处理

ORACLE提供异常情况(EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理

1.异常处理概念

异常情况处理(EXCEPTION)是用来处理正常执行过程中来预料的事件，程序块的异常处理，处理预定义的错误和自定义错误

由于PL/SQL程序块一旦产生异常而没有指出如何处理时，程序就会自动终止整个程序运行

三种类型的异常错误：

预定于(Predefined)错误

ORACLE预定义的异常情况大约有24个，对这种异常情况的处理，无需在程序中定义，由ORACLE自定将其引发
非预定义错误

即其它标准的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;

2.预定义的异常处理

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

3.非预定义的异常处理

对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。

步骤
1. 在PL/SQL块的定义部分定义异常情况
  
  <异常情况> EXCEPTION;
2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用PRAGMA EXCEPTION_INIT语句：
  
  PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);
3. 在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

4.用户自定义的异常处理

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。

用户定义的异常错误是通过显式使用RAISE语句来触发

当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码

步骤
1. 在PL/SQL块的定义部分定义异常情况
  
  <异常情况> EXCEPTION;
2. RAISE<异常情况>；
3. 在PL/SQL块的异常情况处理部分对异常情况做出相应的处理
在PL/SQL中使用SQLCODE,SQLERRM

SQLCODE 返回错误代码数字

SQLERRM 返回错误信息

!

7.存储函数(有返回值)、存储过程(没有返回值)

1.定义

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

2.创建函数

建立内嵌函数

语法

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
因为函数需要返回一个值，所以RETURN包含返回结果的数据类型

CREATE OR REPLACE FUNCTION GET_SAL(
  DEPT_NO EMP.DEPTNO%TYPE,
  TOTAL_COUNT OUT NUMBER
)
  RETURN NUMBER 
  IS
  -- 函数使用过程中，需要声明的变量，记录类型，cursor
      V_SAL NUMBER(10):=0;
      CURSOR SAL_CURSOR IS SELECT SAL FROM EMP WHERE DEPTNO=DEPT_NO;
BEGIN
  -- 函数的执行体
  TOTAL_COUNT := 0;
  FOR C IN SAL_CURSOR LOOP
    V_SAL := V_SAL+C.SAL;
    TOTAL_COUNT := TOTAL_COUNT+1;
  END LOOP;
  RETURN V_SAL;
EXCEPTION
  -- 处理函数执行过程中的异常
END;


DECLARE
  V_COUNT NUMBER(10):=0;
  V_SAL NUMBER(10);
BEGIN
  V_SAL := GET_SAL(20,V_COUNT);
  DBMS_OUTPUT.PUT_LINE(V_SAL);
  DBMS_OUTPUT.PUT_LINE(V_COUNT);
END;

内嵌函数的调用

函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数，应用程序在调用函数时，可以使用以下三种方法向函数传递参数：
- 位置表示法
  - 格式
```
argument_value[,argument_value2...]
```
- 名称表示法
  - 格式
```
argument=>parameter[,...]
```
  - 说明
    - argument为形式参数，它必须与函数定义时所声明的形式参数名称相同
    - parameter为实际参数
    - 在这种格式中，形式参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列
- 混合表示法
  
  在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数
  - 采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面
  - 无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。
参数默认值

在CREATE OR REPLACE FUNCTION语句中声明函数参数时，可以使用DEFAULT关键字为输入参数指定默认值。
```
CREATE OR REPLACE FUNCTION GET_SAL(
	DEPT_NO EMP.DEPTNO%TYPE DEFAULT 50,
    TOTAL_COUNT OUT NUMBER
)
...
```
在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。

3.存储过程

创建过程

说明

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

调用存储过程

ORACEL 使用EXECUTE语句来实现对存储过程的调用
```
EXEC[UTE] PROCEDURE_NAME(parameter1,parameter2...)
```
AUTHID

在创建存储过程时，可使用AUTHID CURRENT_USER或AUTHID DEFINER选项，以表明在执行该过程时Oracle使用的权限
- 如果使用AUTHID CURRENT_USER选项创建一个过程，则Oracle用调用该过程的用户权限执行该过程。调用者必须具有访问该存储过程体中引用的所有数据库对象所必须的权限
- 如果用默认的AUTHID DEFINER选项创建过程，则Oracle使用过程所有者的特权执行该过程。过程的所有者必须具有访问该存储过程体中引用的所有数据库对象所必须的权限

4.调试

在SQLPLUS下调试的方法：
- 使用show error function_name|procedure_name命令来提示源码的错误位置
- 使用user_errors数据字典来查看各存储过程的错误位置

使用grant命令来进行存储过程的运行授权

GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION;

与过程相关数据字典

USER_SOURCE,ALL_SOURCE,DBA_SOURCE,USER_ERRORS

5.删除过程和函数

删除过程

使用DROP PROCEDURE命令对不需要的过程进行删除
```
DROP PROCEDURE [user.] Procudure_name;
```
删除函数

使用DROP FUNCTION命令对不需要的函数进行删除
```
DROP FUNCTION [user.] Function_name;
```

8.动态SQL语句

语法

EXECUTE IMMEDIATE 动态语句字符串

DECLARE
V_CT  NUMBER(10):=0;
V_SQL VARCHAR2(200):='CREATE TABLE EMP_CY1 AS (SELECT ENAME,EMPNO FROM EMP WHERE 1=2)';
V_SQL2 VARCHAR2(200):='INSERT INTO EMP_CY1   SELECT ENAME,EMPNO FROM EMP';
BEGIN
  
EXECUTE IMMEDIATE V_SQL ;--动态SQL字符串里面不能有;
EXECUTE IMMEDIATE V_SQL2;
--INSERT INTO EMP_CY1   SELECT ENAME,EMPNO FROM EMP;
--COMMIT;
EXECUTE IMMEDIATE 'SELECT COUNT(1)  FROM EMP_CY1' INTO V_CT;
DBMS_OUTPUT.put_line('插入后有多少行：'||V_CT);
--EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_CY1';
--SELECT COUNT(1) INTO V_CT FROM EMP_CY1;
--DBMS_OUTPUT.put_line('删除后有多少行：'||V_CT);
END;

9.触发器

触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。

1.触发器类型

定义

触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接运行，而触发器是由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行，并且触发器不能接受参数。

运行触发器就叫做触发或点火(firing).

Oracle事件指的是对数据库的表进行的INSERT、UPDAET及DELETE操作或对视图进行类似的操作。
分类
- DML触发器
- 替代触发器
  
  oracle里，不能直接对两个以上的表建立的视图进行操作，所以给出了替代触发器
- 系统触发器
  
  它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。
触发器组成
- 触发事件，即在何种情况下触发TRIGGER
- 触发时间，即该TRIGGER是触发时间发生之前(BEFORE)还是之后(ALTER)触发，指触发事件和该TRIGGER的操作顺序
- 触发器本身，即该TRIGGER被触发之后的目的和意图
- 触发频率，说明触发器内定义的动作被执行的次数
  - 语句级(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]
BEGIN
trigger_body
END;

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

CREATE OR REPLACE TRIGGER DEL_EMP_TRIGGER
BEFORE DELETE
ON EMP
FOR EACH ROW
BEGIN
	INSERT INTO EMP_H(EMPNO,ENAME,DEPTNO) VALUES(:OLD.EMPNP,:OLD.ENMAE,:OLD.DEPTNO)
END;

10.包

定义

把相关的函数，过程，类型等放到一起组成的逻辑结构

常用的系统包

输出包dbms_output

随机数包dbms_random

DBMS_RANDOM.VALUE(参数1，参数2)
--参数1：随机数字产生的最小范围number类型in模式
--参数2：随机数字产生的最大范围number类型in模式
--如果不写参数，则返回0-1之间(不包含0和1)的number类型的浮点型随机数，否则返回min——max之间的number类型的浮点型随机数

--获取一个0-1之间的随机数字
SELECT DBMS_RANDOM.VALUE() FROM DUAL;
--获取一个0-1000的随机数字，并取整
SELECT TRUNC(DBMS_RANDOM.VALUE(0,1000)) FROM DUAL;

定时任务包dbms_job

说明
- 指定间隔事件内做一些重复的事情

语法

--每隔10秒，向EMP_1表插入一些数据
--创建表格
CREATE TABLE EMP_1
AS 
SELECT EMPNO,ENAME FROM EMP
WHERE 1=2;
--定义序列
CREATE OR REPLACE SEQUENCE SEQ_EMP_1;
--定义存储过程
CREATE OR REPLACE PROCEDURE SP_EMP_1
IS
BEGIN
  INSERT INTO EMP_1 VALUES(SEQ_EMP_1.NEXTVAL,'admin'||SEQ_EMP_1.CURRVAL);
END;
--定义并提交定时任务
-- submit(参数1，参数2，参数3，参数4)；
-- 用来提交定时任务到数据库管理系统
--说明
   -- 参数1，job(out模式的binary_integer类型),用来提交定时任务时接收任务编号的
   -- 参数2，varchar2,要调用的存储过程的名称，例如:'过程名();'
   -- 参数3，date，第一次执行任务的时间
   -- 参数4，interval(varchar2)任务执行的间隔时间
      -- 是一个字符串，但是字符串中必须是date类型，例如：'sysdate+1'
      -- 间隔时间的计算方式，参数4-参数3
DECLARE
  JID BINARY_INTEGER;
BEGIN
  DBMS_JOB.SUBMIT(JID,'SP_EMP_1();',SYSDATE,'SYSDATE+10/(24*60*60)');
  DBMS_OUTPUT.PUT_LINE('JID:'||JID);
  DBMS_JOB.RUN(JID);
END;

自定义包

语法格式

创建包头

--语法格式：创建包头
CREATE [OR REPLACE] PACKAGE 包名
IS|AS
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表以及返回类型;
END [包名];

CREATE OR REPLACE PACKAGE PK_TEST
IS
FUNCTION FUN_M(P_NUM1 IN NUMBER,P_NUM2 IN NUMBER) RETURN VARCHAR2;
PROCEDURE SP_E_D(P_ENAME IN VARCHAR2,P_SALARY IN NUMBER,P_JOB OUT VARCHAR2,P_DNAME OUT VARCHAR2);
END PK_TEST;

创建包体

--语法格式：创建包体
CREATE [OR REPLACE] PACKAGE BODY 包名
IS|AS

PROCEDURE 过程名(参数)
IS|AS
BEGIN
过程体;
END [过程名];

FUNCTION 函数名(参数) RETURN 类型
IS|AS
BEGIN
函数体;
END [函数名];

END;

CREATE OR REPLACE PACKAGE BODY PK_TEST
IS
FUNCTION FUN_M(P_NUM1 IN NUMBER, P_NUM2 IN NUMBER)
          RETURN VARCHAR2
          IS
          S NUMBER(10):=1;
          BEGIN
            IF (P_NUM1 BETWEEN 1 AND 20)  AND (P_NUM2 BETWEEN 1 AND 20) THEN
                 IF P_NUM1>P_NUM2 THEN
                   FOR I IN P_NUM2 .. P_NUM1 LOOP
                     S:=S*I;
                    END LOOP;
                 RETURN TO_CHAR(S);
                   ELSE
                     FOR I IN P_NUM1 ..P_NUM2 LOOP
                             S:=S*I;
                    END LOOP;
                    RETURN TO_CHAR(S);
                   END IF;
            ELSE
              RETURN '不在区间范围内';

            END IF;
          END FUN_M;

PROCEDURE SP_E_D(
          P_ENAME IN VARCHAR2,
          P_SALARY IN NUMBER,
          P_JOB OUT VARCHAR2,
          P_DNAME OUT VARCHAR2
          )
          IS
          BEGIN
            SELECT E.JOB,D.DNAME INTO P_JOB,P_DNAME FROM EMP E,DEPT D WHERE (E.DEPTNO=D.DEPTNO) AND 
          E.ENAME=P_ENAME;
            IF P_JOB<>'MANAGER' AND P_SALARY>8000 THEN
              DBMS_OUTPUT.PUT_LINE('普通员工不能赚这么多薪水');
            ELSE
              UPDATE EMP SET EMP.SAL=P_SALARY WHERE EMP.ENAME=P_ENAME;
              COMMIT;
              DBMS_OUTPUT.PUT_LINE('修改薪水成功');
            END IF;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN 
              DBMS_OUTPUT.PUT_LINE('没有找到该员工');
            WHEN TOO_MANY_ROWS THEN
              DBMS_OUTPUT.PUT_LINE('找到多条记录，异常');
          END SP_E_D;
END PK_TEST;

使用自定义包

SELECT PK_TEST.FUN_M(1,6) FROM DUAL;

BEGIN
	PK_TEST.SP_E_D('WARD',7800);
END;

Norni

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle sql学习笔记

目录1.oracle数据库体系机构简介2.SQL语句分类1.sql语句分为以下四种类型3.SELECT语句4.过滤和排序数据5.单行函数6.多表查询7.分组函数8.子查询9.创建和管理表10.数据处理11.约束12.视图13.其它数据库对象14.控制用户权限15.SET运算符16.高级子查询17.分析函数1.分析函数的定义2.理解over()函数2.1 两个order by的执行时机2.2 开窗...
复制链接

扫一扫