Orcale

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了ORACLE知识，便能在各种类型的机器上使用它。

Oracle主要组件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZxyO5VWM-1590654998711)(1、Oracle介绍/clipboard.png)]

数据库

磁盘上存储的数据的集合，在物理上表现为数据文件、日志文件、控制文件、联机日志、参数文件等。在逻辑上以表空间形式存在必须首先创建数据库，然后才能使用Oracle，其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

数据文件

扩展名是.DBF，用于存储数据库数据的文件数据库表和数据文件不存在一对一对应关系，

控制文件

扩展名是.CTL，是数据库启动及运行所必需的文件默认包含3个控制文件，各个控制文件内容相同

日志文件

扩展名是.LOG，它记录了对数据的所有更改信息多个日志文件组之间循环使用

数据库实例（Oracle Instance）

每个启动的数据库都对应一个数据库实例，由这个实例来访问和控制数据库为了运行数据库，一个Oracle实例有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。

2、表空间管理

表空间

表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。 每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间，一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行，表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TW8hFtoJ-1590654998716)(1、Oracle介绍/clipboard-1590139972538.png)]

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表

Oracle表空间分类

类别	举例	说 明
永久性表空间	SYSTEM，USERS	一般保存表、视图、过程和索引等的数据
临时性表空间	TEMP	只用于保存系统中短期活动的数据
撤销表空间	UNDO	用来帮助回退未提交的事务数据

一般不需要建临时和撤销表空间，除非把它们转移其他磁盘中以提高性能

什么时候创建表空间

为不同的子系统创建独立的表空间，子系统的表或者数据会单独存在某个表空间中，方便备份或转移等操作

创建表空间

语法：

CREATE TABLESPACE tablespacename

​ DATAFILE ‘filename’ [ SIZE integer [ K | M ] ]

​ [ AUTOEXTEND [ OFF | ON ] ] ;

}

示例：

–创建表空间 create tablespace HrSys datafile ‘e:\data\HrSys.dbf’ – 文件位置 先创建文件夹 size 100m – 初始大小 autoextend on – 开启自动增长 next 10m; --每次增长大小 LOGGING – 记录日志

修改表空间

调整表空间的大小

–方法一：更改数据文件的大小 ALTER DATABASE DATAFILE ‘e:\data\HrSys.dbf’ RESIZE 80M; --方法二：向表空间内添加数据文件 ALTER TABLESPACE HrSys ADD DATAFILE ‘e:\data\HrSys02.dbf’ SIZE 20M AUTOEXTEND ON;

删除表空间

删除表空间前先备份再删除，通过DROP TABLESPACE命令创建表空间

– 只删除表空间 不删除数据文件 DROP TABLESPACE HrSys /* =========================================================== | 删除表空间时同时删除里面的数据文件 ============================================================ */ DROP TABLESPACE HrSys INCLUDING CONTENTS AND DATAFILES

3、用户管理

用户是在实例下建立的。不同实例可以建相同名字的用户。SYS和SYSTEM用户都是Oracle 的系统用户，它们都使用SYSTEM表空间，SYS拥有更大的权限。

	SYS用户	SYSTEM用户
地位	Oracle的一个超级用户	Oracle默认的系统管理员，拥有DBA权限
作用	主要用来维护系统信息和管理实例	通常用来管理Oracle数据库的用户、权限和存储等
登录身份	只能以SYSDBA或SYSOPER角色登录	只能以Normal方式登录

创建用户

– 创建用户 CREATE user Hr identified BY hr123 – 创建用户 Hr 并指定表空间为HrSys CREATE user A_Hr identified BY hr123 DEFAULT TABLESPACE HrSys 修改密码 ALTER USER A_Hr IDENTIFIED BY hr123456 – 删除用户 DROP USER A_Hr – 删除用户以及用户下的对象 DROP USER A_Hr CASCADE

权限和角色

权限指执行特定类型SQL 命令或访问其他对象的权利，用户必须赋予相应的权限

权限分为系统权限和对象权限，系统权限允许用户执行某些数据库操作，对象权限允许用户对某一特定对象执行特定的操作。

为了简化权限管理，引入了角色的概念，角色是具有名称的一组权限的组合，常用系统预定义角色：

CONNECT：是授予最终用户的典型权利，最基本的，包括以下权限

ALTER SESSION --修改会话

CREATE CLUSTER --建立聚簇

CREATE DATABASE LINK --建立数据库链接

CREATE SEQUENCE --建立序列

CREATE SESSION --建立会话

CREATE SYNONYM --建立同义词

CREATE VIEW --建立视图

RESOURCE：更为可靠和正式的用户，是授予开发人员的，包括以下权限

CREATE CLUSTER --建立聚簇

CREATE PROCEDURE --建立过程

CREATE SEQUENCE --建立序列

CREATE TABLE --建表

CREATE TRIGGER --建立触发器

CREATE TYPE --建立类型

DBA：数据库管理员角色，拥有管理数据库的最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除。

对于普通用户：授予connect, resource权限。对于DBA管理用户：授予connect，resource, dba权限。

语法：

–分配权限或角色

​ GRANT privileges or role TO user;

​ --撤销权限或角色

​ REVOKE privileges or role FROM user;

–分配权限或角色 GRANT connect, resource TO Hr ; --撤销权限或角色 REVOKE resourceor FROM Hr ;

自定义角色并给用户赋指定的角色

**1.**建一个角色

create role role1;

2.授权给角色

grant create any table,create procedure to role1;

3.授予角色给用户

grant role1 to Hr;

4.查看角色所包含的权限

select * from role_sys_privs;

5、锁住用户

alter user liumingcheng account lock;

6.启用用户

alter user liumingcheng account unlock;

4、Oracle数据类型

Oracle中常用的数据类型有字符类型、数字类型、日期类型、大对象类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ot8yuGIX-1590654998722)(1、Oracle介绍/clipboard-1590140031106.png)]

字符型：

varchar2() 范围为1-4000，如果你超出了这个范围，Oracle会报错。

char表示固定长度，比如 char(10),存小于或等10 的任意大小文件，在数据库中这个都占 10 个空间。

varchar不推荐使用因为它对中文字符的规则是占两个字节，其他数据库占个1字节，这样使用不方便日后进行数据库的转换。

日期型：

在 Oracle中，表示日期统一使用 Date类型后面不用跟范围它所能 。

大型字符集合

blog：主要用来存放非字符的文件，比如音频视等二进制文件，最大4G。

clob：主要用来存放文本文件，最大能存4G 的字节

数字类型：

在 Oracle中，表示数字的类型为 number，它有三种写法：

​ 1）、 number：能够表示任意的何数字

​ 2）、 number(p,s) ：其中， p代表精度， s代表小数点的位

​ 3）、 number§：相当于表示的是 number(p,0)的写法，即表示是整数的写法。注意：第二种写法中， s有可能为正，也有可能为负，如果是正数就代表保留小数点后面，代表保留小数点后面s位，如果小数点后面的字长度大于s位，那么多部分进行四舍五入,如果为负，则表示小数点左边 s个数字全部以 0替换 ,然后四舍五入必须要是整数；

