＜Oracle＞ 【手册笔记No.001】《Oracle 精编实用手册》

序章

本文是Else学习Oracle过程的笔记，编写过程参考了众多CSDN的文章，特此对引用文章及出处进行说明，表示感谢。

1、感谢作者：轻松的小希。参考引用文章出处：学习Oracle这一篇就够了
说明：主要借鉴和学习该篇文章的文章结构，引用了文章的SQL语句部分。作者是为大神，推荐大家关注。

第1章 Oracle 基础概念

1.1 数据库简介

1） 什么是数据库？

数据库就是存放数据的仓库，是人们存储和管理信息世界数据的方式。

2） 为什么要有数据库？

数据是资源。源源不断产生的数据，是不断产生的资源。人们为了更高效、更便捷管理和使用数据资源，而诞生了数据库。

3） 数据库基本概念

数据库：( Database，DB），存储数据。
数据库管理系统：（Database Management System，DBMS )，管理数据库。如，Oracle、Mysql、DB2等。
数据库系统：（Data Base System，DBS），常说的数据库一般指数据库系统，包含数据库+数据库管理系统+数据库管理员等。
**DBA：**数据库管理员。
SQL：（Structured Query Language），结构化查询语言。

4） 数据库分类

1. 关系型数据库：（Relational Database）
   商业型数据库：ORACLE、SQL Server、IBM DB2、Sybase
   开源数据库有：MySQL、PostgreSQL
2. 非关系型数据库：（NoSQL）
   键值（key-value）数据库：面向高性能并发读写，典型代表如Redis。
   列存储（Columnar Storage）数据库：面向PB级的分析应用，如：HBase，Hypertable。
   文档数据库：特点是可以在海量的数据中快速地查询数据，如网页和移动应用数据。典型代表：MongoDB，CouchDB，Mark Logic
   图形数据库：如应用在推荐系统、关系图谱，典型代表：new4j，InfiniteGraph，OrientDB
3. 新式关系型数据库：（NewSQL）
   NewSQL提供与NoSQL系统相同的扩展性能，且保持传统数据库支持的ACID特性。典型代表：SAP HANA，VoltDB，nuoDB，MariaDB，Pivotal
4. MPP（Massively Parallel Processing）
   使用多个SQL数据库节点搭建的数据仓库系统，MPP解决了单个SQL数据库不能存放海量数据的问题。代表产品有Teradata，Vertica，Redshift，Greenplum

5） SQL 分类

1. DQL（数据查询语言）：Data Query Language
   SELECT WHERE ORDER BY HAVING
2. DML（数据操作语言）：Data ManipulationLanguage
   INSERT UPDATE DELETE
3. TPL（事务处理语言）：Transaction Control Language
   COMMIT（提交） SAVEPOINT（保存点） ROLLBACK（回滚）
4. DCL（数据控制语言）：Data Control Language
   GRANT REVOKE
5. DDL（数据定义语言）：Data Definition Language
   CREATE ALTER DROP TRUNCATE
6. CCL（指针控制语言）：Data Query Language
   说明：规定SQL语句在宿主语言的程序中的使用的规则
   DECLARE CURSOR（声明游标）
   FETCH INTO （进入）
   UPDATE WHERE CURRENT（更新当前位置）

6）其他相关概念

1. 国产化中IOE指的是：IBM的小型机、Oracle的数据库、EMC的存储。

1.2 Oracle 简介

1） 什么是Oracle数据库？

1、美国ORACLE公司（甲骨文）的数据库产品。称为Oracle database ，又称Oracle RDBMS。

2、关系型数据库。
3、以分布式数据库为核心。

2） Oracle数据库官方资料

Oracle数据库官网：点击进入
Oracle数据库官网（中国）：点击进入
Oracle数据库官方下载链接：点击进入
Oracle数据库官方下载链接（免费版）：点击进入

3） Oracle数据库发展史

Oracle发展史详细：点击进入

日期	版本	说明
1977年6月	第1版	无
1977年6月	第2版	无
1983年3月	第3版	无
1984年10月	第4版	无
1985年	第5版	无
1988年	第6版	无
1992年6月	第7版	无
1997年6月	Oracle 8	支持面向对象开发、支持处理大量用户和海里数据
1998年9月	Oracle 8i	“i”代表Internet。全方位支持JAVA
2001年6月	Oracle 9i	同上
2003年9月8日	Oracle 10g	“g”代表“grid ,网格”
2007年11月	Oracle 11g	甲骨文公司30年来发布的最重要的数据库版本。实现了信息生命周期管理。是10g的稳定版。
2013年6月26日	Oracle 12C	“C”代表“云计算”。实现了一个实例多个库。
2018年	Oracle 18C	开始按年份标记数据库版本
2019年	Oracle 19C	同上
2021年	Oracle 21C	同上

4） Oracle 数据库各版本说明

版本	缩写	说明
Oracle Database Enterprise Edition，EE	企业版	在线事务处理（OLTP)环境
Oracle Database Standard Edition One ,SE1	标准版1	小型数据库服务器功能
Oracle Database Standard Edition, SE	标准版	供了对更大型计算机和服务集群的支持
Oracle Database Personal Edition, PE	个人版	Windows平台上才提供个人版
Oracle Database XE (Express Edition) ，XE	学习版	免费，该版本可用于学习和演讲

5） Oracle DBA的要求

1. 熟悉操作系统的知识（如linux的vmstat、iostat等命令）、服务器基本知识（CPU核数与SQL语句并发执行的关系、内存与SGA、PGA的关系、磁盘（机械盘、SSD、PCIE-flash卡）的区别）、光纤交换机性能、尾纤、存储等等知识，这些知识便于后期优化。
2. 掌握数据库的基本结构，掌握数据库在Linux、Windows、AIX等不同操作系统下的安装配置、起停、Oracle数据库的备份、恢复、克隆、跨平台迁移、补丁。
3. 掌握oracle性能优化工具(AWR/ASH/ADDM)，通过不同报表去预防及解决oracle的性能问题。
4. 掌握Oracle数据库的高可用知识（RAC、 DATAGUARD、Oracle Golden Gate ）。

第2章 Oracle 建立和介绍

2.1 Oracle 下载

点击打开【下载页面】，选择下图所示【数据库企业版/标准版】，如图二选择版本进行下载即可。也可以拉到页面低端，选择【Oracle Database Express Edition】下载XE免费版。

2.2 Oracle 安装部署

1、安装部署方法很多，以下推荐几个我看过的好的知识

安装	参考
windows下Oracle 19c 安装部署	点击进行跳转
Windows下完全卸载卸载Oracle数据库	点击进行跳转
Linux下安装部署Oracle 19C rac集群	点击进行跳转

2、安装过程有很多遇到的坑，在此列出：

环境：win11 + Oracle 21c

注意事项	解决
用户名和主机名不要一致	有一些数据库配置文件读取时，会出现用户名和主机名错乱的情况。如安装遇阻，可修改用户名和主机名不一致。
当前用户不是administrator	要把当前用户添加至administrators用户组。
安装路径下不要有中文	可能会造成安装失败。
关闭杀毒软件	否则可能会安装失败。
win11下尽量实用较新的版本	老版本数据库对win11的兼容性不好。
完全卸载后进行安装	要注意清理安装失败后的注册表。

