ORACLE全部笔记总结
------------------------------------- Oracle 数据库-----------------------------------------------------------
数据库基础
-
数据库：数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一 管理的大量数据的集合。
-
关系型数据库和非关系型数据库
关系型数据库： Oracle、 DB2、 PostgreSQL、 Microsoft SQL Server、 Microsoft Access、 MySQL
-
关系型数据库是依据关系模型来创建的数据库。
-
所谓关系模型就是 一对一、一对多、多对多 等关系模型。
-
关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
-
关系型数据可以很好地存储一些关系模型的数据，比如一个老师对应多个学生的数据（多对多）一本书对应多个作者（一 对多），一本书对应一个出版日期（一对一）
-
关系模型是我们生活中能经常遇见的模型，存储这类数据一般用关系型数据库
-
关系模型包括数据结构（数据存储的问题，二维表）、操作指令集合（SQL语句）、完整性约束(表内数据约束、表与表之 间的约束)。
-
关系型数据库的优缺点：
-
安全（因为存储在磁盘中，不会说突然断电数据就没有了）、
-
容易理解（建立在关系模型上）、
-
但不节省空间（因为建立在关系模型上，就要遵循某些规则，好比数据中某字段值即使为空仍要分配空间）
-
非关系型数据库： 1.列模型：Hbase 2.键值对模型：redis, MemcacheDB 3.文档类模型：mongoDB 4.图存储
-
非关系型数据库主要是基于非关系模型的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）
-
非关系型模型比如有：
-
列模型：存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，数 据即索引，IO很快，主要是一些分布式数据库）
-
键值对模型：存储的数据是一个个“键值对”，比如name:liming,那么name这个键里面存的值就是limingimage
-
文档类模型：以一个个文档来存储数据，有点类似“键值对”。
-
非关系型数据库优缺点：
-
效率高（因为存储在内存中）但不安全（断电丢失数据，但其中redis可以同步数据到磁盘中），现在很多非关系型数据库 都开始支持转存到磁盘中。
-
-
NoSQL(NoSQL = Not Only SQL )，意即"不仅仅是SQL"。NoSQL，泛指的是非关系型的数据库。是对不同于传统的关系型数据库的 数据库管理系统的统称。NoSQL一般用于超大规模数据的存储。（例如谷歌或Facebook每天为他们的用户收集万亿比特的数据） 这些类型的数据存储不需要固定的模式，无需多余操作就可以横向扩展。
-
NoSQL 数据库具有以下几个优点:
1．易扩展 2．大数据量，高性能 3．灵活的数据模型 4．高可用 -
分布式系统：分布式系统是一个由硬件或软件组件分布在不同的网络计算机上，彼此之间仅仅通过消息传递进行通信和协调的系统。
-
分布式数据库：分布式数据库是多个互连的数据库，他们通常位于多个服务器上，但彼此间通信以实现共同目标，通过分布式数据库管理系统（DDBMS）进行管理。分布式数据库为数据库管理领域提供了分布式计算的优势。基本上，我们可以将分布式数据库定义为分布在计算机网络上的多个相关数据库的集合。
-
分布式计算：分布式计算是一种计算方法，和集中式计算是相对的。随着计算技术的发展，有些应用需要非常巨大的计算能力才能完成，如果采用集中式计算，需要耗费相当长的时间来完成。分布式计算将该应用分解成许多小的部分，分配给多台计算机进行处理。这样可以节约整体计算时间，大大提高计算效率。
-
数据模型通常由 数据结构，数据操作，完整性约束(保证表中的数据是有意义的)三个部分组成
-
数据模型分为： 关系化数据模型、半结构化数据模型、层次模型、网状模型。
Oracle 基础
-
每一行数据成为记录/元组，每一列数据成为字段/属性，行为元组，即记录， 列为属性，即字段。
-
每一列都有自己的数据类型
-
SQL(Structured Query Language),即机构化查询语言，是一个通用的数据库语言，是关系数据库的标准语言。
-
SQL语言分为： (由IBM提出，Oracle是最早支持SQL的数据库)
DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交，无需用Commit提交。（Data Definition Language）
DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。（Data Manipulation Language）
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。
DQL(数据查询语言) - Select 查询语句不存在提交问题。
DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。 -
SQL 与 PLSQL
- SQL99是什么 （1）是操作所有关系型数据库的规则 （2）是第四代语言 （3）是一种结构化查询语言 （4）只需发出合法合理的命令，就有对应的结果显示 - SQL的特点 （1）交互性强，非过程化 （2）数据库操纵能力强，只需发送命令，无需关注如何实现 （3）多表操作时，自动导航简单 （4）容易调试，错误提示，直接了当 （5）SQL强调结果 - PLSQL是什么 - 是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句叫做PLSQL - 过程化语句包括有：类型定义，判断，循环，游标，异常或例外处理。 - PLSQL强调过程 - 为什么要用PLSQL - 因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以需要一个过程化程序设计语言来弥补SQL的不 足之处。 - SQL和PLSQL不是替代关系，是弥补关系 - 书写PLSQL的工具有： （1）SQLPLUS工具 （2）SQLDeveloper工具 （3）第三方工具（PLSQL & 其它） - PLSQL与SQL执行上有什么不同： （1）SQL是单条执行的 （2）PLSQL是整体执行的，不能单条执行。 （3）整个PLSQL结束用/ ; 其中每条语句结束用; -
Oracle 基本操作
1.登录Oracle Sqlplus 用户名/密码 Sqlplus 用户名/密码@全局数据区的唯一表示(SID)2.修改密码
（1） 输入sqlplus （2） 在输入用户名:sqlplus/as sysdba （3） 在输入密码:直接回车 （4） 使用SQL语句修改对应用户的密码：alter user 用户名 identified by 密码;3.创建用户
（1） 创建：create user xxx【用户名】 identified by xxx【密码】; （2） 授权：grant create session, connect, resource to xxx【用户名】;3.切换用户
Conn 用户名 / 密码 Conn 用户名 / 密码 @全局数据库名4.查询当前用户登录的全局数据库名[ 必须在sys/system下才能查询 ]
Select name from v$database;5.查询当前用户登录的数据库实例名
Select instance_name from v$instance;6.查询当前实例的状态
select status from v$instance; (正常结果：OPEN) 7. 查询当前数据库的打开模式 select open_mode from v$database; (正常结果：READ WRITE)-
日志文件的查看：
desc v$logfile
select member from v$logfile;
-
归档日志的查看：
archive log list;
-
查看当前登录用户下的所有表:
select table_name from user_tables; -
更改数据库状态
alter database mldn open;
-
复制数据表
CREATE TABLE [new_tablename] AS SELECT * FROM [old_tablename]
13.查询所有表
SELECT * FROM TAB14.设置SQL PLUS显示格式
set linesize 300; set pagesize 30; COL OWNER FOR A10; COL TABLE_NAME FOR A10; -
-
简单查询
select[DISTINCT] * / 列名称 AS 列别名 from tablename ;
执行顺序：第一步执行FROM子句，确定数据来源 第二步执行select子句，确定要显示的数据列 - * ： 表示查询所有数据列/字段 - 列名称 ： 表示显示指定列 - AS : 指定列的别名 - DISTINCT：表示祛除重复数据 - FROM子句：指定要使用的数据表，可以理解为数据来源祛除重复数据
SELECT DISTINCT CLASS FROM STUDENTINFO;给某一字段设置别名
SELECT employee_id , FIRST_NAME, (SALARY+200)*12+5000 AS 年薪 FROM EMPLOYEES;
使用 "||" 双竖线进行字符串的连接，连接后合并显示
-
SELECT EMPLOYEE_ID || FIRST_NAME FROM EMPLOYEES;
-
SELECT '编号是：'|| EMPLOYEE_ID || '姓名是：'|| FIRST_NAME|| '基本工资是：' || SALARY FROM EMPLOYEES;
编号是：100姓名是：Steven基本工资是：24000
加一个表头 “雇员信息表”
SELECT '编号是：'|| EMPLOYEE_ID || '姓名是：'|| FIRST_NAME|| '基本工资是：' || SALARY 雇员信息 FROM
EMPLOYEES;查询表中的数据量 单位：（行）
SELECT COUNT(*) FROM DEPT ; // 14 行记录
-
-- 限定查询
-
where 子句 (and,or,not)
-
第一步执行from子句来控制数据来源
-
第二步执行where子句对数据源进行条件过滤
-
第三步执行select子句确定要显示的数据列
-
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES WHERE SALARY>5000; 查询工资大于5000的人
-
常用限定运算符
运算符 符号 描述
-
--------------------------------------------------------------------------------------------
关系运算符 > < = != >= <= <> 进行大小或相等比较 != 和 <> 都是不等于的意思
--------------------------------------------------------------------------------------------
判断NULL IS NULL, IS NOTNULL 判断某一列内容是否为空
--------------------------------------------------------------------------------------------
逻辑运算符 AND OR NOT 与 或 非 NOT 为取反，真变假 假变真
---------------------------------------------------------------------------------------------
范围查询 BETWEEN~~~AND BETWEEN 最小值 AND 最大值 在此范围内查询
---------------------------------------------------------------------------------------------
列表范围查询 IN NOT IN
---------------------------------------------------------------------------------------------
模糊查询 LIKE % ：表示任意多个字符，包括零个
中文使用两个百分号来表示 %%
_ ：表示一个任意字符
----------------------------------------------------------------------------------------------
- 关系运算符
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Lex';
SELECT * FROM EMPLOYEES WHERE job_id='AD_VP';
SELECT * FROM EMPLOYEES WHERE SALARY<3000;
SELECT * FROM EMPLOYEES WHERE job_id <> 'AD_VP'; // 不等于
- 判断NULL
- 查询 MANAGER_ID为空的员工信息
SELECT * FROM EMPLOYEES WHERE MANAGER_ID IS NULL;
- 查询 commission_pct 字段不为空的员工信息
SELECT FIRST_NAME,commission_pct FROM EMPLOYEES WHERE commission_pct IS NOT NULL;
- 逻辑运算符
- SELECT * FROM EMPLOYEES WHERE SALARY<5000 AND SALARY>4000;
- SELECT * FROM EMPLOYEES WHERE SALARY<5000 AND job_id='IT_PROG';
- ELECT * FROM EMPLOYEES WHERE (DEPARTMENT_ID=90 AND salary>6000) OR (department_id=30 AND SALARY>8000);
- 查询不是IT部门的员工 且 工资大于10000的人
SELECT FIRST_NAME,SALARY,JOB_ID FROM EMPLOYEES WHERE NOT (JOB_ID='IT_PROG' OR SALARY<10000 );
- 范围查询关键字
- 查询salary在3000~4000之间的员工姓名 包括3000和4000
SELECT FIRST_NAME FROM EMPLOYEES WHERE SALARY BETWEEN 3000 AND 4000;
- 查询在1987年入职的员工
SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1-JUN-1987' AND '12-NOV-1987';
- 列表范围查询
- 查询员工标号是100,101,102 的员工信息
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN(100,101,102);
- 查询 除了员工编号 是100,101,102 以外的员工信息
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN(100,101,102);
- 模糊查询
- 查询所有姓名以S开头的员工信息
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%';
- 查询姓名中第二个字母是m的员工信息
Select * from employees where first_name like '_m%';
- 查询姓名中任意位置包含字母f的员工信息
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '%f%';
- 查询姓名长度为7的员工信息
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '_______';
--排序显示
-
Order by 子句
-
在order by 字句中可以指定需要排序的字段。
-
在所有子句之中 order by 子句是放在最后执行的
-
执行顺序 FROM 子句 --> WHERE子句 --> SELECT子句 --> ORDER BY 子句
-
在 ORDER BY 子句中可以使用 SELECT 子句中设置的字段别名.
-
排序有两种模式 ：
> 升序 ASC 默认升序降序 DESC
-
查询员工所有信息，并按照工资排序
SELECT * FROM EMPLOYEES ORDER BY SALARY ASC 升序SELECT * FROM EMPLOYEES ORDER BY SALARY DESC 降序
-
查询 JOB_ID='IT_PROG'的员工信息，并按照工资降序排列
SELECT * FROM EMPLOYEES WHERE JOB_ID='IT_PROG' ORDER BY SALARY DESC
-
-- 多表查询
-
笛卡尔积
SELECT COUNT() FROM DEPT ; // 4
SELECT COUNT() FROM EMP ; // 14通过查询我们发现 DEPT 表 和 EMP表中一共加起来有18行记录。
SELECT * FROM DEPT,EMP; // 56
而通过多表查询发现两张表一共查询出了56行记录，正好是14*4。
所以在多表查询的时候，由于数据库内部的处理机制，会产生一些无用的数据，这些数据就称为“笛卡尔积”。
-
消除笛卡尔积
一般要消除笛卡尔积 使用关联字段
-
使用等值条件消除笛卡尔积(但是此时积依然存在，只是不显示了)
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-
-
多表查询实例
查询每个雇员的编号、姓名、职位、工资、部门编号、部门位置信息（EMP表，DEPT表）
- 所被查询的字段中没有两表共有字段，所以可以直接写字段名进行查询，如果有共有字段需要使用 表名.字段名 进行引用查询。 -SELECT EMPNO,ENAME,JOB,SAL,DNAME,LOC FROM EMP ,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; -SELECT EMP.EMPNO,EMP.ENAME,EMP.JOB,EMP.SAL,DEPT.DNAME,DEPT.DEPTNO,DEPT.LOC FROM EMP ,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;由于现实生产环境中表名通常比较长，所以多表查询中一般需要给表名设置别名，然后通过别名进行引用查询
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO查询每个员工的姓名，入职日期，工资和工资等级 （EMP 和 SALGRADE）
SELECT E.ENAME,E.HIREDATE,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;查询每个员工的姓名，入职日期，工资和工资等级 ,并给每个等级起别名显示 （EMP 和 SALGRADE）
SELECT E.ENAME,E.HIREDATE,E.SAL, DECODE(S.GRADE,1, 'E等工资',2,'D等工资' ,3,'C等工资',4,'B等工资',5,'A等工资','没有定义该等级') 工资等级 FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;三表查询 (EMP ,DEPT,SALGRADE)
SELECT E.ENAME,E.HIREDATE,E.SAL,D.DEPTNO,D.LOC, DECODE(S.GRADE,1, 'E等工资',2,'D等工资' ,3,'C等工资',4,'B等工资',5,'A等工资','没有定义该等级') 工资等级 FROM EMP E, SALGRADE S, DEPT D WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO=D.DEPTNO -
表的连接
Oracle中表的连接语法
-左外连接：左关系属性 = 右关系属性(+) - Oracle： SELECT * FROM EMP ,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO (+) - sql 1999：SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO) -右外连接：左关系属性(+) = 右关系属性 - Oracle： SELECT * FROM EMP ,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO - sql 1999： SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)-
自身关联
从EMP 表中查询出所有雇员的领导信息
SELECT E.EMPNO,E.ENAME,M.EMPNO,M.ENAME FROM EMP E,EMP M WHERE E.MGR=M.EMPNO (+)
SQL 1999 中表的连接语法（适用大部分数据库）
- 交叉连接（CROSS JOIN） SELECT * FROM EMP CROSS JOIN DEPT // 交叉连接产生笛卡尔积，显示两表数据行数之积行记录 - 自然连接 ( NATURAL JOIN) SELECT * FROM EMP NATURAL JOIN DEPT // 自然连接会自动匹配连接字段把该字段放在首列，并消除笛卡尔积 - JOIN USING 子句 (设置连接字段) SELECT * FROM EMP JOIN DEPT USING(DEPTNO); - JOIN ON 子句 (设置连接条件) SELECT * FROM EMP E JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL); - RIGHT OUTER JOIN ON 右外连接 > 没有雇员的部门会显示出来 SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO) - LEFT OUTER JOIN ON 左外连接 > 没有部门的雇员会显示出来 SELECT * FROM EMP LEFT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO) - FULL OUTER JOIN ON 全外连接 > 没有雇员的部门会显示出来，没有部门的雇员也会显示出来 SELECT * FROM EMP FULL OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)//全外连接只能使用SQL1999语法 -
-
数据的集合运算
-
集合运算时一种二目运算符，一共包含四种运算符，并 查，交 笛卡尔积
-
UNION 并集 ：返回若干个查询结果的全部内容，但重复元组不显示
SELECT * FROM DEPT UNION SELECT * FROM DEPT WHERE DEPTNO=10; // 四行数据
-
UNIONALL 并集：返回若干个查询结果的全部内容，重复元组也会显示
SELECT * FROM DEPT UNION ALL SELECT * FROM DEPT WHERE DEPTNO=10; // 五行数据
-
MINUS 差集 ： 返回若干个查询结果中不同的部分
SELECT * FROM DEPT MINUS SELECT * FROM DEPT WHERE DEPTNO=10; // 三行数据
-
INTERSECT 交集 ：返回若干个查询结果中各查询相同的部分
SELECT * FROM DEPT INTERSECT SELECT * FROM DEPT WHERE DEPTNO=10; // 一行数据
-
-
多表查询综合查询实例
查询出1981年入职的全部员工编号，姓名 ，雇佣日期（按照 xxxx-xx-xx 显示），岗位名称，领导姓名，雇员月工资，雇 员年工资（基本工资+ 奖金），雇员工资等级，部门编号，部门名称，部门位置，要求所查询的员工月工资在1500~3000之 间，将最后的结果按照年工资降序排列，如年工资相等，则按照岗位名称排序。
分步骤进行分析： 1.确定所需要的数据表 -emp表1：员工编号，姓名 ，雇佣日期，岗位名称，雇员月工资，雇员年工资 -emp表2： 领导姓名 -dept表：部门编号，部门名称，部门位置 -salgrade表： 工资等级 2.确定已知的关联字段 - 雇员和领导：emp.mgr=emp.empno - 雇员和部门：emp.deptno=dept.deptno - 雇员和工资等级：EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL 3.开始查询 步骤一：查询出1981年入职的所有 员工编号，姓名 ，雇佣日期，岗位名称，雇员月工资，雇员年工资，并且限定 月薪在1500~3000 之间（仅需要EMP表） 步骤二： 加入领导信息，加入emp2表 使用自身关联 查询出 ：领导姓名 步骤三； 加入部门信息，加入部门表dept，查询出： 部门编号，部门名称，部门位置 步骤四： 加入工资等级信息 加入SALGRADE表，加入限定条件 EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL 查询出工资等级。 步骤五： 进行排序 SQL语句： SELECT E.EMPNO 员工编号, E.ENAME 员工姓名, TO_CHAR(E.HIREDATE,'YYYY-MM-DD') 雇佣日期, E.JOB 职位, E.SAL 月工资, E.SAL*12+NVL(E.COMM,0) 年工资, M.ENAME 领导姓名 , D.DEPTNO 部门编号, D.DNAME 部门名称 , D.LOC 部门位置 DECODE(S.GRADE,1, 'E等工资',2,'D等工资' ,3,'C等工资',4,'B等工资',5,'A等工资','没有定义该等级') 工资等级, FROM EMP E ,EMP M, SALGRADE S, DEPT D WHERE TO_CHAR(E.HIREDATE,'YYYY')='1981' AND (E.SAL BETWEEN 1500 AND 3000) AND (E.SAL BETWEEN S.LOSAL AND S.HISAL) AND E.MGR=M.EMPNO (+) AND E.DEPTNO=D.DEPTNO ORDER BY E.SAL DESC,E.JOB
-- SQL 1999 语法
* 交叉连接（CROSS JOIN）
SELECT * FROM EMP CROSS JOIN DEPT // 交叉连接产生笛卡尔积，显示两表数据行数之积行记录
* 自然连接 ( NATURAL JOIN)
SELECT * FROM EMP NATURAL JOIN DEPT // 自然连接会自动匹配连接字段把该字段放在首列，并消除笛卡尔积
* JOIN USING 子句 (设置连接字段)
SELECT * FROM EMP JOIN DEPT USING(DEPTNO);
* JOIN ON 子句 (设置连接条件)
SELECT * FROM EMP E JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);
* RIGHT OUTER JOIN ON 右外连接
> 没有雇员的部门会显示出来
SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)
* LEFT OUTER JOIN ON 左外连接
> 没有部门的雇员会显示出来
SELECT * FROM EMP LEFT OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)
* FULL OUTER JOIN ON 全外连接
> 没有雇员的部门会显示出来，没有部门的雇员也会显示出来
SELECT * FROM EMP FULL OUTER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)// 全外连接只能使用SQL1999语法
-- 单行函数
* 语法
FUNCATION_NAME(列/ 表达式/ 数值)
函数名称(列/ 表达式/ 数值)
* 单行函数主要分为
- 字符函数
- 数值函数
- 日期函数
- 转换函数
- 通用函数
-
字符函数
-
UPPER(字段名) 和 Lower() 函数 : 大小写之间的转换
自动转换大写
select * from employees where job_id=UPPER('it_prog');
> 自动转换小写select lower ('ZHANGSAN') FROM DUAL
2.INITCAP(字段名) 函数 ： 将数据的首字母大写
> 将记录中的信息首字母大写其余小写SELECT JOB_ID, INITCAP(JOB_ID) FROM EMPLOYEES; // AD_PRES Ad_Pres
-
REPLACE(字段名，'需要替换的信息'，'替换后的信息') 函数 ：替换掉指定内容
将员工姓名中的 'A' 字母 用 '_' 下划线代替。
SELECT FIRST_NAME , REPLACE(FIRST_NAME,'A','_') FROM EMPLOYEES; // Alexander _lexander
4.LENGTH(字段名) 函数 ： 返回字段的长度
> 查询员工姓名长度为5的员工信息 SELECT * FROM EMPLOYEES WHERE LENGTH(FIRST_NAME)=5; SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '_____';-
SUBSTR(字段名，开始下标，结束下标) 函数 截取字符串
查询出名字前三位字母是'Dav'的员工信息
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME, 0, 3)='Dav';
截取名字的前三个字母
SELECT FIRST_NAME, SUBSTR(FIRST_NAME,0,3) FROM EMPLOYEES; // Steven Ste
截取名字的后三个字母
SELECT FIRST_NAME, SUBSTR(FIRST_NAME,-3) FROM EMPLOYEES; // Steven ven
-
ASCII() 函数 ： 求ASCII 码值
> 求ASCII码值 SELECT ASCII('a'),ASCII('A') FROM DUAL; // 97 65 -
CHR() 函数 ：将 ASCII 转化为 字符
> 将ASCII 码转化为字符SELECT CHR(97), CHR(65) FROM DUAL ; // a A
-
TRIN() LTRIM() 和 RTRIM() 去掉字符串所有空格、左空格、右空格。
去掉左空格
SELECT ' A ' , LTRIM (' A ') FROM DUAL;
去掉右空格
SELECT ' ABC ', RTRIM(' ABC ') FROM DUAL;
去掉所有空格(两个字符串之间的空格不能消除)
SELECT ' shiziqian ', TRIM(' shiziqiang ') FROM DUAL;
-
LPAD(字符，字符长度 ，'空缺部分需要补充的数据') RPAD() 字符串填充
左补充 设定字符串度是10，缺少部分补'*'
SELECT LPAD('SHI',10,'*') FROM DUAL; // *******SHI
右补充 设定字符串度是10，缺少部分补'*'
select rpad('SHI',10,'*') from dual // SHI******* > 组合使用 左右两边补充相同的位数 SELECT LPAD ( RPAD('SHIGE',10,'*' ), 15, '*') FROM DUAL; -
INSTR() 函数 字符串的查找 找的好返回位置索引，找不到返回0
SELECT
INSTR ('SHI GE','SHI') 查到得到,
INSTR ('SHI GE','GE') 查找的到,
INSTR ('SHI GE','shi') 查找不到FROM DUAL; // 1 5 0
-
-
数值函数
1.ROUND(数字，保留位数) 函数 ： 对小数进行四舍五入，可以指定保留位数，如果不指定默认将小数全部四舍五入。
SELECT ROUND(3.141592658,3) FROM DUAL; //3.1422.TRUNC(数字，截取位数)函数 ：保留指定位数的小数，如果不指定则表示不保留小数
SELECT TRUNC(3.141592658,3)FROM DUAL ; //3.141-
MOD(数字，数字) 函数 ： 取模/取余
SELECT MOD(10,3) FROM DUAL; // 1
-
-
日期函数
1. 取得当前日期SELECT SYSDATE FROM DUAL ;
SELECT SYSTIMESTAMP FROM DUAL ; // 2020-03-20 10:44:53.932000 +08:00
-
修改日期显示格式
-
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'; // 2020-03-20 09:21:27
-
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd/day') from dual; // 2020/03/20/星期五
-
3.日期计算公式
-
若干天前的日期： 日期 - 数字 = 日期
SELECT SYSDATE-3 FROM DUAL ; // 2020-03-17 09:31:19
-
若干天后的日期： 日期 + 数字 = 日期
SELECT SYSDATE+3 FROM DUAL ; // 2020-03-23 09:30:15 -
两个日期之间的间隔天数： 日期 - 日期 = 数字(天数)
SELECT (SYSDATE-HIREDATE) FROM EMPSELECT ENAME ,JOB, TRUNC((SYSDATE-HIREDATE)/365) 工龄 FROM EMP ;
-
没有日期+日期因为没有意义。
-
ADD_MONTHS(日期，月数) 函数 ； 在指定的日期上加上新的月数求出新的日期
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL; // 2020-06-20 09:50:07SELECT ADD_MONTHS(SYSDATE,-3) FROM DUAL; // 三个月之前的日期
-
MONTHS_BETWEEN(日期1，日期2) 函数 ： 求两个日期间有几个月
SELECT MONTHS_BETWEEN('2019-4-20 09:53:25',SYSDATE) 月数 FROM DUAL ;
-
-
NEXT_DAY(日期，星期数) 函数 ： 求出下一个星期X 的 具体日期
SELECT NEXT_DAY(SYSDATE,'星期二') FROM DUAL ; // 2020-03-24 10:02:25
-
LAST_DAY(日期) 求出指定日期所在月的最后一天日期
SELECT LAST_DAY('2020-02-01')FROM DUAL; // 2020-02-29 00:00:00
-
-
EXTRACT() 函数 截取出某日期的 年 月 日 时 分 秒
- 取指定字符串 SELECT EXTRACT(YEAR FROM DATE '2020-03-20') , EXTRACT(MONTH FROM DATE '2020-03-20') , EXTRACT(DAY FROM DATE '2020-03-20') FROM DUAL; // 2020 3 20 - 取 SYSTIMESTAMP SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) , EXTRACT(MONTH FROM SYSTIMESTAMP) , EXTRACT(DAY FROM SYSTIMESTAMP) , EXTRACT(HOUR FROM SYSTIMESTAMP) , EXTRACT(MINUTE FROM SYSTIMESTAMP) , EXTRACT(SECOND FROM SYSTIMESTAMP) FROM DUAL; 9. TO_TIMESTAMP() 将时间字符串 转换为 时间戳 时间格式 SELECT TO_TIMESTAMP('2020-03-20 12:08:57','yyyy-mm-dd hh24:mi:ss') FROM DUAL ; - 通过 TO_TIMESTAMP() 将时间字符串进行转化后，利用EXTRAC()函数，求得两个时间戳之间的间隔 SELECT EXTRACT(DAY FROM TO_TIMESTAMP('2020-03-20 12:08:57','yyyy-mm-dd hh24:mi:ss') - TO_TIMESTAMP('2020-07-20 12:08:57','yyyy-mm-dd hh24:mi:ss')) FROM DUAL ; -
-
转换函数
1. TO_CHARE('日期，数字，列'/ 转换格式) 将指定的数据按照指定的格式变为字符串型数据 > 查询在2月份雇佣的员工信息 SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02'; > 查询在2月20日雇佣的员工信息 SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM-dd')='02-20'; > 格式化显示雇佣日期 SELECT ENAME,TO_CHAR(HIREDATE,'YYYY-MM-DD') , TO_CHAR(HIREDATE,'YYYY') 年, TO_CHAR(HIREDATE,'MM') 月, TO_CHAR(HIREDATE,'DD') 日 ROM EMP ; > 将数字格式化 - SELECT TO_CHAR(987654321,'999,999,999,9999') FROM DUAL ; // 98,765,4321 - SELECT TO_CHAR(987654321,'000,000,000,0000') FROM DUAL ; // 000,098,765,4321 > 显示货币 - 按照语言环境显示货币类型 SELECT TO_CHAR(987654321,' L000,000,000,0000 ') FROM DUAL ; // ￥000,098,765,4321 - 显示美元 SELECT TO_CHAR(987654321,'$000,000,000,0000') FROM DUAL ; // $000,098,765,4321 2. TO_DATE() 函数 : 将字符串转化为日期 SELECT TO_DATE('1996-05-21','YYYY-MM-DD') FROM DUAL ;-
TO_TIMESTAMP() 将时间字符串 转换为 时间戳 时间格式
SELECT TO_TIMESTAMP('2020-03-20 12:08:57','yyyy-mm-dd hh24:mi:ss') FROM DUAL ;
-
TO_NUMBER() 函数
- SELECT TO_NUMBER('19')+TO_NUMBER('1') FROM DUAL; // 20
-
-
通用函数
1.NVL(null，0) 函数 ： 如果数据是NULL，那么就将其变为0，如果不是null 就保持原数据。 SELECT NVL(NULL,0),NVL(3, 0) FROM DUAL; // 0 3 > 查询员工的年薪 年薪等于 基本工资 + 奖金 - 未使用NVL() 函数，因为comm奖金中有null值，所以计算的年薪中就存在null值。 SELECT EMPNO,ENAME ,JOB,(SAL+COMM)*12年薪 FROM EMP; - 使用NVL() 函数 ，将存在null值的字段进行转化，将null转化为0. SELECT EMPNO,ENAME ,JOB,(SAL+ NVL(COMM, 0) )*12年薪 FROM EMP; 2. NVL2() 函数 进行条件判断 > 如果NVL2()函数中的第一字段值不是空就计算表达式1，如果是null就计算表达式2 SELECT EMPNO,ENAME ,JOB,NVL2(COMM, SAL+COMM, SAL)*12年薪 FROM EMP; 3. NULLIF() 函数 判断两个值是否相等，如果相等返回NULL，如果不等返回表达式1 SELECT NULLIF(1,1),NULLIF(1, 2) FROM DUAL; // NULL 1 > 如果姓名和岗位名长度一致，那么就做一个标记，标记为NULL SELECT EMPNO,ENAME ,SAL , NULLIF(LENGTH(ENAME),LENGTH(JOB)) FROM EMP; 4. DECODE() 函数 类似于 switch case > 输入判断条件1， 然后与后边的表达式进行匹配，找到1，就输出1对应的内容，如果没有找到，就输出默认内容 SELECT DECODE(1, 1,'内容为1', 2,'内容为2', '没有条件匹配') FROM DUAL; > 使用DECODE() 函数将job岗位名称进行中文注释 SELECT ENAME,JOB, DECODE(JOB, 'CLERK','办事员' , 'SALESMAN','会计', 'MANAGER' ,'经理', 'ANALYST','市场分析员', 'PRESIDENT','总经理', '未找到该职位' )职位 FROM EMP; 5. CASE 需要判断的字段 WHEN 字段值 THEN 字段值 WHEN THEN WHEN THEN ELSE END > 实例： 公司要进行涨薪，岗位clerk涨薪1.1倍，SALESMAN 涨薪1.2倍，ANALYSE涨薪1.3倍其余岗位涨薪1.5倍 SELECT ENAME, SAL, CASE JOB WHEN 'CLERK' THEN SAL*1.1 WHEN 'SALESMAN' THEN SAL*1.2 WHEN 'ANALYST' THEN SAL*1.3 ELSE SAL*1.5 END 涨薪后工资 FROM EMP; 6. COALESCE(字段, 值2,值3,值4，值5......) 如果字段的值是空，就用值1替换掉NULL值，如果值1也是NULL，就用值1替换掉NULL值，以此类推。 > 查询员工的comm奖金信息，如果奖金信息为空，那么就显示值1，如果值1还是空，那么就显示只2 SELECT ENAME,SAL,COMM, COALESCE(COMM, NULL,2000) FROM EMP;-
统计函数
1.COUNT() 求出全部的记录数
> 查询表中一共有多少行数据 SELECT COUNT(*) FROM EMP > COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)区别？ - COUNT(*) ：会统计所有记录包括NULL值 - COUNT(字段)： 不会统计NULL值 - COUUNT(DISTINCT 字段)： 不会统计NULL值，也不会统计重复记录。 SELECT COUNT(COMM),COUNT(DISTINCT COMM) FROM EMP 2.SUN(列) 求出总和，操作的；列是数字 > 求出工资的总和 SELECT SUM(SAL) FROM EMP 3. AVG(列) 求平均值 > 求工资的平均值 SELECT AVG(SAL) FROM EMP 4. MAX(列) 最大值 > 求最大值 SELECT MAX(SAL) FROM EMP 5. MIN(列) 最小值 > 求最小值 SELECT MIN(SAL) FROM EMP > 求员工最早最晚雇佣日期 SELECT MIN(HIREDATE) 最早雇佣日期, MAX(HIREDATE) 最晚雇佣日期 FROM EMP 6. MEDIAN(列) 中间值 > 求中值 SELECT MEDIAN(SAL) FROM EMP 7. VARIANCE(列) 返回方差 > 求方差 SELECT VARIANCE(SAL) FROM EMP 8. STDDEV(列) 返回标准差 > 求标准差 SELECT STDDEV(SAL) FROM EMP
-
-- 分组统计查询
-
统计函数
1.COUNT() 求出全部的记录数
> 查询表中一共有多少行数据 SELECT COUNT(*) FROM EMP > COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)区别？ - COUNT(*) ：会统计所有记录包括NULL值 - COUNT(字段)： 不会统计NULL值 - COUUNT(DISTINCT 字段)： 不会统计NULL值，也不会统计重复记录。 SELECT COUNT(COMM),COUNT(DISTINCT COMM) FROM EMP 2.SUN(列) 求出总和，操作的；列是数字 > 求出工资的总和 SELECT SUM(SAL) FROM EMP 3. AVG(列) 求平均值 > 求工资的平均值 SELECT AVG(SAL) FROM EMP 4. MAX(列) 最大值 > 求最大值 SELECT MAX(SAL) FROM EMP 5. MIN(列) 最小值 > 求最小值 SELECT MIN(SAL) FROM EMP > 求员工最早最晚雇佣日期 SELECT MIN(HIREDATE) 最早雇佣日期, MAX(HIREDATE) 最晚雇佣日期 FROM EMP 6. MEDIAN(列) 中间值 > 求中值 SELECT MEDIAN(SAL) FROM EMP 7. VARIANCE(列) 返回方差 > 求方差 SELECT VARIANCE(SAL) FROM EMP 8. STDDEV(列) 返回标准差 > 求标准差 SELECT STDDEV(SAL) FROM EMP -
单字段分组统计 GROUP BY 子句
-
一般使用表中含有重复数据的列进行分组，也可以使用不重复的列进行分组，只不过是一组一条数据，没有意义。
-
将EMP表按照部门号进行分组
SELECT DEPTNO , COUNT(DEPTNO) FROM EMP GROUP BY DEPTNO;
-
统计出每个职位的最高和最低工资
SELECT JOB, COUNT(JOB), MAX(SAL), MIN(SAL) FROM EMP GROUP BY JOB -
分组统计的 语法规则
1. 在查询语句中如果没有GROUP BY子句，如果使用了统计函数，那么在SELECT 子句中只允许存在统计函数，不能出现其任何字段。正确： SELECT COUNT(*) FROM EMP;
错误： SELECT COUNT(*), ENAME FROM EMP;
-
在统计查询中如果存在GROUP BY 子句，那么在SELECT 子句中只允许出现统计函数 和 分组字段 ，不能出现其他任何字段
正确： SELECT DEPTNO, COUNT(DEPTNO) FROM EMP GROUP BY DEPTNO;
错误： SELECT DEPTNO , COUNT(DEPTNO)，ENAME FROM EMP GROUP BY DEPTNO;
-
统计函数允许嵌套使用，但是嵌套统计函数之后的SELECT 语句中不能再使用任何字段，包括分组字段。
按部门分组后，求出每个组的平均工资，在求出所有组的最高平均工资
正确： SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO
错误： SELECT MAX(AVG(SAL)) ,DEPTNO FROM EMP GROUP BY DEPTNO
-
-
实例： 查询每个部门的名称，部门人数，部门平均工资，部门平均工龄
SELECT D.DNAME 部门名称, COUNT(E.EMPNO) 部门人数, ROUND(AVG(E.SAL),2) 部门平均工资, ROUND(AVG(MONTHS_BETWEEN(E.HIREDATE,SYSDATE))/12, 2) 平均工龄FROM EMP E, DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO
GROUP BY D.DNAME
-
实例： 查询每个员工的工资等级，每个等级的人数，每个等级的平均工资
SELECT S.GRADE 工资等级, COUNT(E.EMPNO)员工数量, ROUND(AVG(E.SAL), 2) 平均工资
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
GROUP BY S.GRADE
-
-
- 实例： 统计领取佣金与不领取佣金的员工的平均工资，工作年限，员工人数
> 使用以下这种方法，会按照COMM的每种数值进行分组，并不是按照是否领取佣金分组的
SELECT E.COMM, ROUND(AVG(E.SAL),2) 平均工资,
ROUND(AVG(MONTHS_BETWEEN(E.HIREDATE,SYSDATE))/12, 2)平均工龄,
COUNT(E.EMPNO) 员工人数
FROM EMP E
GROUP BY E.COMM
> 正确方法:分别查询领取佣金和不领取佣金员工的信息，然后使用 UNION 进行结果集的结合。
SELECT '不领取佣金', ROUND(AVG(E.SAL),2) 平均工资,
ROUND(AVG(MONTHS_BETWEEN(E.HIREDATE,SYSDATE))/12, 2)平均工龄,COUNT(E.EMPNO) 员工人数
FROM EMP E
WHERE COMM IS NULL
UNION
SELECT '领取佣金',ROUND(AVG(E.SAL),2) 平均工资,
ROUND(AVG(MONTHS_BETWEEN(E.HIREDATE,SYSDATE))/12, 2)平均工龄, COUNT(E.EMPNO) 员工人数
FROM EMP E
WHERE COMM IS NOT NULL
-
多字段分组统计 GROUP BY 子句
- 查询出每个部门的详细信息
SELECT D.DEPTNO 部门编号, D.DNAME 部门名称,
D.LOC 部门地址, NVL(ROUND(AVG(E.SAL),2),0) 部门平均工资, NVL(MAX(E.SAL),0) 最高工资, NVL(MIN(E.SAL),0)最低工资, COUNT(E.EMPNO)部门人数FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
GROUP BY D.DEPTNO,D.DNAME,D.LOC
ORDER BY 部门平均工资 DESC
-
HAVING 子句
-
使用 GROUP BY 子句可以实现数据的分组显示，但是在很多时候需要对分组后的数据再次进行筛选过滤，例如筛选出部 门人数超过5个人的部门信息，HAVING 子句就可以实现这样的功能。
-
HAVING 子句必须和 GROUP BY 子句同时使用
-
WHERE子句中的限定筛选条件对GROUP BY 不起作用，因为WHERE子句比GROUP BY 子句先执行，且WHERE子句中不能使用统 计函数
-
语句执行顺序： FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY
-
实例：查询所有平均工资大于10000的职位信息，平均工资，雇员人数
SELECT JOB 职位, ROUND(AVG(SAL),2) 平均工资, COUNT(EMPNO)雇员人数FROM EMP
GROUP BY JOB
HAVING AVG(SAL)>10000
- 实例： 查询出至少有3个员工的部门的部门编号，名称，平均工资，最低工资，最高工资 SELECT D.DEPTNO 部门编号, D.DNAME 部门名称, D.LOC 部门地址, NVL(ROUND(AVG(E.SAL),2),0) 部门平均工资, NVL(MAX(E.SAL),0) 最高工资, NVL(MIN(E.SAL),0)最低工资, COUNT(E.EMPNO)部门人数 FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO GROUP BY D.DEPTNO,D.DNAME,D.LOC HAVING COUNT(E.EMPNO)>3 ORDER BY 部门平均工资 DESC-
实例： 显示非销售人员的岗位名称以及工资总和 ，并且要求从事同一工作的员工月工资总和大于5000，输入后按照月工资总和升序排列
SELECT JOB 岗位名称, SUM(SAL)工资总和FROM EMP
WHERE JOB <> 'SALESMAN'
GROUP BY JOB
HAVING SUM(SAL)>5000
ORDER BY 工资总和
-
-- 子查询
* 子查询就是指一个完成的查询语句中，嵌套了多个不同功能的小查询，从而一起完成复杂查询的一种编写形式
* 子查询编写中一定要使用括号标记
- 查询公司中工资最低的雇员的完整信息
SELECT * FROM EMP
WHERE SAL=(SELECT MIN(SAL) FROM EMP) // 子查询
* 子查询可以返回的数据类型一共有四种
- 单行单列：返回的是一个具体列的内容，可以理解为是一个单值数据
- 单行多列：返回一行中多个列的内容
- 多行单列：返回多行记录中同一列的内容，相当于给出了一个操作范围
- 多行多列：返回的内容相当于一个临时表
* 子查询的常见操作
- WHERE 子句： 在WHERE 子句中的子查询，此时子查询一般返回的都是单行单列，单行多列，多行单列
- HAVING 子句： 在HAVING 子句中的子查询返回的都是单行单列数据，同时使用统计函数。
- FROM 子句： 在FROM 子句中，此时查询返回的一般都是多行多列，一张临时数据表。
* WHERE 子句中的子查询
> WHERE 子句中的单行单列子查询
- 查询出基本工资比ALLEN低的全部员工信息
SELECT * FROM EMP
WHERE SAL<(SELECT SAL FROM EMP WHERE ENAME='ALLEN')
- 查询出基本工资高于公司平均薪资的员工信息
SELECT * FROM EMP
WHERE SAL>( SELECT AVG(SAL) FROM EMP)
- 查询和 ALLEN 从事同一工作的员工信息，而且薪资要高于员工编号为7521的员工
SELECT * FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN')
AND SAL>(SELECT SAL FROM EMP WHERE EMPNO =7521)
> WHERE 子句中的单行多列子查询
- 查询与SCOTT 从事同一工作 且工资相同的雇员信息 返回一行两列信息
SELECT * FROM EMP
WHERE (JOB,SAL)=(SELECT JOB,SAL FROM EMP WHERE ENAME='SCOTT')
AND ENAME<>'SCOTT'
- 查询与员工编号为7566从事同一工作的且领导相同的员工信息
SELECT * FROM EMP
WHERE (JOB ,MGR)=(SELECT JOB ,MGR FROM EMP WHERE EMPNO = 7566 )
AND EMPNO<>7566
- 查询与ALLEN 从事同一工作且同一年入职的员工信息
SELECT * FROM EMP
WHERE (JOB ,TO_CHAR(HIREDATE,'yyyy'))=(SELECT JOB ,TO_CHAR(HIREDATE,'yyyy') FROM EMP WHERE ENAME = 'ALLEN' )
AND ENAME<>'ALLEN'
> WHERE 子句中的多行单列子查询
- 在进行多行子查询，主要使用三种操作符 IN ANY/SOME ALL
> IN NOT IN 操作符：
# 在IN 中如果返回的数据中有NULL 不会有影响，在NOT IN 中如果有NULL那么不会有任何数据返回
# 查询出每个部门工资最低的员工信息
SELECT * FROM EMP
WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
# 查询出每个员工信息刨除工资最低的员工信息
SELECT * FROM EMP
WHERE SAL NOT IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)
> ANY /SOME 操作符
- ANY /SOME 功能完全一致，SOME是为了防止歧义后加的
- = ANY ：表示与子查询中的每个元素进行比较，功能与in类似. 显示子查询范围内的数据
- > ANY: 比子查询的最小值要大，显示比子查询中的最小值大的数据
- < ANY: 比子查询的最小值要小，显示比子查询中的小值小的数据
- 查询出每个部门职位是经理的最低工资和所有信息
SELECT * FROM EMP
WHERE SAL =ANY(SELECT MIN(SAL) FROM EMP WHERE JOB ='MANAGER' GROUP BY DEPTNO）
> ALL 操作符
- <> ALL: 等于NOT IN 全都不在子查询结果范围内
- >ALL: 比子查询中的最大值还要大
- < ALL : 比子查询的最小值还要小
- 查询出公司比每个部门职位是经理的最低工资中最高的部门要高的员工的所有信息
SELECT * FROM EMP
WHERE SAL >ALL (SELECT MIN(SAL) FROM EMP WHERE JOB ='MANAGER' GROUP BY DEPTNO）
> EXISTS 操作符
EXISTS结构用于判断子查询中是否有数据返回，如果有数据返回，则EXIXTS结构返回TRUE，如果没有数据返回则EXISTS结构返回FALSE
SELECT * FROM EMP
WHERE EXISTS(SELECT * FROM EMP WHERE EMPNO=9999)
SELECT * FROM EMP
WHERE EXISTS(SELECT * FROM EMP )
> NOT EXISTS 操作符
SELECT * FROM EMP
WHERE NOT EXISTS(SELECT * FROM EMP WHERE EMPNO=9999)
SELECT * FROM EMP
WHERE NOT EXISTS(SELECT * FROM EMP )
* HAVING 子句中的 子查询
- HAVING 子句是结合GROUP BY 子句一起使用的，其主要目的是进行分组后数据的再次过滤，而且与where子句不同的是 HAVING 可以使用统计函数
- 一般而言 HAVING 中使用的子查询返回的数据往往是单行单列的，按照一个数值的方式返回，再通过统计函数进行过滤
- 查询每个部门的平均薪资，显示出比整个公司平均薪资要大的部门薪资
SELECT DEPTNO 部门编号 , COUNT(EMPNO) 部门人数, ROUND(AVG(SAL),2)部门平均薪资
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>(SELECT AVG(SAL) FROM EMP )
- 查询出每个部门最高的平均工资及平均工资
SELECT D.DEPTNO,ROUND(AVG(E.SAL),2)
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY D.DEPTNO
HAVING AVG(SAL)=(SELECT ROUND (MAX(AVG(SAL))) FROM EMP GROUP BY DEPTNO)
* 在FROM 子句中使用子查询
- FROM 子句的主要功能是确定数据的来源，数据源都是数据表，表的特征是 行+列的集合，在from子句之中出现的内容一般都是多行多列的字段
- 使用子查询可以解决多表查询带来的性能问题，子查询的笛卡尔积更小
- 查询出每个部门的编号，名称，位置，部门人数，平均工资
SELECT D.DEPTNO ,D.DNAME ,D.LOC ,TEMP."部门人数",TEMP."平均工资"
FROM DEPT D , (
SELECT DEPTNO,COUNT(EMPNO)部门人数, ROUND(AVG(SAL), 2)平均工资
FROM EMP
GROUP BY DEPTNO ) TEMP
WHERE D.DEPTNO=TEMP.DEPTNO
- 查询出所有部门再SALES工作的员工编号，姓名，基本工资，奖金，职位 雇佣日期，部门最高工资，部门最低工资
SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.SAL,E.COMM,E.JOB,E.HIREDATE, TEMP.部门最高工资,TEMP.部门最低工资
FROM EMP E, (
SELECT DEPTNO, MAX(SAL)部门最高工资,MIN(SAL) 部门最低工资
FROM EMP
GROUP BY DEPTNO) TEMP
WHERE E.DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')
AND E.DEPTNO=TEMP.DEPTNO
- 查询出所有薪资高于公司平均薪资的员工的 编号，姓名，基本工资，职位，雇佣日期 所在部门名 位置 上级领导 名称，工资等级，部门人数 平均薪资 平均工龄
SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.SAL,E.COMM,E.JOB,E.HIREDATE,D.DNAME,D.LOC,M.MGR,S.GRADE,
TEMP.部门人数,TEMP.平均工资,TEMP.平均工龄
FROM EMP E , EMP M ,DEPT D, SALGRADE S ,(
SELECT DEPTNO,COUNT(EMPNO)部门人数, ROUND(AVG(SAL), 2)平均工资 ,ROUND(AVG(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12), 2)平均工龄
FROM EMP
GROUP BY DEPTNO) TEMP
WHERE E.SAL>(SELECT AVG(SAL) FROM EMP )
AND E.MGR=M.EMPNO (+)
AND E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.DEPTNO=TEMP.DEPTNO
- 查询出薪资比ALLEN 和 Clark高的所有员工编号 ，姓名，基本工资，部门名称，领导姓名，部门人数
SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.SAL,D.DNAME,M.ENAME,
TEMP."部门人数"
FROM EMP E , EMP M , DEPT D ,(
SELECT DEPTNO DNO, COUNT(EMPNO)部门人数
FROM EMP
GROUP BY DEPTNO ) TEMP
WHERE E.SAL> ANY(SELECT SAL FROM EMP WHERE ENAME IN ('ALLEN','CLARK') )
AND E.MGR=M.EMPNO (+)
AND E.DEPTNO=D.DEPTNO
AND E.DEPTNO=TEMP.DNO
- 查询出部门经理的姓名，薪资，部门名称 ，部门人数 部门平均薪资
SELECT E.ENAME,E.SAL , D.DNAME 部门名称 ,TEMP.部门人数,TEMP.平均工资
FROM EMP E, DEPT D,(
SELECT DEPTNO,COUNT(EMPNO)部门人数, ROUND(AVG(SAL), 2)平均工资
FROM EMP
GROUP BY DEPTNO)TEMP
WHERE JOB='MANAGER'AND E.DEPTNO=TEMP.DEPTNO AND E.DEPTNO=D.DEPTNO
-
在SELECT 子句中使用子查询
- 查询出每个部门的编号，部门名称，地址，部门人数，部门平均工资在from子句中使用子查询实现
SELECT D.DEPTNO ,D.DNAME,D.LOC ,TEMP.部门人数, TEMP.平均工资 FROM EMP E ,DEPT D , ( SELECT DEPTNO ,COUNT(EMPNO)部门人数, ROUND(AVG(SAL), 1)平均工资 FROM EMP GROUP BY DEPTNO )TEMP WHERE E.DEPTNO=D.DEPTNO > 在SELECT 子句中使用子查询实现 SELECT DEPTNO ,DNAME,LOC, (SELECT COUNT(EMPNO) FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DEPTNO) 部门人数, (SELECT ROUND(AVG(SAL),1) FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DEPTNO) 平均工资 FROM DEPT
-- WITH 子句
* 临时表实际上是一个查询结果，如果一个查询结果返回的是多行多列，那么就可以将其放在FROM子句中，表示其为一张临 时表
* 使用WHITH子句定义临时表
WITH E AS (SELECT * FROM EMP ) //E 就是一张临时表
SELECT * FROM E // 查询临时表E 中的所有数据
> 查询出每个部门的编号，部门名称，地址，部门人数，部门平均工资
WITH E AS (
SELECT DEPTNO, COUNT(EMPNO )部门人数,ROUND(AVG(SAL),1)平均工资
FROM EMP
GROUP BY DEPTNO
)
SELECT D.DEPTNO, D.DNAME , D.LOC , E.部门人数, E.平均工资
FROM E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
> 查询出公司每个部门工资最高的员工的，员工信息，和部门信息
WITH EM AS (
SELECT DEPTNO , MAX(SAL)MAX
FROM EMP
GROUP BY DEPTNO
)
SELECT E.EMPNO,E.ENAME,E.JOB ,E.HIREDATE,E.SAL, D.DEPTNO ,D.DNAME
FROM EMP E , DEPT D , EM
WHERE E.DEPTNO = D.DEPTNO AND E.SAL=EM.MAX AND E.DEPTNO = EM.DEPTNO
ORDER BY DEPTNO
-- 分析函数
* 分析函数的基本语法
Analytic_function([arguments])
OVER(
[partition By 子句]
[order by 子句 [ASC|DESC] [NULLS FIRST | NULLSLAST]
[windowing 子句]
)
- Analytic_function 函数名称：类似于统计函数(count(),sum()等)，但是在此处有更多的函数支持
- OVER 子句： 为分析函数指明一个查询结果集，此语句在SELECT 自己之中使用
- partition by 子句：将一个结果集划分为N组，而后按不用的组队数据进行统计，如果省略此子句,则全部的结果集 被看作是一个组
- Order by 子句： 明确知指明数据在每个组内的排列顺序，分析函数的结果与排列顺序有关
~ [NULLS FIRST | NULLS LAST]:表示返回数据行中包含NULL值的话，null值是放在排序前还是排序末尾
- Windowing 子句： 给出在定义变化的固定数据窗口方法，分析函数将对此数据进行操作，定义分析函数作用的行集
合,集合中的数据可以看作是一个窗口。
* PARTITION BY 子句： 对数据按字段进行分区统计
> 计算每个部门的总工资
SELECT DEPTNO ,ENAME , SAL ,
SUM(SAL ) OVER(PARTITION BY DEPTNO ) 总工资 // 按照部门编号进行分区求总工资
FROM EMP
> 计算同一部门且同一岗位的总工资
SELECT DEPTNO ,ENAME , SAL ,JOB ,
SUM(SAL) OVER(PARTITION BY DEPTNO,JOB ) 总工资
FROM EMP
- ORDER BY 子句 对分区内的数据进行排序
SELECT DEPTNO ,ENAME , SAL ,JOB , HIREDATE,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL ,HIREDATE DESC ) // 工资相同，按照入职日期排序数字大的在前
FROM EMP
-
WINDOWING 分窗子句
- 分窗子句主要用于定义一个变化或固定的数据窗口方法，主要用于定义分析函数在操作行的集合-
分窗子句有两种实现方式
值域窗(RANG WINDOW)：逻辑偏移。当前分区之中，当前行的前N行，到当前行的记录集
行窗：(ROW window): 物理偏移。以排序的结果顺序计算偏移当前行的起始记录集
-
如果想要指定RANGE 或者 ROW 的偏移量，可以采用如下几种排序列
RANG | ROW 数字 PERCEDING
RANG | ROW BETWEEN UNBOUNDE PRECEDING AND CURRENT NOW
RANG | ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-
PRECEDING : 主要是设置一个偏移量，这个偏移量可以是用户设置的数字，或者是其他标记
-
BETWEEN... AND ... ： 设置一个偏移量的操作范围
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE 300 PRECEDING )总工资
FROM EMP
-
UNBOUNDED PRECEDING： 不限制偏移量大小
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )总工资
FROM EMP
-
CURRENT ROW: 表示当前行
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN 0 PRECEDING AND CURRENT ROW )总工资
FROM EMP
-
FLOWING ：如果不写此语句表示使用上N 行与当前行指定数据比较，比如编写此语句，表示当前行与下N 行数据 比较
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING )总工资
FROM EMP
-
ROWS 物理偏移 当前行与前两行
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS 2 PRECEDING )总工资
FROM EMP
- ROWS 不设置偏移量，并向下比较
SELECT DEPTNO ,EMPNO,ENAME ,SAL ,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )总工资
FROM EMP
-
-
-
分析函数范例之统计函数
利用分析函数 查询雇员编号为7369的雇员姓名，职位，基本工资，部门编号，部门人数，平均工资，最高工资，最低工资，总工资
SELECT * FROM ( SELECT EMPNO ,ENAME , JOB ,SAL ,DEPTNO , COUNT(EMPNO) OVER(PARTITION BY DEPTNO ) 部门人数, AVG(SAL) OVER(PARTITION BY DEPTNO)平均工资, MAX(SAL) OVER(PARTITION BY DEPTNO)最高工资, MIN(SAL)OVER (PARTITION BY DEPTNO)最低工资, SUM(SAL)OVER (PARTITION BY DEPTNO)总工资 FROM EMP )TEMP WHERE TEMP.EMPNO=7369;查询每个雇员的编号，姓名，基本工资，所在部门名称，部门位置，以及 部门人数，平均工资，最高工资，最低工资 ，总工资
SELECT E.EMPNO ,E.ENAME , E.JOB ,E.SAL ,D.DEPTNO ,D.DNAME ,D.LOC ,
COUNT(E.EMPNO) OVER(PARTITION BY E.DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 部门人数, ROUND(AVG(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 2) 平均工资, MAX(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)最高工资, MIN(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)最低工资, SUM(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)总工资FROM EMP E ,DEPT D
WHERE E.DEPTNO= D.DEPTNO
-
分析函数范例之等级函数(一般用于数据的排序编号)
RUNK()函数
- 根据ORDER BY 子句的排序字段，从分区查询每一行数据，按照排序生成序号，如果数据相同则生成相同序号，下一个 数据会跳号
SELECT EMPNO ,ENAME , SAL ,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL ) FROM EMPDENSE_RANK()函数
- 根据ORDER BY 子句的排序字段，从分区查询每一行数据，按照排序生成序号，如果数据相同则生成相同序号，下一个 数据不会跳号
SELECT EMPNO ,ENAME , SAL ,
DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL ) FROM EMPFIRST() 函数
- 取出DENSE_RANK()函数返回集合中的第一行数据SELECT DEPTNO ,
MAX(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC )最高工资
FROM EMPGROUP BY DEPTNO
LAST 函数
-
取出DENSE_RANK()函数返回集合中的最后一行数据
SELECT DEPTNO ,MIN(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL DESC) 最低工资
FROM EMP
GROUP BY DEPTNO
FIRST_VALUE(列) 函数
-
返回分区中的第一个值
SELECT DEPTNO ,ENAME ,SAL ,
FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)FROM EMP
WHERE DEPTNO =10;
LAST_VALUE(列) 函数
-
返回分区中的最后一个值
SELECT DEPTNO ,ENAME ,SAL ,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)FROM EMP
WHERE DEPTNO =10;
LAG(列名称[,行数字][, 默认值])
- 访问分区中指定的前N行记录,如果没有则返回默认值 SELECT DEPTNO ,ENAME ,SAL ,LAG(SAL,2,0) OVER(PARTITION BY DEPTNO ORDER BY SAL ) // 找到数据的前两行数据进行显示
FROM EMP
WHERE DEPTNO =10;
LEAD(列名称[,行数字][, 默认值])
- 访问分区中指定的后N行记录,如果没有则返回默认值
SELECT DEPTNO ,ENAME ,SAL ,
LEAD(SAL,2,0) OVER(PARTITION BY DEPTNO ORDER BY SAL ) // 找到数据的后两行数据进行显示
FROM EMP
WHERE DEPTNO =10;
ROW_NUMBER()
- 返回每组中的行号SELECT EMPNO ,ENAME , SAL ,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL ) FROM EMP -
KEEP 语句
- KEEP 语句取得保留满足条件的数据，需要在使用DENSE_RANK()函数，确定集合后才可以使用，通过FIRST或者LAST取得集合中的数据
SELECT DEPTNO ,
MAX(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC )最高工资,
MIN(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL DESC) 最低工资FROM EMP
GROUP BY DEPTNO
-
报表函数
- CUME_DIST() ： 计算某一行在分区中的相对位置
SELECT DEPTNO , ENAME,SAL ,
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL ) //按照分组中值的数量进行平均划分，相同数值，划分相同
FROM EMP
WHERE DEPTNO IN (10,20)
-
NTILE(数字)：将一个分区分为表达式的散列表示
SELECT DEPTNO ,ENAME ,SAL ,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ),
NTILE(6) OVER(PARTITION BY DEPTNO ORDER BY SAL )
FROM EMP- RATIO_TO_REPORT(表达式)：该函数计算expression/sum(expression)的值，给出相对于总数的百分比
SELECT DEPTNO ,SUM(SAL),
ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(), 3) * 100 || '%' //某部门的总工资占全公司总工资的百分比
FROM EMP
GROUP BY DEPTNO
-
行列转换
使用DECODE函数进行行列转置
SELECT DEPTNO,
SUM(DECODE(JOB , 'PRESIDENT', SAL,0))PRESIDENT,
SUM(DECODE(JOB , 'MANAGER', SAL,0))MANAGER,
SUM(DECODE(JOB , 'CLERK', SAL,0))CLERK,
SUM(DECODE(JOB , 'SALESMAN', SAL,0))SALESMAN,
SUM(DECODE(JOB , 'ANALYST', SAL,0))ANALYST,
SUM(DECODE(JOB , 'WebDe', SAL,0))WEB
FROM EMP
GROUP BY DEPTNO
使用 SELECT子查询进行 行列转置
SELECT TEMP.DEPTNO, SUM(TEMP.PRESIDENT) 总裁, SUM(TEMP.MANAGER)经理, SUM(TEMP.CLERK)办事员, SUM(TEMP.ANALYST)数据分析, SUM(TEMP.WebDe)WEB前段开发 FROM (SELECT DEPTNO, (SELECT SUM(SAL) FROM EMP WHERE JOB ='PRESIDENT' AND EMP.EMPNO=E.EMPNO) PRESIDENT, (SELECT SUM(SAL) FROM EMP WHERE JOB ='MANAGER' AND EMP.EMPNO=E.EMPNO) MANAGER, (SELECT SUM(SAL) FROM EMP WHERE JOB ='CLERK' AND EMP.EMPNO=E.EMPNO) CLERK, (SELECT SUM(SAL) FROM EMP WHERE JOB ='ANALYST' AND EMP.EMPNO=E.EMPNO)ANALYST, (SELECT SUM(SAL) FROM EMP WHERE JOB ='WebDe' AND EMP.EMPNO=E.EMPNO) WebDe FROM EMP E ) TEMP GROUP BY TEMP.DEPTNO ORDER BY TEMP.DEPTNO使用 PIVOT, UNPIVOT 函数进行 行列转置
SELECT * FROM ( SELECT DEPTNO ,JOB ,SAL FROM EMP ) PIVOT( SUM(SAL) FOR JOB IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'CLERK' AS CLERK, 'ANALYST' AS ANALYST, 'WebDe' AS WebDe ) ) ORDER BY DEPTNO使用PIVOT进行 行列转换(两个条件)
SELECT * FROM ( SELECT DEPTNO ,JOB ,SAL ,SEX FROM EMP ) PIVOT( SUM(SAL) AS 总工资, MAX(SAL) AS 最高工资 FOR （JOB ，SEX) IN ( ('PRESIDENT','男') AS 男总监, ('PRESIDENT','女') AS 女总监, ('MANAGER','男' )AS 男经理, ('MANAGER','女' )AS 女经理, ('CLERK','男' )AS 男办事员, ('CLREK','女' )AS 女办事员, ('ANALYST','男' )AS 男数据分析, ('ANALYST','女' )AS 女数据分析, ('WebDe' ,'男 ')AS 男WEB, ('WebDe' ,'女 ')AS 女WEB ) ) ORDER BY DEPTNO使用PIVOT 转换XML 数据
SELECT * FROM ( SELECT DEPTNO ,JOB ,SAL FROM EMP )PIVOT XML(
SUM (SAL) FOR JOB IN(ANY))
UNPIVOT 函数 把行列转换的表，再转回来
-
INCLUDE NULLS : 列变为行之后保留所有空数据
-
EXCLUDE NULLS : 列变行之后不保留空数据（默认）
--
WITH TEMP AS(SELECT * FROM ( SELECT DEPTNO ,JOB ,SAL FROM EMP )PIVOT(
SUM(SAL) FOR JOB IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'CLERK' AS CLERK, 'ANALYST' AS ANALYST, 'WebDe' AS WebDe ))ORDER BY DEPTNO
) SELECT * FROM TEMP
UNPIVOT EXCLUDE NULLS(
SAL_SUM FOR JOB IN ( PRESIDENT AS 'PRESIDENT', MANAGER AS 'MANAGER', CLERK AS 'CLERK', ANALYST AS 'ANALYST', WebDe AS 'WebDe' )) ORDER BY DEPTNO
-
-- 设置层次函数
-
语法格式
LEVEL...
CONNECT BY [NOCYCLE] PRIOR 连接条件
[START WITH 开始条件]
LEVEL: 可以根据数据所处的层次结构实现自动的层次编号 CONNECT BY: 指的是数据之间的连接 NOCYCLE 需要结合 CONNECT_BY_ISCYCLE伪列确定出父子节点的循环关系 START WITH：根节点数据的开始条件 -
按照领导层次 分层显示并判断根节点和叶子节点
-
LPAD('|-', LEVEL*2, ' ') ：进行分割显示
-
CONNECT_BY_ISLEAF：伪列判断根节点和叶子结点
-
CONNECT_BY_ROOT : 伪列求根节点的数据名称
-
SYS_CONNECT_BY_PATH(): 函数，按照给出的节点关系，自动的将当前节点中所有相关路径进行显示
-
CONNECT_BY_ISCYCL：伪列，判断是否存在循环，需要和 NOCYCLE关键字一起使用
-
在使用层次查询进行显示时，如果直接使用ORDER BY 子句进行排序，可能会破坏数据的组成结构
-
在层次查询中如果需要使用ORDER BY 排序，需要添加 SIBLINGS关键字 ORDER SIBLINGS BY 字段
SELECT EMPNO ,LPAD('|-', LEVEL*2, ' ') || SYS_CONNECT_BY_PATH(ENAME,'=>'),
MGR,
LEVEL,
DECODE(CONNECT_BY_ISLEAF, 0, '根节点',1,' 叶子节点') isleaf,
CONNECT_BY_ROOT ENAME
FROM EMP
CONNECT BY PRIOR EMPNO=MGR AND EMPNO!=7698 // 去掉某一个节点
START WITH MGR IS NULL
-
-- 更新及事物处理
* 在SQL 语句中，数据操作语言语言(DML)由两部分组成：查询：DQL 和 更新（增加，修改，删除）
* 增加/ 插入数据( INSERT INTO)
- 形式1 ：插入一条新数据
> INSERT INTO 表名 [(列1,列2,列3,...)] VALUES[(值1,值2,值3...)]
INSERT INTO MYEMP(EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,SEX)
VALUES (1909,'小孟','HR',1905,SYSDATE,7500,600,10,'女')
- 形式2： 插入子查询的返回结果
INSERT INTO 表名[(列1,列2,列3,...)]子查询
INSERT INTO MYEMP(EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,SEX)
SELECT * FROM EMP WHERE EMPNO=1908
> 第二种方式
INSERT INTO MYEMP SELECT * FROM EMP WHERE EMPNO =1907
* 修改/ 更新数据 (UPDATE SET)
- 数据库的更新出操作主要是指对数据表中的数据进修改
- 形式1：由用户指定要修改的字段
UPDATE 表名称 SET 字段=值 WHERE 更新条件
UPDATE MYEMP SET SAL = 65000,COMM=99999 DEPTNO=10 WHERE EMPNO =1905
- 形式2：基于子查询的更新
UPDATE 表名称 SET (COLUMN,COLUMN...)=(SELECT COLUMN,COLUMN... FROM tablename WHERE 查询条件
- 将薪资低于公司平均薪资的员工工资涨薪百分之二十
UPDATE MYEMP SET SAL=SAL*1.2 WHERE SAL<(SELECT AVG(SAL) FROM MYEMP)
- 将员工编号为7639的员工的职位，基本工资，雇佣日期更新为与7839相同
UPDATE MYEMP SET(JOB ,SAL, HIREDATE)=(SELECT JOB ,SAL ,HIREDATE FROM MYEMP WHERE EMPNO=7839)
WHERE EMPNO=7369
-
数据的删除
-
DELETE FROM 表名 WHERE 删除条件
-
删除雇员编号是7566、7312,7603的雇员信息
DELETE FROM MYEMP WHERE EMPNO IN (7566,7312,1603)
-
删除公司工资最低的雇员
DELETE FROM MYEMP WHERE SAL=(SELECT MIN(SAL) FROM MYEMP)
-
-- 事务处理
* 什么是事务：
- 在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确 保这一组SQL语句所作的操作要么完全成功执行,完成整个工作单元操作,要么一点也不执行。
主要作用：确保数据库的完整性。
* 事务的特性(ACID)
- 对一组SQL语句操作构成事务,数据库操作系统必须确保这些操作的原子性,一致性,隔离性,持久性.
1、原子性(Atomicity)
事务的原子性是指事务中包含的所有操作要么全做，要么不做,也就是说所有的活动在数据库中要么全部反映,要么全部不反映,以保证数据库的一致性。
2、一致性(Consistency)
事务的一致性是指数据库在事务操作前和事务处理后,其中数据必须满足业务的规则约束。
3、隔离性(Isolation)
隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或修改的能力,隔离性可以防止多个事务的并发执行时,由于它们的操作命令交叉执行而导致数据的不一致性。
4、持久性(Durability)
事务的持久性是指在事务处理结束后,它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。
* SESSION： 是指一个会话，每一个连接到SESSION 上的用户都通过SESSION表示，服务器依靠SESSION 来区分不同的用户 在所有的开发中SESSION 都指的是用户，每一个SESSION 都拥有独立的事务，不同的SESSION 事务是完全隔 离的
* 缓冲区： 对于每一个SESSION 而言，每一个数据库的更新操作在事务没有被提交之前都只是暂时保留在了缓冲区之中 并不会真正的向数据库发送更新命令修改数据库中的数据，这时候如果发现更新的数据有问题，可以进行回 滚操作，撤销刚才的更新命令。回滚：ROLLBACK.
* ROLLBACK: 在默认情况下，执行ROLLBACK命令意味着全部操作都要回滚，如果希望回滚到指定操作点，可以采用 SAVEPOINT设置一些保存点，这样在回滚的时候，可以ROLLBACK到指定的保存点。
* 设置保存点：
- SAVEPOINT SP_1;
* 回退到保存点：
- ROLLBACK TO SP_1;
* 提交 COMMIT ：
在默认情况下，所有的事务都不会自动提交的，需要用户手动提交，如果希望每执行一个原子性操作，事务都会跳过缓冲区，自动提交。需要设置
- 设置事务自动提交
- SET AUTOCOMMIT ON / OFF
- 事务的提交方式
1、显式提交：用COMMIT命令直接完成的提交为显式提交。其格式为：SQL> COMMIT;
2、隐式提交：用SQL命令间接完成的提交为隐式提交。
这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，
DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME
3、自动提交：若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，
这就是自动提交。其格式为：SQL> SET AUTOCOMMIT ON;
-- 锁
* 什么是锁：
- 不同的SESSION，同时操作了同一数据资源。数据在同一时刻只能被一个session操作
- 数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同 一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
- 如果是单用户的系统，那完全没有必要这个锁，就是因为有多用户并发操作，我们为了确保资源的安全性（也就 是Oracle的数据完整性和一致性）才引申出这个锁出来。Oracle 利用其锁机制来实现事务间的数据并发访问及数 据一致性。
- 加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求， 对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进 行更新操作。
- Oracle的锁机制是一种轻量级的锁定机制，不是通过构建锁列表来进行数据的锁定管理，而是直接将锁作为数据 块的属性，存储在数据块首部。
- 在 Oracle 数据库中，它并不是对某个表加上锁或者某几行加上锁， 锁是以数据块的一个属性存在的。 也就是 说， 每个数据块本身就存储着自己数据块中数据的信息，这个地方叫 ITL(Interested Transaction List)， 凡是在这个数据块上有活动的事务，它的信息就会记录在这里面供后续的操作查询，一保证事务的一致性。
- 在oracle数据库中，不存在真正意义上属于某个对象或数据的锁。oracle锁的信息是数据块的一个物理属性，而 不是逻辑上属于某个表或某个行。
* 锁的分类
- 行级锁定：
> 又称记录锁定，对当前事务中的一行数据以独占的方式进行锁定，在此事务结束之前，其他事务需要一 直等待该事务完结。
> 当用户执行了，INSERT,UPDATE, DELETE, SELECT FOR UPDATE语句时，Oracle将隐式地实现记录的锁定 这种锁定被称为排它锁
> 锁定一条数据
- SELECT * FROM myemp WHERE deptno=10 FRO UPDATE;
> 排它锁的特点是：当一个事务执行了相应的数据操作之后，如果此时事务没有提交，那么会以独占的方 式锁定这些操作的数据，其他事务需要等到该事务释放锁之后才能进行操作。
- 表级锁定： 对整张表进行数据锁定，只允许当前事务访问数据表，其他事务无法访问。
> 表级锁定需要用户明确的使用 LOCK TABLE 语句进行手工锁定
语法：LOCK TABLE 表名/ 视图名 IN 锁定模式 MODE NOWAIT
- NOWAIT: 这是一个可选项，当视图锁定一张数据表时，如果发现已经被其他事务锁定，不会 等待
- ROW SHARE： 行共享锁，在锁定期间允许其他事务对数据进行各种操作，但不允许其他事务 对同一张表进行独占操作。
- ROW EXCLUSIVE: 行排他锁，允许用户进行任何操作，但是与行共享锁不同的是他不能防止其 他事务对表进行手工锁定或独占操作。
- SHARE：共享锁，其他事务只允许进行查询操作，不能进行修改操作
- EXCLUSIVE 排它锁： 事务将以独占的方式锁定表，其他用户允许查询，但是不能修改，也不 能设置任何的锁
- SHARE ROW EXCLUSIVE: 共享排它锁，允许任何用户进行查询操作，单不允许其他用户使用共 享锁，之前所使用的 SELECT FOR UPDATE,就是共享排它锁的常见应用。
> 给myemp表添加共享锁：
- LOCK TABLE myemp IN SHARE MODE NOWAIT
> 解除锁定：
- ALTER SYSTEM KILL SESSION 'SID , SERIAL#'
- 结束一个session 也就是解除锁 需要两个标记,可以通过数据字典查询
> SESSION ID (SID): v$locked_object
SELECT session_id from v$locked_object;
> 序列号 (SERIAL#): v$session
SELECT * FROM v$session WHERE sid IN(SID值)
# 按用户和系统分可以分为自动锁和显示锁
- 自动锁(Automatic Locks):当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有必要的锁。
> 自动锁分为三种：
DML 锁
DDL 锁
systemlocks。
- 显示锁（ Manual Data Locks）
- 某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为 数据库对象设定的。
# 按锁级别分可以分为排它锁和共享锁
- 排他锁（exclusive lock，即X锁）
事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁
- 共享锁（share lock，即S锁）
共享锁使一个事务对特定数据库资源进行共享访问同时另一事务也可对此资源进行访问或获得相同共享锁。
共享锁为事务提供高并发性，但 拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。
# 按操作分可以分为 DML锁、DLL锁和 System Locks
- DML 锁：
> 用于控制并发事务中的数据操纵，保证数据的一致性和完整性。
> 它又分为：
1 .TM 锁（表级锁）
2 .TX 锁（事务锁或行级锁）
DML 语句能够自动地获得所需的 表级锁（TM）与 行级（事务）锁（TX）。
- DDL 锁: 用于保护数据库对象的结构，如表、索引等的结构定义。
- System Locks: oracle使用不同类型的系统锁来保护内部数据库和内存结构,这些机制是用户无法访问的。
-- 替代变量
* 替换变量是指在进行数据查询或更新时，由用户自己输入数据，在ORACLE中用&标记
* 由用户输入工资值进行where条件筛选(输入数字) 输入值后边不要加 ; 号
select ename,job,sal,hiredate from emp where sal>&inputsal
* 由用户输入姓名(输入字符串)
select ename,job,sal,hiredate from emp where ENAME ='&inputENAME'
* 自动大小写转换
- 转大写：
select ename,job,sal,hiredate from emp where ENAME =UPPER('&inputENAME')
- 转小写：
select ename,job,sal,hiredate from emp where ENAME =LOWER('&inputENAME')
* 模糊查询
select ename,job,sal,hiredate from emp where ENAME LIKE'%&INPUTKEYWORD'
* 输入日期格式
select ename,job,sal,hiredate from emp where HIREDATE<TO_DATE('&INPUTHIREDATE','yyyy-mm-dd')
* 输入两个值
select ename,job,sal,hiredate from emp where ENAME =UPPER('&inputENAME') AND SAL>&INPUTSAL
-
在select语句中使用替代变量
SELECT &INPUTColumName FROM EMP WHERE DEPTNO=&INPUTDEPTNO -
在FROM 子句中使用替代变量
SELECT * FROM &INPUTTABLENAME; -
在ORDER BY 子句中使用 替代变量
SELECT * FROM EMP WHERE DEPTNO=20 ORDER BY &INPUTORDERBYCOLUMN -
在GROUP BY 子句中使用替代变量
SELECT &INPUTGROUPBYCOLUMN ,COUNT(EMPNO) ,AVG(SAL) FROM EMP GROUP BY &INPUTGROUPBYCOLUMN
-- 表的创建与管理
* 数据表的概念
- 数据表是一种行与列的数据组合，也是数据库中最基本的组成单元，数据表可以理解为对现实业务的抽象结果。
- 表的最基本组成单元是字段，而每一个字段都有其数据类型。
- Oracle中的数据类型
> VARCHAR2 字符串 不超过200个字
> NUMBER 数值型 NUMBER(n) 表示整数,可以用int替代
NUMBER(n,m) 其中M表示小数位，N-M表示整数位，也可以用float替代
> DATE 日期型
> CLOB 大文本文件 最大可以保存4G 文本大小
> BLOB 二进制文件 最大4G 图片，音乐，电影等
* 表的创建
- 标的创建属于数据定义语言(DDL)，数据表和表中的列都属于数据库中的对象。
- 当发生DDL 操作的时候事务会自动提交，DDL不受事务的控制
- 语法
CREATE TABLE 用户名.表名称( 字段名称 字段类型 )
CREATE TABLE STUDENT (
SID NUMBER(10) ,
CLASSID NUMBER(5),
NAME VARCHAR2(50) DEFAULT '未添加' ,
AGE NUMBER(3),
BIRTHDAY DATE DEFAULT SYSDATE,
NOTE CLOB
)
- 插入数据
INSERT INTO STUDENT( SID,CLASSID,NAME,AGE,BIRTHDAY,NOTE)
VALUES(195021116,1910,'施歌',23,TO_DATE('1998-05-21', 'yyyy-mm-dd'),
'1910班学生，喜欢看定影');
- 用户名又称为模式名，就是当前登录用户
- 表名称以及列名称的定义要求
- 必须以字母开头
- 长度为1~30个字符
- 由大写字母，小写字母，数字0~9 ‘_’,'$','#' 组成，名称要有意义
- 在同一个用户下不能有相同的表名称
- 不能使用Oracle中的保留字。
* 表的复制
- CREATE TABLE 表名称 AS 子查询
CREATE TABLE STUDENTDUPL AS (SELECT * FROM STUDENT )
- 部分复制
CREATE TABLE EMPDUPL AS (SELECT * FROM EMP WHERE DEPTNO=10)
- 利用多表查询，子查询结果创建一个实体表
CREATE TABLE DEPARTMENT
AS
SELECT D.DEPTNO,D.DNAME,D.LOC ,COUNT(E.EMPNO)部门人数,ROUND(AVG(E.SAL), 2)平均工资,
MAX(E.SAL)最高工资,
SUM(E.sal+NVL(E.COMM, 0))总工资
FROM EMP E, DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO
GROUP BY D.DEPTNO,D.DNAME,D.LOC
ORDER BY D.DEPTNO
- 只复制表结构，不复制数据（编写一个不可能满足的条件）
CREATE TABLE EMPCOPY AS (SELECT * FROM EMP WHERE 1=2)
* 数据字典
- 在Oracle中提供了一种数据专门用于记录数据库对象信息、对象结构、管理信息、存储信息的数据表，那么这种类型的表就称为数据字典。
- 在Oracle中一共定义了两类数据字典
> 静态数据字典：这类数据字段由表和视图组成，其中视图分为三类：
user_* ：存储了所有当前用户的对象信息，所有的数据表都属于数据库对象。
all_* ： 存储了所有当前用户可以访问的对象信息（某些对象可能不属于此用户）
dba_* : 存储了数据库中所有对象的信息(数据库管理员操作)
> 动态数据字典：随着数据库的运行不断更新的数据表，一般用来保存 内存和磁盘状态，这类数据字典
都以 ‘v$’ 开头。
> 所有的数据表都属于数据库对象,每当创建一张新表时，会自动在指定的数据字典表中执行一条增加语句。
- 要想知道全部数据表对象的信息，使用user_tables这个数据字典。
SELECT * FROM USER_TABLES
* 修改表名称(rename)
- 修改表名称就相当于修改数据字典中表的信息
- 语法:
RENAME STUDENTDUPL TO STU
* 截断表
- 如果想要清空一张表中的全部数据，可以使用DELETE from 表名称，但是使用delete清空表数据的方法，需要时间较长，而且一张表所占用的资源(例如： 索引，约束等)也不会立即释放，这是就需要截断表操作，
- 表截断之后，表所占用的全部资源都将释放掉，清空所有数据，包括索引，约束等，不能进行回滚，表结构还在。
- 语法
TRUNCATE TABLE 表名称
TRUNCATE TABLE DEPARTMENT
-
删除表
-
语法
DROP TABLE 表名称
DROP TABLE DEPARTMENT
-
删除后会放入回收站(recyclebin)，可以使用 FLASHBACK闪回技术恢复
BIN$02oGYQvzS7+GX9/ywW6Niw==$0
-
直接删除不放入回收站（purge）
DROP TABLE MYEMP PURGE
-
-
闪回 FLASHBACK
-
FLASHBACK是ORACLE10G 以后提供的一种数据保障技术，为了解决误删带来的数据丢失问题，所有删除的表数据会 先默认放到回收站中，如果发现有误删可以进行恢复。
-
查看闪回回收站中的数据
- SHOW RECYCLEBIN
- SELECT * FROM RECYCLEBIN
-
闪回 FLASHBACK TO BEFORE
恢复 MYEMP表
FLASHBACK TABLE 表名 TO BEFORE 之前的操作。
FLASHBACK TABLE MYEMP TO BEFORE DROP
-
删除回收站中的表 (PURGE)
PURGE TABLE 表名
PURGE TABLE MYEMP -
清空回收站
PURGE RECYCLEBIN
-
-
修改表结构
-
为表增加字段/列
ALTER TABLE 表名 ADD (字段名 字段类型 )
ALTER TABLE STU ADD(SEX VARCHAR(10) DEFAULT '男');
ALTER TABLE STU ADD (PHOTO VARCHAR2(100) DEFAULT 'NOPHOTO.JPG')
-
删除表中的列/字段
ALTER TABLE STU DROP COLUMN PHOTO
-
设置无用列/字段 (设置成无用列后就不会显示了)
ALTER TABLE 表名 SET UNUSED(列名称)
ALTER TABLE STU SET UNUSED(SEX)
ALTER TABLE 表名 SET UNUSED COLUMN 列名称
ALTER TABLE STU SET UNUSED COLUMN NAME
-
删除无用列/字段
ALTER TABLE STU DROP UNUSED COLUMN
-
-
添加注释 (comment)
-
查看注释信息
-
表的注释信息：在Oracle中提供了一个 ‘user_tab_comments’ 的数据字典
select * from user_tab_comments // 列出所有表的注释信息
select * from user_tab_comments where table_name='STU' // 查询单个表的注释信息
-
列的注释信息：' user_col_comments '
SELECT * FROM USER_COL_COMMENTS // 查看所有列的注释信息
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='STU' // 查看某个表中列的注释信息
-
添加注释
-
添加表注释语法
COMMENT ON TABLE 表名 IS '要添加的注释信息'
> COMMENT ON TABLE STU IS '1910班学生信息' -
添加列注释语法
COMMENT ON COLUMN 表名.列名 IS '要添加的注释信息'
COMMENT ON COLUMN STU.SID IS '学生的学号'
-
-
-
设置 可见 / 不可见 字段 （12C 开始支持）
-
在设计数据库阶段，如果某些数据列的内容目前不需要使用，那么直接为其设置NULL值数据即可，但是会给开发人员带来困扰(不知道这些列用来做什么)，这时候可以将该字段设置为不可见字段/列。
-
设置为不可见字段后，表结构中不会显示该字段，也不能被使用。但是在数据字典' user_tab_column '中可以查到
-
设置不可见列语法：
ALTER TABLE STU MODIFY(SID INVISIBLE) // 将SID 列设置为不可见列
-
设置可见列语法：
ALTER TABLE STU MODIFY(SID VISIBLE) // 将SID 列设置为可见列
-
在创建表的时候也可以直接设置可见/不可见
CREATE TABLE STUDENT (
SID NUMBER(10) INVISIBLE , CLASSID NUMBER(5) VISIBLE )
-
-
表空间的管理
-
Oracle中 数据库也被成为实例(Instance),而数据库中维护的是表空间，每张表都要保存在表空间之中。
-
在数据库和磁盘数据之间存在了两种结构
逻辑结构：Oracle中所引入的结构，开发人员所操作的都只针对Oracle的逻辑结构。
物理结构：操作系统所拥有的存储结构，而逻辑结构到物理结构的转换由Oracle数据库管理系统来完成。
-
表空间是oracle数据库中最大的逻辑结构，每一个oracle数据库都有若干个表空间组成，每一个表空间又由若干个 数据文件组成，用户所创建的数据表也统一被表空间所管理。
-
表空间与磁盘上的数据对应，所以直接与物理存储结构关联，而用户所创建的表，索引，视图，子程序等被表空间 保存到了不同的区域内。
-
Oracle中有两类表空间
系统表空间：是在数据库创建时与数据库一起建立起来的，例如 System 、Sysaux表空间
非系统表空间：由具备管理员权限的数据库用户创建，主要用于保存用户数据，索引 等数据库对象，如; users,temp,undotbs1等
-
常见的表空间：
SYSTEM 表空间: 一个数据库中至少有一个表空间，即SYSTEM表空间，SYSTEM表空间主要存储数据字典，PL/SQL 程序源代码和编译后代码，例如：存储过程，函数，包，数据库触发器。
SYSAUX表空间：是SYSTEM 表空间的辅助表空间，存储了数据库的工具和可选组件
USERS 表空间：用于存储用户数据
UNDO (UNDOTBS1)表空间：用于事务的回滚，撤销
TEMP 表空间：用于存在Oracle运行中需要存放的临时数据，如排序的中间结果等。
-
创建表空间
CREATE[TEMPORARY]TABLESPACE 表空间名
[DATAFILE|TEMPFILE 表空间文件保存路径][SIZE 数字[k|m]]
[AUTOEXTEND ON|OFF] [NEXT 数字[k|m]]
[LOGGING|NOLOGGING]
-
说明：
-
DATAFILE ：保存表空间的磁盘路径，可以设置多个
-
TEMPFILE ：保存临时表空间的磁盘路径
-
SIZE：开辟空间的大小，其中有单位K字节，和M兆
-
AUTOEXTEND ON|OFF ： 是否自动扩展表空间
-
NEXT ： 定义表空间的增长量
-
LOGGING|NOLOGGING ：是否对DML 进行日志记录。
-
实例 1 创建数据表空间
CREATE TABLESPACE SDB
DATAFILE 'E:\app\Administrator\product\11.2.0\dbhome_1\tablespace_self\SDB.dbf'
SIZE 10M AUTOEXTEND ON NEXT 2M
LOGGING;
-
-
实例2 创建临时表空间
CREATE TEMPORARY TABLESPACE SHI
TEMPFILE 'E:\app\Administrator\product\11.2.0\dbhome_1\tablespace_self\TEMP01.dbf' SIZE 10M, 'E:\app\Administrator\product\11.2.0\dbhome_1\tablespace_self\TEMP02.dbf' SIZE 10M,AUTOEXTEND ON NEXT 2M
LOGGING;
- 查看表空间信息 'dba_tabspaces' 数据字典 SELECT * FROM DBA_TABLESPACES - 查看数据表空间大小 SELECT * FROM DBA_DATA_FILES - 查看临时表空间大小 SELECT * FROM DBA_TEMP_FILES - 查看表空间配额 SELECT * FROM DBA_TS_QUOTAS - 使用表空间 CREATE TABLE STUDENT ( SID NUMBER(10) , CLASSID NUMBER(5), NAME VARCHAR2(50) DEFAULT '未添加' , ) tablesapce 表空间名 -
-- 完整性约束
-
什么是完整性约束：
-
数据的完整性约束是对数据描述的某种约束条件，关系型数据模型中可以有三类完整性约束：实体完整性、参照完整 性和用户定义的完整性。
-
完整性约束是保证用户对数据库所做的修改不会破坏数据的一致性，是保护数据正确性和相容性的一种手段。
-
-
完整性约束包括：
1.主键约束(Primary) ：表示一个唯一的标识，添加了主键约束的字段不能为null，且会自动添加唯一约束。
2.唯一约束(unique) ：添加了唯一约束的字段的值是唯一的，不能有重复。
3.检查约束(check)： 用户自定义编写检查条件，保证该字段的数据不存在约束条件之外的数据。
4.非空约束(not null) --属于检查约束： 有非空约束的字段，值不能为空或者插入null值。
5.外键约束(foreign key)： 是在两张表上进行的关联约束，加入关联约束后就产生了父子关系，若表之间存在联系,则 建立外键约束可保证数据的完整性和规范性。
6.隐式约束：例如数据类型对数据的约束。
-
在一个数据库管理系统中，为了能够维护数据库的完成性，提供了以下几种支持
-
提供定义完整性约束的条件机制： 在数据表上定义规则，这些规则是数据库中的数据必须满足的语义约束条件
-
提供完整性检查方法：在更新数据库时检查更新的数据是否满足完整性约束条件
-
违约处理：DBMS发现了数据库违反了完整性约束之后，要采取违约处理行为，如拒绝(NO ACTION)执行该操作，
或者联级(CASCADE) 执行其他操作。
-
* 非空约束 (NOT NULL 简称： NK)
- 正常情况下NULL 是属性的合法数据值，如果现在某个字段不能为空必须存在数据，那么就可以依靠非空约束来进行控 制数据，这样在数据更新时，如果该字段存在NULL 值就会报错。
- 创建表时将字段设置为NOT NULL
> CREATE TABLE MEMBER (
SID NUMBER(10) NOT NULL ,
CLASSID NUMBER(5) NOT NULL,
NAME VARCHAR2(50) DEFAULT '未添加' ,
)
> 表结构
SID NOT NULL NUMBER(10)
CLASSID NOT NULL NUMBER(5)
NAME VARCHAR2(50)
> 插入数据
INSERT INTO MEMBER (SID ,CLASSID,NAME)
VALUES(NULL , NULL, 'MORTY')
- 提示信息：
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."SID")
-
唯一约束(UNIQUE 简称 UK)
-
唯一约束(UNIQUE 简称 UK)，表示在表中的某一字段数据不允许出现重复的情况
-
设置唯一约束
CREATE TABLE MEMBER (
SID NUMBER(10) UNIQUE, // 简单定义唯一约束 CLASSID NUMBER(5) , CONSTRAINT UK_MEMBER_CLASSID UNIQUE(CLASSID) // 规范定义唯一约束 NAME VARCHAR2(50) DEFAULT '未添加' ) -
插入数据
INSERT INTO MEMBER (SID ,CLASSID,NAME)VALUES( 1, 1910, 'MORTY')
INSERT INTO MEMBER (SID ,CLASSID,NAME)
VALUES( 1, 1910, 'MORTY')
-
报错：ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011572)
此时发现报错信息并不清晰，因为约束在数据库中也是一个对象，为了方便维护每一个约束都有自己的名 字，如果用户没有指定约束名，那么系统会自动分配一个约束名。
可以使用constraint关键字为约束定义名字
约束名定义规范：约束简写_表名_字段名 ：UK_MEMBER_SID
-
此时报错信息：
违反唯一约束条件 (SCOTT.UK_MEMBER_CLASSID)
唯一约束(UNIQUE)不受NULL 值影响，NULL值不算重复数据。
-
-
-
-
主键约束(PRIMARY KEY 简称： PK)
-
如果一个字段即要求不能为NULL，又要求字段值不能重复，则可以使用主键约束。
-
主键约束=非空约束+唯一约束
-
主键约束使用PRIMARY KEY 进行指定
-
设置主键约束
CREATE TABLE MEMBER (
SID NUMBER(10) PRIMARY KEY , //简写主键约束 CONSTRAINT PK_MEMBER_SID PRIMARY KEY(SID),//规范主键约束 CLASSID NUMBER(5) NOT NULL , CONSTRAINT UK_MEMBER_CLASSID UNIQUE(CLASSID), NAME VARCHAR2(50) DEFAULT '未添加' ) - 插入数据 INSERT INTO MEMBER (SID ,CLASSID,NAME) VALUES( 1, 1910, 'MORTY') INSERT INTO MEMBER (SID ,CLASSID,NAME) VALUES( NULL, 1910, 'MORTY') - 报错：1.ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."SID") 2.ORA-00001: 违反唯一约束条件 (SCOTT.PK_MEMBER_SID) > 因为主键约束是唯一约束加非空约束，所以插入数据时违反了哪个约束就会报哪个的错误。-
复合主键
-
在实际开发中，一般一张表只会设置一个主键，但是也允许为一张表设置多个主键，在符合主键中，只有两个主键 字段的内容完全一致，才会发生违反约束的错误。
-
CREATE TABLE MEMBER (
SID NUMBER(10) , CLASSID NUMBER(5) , CONSTRAINT PK_MEMBER_SID PRIMARY KEY(SID,CLASSID), AME VARCHAR2(50) DEFAULT '未添加' )-
插入数据
INSERT INTO MEMBER (SID ,CLASSID,NAME)
VALUES( 1, 1910, 'MORTY')
INSERT INTO MEMBER (SID ,CLASSID,NAME)
VALUES( 1, 191, 'MORTY')
以上两条数据都能插入成功，因为设置了复合主键，需要所设置复合主键的两个字段的值均重复才违反约束，虽然sid是一样的，但是CLASSID 不一样，是不违反约束的。
-
-
-
检查约束(CHECK 简称CK )
- 检查约束是对数据增加的条件过滤，表中的每行数据必须满足所设定的过滤条件，若不满足，不能更新成功。 - 创建检查约束 CREATE TABLE MEMBER ( SID NUMBER(10) , CLASSID NUMBER(5) , NAME VARCHAR2(50) DEFAULT '未添加' , EMAIL VARCHAR2(50) , AGE NUMBER(5) CHECK(AGE BETWEEN 0 AND 150), SEX VARCHAR2(6), CONSTRAINT PK_MEMBER_SID_CLASSID PRIMARY KEY(SID,CLASSID), CONSTRAINT UK_MEMBER_EMAIL UNIQUE (EMAIL), CONSTRAINT CK_MEMBER_SEX CHECK(SEX IN('男','女')) )-
插入数据
INSERT INTO MEMBER (SID ,CLASSID,NAME,email,age,sex)VALUES( 2, 1911, 'MORTY','1207091@QQ.COM',150,'男')
INSERT INTO MEMBER (SID ,CLASSID,NAME,email,age,sex)
VALUES( 3, 1911, 'MORTY','1207071@QQ.COM',150,'啦')
- 报错： ORA-02290: 违反检查约束条件 (SCOTT.CK_MEMBER_SEX) -
-
外键约束 (FOREIGN KEY 简称 FK)
- 创建两张表 1.成员表 包括成员基本信息 CREATE TABLE MEMBER ( MID NUMBER(10) , NAME VARCHAR2(50) DEFAULT '未添加' , email VARCHAR2(50) , age NUMBER(5) CHECK(AGE BETWEEN 0 AND 150), sex VARCHAR2(6), CONSTRAINT PK_MEMBER_SID PRIMARY KEY(MID), CONSTRAINT UK_MEMBER_EMAIL UNIQUE (email), CONSTRAINT CK_MEMBER_SEX CHECK(SEX IN('男','女')) ) 2.建议表，包括建议ID，建议内容，提建议的员工编号 CREATE TABLE ADVICE( ADID NUMBER(5), CONTENT CLOB NOT NULL, MID NUMBER(5), CONSTRAINT PK_ADID PRIMARY KEY(ADID) )-
查看员工的基本信息和每个员工提了几条建议
SELECT M.* , TEMP.建议数量
FROM MEMBER M,(SELECT MID,COUNT(ADID)建议数量 FROM ADVICE GROUP BY MID )TEMP
WHERE M.MID=TEMP.MID
-
此时我们再向advice表中插入一条数据
INSERT INTO ADVICE(ADID,CONTENT,MID)
VALUES(104,'天气好好好好啊',99)
我们发现尽管MEMBER表中不存在MID为99的成员，但是该条数据还是成功插入了，但是子表(advice)中的数据 必须参考父表(MEMBER)进行添加，脱离MEMBER表数据就没有意义了，所以此时需要添加外键。
-
在表中添加外键约束
CREATE TABLE ADVICE(
ADID NUMBER(5), CONTENT CLOB NOT NULL, MID NUMBER(5), CONSTRAINT PK_ADID PRIMARY KEY(ADID), CONSTRAINT FK_MID FOREIGN KEY(MID) REFERENCES MEMBER(MID) )
-
-
此时再次插入不合法数据
INSERT INTO ADVICE(ADID,CONTENT,MID) VALUES(104,'天气好好好好啊',99) -报错：由于不存在MID为 99的成员 所以报错：
ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项关键字
-
添加外键约束的注意事项
添加外键约束之后，如果想要删除(DELETE from)所有父表中的数据，必须先删除所有对应的子表数据，但是这 样的方法效率低，如果成千上万的子表就需要删除很长时间。所以提出了级联操作。
> 在进行外键设置的时候，设置为外键的字段在父表中必须是 主键约束 或者 唯一约束。在创建表的时候需要考虑好相应的主外键关系，不能出现互为外键的情况，会造成两张表都删除不了
如果出现可以使用强制删除命令：DROP TABLE MEMBER CASCADE CONSTRAINT
-
级联操作
1.级联删除：ON DELETE CASCADE. 当主表数据被删除的时候，子表数据同时被清理。
> 配置级联删除 (在外键后添加 ON DELETE CASCADE )CREATE TABLE ADVICE(
ADID NUMBER(5), CONTENT CLOB NOT NULL, MID NUMBER(5), CONSTRAINT PK_ADID PRIMARY KEY(ADID), CONSTRAINT FK_MID FOREIGN KEY(MID) REFERENCES MEMBER(MID) ON DELETE CASCADE ) > 删除MEMBER表中 MID 为1 的成员信息 DELETE FROM MEMBER WHERE MID=1 - 成功删除，查看两个表，发现MID为1的数据同时被删除了。
2.级联更新
当主表中的记录被删除以后，而所对应的子表记录中相应字段的内容会被设置为NULL
配置级联更新 (在外键后加 ON DELETE SET NULL)
CREATE TABLE ADVICE( ADID NUMBER(5), CONTENT CLOB NOT NULL, MID NUMBER(5), CONSTRAINT PK_ADID PRIMARY KEY(ADID), CONSTRAINT FK_MID FOREIGN KEY(MID) REFERENCES MEMBER(MID) ON DELETE SET NULL ) > 删除MEMBER表中 MID 为1 的成员信息 DELETE FROM MEMBER WHERE MID=1 - 成功删除。查看两个表，member表中 MID=1 的成员信息被删除， 而ADVICE表中， MID字段为1的记录全部被设置为NULL值 -
-
查看约束
-
可以利用 USER_CONSTRAINTS 或者 USER_CONS_COLUMNS 数据字典查看。
查看EMP 表的约束信息
1.SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP'
2.SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='EMP'
-
-
修改约束
-
增加约束
1.添加主键约束
ALTER TABLE MEMBER ADD CONSTRAINT PK_MEMBER_SID PRIMARY KEY(MID);
2.添加唯一约束
ALTER TABLE MEMBER ADD CONSTRAINT UK_MEMBER_CLASSID UNIQUE(CLASSID);-
添加检查约束
ALTER TABLE MEMBER ADD CONSTRAINT CK_MEMBER_SEX CHECK(SEX IN('男','女')); //sex
ALTER TABLE MEMBER ADD CONSTRAINT CK_MEMBER_AGE CHECK(AGE BETWEEN 1 AND 150); //age
4.添加非空约束
ALTER TABLE MEMBER MODIFY(NAME VARCHAR2(20) NOT NULL);5.添加外键约束
ALTER TABLE ADVICE ADD CONSTRAINT FK_ADVICE_MID FOREIGN KEY(MID) REFERENCES MEMBER(MID) ON DELETE CASCADE-
启用 / 禁用 约束
1.启动约束
-
如果想要启用约束需要先解决违反约束条件的数据
-
语法：
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名称
ALTER TABLE MEMBER ENABLE CONSTRAINT PK_MEMBER_SID
- 禁用约束
-
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名称
ALTER TABLE MEMBER DISABLE CONSTRAINT CK_MEMBER_SEX
-
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名称 CASCADE
当一个主表存在子表的时候，是不能直接禁用主键的
ALTER TABLE MEMBER DISABLE CONSTRAINT CK_MEMBER_SEX
会报错： ORA-02297: 无法禁用约束条件 (SCOTT.PK_MEMBER_SID) - 存在相关性
-
需要使用级联操作进行禁用
ALTER TABLE MEMBER DISABLE CONSTRAINT PK_MEMBER_SID CASCADE
-
-
删除约束
删除普通约束
ALTER TABLE MEMBER DROP CONSTRAINT CK_MEMBER_SEX删除主键约束需要设置级联
-
直接删除：ALTER TABLE MEMBER DROP CONSTRAINT PK_MEMBER_SID
报错：ORA-02273: 此唯一/主键已被某些外键引用 -
级联删除： ALTER TABLE MEMBER DROP CONSTRAINT PK_MEMBER_SID CASCADE
-
-
综合实践
-
脚本
/* 删除表 */
DROP TABLE SPORTER PURGE
DROP TABLE ITEM PURGE
DROP TABLE GRADE PURGE
PURGE RECYCLEBIN
/* 创建表 */
CREATE TABLE SPORTER(
SPORTERID NUMBER(5),
NAME VARCHAR2(30) NOT NULL,
SEX VARCHAR2(10),
DEPARTMENT VARCHAR2(30) NOT NULL,
CONSTRAINT PK_SPORTER_SPORTERID PRIMARY KEY(SPORTERID),
CONSTRAINT CK_SPORTER_SEX CHECK(SEX IN('男','女'))
)
CREATE TABLE ITEM(
ITEMID NUMBER(5),
ITEMNAME VARCHAR2(30) NOT NULL,
LOCATION VARCHAR2(30) NOT NULL,
CONSTRAINT PK_ITEM_ITEMID PRIMARY KEY(ITEMID)
)
CREATE TABLE GRADE(
SPORTERID NUMBER(5),
ITEMID NUMBER(5),
MARK NUMBER(1),
CONSTRAINT FK_GRADE_SPORTERID FOREIGN KEY(SPORTERID) REFERENCES SPORTER(SPORTERID) ON DELETE SET NULL,
CONSTRAINT FK_GRADE_ITEMID FOREIGN KEY(ITEMID) REFERENCES ITEM(ITEMID) ON DELETE SET NULL,
CONSTRAINT CK_GRADE_MARK CHECK(MARK IN (0,2,4,6))
)
/* 查看表约束 */
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='SPORTER'
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='ITEM'
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='GRADE'
-- 测试数据
INSERT INTO SPORTER(SPORTERID , NAME ,SEX,DEPARTMENT)
VALUES(19501,'小婉','女','测试部');
INSERT INTO SPORTER(SPORTERID , NAME ,SEX,DEPARTMENT)
VALUES(19502,'施歌','男','开发部');
INSERT INTO SPORTER(SPORTERID , NAME ,SEX,DEPARTMENT)
VALUES(19503,'傻戴','男','开发部');
INSERT INTO SPORTER(SPORTERID , NAME ,SEX,DEPARTMENT)
VALUES(19504,'秦汉','男','开发部');
INSERT INTO SPORTER(SPORTERID , NAME ,SEX,DEPARTMENT)
VALUES(19505,'瑶瑶','女','测试部');
SELECT * FROM SPORTER
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (001,'田径','奔月运动场');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (002,'足球','一操场');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (003,'网球','网球馆C');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (004,'乒乓球','室内乒乓球A场地');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (005,'三级跳','二操场');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (006,'篮球','室内篮球场');
INSERT INTO ITEM(ITEMID,ITEMNAME,LOCATION)
VALUES (007,'游泳','游泳馆');
SELECT * FROM ITEM
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19501,1,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19501,4,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19501,7,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19502,1,4);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19502,7,4);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19502,3,2);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19503,1,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)VALUES(19503,2,4);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19503,4,4);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19504,7,0);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19504,1,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19504,2,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19504,6,6);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19505,1,0);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19504,5,2);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)
VALUES(19505,7,4);
INSERT INTO GRADE(SPORTERID,ITEMID,MARK)VALUES(19505,4,2);
SELECT * FROM GRADE
--提交事务
COMMIT; -
-- 视图
-
视图的定义
- 视图是从一个或几个实体表(或视图)中导出的表，它与实体表不同，视图本身是一个不包含任何真是数据的虚拟表。 在数据库中只存放视图的定义，而不会存放视图对应的数据，这些数据仍然存放在原来的实体表中，所以视图表中的 数据如果发生变化，从视图中查询的数据也会随之而改变。从这个意义上讲视图就是一个窗口，通过它可以看到数据 库中感兴趣的数据及其变化。 - 视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的 视图只有逻辑定义。每次使用的时候,只是重新执行SQL。 - 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一 个视图也可以从另一个视图中产生。 - 视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。 - 视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅限查询）。 - 还有一种视图：物化视图（MATERIALIZED VIEW ），也称实体化视图，快照 （8i 以前的说法） ，它是含有数据的，占用存储空间。 - 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项) -
创建视图
- 创建视图语法
CREATE [FORCE | NOFORCE] [OR REPLACE] VIEW 视图名称[别名1，别名2...]
AS
子查询 [WITH CHECK OPTION[CONSTRAINT 约束名]] [ with read only ];
- 语法参数：
FORCE: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
NOFORCE: (默认状态) 表示要创建视图的基表必须存在，否则无法创建
REPLACE：如果存在同名的视图, 则使用新视图"替代"已有的视图。
WITH CHECK OPTION: 禁止修改where条件字段
WITH CHECK ONLY: 只读 创建的视图只能用于查询数据, 而不能用于更改数据.
- 例子：创建一张雇员工资大于2000的，雇员信息视图
给scott用户授权创建视图的权限
grant create view to scott;
创建视图
CREATE VIEW v_empinfo
AS SELECT * FROM EMP WHERE SAL > 2000- 视图数据字典 USER_VIEWS
查看所有视图信息
SELECT * FROM USER_VIEWS
-
例子： 创建一张只包含20部门雇员信息的视图
CREATE VIEW v_20deptAS
SELECT * FROM EMP WHERE DEPTNO=20
SELECT * FROM v_20dept
-
替换掉已创建的view视图
创建部门详细信息视图
CREATE OR REPLACE VIEW MYVIEW AS SELECT E.DEPTNO ,D.DNAME ,D.LOC ,COUNT(E.EMPNO)总人数,MAX(E.SAL)最高工资,MIN(E.SAL)最低工资,SUM(E.SAL)总工资,ROUND(AVG(E.SAL),2)平均工资 FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY E.DEPTNO ,D.DNAME ,D.LOC SELECT * FROM MYVIEW在创建视图语句中设置字段别名
CREATE OR REPLACE VIEW MYVIEW (部门编号,部门名称,部门位置,部门人数,部门最高工资, 部门最低工资,部门总工资,部门平均工资) AS SELECT E.DEPTNO ,D.DNAME ,D.LOC ,COUNT(E.EMPNO)总人数,MAX(E.SAL)最高工资,MIN(E.SAL)最低工资,SUM(E.SAL)总工资,ROUND(AVG(E.SAL),2)平均工资 FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY E.DEPTNO ,D.DNAME ,D.LOC SELECT * FROM MYVIEW -
在视图上进行更新
- 向视图中插入数据 . 插入成功后基表中的数据也会更新，未插入数据的列中的数据会用NULL代替，如果有非空约束， 那么就会出现错误
INSERT INTO V_DEPT20(DEPTNO,EMPNO,ENAME)
VALUES (20,1520,'张华')- 修改视图中的数据
UPDATE V_DEPT20 SET ENAME='张华2' WHERE EMPNO=1520
- 删除视图中的数据
DELETE FROM V_DEPT20 WHERE EMPNO=1520
- 创建一张复杂视图
CREATE OR REPLACE VIEW V_MYVIEW
AS
SELECT E.EMPNO,E.ENAME,E.SAL,E.JOB,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E ,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=20
在向多表查询中使用 INSERT INTO 插入数据，UPDATE SET 更新数据，DELETE FROM 删除数据时
提示：> ORA-01776: 无法通过联接视图修改多个基表
所以在复杂视图中更新数据是不能使用的
WITH CHECK OPTION CONSTRAINT 约束名
CREATE OR REPLACE VIEW V_DEPT20 AS SELECT DEPTNO,EMPNO,ENAME FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION CONSTRAINT V_DEPT20_CK // 禁止修改WHERE子句的条件字段的值 # 尝试修改： UPDATE V_DEPT20 SET DEPTNO=40 WHERE EMPNO=7369 # 报错： ORA-01402: 视图 WITH CHECK OPTION where 子句违规WITH READ ONLY 只读
CREATE OR REPLACE VIEW V_DEPT20 AS SELECT DEPTNO,EMPNO,ENAME FROM EMP WHERE DEPTNO=20 WITH READ ONLY # 尝试修改 UPDATE V_DEPT20 SET DEPTNO=40 ,EMPNO=1520,ENAME='RICK' WHERE EMPNO=7369报错：
> ORA-42399: 无法对只读视图执行 DML 操作删除视图
-
DROP VIEW 视图名称
DROP VIEW V_DEPT20
SELECT * FROM USER_VIEWS
-- 序列
* 什么是序列
- 序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。
不占用磁盘空间，占用内存。
- 其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
- 在许多数据库之中都会为用户提供一种自动增长的操作，序列可以自动的按照既定的规则实现数据的编号操作。
* 创建序列
CREATE SEQUENCE 序列名称
[INCREMENT BY 步长]
[START WITH 开始量]
[MAXVALUE 最大值 | NOMAXVALUE]
[MINVALUE 最小值 | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE 缓存大小| NOCACHE]
- 解析：
1) INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照 此步长递减的。
2) START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
3) MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于 递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
4) MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于 递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。
5) CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如 果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不 循环，达到限制值后，继续产生新值就会发生错误。
6) CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行 内存缓冲，可以改善序列的性能。
大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。
7) NEXTVAL 返回序列中下一个有效的值，每调用一次序列都会自动增长
8) CURRVAL 中存放序列的当前值,重复调用多次后序列内容不会有任何改变，NEXTVAL 应在 CURRVAL 之前指 定 ，二者应同时有效。
9) LAST_NUMBER 最后一次操纵的数值
- 创建序列
CREATE SEQUENCE MYSEQ
- 利用数据字典查询序列 USER_SEQUENCES
SELECT * FROM USER_SEQUENCES
- CURRVAL 和 NEXTVAL
> CURRVAL 中存放序列的当前值,重复调用多次后序列内容不会有任何改变，NEXTVAL 应在 CURRVAL 之前指 定 ，二者应同时有效。
# SELECT MYSEQ.CURRVAL FROM DUAL
> NEXTVAL 返回序列中下一个有效的值，每调用一次序列都会自动增长
# SELECT MYSEQ.NEXTVAL FROM DUAL
> CURRVAL 和 NEXTVAL，一定要先使用NEXTVAL，而后才可以使用CURRVAL，因为只有执行了NEXTVAL序列才进入 了一个可用的状态
- 应用序列
DROP TABLE MEMBER PURGE
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT PK_MEMBER_MIN PRIMARY KEY(MID)
)
INSERT INTO MEMBER(MID,NAME)
VALUES(MYSEQ.NEXTVAL,'张华') // 将MID 设置为 MYSEQ.NEXTVAL 自动增长
SELECT * FROM MEMBER
- 删除序列
DROP SEQUENCE 序列名
DROP SEQUENCE MYSEQ
- 创建特殊功能的序列
- 默认情况下序列的步长是 1，可以使用 INCREMENT BY 步长 进行指定步长长度
CREATE SEQUENCE 序列名 INCREMENT BY 步长
CREATE SEQUENCE MYSEQ INCREMENT BY 3
SELECT MYSEQ.NEXTVAL FROM DUAL // 1、4、7、10、
- 默认初始值是 1 ， 用户可以使用 START WITH 初始值，指定序列的初始值
CREATE SEQUENCE MYSEQ START WITH 30
- 设置缓存为100
CREATE SEQUENCE MYSEQ CACHE 100
- 不设置缓存
CREATE SEQUENCE MYSEQ NOCACHE
- 设置循环序列
CREATE SEQUENCE MYSEQ
START WITH 1
INCREMENT BY 2
MAXVALUE 10
MINVALUE 1
CACHE 5
CYCLE
# 查询序列
SELECT MYSEQ.NEXTVAL FROM DUAL
SELECT * FROM USER_SEQUENCES
- 修改序列
> STARE WHIT 值不能修改
> 最大值不能小于原来的值
ALTER SEQUENCE MYSEQ
INCREMENT BY 1
MAXVALUE 20
MINVALUE 1
CACHE 5
CYCLE
- 自动序列
> 从Oracle12C起，为了帮助用户生成数据表的流水编号，所以提供了类似DB2 mysql那样的自动增长列，这种自动增长列实际上也是一个序列，只是这个序列对象的定义由Oracle自己控制。
> Oracle自动增长列需要在定义列的时候进行设置。
> 语法：
CREATE TABLE MEMBER(
MID NUMBER GENERATED BY DEFAULT AS IDENTITY(
START WITH 1
INCREMENT BY 1
MAXVALUE 10
MINVALUE 1
CACHE 10
CYCLE
)
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT PK_MEMBER_MIN PRIMARY KEY(MID)
)
-- 同义词
* 同义词 = 表的别名
* 作用： 为不同用户下的数据表的访问带来了方便的机制。
* 现在假如说有一张数据表的名称是 scott.student，而现在又为这张表起了一个 scott的名字，以后就直接可以通过scott 访问scott.student了。
* 目前在Scott用户下有一张EMP 表，如果切换用户使用SYSTEM 用户登录，如果想访问EMP 表需要 Scott.EMP 才可以访问 EMP 表
- 例如DUAL 表 是属于SYS用户下的，而在Scott用户下也可以直接使用
* 创建同义词
CREATE [PUBLIC] SYSNONYM 同义词名称 FOR 数据库对象
> 1.所创建的同义词只能被创建同义词的用户使用
CREATE SYNONYM MYEMP FOR scott.emp;
> 2.所创建的同义词可以被所有用户使用 (公共同义词)
CREATE PUBLIC SYNONYM MYEMP FOR SCOTT.EMP;
* 查看同义词 USER_SYNONYMS 数据字典
SELECT * FROM USER_SYNONYMS
* 删除同义词
DROP SYNONYM 同义词名称
=----------------------------------------------------------------------------------------------------------------
-- 伪列
* 伪列：Oracle 中伪列就像一个表列（表中的列），但是它并没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除它们的值
* ROWID可以分为物理rowid和逻辑rowid两种。普通的表中的rowid是物理rowid，索引组织表(IOT)的rowid是逻辑rowid。
当表中有大量重复数据时，可以使用ROWID快速删除重复的记录。
* ROWID
- 数据表的每一行所保存的记录，实际上Oracle会默认为每一行记录分配一个唯一的地址编号，而这个地址编号就是通过 ROWID 进行表示的，所有的数据都可以通过ROWid进行数据定位。
- 查询DEPT表的所有信息
SELECT * FROM DEPT
SELECT ROWID,DEPTNO,DNAME ,LOC FROM DEPT // 默认列ROWID AAAR3qAAEAAAACHAAA
- ROWID的组成部分 AAAR3qAAEAAAACHAAA
数据对象号(data object number) : AAAR3q
相对文件号：(relativa file number) : AAE
数据块号：(block number)：AAAACH
数据行号：(row number):AAA
- 还原ROWID 的内容
SELECT ROWID,
DBMS_ROWID.ROWID_OBJECT(ROWID) 数据对象号,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) 相对文件号,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) 数据块号,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) 数据行号,
DEPTNO,DNAME,LOC
FROM DEPT
- 实例：删除表中的重复数据，重复的数据只保留一行
> 创建副表
CREATE TABLE MYDEPT AS SELECT * FROM DEPT
> 查询副表内容
SELECT ROWID ,DEPTNO ,DNAME ,LOC FROM MYDEPT
> 向副表中插入重复数据
INSERT INTO MYDEPT(DEPTNO,DNAME,LOC) VALUES(10,'ACCOUNTING','NEW YORK')
INSERT INTO MYDEPT(DEPTNO,DNAME,LOC) VALUES(10,'ACCOUNTING','NEW YORK')
INSERT INTO MYDEPT(DEPTNO,DNAME,LOC) VALUES(20,'RESEARCH','DALLAS')
> 查询副表中的数据
SELECT DEPTNO,DNAME,LOC, MIN(ROWID) // 查询出ROEWID 最小的数据
FROM MYDEPT
GROUP BY DEPTNO,DNAME,LOC
> 删除重复数据
DELETE FROM MYDEPT
WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM MYDEPT GROUP BY DEPTNO
-
ROWNUM
-
ROWNUMBER 伪列：对符合条件的查询结果添加一个从1开始的序列号。
-
查看ROWNUM： SELECT ROWNUM , EMPNO ,ENAME, JOB ,SAL ,HIREDATE FROM EMP
-
ROWNU作用：
取出一个查询的第一行记录
SELECT * FROM EMP WHERE ROWNUM=1;// 只能是首行 1 ，2会报错 > 取出一个查询的前N行记录SELECT * FROM EMP WHERE ROWNUM<=10; // 取出前十条记录
SELECT * FROM EMP WHERE ROWNUM<10;
- 分页显示 > 数据显示部分：主要是从数据表中选择出指定的部分数据，需要ROWNUM伪列 > 分页控制部分：留给用户控制端，用户只需要选择指定页数，那么应用就会根据用户的选择，列出指定的部分数据，相当于控制格式中的currentPage。 > 分页操作语法 SELECT * FROM( SELECT 列1,列2, rownum 别名 from 表名称 别名 WHERE ROWNUM<=(currentPage(当前页) * linesize(每页显示记录行数)) temp WHERE TEMP.ROWNUM别名>(currentPage(当前页) -1) * linesize(每页显示记录行数) ) > 取出表中的6~10行数据 SELECT * FROM ( SELECT E.EMPNO ,E.ENAME, E.JOB ,E.SAL ,E.HIREDATE,ROWNUM RN FROM EMP E WHERE ROWNUM <=10 ) TEMP WHERE TEMP.RN>5 -
-
FETCH (Oracle 12C 开始支持)
- 三种使用方式
1.取得前N行数据
FETCH FIRST 行数 ROW ONLY
取得前5行记录
SELECT * FROM EMP ORDER BY EMPNO FETCH FIRST 5 ROW ONLY2.取得指定范围的记录
OFFSET 开始位置 ROWS FETCH NEXT 个数 ROWS ONLY
取得第四条~第五条 记录
SELECT * FROM EMP ORDER BY EMPNO OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY //从初始行的下一行开始取，不算初始行3.按照百分比取记录：FETCH NEXT 百分比 percent rows only
取百分之十的数据
SELECT * FROM EMP ORDER BY EMPNO FETCH NEXT 10 PERSECT ROWS ONLY
-- 索引
-
主要进行查询优化， 索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化 DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。
-
如果SQL语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表。
-
如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行。
-
通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块。
-
几种基本索引 ：B树索引，降序索引，位图索引，函数索引
- 索引的目的
主要是减少IO,这是本质,这样才能体现索引的效率。
1 大表，返回的行数<5%
2 经常使用where子句查询的列
3 离散度高的列
4 更新键值代价低
5 逻辑AND、OR效率高
* 查看索引：
select * from user_indexes;
select * from user_ind_columns;
- B树索引
- B树索引又写为(B*Tree)是最基本的索引结构，在Oracle中默认建立的索引结构就是该类索引。一般B树索引在检索高基数 数列(该列上的重复内容较少或没有)的时候可以提供高性能的检索操作
- B树索引由分支块(branch block )和 叶块(leaf block) 组成，在树结构中位于最底层底块被称为叶块，包含每个被索引列的值和ROWID，在叶节点上面是分支块，用来导航结构，包含索引列(关键字)范围和另一索引块地址。
> 叶子结点(leaf node)：包含直接指向表中的数据行(即索引项)
> 分支节点(branch node)：包含指向索引里其他分支节点或叶子节点
> 根节点(root node)：一个B树索引只有一个根节点，是位于最顶部的分支节点。
- 索引的组成
> 索引项头(Entry Header) : 存储了行数和锁的信息
> 索引列长度和值 ： 两者需要同时出现，定义了列的长度而在长度之后保存的就是列的内容
> ROWID: 指向表中数据行的ROWID，可以提供此ROWID 找到表中完整记录。
- 创建B*Tree索引
> 最好的索引是在高基数列上使用(重复数据少或没有重复数据)
> 语法：
CREATE INDEX 索引名称 ON [用户名].表名称(字段名)
> 在SCOTT 用户下的 EMP表的 SAL字段上创建索引
CREATE INDEX EMP_SAL_IND ON SCOTT.EMP(SAL)
> 如果表中的列上设置了唯一约束或者主键约束的话也会自动查创建索引
- 创建降序索引
> 在SCOTT用户下的EMP表的HIREDATE字段上设置降序索引
CREATE INDEX EMP_HIREDATE_IND_DESC ON SCOTT.EMP(HIREDATE)
- 创建函数索引
CREATE INDEX EMP_ENAME_IND ON SCOTT.EMP(LOWER(ENAME))
-
位图索引
-
如果现在某一列上的数据都属于低基数列的时候，就可以使用位图索引来提升查询性能
-
创建位图索引
在emp表中的DEPTNO 字段上创建位图索引
CREATE BITMAP INDEX EMP_DEPTNO_IND ON SCOTT.EMP(DEPTNO)
-
-
索引的删除
- DROP INDEX 索引名称
DROP INDEX EMP_ENAME_IND
-- 权限及角色管理
- 用户在Oracle数据库之中是以对象的形式存在的，用户的操作依然需要使用导数据字典。
* 创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
[DEFAULT TABLESPACE 数据表空间名称]
[TEMPORARY TABLESPACE 临时表空间名称]
[QUOTA 数字 [k | M] | UNLIMITED ON 表空间名称]
[PROFILE 概要文件名| DEFAULT]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
* 创建用户
CREATE USER SHI IDENTIFIED BY SHI123
DEFAULT TABLESPACE SDB
QUOTA 30M ON SDB
ACCOUNT UNLOCK
PASSWORD EXPIRE
* 查看用户 使用数据字典 DBA_USERS
SELECT * FROM DBA_USERS
* 查看表空间配额
SELECT * FROM DBA_TS_QUOTAS
* 概要文件
- 概要文件是口令限制和资源限制的命名集合，是 Oracle安全策略的重要组成部分，利用概要文件可以对数据库用户进行 口令管理和资源限制。例如使用概要文可以指定口令有效期、口令校验函数、用户连接时间以及最大空闲时间等。概要 文件具有以下一些作用：
• 限制用户执行消耗资源过度的SQL操作。
• 自动断开空闲会话。
• 在大而复杂的多用户数据库系统中合理分配资源。
• 控制用户口令的使用。
- 在建立数据库时， Oracle 会自动建立名称为 DEFAULT的默认概要文件，初始的DEFAULT概要文件的所有口令及资源限制 选项值均为UNLIMITED，即未进行任何口令及资源限制。 当建立用户时， 如果不指定概要文件， 则Oracle会将DEFAULT 概要文件分配给该用户。根据用户所承担任务的不同， DBA应该建立不同的概要文件，并将概要文件分配给相应用户。 一个用户只能分配一个概要文件， 一个概要文件可以同时包含口令限制和资源限制。
- 查看default概要文件的信息
select PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT from sys.dba_profiles where PROFILE='DEFAULT';
- 修改profile文件里面的内容
alter profile default limit IDLE_TIME 10;
- 概要文件的基本参数
（1）控制资源参数
sessions_per_user : 限制用户当前会话的数量
cpu_per_session： 限制一个会话使用的cpu时间
cpu_per_call ：限制一个sql语句使用的cpu时间
logical_reads_per_session：限制每个会话读取的数据库数据块数，包括从内存和磁盘读取的总和
logical_reads_per_call：限制sql语句读取的数据库数据块数，包括从内存和磁盘读取的总和
private_sga ：sga中私有区域的大小
connect_time ：指定一个会话连接到数据库的最大时间
idle_time default：指定一个会话可以连续空闲的最长时间，单位：分钟
composite_limit：设置用户对系统资源的综合消耗。由： cpu_per_session、logical_reads_per_session、private_sga、connect_time综合决定
（2） password_parameters 参数：
failed_login_attempts: 最大错误登录次数
password_lock_time:登录失败后账户被锁天数
password_life_time:密码有效天数
password_grace_time：用户密码被中止前多少天提醒用户修改密码
password_reuse_time：用户修改密码后多少天，用户才可以再次使用原来的密码
password_reuse_max：密码被重新使用后，可修改的次数
password_verify_function：密码复杂度审计函数
password_life_time 密码多少天后失效；
password_reuse_time 密码保留的时间；
password_grace_time 设置密码失效后锁定。
- 创建一个概要文件
CREATE PROFILE SHI_PROFILE LIMIT
CPU_PER_SESSION 100000 //CPU 占用时间最长100秒
LOGICAL_READS_PER_SESSION 200 // 每个session最多允许读取2000个数据块
CONNECT_TIME 60 // 每个session最多允许连接60分钟
IDLE_TIME 30 // 空闲时间最长30分钟
SESSIONS_PER_USER 10 // 每个用户最多打开10个session
FAILED_LOGIN_ATTEMPTS 3 // 登录最多允许错误三次
PASSWORD_LOCK_TIME UNLIMITED // 密码被锁定的话会永久锁定
PASSWORD_LIFE_TIME 60 //每60天需要修改一次密码
PASSWORD_REUSE_TIME 30 // 用户修改密码后多少天，用户才可以再次使用原来的密码 旧密码30天后可以重新使用
PASSWORD_GRACE_TIME 6 // 密码失效后可以继续使用6天
- 更改PROFILE 文件
ALTER PROFILE SHI_PROFILE LIMIT
CPU_PER_SESSION 10000
PASSWORD_LIFE_TIME 30
- 查看概要文件 数据字典 dba_profiles
SELECT * FROM dba_profiles WHERE PROFILE='SHI_PROFILE';
- 创建用户并使用PROFILE文件
CREATE USER SOR IDENTIFIED BY SHI123
PROFILE SHI_PROFILE;
- 更改已有用户的PROFILE文件
ALTER USER SHI PROFILE SHI_PROFILE
- 删除profile文件
> 语法：
DROP PROFILE PROFILENAME
DROP PROFILE SHI_PROFILE
> 如果该profile文件已经被使用过指定了用户的话，直接删除会提示：
ORA-02382: 概要文件 SHI_PROFILE 指定了用户, 不能没有 CASCADE 而删除.
-需要加CASCADE 删除
DROP PROFILE SHI_PROFILE CASCADE;
-
维护用户
-
修改密码
ALTER USER 用户名 IDENTIFIED BY 密码
ALTER USER SHI IDENTIFIED BY HELLO;
-
将用户设置为锁定状态
ALTER USER 用户名 ACCOUNT LOCK
ALTER USER SHI ACCOUNT LOCK;
-
为用户解锁
ALTER USER 用户名 ACCOUNT UNLOCK
ALTER USER SHI ACCOUNT UNLOCK;
-
将用户密码设置为失效
ALTER USER 用户名 PASSWORD EXPIRE // 下次登录会强制要求修改密码
ALTER USER SHI PASSWORD EXPIRE;
-
修改用户的表空间配置
ALTER USER 表名
QUOTA 20M ON SYSTEM
QUOTA 35M ON USER
-
删除用户
DROP USER 用户名 [CASCADE]
-
-
权限管理
-
权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：Object 对象级、System 系统级、Role 角 色级。这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户"Public"（用户public是oracle预 定义的，每个用户享有这个用户享有的权限）,那么就意味作将该权限授予了该数据库的所有用户。
-
对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色 继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。
-
权限分类
1、系统权限：进行数据库资源操作的权限，例如创建数据表索引等权限
2、对象权限：维护数据库中对象的能力，由一个用户操作另外一个用户的对象
-
系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE: 拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT: 拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
-
系统权限授权命令：
系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)
授权命令：SQL> grant connect, resource, dba to 用户名1, [用户名2]...;
注: 普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可 以被回收。
语法：
GRANT 权限名 TO [用户名|角色名|public] [WITH ADMIN OPTION] // WITH ADMIN OPTION表示该用户的权限也可以授予别的用户给用户SHI 授予 CREATE SESSION 权限 (session权限可以进行登录)
GRANT CREATE SESSION TO SHI;
给用户是授予 SEQUENCE，view 权限
GRANT CREATE TABLE,CREATE SEQUENCE,CREATE VIEW TO SHI WITH ADMIN OPTION查看用户的所有权限 数据字典dba_sys_privs
SELECT * FROM DBA_SYS_PRIVS
SELECT * FROM DBA_SYS_PRIVS where grantee in('SHI')
查看当前用户有哪些权限
select * from session_privs;
-
撤销权限
语法：
REVOKE 权限名 FROM 用户名
REVOKE CREATE TABLE, CREATE VIEW FROM SHI;
注意：
-
如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，
取消该用户的系统权限并不会级联取消这些用户的相同权限。- 系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响，
系统权限可以跨用户回收，即A可以直接收回C用户的权限。
- 系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响，
-
-
对象权限
系统权限针对的是全局用户，而对象权限指的是一个用户下虽有相关对象的操作
利用用户 SHI 访问用户SCOTT 下的EMP 表
SELECT * FROM SCOTT.EMP // 发现无法访问，表或视图不存在
> 对象权限包括select, update, insert, alter, index, delete, execute , reference, all //all包括所有权限
> 对象权限授权GRANT 对象权限名 | ALL
ON 对象(哪个用户下的哪个表哪个字段)
TO [用户名|角色名|public]
[WITH GRANT OPTION]
> 给用户SHI 授予对SCOTT用户下的EMP表的 查询，插入数据权限GRANT SELECT,INSERT
ON SCOTT.EMP
TO SHI
// 此时再SHI 用户下可以查询SCOTT 用户下的EMP 表了
> 给用户SHI 授予对SCOTT用户下的dept表的部门名称的修改权限。GRANT UPDATE(DNAME) ON SCOTT.DEPT TO SHI
> 查看当前用户权限 利用user_tab_privs_recd 数据字典查看select * from user_tab_privs_recd ;
> 查看用户某一个列的权限 user_col_privs_recdselect * from user_tab_privs_recd ;
> 回收权限-
回收 用户SHI对SCOTT用户下的EMP表的 查询，插入数据权限
REVOKE SELECT ,INSERT ON SCOTT.EMP FROM SHI
-
-
角色 ROLE
- 角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。系统预定义角色是在数据库安 装后，系统自动创建的一些常用的角色。下介简单的介绍一下这些预定角色。角色所包含的权限可以用以下语句查询.
预设角色
CONNECT, RESOURCE, DBA 这些预定义角色主要是为了向后兼容。 > 查询预定角色权限 select * from role_sys_privs where role='角色名';查看角色
SELECT * FROM DBA_ROLES SELECT * FROM DBA_ROLES WHERE ROLE IN('M','S')创建一个普通角色
CREATE ROLE 角色名 > 创建一个带密码角色 CREATE ROLE 角色名 IDENTIFIED BY 密码 CREATE ROLE S IDENTIFIED BY HELLO;为角色授权
GRANT CREATE TABLE ,CREATE VIEW TO M GRANT CREATE ANY TABLE,CREATE ANY VIEW,SELECT ANY TABLE,INSERT ANY TABLE TO S > 使用角色给用户授权 GRANT 角色名1,角色名2 TO 用户名 GRANT M,S TO SHI
-- 数据库设计
- 数据库设计简介
按照规范设计，将数据库的设计过程分为六个阶段：
A、系统需求分析阶段
B、概念结构设计阶段
C、逻辑结构设计阶段
D、物理结构设计阶段
E、数据库实施阶段
F、数据库运行与维护阶段
需求分析和概念结构设计独立于任何数据库管理系统。
- 系统需求分析
1、需求分析的任务
需求分析的任务：对现实世界要处理的对象进行详细的调查，通过对原系统的了解，收集支持新系统的基础数据并对其进行处理，在此基础上确定新系统的功能。
A、调查分析用户活动
B、收集和分析需求数据，确定系统边界信息需求，处理需求，安全性和完整性需求
C、编写系统分析报告
2、需求分析的方法
需求分析有两种方法：自顶向下、自底向上
A、自顶向下
- 自顶向下方法从最上层的系统组织机构入手，采用逐层分解的方式分析系统。
- 用数据流图和数据字典描述系统
- 数据流图：描述输入数据到输出数据的变换过程
- 数据流：由一组固定成分的数据组成，代表数据的流动方向
- 处理：描述了输入数据到输出数据的变换
- 文件：用于存储数据
- 源或宿：存在于系统之外的人员或组织，表示系统输入数据的来源和输出数据的去向
B、自底向上
-
ER 模型(Entity relationship model)
- 在ER图中实体是长方形，属性是椭圆形，关系为菱形。
# 实体（entity）：
- 即数据模型中的数据对象（即数据表），用长方体来表示，每个实体都有自己的实体成员（entity member）或者说 实体对象（entity instance），例如学生实体里包括张三、李四等。
# 属性（attribute）：
- 即实体所具有的属性，例如学生具有姓名、学号、年级等属性，用椭圆形表示，属性分为唯一属性
（ unique attribute）和非唯一属性，唯一属性指的是唯一可用来标识该实体实例或者成员的属性，用下划线表示， 一 般来讲实体都至少有一个唯一属性。
# 关系（relationship）：
- 用来表现数据对象与数据对象之间的联系，例如学生的实体和成绩表的实体之间有一定的联系，每个学生都有自己的 成绩表，这就是一种关系，关系用菱形来表示。
- 关联关系有三种：
1对1（1:1）：
指对于实体集A与实体集B，A中的每一个实体至多与B中一个实体有关系；反之，在实体集B中的每个实体至多与实体集A中一个实体有关系。
1对多（1:N）：
1对多关系是指实体集A与实体集B中至少有N(N>0)个实体有关系；并且实体集B中每一个实体至多与实体集A中一个实体有关系。
多对多（M:N）：
多对多关系是指实体集A中的每一个实体与实体集B中至少有M(M>0)个实体有关系，并且实体集B中的每一个实体与实体集A中的至少N（N>0）个实体有关系。
- ER实体补充讲解：
# ER的实体还会细分为弱实体和复合实体：
- 弱实体：一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，弱实体必须依赖强实体存在，例 如上图的学生实体和成绩单实体，成绩单依赖于学生实体而存在，因此学生是强实体，而成绩单是弱实体。
- 弱实体和强实体的联系必然只有1：N或者1：1，这是由于弱实体完全依赖于强实体，强实体不存在，那么弱实体就不 存在，所以弱实体是完全参与联系的，因此弱实体与联系之间的联系也是用的双线菱形。
- 复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N联系，它由每个关联实体的主 玛组成，用长方体内加一个菱形来表示。
- ER属性补充讲解：
ER图的属性还细分为复合属性、多值属性和派生属性、可选属性，同时还有用来表示联系的属性，称为联系属性；
- 复合属性(composite attribute)：
复合属性是指具有多个属性的组合，例如名字属性，它可以包含姓氏属性和名字属性，如下图：
复合属性也有唯一属性，例如学生的所在班级属性，由于多个年级都有班级，所以单单班级属性是不唯一的，但是和 年级组成的复合属性后则可以匹配成唯一属性。
- 多值属性（multivalued attribute）：
一个实体的某个属性可以有多个不同的取值，例如一本书的分类属性，这本书有多个分类，例如科学、医学等，这个分类就是多值属性， 用双线椭圆表示。
- 派生属性(derivers attribute)：
派生属性是非永久性存于数据库的属性。派生属性的值可以从别的属性值或其他数据（如当前日期）派生出来，用虚线椭圆表示，如下图。
下面的小组人数就是典型的派生属性，随着学生实例的参加的兴趣小组变化，小组人数属性也会变化，一般来讲派生属性不存在于数据库中，而是通过相应的公式进行计算得到，如果要放到数据库中，那么隔一段时间就要进行更新，否则会出现数据错误。
- 可选属性(optional attribute)：
并不是所有的属性都必须有值，有些属性的可以没有值，这就是可选属性，在椭圆的文字后用（O）来表示，如下图的地址就是一个可选属性。
- 联系属性：
联系属于用户表示多个实体之间联系所具有的属性，一般来讲M:N的两个实体的联系具有联系属性，在1:1和1：M的实体联系中联系属性并不必要。
*数据库设计范式
- 目前关系数据库有六种范式：
第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、
巴斯-科德范式（BCNF）、 第四范式(4NF） 和 第五范式（5NF，又称完美范式）。
- 第一范式
第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项，不能是数组集合等复合属性，只能是基本数据类型。
-- PL/SQL编程
-
什么是PL/SQL？
-
PL/SQL是Oracle在关系数据库结构化查询语言SQL上扩展的一种过程化查询语言
-
SQL 与 PL/SQL编程语言之间不同的地方在于，SQL 没有变量，SQL 没有流程控制，而PL/SQL 是流程化和过程化的结合 体，而且最为重要的是，在用户执行多条SQL语句时，每条语句都是逐一发给数据库的，而PL/SQL 可以将多条语句一起 发送给数据库减少网络流量。
-
-
PL/SQL 语法：
DECLARE
-- 声明部分，例如定义变量，常量，游标
变量名 变量类型 := 初始值 变量名 emp.sal % TYPE -- 引用类型的变量 emp % rowtype -- 记录型变量BEGIN
-- 业务逻辑
-- 程序编写 SQL语句EXECTPION
-- 异常部分处理异常END ;
/
解析：
DECLARE : 声明部分，包含变量定义，用户定义的PL/SQL类型，游标，引用的函数或过程
BEGIN: 执行部分，包括变量赋值，对象初始化，条件结构，迭代结构，嵌套的PL/SQL匿名块或者是对局部或存储 PL/SQL命名块的调用。
EXCEPTION：异常部分，包含错误处理语句，该语句可以像执行部分一样使用所有项
END ： 结束部分，程序执行到END 表示结束，分号用于结束匿名块，而 /执行块程序
-
第一个 PL/SQL 程序 输出 HELLO WORLD
SET SERVEROUTPUT ON; // 首先打开屏幕显示
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO WORLD !');
END;
/
* 定义变量并赋值
DECLARE
V_NUM NUMBER ; -- 定义变量
BEGIN
V_NUM :=30; --为变量赋值
DBMS_OUTPUT.PUT_LINE('V_NUM的值是：'|| V_NUM); -- || 字符串的连接
END;
-
输入一个雇员编号，而后取得该雇员的姓名
DECLARE
V_ENO NUMBER ;V_ENAME VARCHAR2(10);BEGIN
V_ENO := &EMPNO; --从键盘接收雇员编号 SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=V_ENO; -- INTO: 把 ENAME 赋值给V_ENAME DBMS_OUTPUT.PUT_LINE('雇员编号为：' || V_ENO || '雇员的名字为：' || V_ENAME);END;
-
声明并使用变量
-
PL/SQL 是一种强类型的编程语言，所有的变量必须在声明后才可以使用，变量都需要在declare部分进行声明。
-
变量名组成可以由字母 数字 下划线，$，#等组成
-
要求所有变量必须由字母开头，不能是Oracle关键字
-
变量名长度最大为30字符。
-
不区分大小写
-
定义并赋值使用实例：
DECLARE
V_RESULT VARCHAR2(10) NOT NULL ; -- 声明变量并且此变量不能为空 V_RESULTA NUMBER:=20; --声明并赋值 V_RESULTB NUMBER; BEGIN V_RESULTB:=50; -- 给变量赋值 V_RESULT:='施歌'; DBMS_OUTPUT.PUT_LINE('V_RESULE内容： '|| V_RESULT); DBMS_OUTPUT.PUT_LINE('计算结果为： '|| (V_RESULTA+V_RESULTB)); END ;-
定义常量
V_RESULT CONSTANT NUMBER NOI NULL :=100; -- CONSTANT关键字定义常量，常量赋值后不能更改其值。
-
%type 在编写PL/SQL程序的时候，如果希望某一个变量与指定数据表中的某一列的类型一样，
则可以采用：
变量定义 表名称.字段名称%TYPE --定义一个与指定字段类型相同的变量V_ENAME EMP.ENAME%TYPE;
DECLARE
V_ENO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGINDBMS_OUTPUT.PUT_LINE('请输入雇员编号');
V_ENO:=&EMPNO;
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=V_ENO;
DBMS_OUTPUT.PUT_LINE('雇员编号为：'||V_ENO ||'的雇员姓名为：'||V_ENAME);
END;- %ROWTYPE 获取一行记录
DECLARE
V_DEPTROW DEPT%ROWTYPE ;--可以装下一行的DEPT内容 BEGIN SELECT * INTO V_DEPTROW FROM DEPT WHERE DEPTNO=10; DBMS_OUTPUT.PUT_LINE('部门编号： '||V_DEPTROW.DEPTNO ||'部门名称： '||V_DEPTROW.DNAME||'部门位置： '||V_DEPTROW.LOC ); END; -
-
PL/SQL 运算符
-
赋值运算符
变量 := 表达式
DECLARE
V_NAME VARCHAR2(10) :='施歌'; V_AGE NUMBER;BEGIN
V_AGE:=20; DBMS_OUTPUT.PUT_LINE('姓名：'||V_NAME|| '年龄： '||V_AGE); END; -
连接运算符
-
关系运算符
DECLARE
V_NUM1 NUMBER:=100;
V_NUM2 NUMBER:=20;
V_NAME VARCHAR2(50):='施歌施歌施歌';
BEGINIF V_NUM1>V_NUM2
THEN
DBMS_OUTPUT.PUT_LINE('NUM1大于NUM2');END IF ;
IF V_NAME LIKE '%施歌%'
THEN
DBMS_OUTPUT.PUT_LINE('V_NAME中包含施歌');END IF ;
END;
-
逻辑运算符
DECLARE
V_FLAG1 BOOLEAN :=TRUE;
V_FLAG2 BOOLEAN :=FALSE;
V_FLAG3 BOOLEAN;
BEGIN
IF V_FLAG1 AND (NOT V_FLAG2)
THEN
DBMS_OUTPUT.PUT_LINE('IF V_FLAG1 AND VFLAG2=TRUE');END IF;
IF V_FLAG1 OR V_FLAG2
THEN
DBMS_OUTPUT.PUT_LINE('IF V_FLAG1 AND VFLAG2=TRUE');END IF;
IF V_FLAG1 OR V_FLAG3
THEN
DBMS_OUTPUT.PUT_LINE('IF V_FLAG1 AND VFLAG3=TRUE');END IF;
IF V_FLAG1 AND V_FLAG3 IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('IF V_FLAG1 OR V_FLAG3 IS NULL = NULL ');END IF;
END ;
-
-
数据的类型划分
- Oracle中的数据类型分为四类：
标量数据类型(scalar 基本数据类型)：用于保存单个值。例如：字符串，数字，日期，布尔
复合类型(composite):复合类型可以从内部存放多种数值，类似于多个变量的集合。例如记录类型，嵌套表，
索引表，可变数组等都称为复合类型。
引用类型(reference):用于指向另一不同对象。例如：REF CURSOR, REF;
LOB 类型：大数据类型，最多可存放4G 信息，主要用来处理二进制数据。
数据类型 参数 描述
char(n) n=1 to 2000字节 定长字符串，n字节长，如果不指定长度，
缺省为1个字节长（一个汉字为2字节）
varchar2(n) n=1 to 4000字节 可变长的字符串，具体定义时指明最大长度n，
这种数据类型可以放数字、字母以及ASCII码字符集
(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。
如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度，
如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。
number(m,n) m=1 to 38
n=-84 to 127 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数
n是小数点以后的位数。
如：number(5,2)，则这个字段的最大值是99,999，如果数值超出 了位数限制就会被截取多余的位数。
如：number(5,2)，但在一行数据中的这个字段输入575.316，则 真正保存到字段中的数值是575.32。
如：number(3,0)，输入575.316，真正保存的数据是575。
date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日期，
Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。
缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。
long 无 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的 长串数据，如果要进行字符搜索就要用varchar2类型。
long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
raw(n) n=1 to 2000 可变长二进制数据，在具体定义字段的时候必须指明最大长度 n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本 文件，如Miceosoft Word文档。
raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
long raw无可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。
在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
blob
clob 无 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本 nclob 文件，如Miceosoft Word文档，以及音频、视频等非文本 文件，最大长度是4GB。
LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。
bfile 无 在数据库外部保存的大型二进制对象文件，最大长度是4GB。
这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。
Oracle 8i可以读取、查询BFILE，但是不能写入。
大小由操作系统决定。
-
数值型
数值型可以保存整数，浮点数，可以使用 NUMBER, PLS_INTEGER, BINAGR_INTEAGER, BINAGR_FLOAT, BINAGR_DOUBLENUMBER 可以表示为NUMBER(n) 和 NUMBER(N,M)
DECLARE V_X NUMBER(3); --最多只能为3位数字 V_Y NUMBER(5,2); --3位整数，两位小数 BEGIN V_X :=-500; V_Y:=200.12; DBMS_OUTPUT.PUT_LINE(V_X); DBMS_OUTPUT.PUT_LINE(V_Y); DBMS_OUTPUT.PUT_LINE(V_X + V_Y); END; > PLS_INTEGER, BINAGR_INTEAGERPLS_INTEGER, BINAGR_INTEAGER 与NUMBER相比所占用的范围更小。
在数学计算时，由于NUMBER类型保存的数据为十进制类型，所以需要先将数据转化为二进制才能进行计算，而
PLS_INTEGER, BINAGR_INTEAGER 采用的是二进制补码的形式存储，所以在性能上要比NUMBER更高。PLS_INTEGER 与 BINAGR_INTEAGER 也是有区别的，当使用BINAGR_INTEAGER 操作大于其范围的数据时，会自动将 其变为NUMBER 类型数据保存，而使用 PLS_INTEGER 操作大于其范围的数据时，会抛出异常。
# 实例 DECLARE V_X PLS_INTEGER:=10; --最多只能为3位数字 V_Y PLS_INTEGER:=20; --3位整数，2位小数 V_Z BINARY_INTEGER:=50; BEGIN DBMS_OUTPUT.PUT_LINE(V_X); DBMS_OUTPUT.PUT_LINE(V_Y); DBMS_OUTPUT.PUT_LINE(V_Z); DBMS_OUTPUT.PUT_LINE(V_X + V_Y+V_Z); END; > BINARY_FLOAT 和 BINARY_DOUBLEBINARY_FLOAT 和 BINARY_DOUBLE 比使用NUMBER 节省空间，所能表示的范围也越大，因为是直接采用二进制存储 ，这样在科学计算时性能更高。
DECLARE V_X BINARY_FLOAT:=3.1415926F; V_Y BINARY_FLOAT:=2.370F; V_Z BINARY_DOUBLE:=3.1415926D; BEGIN DBMS_OUTPUT.PUT_LINE(V_X); DBMS_OUTPUT.PUT_LINE(V_Y); DBMS_OUTPUT.PUT_LINE(V_Z); DBMS_OUTPUT.PUT_LINE(V_X + V_Y+V_Z); END;查看数据类型的最大值，最小值
# 如果超过数据类型所表示的范围就会显示 INF # 最大值： BEGIN DBMS_OUTPUT.PUT_LINE(BINARY_FLOAT_MAX_NORMAL); END; # 最小值 BEGIN DBMS_OUTPUT.PUT_LINE(BINARY_FLOAT_MIN_NORMAL); END; -
字符型
char 与 varcher2
char
固定长度，非 Unicode 字符数据，长度为 n 个字节。n 的取值范围为 1 至 8,000，存储大小是 n 个字节。char 的 SQL 2003 同义词为 character。
varchar2
可变长度，非 Unicode 字符数据。n 的取值范围为 1 至 8,000。max 指示最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加 2 个字节。所输入数据的长度可以为 0 个字符。SQL-2003 中的 varchar2 就是 char varying 或 character varying。
# char，varchar2
最多8000个英文，4000个汉字
> nchar 与 nvarcher2
# nchar
n个字符的固定长度的 Unicode 字符数据。n 值必须在 1 到 4,000 之间（含）。存储大小为两倍 n 字节。nchar 的 SQL-2003 同义词为 national char 和 national character。
# nvarchar2
可变长度 Unicode 字符数据。n 值在 1 到 4,000 之间（含）。max 指示最大存储大小为 2^31-1 字节。存储大小是所输入字符个数的两倍 + 2 个字节。所输入数据的长度可以为 0 个字符。nvarchar2 的 SQL-2003 同义词为 national char varying 和 national character varying。
# nchar，nvarchar2
可存储4000个字符，无论英文还是汉字
# char，nchar
定长，速度快，占空间大，需处理
定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，
使长度达到相应的长度
# varchar2，nvarchar2
变长，速度慢，占空间小，无需处理
变长字符数据则不会以空格填充，比较例外的是，text存储的也是可变长。
1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10 个字节，都要占去10个字节的空间,不足的自动用空格填充，所以在读取的时候可能要多次用到trim（）。
2、varchar2。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10 个字符，把它定义为 varchar2(10)是最合算的。varchar2类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个 字节用于保存实际使用了多大的长度。从空间上考虑，用varchar2合适；从效率上考虑，用char合适，关键是根据实际情 况找到权衡点。
3、NCHAR、Nvarchar2、NTEXT。
这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。
我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、 nvarchar2的长度是在1到4000之间。和char、varchar2比较起来，nchar、nvarchar2则最多存储4000个字符，不论是英文还是汉字；而char、varchar2最多能存储8000个英文，4000个汉字。
可以看出使用nchar、nvarchar2数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。
所以一般来说，如果含有中文字符，用nchar/nvarchar2，如果纯英文和数字，用char/varchar2
-
日期型
日期类型数据主要包含 日期 DATA，时间 TIMESTAMP , 时间间隔 INTEVAL
DATE 实例
DECLARE
V_DATE1 DATE :=SYSDATE;
V_DATE2 DATE :=SYSTIMESTAMP;
V_DATE3 DATE :='09-4月-2020';
BEGINDBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE1,'yyyy-mm-dd hh24:mi:ss'));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE2,'yyyy-mm-dd hh24:mi:ss'));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE3,'yyyy-mm-dd hh24:mi:ss'));
END;TIMESTAMP 精确到毫秒
TIMESTAMP 的两个子类型
1.TIMESTAMP WITH TIME ZONE 包含与格林威治时区的时区偏移量
DECLARE V_TIMESTAMP1 TIMESTAMP WITH TIME ZONE :=SYSTIMESTAMP; BEGIN DBMS_OUTPUT.PUT_LINE(V_TIMESTAMP1); END;2.TIMESTAMP WITH LOCAL TIME ZONE 不管是何种时区的数据，都使用当前数据库的时区。
DECLARE V_TIMESTAMP1 TIMESTAMP WITH LOCAL TIME ZONE :=SYSTIMESTAMP; BEGIN DBMS_OUTPUT.PUT_LINE(V_TIMESTAMP1); END; # TIMESTAMP 实例 DECLARE V_TIMESTAMP1 TIMESTAMP :=SYSDATE; V_TIMESTAMP2 TIMESTAMP :=SYSTIMESTAMP; V_TIMESTAMP3 TIMESTAMP :='09-4月-2020'; BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_TIMESTAMP1,'yyyy-mm-dd hh24:mi:ss')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_TIMESTAMP2,'yyyy-mm-dd hh24:mi:ss')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_TIMESTAMP3,'yyyy-mm-dd hh24:mi:ss')); END;INTERVAL 保存两个时间戳之间的时间间隔
INTERVAL 包含两种子类型
1. INTERVAL YEAR TO MONTHS 保存操作年和月之间的时间间隔，用户可以指定设置年的时间精度，如果不设置精度 默认保存2位 赋值字符串格式 '年-月' // '01-01' DECLARE V_INTERVAL INTERVAL YEAR(3) TO MONTH:=INTERVAL '01-01' YEAR TO MONTH; --1年零1个月 BEGIN DBMS_OUTPUT.PUT_LINE( SYSDATE+V_INTERVAL); END ;-
INTERVAL DAY TO SECOND 保存和操作天时分秒之间的时间间隔，如未设置天的精度数字，默认保存两位，如果没有设置秒的精度，默认保存6位
赋值字符串格式 '天 时:分:秒.毫秒' //'1 1:30:30.100'
DECLARE
V_INTERVAL INTERVAL DAY(3) TO SECOND:=INTERVAL '1 1:30:30.100' DAY TO SECOND; --1天零1个小时三十分钟三十秒 100毫秒BEGIN
DBMS_OUTPUT.PUT_LINE( SYSDATE+V_INTERVAL);END ;
-
-
布尔型 BOOLEAN
DECLARE
V_FLAG BOOLEAN;BEGIN
V_FLAG:=TRUE;
IF V_FLAG THEN
DBMS_OUTPUT.PUT_LINE('条件满足'); END IF;END ;
-
子类型
在某一标量类型的基础上定义更多的约束条件，从而创建一个新的类型，此时新的类型被称为子类型
语法：
SUBTYPE 子类型名称 IS 父数据类型 约束条件
定义NUMBER 的子类型
DECLARE SUBTYPE SCORE IS NUMBER(5,2) NOT NULL; V_MARK SCORE :=99.35; BEGIN DBMS_OUTPUT.PUT_LINE(V_MARK); END;定义VARCHER2 的子类型
DECLARE
SUBTYPE NAME IS VARCHAR2(10) NOT NULL; V_NAME NAME :='施歌'; BEGIN DBMS_OUTPUT.PUT_LINE(V_NAME); END;
-
程序结构
-
顺序结构，分支结构，循环结构
-
IF 的三种结构
if ... then
.........
end if;
实例
DECLARE V_COUNT NUMBER; BEGIN SELECT COUNT(EMPNO) INTO V_COUNT FROM EMP ; IF V_COUNT>10 THEN DBMS_OUTPUT.PUT_LINE('EMP 表的数据为'||V_COUNT||'条'); END IF;END ;
2.if ... then
......
else
....
end if;
实例
DECLARE
V_COUNT NUMBER;BEGIN
SELECT COUNT(DEPTNO) INTO V_COUNT FROM DEPT ; IF V_COUNT>10 THEN DBMS_OUTPUT.PUT_LINE('DEPT表数据大于10条'); ELSE DBMS_OUTPUT.PUT_LINE('DEPT表数据小于10条'); END IF;END ;
3.if ... then
......
elsif ... then
....
end if;
这里需要特别注意, 这里中间是 “ELSIF”，而不是ELSE IF 。
实例
DECLARE
V_COUNT NUMBER;
BEGIN
SELECT COUNT(EMPNO) INTO V_COUNT FROM EMP ;
IF V_COUNT>10 THEN
DBMS_OUTPUT.PUT_LINE('EMP 表的数据大于10条');ELSIF V_COUNT<10 THEN
DBMS_OUTPUT.PUT_LINE('EMP 表的数据小于10条');ELSE
DBMS_OUTPUT.PUT_LINE('EMP 表的数据等于10条'); END IF;END ;
综合例子
# 按照用户输入的员工编号，查询出员工的姓名，工资，如果工资大于3000显示高工资，大于2000显示中等工资，小 于2000小时低工资 set serveroutput on; -- 打开屏幕显示 DECLARE V_SAL NUMBER; V_ENO NUMBER; V_ENAME EMP.ENAME%TYPE; BEGIN V_ENO:=&EMPNO; SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=V_ENO; IF V_SAL>3000 THEN DBMS_OUTPUT.PUT_LINE(V_ENAME||'高工资'); ELSIF V_SAL>2000 THEN DBMS_OUTPUT.PUT_LINE(V_ENAME||'中等工资'); ELSE DBMS_OUTPUT.PUT_LINE(V_ENAME||'低工资'); END IF; END ;-
CASE
case
when <条件表达式1> then pl/sql语句1; when <条件表达式2> then pl/sql语句2; ...... when <条件表达式n> then pl/sql语句n; [else pl/sql语句n+1;]
end;
实例
# 按要求分别给不同岗位的员工涨工资DECLARE
V_JOB EMP.JOB%TYPE;
V_ENO EMP.EMPNO%TYPE;
BEGIN
V_ENO:=&EMPNO;
SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO=V_ENO;
CASE V_JOB
WHEN 'CLERK' THEN
UPDATE EMP SET SAL=SAL1.05;
WHEN 'ANALYST' THEN
UPDATE EMP SET SAL=SAL1.08;
WHEN 'SALESMAN' THEN
UPDATE EMP SET SAL=SAL1.10;
WHEN 'MANAGER' THEN
UPDATE EMP SET SAL=SAL1.20;
ELSE
DBMS_OUTPUT.PUT_LINE('雇员'||V_ENO||'工资不具备上涨资格');
END CASE;
END; -
-
循环结构
Oracle中有两种循环 FOR 和 LOOP
1.LOOP (先执行后判断，至少执行一次)
LOOP
循环语句块;
EXIT WHEN 循环结束条件; 循环结束条件修改;END LOOP；
实例
DECLARE
V_I NUMBER :=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(V_I);
EXIT WHEN V_I>3;
V_I:=V_I+1;
END LOOP ;
END;2.WHILE LOOP (先判断再执行)
WHILE(循环结束条件) LOOP 循环执行语句块； 循环结束条件修改；END LOOP；
实例
DECLARE
V_I NUMBER :=1;
BEGIN
WHILE( V_I<3)LOOP
DBMS_OUTPUT.PUT_LINE(V_I);
V_I:=V_I+1;
END LOOP ;
END;- FOR
FOR循环索引 IN[REVERSE] 循环区域下限 循环区域上限 LOOP
循环执行语句块；END LOOP;
实例
DECLARE
V_I NUMBER :=1;
BEGIN
FOR V_I IN 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(V_I);
END LOOP ;
END;实例 2
反转
DECLARE
V_I NUMBER :=1;
BEGIN
FOR V_I IN REVERSE 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(V_I);
END LOOP ;
END;
4.控制循环 EXIT 和 CONTINUE
# EXIT
DECLARE
V_I NUMBER :=1;
BEGIN
FOR V_I IN 1 .. 10 LOOP
IF V_I= 5 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(V_I);
END LOOP ;
END;
# CONTINUE
DECLARE
V_I NUMBER :=1;
BEGIN
FOR V_I IN 1 .. 10 LOOP
IF MOD(V_I,2) =0 THEN // MOD()取模
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE(V_I);
END LOOP ;
END;
5.GOTO 语句
- 无条件跳转指令
DECLARE
V_I NUMBER :=1;
BEGIN
FOR V_I IN 1 .. 10 LOOP
IF V_I =2 THEN
GOTO ENDPOINT;
END IF;
DBMS_OUTPUT.PUT_LINE(V_I);
END LOOP ;
<
DBMS_OUTPUT.PUT_LINE('GOTO 跳转结束FOR循环');
END;
-
内部程序块
DECLARE
V_X NUMBER:=30; --全局变量
BEGIN
DECLAREV_X NUMBER:=40; --局部变量 V_Y NUMBER:=20; BEGIN DBMS_OUTPUT.PUT_LINE('内部程序块输出:'|| V_X); DBMS_OUTPUT.PUT_LINE('内部程序块输出:'|| V_Y); END;DBMS_OUTPUT.PUT_LINE('全局输出:'|| V_X);
END;
-- 异常处理 EXCEPTION
* 异常处理分类
- 编译型异常 ： 程序语法出现错误所导致的异常
- 运行时异常 ： 程序没有语法问题，但是在运行时会因为程运算或者返回结果而出现错误
- EXCEPTION 子句，捕获异常 语法：
EXCEPTION
WHEN exception_Name THEN --exception_Name为异常的名字
statement1;
WHEN OTHERS THEN
statement1;
- 系统预定义的21种类型异常
系统异常名 产生原因
access_into_null 未定义对象
case_not_found case中若未包含相应的when，并且没有设置
collection_is_null 集合元素未初始化
curser_already_open 游标已经打开
dup_val_on_index 唯一索引对应的列上有重复的值
invalid_cursor 在不合法的游标上进行操作
invalid_number 内嵌的 sql 语句不能将字符转换为数字
no_data_found 使用 select into 未返回行，或应用索引表未初始化的
too_many_rows 执行 select into 时，结果集超过一行
zero_divide 除数为 0
subscript_beyond_count 元素下标超过嵌套表或varray的最大值
subscript_outside_limit 使用嵌套表或 varray 时，将下标指定为负数
value_error 赋值时，变量长度不足以容纳实际数据
login_denied pl/sql 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
not_logged_on pl/sql 应用程序在没有连接 oralce 数据库的情况下访问数据
program_error pl/sql 内部问题，可能需要重装数据字典＆ pl./sql系统包
rowtype_mismatch 主游标变量与 pl/sql 游标变量的返回类型不兼容
self_is_null 使用对象类型时，在 null 对象上调用对象方法
storage_error 运行 pl/sql时，超出内存空间
sys_invalid_id 无效的 rowid 字符串
timeout_on_resource oracle 在等待资源时超时
- 处理被除数为零异常
DECLARE
V_RESULT NUMBER;
BEGIN
V_RESULT:=10/0; -- 被除数为0
DBMS_OUTPUT.PUT_LINE(V_RESULT);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('被除数不能为0');
DBMS_OUTPUT.PUT_LINE(SQLCODE); --捕获异常
END;
- 处理赋值错误异常
DECLARE
V_VAR1 VARCHAR2(1);
V_VAR2 VARCHAR2(5):='JAVA';
BEGIN
V_VAR1 := V_VAR2;
DBMS_OUTPUT.PUT_LINE(V_VAR1);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('数据赋值错误');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
- 处理未找到数据异常
DECLARE
V_ENO EMP.EMPNO%TYPE;
V_NAME EMP.ENAME%TYPE;
BEGIN
V_ENO := &INPUTEMPNO;
SELECT ENAME INTO V_NAME FROM EMP WHERE EMPNO=V_ENO;
DBMS_OUTPUT.PUT_LINE(V_ENO||V_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('您输入的雇员不存在');
END;
- 处理接收行数超过所设置变量接收范围异常
DECLARE
V_DNO EMP.DEPTNO%TYPE;
V_NAME EMP.ENAME%TYPE;
BEGIN
V_DNO := &INPUTDEPTNO;
SELECT ENAME INTO V_NAME FROM EMP WHERE DEPTNO=V_DNO;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回的行数超出变量的接收能力范围');
END;
- 使用OTHERS 处理异常
DECLARE
V_DNO EMP.DEPTNO%TYPE;
V_NAME EMP.ENAME%TYPE;
BEGIN
V_DNO := &INPUTDEPTNO;
SELECT ENAME INTO V_NAME FROM EMP WHERE DEPTNO=V_DNO;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('返回的行数超出变量的接收能力范围');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
- 自定义异常
> 方式一 ：在声明块中声明EXCEPTION 对象，此方式有两种选择：
# 选择一： 声明异常对象并用名称来引用它，此方式使用普通的OTHERS异常，捕获用户异常。
~ 自定义异常
DECLARE
V_DATA NUMBER;
V_MYEXP EXCEPTION; -- 定义一个异常变量
BEGIN
V_DATA:=&INPUTDATA; --输入数据
IF V_DATA>10 AND V_DATA<100 THEN
RAISE V_MYEXP; --抛出异常
END IF;
EXCEPTION
WHEN V_MYEXP / OTHERS THEN -- 出现自定义异常
DBMS_OUTPUT.PUT_LINE('输入的数据不符合要求');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
~ 为自定义异常设置编码
PRAGME EXCEPTION_INIT(V_MYEXP,-20789); -- 为自定义异常设置错误编码
# 选择二： 声明异常对象并将它与有效的Oracle错误代码映射，需要编写单独的WHEN 语句块捕获。
DECLARE
V_DATA NUMBER;
V_MYEXP EXCEPTION; -- 定义一个异常变量
V_INPUT_ROWID VARCHAR2(18);
PRAGMA EXCEPTION_INIT(V_MYEXP,-01410); -- 为自定义异常设置错误编码
BEGIN
V_INPUT_ROWID :='&INPUTROWID'; --输入一个ROWID 数据
IF LENGTH( V_INPUT_ROWID)<>18 THEN
RAISE V_MYEXP; --抛出异常
END IF;
EXCEPTION
WHEN V_MYEXP THEN -- 出现自定义异常
DBMS_OUTPUT.PUT_LINE('输入的数据不符合要求');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
> 方式二： 在执行块中构建动态异常 ，通过 RAISE_APPLICATION_ERROR 函数可以构建动态异常，在出发异常时，可以 使用 -20000 ~ -20999 之间的数字，如果使用动态异常，可以在运行时指派错误信息。
~ 可以将用户自定义异常添加到异常列表，异常堆栈之中。
RAISE_APPLICATION_ERROR(错误号,错误信息,是否添加到堆栈[TRUE,FALSE])
> 实例：向DEPT表中插入数据
DECLARE
V_DNO DEPT.DEPTNO%TYPE; -- 部门编号
V_NAME DEPT.DNAME%TYPE; -- 部门名称
V_PLACE DEPT.LOC%TYPE; -- 部门位置
V_DEPTCOUNT NUMBER; -- 保存COUNT(）函数的结果
BEGIN
V_DNO := &INPUTDNO; -- 接收用户输入的部门编号
V_NAME := '&INPUTNAME'; -- 接收用户输入的部门名称
V_PLACE := '&INPUTLOC'; -- 接收用户输入的部门位置
SELECT COUNT(DEPTNO) INTO V_DEPTCOUNT FROM DEPT WHERE DEPTNO=V_DNO;
IF V_DEPTCOUNT > 0 THEN
RAISE_APPLICATION_ERROR(-20088,'此部门编号已存在，请重新输入');
ELSE
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(V_DNO,V_NAME,V_PLACE);
DBMS_OUTPUT.PUT_LINE('新部门增加成功');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- 集合
-
记录类型
- 查找员工信息
DECLARE
V_NO EMP.EMPNO%TYPE;
V_NAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_SAL EMP.SAL%TYPE;
V_HIREDATE EMP.HIREDATE%TYPE;
V_COM EMP.COMM%TYPE;
BEGIN
V_NO:=&INPUTEMPNO;
SELECT ENAME,JOB,SAL,HIREDATE,COMM INTO V_NAME,V_JOB,V_SAL,V_HIREDATE,V_COM FROM EMP WHERE EMPNO=V_NO;
DBMS_OUTPUT.PUT_LINE('员工姓名：' || V_NAME || '工作岗位：' ||V_JOB || '薪资：' || V_SAL || '入职日期：'
||TO_CHAR(V_HIREDATE,'yyyy-mm-dd') || '奖金：'||V_COM);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20017,'此员工不存在');
END;- 定义记录类型
TYPE 类型名称 IS RECORD(
成员名称 数据类型 成员名称 数据类型 ...)
定义一个记录类型的变量
DECLARE V_NO EMP.EMPNO%TYPE; TYPE EMP_TYPE IS RECORD( -- 创建记录类型 NAME EMP.ENAME%TYPE, JOB EMP.JOB%TYPE, SAL EMP.SAL%TYPE, HIREDATE EMP.HIREDATE%TYPE, COM EMP.COMM%TYPE); V_EMP EMP_TYPE ; --定义一个记录类型的变量 BEGIN V_NO:=&INPUTEMPNO; SELECT ENAME,JOB,SAL,HIREDATE,COMM INTO V_EMP FROM EMP WHERE EMPNO=V_NO; DBMS_OUTPUT.PUT_LINE('员工姓名：' || V_EMP.NAME || '工作岗位：' ||V_EMP.JOB || '薪资：' || V_EMP.SAL || '入职日期：' ||TO_CHAR(V_EMP.HIREDATE,'yyyy-mm-dd') || '奖金：'||V_EMP.COM); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20017,'此员工不存在'); END; > 定义记录类型实例 DECLARE TYPE DEPT_TYPE IS RECORD( --定义一个记录类型 DEPTNO DEPT.DEPTNO%TYPE:=80, DNAME DEPT.DNAME%TYPE, LOC DEPT.LOC%TYPE ); V_DEPT DEPT_TYPE; ---定义记录类型变量 BEGIN V_DEPT.DNAME:='NANS'; -- 给记录中的元素赋值 V_DEPT.LOC:='BeingJing'; DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPTNO||V_DEPT.DNAME||V_DEPT.LOC); --输出记录中的元素。 END;-
嵌套记录类型 (在一个记录类型中再定义另一个记录类型的成员变量)
DECLARE
TYPE DEPT_TYPE IS RECORD( --创建DEPT_TYPE 记录类型
DEPTNO DEPT.DEPTNO%TYPE:=80,
DNAME DEPT.DNAME%TYPE,
LOC DEPT.LOC%TYPE
);TYPE EMP_TYPE IS RECORD( -- 创建EMP_TYPE记录类型 NAME EMP.ENAME%TYPE, JOB EMP.JOB%TYPE, SAL EMP.SAL%TYPE, HIREDATE EMP.HIREDATE%TYPE, COM EMP.COMM%TYPE, DEPT DEPT_TYPE -- 嵌套了一个DEPT_TYPE类型的变量 ); V_EMP EMP_TYPE ; --定义一个EMP_TYPE记录类型的变量BEGIN
SELECT E.ENAME,E.JOB,E.SAL,E.HIREDATE,E.COMM,D.DEPTNO,D.DNAME,D.LOC INTO V_EMP.NAME,V_EMP.JOB,V_EMP.SAL,V_EMP.HIREDATE,V_EMP.COM, V_EMP.DEPT.DEPTNO,V_EMP.DEPT.DNAME,V_EMP.DEPT.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO(+) AND E.EMPNO=7369;DBMS_OUTPUT.PUT_LINE( '员工姓名：' || V_EMP.NAME || '工作岗位：' ||V_EMP.JOB || '薪资：' || V_EMP.SAL || '入职日期：'||TO_CHAR(V_EMP.HIREDATE,'yyyy-mm-dd') || '奖金：'||V_EMP.COM||'部门编号：'|| V_EMP.DEPT.DEPTNO ||'部门名称:'|| V_EMP.DEPT.DNAME||'部门地址：'||V_EMP.DEPT.LOC);
END;
- 利用记录类型向表中插入数据
DECLARE
TYPE DEPT_TYPE IS RECORD(DEPTNO DEPT.DEPTNO%TYPE,
DNAME DEPT.DNAME%TYPE,
LOC DEPT.LOC%TYPE
);
V_DEPT DEPT_TYPE;BEGIN V_DEPT.DEPTNO:=80; V_DEPT.DNAME:='RD'; V_DEPT.LOC:='望京'; INSERT INTO DEPT VALUES V_DEPT; --直接插入记录类型数据END;
- 利用记录更新表中的数据
DECLARE
TYPE DEPT_TYPE IS RECORD(
DEPTNO DEPT.DEPTNO%TYPE,
DNAME DEPT.DNAME%TYPE,
LOC DEPT.LOC%TYPE
);
V_DEPT DEPT_TYPE;
BEGIN
V_DEPT.DEPTNO:=80;
V_DEPT.DNAME:='RD';
V_DEPT.LOC:='恒电大厦';
UPDATE DEPT SET ROW =V_DEPT WHERE DEPTNO=V_DEPT.DEPTNO;
END;
-
索引表
- 索引表类似于程序语言中的数组，可以保存多个数据，并且通过下标来访问每一个数据。
- 索引表不需要进行初始化，可以直接为指定的索引赋值，开辟索引表的不一定必须连续
- 索引表不仅可以使用数字作为索引下标，也可以利用字符串表示索引下标，使用数字作为索引下标的时候也可以设置 负数
- 定义索引表语法：
TYPE 类型名称 IS TABLE OF 数据类型 [not null]
INDEX BY [PLS_INTEGER | BINAGR_INTEGER|VARCHAR2(长度)];
- 创建一个索引表
DECLARE
TYPE INFO_INDEX IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
V_INFO INFO_INDEX;
BEGIN
V_INFO(1):='施歌';
V_INFO(10):='JAVA';
DBMS_OUTPUT.PUT_LINE(V_INFO(1));
DBMS_OUTPUT.PUT_LINE(V_INFO(10));
END;
- EXISTS() 函数 (判断索引值是否存在)
DECLARE
TYPE INFO_INDEX IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
V_INFO INFO_INDEX;
BEGIN
V_INFO(1):='施歌';
V_INFO(10):='JAVA';
IF V_INFO.EXISTS(10) THEN
DBMS_OUTPUT.PUT_LINE(V_INFO(10));
END IF;
IF V_INFO.EXISTS(30) THEN
DBMS_OUTPUT.PUT_LINE(V_INFO(30));
ELSE
DBMS_OUTPUT.PUT_LINE('该索引值不存在');
END IF;
END;
- 创建索引类型为VARCHAR2() 类型的索引表
DECLARE
TYPE INFO_INDEX IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(30);
V_INFO INFO_INDEX;
BEGIN
V_INFO('公司名称'):='夜萤科技';
V_INFO('boss'):='施歌';
DBMS_OUTPUT.PUT_LINE(V_INFO('公司名称'));
DBMS_OUTPUT.PUT_LINE(V_INFO('boss'));
END;
- 创建 %ROWTYPE 类型的索引表
DECLARE
TYPE DEPT_INDEX IS TABLE OF DEPT%ROWTYPE INDEX BY PLS_INTEGER;
V_DEPT DEPT_INDEX;
BEGIN
V_DEPT(0).DEPTNO:=70;
V_DEPT(0).DNAME:='RICK';
V_DEPT(0).LOC:='Seattle';
V_DEPT(1).DEPTNO:=90;
V_DEPT(1).DNAME:='JERRY';
V_DEPT(1).LOC:='San Francisco';
IF V_DEPT.EXISTS(0) THEN
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).DEPTNO);
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).DNAME);
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).LOC);
END IF;
IF V_DEPT.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).DEPTNO);
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).DNAME);
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).LOC);
END IF;
END;
- 记录类型，索引类型嵌套使用，创建记录类型的索引表
DECLARE
TYPE DEPT_TYPE IS RECORD(--创建DEPT_TYPE 记录类型
DEPTNO DEPT.DEPTNO%TYPE:=80,
DNAME DEPT.DNAME%TYPE,
LOC DEPT.LOC%TYPE
);
TYPE DEPT_INDEX IS TABLE OF DEPT_TYPE INDEX BY PLS_INTEGER;
V_DEPT DEPT_INDEX;
BEGIN
V_DEPT(0).DEPTNO:=70;
V_DEPT(0).DNAME:='RICK';
V_DEPT(0).LOC:='Seattle';
V_DEPT(1).DEPTNO:=90;
V_DEPT(1).DNAME:='JERRY';
V_DEPT(1).LOC:='San Francisco';
IF V_DEPT.EXISTS(0) THEN
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).DEPTNO);
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).DNAME);
DBMS_OUTPUT.PUT_LINE(V_DEPT(0).LOC);
END IF;
IF V_DEPT.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).DEPTNO);
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).DNAME);
DBMS_OUTPUT.PUT_LINE(V_DEPT(1).LOC);
END IF;
END;
-
嵌套表
-
嵌套表是一种类似于索引表的结构，也可以用于保存多个数据，而且可以保存符合类型的数据。
-
嵌套表指的是在一个数据表定义时同时加入了其他内部表定义，这一概念是在Oracle8中引入的，他们可以使用SQL进行访问，也可以进行动态扩展。
-