不建议使用VARCHAR、 INTEGER、FLOAT、DOUBLE等类型，非SQL标准

5、表的管理

建表

语法：

Create table 表名（

字段1 数据类型 [default 默认值],

字段2 数据类型 [default 默认值],

…

字段n 数据类型 [default 默认值]

）;

范例：创建并操作stuInfo表

/* =========================================================== | 示例1：创建stuInfo表 ============================================================ / CREATE TABLE stuInfo /-创建学员信息表-/ ( stuName VARCHAR2(20) NOT NULL, --学员姓名,非空（必填） stuNo CHAR(6) NOT NULL, --学号,非空（必填） stuAge NUMBER(3,0) NOT NULL, --年龄,非空（必填） stuID NUMERIC(18,0), --身份证号，代表18位数字,小数位数为0 stuSeat NUMERIC(2,0) --座位号 ); / =========================================================== | 补充：更改stuName列的定义，使此列能容纳25个字符 ============================================================ / ALTER TABLE stuInfo MODIFY (stuName VARCHAR2 (25)); / =========================================================== | 补充：向stuInfo 表添加stuTel_no 和stuAddress 两个列 ============================================================ / ALTER TABLE stuInfo ADD (stuTel_no VARCHAR2 (12), stuAddress VARCHAR2(20)); / =========================================================== | 补充：从stuInfo表中删除列 ============================================================ / ALTER TABLE stuInfo DROP COLUMN stuTel_no; --删除stuTel_no列 --或者 ALTER TABLE stuInfo DROP (stuTel_no,stuSeat);–删除stuTel_no和stuSeat列 / =========================================================== | 补充：删除stuInfo表 ============================================================ / DROP TABLE stuInfo; / =========================================================== | 补充：插入stuInfo表数据 ============================================================ / INSERT INTO stuInfo VALUES (‘张三’，1，18，null，1); INSERT INTO stuInfo VALUES (‘李四’，2，20，null，2); INSERT INTO stuInfo VALUES (‘王五’，3，15，null，3); INSERT INTO stuInfo VALUES (‘张三’，4，18，null，4); INSERT INTO stuInfo VALUES (‘张三’，5，20，null，5); COMMIT; / =========================================================== | 选择无重复的行 ============================================================ / SELECT DISTINCT stuName,stuAge FROM stuInfo; / =========================================================== | 按照姓名升序，如果姓名相同按照年龄降序排序 ============================================================ / SELECT stuNo,stuName, stuAge FROM stuInfo WHERE stuAge>17 ORDER BY stuName ASC, stuAge DESC; / =========================================================== | 使用列别名 ============================================================ / SELECT stuName as “姓 名”,stuAge as “年 龄”, stuID as 身份证号 FROM StuInfo; / =========================================================== | 利用现有的表创建新表 ============================================================ / CREATE TABLE newStuInfo1 AS SELECT * FROM StuInfo; CREATE TABLE newStuInfo2 AS SELECT stuName,stuNo,stuAge FROM StuInfo; CREATE TABLE newStuInfo3 AS SELECT * FROM StuInfo WHERE 1=2; SELECT * FROM newStuInfo1; SELECT * FROM newStuInfo2; SELECT * FROM newStuInfo3; / =========================================================== | 查看表中行数 ============================================================ / SELECT COUNT() FROM stuInfo; SELECT COUNT(1) FROM stuInfo; /* =========================================================== | 取出stuName,stuAge列不完全重复记录 ============================================================ / SELECT stuName,stuAge FROM stuInfo GROUP BY stuName,stuAge HAVING(COUNT(stuAge||stuAge) <2); / =========================================================== | 删除stuName,stuAge列重复的行(保留一行) ============================================================ / DELETE FROM stuInfo WHERE ROWID NOT IN( SELECT MAX(ROWID) FROM stuInfo GROUP BY stuName,stuAge HAVING(COUNT(stuAge||stuAge)>1) UNION SELECT max(ROWID) FROM Stuinfo GROUP BY stuName,stuAge HAVING(COUNT(stuAge||stuAge)=1) ); SELECT * FROM stuInfo; / =========================================================== | 查看当前用户所有数据量>100万的表的信息 ============================================================ */ SELECT * FROM user_all_tables a WHERE a.num_rows>1000000;

6、伪表伪列

伪表： dual

概念：一个里面没有任何数据，并且不允许你进行插入和删除操作的Oracle自带的表，这个

表里只有一个较多dummy的字符段并且里面没有任何数据，这个表的最大作用是用来进行一些相关函数以及运算的测试表。

伪列ROWID和ROWNUM

Oracle中伪列就像一个表列，但是它并没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除它们的值，常用的伪列有ROWID和ROWNUM。

ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行

/* =========================================================== | 可以使用SELECT语句查看ROWID值 ============================================================ / SELECT ,ename FROM Scott.emp WHERE ename=‘SMITH’; / =========================================================== | 使用ROWNUM从EMP表中提取10条记录并显示序号 ============================================================ / SELECT emp.,rownum FROM Scott.emp WHERE ROWNUM<11;

我们可以根据rownum来取结果集的前几行，比如前5行，但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想 实现我们的需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。

— 查询前5条数据 select rownum rm, a.* from emp a where rownum <6 /* =========================================================== | 使用ROWNUM从进行分页查询 ============================================================ — 第一种写法： select * from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5 —第二种写法： select * from (select rownum r ,emp.* from emp) b where b.r >5 and b.r <11

7、SQL 操作符

Oracle 支持的 SQL 操作符分类

算术操作符、逻辑操作符、连接操作符、比较操作符、集合操作符。

集合操作符

UNION（联合）：将多个结果合并在一起显示出来，默认去掉重复的数据。

UNION ALL（联合所有）：将多个结果合并全部显示出来，不管是不是重复。

INTERSECT（交集）：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

MINUS（减集）：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

/* =========================================================== | 补充 ：创建退休员工表 ============================================================ / – 复制表结构和数据 CREATE TABLE retireEmp AS SELECT * FROM employee; – 修改列名 ALTER TABLE retireEmp RENAME COLUMN empno TO rempno; – 修改数据 UPDATE retireEmp SET rempno=8888 WHERE rempno=7788; / =========================================================== | union操作符 查询退休表和员工表中重复的数据 ============================================================ / SELECT empno FROM emp UNION SELECT rempno FROM retireEmp; / =========================================================== | union+order by 通过order by 可以改变默认排序 ============================================================ / SELECT empno FROM emp UNION SELECT rempno FROM retireEmp ORDER BY empno; / =========================================================== | INTERSECT操作符 ============================================================ / SELECT empno FROM emp INTERSECT SELECT rempno FROM retireEmp; / =========================================================== | MINUS操作符 ============================================================ / SELECT empno FROM emp MINUS SELECT rempno FROM retireEmp; --SQL Server语句 SELECT * FROM employee C WHERE NOT EXISTS (SELECT * FROM retireEmp G WHERE C.empno = G.rempno ) / =========================================================== | 连接操作符 ============================================================ */ SELECT job||’_’||ename FROM emp;