2.3 Oracle 环境变量及参数

根据部署方式不同，分为两种：1、将数据库安装在本地服务器，称为客户端。2、将数据库部署在远程服务器，服务端。

1、linux环境变量设置
客户端：
服务端：
2、Windows环境变量设置
客户端：
服务端：

2.4 Oracle 访问方式

1、命令行访问

一般使用SQLPLUS工具，访问方式：打开CMD命令行窗口，执行登录命令：
管理员登录：sqlplus ./ as sysdba
一般用户登录（不指定实例）：sqlplus scott/123
一般用户登录（指定实例）：sqlplus scott/123@orcl
输入密码后登录成功

C:\Users\tangc>sqlplus ./ as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 12月 29 21:23:01 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

输入口令:

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

2、工具访问
主要使用PLSQL Developer
—待补全—

2.5 Oracle 目录

1、admin目录

记录 Oracle 实例的配置，运行日志等文件。每个实例一个目录。 SID：System IDentifier 的缩写，是 Oracle 实例的唯一标记。在 Oracle 中一个实例只能 操作一个数据库。如果安装多个库那么就会有多个实例，我们可以通过实例 SID 来区分。 由于Oracle中一个实例只能操作一个数据库的原因oracle中也会使用SID来作为库的名称。

2、cfgtoollogs 目录

下面子目录分别存放当运行 dbca，emca，netca 等图形化配置程序时的 log。

3、checkpoints 目录

存放检查点文件

4、diag 目录

Oracle11g 新添加的一个重组目录。其中的子目录，基本上 Oracle 每个组件都有了自己 单独的目录，在oracle10g 中我们一直诟病的 log 文件散放在四处的问题终于得到解决，无论是asm还是 crs 还是 rdbms，所有组件需要被用来诊断的 log 文件都存放在了这个新的目 录下。

5、flash_recovery_area(闪回区)目录

闪回区：分配一个特定的目录位置来存放一些特定的恢复文件，用于集中和简化管理数据库恢复工作。闪回区可存储完全的数据文件备份、增量备份、数据文件副本、当前的控制 文件、备份的控制文件、spfile 文件、快照控制文件、联机日志文件、归档日志、块跟踪文 件、闪回日志。

6、oradata目录

存放数据文件
CONTROL01.CTL Oracle ：数据库的控制文件。
EXAMPLE01.DBF Oracle： 数据库表空间文件。
REDO01.LOG Oracle ：数据库的重做日志文件。
SYSAUX01.DBF 11g： 新增加的表空间。主要存储除数据字典以外的其他数据对象。由系统内部自动维护。
SYSTEM01.DBF ：用于存放 Oracle 系统内部表和数据字典的数据。比如，表名、列名、用户名等。
TEMP01.DBF ：临时表空间文件。
UNDOTBS01.DBF ：撤销表空间文件。用来保存回滚数据。
USERS01.DBF： 用户表空间。

7、product目录

Oracle RDBMS 的软件存放目录。RDBMS 即关系数据库管理系统(Relational Database ManagementSystem)。

2.6 数据库与实例

1、数据库名

1）数据库的标识。
2）数据库名存放的参数文件为：
$Oracle_HOME/admin/db_name/pfile/init.ora
$ORACLE_BASE/admin/db_name/pfile/init.ora
$ORACLE_HOME/dbs/SPFILE<实例名>.ORA)
3）不要随便修改数据库名。数据库名存放在参数文件和控制文件中，控制文件是二进制，用户无法修改；如果修改了参数文件中数据库名，那么启动时控制文件和参数文件的数据库名称不一致，导致数据库启动失败，将返回ORA-01103 错误。

2、实例

1）实例名是代表⽤于加载或打开⼀个数据库所⽤的memory structures + Background process (MEM + BGP)。内存＋后台程序。
2）访问Oracle都是访问⼀个实例，但这个实例如果关联了数据库⽂件，就是可以访问的，如果没有，就会得到实例不可⽤的错误。

3、实例和数据库

1）实例是临时的，它只在相关的进程和内存集合存在时存在；数据库是永久的，只要⽂件存在它就存在。
2）⼀个实例在其⽣存期内可安装和打开单个数据库；数据库可以被很多实例安装和打开，或者⼀个接⼀个的实例安装和打开，或者由多个实例同时安装和打开 (RAC)。
3）实例就是⼀组操作系统进程（或者是⼀个多线程的进程）以及⼀些内存，这些进程可以操作数据库；⽽数据库只是⼀个⽂件集合（包括数据⽂件、临时⽂件、重做⽇志⽂件和控制⽂件）。
4）在任何时刻，⼀个实例只能有⼀组相关的⽂件（与⼀个数据库关联）。⼤多数情况下，反过来也成⽴：⼀个数据库上只有⼀个实例对其进⾏操作。不过，Oracle的应⽤集群（Real Application Clusters，RAC）是⼀个例外，这是Oracle提供的⼀个选项，允许在集群环境中的多台计算机上操作，这样就可以有多台实例同时装载并打开⼀个数据库（位于⼀组共享物理磁盘上）。由此，我们可以同时从多台不同的计算机访问这个数据库。Oracle RAC能⽀持⾼度可⽤的系统，可⽤于构建可扩缩性极好的解决⽅案。

2.7 Oracle 体系结构

1、Oracle server组成：

实例instance
数据库database

总体结构分为三部分：

SGA
PGA
File文件

2、按照功能分为：

存储结构：包括控制文件、数据文件、日志文件等文件。
内存结构：包括系统全局区和程序全局区。
进程结构：包括前台进程和后台进程。

第3章 Oracle 使用

3.1 Oracle常用概念

trace文件：
Sqlplus工具：
控制文件：
参数文件，控制文件,数据文件和日志文件
实例：

3.2 Oracle 启停

1、启动Oracle服务

net start oracleserviceorcl

2、启动三阶段：>

第一阶段：nomount：启动实例，建立共享内存池。
启动到一阶段—> SQL> startup nomount
第二阶段mount：加载控制文件。
SQL> alter database mount;
启动到二阶段—> SQL> startup mount
第三阶段open：打开控制文件中的描述文件。
SQL> alter database open;
启动到三阶段—> SQL> startup

3、启动时加载参数文件。（略）

pfile
spfile

4、启动Oracle监听

说明：本地访问不需要启动，客户端访问需要，本质是进程代理。
SQL>lsnrctl start

5、断开数据库会话

SQL>quit
SQL>exit

6、关闭数据库

SQL>shutdown
SQL>shutdown immediate

7、几种启动

1、startup nomount

⾮安装启动，这种⽅式启动下可执⾏：重建控制⽂件、重建数据库
读取init.ora⽂件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora⽂件。

2、startup mount dbname

安装启动，这种⽅式启动下可执⾏：
数据库⽇志归档、
数据库介质恢复、
使数据⽂件联机或脱机，
重新定位数据⽂件、重做⽇志⽂件。
执⾏“nomount”，然后打开控制⽂件，确认数据⽂件和联机⽇志⽂件的位置，
但此时不对数据⽂件和⽇志⽂件进⾏校验检查。

3、startup open dbname

先执⾏“nomount”，然后执⾏“mount”，再打开包括Redo log⽂件在内的所有数据库⽂件，
这种⽅式下可访问数据库中的数据。

