Oracle
Oracle入门
Oracle概述
Oracle公司介绍
- 甲骨文公司，全称甲骨文股份有限公司(甲骨文软件系统有限公司)，是全球最大的企业级软件公司，总部位于美国加利福尼亚州的红木滩
- 1989年正式进入中国市场
诞生初期
- 20世纪约70年代 一间名为Ampex的软件公司，正为中央情报局设计一套名叫Oracle的数据库，埃里森是程序员之一。
- 1977年埃里森与同事Robert Miner创立“软件开发实验室”（Software Development Labs），当时IBM发表“关系数据库”的论文，埃里森以此造出新数据库，名为甲骨文。
- 1978年 公司迁往硅谷，更名为“关系式软件公司”（RSI）。RSI在1979年的夏季发布了可用于DEC公司的PDP-11计算机上的商用ORACLE产品，这个数据库产品整合了比较完整的SQL实现，其中包括子查询、连接及其他特性。
- 第一个客户
- 美国中央情报局
发展壮大
- 1984年
先后进军加拿大、荷兰、英国、奥地利、日本、德国、瑞士、瑞典、澳洲、芬兰、法国、中国香港、挪威、西班牙 - 1986年上市时
年收入暴升至5500万美元，同年3月招股，集资3150万美元。 - 1987年
两年内再进军墨西哥、巴西、中国、塞浦路斯、马来西亚及新西兰。一年后，收入再升一倍至2.82亿美元。 - 1990年，
甲骨文两年内挥军进入智利、希腊、韩国、葡萄牙、土耳其、委内瑞拉、中国台湾、比利时、阿根廷、哥伦比亚、哥斯达黎加及菲律宾等地， - 1992年
旗舰产品Oracle 7面世，使该公司业务重新步上轨道，年收入达到11.79亿美元。曾被视为甲骨文接班人、但后来被踼出局的Raymond Lane担任首席运营官
收购与上市
- 2009年4月20日，甲骨文公司宣布以每股9.50美元，总计74亿美元收购太阳微系统公司（Sun）。
- 2018年12月，世界品牌实验室编制的《2018世界品牌500强》揭晓，甲骨文公司排名第31 。
Oracle安装步骤
-
1、解压以上下载的Oracle安装文件
-
2、 将win64_11gR2_database_2of2\database\stage\Components文件夹下的所有文件拷到win64_11gR2_database_1of2\database\stage\Components目录下
将以上所有文件拷贝到win64_11gR2_database_1of2\database\stage\Components目录下否则在Oracle11g安装过程中会出现以下错误：未找到文件 E:\app\xxj\product\11.2.0\dbhome_1\owb\external\oc4j_applications\applications\WFMLRSVCApp.ear，其实oracle安装软件的第二部分就是用来放到第一部分中使用的。
-
3、在win64_11gR2_database_1of2\database目录下点击setup.exe安装程序，启动Oracle的安装
-
4、Oracle启动安装弹出如下界面，进行如下选择：
-
5、取消安全更新，在弹出的警告窗口点击“确定”按钮
-
6、确定安装选项为创建和配置数据库
-
7、在系统类中选择安装为桌面类，如果你这个机器只用于安装oracle数据库，不会运行其他应用，那么你可以选择“服务器类”
-
8、进入典型安装选项，选择自己安装数据库的目录、数据库版本、字符集、实例名称和系统用户的密码
-
9、系统进行先决条件检查
注意：如果出现：Environment variable: “PATH” - This test checks whether the length of the environment variable "PATH - This test checks whether the length of the environment variable “PATH” does not exceed the recommended length Environment variable: “PATH”.这个错误，那就说明你的Path环境变量长度太长，你可以编辑环境变量，将不需要的环境变量先删除，但是千万不要将windows系统自带的那些环境变量删除。
windows下的PATH值在 “我的电脑”右键->“属性” -> “高级系统设置” -> "环境变量“，编辑其中的Path环境变量 -
10、先决条件检查通过就会出现一个安装配置的概要文件，你可以详细检查一下自己的配置，还可以保存这个配置文件
-
11、如果没什么问题就点击”完成“按钮，进行Oracle的安装和实例拷贝
-
12、安装完成后就会提示安装成功，并会显示日志文件所在目录，此时可以进行口令管理为不同的用户设置口令，如何你不设置，那么默认系统用户名为：system密码为你刚才在典型安装中配置的密码
-
13、点击”确定“按钮，提示安装成功
- 检查
Oracle服务
- 我的电脑->管理->服务->Oracle
- 查看Oracle服务的安装情况
- 其中服务名为：OracleRemExecService是安装过程启动的临时服务，在完成Oracle的安装后，重新启动计算机，该服务会自动停止并注销。
- 检查
-
14、使用sqlplus连接数据库
使用system用户和配置的密码连接即可。
安装完成！！
oracle11g完全卸载
- 1.在桌面桌面点击我的电脑，右键管理，打开系统服务
- 2.找到所有以oracle开头的服务全部停止运行。
- 3.打开开始菜单，打开oracle11g目录，点击Universal Installer开始卸载
- 4.点击卸载产品，勾选全部产品信息，开始卸载。
- 5.卸载完成后产品清单已经没有信息了。
- 6.运行regedit，打开注册表信息栏目
- 7.展开HKEY_LOCAL_MACHINE\SOFTWARE，找到关于oracle的信息全部删除
- 8.在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services信息项中，找到以oracle开头的信息项也全部删除。
- 9.在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application信息项中，找到以oracle开头的信息项也全部删除。
- 10.在HKEY_CLASSES_ROOT信息项中，删除所有以ora开头的信息项
- 11.重启电脑删除安装目录，即可完整卸载oracle11g
Oracle安装常见异常
ora-00119和ora-00132
安装到98%时出现注册OCX时出现OLE初始化错误或OCX加载错误。
- 解决办法：点击中止安装，错误原因是安装路径中有中文字符，重新安装，修改一下路径就行了。
安装过程中提示 OracleMTSRecoveryService 时出现操作系统错误
- 出现这种情况一般是之前曾安装过注册表没有删除干净导致。
- 运行注册表（cmd-输入regedit）,到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services下，找到OracleMTSRecoveryService及OracleRemExecService，查看ImagePath对应路径是否正确，如OracleRemExecService的ImagePath：“C:\Users\yong\AppData\Local\Temp\oraremservice\RemoteExecService.exe” 我之前报错就是因为Temp后多了一个“”
- 核对正确后，点击重试，继续安装
- 如果还不成功，彻底卸载oracle重新安装
安装过程中出现ora-00922（缺少或无效选项）错误
- 安装Oracle 11g R2的过程中，在新建数据库实例时出现了该错误，如果选择"忽略"就会出现ora-28000错误，继续忽略，等到安装完成，直接按ORA-2800错误解决步骤解决即可。
- 经网络查询验证，这是属于在前面配置管理员密码的时候，采用了数字开头的密码，Oracle貌似对此不支持，但当时不提示出错，晕倒！据说包含其他非法特殊字符也可能产生此问题。
ORA-00922: 选项缺失或无效
- 错误原因：一般是语句的语法有问题。比如命名不对，关键字写错等等。对于非标准的命名，一般采用双引号来创建。
- 解决办法：
- 标识符命名规则:
1、必须以字母开始。
2、长度不能超过30个单字节字符。
3、只能包括A-Z，a-z，0-9，_，$和#。
4、不能在相同用户下建立两个同名的对象。
5、不能使用保留字和关键字
- 标识符命名规则:
ORA-28000: 账户锁定
- 解决办法
第一步：使用PL/SQL，登录名为system,数据库名称不变，选择类型的时候把Normal修改为Sysdba;
第二步：选择myjob,查看users;
第三步：选择system,右击点击“编辑”；
第四步：修改密码，把“帐户被锁住”的勾去掉；
第五步：点击“应用”再点击“关闭”；
第六步：重新登录就可以通过验证了；
PLSQL数据库连接工具
安装
- 1、双击plsqldev1207x64.msi开始安装。
- 2、接受协议并选择版本
- 3、如果不想安装到默认路径，可点击“Change”按钮，自定义安装路径
- 4、选择安装方式
- 5、点击“Install”开始安装
- 6、安装完成
- 7、首次打开PLSQL会提示输入license，可直接输入第一步中的注册码
Product Code(产品编号)：4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz
serial Number(序列号)：601769
password(口令)：xs374ca
汉化
- 安装语言包，安装路径需要选择PLSQL的安装路径。安装完后重启即可。
常见异常
- ORA-01017: invalid username/password; logon denied
基本操作
- （1）登陆成功后即可进入对象浏览器窗口界面
- （2）在对象浏览器可以选择查看哪个用户，例如SCOTT（当前登陆的用户的所有object）
- （3）找到table文件夹，里边就是当前账户的所有表格
表格操作
- （1）查看
选中需要查看的表——》右键——》选中“查询数据”,即可看到数据了。 - （2）编辑
选中表——》右键——》Edit(编辑），可以对表进行编辑 - （3）新建
点击左上角新建-> 选择sql窗口 --> 编写代码 -->选择最上侧菜单栏选择“会话”->执行
Oracle database 介绍
- Oracle Database，又名Oracle RDBMS，或简称Oracle
- 是甲骨文公司的一款关系数据库管理系统
- 它是在数据库领域一直处于领先地位的产品。
- 可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。
特点
1、完整的数据管理功能：
- 1）数据的大量性
- 2）数据的保存的持久性
- 3）数据的共享性
- 4）数据的可靠性
2、完备关系的产品：
- 1）信息准则—关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示；
- 2）保证访问的准则
- 3）视图更新准则—只要形成视图的表中的数据变化了，相应的视图中的数据同时变化
- 4）数据物理性和逻辑性独立准则
3、分布式处理功能：
- ORACLE数据库自第5版起就提供了分布式处理能力，到第7版就有比较完善的分布式数据库功能了，一个ORACLE分布式数据库由oraclerdbms、sqlNet、SQLCONNECT和其他非ORACLE的关系型产品构成。
4、用ORACLE能轻松的实现数据仓库的操作。
Oracle基本概念
1．启动的服务
- oracle核心服务——OracleServiceORCL
- oracle监听服务——OracleOraDb11g_home1TNSListener
- oracle数据库工作日程调度（没有工作日程安排不用启动，占资源）——OracleJobSchedulerORCL
2．Sql Server与MySQL与Oracle最大的不同
应用场景:
Oracle
- Oracle的应用，主要在传统行业的数据化业务中，比如：银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务；零售、物流这样对海量数据存储分析要求很高的业务。
- 高新制造业如芯片厂也基本都离不开Oracle；电商也有很多使用者，如京东（正在投奔Oracle）、阿里巴巴（计划去Oracle化）
- 特点：
- 由于Oracle对复杂计算、统计分析的强大支持，在互联网数据分析、数据挖掘方面的应用也越来越多
- 保证扩展性和无损迁移
- 支持高并发
- 对海量数据实时处理、操作，建立高运算量
MySQL
- MySQL基本是生于互联网，长于互联网。其应用实例也大都集中于互联网方向，
- 例如SNS、LBS等互联网业务
- 特点：
- 高并发存取能力并不比大型数据库差
- 价格便宜，安装使用简便快捷
MS SQL Server
- windows生态系统的产品，好处坏处都很分明
- 例如，自建ERP系统、商业智能、垂直领域零售商、餐饮、事业单位等等
- 特点：
- 好处就是，高度集成化，微软也提供了整套的软件方案，基本上一套win系统装下来就齐活了。
架构:
Oracle
- 数据文件包括：控制文件、数据文件、重做日志文件、参数文件、归档文件、密码文件。
- 特点：
- 根据文件功能行进行划分，并且所有文件都是二进制编码后的文件，对数据库算法效率有极大的提高。
- 由于Oracle文件管理的统一性，就可以对SQL执行过程中的解析和优化，指定统一的标准：
- RBO（基于规则的优化器）、CBO（基于成本的优化器）
- 通过优化器的选择，以及无敌的HINT规则，给与了SQL优化极大的自由，对CPU、内存、IO资源进行方方面面的优化。
Mysql
- 特点：
- 自由选择存储引擎
- 每个表都是一个文件，都可以选择合适的存储引擎。
- 在多表关联、子查询优化、统计函数等方面是软肋，而且只支持极简单的HINT。
SQL Server
- 数据架构基本是纵向划分，分为：Protocol Layer（协议层）， Relational Engine（关系引擎）， Storage Engine（存储引擎）， SQLOS。
- 工作过程跟Oracle是非常相似的。在成本之上也是支持很丰富的HINT，包括：连接提示、查询提示、表提示。
注意：Oracle只有一个数据库，一台电脑只会安装一个数据库，Oracle只有账户。
3．Oracle体系
Oracle进阶
表空间和用户权限管理
表空间
什么是表空间？
- 数据库的逻辑存储空间，可以理解为在数据库中开辟的空间用来存储数据库对象;每个数据库至少有一个表空间(称作SYSTEM表空间)
表空间和数据文件的关系：
- 表空间由一个或多个数据文件组成;数据文件的大小和位置可以自己定义;
表空间的分类
- 永久表空间：数据库中要永久化存储的一些对象，如：表、视图、存储过程
- 临时表空间：数据库操作当中中间执行的过程，执行结束后，存放的内容会被自动释放
- UNDO表空间：用于保存事务所修改数据的旧值，可以进行数据的回滚
oracle 用户与表空间关系:
- 用户=商家 表=商品 表空间=仓库
- oracle中用户的所有数据都是存放在表空间中的，很多个用户可以共用一个表空间，也可以指定一个
- 用户只用某 一个表空间。
- 表空间：创建表空间会在物理磁盘上建立一个数据文件，作为数据库对象（用户、表、存储过程等等）
- 物理存储空间；
- 用户：创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间；
- 创建用户后，可以在用户上，创建表、存储过程等等其他数据库对象；
- 表：是数据记录的集合；
- 创建过程： 表空间—>用户—>表;
- 所属关系： 表空间 包含 用户包含 表；
使用SQL语言创建表空间
- 创建表空间
- create [temporary] tablespace 表空间名称 tempfile|datafile ‘表空间数据文件路径.dbf’ size 表空间初始大小; [ AUTOEXTEND [ OFF | ON ] ] ; AUTOEXTEND表明是否自动扩展. OFF | ON 表示自动扩展是否被关闭.
- 备注：如果创建的是临时表空间，需要加上temporary关键字;
- 修改表空间
- 修改表空间的状态
-(通过这个dba_tablespaces数据字典) - select status from dba_tablespaces where tablespace_name=”;(条件是表空间的名字,需要大写)
- 设置联机或脱机的状态(表空间是脱机时不可用，默认是联机的)
- alter tablespace tablespace_name online|offline;
- 设置只读或可读写的状态(只有在联机状态才可以更改,默认的联机状态就是读写状态)
- alter tablespace tablespace_name read only | read write;
- 修改表空间的大小
- A.更改数据文件的大小
- ALTER DATABASE DATAFILE ‘D:\oracle\product\10.2.0\userdata\db_test.dbf’ RESIZE 800M;
- B.向表空间内添加数据文件
- ALTER TABLESPACE db_test ADD DATAFILE ‘D:\oracle\product\10.2.0\userdata\db_test.dbf\db_test02.DBF’ SIZE 200M AUTOEXTEND ON;
- A.更改数据文件的大小
- 修改数据文件
- A.向表增加数据文件
- alter tablespace tablespace_name add datafile ‘xx.dbf’ size xx;
- select file_name from dba_data_files where tablespace_name=”;(条件是表空间的名字,需要大写)
- 备注：通过这条select语句就查询到当前表空间中的数据文件
- 删除数据文件(不能删除表空间当中第一个数据文件，如果要删除就需要删除整个表空间)
- alter tablespace tablespace_name drop datafile ‘xx.dbf’;
- A.向表增加数据文件
- 修改表空间的状态
- 删除表空间
- drop tablespace tablespace_name[including contents];
- 备注：如果只是删除表空间不删除该表空间下的数据文件，则不加including contents;
- 查看表空间
- （1）数据字典
- dba_tablespaces(系统管理员级别查看的数据字典)
- user_tablespaces(普通用户查看的数据字典)
- （2）查看有几个表空间
- select tablespace_name from dba_tablespaces;
- （3）查看用户的默认表空间、临时表空间等等
- select default_tablespace from dba_users where username=’SYS’;
- （1）数据字典
用户
创建用户
- Create user 用户名 identified by 密码 Default tablespace 表空间 --指定该用户管理的表空间
修改用户
- 修改用户的表空间限额
- Alter user 用户名（大写） quota 限额大小 （20M） to 表空间名（大写）
删除用户
- DROP USER 用户名
- Drop user 用户名 cascade;
- –CASCAED表示删除用户的同时删除其建立的实体
查看用户
- 查询用户
- SELECT * FROM dba_users WHERE username=‘用户名’;
- 查看表空间限额
- SELECT * FROM dba_ts_quotas WHERE username=‘用户名’;
用户授权
角色分类：
- Connect：临时用户角色
- Resource:正式用户角色
- DBA:管理员（一般不用）
授予角色：
- Grant connect , resource to 账户
撤销角色
- Revoke connect，resource from 账户
授予权限：
- 1）授权系统权限
- grant create session to 账户
- 2）授权表权限
- Grant select on scott.emp to 账户
- – 将scott用户下的emp所有权限赋值给账户
- Grant all on scott.emp to 账户
撤销权限：
- revoke select on scott.emp from 账户
系统权限
- 系统权限 权限的操作
- CREATE USER 可以创建其他 Oracle 用户 (需要有 DBA 角色权限)
- DROP USER 可以删除另一个用户
- DROP ANY TABLE 可以删除在任意方案中的表
- BACKUP ANY TABLE 可以备份在任何方案中的任何表
- SELECT ANY TABLE 可以查询在任何方案中的表、视图或快照
- CREATE ANY TABLE 可以在任何方案中创建表
- CREATE SESSION 连接到数据库
- CREATE TABLE 在用户的方案中创建表
- CREATE SEQUENCE 在用户的方案中创建序列
- CREATE VIEW 在用户的方案中创建视图
- CREATE PROCEDURE 在用户的方案中创建存储过程、函数或包
表权限
序列
什么是序列?
- 是oacle提供的用于产生一系列唯一数字的数据库对象。
序列的作用
- 1）自动提供唯一的数值
- 2）共享对象
- 3）主要用于提供主键值
- 4）将序列值装入内存可以提高访问效率
介绍序列相关语法和使用
语法
- 创建
- CREATE SEQUENCE 序列名
- START WITH 10 – 指定第一个序号从 10 开始
- INCREMENT BY 10 --指定序号之间的间隔为 10
- MAXVALUE 2000 – 表示序列的最大值为 2000
- MINVALUE 10 – 表示序列的最小值为 10
- NOCYCLE – 在达到最大值后停止生成下一个值 cycle
- CACHE 10; – 指定内存中预先分配的序号数
- 修改
- //修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
- alter SEQUENCE sequence //创建序列名称
- [INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
- [START WITH n] //开始的值,递增默认是minvalue 递减是maxvalue
这里写代码片 - [{MAXVALUE n | NOMAXVALUE}] //最大值
- [{MINVALUE n | NOMINVALUE}] //最小值
- [{CYCLE | NOCYCLE}] //循环/不循环
- [{CACHE n | NOCACHE}];//分配并存入到内存中
- 例如： Alter sequence seqEmp maxvalue 5; Select seqEmp.nextval from dual;
- 修改序列的注意事项：
- 必须是序列的拥有者或对序列有 ALTER 权限
- 只有将来的序列值会被改变
- 改变序列的初始值只能通过删除序列之后重建序列的方法实现
- 删除
- 1.使用DROP SEQUENCE 语句删除序列
- 2.删除之后，序列不能再次被引用
什么时候会出现裂缝（不连续的情况）
- 序列在下列情况下出现裂缝: 123 56
- 1、 回滚
- 2、 系统异常
- 3、 多个表同时使用同一序列
序列和使用CRUD生成主键的区别
序列
- 需要底层数据库的支持序列，支持序列的数据库有DB2、PostgreSql、Qracle、SAPDb等在不同数据库之间移植程序，特别从支持序列的数据库移植到不支持序列的数据库需要修改配置文件。
- 需要主键值时可以调用seq_name.nextval或者seq_name.curval得到，数据库会帮助我们维护这个sequence序列，保证每次取到的值唯一
GUID
- UUID使用128位UUID算法生成主键，能够保证网络环境下的主键唯一性，也就能够保证在不同数据库及不同服务器下主键的唯一性
- 特点;能够保证数据库中的主键唯一性，生成的主键占用比较多的存贮空间。
同义词
同义词的用途
- 同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性
同义词的分类
公用Oracle同义词:
- 由一个特殊的用户组Public所拥有
- 数据库中所有的用户都可以使用公用同义词
- 公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用
私有Oracle同义词
- 它是跟公用同义词所对应，他是由创建他的用户所有
- 这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。
创建同义词
- Create [public] synonym 同义词名称 for [username.]objectName；
同义词查询
- 同义词作为一种数据库对象，它的相关信息被存储在数据字典中。
- 与同义词有关的数据字典有三个：
- user_sysnonyms、
- all_synonyms、
- dba_synonyms。
删除同义词
- Drop [public] synonym 同义词名称
作用
- 多用户协同开发中，可以屏蔽对象的名字及其持有者。
- 为用户简化sql语句
- 为分布式数据库的远程对象提供位置透明性
索引
索引的定义
- 在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快
- 作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
- 索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是- 一个独立于表的对象，可以存放在与表不同的表空间中。
- 索引记录中存有索引关键字和指向表中数据的指针（地址）
特点：
- 1、 类似书的目录结构
- 2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度
- 3、 索引直接指向包含所查询值的行的位置，减少磁盘I/O
- 4、 与所索引的表是相互独立的物理结构
- 5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引
- 6、 语法：CREATE INDEX index ON table (column[, column]…);
- 7、 B-tree结构(非bitmap)：
索引的分类
（1）索引逻辑分类
- 单列索引：基于一列的操作
- 多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。
- 惟一索引：列的值各不相同。
- 非惟一索引：列的值允许相同。
- 基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。
（2）索引物理分类
- 分区索引
- 非分区索引：非分区既可以是B－树，也可以是位图索引。
- B-树索引：包括正常索引或反转关键字索引
- 位图索引
索引的不足
- 创建和维护索引，比较耗费时间，随着数据量的增大而增大
- 创建索引，占一定的物理空间（聚簇索引，占用空间会更大）
- 在对表进行增删改的时候，索引相应的也需要进行动态的更新
比较适合建立索引的列的特点
- 经常需要搜索的列上
- 主键，一般建立唯一性索引，保持数据的唯一性
- 外键，提高表与表之间连接的速度
- 需要排序的列上
- where子句后边经常出现的字段
- 经常需要根据范围进行搜索的列上，比如日期
不适合建立索引的列的特点
- 很少进行搜索的列上
- 列取值比较少的列上
- blob类型的列上
- 修改频率比较高的列上
使用索引的优势
- 大大加快检索数据的速度
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
- 加速表与表之间的连接
- 查询语句汇总含有分组或者排序的语句时，速度更快
- 查询的过程中，使用索引，使用优化隐藏器，从而提高系统的性能
语法
创建索引
- unique|bitmap : unique表示唯一值索引，bitmap表示位图索引，为空则默认为B-tree索引
- column_name|expression asc|desc , … :可以单列索引，也可以多列进行联合索引，当为的时候，为“基于函数的索引”
- storage : 可以设置表空间的存储参数
- logging|nologging : 是否对索引产生redolog（对于大表来说，可以设置为nologging从而来减少空间占用，提高效率）
- nocompress|compressnn : 是否使用“键压缩”（使用键压缩可以删除一个键列中出现的重复值）
- nosort|reverse : nosort表示与表中相同的顺序进行创建索引，reverse表示使用与表中相反的顺序进行创建索引
修改和删除索引
- 修改索引
- partition|nopartition|global partition : 可以在分区表上和未分区表上对创建的索引进行分区
- –将反向键索引更改为正常B树索引
- ALTER INDEX index_reverse_empno REBUILD NOREVERSE;
- –将索引移到指定表空间
- ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;
- 删除索引
- DROP INDEX index_bit_job;
类型
唯一索引
- （1） 何时创建：当某列任意两行的值都不相同
- （2）当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立
- （3）语法：CREATE UNIQUE INDEX index ON table (column);
- （4）演示：create unique index index_unique_grade on salgrade(grade);
反向键索引
- 目的：比如索引值是一个自动增长的列：
- 多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索 引。
- 性能问题：
- 语法：
- create index 索引名 on 表名(字段名) reverse;
- 例子：
- create index index_reverse_empno on SCOTT.employee(empno) reverse;
- 重建为标准索引：反之不行
- 语法：
位图索引
- 1、 何时创建：
- 列中有非常多的重复的值时候。例如某列保存了 “性别”信息。
- Where 条件中包含了很多OR操作符。
- 较少的update操作，因为要相应的跟新所有的bitmap
- 2、 结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。
- 3、 优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多
- 4、 语法：CREATE BITMAP INDEX index ON table (column[, column]…);
- 5、 列子：
- create bitmap index index_job on SCOTT.employee(job) ;
函数索引
- 1、 何时创建：在WHERE条件语句中包含函数或者表达式时
- 2、 函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
- 3、 语法：CREATE INDEX index ON table (FUNCTION(column));
- 4、 演示
- create index index_enameon on employee (UPPER(ename));
- 必须要分析表，并且 query_rewrite_enabled=TRUE
- 或者使用提示/+ INDEX(ic_index)/
分区表
什么分区表
- Oracle允许把一个表中的所有行分成几个部分，并将它们存储在不同的表空间，分成的每一部分成为一个分区，被分区的表成为分区表。
- 对于包含大量数据的表来说，分区很有用，优点有以下几点：
- 1）改善表的查询性能。在对表进行分区后，用户执行sql查询时可以只访问表中的特定分区而非整个表。
- 2）表更容易管理。因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。
- 3）便于备份和恢复。可以独立地备份和恢复每个分区。
- 4）提高数据安全性。将不同的分区分布在不同的磁盘，可以减少所有分区的数据同时损坏的可能性。
什么情况下使用分区表
- 1）数据量大于2GB。
- 2）已有的数据和新添加的数据有明显的界限划分。
分区表的分类的特点
范围分区
- 是一种常用的表分区方法，它是oracle引进的第一个分区类型
- 范围分区用于可以根据某些条件按范围分开的数据
- 如果数据均匀的分布在所建立的不同的范围内，那么使用范围分区将得到最好的分区效果。范围可以基于顺序数或部分数，范围分区技术通常基于时间（例如月或季度）
- 步骤
- 1）创建分区表
- 2）向表中插入数据
- 3）查询P1区的数据
- 4）如果向表中插入以下记录，会提示插入的分区关键字未映射到任何分区
- 5）按范围分区时，如果某些记录暂时无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中
- 6）再次插入以下数据
- 7）查询
间隔分区
- 间隔分区是指已某个指定的条件未间隔划分分区
- 创建间隔分区表
CREATE TABLE STU3 ( STUID NUMBER, STUNAME VARCHAR2(5), STU_DATE DATE NOT NULL ) PARTITION BY RANGE(STU_DATE) INTERVAL(NUMTOYMINTERVAL(1,'YEAR')) (PARTITION P1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')));
- 插入数据
INSERT INTO STU3 VALUES (1,'a',to_date('2014-01-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (2,'a',to_date('2014-02-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (3,'a',to_date('2014-03-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (4,'a',to_date('2014-06-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (5,'a',to_date('2014-07-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (6,'a',to_date('2014-08-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (7,'a',to_date('2014-09-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (8,'a',to_date('2014-10-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (9,'a',to_date('2014-11-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (10,'a',to_date('2014-12-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (11,'a',to_date('2014-04-1','yyyy-mm-dd'));
INSERT INTO STU3 VALUES (12,'a',to_date('2014-05-1','yyyy-mm-dd'));
- 获得分区情况
SELECT table_name,partition_name FROM user_tab_partitions WHERE table_name=UPPER('stu3');
- 查询分区
查看所有分区的命令
SELECT table_name,partition_name FROM user_tab_partitions WHERE table_name=UPPER('stu3');
PL/SQL编程
1、介绍PL/SQL块
（1）什么是PL/SQL？
- PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言
（2）SQL与PL/SQL？
- 1） SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。
- 2）PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理
- 3）PL/SQL是对SQL语言存储过程语言的扩展
（3）PL/SQL的工作原理
- PL/SQL引擎接受 PL/SQL 块并对其进行编译执行
- 该引擎执行所有过程语句
- 将SQL语句发送给Oracle的SQL语句执行器
（4）PL/SQL的优点
过程化
- PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。
模块化
- PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函 数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。
运行错误的可处理性
- 使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL 错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。
提供大量内置程序包
- ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。
（5）PL/SQL 可用的SQL语句
- PL/SQL中可以使用的SQL语句有：
- INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT
- 提示：在 PL/SQL中只能用 SQL语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的话，只能以动态的方式来使用。
2、了解PL/SQL结构
- 三个块组成，即声明部分、执行部分、异常处理部分
- PL/SQL块的结构如下
- DECLARE --声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 BEGIN – 执行部分: 过程及SQL 语句 , 即程序的主要部分 EXCEPTION – 执行异常部分: 错误处理 END;
- PL/SQL块可以分为以下几类
-
- 无名块或匿名块（anonymous）：动态构造，只能执行一次，可调用其它程序，但不能被其它程序调用。
-
- 命名块（named）：是带有名称的匿名块，这个名称就是标签。
-
- 子程序（subprogram）：存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序中调用它们。
-
- 触发器（Trigger）：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
- 5.程序包/包（package）：存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子程序调用。但如果声明的是局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。
-
3、基础知识
变量
（1）声明变量
- DECLARE variable_name [CONSTANT] type [NOT NULL] [:=value]
- DECLARE – 声明
- variable_name – 变量名
- CONSTANT – 是否是常量
- type – 变量类型
- NOT NULL – 是否非空
- [:=value] – 变量初始值
（2）变量命名规范
- 变量名首字母必须是英文字母
- 其后可以是字母、数字或者特殊字符$、#和下划线
- 变量名长度不超过30个字符
- 变量名中不能有空格
- 不能是SQL保留字
（3） 变量类型
- 在ORACLE中可以使用的变量类型有：
- 复合类型
- CLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型–记录和表.
- 记录类型
- 定义记录类型语法如下:
TYPE record_name IS RECORD( v1 data_type1 [NOT NULL] [:= default_value ], v2 data_type2 [NOT NULL] [:= default_value ], ...... vn data_typen [NOT NULL] [:= default_value ] );- 例4 ：
DECLARE TYPE test_rec IS RECORD( Name VARCHAR2(30) NOT NULL := '胡勇', Info VARCHAR2(100)); rec_book test_rec; BEGIN rec_book.Name :='胡勇'; rec_book.Info :='谈PL/SQL编程;'; DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info); END;
- 数组类型
- 数据是具有相同数据类型的一组成员的集合。
- 在PL/SQL中，数组数据类型是VARRAY。
- 语法
- TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];
- varray_name是VARRAY数据类型的名称，
- size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是
- element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。
- 对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。
- 例6 ：
DECLARE --定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型 TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25); --声明一个该VARRAY数据类型的变量 v_reg_varray REG_VARRAY_TYPE; BEGIN --用构造函数语法赋予初值 v_reg_varray := reg_varray_type ('中国', '美国', '英国', '日本', '法国');DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、' ||v_reg_varray(2)||'、' ||v_reg_varray(3)||'、' ||v_reg_varray(4)); DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5)); --用构造函数语法赋予初值后就可以这样对成员赋值 v_reg_varray(5) := '法国'; DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5)); END;
- 使用%TYPE
- 定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。
- 优点
- 1.所引用的数据库列的数据类型可以不必知道；
- 2.所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
DECLARE -- 用%TYPE 类型定义与表相配的字段 TYPE T_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); -- 声明接收数据的变量 v_emp T_Record; BEGIN SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||' ' || TO_CHAR(v_emp.t_sal)); END;
- 使用%ROWTYPE
- PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致
- 优点
- 1、所引用的数据库中列的个数和数据类型可以不必知道；
- 2、所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
- 例9：
DECLARE v_empno emp.empno%TYPE :=& no; rec emp%ROWTYPE; BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate); END;
- BIND 变量
- 绑定变量是在主机环境中定义的变量。
- 在PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL 环境中声明绑定变量，使用命令VARIABLE。
- 例10：
VARIABLE result NUMBER; BEGIN SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp WHERE empno=7844; END; --然后再执行 PRINT result
例子：变量赋值
```sql
DECLARE
v_emp_name VARCHAR2(10);
BEGIN
-- 赋值
SELECT ename into v_emp_name FROM scott.emp where empno = 7369;
--输出
DBMS_OUTPUT.put_line(v_emp_name);
END;
```
-
例1. 插入一条记录并显示；
DECLARE Row_id ROWID; info VARCHAR2(40); BEGIN INSERT INTO scott.dept VALUES (90, '财务室', '海口') RETURNING rowid, dname||':'||to_char(deptno)||':'||loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); DBMS_OUTPUT.PUT_LINE(info); END;- RETURNING
- RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES 子句插入数据时，RETURNING 字句还可将列表达式、ROWID和REF值返回到输出变量中。
- 限制：
- 1．不能与DML语句和远程对象一起使用；
- 2．不能检索LONG 类型信息；
- 3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。
- RETURNING
运算符
- 关系运算符
| 符号 | 释义 | 示例 |
|---|---|---|
| = | 等于 | n = 1 |
| <>或!=或~= | 不等于 | n != 1 |
| < | 小于 | n < 1 |
| > | 大于 | n > 1 |
| <= | 小于等于 | n <= 1 |
| >= | 大于等于 | n >= 1 |
- 算术运算符
| 符号 | 释义 | 示例 |
|---|---|---|
| + | 加 | n := 1 + 1; |
| - | 减 | n := 2 - 1; |
| * | 乘 | n := 2 * 3; |
| / | 除 | n := 6 / 2; |
| mod(,) | 取模，取余 | n : = mod(3,2) |
| ** | 乘方 | 10**2 =100 |
- 逻辑运算符
| 符号 | 释义 | 示例 |
|---|---|---|
| AND | 逻辑与 | m=3 and n is null |
| OR | 逻辑或 | m=3 or n is null |
| NOT | 逻辑非 | not n is null |
- 比较运算符
| 符号 | 释义 | 示例 |
|---|---|---|
| LIKE | 是否满足匹配 | ‘YIERSHANREN’ like ‘%Y’返回true |
| BETWEEN | 是否处于一个范围中(包括左右边界) | 2 between 1 and 3 返回true |
| IN | 是否处于一个集合中 | ‘x’ in (‘x’,’y’) 返回true |
| IS NULL | 判断变量是否为空 | n:=3,n is null,返回false |
变量赋值
语法
- variable := expression ;
- variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.
字符及数字运算特点
- 空值加数字仍是空值：NULL + < 数字> = NULL
- 空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>
BOOLEAN 赋值
- 布尔值只有TRUE, FALSE及 NULL 三个值
数据库赋值
- 数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应
可转换的类型赋值
- 1.CHAR 转换为 NUMBER：
- 使用 TO_NUMBER 函数来完成字符到数字的转换，如：
- v_total := TO_NUMBER(‘100.0’) + sal;
- 2.NUMBER 转换为CHAR：
- 使用 TO_CHAR函数可以实现数字到字符的转换，如：
- v_comm := TO_CHAR(‘123.45’) || ‘元’ ;
- 3.字符转换为日期：
- 使用 TO_DATE函数可以实现 字符到日期的转换，如：
- v_date := TO_DATE(‘2001.07.03’,‘yyyy.mm.dd’);
- 4.日期转换为字符
- 使用 TO_CHAR函数可以实现日期到字符的转换，如：
- v_to_day := TO_CHAR(SYSDATE, ‘yyyy.mm.dd hh24:mi:ss’) ;
变量作用范围及可见性
- 1.变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。
- 2.一个变量（标识）只能在你所引用的块内是可见的。
- 3.当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。
- 4.在子块中重新定义该变量后，它的作用仅在该块内。
控制语句
控制语句:
- IF表达式
- 语法
- IF <布尔表达式> THEN PL/SQL 和 SQL语句 END IF;
- IF <布尔表达式> THEN PL/SQL 和 SQL语句 ELSE 其它语句 END IF;
- ELSIF 不能写成 ELSEIF
- 语法
- CASE 表达式
- 语法
- CASE 条件表达式 WHEN 条件表达式结果1 THEN 语句段1 WHEN 条件表达式结果2 THEN 语句段2 … WHEN 条件表达式结果n THEN 语句段n [ELSE 条件表达式结果] END
- 语法
循环语句:
- LOOP
- 语法
- LOOP 要执行的语句; EXIT WHEN <条件语句> --条件满足，退出循环语句 END LOOP;
- 语法
- WHILE 循环
- 语法
- WHILE <布尔表达式> LOOP 要执行的语句; END LOOP;
- 语法
- 数字式循环
- 语法
- FOR 循环计数器 IN [ REVERSE ] 下限 … 上限 LOOP 要执行的语句; END LOOP [循环标签];
- 每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。
- 语法
顺序语句:
- 标号和GOTO
- PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思
- 语法
- GOTO label;
<<label>> /*标号是用<< >>括起来的标识符 */ - 注意，在以下地方使用是不合法的，编译时会出错误。
- u 跳转到非执行语句前面。
- u 跳转到子块中。
- u 跳转到循环语句中。
- u 跳转到条件语句中。
- u 从异常处理部分跳转到执行。
- u 从条件语句的一部分跳转到另一部分。
- NULL 语句
- 在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。
4、异常
概述
- 在编写查询语句，函数，存储过程等一系列语句中，有些时候也会或多或少的出现各种错误，这些错误就可以当做异常。这些错误可能来自程序本身，也可能来自开发人员自定义的数据
异常的分类
预定义异常
- 语法
- 异常类型
- ORA-0001 Dup_val_on_index -----------违反了唯一性限制
- ORA-0051 Timeout-on-resource -----------在等待资源时发生超时
- ORA-0061 Transaction-backed-out-----------由于发生死锁事务被撤消
- ORA-1001 Invalid-CURSOR----------- 试图使用一个无效的游标
- ORA-1012 Not-logged-on----------- 没有连接到ORACLE
- ORA-1017 Login-denied -----------无效的用户名/口令
- ORA-1403 No_data_found SELECT INTO-----------没有找到数据
- ORA-1422 Too_many_rows SELECT INTO -----------返回多行
- ORA-1476 Zero-divide-----------试图被零除
- ORA-1722 Invalid-NUMBER-----------转换一个数字失败
- ORA-6500 Storage-error -----------内存不够引发的内部错误
- ORA-6501 Program-error----------- 内部错误
- ORA-6502 Value-error----------- 转换或截断错误
- ORA-6504 Rowtype-mismatch-----------宿主游标变量与 PL/SQL变量有不兼容行类型
- ORA-6511 CURSOR-already-OPEN----------- 试图打开一个已处于打开状态的游标
- ORA-6530 Access-INTO-null -----------试图为null对象的属性赋值
- ORA-6531 Collection-is-null----------- 试图将Exists 以外的集合( collection)方应用于一个null pl/sql 表上或varray上
- ORA-6532 Subscript-outside-limit -----------对嵌套或varray索引得引用超出声明围以外
- ORA-6533 Subscript-beyond-count -----------对嵌套或varray 索引得引用大于集合中元素的个数
非预定义异常
- 应用场景与解析
- 在PL/SQL 块的声明部分定义异常情况： --<异常情况> EXCEPTION; --将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句 --PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>)； --在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理
- 编写PL/SQL程序时，应该充分考虑到各种可能出现的异常，并且都作出适当的处理，这样的程序才是健壮的。对于这类非预定义的异常，由于它也被自动抛出的，因而只需要定义一个异常，把这个异常的名称与错误的代码关联起来，然后就可以像处理预定义异常那样处理这样的异常了。
- 在抛出ORACLE预定义异常之前先抛出自己定义的非预定义异常，可以大大节约异常定位的时间，比如说很多情况都最终导致同一种预定义异常情况下。
- 异常类型
https://blog.csdn.net/keith_walker/article/details/78790031
自定义异常
- 语法
--用户自定义异常 DECLARE v_empno emp.empno%TYPE :=& empno; --声明变量 V_empno,类型 emp.empno%TYPE 类型与emp表empno列类型一致 no_result EXCEPTION; --声明一个异常 名为 no_result BEGIN UPDATE emp SET sal=sal+100 WHERE empno=v_empno; IF SQL%NOTFOUND THEN RAISE no_result; --触发异常 -- raise触发 END IF; EXCEPTION WHEN no_result THEN --当异常被触发 DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!'); WHEN OTHERS THEN --可以理解为异常包,接收 定义异常之外的 异常 DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); END;
5、游标
概念
- 位于内存中的 “临时表”。 具体如下：游标是从数据表中提取出来的数据，以 临时表 的形式存放到 内存中，在游标中有一个 数据指针， 在初始状态下指向的是首记录，利用 fetch 语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回到数据库中。
作用
- 1、用来查询数据库，获取记录集合（结果集）的指针，可以让开发者 一次访问一行结果集， 在每条结果集上作操作。
- 2、用 ‘牺牲内存’ 来提升 SQL 执行效率，适用于 大数据处理。
游标分类
静态游标
- 隐式游标
- 用于处理DML语句和返回单行数据的SELECT查询。
- 显式游标
- 用于对查询语句的处理，尤其是在查询结果为多条记录的情况下。
- 显示游标和隐式游标共性属性
- SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
- SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
- SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
- SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
动态游标
- 强类型游标
- 在声明变量时使用return关键字定义游标的返回类型
- 弱类型游标
- 在声明变量时不使用return关键字定义游标的返回类型
语法
- 普通游标
- 系统引用型游标
游标常用属性
6、存储过程和函数
创建储存过程和函数
储存过程
- 语法
--定义 create[or replace] procedure 存储过程名称(参数名 [in]/out 数据类型) is/as begin --逻辑表达式 end [存储过程名称]; --定义存储过程计算年薪，并答应输出 create or replace procedure proc_salyears(v_no in number) is sal_years number(9,2); begin --计算年薪 select sal*12+nvl(comm,0) into sal_years from emp where empno=v_no; --输出 dbms_output.put_line(sal_years); end; - 调用储存过程
- 方式1: call proc_salyears(7788); 方式2: begin proc_salyears(7369); end;
- out参数的存储过程
--计算年薪并返回 create or replace procedure proc_salyears(v_no in number,sal_years out number) is begin --计算年薪 select sal*12+nvl(comm,0) into sal_years from emp where empno=v_no; end; --调用存储过程 declare v_sal number(9,2); begin proc_salyears(7876,v_sal); dbms_output.put_line(v_sal); end;
储存函数
- 语法
--定义 create or replace function 存储函数名(参数名 in/out 数据类型) return 数据类型 is|as begin return 具体的数据; end [存储函数名称]; --定义存储函数名计算年薪 create or replace function fun_salyears(f_no number) return number is sal_years number(9,2); begin select sal*12+nvl(comm,0) into sal_years from emp where empno=f_no; return sal_years; end ; - 使用储存函数
declare sal_yeats number(9,2); begin sal_yeats := fun_salyears(7876); dbms_output.put_line(sal_yeats); end; --可简写 begin dbms_output.put_line(fun_salyears(7369)); end; - 一般，只有在确认function_name函数是新函数或是要更新的函数时，才使用OR REPALCE关键字，否则容易删除有用的函数。
in与out
- N,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。
储存过程和储存函数的区别
- 1、函数只能返回一个变量，而存储过程可以返回多个；
- 2、函数的限制比较多，不能用临时表，只能用表变量，有些函数不能用，存储过程限制少；
- 3、存储过程处理的功能比较复杂，而函数实现的功能针对性强；
- 4、存储过程可以执行修改表的操作，但是函数不能执行一组修改全局数据库状态的操作；
- 5、存储过程可以返回参数，如记录集，函数只能返回值或者表对象。
- 6、存储过程一般是作为独立部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，所以在查询中位于from关键字后面，sql语句中不可以含有存储过程。
删除过程和函数
1.删除过程
- 可以使用DROP PROCEDURE命令对不需要的过程进行删除，语法如下： DROP PROCEDURE [user.]Procudure_name;
2.删除函数
- 可以使用DROP FUNCTION 命令对不需要的函数进行删除，语法如下： DROP FUNCTION [user.]Function_name;
过程与函数的比较
使用过程与函数优点
- 1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。
- 2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。
- 3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。
- 4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
- 5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。
- 6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。
过程与函数的相同功能
- 1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
- 2、 输入参数都可以接受默认值，都可以传值或传引导。
- 3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
- 4、 都有声明部分、执行部分和异常处理部分。
- 5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
使用过程与函数的原则
- 1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。
- 2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。
- 3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。
评论区