8、SQL函数

SQL函数

分类：

1、单行函数：每一行只返回一个值，可以出现在 SELECT 子句中和 WHERE 子句中，常见的函数有：

日期函数、数字函数、字符函数、转换函数、其他函数

2、分组函数：根据分组的情况，返回每组里的一个结果值，可以在使用ORDER BY 和 HAVING子句中使用

3、分析函数：分析函数根据一组行来计算聚合值，分析函数为每组记录返回多个行

转换函数：

/* =========================================================== | TO_CHAR()函数 ============================================================ / SELECT TO_CHAR（sysdate,‘YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS’） FROM dual; / =========================================================== | TO_CHAR()函数 ============================================================ / SELECT to_char（1210.7, ‘$9,999.00’） FROM dual; / =========================================================== | TO_DATE()函数 ============================================================ / SELECT to_date（‘2013-07-13’ , ‘yyyy-mm-dd’） FROM dual; / =========================================================== | TO_NUMBER()函数 ============================================================ */ SELECT SQRT（to_number（‘100’）） FROM dual;

其他函数

/* =========================================================== | 其他函数 NVL() NVL2() DECODE（） ============================================================ */ SELECT ename, sal+NVL(comm,0) sal1, NVL2(comm,sal+comm,sal) sal2, DECODE(to_char(hiredate,‘MM’),‘01’,‘一月’,‘02’,‘二月’,‘03’,‘三月’, ‘04’,‘四月’,‘05’,‘五月’,‘06’,‘六月’,‘下半年’) mon FROM employee;

分析函数

/* =========================================================== | 分析函数 RANK() DENSE_RANK() ROW_NUMBER() ============================================================ */ SELECT ename, deptno, sal, --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同, --且和下一条不同记录的排名之间空出排名 1 1 3 3 5 6 RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC) “RANK”, --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号相同, --且和下一条不同记录的排名之间不空出排名 1 1 2 3 3 4 DENSE_RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC) “DENSE_RANK”, --按照每个部门分组,对薪水从大到小排序,每个部门序号从1开始,同一个部门相同薪水序号继续 --递增，顺序排名 1 2 3 4 5 6 ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY sal DESC) “ROW_NUMBER” FROM emp; – 不分组的的分析函数 查询emp表中所有的工资排名 SELECT ename, sal, RANK( ) OVER (order by sal desc) “RANK” from emp

9、事务控制语言

事务控制语言

用于事务控制的语句，常用的关键字COMMIT、ROLLBACK、SAVEPOINT、ROLLBACK TO <SavePoint_Name>

/* =========================================================== | 事务控制语句应用举例 ============================================================ */ select * FROM dept; --执行步骤一：创建dept 表 CREATE TABLE DEPT ( DEPTNO NUMBER(2) PRIMARY KEY, --部门编号 DNAME VARCHAR2(14) , --部门名称 LOC VARCHAR2(13) --地址 ) ; --执行步骤二：插入数据 INSERT INTO DEPT VALUES (10,‘ACCOUNTING’,‘NEW YORK’); INSERT INTO DEPT VALUES (20,‘RESEARCH’,‘DALLAS’); INSERT INTO DEPT VALUES (30,‘SALES’,‘CHICAGO’); INSERT INTO DEPT VALUES (40,‘OPERATIONS’,‘BOSTON’); --执行步骤三：操作dept 表 INSERT INTO dept VALUES(50,‘a’,null); INSERT INTO dept VALUES(60,‘b’,null); SAVEPOINT a; INSERT INTO dept VALUES(70,‘c’,null); ROLLBACK TO SAVEPOINT a; --执行步骤四：查看dept 表,有50、60 号部门 SELECT * FROM dept; --执行步骤五：回滚 ROLLBACK;–没有50、60 号部门 --执行步骤六：查看dept 表 SELECT * FROM dept;

10、视图

视图：视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。从数据库系统外部来看，视图就如同一张表一样。视图可以查询，但不可以新增、删除、修改。

–创建视图 create view myview as select empno,ename,job,deptno from emp; --查看视图 select * from myview; --对原视图进行字段的修改 drop view myview; create view myview as select empno,ename,job,sal,deptno from emp; --便捷的对原视图的修改(这种方式在同义词里也可以使用 create or replace view myview as select empno,ename,job,deptno from emp; --视图的数据变动 create or replace view myview as select empno,ename,job,deptno from emp1 where deptno = 20; --针对视图的创建条件进行修改 update myview set deptno=10 where empno=7369; --查看下这个视图的母表 select * from emp1; --发现对视图的修改反应到了母表上面，这样不好，进行视图的修改,添加一个 with check option 的命令； create or replace view myview as select empno,ename,job,deptno from emp1 where deptno = 20 with check option; --再对视图的创建条件进行修改 update myview set deptno=10 where empno=7566; --发现不能再修改视图的创建条件了，那么，尝试下修改其他的数据 update myview set ename=‘刘明城’ where empno=7566; --发现可以修改其他的数据，这样的话，不符合视图的要求，那么重新在末尾添加一行命令： with read only create or replace view myview as select empno,ename,job,deptno from emp1 where deptno = 20 with read only; --再尝试下能不能修改其他数据 update myview set ename=‘小小强’ where empno=7566; --发现已经成功的不让我修改其他的数据，那么创建条件能不能改呢？ update myview set deptno = 10 where empno=7566; --删除视图 drop view myview;

视图主要用于查询 ，所以在创建视图的时候在最后面加上with read only

11、同义词

同义词（oracle独有）：

​ 类似于 dual 这张表的使用方式，对某一张表进行设置为同义词可以让其他 用户不需要通过用户名.表名的方式来进行表的访问，而是可以直接让其他用户操作在 他们用户下没有的这张表

同义词的用处

简化sql语句、隐藏对象的名称和所有者，为分布式数据库的远程对象提供了位置透明性、提供对象的公共访问如果一张表没有在当前用户下，如果想要访问这张表只能通过对象名.表名，将用户下的表设置为公有同义词以后，其他用户可以直接访问而不需要经过对象去调表。

同义词的分类

公有同义词： 对外开放，同义词的名称可以和表名一样

私有同义词：只能当前用户使用，其他对象不能使用，同义词的名称不能和表名一样

创建同义词：

/** 私有的同义词 synonym 前面默认了一个 private，让这个同义词给本用户去使用，所以如果你创建的 是私有的同义词的话那么同义词的词名是不允许跟原表名相同的 / CREATE OR REPLACE private SYNONYM pemp FOR scott.emp; / 公有的同义词 synonym 前面默认了一个 public，让这个同义词给其他用户去使用 **/ CREATE OR REPLACE public SYNONYM emp FOR scott.emp;

12、序列

mysql 主键可以设置自动增长

oracle不支持自动增长，如果需要设置类似的键值，可以是用序列

创建序列：

CREATE SEQUENCE SEQ_OA START WITH 1 – 从１开始 INCREMENT BY 1 – 每次增长１ -1 表示负增长 MAXVALUE 2000 – 最大值为2000 MINVALUE 1 --最小值 CYCLE – 达最大值以后循环从1开始

– 在mybatis中 使用的数据库是oracle 现在需要新增一条数据 主键使用序列 SELECT SEQ_OA.NEXTVAL FROM dual INSERT INTO student (stuId ,stuName,stuAge) values( #{stuId} ,#{stuname}，#{stuAge} )

13、索引

什么是索引

1）索引是数据库对象之一，合理的使用索引可以大大降低i/o 次数用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

6）oracle创建主键时会自动在该列上创建索引

但是，对表进行insert,update,delete处理时，由于要表的存放位置记录到索引项中而会降低一些速度。

注意：一个基表不能建太多的索引；

​ 空值不能被索引

​ 只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

建立索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

1． 单列索引

单列索引是基于单个列所建立的索引，比如:

CREATE index 索引名 on 表名(列名)

2． 复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是

要求列的组合必须不同,比如：

Create index emp_idx1 on emp(ename,job); Create index emp_idx1 on emp(job,ename); – 范例：给person表的name建立索引 create index pname_index on person(name); – 范例：给person表创建一个name和gender的索引 create index pname_gender_index on person(name, gender);

修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2. 合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引

方式一：删除原来的索引，重新建立索引

方式二：

alter index index_sno rebuild;

删除索引

drop index index_sno;

查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name =‘tablename’; – eg: create index index_sno on student(‘name’); select * from all_indexes where table_name=‘student’;

索引分类

B树索引

（默认索引，保存讲过排序过的索引列和对应的rowid值）

1）说明：

1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值

2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

3.能够适应精确查询、模糊查询和比较查询

2）创建例子

craete index index_sno on student(‘sno’);

3）适合使用场景：

列基数（列不重复值的个数）大时适合使用B数索引

位图索引

1）说明：

1.创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行 使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索 引中的映射函数完成位到行的ROWID的转换

2)创建例子

create bitmap index index_sno on student(sno);

3. 适合场景：

对于基数小的列适合简历位图索引（例如性别等）

单列索引和复合索引（基于多个列创建）

1. 注意：

即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列

函数索引

1)说明：