4、startup，等于以下三个命令

startup nomount
alter database mount
alter database open

5、startup restrict

约束⽅式启动
这种⽅式能够启动数据库，但只允许具有⼀定特权的⽤户访问
⾮特权⽤户访问时，会出现以下提⽰：
ERROR：
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的⽤户使⽤

6、startup force

强制启动⽅式
当不能关闭数据库时，可以⽤startup force来完成数据库的关闭
先关闭数据库，再执⾏正常启动数据库命令
等同于shutdown abort + startup的组合，即强制关闭数据库+ 正常启动数据库。

7、startup pfile=参数⽂件名

带初始化参数⽂件的启动⽅式
先读取参数⽂件，再按参数⽂件中的设置启动数据库
例：startup pfile=E:Oracleadminoradbpfileinit.ora

8、startup EXCLUSIVE

排它方式启动数据，只允许当前例程使用数据库

8、linux环境下启动

① 单机环境下

--------启动ORACLE系统--------
su - oracle
oracle>svrmgrl
SVRMGR>connect internal
SVRMGR>startup
SVRMGR>quit
--------关闭ORACLE系统--------
oracle>svrmgrl
SVRMGR>connect internal
SVRMGR>shutdown
SVRMGR>quit

②双机环境下

--------启动ORACLE系统--------
hareg -y oracle
--------关闭ORACLE系统--------
hareg -n oracle

3.3 Oracle 登录

四个系统用户：

1、sys

说明：Oracle的超级账户，权限最大。
登录1：sqlplus / as sysdba
登录2：sqlplus sys/123456 as sysdba

2、system

说明:通常用来创建一些用于查看管理信息的表和视图。

3、sysman

说明： oracle数据库中用于EM管理的用户，如果你不用该用户，也可以删除

4、scott

说明：oracle提供的示例用户。提供初学者学习操作的数据表（emp,dept,salgrade,bonus）
10g后oracle不再默认建立测试用户，可找回。21c无法找回。详细方法参加：
点击跳转：oracle测试用户scott建立

服务端登录：

1、管理员登录：$ sqlplus / as sysdba
2、普通用户登录（不指定实例）：$sqlplus scott/123

3、普通用户登录（指定实例）：$sqlplus scott/123@orcl

客户端登录：

1、原始登录方法：$sqlplus scott/123@//192.168.94.133/orcl
说明：192.168.94.133是服务端的IP地址

2、修改配置文件后的登录方法：$sqlplus scott/123@orcl_133
说明：“orcl_133”是“//192.168.94.133/orcl”的别名
【配置方法】
在客户端安文件添加下面字段：
E:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.oraE:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora
添加：

ortcl_133 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.133)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

3.4 Oracle 常用技巧

系统	功能	命令
Windows	打开运行	win + R
Windows	打开注册表	运行 + regedit
Windows	打开环境变量设置	运行 + “sysdm.cpl”-> 高级 -> 环境变量
Windows	DOS窗口清屏	clear SCR 或者 cls
Sqlplus工具	清屏	clear screen 或者 host cls
Sqlplus工具	执行命令	语句后面加上分号 ；
Sqlplus工具	大小写	表名、列名不区分大小写，但是数据表行数据匹配大小写
Sqlplus工具	每页显示行数 ------ 查询当前设置	set pagesize 1000 ------ show pagesize
Sqlplus工具	每行显示字符 ------ 查询当前设置	set linesize 120 ------ show linesize
Sqlplus工具	将number类型的user_id列显示为6位	col user_id format 999999; —或者— col user_id format 9999,99
Sqlplus工具	将字符类型的user_name列显示为40宽	col user_id format a40;
slqplus工具	设置结果的展示长度	set long 120; show long
slqplus工具	显示十行结果	select * from user_tables where rownum < 11;
Sqlplus工具	设置当前session是否对修改的数据自动提交	set autocommit on;
slqplus工具	交互式输入upper	举例：select text from all_source where owner=user and name=upper(‘&plsql_name’);

3.5 Oracle 常用表 - 未编辑

3.6 Oracle 面试技能 - 未编辑

1、dataguard
2、goldengate
3、RAC
4、RMAN
5、OCP\OCM证书

第4章 Oracle 命令

4.1 启停命令

分类	操作	命令
启动	启动服务	net start oracleserviceorcl
启动	启动监听	lsnrctl start 另进入监听控制台：lsnrctl
启动	监听相关	1、lsnrctl status：检查当前监听器的状态 2、lsnrctl start [listener-name]：启动所有的监听器,可以指定名字来启动特定的监听器 3、lsnrctl stop [listener-name]：关闭所有的监听器，可以指定名字来关闭特定的监听器 4、lsnrctl reload：重启监听器，此命令可以代替lsnrctl stop,lsnrctl start 5、lsnrctl hep：可以显示所有可用的监听器命令
启动	启动数据库	1、startup 2、startup nomount 3、startup mount
启动	进入sqlplus控制台	sqlplus nolog
启动	登录数据库	1、sysdba用户：sqlplus / as sysdba 2、其他用户： sqlplus 用户名/密码
启动	sqlplus连接数据库	conn 用户名/密码 连接到指定数据库：CONNECT user_name/passwd@数据库名称
停止	停止数据库	shutdown shutdown immediate
停止	停止监听	lsnrctl stop

4.2 查询命令

类别	操作	命令
查看	数据库名	select name from v$database;
查看	数据库实例名	select instance_name from v$instance;
查看	数据库版本	select * from v$version; show release; – Select version FROM Product_component_version where SUBSTR(PRODUCT,1,6)=‘Oracle’;
查看	数据库安装目录	select count(1) from dba_directories;
查看	数据库错误信息	show error;
查看	数据库的创建日期和归档方式	Select Created, Log_Mode, Log_Mode From V$Database;
查看	数据库初始化参数	show parameters; 查看具体参数：show PARAMETERS [parameter_name]
查看	当前数据库几个用户连接	select username,sid,serial# from v$session;
查看	用户	当前：show user; 所有：select username,account_status from dba_users;
查看	当前用户的角色	select * from user_role_privs
查看	当前用户的系统权限	select * from user_sys_privs
查看	当前用户的表级权限	select * from user_tab_privs;
查看	当前用户下的所有表	select * from user_tables
查看	包含LOG字符的表	select object_name,object_id from user_objects where instr(object_name,‘LOG’)>0;
查看	表的创建时间	select object_name,created from user_objects where object_name=upper(‘&table_name’);
查看	表的大小	select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);
查看	放入内存中的表	select table_name,cache from user_tables where instr(cache,‘Y’)>0;
查看	索引个数和类别	select index_name,index_type,table_name from user_indexes order by table_name;
查看	索引的索引字段	select * from user_ind_columns where index_name=upper(‘&index_name’);
查看	索引的大小	select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&index_name’);
查看	序列号，last_number是当前值	select * from user_sequences;
查看	视图名称	select view_name from user_views;
查看	视图的创建语句	select view_name,text_length from user_views; set long 2000; (根据视图的text_length值设定set long ) select text from user_views where view_name=upper(‘&view_name’);
查看	查看同义词的名称	select * from user_synonyms
查看	某表的约束条件	select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper(‘&table_name’); ------ select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper(‘&table_owner’) and c.table_name = upper(‘&table_name’) and c.owner = cc.owner and c.constraint_name = c.constraint_name order by cc.position;
查看	函数	select object_name,status from user_objects where object_type=‘FUNCTION’;
查看	存储过程	select object_name,status from user_objects where object_type = ‘PROCEDURE’;
查看	函数或存储过程的源码	select text from all_source where owner=user and name=upper(‘&plsql_name’);
查看	表空间名称及大小	select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
查看	表空间物理文件及大小	select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
查看	表空间使用情况	select sum(bytes)/(10241024) as free_space,tablespace_name from dba_free_space group by tablespace_name; — select a.tablespace_name,a.bytes total,b.bytes used, c.bytes free,(b.bytes100)/a.bytes “% used”,(c.bytes*100)/a.bytes “% free” from sys.sm$t{s}_{a}vaila,sys.sm$ts_used b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;
查看	回滚段名称及大小	select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;
查看	控制文件	select name from v$controlfile;
查看	日志文件	select member from v$logfile;
查看	数据库对象	select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