1. 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
2. 函数索引既可以使用B数索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
3. 函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等聚合函数

2）例子：

create index fbi on student (upper(name)); select * from student where upper(name) =‘WISH’;

索引建立原则总结

1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
3. 小表不要简历索引
4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
6. 经常进行连接查询的列应该创建索引
7. 使用create index时要将最常查询的列放在最前面
8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

注意事项

\1. 通配符在搜索词首出现时，oracle不能使用索引，eg：

–我们在name上创建索引；

create index index_name on student(‘name’);

–下面的方式oracle不适用name索引

select * from student where name like ‘%wish%’;

–如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：

select * from student where name like ‘wish%’;

\2. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）

select * from student where not (score=100); select * from student where score <> 100;

–替换为

select * from student where score>100 or score <100

\3. 索引上使用空值比较将停止使用索引， eg：

select * from student where score is not null;

14、分区表

分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库 管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但 是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表，官方给的建议是：

a. 表的大小超过2GB。

b. 表中包含历史数据，新的数据被增加到新的分区中。

表分区的优缺点

优点：

a.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

b.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。

c.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。

d.均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

​ 分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能。

表分区的几种类型及操作方法

范围分区（RANGE）

​ 范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。当使用范围分区时，请考虑以下几个规则：

1）每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

​ 2）所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

​ 3）在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 );

例二：按时间划分

CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(‘01- MAY -2003’,‘DD-MON-YYYY’)) TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(‘01-JUN-2003’,‘DD-MON-YYYY’)) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(‘01-JUL-2003’,‘DD-MON-YYYY’)) TABLESPACE ORD_TS03 );

例三：MAXVALUE

CREATE TABLE RANGETABLE ( idd INT PRIMARY KEY , iNAME VARCHAR(10), grade INT ) PARTITION BY RANGE (grade) ( PARTITION part1 VALUES LESS THAN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE Part2_tb );

–——在表上执行查询 select * from RANGETABLE;

–——在表分区上执行查询 select * from RANGETABLE partition(part1);

列表分区（LIST）

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。

例一：

CREATE TABLE PROBLEM_TICKETS ( PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT NULL, DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20) ) PARTITION BY LIST (STATUS) ( PARTITION PROB_ACTIVE VALUES (‘ACTIVE’) TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES (‘INACTIVE’) TABLESPACE PROB_TS02 );

CREATE TABLE emp ( empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER ) PARTITION BY HASH (empno) PARTITIONS 8 STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

例二：

CREATE TABLE ListTable ( id INT PRIMARY KEY , name VARCHAR (20), area VARCHAR (10) ) PARTITION BY LIST (area) ( PARTITION part1 VALUES (‘guangdong’,‘beijing’) TABLESPACE Part1_tb, PARTITION part2 VALUES (‘shanghai’,‘nanjing’) TABLESPACE Part2_tb );

哈希分区（散列分区）（HASH）

例一：

CREATE TABLE HASH_TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 )

简写：

CREATE TABLE emp ( empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER ) PARTITION BY HASH (empno) PARTITIONS 8 STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

​ hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

组合分区（RANGE-LIST 和 RANGE-HASH）

基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN(TO_DATE(‘2003-01-01’,‘YYYY-MM-DD’))TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES (‘ACTIVE’) TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES (‘INACTIVE’) TABLESPACE rptfact2009 ), PARTITION P2 VALUES LESS THAN (TO_DATE(‘2003-03-01’,‘YYYY-MM-DD’)) TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES (‘ACTIVE’) TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES (‘INACTIVE’) TABLESPACE rptfact2009 ) )

2）基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test ( transaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date ) partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) ( partition part_01 values less than(to_date(‘2006-01-01’,‘yyyy-mm-dd’)), partition part_02 values less than(to_date(‘2010-01-01’,‘yyyy-mm-dd’)), partition part_03 values less than(maxvalue) ); CREATE TABLE range_hash_example( range_column_key int, hash_column_key INT, DATA VARCHAR2(20) ) PARTITION BY RANGE(range_column_key) SUBPARTITION BY HASH(hash_column_key) SUBPARTITIONS 2 ( PARTITION part_1 VALUES LESS THAN (100000000) ( SUBPARTITION part_1_sub_1, SUBPARTITION part_1_sub_2, SUBPARTITION part_1_sub_3 ), PARTITION part_2 VALUES LESS THAN (200000000) ( SUBPARTITION part_2_sub_1, SUBPARTITION part_2_sub_2 ) );

–注： subpartitions 2 并不是指定subpartition的个数一定为2，实际上每个分区的子分区个数可以不同。如果不指定subpartition的具体明细，则系统按照subpartitions的值指定subpartition的个数生成子分区，名称由系统定义 。

有关分区表的维护操作

添加分区

以下代码给SALES表添加了一个P3分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE(‘2003-06-01’,‘YYYY-MM-DD’));

注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给SALES表的P3分区添加了一个P3SUB1子分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(‘COMPLETE’); – range partitioned table ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE(‘2008-10-1 00:00:00’,‘yyyy-mm-ddhh24:mi:ss’)); --list partitioned table ALTER TABLE list_example ADD PARTITION part04 VALUES(‘TE’); --Adding Values for a List Partition ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES(‘MIS’); --Dropping Values from a List Partition ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES(‘MIS’); --hash partitioned table ALTER TABLE hash_example ADD PARTITION part03; --增加subpartition ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;

注:hash partitioned table新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中，所以被重新分配的分区的indexes需要rebuild 。

删除分区

ALTER TABLE SALES DROP PARTITION P3; ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2 UPDATE INDEXES; --如果省略update indexes子句的话，必须重建受影响的分区的index; ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;

拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE(‘2003-02-01’,‘YYYY-MM-DD’)) INTO (PARTITION P21,PARTITION P22);

注意：如果是RANGE类型的，使用at，LIST类型的使用values。

接合分区（coalesce）

分区接合是针对散列分区或者*-散列子分区的，目的是减少分区数。当某个散列分区接合后，Oracle将其分区的数据分散到其它分区中。被接合的分区是由数据库选择的，接合完成后该分区会被删除，且如果没有使用UPDATE INDEX子句，本地索引和全局索引均将变成不可用，一般需要重建索引。

–散列分区表的散列分区接合 ALTER TABLE table_name COALESCE PARTITION; --散列分区表的散列子分区接合 ALTER TABLE table_name MODIFY PARTITION partition_name COALESCE SUBPARTITION; --重命名表分区 ALTER TABLE table_name RENAME PARTITION old_name TO new_name; ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;

15、PLSQL 简介及基本语法

PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。PL/SQL是是由甲骨文公司在90年代初开发，以提高SQL的功能。

PL/SQL是嵌入在Oracle数据库中的三个关键的编程语言之一（只有oracle支持）

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL****的工作原理

1、 PL/SQL引擎接受 PL/SQL 块并对其进行编译执行

2、 该引擎执行所有过程语句

3、 将SQL语句发送给Oracle的SQL语句执行器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1cNg9YrS-1590654998731)(1、Oracle介绍/clipboard-1590140307076.png)]

PL/SQL 基本结构及语法

[declare] --定义变量，说明部分 （变量说明，游标申明，例外说明 〕

begin --程序体的开始

语句序列（DML语句）

[exception] --异常处理

end; --程序体的结束

–书写第一个 plsql 程序块 打印输出字符串 begin --打印输出 dbms_output.put_line(‘hello plsql’); end;

变量的定义

变量名与 java 中变量名的定义一样，长度不能超过 32 位

语法：变量名 变量类型

v_name varchar2(20);

变量基本类型：

varchar2() 32767 个字节

char() 32767

number(m,n)

date

boolean true/false/null

binary_integer 整型

变量在定义时可以初始化，使用 := 赋值

v_name varchar2(20) := ‘hello’;

常见类型的变量定义并赋值

declare v_name varchar2(20) := ‘hello’; v_id binary_integer := 12; v_pai number(3,2) := 3.14; v_sex char(1) := ‘m’; v_married boolean := true; v_date date := sysdate; begin dbms_output.put_line(v_name); dbms_output.put_line(v_id); dbms_output.put_line(v_pai); dbms_output.put_line(v_sex); if v_married then dbms_output.put_line(‘married’); else dbms_output.put_line(‘no marry’); end if; dbms_output.put_line(v_date); end;

变量的复合类型

表类型

类似于 java 中的数组，用来存储某种特定数据类型的数据

语法：

​ type 表类型名称 is table of 类型 index of binary_integer;

例如：用来存储字段值

declare type table_type is table of varchar2(20) index by binary_integer; v_name table_type begin select name into v_name(0) from t_user where id=1; select name into v_name(1) from t_user where id=123; dbms_output.put_line(v_name(0)); end;

— 简化的表类型

定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)类型一致时使用简化编程

语法：%type

​ t_user.name%type; 表示类型是 t_user.name 字段的类型

declare v_name t_user.name%type; begin select name into v_name from t_user where id=1; dbms_output.put_line(v_name); end;

记录类型

作用：可以用来存储一条记录 类似于java中的 对象数组

语法：

​ type 记录类型名 is record(变量名 类型 [,变量名 类型…])

例如：

declare type row_type is record(v_id binary_integer,v_name varchar2(20)); v_record row_type; begin select id,name into v_record.v_id , v_record.v_name from t_user where id=1; dbms_output.put_line(v_record.v_id); dbms_output.put_line(v_record.v_name); end;

作用：如果数据类型和数据库中表的结构一致时可以使用记录类型，返回一个记录类型

%rowtype 取表中的一条记录中的字段类型组成一个记录类型 temp_emp%rowtype 表示当前变量的类型是跟 t_user 表中的记录类型一致

declare v_user temp_emp%rowtype; begin select id,name,age into v_user.id,v_user.name,v_user.age from t_user where id=1; dbms_output.put_line(v_user.id || v_user.name || v_user.age); end;

传递参数

定义形参使用& ，如果是字符类型需要使用’’引起来

declare type table_type is table of varchar2(20) index by binary_integer; v_name table_type; begin select ename into v_name(0) from emp where empno=&no; dbms_output.put_line(v_name(0)); end;

16、在 PLSQL 中使用 SQL 语句

select

查询语句,必须使用into 关键字返回结果必须是一条，返回多条记录会报 too_many_rows异常，没有数据返回no_data_found异常

declare type table_type is table of varchar2(20) index by binary_integer; v_name table_type; begin select ename into v_name(0) from emp where empno=&no; dbms_output.put_line(v_name(0)); end;

insert、update 、delete

例如：创建一张测试表并添加一条数据

create table test_emp (empno number(5) ,ename varchar2(50) job varchar(20)) – insert新增 begin insert into test_emp(empno,ename,job) values(1234,‘xiaohua’,‘mishu’); commit; end; ---- 通过参数输入值 添加表数据 begin insert into test_emp(empno,ename,job) values(&no,’&name’,’&job’); commit; end; – 修改update begin update test_emp set job=‘baobiao’ where empno=2345; commit; end; --delete删除 begin delete from test_emp where empno=&no; commit; end;

增删改注意提交事务

获得操作的记录条数

sql%rowcount 是游标中的一个属性，用来获得操作的记录数

declare v_count number(10) ; begin delete from test_emp where empno=&no; v_count := sql%rowcount; – 获取操作记录数 commit; dbms_output.put_line(v_count||‘rows deleted’); end;