4.3 操作命令

类别	操作	命令
操作	停掉某个进程	alter system kill session ‘sid,serial#’;

4.5 执行sql脚本文件

1、执行sql文件

1、sqlplus连接

sql：@+路径+文件

2、工具连接

PLSQL Developer：文件→打开→SQL脚本
sqldeveloper：文件→打开

2、编写sql文件

spool E:\执行结果.log;
set feedback off;
set define off;
prompt -----初始化数据 start—
@E :\文件夹*1.sql
@E :\文件夹*2.sql
@E :\文件夹*3.sql
…
prompt -----初始化数据 end—
prompt done;
spool off;

3、常用参数

set echo off; #控制start命令不列出命令文件中的每一命令
set feedback off; #显示由查询返回的记录数 off等价为0
set heading off; #报表中列标题不打印
set pagesize off; #置 从顶部标题至页结束之间的行数
set linesize off; #置一行中显示的字符总数
set termout off; #由文件执行命令时不显示输出
set trimout on; #每一显示行的末端去掉空格
set trimspool on; #去掉spool输出时每行末端空格
col eventtypeid format A10; #字段eventtypeid 输出时的格式设置为文字型，且占10个字符宽度，下面类似
col eventtime format A10;
col srcip format A16;
col dstip format A16;
col srcport format A10;
col dstport format A16;
spool d:/home/oracle/http.txt; #将下面开始的输出记录到这个文件
select eventtypeid, eventtime,srcip, srcport, dstip, dstport , param from netids_eventlog t; #查询语句
spool off; #输出记录结束

第5章 SQL语言之DQL语言

5.1 语法

1. select [TOP|DISTINCT] [选择列表]|[*]
2. from 数据源
3. [where 查询条件]
4. [group by 分组条件]
5. [having 过滤条件]
6. [order by 排序条件 asc|desc nulls first|last];

5.2 执行顺序

（5）select [（5-3）TOP|（5-2）DISTINCT] （5-1）[选择列表]|[*]
（1）from 数据源
（2）[where 查询条件]
（3）[group by 分组条件]
（4）[having 过滤条件]
（6）[order by asc|desc nulls first|last];

5.3 运算符

条件运算符：>、>=、<、<=、=、<=>、!=、<>
逻辑运算符：and、or、not
模糊运算符：
like：%任意多个字符、_任意单个字符、如果有特殊字符，需要使用escape转义
between and
not between and
in
is null
is not null

5.4 查询语句

1、查询表

–查询所有员工的信息
select * from emp;

2、别名查询

–查询所有员工的姓名
select e.ename from emp e;

3、去重（唯一值）查询

–查询所有部门的编号
select distinct e.deptno from emp e;

4、条件查询

–查询工资>3000的员工信息
select * from emp where sal > 3000;

5、分组查询

–统计每个部门有多少个人
select deptno as “部门”,count(*) as “人数” from emp group by deptno;

6、分组过滤

–统计部门人数>5人的部门的编号
select deptno as “部门”, count() as “人数”
from emp
group by deptno
having count() > 5;

7、排序查询

–按照员工主管编号由高到低进行排序，NULL值放到最后边
select * from emp order by mgr desc nulls last;

8、分页查询

–查询前10条员工的信息
select *
from (select rownum hanghao,e.* from emp e) t
where t.hanghao >=1 and t.hanghao <= 10;
显示结果中的前10行,也可使用rownum<11。要结合排序得到想要的结果。
select *
from (select rownum hanghao,e.* from emp e) t
where t.hanghao >=1 and rownum<11;

9、多表查询

内连接

隐式内连接：select * from emp e1, dept d1 where e1.deptno = d1.deptno;
显示内连接：select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

外连接

左外连接
隐式左外连接：select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
显示左外连接：select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
右外连接
隐式右外连接：select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
显示右外连接：select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
全外连接：select * from emp e1 full outer join dept d1 on e1.deptno = d1.deptno;

交叉连接

隐式交叉连接：select * from emp, dept;
显示交叉连接：select * from emp e1 cross join dept d1;

10、联合查询

并集运算：将两个查询结果进行合并

/*
union : 它会去除重复的，并且排序
union all : 不会去除重复的，不会排序
*/
–工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
–工资大于1500或者20号部门下的员工
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

交集运算：找两个查询结果的交集

–工资大于1500并且20号部门下的员工
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;

差集运算：找两个查询结果的差集

–1981年入职员工(不包括总裁和经理)
select * from emp where to_char(hiredate,‘yyyy’) = ‘1981’
minus
select * from emp where job = ‘PRESIDENT’ or job = ‘MANAGER’;

注意事项：

列的类型要一致
列的顺序要一致
列的数量要一致，如果不够，可以使用null填充

11、子查询

关键词：

单行子查询：>、>=、<、<=、!=、<>、=、<=>
多行子查询：in、not in、any、some、all、exits

1、in的使用

–查询所有经理的信息
select * from emp where empno in (select mgr from emp where mgr is not null);

2、not in的使用

–查询不是经理的信息
select * from emp where empno not in (select mgr from emp where mgr is not null);

3、any的使用

–查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > any (select sal from emp where deptno = 10);

4、some的使用

–查询出比10号部门任意一个员工薪资高的员工信息
select * from emp where sal > some (select sal from emp where deptno = 10);

5、all的使用

–查询出比20号部门所有员工薪资高的员工信息
select * from emp where sal > all (select sal from emp where deptno = 20);

6、exits的使用

–查询有员工的部门的信息
select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno);

第6章 SQL语言之DCL语言

6.1 表空间

创建表空间
语法

create tablespace 表空间的名称
datafile ‘文件的路径’
size 初始化大小
autoextend on
next 每次扩展的大小;

演示

vcreate tablespace mytest
datafile ‘d:/mytest.dbf’
size 100m
autoextend on
next 10m;

删除表空间
语法

drop tablespace 表空间的名称;

演示

drop tablespace mytest;

6.2 用户

创建用户
语法

create user 用户名
identified by 密码
default tablespace 表空间的名称;

演示

create user zhangsan
identified by 123456
default tablespace mytest;

删除用户 - DBA用户操作