DDL(create/drop/alter/truncate)

–存在就删除不存在就创建

select count() from user_tables where table_name=‘TEST_EMP’; declare v_count number(10); begin select count() into v_count from user_tables where table_name=‘TEST_EMP’; if v_count=1 then execute immediate(‘drop table test_emp’); end if; execute immediate(‘create table test_emp(id number(10) primary key , name varchar2(20))’); end;

17、PLSQL 中的流程控制

选择结构

if单分支

if 判断条件 then

​ ----执行语句

end if;

if多分支

if 判断条件 then

·····

elsif 判断条件then

·····

elsif 判断条件then

·····

else

·····

end if;

例如：

declare v_job test_emp.job%type; v_sal number(10); v_empno test_emp.empno%type :=&no; v_oldsal number(10); begin select job,sal into v_job,v_oldsal from test_emp where empno=v_empno; if ‘MANAGER’=v_job then v_sal := 100; elsif ‘SALESMAN’=v_job then v_sal := 50; elsif ‘CLERK’= v_job then v_sal := 10; else v_sal := 0; end if; update test_emp set sal=v_oldsal+v_sal where empno=v_empno; commit; end;

case选择

语法：

case 变量

when条件 then

…………

when 条件 then

…………

else

…………

end case;

declare v_job test_emp.job%type; v_add number(10); v_empno test_emp.empno%type := &no; v_oldsal test_emp.sal%type; begin select job,sal into v_job,v_oldsal from test_emp where empno=v_empno; case v_job when ‘MANAGER’ then v_add := 100; when ‘SALESMAN’ then v_add := 50; when ‘CLERK’ then v_add := 10; else v_add :=0; end case; update test_emp set sal=v_oldsal+v_add where empno=v_empno; commit; end;

循环控制

loop 循环

declare v_id number(10) :=10; begin loop dbms_output.put_line(v_id); if v_id=20 then exit; end if; v_id := v_id+1; end loop; end;

---- 将test_emp表中所有记录的ename值存在表类型 v-ename中 declare type table_type is table of varchar2(10) index by binary_integer; v_index number(10) :=0; v_ename table_type; v_count number(10); v_row number(10) ; begin select count() into v_count from test_emp; loop v_row := v_index+1; select e.ename into v_ename(v_index) from (select t.,rownum r from test_emp t) e where r= v_row; if v_row = v_count then exit; end if; v_index := v_index + 1; end loop; dbms_output.put_line(v_ename(7)); dbms_output.put_line(v_ename(9)); dbms_output.put_line(v_ename(13)); end;

for 循环

---- 从100开始到110 结束循环输出 declare v_aaa number(10) ; begin --从小到大的循环 for v_aaa in 100 … 110 loop dbms_output.put_line(v_aaa); end loop; end; — 反转输出 declare v_aaa number(10) ; begin --从大到小的循环 for v_aaa in reverse 100…110 loop dbms_output.put_line(v_aaa); end loop; end;

while 循环

declare v_index binary_integer :=100; begin while v_index<120 loop dbms_output.put_line(v_index); v_index := v_index+1; end loop; end;

用标签定义循环嵌套

begin <> for v_index in 1…4 loop <> for i in 1…3 loop if i=2 then exit inner; end if; dbms_output.put_line(i); end loop inner; dbms_output.put_line(v_index); end loop out; end;

18、游标

游标 CURSOR

​ 在 sql 执行的时候会在内存中开辟一个区域，这个区域叫context 上下文环境，游标 cursor 就是指向这个内存区域的一个指针。

游标的作用

通过游标来操作 SQL 语句执行的返回结果。

游标的属性：

sql%rowcount 影响的记录数

%found 游标中有没有下一条 true/false – rs.next()

%notfound 游标中有没有下一条 true/false – rs.hasNext()

%isopen 标识当前游标有没有开启执行true/false

游标的分类

隐 式 游 标 ： 由 数 据 库 管 理 系 统 创 建 调 用 执 行

显示游标：由程序员创建调用执行，能直接对其进行操作。

游标的使用

1.定义游标

2.开启，调用执行

3.操作游标，获取数据

4.关闭游标

declare --定义游标 – Cursor 游标名 is 查询语句 cursor mycursor is select empno,ename from test_emp ; v_id binary_integer; v_name varchar2(20); begin --开启执行游标 open mycursor; loop fetch mycursor into v_id,v_name; exit when mycursor%notfound ; dbms_output.put_line(v_id); dbms_output.put_line(v_name); end loop; --关闭游标 close mycursor; end;

使用游标获取所有的字段

DECLARE – 定义一个游标 CURSOR mycursor IS SELECT * FROM temp_emp WHERE deptno=20; v_emp temp_emp%ROWTYPE; BEGIN – 打开游标 OPEN mycursor ; – 循环读取 LOOP – FETCH mycursor INTO v_no,v_name,v_job; FETCH mycursor INTO v_emp ; – 如果读到最后一条 – 游标指针已经到最后 IF mycursor%NOTFOUND THEN EXIT; END IF; dbms_output.put_line(v_emp.empno || v_emp.ename ); END LOOP; CLOSE mycursor; END;

使用传参游标

注意点：重名 或 查询条件 和 参数名 不一致都会出错)

declare --定义游标时传参 cursor mycursor(v_empno number) is select empno,ename from test_emp where empno=v_empno ; v_id binary_integer; v_name varchar2(20); begin --开启执行游标 调用时传实参 open mycursor(7788); loop --操作游标，获得数据 fetch mycursor into v_id,v_name; exit when mycursor%notfound ; dbms_output.put_line(v_id); dbms_output.put_line(v_name); end loop; --关闭游标 close mycursor; end;

定义游标变量

declare --定义游标类型 return 返回一个记录 type cursor_type is ref cursor return test_emp%rowtype; --定义变量，类型是游标类型 mycursor cursor_type; --定义变量为记录类型 myrecord test_emp%rowtype; begin --开启执行游标并赋值 open mycursor for select * from test_emp; loop fetch mycursor into myrecord; exit when mycursor%notfound; dbms_output.put_line(myrecord.empno||myrecord.ename); end loop; end;

declare v_input char(1) := upper(’&c’); type cursor_type is ref cursor ; type record_type is record (id binary_integer,name varchar2(10)); myrecord record_type; mycursor cursor_type; begin if ‘D’=v_input then open mycursor for select deptno,dname from dept; elsif ‘E’= v_input then open mycursor for select empno,ename from emp; else dbms_output.put_line(‘no data’); end if; loop fetch mycursor into myrecord; exit when mycursor%notfound; dbms_output.put_line(myrecord.id||myrecord.name); end loop; end;

19、异常

系统异常

由数据库管理系统捕获处理 预定义异常：有异常编码，异常名称，异常信息

no_data-found too_many_rows