drop user xxx；删除用户，用户下无对象。
drop user xxx cascade;删除用户及用户下对象。

6.3 权限

1、权限列表
系统权限分类：DBA用户授予

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

实体权限分类

select、update、insert、alter、index、delete、all

2、给用户授权 - DBA用户操作
语法

grant 系统权限列表 to 用户名;
或者
grant 实体权限列表 on 表名称 to 用户名;

演示

grant CONNECT to zhangsan;
或者
grant CONNECT,RESOURCE to zhangsan;
或者
grant CONNECT,RESOURCE,DBA to zhangsan;
或者
grant DBA to zhangsan;
或者
grant all on emp to zhangsan;

2、取消授权
语法

revoke 系统权限列表 from 用户名;
或者
revoke 实体权限列表 on 表名称 from 用户名;

演示

revoke CONNECT from zhangsan;
或者
revoke CONNECT,RESOURCE from zhangsan;
或者
revoke CONNECT,RESOURCE,DBA from zhangsan;
或者
revoke DBA from zhangsan;
或者
revoke all on emp from zhangsan;

6.4 修改密码

语法

alter user 用户名 identified by “密码”;

演示

alter user zhangsan identified by “123456”;

第7章 SQL语言之DDL语言

7.1 数据类型

类型	关键字	说明
字符串类型	CHAR	定长字符串，可用空格填充达到其最大长度，最多可存储2000字节信息
字符串类型	NCHAR	包含UNICODE格式数据的定长字符串，最多可存储2000字节信息
字符串类型	VARCHAR2	变长字符串，不能用空格填充来达到其最大长度，最多可存储4000字节信息
字符串类型	NVARCHAR2	包含UNICODE格式数据的变长字符串，最多可存储4000字节信息
数值类型	NUMBER	NUMBER(p,s)是最常见的数字类型，关于NUMBER的有效位§和精确位(s)遵循以下规则： p：是有效数据总位数，取值范围为【1-38】，默认值是38 s：表示精确到多少位，取值范围为【-84-127】，默认值是0
数值类型	INTEGER	INTEGER是NUMBER的子类型，它等同于NUMBER(38,0)，用来存储整数
数值类型	FLOAT	Float(n)是NUMBER的子类型 数 n 指示位的精度，n值的范围可以从1到126

7.2 创建表

语法

create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);

示例

create table users(
id number,
username varchar2(20),
password varchar2(20)
);

7.3 复制表

语法

create table 表名 as 查询语句;

示例

create table emp_copy as
select * from emp
;

7.4 删除表

语法

drop table 表名;

示例

drop table emp_copy;

7.4 清空表

语法

方式一：delete from table 表名;
方式二：truncate table 表名;

示例

方式一：delete from table emp_copy;
方式二：truncate table emp_copy;

7.5 修改表

1、添加一列

格式：alter table 表名 add 列名 列的类型;
示例：alter table users add phone varchar2(11);

2、修改列名

格式：alter table 表名 rename column 旧列名 to 新列名;
示例：alter table users rename column phone to mobile;

3、修改类型

格式：alter table 表名 modify 列名 列的类型;
示例：alter table users modify mobile char(11);

4、删除一列

格式：alter table 表名 drop column 列名;
示例：alter table users drop column mobile;

5、修改表名

格式：rename 旧表名 to 新表名;
示例：rename users to myusers;

7.6 表约束

1、语法

CREATE TABLE 表名(
列名 列的类型 primary key,–主键约束
列名 列的类型 not null,–非空约束
列名 列的类型 unique,–唯一约束
列名 列的类型 check(列名 in (检查列表)),–检查约束
constraint 约束名 foreign key(字段名) references 主表(被引用列)–外键约束
) ;

2、示例

–商品分类表
create table category(
cid number primary key,
cname varchar2(20)
);


–商品详情表
create table product(
pid number primary key,–主键约束
pname varchar2(50) not null,–非空约束
pimg varchar2(50) unique,–唯一约束
pflag varchar2(10) check(pflag in (‘上架’,‘下架’)),–检查约束
cid number,
constraint FK_CATEGORY_ID foreign key(cid) references category(cid)–外键约束
);

3、修改

1、主键约束

添加
alter table product add constraint PK_PRODUCT_PID primary key(pid);
删除
alter table product drop constraint PK_PRODUCT_PID;
或者
alter table product drop primary key;
2、非空约束
添加
alter table product modify pname not null;
删除
alter table product modify pname null;

3、唯一约束

添加
alter table product add constraint UK_PRODUCT_PIMG unique(pimg);
删除
alter table product drop constraint UK_PRODUCT_PIMG;
或者
alter table product drop unique(pimg);

4、检查约束

添加
alter table product add constraint CK_PRODUCT_PFLAG check(pflag in (‘上架’,‘下架’));
删除
alter table product drop constraint CK_PRODUCT_PFLAG;

5、外键约束

添加
alter table product add constraint FK_PRODUCT_ID foreign key(cid) references category(cid);
删除
alter table product drop constraint FK_PRODUCT_ID;

第8章 SQL语言之DML语言

8.1 插入语句

格式：insert into 表名(列名1,列名2,…) values(值1,值2,…);
演示：insert into category(cid,cname) values(1,‘电视’);
注意：commit;

8.2 修改语句

格式：update 表名 set 列名1=值1,列名2=值2,… where 查询条件;
演示：update category set cname=‘汽车’ where cid = 1;
注意：commit;

8.3 删除语句

格式：delete from 表名 where 查询条件;
演示：delete from category where cid = 1;
注意：commit;

第9章 SQL语言之TCL语言

9.1 事务

名词	定义
事务	一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
特点	1、原子性：一个事务是不可再分割的整体，要么都执行要么都不执行 2、一致性：一个事务的执行不能破坏数据库数据的完整性和一致性 3、隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的 4、持久性：一个事务一旦提交了，则永久的持久化到本地

9.2 事务操作

操作	方法
开启事务	Oracle 中事务是隐式自动开始，无需显示手动开启。
编写一组逻辑sql语句	注意：sql语句支持的是insert、update、delete
【设置回滚点】	命令：savepoint + 回滚点名;
结束事务	提交：commit; 回滚：rollback; 回滚到指定的地方： rollback to 回滚点名;

9.3 隔离级别 - 数据一致性（读问题）

类型	问题	解决
概念	并发	指的是多个用户同时操作。如应用的并发，指的是，多个用户同时访问应用程序。数据库的并发指的是多个用户同时对数据库进行操作。
概念	数据库并发	在单用户数据库中，用户可以修改数据，而不用担心其他用户同时修改相同的数据。然而，在多用户数据库中，多个同时交易中的语句可能更新相同的数据。同时执行的事务必须产生有意义和一致的结果。
概念	事务并发	指的是数据库中多个事务同时执行，一般事务并发引发问题，是由于多个事务同时操作了相同数据。
概念	读问题	事务并发的问题，一般就是数据库的读问题。
概念	数据一致性(data consistency)	多用户数据库中每个能够看到一致的数据。
概念	数据并发（data concurrency）	多个用户同时访问相同数据时，多用户数据库管理系统必须提供足够的并发性控制，以便数据不会被不正确地更新或更改，从而损害数据完整性。
概念	隔离级别 （Isolation Level）	即事务的隔离级别，一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。 数据库设置隔离级别来解决并发问题。
事务并发问题	脏读（dirty read）	一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样。 Oracle不允许一个会话读取其他事务未提交的数据修改结果，不存在脏读。
事务并发问题	不可重复读（nonrepeatable read）	一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
事务并发问题	幻读 不存在读取 （phantom read）	一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样
orcale隔离级别	已提交读取 （Read Committed Isolation Level）	在读提交隔离级别中，事务执行的每个查询只能看到在查询(而不是事务)开始之前提交的数据。
orcale隔离级别	可串行化或序列化 （Serializable Isolation Level）	在可序列化隔离级别中，事务只看到事务(而不是查询)开始时提交的更改以及事务本身所做的更改
orcale隔离级别	只读模式 （Read-Only Isolation Level）	只读隔离级别与可序列化隔离级别相似，但只读事务不允许在事务中修改数据，除非用户是 sys。