declare v_name varchar2(10); begin select ename into v_name from test_emp where empno=1234; dbms_output.put_line(v_name); exception when no_data_found then dbms_output.put_line(‘no data’); when too_many_rows then dbms_output.put_line(‘too many rows’); when others then dbms_output.put_line(‘error’); end;

非预定义异常：有编码，有信息，没有异常名称

declare e_child exception; pragma exception_init(e_child,-02292); begin delete from dept where deptno=10; dbms_output.put_line(‘deleted’); exception when e_child then dbms_output.put_line(‘has constraint’); when others then dbms_output.put_line(‘others’); end;

自定义异常

由程序员捕获处理

declare v_id binary_integer := &no; v_comm test_emp.comm%type; --定义异常名称 e_null exception; --将异常名称跟异常编码绑定 pragma exception_init(e_null,-20101); begin select comm into v_comm from test_emp where empno=v_id; dbms_output.put_line(v_comm); if v_comm is null then --抛出异常 raise e_null; else update test_emp set comm=comm+100 where empno=v_id; commit; end if; exception --捕获异常 when e_null then dbms_output.put_line(‘comm is null’); --捕获其他类型的异常 when others then dbms_output.put_line(‘others’); end; --------示例2 declare v_id binary_integer := &no; v_comm test_emp.comm%type; e_null exception; pragma exception_init(e_null,-20101); begin select comm into v_comm from test_emp where empno=v_id; dbms_output.put_line(v_comm); if v_comm is null then raise e_null; else update test_emp set comm=comm+100 where empno=v_id; commit; end if; exception when others then --打印输出异常编码 sqlcode 和异常信息 sqlerrm dbms_output.put_line(sqlcode||’—’||sqlerrm); end;

20、存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。过程相当于 java 中的方法，定义用于完成某种特定操作，PL/SQL： 匿名块 过程有名称，会存在与数据库系统中。

创建存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] AS/IS begin – PLSQL子程序体； End [过程名];

定义无参过程

create or replace procedure pro_insert as begin for v_index in 1…5 loop insert into test_emp(empno) values(v_index); end loop; commit; end;

调用过程

begin mypro end;

定义有参过程

create or replace procedure mypro(v_id in number,v_sal in number) is --定义变量 不需要使用DECLARE v_count binary_integer; pemp test_emp%rowtype; begin select count(*) into v_count from test_emp where empno=v_id; if v_count>0 then select * into pemp from test_emp where empno = v_id; update test_emp set sal=v_sal where empno=v_id; commit; end if; — 异常处理 exception when others then dbms_output.put_line(sqlcode||’—’||sqlerrm); dbms_output.put_line(‘涨工资前’ || pemp.sal || ‘涨工资后’ ||pemp.sal); end;

调用有参过程

通过匿名块：

Begin Mypro(7788,8000); end;

含有输出参数的过程

create or replace procedure query_sal(v_id in number,v_sal out number) is begin select sal into v_sal from temp_emp where empno=v_id; end;

含有输出参数的过程调用

declare v_sal number; begin query_sal(7788,v_sal); dbms_output.put_line(v_sal); end;

JDBC 调用过程

调用无参的过程

//存储过程调用 SQL 语句必须用｛call 过程名称()｝ String sql = “{call pro_insert()}”; conn = DriverManager.getConnection(url,user,password); //使用CallableStatement对象调用 CallableStatement stmt = conn.prepareCall(sql); stmt.execute();

调用带输入参数的过程

//通过？号占位符 String sql = “{call mypro(?,?)}”; conn = DriverManager.getConnection(url,user,password); CallableStatement stmt = conn.prepareCall(sql); //设置输入参数 stmt.setInt(1,7788); stmt.setInt(2,3000); stmt.execute();

调用输入输出参数的过程

— query_sal(v_id in number,v_sal out number) String sql = “{call query_sal(?,?)}”; CallableStatement stmt = conn.prepareCall(sql); stmt.setInt(1, 7369); //向过程注册一个 int 类型的输出参数 stmt.registerOutParameter(2,Types.INTEGER); stmt.execute(); //通过 stmt 对象取出输出参数 int sal = stmt.getInt(2);

21、函数

执行功能返回计算结果

常见的系统函数：to_char to_date nvl length upper mod ·····

自定义函数

函数一般是在sql语句中使用

返回单个值

create or replace function myfun(v_id number) — 定义返回值类型 return number – 方法的返回值类型 is — 定义的变量 v_sum number; begin select count(*) into v_sum from temp_emp where deptno=v_id; return v_sum; end; — 测试自定义函数 必须写在sql语句中 select myfun(30) from dual;

返回多个值的函数.—第1个值以后返回使用out来处理

create or replace function myfun(v_id number ,v_avgsal out number) return number is v_sum number; begin — 查找指定部门编号的员工总人数和平均工资 select count(*),avg(sal) into v_sum,v_avgsal from emp where deptno=v_id; return v_sum; end; — 测试 declare v_sal number(10); v_count number; begin v_count:= myfun(10,v_sal); dbms_output.put_line(v_sal); dbms_output.put_line(v_count); end;

过程和函数的异同

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。

但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

异：

procedure function

in|out in|out(一般不建议采用)

不用 return 定义返回 return 先定义返回类型， 后定义返回值

独立存在调用 必须存在于SQL语句或者表达式中，不能独立调用

22.包（package）

作用

更好的管理和调用函数和过程，封装函数和过程的实现

定义包头

Create or replace package com_my_package Is —定义的变量一般是定义全局常量 constant PI v_sum test_emp.sal%type; --定义表类型 type cursor_type is ref cursor; – 定义游标类型 --定义过程find_all procedure find_all(v_pid in number,my_cursor out cursor_type); --定义函数sum sal function sum_sal(v_pid number) return number; end

定义包体

create or replace package body com_my_package is --实现过程find all procedure find_all(v_pid in number,my_cursor out cursor_type) is begin if v_pid=0 then open my_cursor for select empno,ename from test_emp; else open my_cursor for select empno,ename from test_emp where deptno=v_pid; end if; end find_all; --实现函数sum_sal function sum_sal(v_pid number) return number is v_count number; begin if v_pid=0 then select sum(sal) into v_count from test_emp; else select sum(sal) into v_count from test_emp where deptno=v_pid; end if; return v_count; end sum_sal; end com_my_package;

执行包 –调用函数

select com_my_package.sum_sal(0) from dual;

执行包 –调用

declare type cursor_type is ref cursor; my_cursor cursor_type; type row_type is record(v_id number(10),v_name varchar2(10)); my_rows row_type; begin com_my_package.find_all(0,my_cursor); loop fetch my_cursor into my_rows; exit when my_cursor%notfound; dbms_output.put_line(my_rows.v_id||my_rows.v_name); end loop; end;

在包中定义变量、常数、过程、函数

1.便于管理

2.将函数过程的实现定义在包体中，比较隐蔽安全

3.包一次调用整个装载进内存，便于访问 要求：

• 必须先创建包头，然后再创建包体包头中只声明，不实现，
• 包体中实现 包头中定义的变量是全局变量