9.3.1 标准ANSI/ISO隔离级别与数据读取一致性

隔离级别	脏读（dirty read）	不可重复读（nonrepeatable read）	幻读（phantom read）
未提交读 （Read uncommitted）	√	√	√
提交读 （Read committed）	×	√	√
可重复读 （Repeatable read）	×	×	√
序列化读 （Serializable）	×	×	×

9.3.2 Oracle设置隔离级别

1、应用程序的设计开发者及数据库管理员可以依据应用程序的需求及系统负载（workload）而为不同的事务选择不同的隔离级别（isolation level）用户可以在事务开始时使用以下语句设定事务的隔离级别，一般使用sysdba用户：

隔离级别	设置命令
已提交读模式	SET TRANSACTION ISOLATION LEVEL＝READ COMMITTED;
串行模式	SET TRANSACTION ISOLATION LEVEL＝ SERIALIZABLE;
只读模式	SET TRANSACTION＝ READ ONLY;

2、如果在每个事务开始时都使用 SET TRANSACTION 语句，将加重网络及处理器的负担。用户可以使用 ALTER SESSION 语句改变一个会话内所有事务的默认隔离级别，无法设置为只读隔离级别：

隔离级别	设置命令
串行模式	ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
已提交读模式	ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

9.4 锁 - 丢失更新（写问题）

概念	解释
一致性问题	事务A对数据进行修改、增加，事务B总是在做读操作。所以，结果出现脏读、不可重复读、幻读等问题，称为一致性问题。简称，读问题。
丢失更新问题	两事务都在对数据进行修改则会导致丢失更新问题。
锁	数据库未解决丢失更新设定的机制。 单用户模式没有锁，多用户数据库才有锁。
锁的模式	Oracle多用户数据库有两种锁：排他锁（Exclusive lock mode）和共享锁（Share lock mode）。
排他锁（Exclusive lock mode）	此模式防止关联的资源被共享。事务在修改数据时获得独占锁。第一个专用锁定资源的事务是唯一可以在释放专用锁定之前更改资源的事务。
共享锁（Share lock mode）	此模式允许共享关联的资源，具体取决于所涉及的操作。读取数据的多个用户可以共享数据，每个用户都持有一个共享锁，以防止需要独占锁的写入器进行并发访问。多个事务可以获取同一资源上的共享锁。
锁转换（Lock Conversion）	数据库会自动将限制性较低的表锁转换为限制性较高的表锁，如共享表锁转换为排他表锁。
锁升级Lock Escalation	1、多个锁保持在一个粒度级别(例如行)并且数据库将锁升级到更高的粒度级别(例如 table)时，行锁升级为表锁。 2、Oracle不支持锁升级。锁升级大大增加了死锁的可能性。例子：假设系统正试图代表事务1升级锁，但由于事务2持有的锁而无法升级锁。如果事务2在继续之前还需要相同数据的锁升级，则会创建死锁。
死锁deadlock	1、死锁是指两个或多个用户正在等待彼此锁定的数据的情况。死锁阻止某些事务继续工作。 说明：Oracle自动检测死锁并解决它们，方法是回滚一个与死锁有关的语句，释放一组相互冲突的行锁。数据库向进行语句级回滚的事务返回相应的消息。回滚的语句属于检测死锁的事务。通常，发出信号的事务应该显式回滚，但它可以在等待后重试回滚语句。
自动锁Automatic Locks	数据库代表一个事务自动锁定一个资源，以防止其他事务做出独占访问同一资源的事情。
手动锁Manual Data Locks	可以手动覆盖预言机数据库的默认锁定机制。
悲观锁	1、认为两个事务更新操作一定会发生丢失更新 2、账户交易频繁建议直接使用悲观锁 解决：通过在语句后边添加for update来实现行级上锁，所以又称为“行级锁”，例如：select * from t_account t wheret.id=‘1’ for update;
乐观锁	1、认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定 2、如果是交易减库存的操作可以考虑乐观锁，保证并发度。 解决：由程序员自己解决，可以通过给数据表添加自增的version字段或时间戳timestamp，进行数据修改时，数据库会检测version字段或者时间戳是否与原来的一致，若不一致，抛出异常或者重新查询

9.4.1 自动锁

锁类别	描述	包含的具体锁
DML Locks	保护数据。例如，表锁锁定整个表，而行锁定选定的行。	1、Row Locks (TX)行锁：行锁(row lock) ，也称为 TX 锁，是指单行表上的一个锁。事务为每一个被 INSERT、 UPDATE、 DELETE、 MERGE 或 SELECT… FOR 更新语句修改的行获取一个行锁。在事务提交或回滚之前，行锁一直存在。 2、Table Locks ™表锁：当一个表被带有 FOR UPDATE 子句或 LOCK TABLE 语句的 INSERT、 UPDATE、 DELETE、 MERGE、 SELECT 修改时，事务就会获得一个表锁(也称为 TM 锁)。 3、锁和外键：外键没有被索引，那么子表可能会被锁定得更频繁，会出现死锁，并发性也会减少。
DDL Locks	当数据库对象被DDL语句操作时，DDL锁保护对象的定义。	1、Exclusive DDL Locks独占锁：独占 DDL 锁可以防止其他会话获取 DDL 或 DML 锁。 2、Share DDL Locks 共享锁：资源的共享 DDL 锁可以防止相互冲突的 DDL 操作受到破坏性干扰，但允许对类似的 DDL 操作进行数据并发性。； 3、Breakable Parse Locks解析锁：获取解析锁是为了在引用对象被更改或删除时使关联的共享 SQL 区域失效。
System Locks	数据库使用各种类型的系统锁来保护内部数据库和内存结构。用户无法访问这些机制，因为用户无法控制它们的发生或持续时间。	1、Latches 锁存器：简单的低级串行化机制，用于协调对共享数据结构、对象和文件的多用户访问，保护共享内存资源免受损坏，通常保护一组对象。 2、Mutexes互斥锁：防止内存中的对象在被并发进程访问时过期或损坏，通常保护单个对象。 3、Internal Locks内部锁：内部锁是比锁存器和互斥锁更高级、更复杂的机制，可用于各种目的。包含：Dictionary cache locks字典缓存锁、File and log management locks文件和日志管理锁、Tablespace and undo egment locks表空间和撤消段锁。

9.4.2 手动锁