create or replace package com_constant is v_pi constant number(3,2):= 3.14; end com_constant; — 获取包中的常量 begin dbms_output.put_line(com_constant.v_pi); end;

JDBC 中访问带包的过程及函数（怎么接收游标类型的数据）

//调用时包名.过程/函数名称 String sql = “{call com_my_package.find_all(?,?)}”; conn = DriverManager.getConnection(url, user, password); CallableStatement stmt = conn.prepareCall(sql); stmt.setInt(1,10); //当输出参数为数据库中 PLSQL 特有的类型时，必须用OracleTypes 中的常数 stmt.registerOutParameter(2, OracleTypes.CURSOR); stmt.execute(); //将游标类型强转成 ResultSet ResultSet rs = (ResultSet)stmt.getObject(2); while(rs.next()){ System.out.println(rs.getInt(“empno”)); System.out.println(rs.getString(“ename”)); }

23、触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器可用于

 数据确认

 实施复杂的安全性检查

 做审计，跟踪表上所做的数据操作等

 数据的备份和同步

触发器的类型

表级(语句)触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响 了多少行 。

行级触发器 ：触发语句作用的每一条记录都被触发。在行级触 发器中使用old和new伪记录变量, 识别值的状态。

语法：

CREATE [or REPLACE] TRIGGER 触发器名

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF 列名]}

ON 表名

[FOR EACH ROW [WHEN(条件) ] ]

begin

PLSQL 块

End 触发器名

表级触发器示例：

– 添加、修改、删除数据之前 触发 create or replace trigger my_test_emp_trigger before insert or update or delete on temp_emp begin if to_char(sysdate,‘DY’) in (‘星期六’,‘星期日’) then raise_application_error(-20001,‘u can’‘t update this table’); end if; end; — 插入一条数据之后触发 create or replace trigger testTrigger after insert on temp_emp declare – local variables here begin dbms_output.put_line(‘一个员工被插入’); end testTrigger;

行级触发器

:new.字段名称 – 要修改成的值

:old.字段名称 –- 原来的值

​ for each row :这个触发器是行触发器

create or replace trigger my_trigger after insert or delete on temp_emp for each row begin --判断是否为插入操作 inserting，updating/deleting if inserting then insert into emp(empno) values(:new.empno); elsif deleting then delete from emp where empno=(:old.empno); end if; end;

— 在修改temp_emp表中的数据时 触发 判断职位如果不是’MANAGER’，‘PRESIDENT’ — 而且想要修改的工资超过10000 抛异常 create or replace trigger sal_trigger before update on test_emp for each row begin if :old.job not in(‘MANAGER’,‘PRESIDENT’) and :new.sal>10000 then  if; end;

–判断员工涨工资之后的工资的值一定要大于涨工资之前的工资 create or replace trigger addsal4p before update of sal on temp_emp for each row begin if :old.sal >= :new.sal then raise_application_error(-20002, ‘涨前的工资不能大于涨后的工资’); end if; end;

1、查询scott.emp表中员工表中薪水从高到低排序的第5~9条记录

select * from (

select rownum rn,tt.* from(

select * from emp order by sal esc

) tt where rownum <10

) where rn >4;

2、查询每个部门薪水第二高的员工基本信息（包含并列第二）

select max(sal)

from emp

where sal not in(select max(sal)from emp);

select emp.*,sal,dense_rank() over(order by sal desc)pm from emp;

3、列出至少有一个员工的所有部门

select distinct deptno from emp;

4、列出薪水比"SMITH"高的所有员工

select ename,sal

from emp

where sal>(select sal

from emp where upper(ename)=‘SMITH’);

5、列出入职时间早于其直接上级的所有员工

select * from emp a where hiredate>(select hiredate from emp where empno=a.mgr);

6.显示职员的入职年限

select empno,ename,sysdate-hiredate from emp;

7.列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

select job as 工作，min(sal) as 最低薪金

from emp

group by job

having min(sal)>1500;

8.找出各月最后一天受雇的所有雇员

select *

from emp

where hiredate=last_day(hiredate);

9、列出收入（薪水+奖金）最高的前三名

select * from

(select deptno,ename,e.sal*12+nvl(e.comm,0),row_number() over (partition by deptno

​ order by e.sal*12+nvl(e.comm,0) desc) rn

from emp e)

​ where rn<3;

10.查询部门名称是’SALES’的员工姓名及薪水等级

select ename,grade from emp ,salgrade ,dept where

dname=’SALES’ and sal between LOSAL and HISAL

and EMP.deptno = DEPT.deptno ;

11.查询出工作职位是CLERK的所有员工姓名及薪水等级，并按照薪水升序排列

select grade,z.ename,sal from salgrade s,(select emp.* from emp where job =‘CLERK’)z where losal<=z.sal and hisal>=z.sal order by z.sal asc;

第二天

14.查询工资小于2000的人的名字,工资,提成

select nvl(e.comm,0),e.ename,e.sal

from emp e

where e.sal<2000

15.查询提成是0或是空的人的编号,名字,提成,部门编号

select e.empno,e.ename,e.comm,d.deptno

from emp e,dept d

where e.comm is null;

16.查询工资大于1000或者部门是30的员工的编号,姓名,工资

select e.empno,e.ename,e.sal

from emp e

where e.sal>1000 or e.deptno=30;

17.查询部门10的部门名称,位置等

select d.dname,d.loc

from dept d

where d.deptno=10

18.查询职位(JOB)为’PRESIDENT’的员工的工资

select e.sal

from emp e

where e.job=‘PRESIDENT’

19.查询有提成的员工信息

select *

from emp e

where e.comm is not null;

20.查询名字长度为4 的员工的员工编号,姓名

select *

from emp

where length(ename)=4;

21.显示10 号部门的所有经理(‘MANAGER’)和20 号部门的所有职员(‘CLERK’)的详细信息

select *

from emp

where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’

22.显示各个部门经理(‘MANAGER’)的工资

select sal

from emp

where job=‘MANAGER’

23.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息

select *

from emp

where comm>sal

24.检索以S结尾的员工姓名及月收入( 月收入是工资和提成之和 )

select ename,sal+nvl(comm,0)

from emp

where ename like ‘%S’;

25.查询员工姓名的第三个字母是A的员工姓名

select ename

from emp

where ename like ‘__A%’;

26.查询工资不在1000到2000之间的员工的姓名和工资

select ename,sal

from emp

where sal not between 1000 and 2000;

\27. 查询公司中没有管理者的员工姓名及job

select ename,job

from emp

where mgr is null;

28.求ename列的长度,显示员工编号,名字,长度

select empno,ename,length(ename) 长度

from emp;

29.将ename转成小写,显示员工编号,名字,小写名字

select empno,ename,lower(ename) 小写名字

from emp;

30.将ename中的A,替换成ABC,显示编号,替换前的名字,替换后的名字

select empno,ename,replace(ename,‘A’,‘ABC’)

from emp;