类别	更新命令:需要查询更具体的命令。
事务	1、SET TRANSACTION ISOLATION LEVEL 2、LOCK TABLE 3、SELECT … FOR UPDATE
PL/SQL	1、Request a lock of a specific type 2、Give the lock a unique name recognizable in another procedure in the same or in another instance 3、Change the lock type 4、Release the lock
手动添加行锁	语句后边添加for update来实现行级上锁 示例：select * from t_account t wheret.id=‘1’ for update;

第10章 数据库对象

10.1 序列

1、概念

序列:

Sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。

作用:

1、可使用序列当作主键。
2、可使用序列实现自增列。

2、语法

create sequence 序列名称
start with 从几开始
increment by 每次增长多少
[maxvalue 最大值] | nomaxvalue
[minvalue 最小值] | nominvalue
cycle | nocycle --是否自动循环
[cache 缓存数量] | nocache;

3、示例

--创建序列
create sequence auto_increment_seq
start with 1
increment by 1
nomaxvalue
minvalue 1
nocycle
cache 10000;

--调用序列
select auto_increment_seq.nextval from dual;
select auto_increment_seq.currval from dual;

10.2 索引

1、语法

创建索引

create [UNIQUE]|[BITMAP] index 索引名 on 表名(列名1,列名2,...);

修改索引

–重命名索引
alter index 索引名称 rename to 新的名称;
–合并索引
alter index 索引名称 coalesce;
–重建索引
alter index 索引名称 rebuild;
–修改某列
先删除，在创建

删除索引

drop index 索引名称;

2、示例

创建索引

create index INX_CATEGORY_CNAME on category(cname);

修改索引

–重命名索引
alter index INX_CATEGORY_CNAME rename to INX_CATEGORY_CNAME_NEW;
–合并索引
alter index INX_CATEGORY_CNAME_NEW coalesce;
–重建索引
alter index INX_CATEGORY_CNAME_NEW rebuild;
–修改某列
先删除，在创建

删除索引

drop index INX_CATEGORY_CNAME;

10.3 视图

1、概念

视图是对查询结果的一个封装，视图里面所有的数据，都是来自于它查询的那张表，视图本身不存储任何数据，但是可以修改原数据，但是不建议这样使用

2、语法

创建视图

create view 视图名称
as 查询语句
[with read only];

修改视图

create or replace view 视图名称
as 查询语句
[with read only];

删除视图

drop view 视图名称;

3、示例

创建视图

create view view_emp as
select ename,job,mgr from emp;

修改视图

create or replace view view_emp as
select ename,job,mgr,deptno from emp;

删除视图

drop view view_emp;

10.4 同义词

1、概念

同义词就是别名的意思和视图的功能类似，就是一种映射关系

2、语法

创建同义词

create [public] synonym 同义词名称 for 对象的名称;
修改同义词
create or replace [public] synonym 同义词名称 for 对象的名称;
删除同义词
drop [public] synonym 同义词名称;

3、示例

创建同义词

--创建
create synonym syno_emp for emp;
--调用
select * from syno_emp;

修改同义词

--创建
create or replace synonym syno_emp_update for emp;
--调用
select * from syno_emp_update;

删除同义词

drop synonym syno_emp_update;

10.5 游标

1、概念

游标是用来操作查询结果集，相当于是JDBC中ResultSet，它可以对查询的结果一行一行的获取

2、语法

--第一步：定义游标
    --第一种：普通游标
    cursor 游标名[(参数 参数类型)] is 查询语句;
    --第二种：系统引用游标
    游标名 sys_refcursor;

--第二步：打开游标
    --第一种：普通游标
    open 游标名[(参数 参数类型)];
    --第二种：系统引用游标
    open 游标名 for 查询语句;

--第三步：获取一行
	fetch 游标名 into 变量;

--第四步：关闭游标
	close 游标名;

3、示例

-----普通游标使用-----

--输出指定部门下的员工姓名和工资
declare
  --1.声明游标
  cursor vrows(dno number) is select * from emp where deptno = dno;
  --声明变量
  vrow emp%rowtype;
begin
  --2.打开游标 
  open vrows(10);
  --3.循环遍历
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
  end loop;
  --4.关闭游标
  close vrows;
end;

-----系统引用游标使用-----

--输出员工表中所有的员工姓名和工资
declare
  --1.声明系统引用游标
  vrows sys_refcursor;
  --声明变量
  vrow emp%rowtype;
begin
  --2.打开游标
  open vrows for select * from emp;
  --3.循环遍历
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal);
  end loop;
  --4.关闭游标
  close vrows;
end;

-----使用for循环输出-----

--输出员工表中所有的员工姓名和工资
declare
  cursor vrows is select * from emp;
begin
  --自动定义变量vrow，自动打开游标，自动关闭游标
  for vrow in vrows loop
    dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal || '工作:' || vrow.job);
  end loop;
end;

10.6 存储过程

1、概念

存储过程实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效

2、语法
-----创建存储过程-----

create procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
 --声明部分
begin
 --业务逻辑 
end;

-----修改存储过程-----

create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...)
is|as
 --声明部分
begin
 --业务逻辑 
end;

-----删除存储过程-----

drop procedure 存储过程名称;

-----调用存储过程-----

--方式一：
call 存储过程名称(...);

--方式二：
declare

begin
  存储过程名称(...);
end;

3、示例
-----创建存储过程-----

--给指定员工涨薪并打印涨薪前和涨薪后的工资
create procedure proc_update_sal(vempno in number,vnum in number)
is
  --声明变量
  vsal number;
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:' || vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:' || (vsal + vnum));
  --提交事物
  commit;
end;

--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);

-----修改存储过程-----

--给指定员工涨薪并打印涨薪前和涨薪后的工资
create or replace procedure proc_update_sal(vempno in number,vnum in number)
is
  --声明变量
  vsal number;
begin
  --查询当前的工资
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前的工资
  dbms_output.put_line('涨薪前:' || vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后的工资
  dbms_output.put_line('涨薪后:' || (vsal + vnum));
  --提交事物
  commit;
end;

--给员工编号为7521的员工涨工资10元
call proc_update_sal(7521, 10);

-----删除存储过程-----

drop procedure proc_update_sal;

10.7 函数

1、概念

函数实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效，它跟存储过程没有什么本质区别，存储过程能做的函数也能做，只不过函数有返回值

2、语法
-----创建函数-----

create function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
 --声明部分
begin
 --业务逻辑 
end;

-----修改函数-----

create [or replace] function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型
is|as
 --声明部分
begin
 --业务逻辑 
end;

-----删除函数-----

drop function 函数名称;

-----调用函数-----

--方式一：
select 函数名称(...) from dual;

--方式二：
declare
  变量名 变量类型;
begin
  变量名 = 函数名称(...);
end;

3、示例
-----创建函数-----

--查询指定员工的年薪
/*
    参数 : 员工的编号
    返回 : 员工的年薪          
*/
create function func_getsal(vempno number) return number
is
  vtotalsal number;
begin
  select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--查询员工编号为7788的年薪
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

-----修改函数-----

--查询指定员工的年薪
/*
    参数 : 员工的编号
    返回 : 员工的年薪          
*/
create or replace function func_getsal(vempno number) return number
is
  vtotalsal number;
begin
  select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--查询员工编号为7788的年薪
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

-----删除函数-----

drop function func_getsal;

10.7 触发器

1、概念

当用户执行了 insert | update | delete 这些操作之后，可以触发一系列其它的动作、业务逻辑，使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发，还支持语句级触发。

2、分类

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

3、语法
-----创建触发器-----

create trigger 触发器名称
before|after
insert|update|delete 
on 表名称
[for each row]--行级触发器
declare
 --声明部分
begin
 --业务逻辑 
end;

-----修改触发器-----

create [or replace] trigger 触发器名称
before|after
insert|update|delete 
on 表名称
[for each row]--行级触发器
declare
 --声明部分
begin
 --业务逻辑 
end;

-----删除触发器-----

drop trigger 触发器名称;

3、示例
-----INSERT 型触发器-----

--新员工入职之后，输出一句话: 欢迎加入我们
create or replace trigger tri_emp_insert
after
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入我们');
end;

--插入数据就可以自动触发触发器
insert into emp(empno, ename) values(9527, '马哈哈');

-----UPDATE 型触发器-----

--判断员工涨工资后的工资一定要大于涨工资前的工资
create or replace trigger tri_emp_update_sal
before
update
on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

--更新数据就可以自动触发触发器（无异常）
update emp set sal = sal + 10;
select * from emp;

--更新数据就可以自动触发触发器（有异常）
update emp set sal = sal - 100;
select * from emp;

-----DELETE 型触发器-----

--老员工离职之后，输出一句话: 有员工离职了
create or replace trigger tri_emp_delete
after
delete
on emp
declare

begin
  dbms_output.put_line('有员工离职了');
end;

--删除数据就可以自动触发触发器
delete from emp where empno = 9527;

-----删除触发器-----

drop trigger tri_emp_insert;
drop trigger tri_emp_update_sal;
drop trigger tri_emp_delete;

第11章 PL/SQL编程

11.1 PL/SQL编程基础

1、概念

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）

2、语法

declare
-- 声明变量

begin
-- 业务逻辑


end;

3、变量

declare
–声明变量
– 格式一：变量名 变量类型;
– 格式二：变量名 变量类型 := 初始值;
– 格式三：变量名 变量类型 := &文本框名;
– 格式四：变量名 表名.字段名%type;
– 格式五：变量名 表名%rowtype;
vnum number;
vage number := 28;
vabc number := &abc;–输入一个数值，从一个文本框输入
vsal emp.sal%type; --引用型的变量，代表emp.sal的类型
vrow emp%rowtype; --记录型的变量，代表emp一行的类型
begin
–业务逻辑
dbms_output.put_line(vnum); --输出一个未赋值的变量
dbms_output.put_line(vage); --输出一个已赋值的变量
dbms_output.put_line(vabc); --输出一个文本框输入的变量
select sal into vsal from emp where empno = 7654; --将查询到的sal内容存入vsal并输出
dbms_output.put_line(vsal);
select * into vrow from emp where empno = 7654; --将查询到的一行内容存入vrow并输出
dbms_output.put_line(vrow.sal);
dbms_output.put_line(123); --输出一个整数
dbms_output.put_line(123.456); --输出一个小数
dbms_output.put_line(‘Hello,World’); --输出一个字符串
dbms_output.put_line(‘Hello’||‘,World’); --输出一个拼接的字符串，||拼接符Oracle特有
dbms_output.put_line(concat(‘Hello’,‘,World’)); --输出一个拼接的字符串，concat函数比较通用
end;

11.2 if 语句

1、语法

if 条件1 then

elsif 条件2 then

else

end if;

2、示例

declare
  age number := &age;
begin
  if age < 18 then
    dbms_output.put_line('小屁孩');
  elsif age >= 18 and age <= 24 then
    dbms_output.put_line('年轻人');
  elsif age > 24 and age < 40 then
    dbms_output.put_line('老司机');
  else
    dbms_output.put_line('老年人');
  end if;
end;

11.3 where 语句

1、语法

while 条件 loop
        
end loop;

2、示例

declare
  i number := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

11.4 for 循环

1、语法

for 变量  in [reverse] 起始值..结束值 loop
        
end loop;

2、示例

declare

begin
  for i in reverse 1 .. 10 loop
    dbms_output.put_line(i);
  end loop;
end;

11.5 loop循环

1、语法

loop
  
  exit when 条件
  
end loop;

2、示例

--输出1~10
declare
  i number := 1;
begin
  loop
    exit when i > 10;
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

11.6 异常

1、含义

意外是程序运行时发生的异常，相当与java中的异常。

2、语法

declare
   --声明变量
begin
   --业务逻辑
exception
   --处理异常
   when 异常1 then
     ...
   when 异常2 then
     ...
   when others then
     ...处理其它异常
end;

3、说明

系统异常

异常类别	异常代码	解释
系统异常	zero_divide	除数为零异常
系统异常	value_error	类型转换异常
系统异常	no_data_found	没有找到数据
系统异常	too_many_rows	查询出多行记录，但是赋值给了%rowtype一行数据变量

自定义异常

declare
   --声明变量
   异常名称 exception;
begin
   --业务逻辑
   if 触发条件 then
      raise 异常名称; --抛出自定义的异常
exception
   --处理异常
   when 异常名称 then
     dbms_output.put_line('输出了自定义异常');  
   when others then
     dbms_output.put_line('输出了其它的异常');  
end;

4、示例

-----内置系统异常-----

  vi   number;
  vrow emp%rowtype;
begin
  --以下四行对应四个异常，测试请依次放开
  vi := 8/0;  
  --vi := 'aaa';
  --select * into vrow from emp where empno = 1234567;
  --select * into vrow from emp;
exception
  when zero_divide then
    dbms_output.put_line('发生除数为零异常');
  when value_error then
    dbms_output.put_line('发生类型转换异常');
  when no_data_found then
    dbms_output.put_line('没有找到数据异常');
  when too_many_rows then
    dbms_output.put_line('查询出多行记录，但是赋值给了%rowtype一行数据变量');
  when others then
    dbms_output.put_line('发生了其它的异常' || sqlerrm);
end;

-----抛出系统异常-----

--查询指定编号的员工，如果没有找到，则抛出系统异常
declare
  --1.声明一个变量 %rowtype
  vrow emp%rowtype;
begin
  --查询员工信息，保存起来
  select * into vrow from emp where empno = 8000;
  --判断是否触发异常的条件
  if vrow.sal is null then
    --抛出系统异常
     raise_application_error(-20001,'员工工资为空');
  end if;
exception
  when others then
    dbms_output.put_line('输出了其它的异常' || sqlerrm);
end;

-----抛出自定义异常-----

--查询指定编号的员工，如果没有找到，则抛出自定义异常
declare
  --1.声明一个变量 %rowtype
  vrow emp%rowtype;
  --2.声明一个自定义的异常
  no_emp exception;
begin
  --查询员工信息，保存起来
  select * into vrow from emp where empno = 8000;
  --判断是否触发异常的条件
  if vrow.sal is null then
    raise no_emp; --抛出自定义的异常
  end if;
exception
  when no_emp then
    dbms_output.put_line('输出了自定义异常');
  when others then
    dbms_output.put_line('输出了其它的异常' || sqlerrm);
end;