ORACLE数据库入门再在屋里坐会
一、数据库简介
数据库概述
数据库(database)是按照数据结构来组织,存储和管理数据的仓库,它产生与距今五十年前. 简单来说是本身可视为电子化的文件柜–存储电子文件的处所,用户可以对文件中的数据运行新增,截取,更新,删除等操作。
常见的数据模型
1. 层次结构模型:层次结构模型实质上是一种有根节点的定向有序的,IMS(Information ManagementSystem)是典型代表
2. 网状结构模型: 按照网状数据结构建立的数据库系统称为网状数据库系统,其典型的代表为DBTG(Data Base Task Group)
3. 关系结构模型: 关系数据结构把一些复杂的数据结构归纳为简单的二元关系(即二维关系表形式),常见的有Oracle,MySQL,mssql等
主流的数据库
1. mssql -> 微软 -> 只能运行在Windows平台,体积比较庞大,占用许多系统资源,但使用方便,支持命令和图形化管理(收费) -> 中型企业
2. MySQL -> 甲骨文 -> 是个开源的数据库server,可运行在多种平台,特点是响应速度特别快,主要面向中小性企业 -> 中小型企业
3. PostgreSQL -> 号称<世界上最先进的开源数据库>,可以运行在多种平台下,是tb级数据库,而且性能也很好 -> 中大型企业
4. Oracle -> 甲骨文 -> 获得最高认证级别的ISO标准安全认证,性能最高,保持开放平台下的TPC-D和TPC-C的世界记录,但价格不菲 -> 大型企业
5. DB2 -> IMB -> DB2在企业级的应用广泛,在全球的500家最大的企业中,相当一部分使用DB2数据库服务器(收费) -> 大型企业
6. Access -> 微软 -> Access是一种桌面数据库,只适合数据量少的应用,在处理少量数据和单机访问的数据库时是很好的,效率也很高 -> 小型企业
二、ORACLE数据库概述
ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一 -> 拉里 埃里森。
体系机构
从严格意义上来讲Oracle DataBase是有两个部分组成。
实例: 实例是数据库启动时初始化的一组进程和内存结构。
数据库: 数据库则指的是用户存储数据库的一些物理文件。
物理结构
物理结构包含三个数据文件:
1) 控制文件 -> v$controlfile
2) 数据文件 -> v$datafile
3) 在线重做日志文件 -> 日志组(v$log),日志组成员文件(v$logfile)
逻辑结构
功能: 数据库如何使用物理空间。
组成: 表空间, 段, 区, 块的组成层次。
表空间(tablespace): 表空间从逻辑上是多个段的结合,在物理上是多个数据文件的集合,相当于在段和数据文件的对应中加入了一个中间层来解决多对多的关系. 表空间 TableSpace,是存储结构中的最高层结构.建立一个表空间的时候, 需要指定存储的文件. 一个表空间可以指定多个数据文件, 多个文件可以在不同的物理存储上. 也就是说, 表空间是可以跨物理存储的. 但是有一点就是, 表空间下一级对象数据段的存储, 是不能指定存储在那个文件里的。
段(segment): 段就是包含所有数据的逻辑结构,数据段是与数据库对象相对应,一般一个数据库对象对应一个数据段,多个extent构成一个数据段,每个数据段实际上就是数据库一个对象的代表,比较典型的段就是”表”,称为表段,还有索引段,撤销段等等。
区(extent): 是比比数据块大一级的存储结构,表示的是一连串连续的数据块集合。
块(block): 数据块(block) 是 Oracle 数据信息的最小逻辑存储单元,Oracle所有对数据的操作和空间分配,实际上都是针对数据块 block 的操作。
三、用户和表空间
用户
1、系统用户
1)sys system(sys权限高于system)
2)sysman
3)scott/tiger
2、用户登录
语法一: 输入用户名:username/password
eg:system/123456
语法二: conn[ect] username/[password] [@server][as sysdba | sysoper]
eg1:登录帐号号,输入:
connect sys/123456 @orcl as sysdba
其中,orcl是安装时设置的服务器名
eg2:登录帐号号,输入:
connect system/123456
eg3:登录帐号号,输入:
connect system/
eg4:登录sys帐号
conn / as sysdba
语法三: 打开DOS命令窗口,输入: sqlplus username/password[as sysdba]
eg1:登录system帐号
sqlplus system/123456 as sysdba
eg2:登录sys帐号
sqlplus / as sysdba
show user -> sys
3、断开与当前数据库的连接 -> disc
4、显示用户名 -> show user
5、查询所有ORACLE提供的用户
1)select * from all_users
2)select * from dba_users
6、断开与当前数据库的连接并会退出 -> exit
7、编辑指定或缓冲区的SQL脚本
ed[it] 路径 + 文件名
tab : 返回当前账号下所有的列表
8、执行 -> start | @ 路径 + 文件名
9、将sqlplus屏幕中的内容输出到指定的文件
语法: 开始印刷 -> spool 路径名+文件名
结束印刷 -> spool off
10、设置/显示宽度(linesize): 设置显示行的宽度,默认为80个字符
语法: set linesize 列数宽度 / show linesize
11、设置显示页数(pagesize): 设置每页显示的行数,默认是14行
语法: set pagesize 行数 / show pagesize
12、设置列宽
语法: col[umn] 列名 for[mat] 格式
其中格式有两种情况:
1)字符串型数据,an,以a开始,后面表示n个字符列宽
2)数据型数据,9,表示一位列宽,跟多少个9,就表示占多少个列宽
13、启用 / 禁用用户
语法: alter user 用户名 account unlock(禁用) | lock(启用)
注:
1)数据表是绑定在用户中的,可以通过数据字典tab查看当前用户下的数据表(一个数据表(实例)中包含多个用户,每个用户下包含多张数据表)
select * from tab ; -> 相当于MySQL中的show tables ;
2)查看表 / 视图结构 -> desc 表名 / 视图
3)l[ist] -> 显示上一条SQL命令
/ -> 执行上一条SQL命令
r[un] -> 显示并执行上一条SQL命令
4)在11和12点中的设置只在当前会话中永久有效或永久失效,则配置一下文件:
D:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql
每次登录用户时都会加载此文件，可以此文件中进行配置，如下：
set linesize 200
set pagesize 20
5)注释
— 单行注释
/*多行注释*/
6)清屏 -> host doc命令/linux命令
window系统 -> host cls
linux系统 -> host clear
7)临时修改上一条SQL语句
ed[it] -> 标记时,后面不要加分号,它是一个单纯的SQL语句,然后执行上一条SQL命令: /
表空间
1、分类
1)永久表空间
2)临时表空间
3)UNDO表空间 -> 保存修改之前的数据,用于事务的回滚,撤销操作
2、查看用户的表空间
1)系统管理员 -> dba_tablespaces
2)普通用户 -> user_tablespaces
3)系统管理员 -> dba_users
4)普通用户 -> user_users
3、设置用户的默认表空间和临时表空间
alter user username
default|temporary
tablespace tablespace_name
4、创建表空间
create [temporary] tablespace
tablespace_name
tempfile|datafile \’xx.dbf\’ size xx
eg1:创建一个永久表空间
create tablespace test1_tablespace
datafile \’testfile1.dbf\’ size 10M ;
eg2:创建一个临时表空间
create temporary tablespace temp1_tablespace
tempfile \’tempfile1.dbf\’ size 20M ;
eg3:查看
desc dba_data_files ; //永久表空间
desc dba_temp_files ; //临时表空间
select file_name from dba_data_files where tablespace_name = \’TEST1_TABLESPACE\’ //注：表空间的名称要大写TEST1_TABLESPACE
5、修改表空间
1)修改表空间的状态
a)设置两计或脱机状态 -> alter tablespace tablespace_name online | offline;
设置脱机状态 -> alter tablespace test1_tablespace offline
设置联机状态 -> alter tablespace test1_tablespace online
b)设置只读或可读写状态->alter tablespace tablespace_name
read only | read write
设置只读状态 -> alter tablespace test1_tablespace read only
设置可读写状态 -> alter tablespace test1_tablespace read write
注: online状态默认为read write
2)修改数据文件
a)增加数据文件 -> alter tablespace tablespace_name
add datafile \’xx.dbf\’ size xx ;
给表空间添加一个数据文件 -> alter tablespace test1_tablespace
add datafile \’testfile2.dbf\’ size 10M ;
b)删除数据文件 -> alter tablespace tablespace_name
drop datafile \’xx.dbf\’;
6、删除表空间
drop tablespace tablespace_name [including connects]
注: including connects删除表空间的同时, 把数据也删除
四、用户管理和常用的命令
用户介绍
ORACLE用户是学习ORACLE数据库中的基础知识,Oracle数据库中默认为我们提供了30个用户,我们可以通过all_users进行查询。
系统常用的默认ORACLE用户：
1.sys用户: 超级用户,完全是个sysdba(管理数据库的人),拥有dba, sysdba, sysoper等角色或权限,是oracle权限最高的用户,登录时不能用normal
2.system用户: 超级用户, 默认是sysoper(操作数据库的人),不过它也能以sysdba的权限登录,拥有普通dba角色权限
3.scott用户: 是个演示用户,是用来学习Oracle用的
常用命令
1、登录命令 -> 用于登录到Oracle数据库
用法 -> sqlplus 用户名/密码 [as sysdba]
注: 当用特权用户登录时,必须带上sysdba
普通用户登录 -> sqlplus scott/tiger
sys用户登录 -> sqlplus sys/sys as sysdba
操作系统的身份登录 -> sqlplus conn/ as sysdba
2、连接命令(conn) -> 用于连接到Oracle数据库,也可实现用户的切换
用法 -> conn 用户名/密码 [as/sysdba/sysoper]
注: 当用特权用户登录时,必须带上sysdba或sysoper
3、断开连接(disc) -> 断开当前数据库的连接
用法 -> disc
4、显示用户名(show user) -> 显示当前用户名
用法 -> show user
5、退出(exit) -> 断开与当前数据库的连接并会退出
用法 -> exit
6、编辑脚本(edit/ed) -> 编辑指定或缓冲区的SQL脚本
用法 -> edit [文件名]
7、运行脚本(start/@) -> 运行指定的脚本
用法 -> start/@ 文件名
8、印刷屏幕(spool) -> 将sqlplus屏幕中的内容输出到指定的文件
用法 -> 开始印刷(spool 文件名) 结束印刷(spool off)
9、显示宽度(linesize) -> 设置显示行的宽度,默认是80个字符
用法 -> set linesize 120
10、显示页数(pagesize) -> 设置每页显示的行数,默认是14页
用法 -> set pagesize 20
用户管理
1、创建用户
Oracle中需要创建用户一定是要具有dba(数据库管理员)权限的用户才能创建,而且创建的新用户不具备任何权限,连登录都不行
语法：
create user 新用户名 identified by 密码
[default tablespace (表空间)]
[temporary tablespace (临时表空间)]
[profile (资源文件名)]
[quota(空间大小,单位M) on (表空间)]
例 -> create user zs identified by 123456 -> 创建普通用户zs ,密码123456, 没有指定用户所属的表空间,资源文件和临时文件同时因为没有指定quota(空间配额), zs用户将不能在相应的表空间建立数据对象,在Oracle11G中,普通用户默认表空间为Users, 临时表空间为temp, 资源文件默认使用default
附：解析
表空间: 表空间是数据库中最大的逻辑单位,一个Oracle数据库至少包含一个表空间, 就是名为system的系统表空间,一个用户只属于一个表空间,默认为Users
临时表空间: 临时表空间主要用于order by 语句的排序以及其他一些中间操作如:读取数据时 临时存储空间, Oracle从9i开始, 临时表空间通用temp
用户资源文件: 用户资源文件用来对用户的资源存取进行限制, 包括CPU资源的使用, 密码的使用策略等, 默认Default
管理表空间
查看表空间 -> dba sys v$ 开头的对象要以dba身份访问
select * from v$tablespace -> 查看系统当前拥有的表空间
select * from username,default_tablespace from user_users -> 查看当前用户所属的表空间
创建表空间
语法:
create tablespace 表空间名
datafile \’文件路径/文件名/dbf\’ -> 数据文件存放在打他datafile指定目录中
size 大小M -> 默认大小(M)
autoextend on -> 自动扩展
next 每次扩展M maxsize 最大扩展到M ;
删除表空间
删除表空间及空间文件
drop tablespace 表空间名 including contents and datafiles ;
管理资源文件
查看资源文件
select * from daba_profiles where profile=\’资源文件名\’
创建资源文件
语法一:
create profile \’资源文件名\’ limit ->创建资源文件
sessions_per_user 3 -> 支持该用户同时并发访问量为3次
failed_login_attempts 3 -> 密码输入如果错误3次
password_lock_time unlimited ; -> 永久锁定(dba可解锁)
语法二:
create user \’用户名\’ identified by 密码 -> 创建用户
default tablespace \’表空间名\’ -> 默认表空间
profile \’资源文件名\’ -> 默认用户资源文件
quota 大小(M) on \’表空间名\’ -> 给用户在表空间分配(M)配额
删除资源文件
drop profile 资源文件名
2、修改密码
修改用户密码一般有两种方式, 一种是通过password修改, 另一种是通过alter user实现, 如果要修改他人的密码, 必须要具有相关权限才可以。
用法:
方式一: password [用户名]
方式二: alter user 用户名 identified by 新密码
3、用户禁用/启用
Oracle中想要禁用或启用一个账户也同样是使用alter user 命令来完成, 只是语法和修改密码有所不同。
禁用 -> alter user 用户名 account lock
启用 -> alter user 用户名 account unlock
4、删除用户
Oracle中要删除一个用户,必须要具有dba的权限,而且不能删除当前用户,如果要删除的用户所有数据对象, 那么必须加上关键字cascade
用法:
drop user 用户名 [cascade]
用户权限与角色
1、权限
Oracle中权限分为系统权限和实体权限
系统权限: 系统规定用户使用数据库的权限(系统权限是对用户而言)
授予系统权限 -> 要实现授予系统权限只能由DBA用户授出
查看ORACLE所有系统权限
select * from system_privilege_map
用法 -> grant 系统权限1[,系统权限2] … to 用户名1[,用户名2] … [with admin option]
系统权限回收 -> 系统权限只能由DBA用户回收
用法 -> revoke 系统权限 from 用户名
实体权限(对象权限)
某种权限对用户其他用户的表或视图的存取权限(针对表和视图而言),主要包括select update insert alter index delete all其中all包括所有权限
授予实体权限
用法 -> grant 实体权限1[,实体权限2] … on 表名 to 用户名1[,用户名2] …[with grant option]
实体权限回收
用法 -> revoke 实体权限 on 表名 from 用户名
查询用户拥有那些权限(用户都能使用)
select * form user_tab_privs; -> 查询授予用户的对象权限
select * form user_role_privs; -> 查询授予用户的角色权限
select * form user_sys_privs; -> 查询授予用户的系统权限
select * form session_privs; -> 查询当前用户拥有的所有权限
查看全部用户拥有权(dba身份访问)
select * from dba_tab_privs; -> 查询授予全部用户的对象权限
select * from dba_role_privs; -> 查询授予全部用户的角色权限
select * from dba_sys_privs; -> 查询授予全部用户的系统权限
2、角色
角色是一组权限的结合,将角色赋给一个用户,这个用户就拥有了和这个角色中的所有权限
角色分为 -> 预定义角色和自定义角色
系统预定义角色 ->预定义角色是在数据库安装后系统自动创建的一些常用的角色
DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构
RESOURCE: 拥有resource权限的用户只可以创建实体, 不可以创建数据库结构
CONNECT: 拥有Connect权限的用户则可以登录Oracle, 不可以创建实体, 不可以创建数据结构
注: 对于普通用户: 授予 connect, resource 权限
对于 DBA 管理用户: 授予 connect, resource, dba 权限
connect resource dba这些预定义角色主要是为了向后兼容. 其主要是用于数据库管理, oracle 建议用户自己设计数据库管理和安全的权限规划, 而不要简单的使用这些预定角色, 将来的版本中这些角色可能不会作为预定义角色
delete_catalog_role, execute_catalog_role, select_catalog_role 这些角色主要用于访问数据字典视图和包
exp_full_database,imp_full_database这两个角色用于数据导入导出工具的使用
自定义角色
Oracle建议自定义自己的角色,可以更加灵活方便管理用户
创建角色
create role admin;
授权给角色
grant connect,resource,to admin[with admin option];
grant select,insert on 表名 to admin [with grant option];
将角色赋予用户
grant admin to 用户[with admin option];
grant role_name to role_name[with admin option];
撤销角色权限
revoke connect from admin ;
删除角色
drop role admin ;
五、SQL语言与函数
SQL概述
SQL(Structure Query Language)机构化查询语言, 是一种数据库查询和程序设计语言, 用于存取数据以及查询,更新和管理关系数据库系统, 同时也是数据脚本文件的扩展名
SQL语言的主要部分
1)数据定义语言Data Definition Language(DDL) -> 用来建立数据库,数据对象和定义序列(如:create drop alter等语句)
2)数据操作语言Data Manipulation Language(DML) -> 用来插入, 修改, 删除, 查询, 可以修改数据库中的数据(如:insert(插入) update(修改) delete(删除)语句)
3)数据查询语言Data Query Language(DQL) -> 是SQL语言中, 负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句(如: select(查询))
4)数据控制语言Data Controlling Language(DCL) -> 用来控制数据库组件的存取允许,存取权限等(如: grant revoke commit rollback等语句)
5)事务控制语言Transactional Control Language(TCL) -> 用于维护数据的一致性包括commit(提交事务), rollback(回滚事务)和savepoint(设置保存点)3条语句
概念
1、SQL
结构化查询语言,是一门用于操作数据库的程序设计语言
2、数据库
存放数据的仓库
数据表: 存放数据的载体,在关系数据库中,以二维表的形式存放数据
行: 是数据的集合 -> 记录
列: 具有某种数据类型的数据
3、关键字(字段）
关键字是关系模型中一个重要概念,它是逻辑结构,不是数据库的物理部分
1)候选关键字: 如果一个属性集能惟一地标识表的一行而又不含多余的属性,那么这个属性集称为侯选关键字
2)主关键字(主键): 主关键字是被挑选出来, 作表的行的惟一标识的侯选关键字 一个表最多只可以有一个主关键字,主关键字又称为主键
3)公共关键字: 在关系数据库中, 关系之间的联系是通过相容或相同的属性注来表示的; 如果两个关系中具有相容或相同的属性或属性组, 那么这个属性或属性组被称为这两个关系的公共关键字
4)外关键字(外键): 如果公共关键字在一个关系中是主关键字, 那么这个公共关键字被称为另一个关系的外关键字; 由此可见, 外关键字表示了两个关系之间的联系;以另一个关系的外关键字作主关键字的表被称为主表, 具有此外关键字的表被称为主表的从表; 外关键字又称作外键; 另外, 表间关系也是通过主键来实现的; 一个表可以有多少外键, 即可以跟另外多个表建立关系;
注:
a)外键一般设置多的一边,冗余少
b)外键关联的字段, 在另一张表一般是主键
c)主键所在的表称为主键表(主表) 外键所在的表称为外键表(从表)
d)外键与主键的数据类型要一致
Oracle数据的数据类型
定义: 开辟空间, 指定空间存放的数据种类,规范数据符合实际
类型
1.字符类型
1)char(长度): 1~2000 字节 ->固定长度字符串,长度不够的用空格补充
2)varchar2(长度): 1~4000 字节 ->可变长度字符串, 与 char类型相比, 使用 varchar2可以节省磁盘空间, 但查询效率没有 char 类型高
2.数值类型
number(m,n): m(1~38) n(-84~127) ->可以存储正数 负数 零 定点数和精度为38位的浮点数, 其中, m代表精度, 代表数字的总位数; n表示小数点右边素质的位数
3.日期类型
date: 7字节 ->用于存储表中的日期和时间数据, 取值范围是公元前4712年1月1日至公元9999 年12月31日, 7 个字节分别表示世纪 年 月 日 时 分和秒
4.其他类型
1)二进制数据类型:
raw(1-2000字节) ->可变长二进制数据, 在具体定义字段的时候必须指明最大长度 n
long raw(1-2GB) ->可变长二进制数据
2)LOB数据类型
CLOB(Character Large Object 1-4GB) ->只能存储字符数据
NCLOB(Native Character Large Object 1-4GB) ->保存本地语言字符集数据
BLOB(Binary Large Object 1-4GB) ->以二进制信息保存数据
运算符
1. 算术运算符[+,-,*,/,mod(10,3)]
2. 关系运算符[>,>=,<,<=,!=,<>,=]BETWEEN…AND、IN、LIKE 和 IS NULL]
3. 逻辑运算符 and or not
4. 集合操作符 union,union all,minus,INTERSECT
union:取出 2 个表数据合并，去除重复记录
union all:取出 2 个表数据合并，保留重复记录
minus:取出 2 个表中不同数据
Intersect:取出 2 个表中共有数据【交集】
5. 连接操作符 ||
DDL语言（数据定义语言）
1、create table 命令
用于创建表, 在创建表时，经常会创建该表的主键、外键、唯一约束、Check 约束等
语法结构
create table 表名(
[字段名] [类型] [约束]
………..
primary key(column1,column2),
foreign key(column1,column2,…..column_n),
references tablename(column1,column2,…..column_n)
)
2、alter table 命令
对已经存在的表进行修改，可以新增或删除字段，修改字段名或其类型和类型长度
修改表名: alter table old_table rename to new_table
修改字段名:alter table table_name rename column old_column to new_column
添加字段: alter table table_name add (new_column varhcar2(200) default‘null’,new_column2 number)
修改数据类型:alter table table_name modifiy (filedname varchar2(100))
删除字段:alter table table_name drop column column_name
添加主键alter table table_name add constraint pk_name primary key(column_id)
删除主键:alter table table_name drop constraint pk_name
添加外键,检查,唯一约束alter table table_name add constraint fk_name foreign key(column_id) references 主表(主键)
添加检查约束: Alter table stu add constraint ck_sname check(length(sname)>=2)
添加唯一约束:alter table stu add constraint uq_sname unique(sname)
删除外键:alter table table_name drop constraint fk_name
3、drop table命令
用于从数据库中删除表及全部数据
drop table table_name [purge];–purge:清除缓存
4、Truncate table 命令
可以快速删除表的记录并释放空间，不使用事务处理，速度快且效率高，但无法回滚事务:truncate table table_name
5、其他create命令
create index：创建数据表索引
create procedure：创建存储过程
create function：创建用户函数
create view：创建视图
create trigger：创建触发程序
create sequence: 创建序列
6、SEQUENCE
在 oracle 中 sequence 就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
创建语法：
create sequence sequence_marks
increment by 1 –每次加几个
start with 1 –从 1 开始计数
nomaxvalue –不设置最大值
nocycle –一直累加，不循环
cache 10 –使序列号预分配,默认 nocache
例子: select sequence_marks.currval from dual insert into table_name values(sequence_marks.nextval)
删除 :drop sequence sequence_marks
DML语言 -> 数据操作语言
1、 INSERT 语句
Insert into table_name(column1,column2 … … .column_n) values (val1….)
插入多条：
insert into 表名(列名…..) select 列名…. from 源表名
insert into stu select 1,\’abc\’ from dual union
select 2,\’abcd\’ from dual;
2、UPDATE 语句
update table_name set column1=value,…. where [condition…]
3、DELETE 语句
Delete from table_name where [condition…]
DQL语言 -> 数据库查询语言
1、基本语法
SELECT column_list[*查询所有数据]
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
例子：
SQL>select * from em–查询所有数据
SQL>select ename,job from em–查询指定的字段数据
SQL> select * from emp where sal>1000–加条件
2、聚合函数
注：以下例子数据来自ORACLE自带表“EMP”和“DEPT”
聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。不能在WHERE 子句中使用组函数。
AVG(expression): 返回集合中各值的平均值
查询所有人都的平均工资
select avg(sal) from emp
COUNT(expression): 以 Int32 形式返回集合中的项数
查询工资低于 2000 的人数
select count(*) from emp where sal<2000
MAX(expression): 返回集合中的最大值
查询最高工资
select max(sal) from emp
MIN(expression): 返回集合中的最小值
查询最低工资
select max(sal) from emp
SUM(expression): 返回集合中所有值的总和
查询部门编号为 20 的工资总和
select sum(sal) from emp where deptno=20
3、排序函数
用于根据指定的列对结果集进行排序
ORDER BY 语句
查询所有信息并按工资排序
select * from emp order by sal
升序(asc)
查询所有信息并按工资升序排序
select * from emp order by sal asc
降序(desc)
查询所有信息并按工资降序排序
select * from emp order by sal desc
ROWNUM 与 ORDER BY[ROWNUM：伪列，只能使用<,<=,!=]
4、分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
GROUP BY 子句
查询每个部门的平均工资
select avg(sal) from emp group by deptno
HAVING 子句
查询部门的平均工资大于 2000
select avg(sal) from emp group by deptno having avg(sal)>2000
5、连接查询
连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。
1）内连接
内连接也叫连接，是最早的一种连接。还可以被称为普通连接或自然连接
查询工资最高的 5 个人信息
select e.*,rownum 编号 from (select * from emp order by sal desc) e
where rownum<=5
[或 rownum!=6]
这自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
等值连接:
select * from emp inner join dept on emp.deptno=dept.deptno
select * from emp,dept where emp.deptno=dept.deptno
不等值连接:
select * from emp inner join dept on emp.deptno!=dept.deptno
2）外连接
外连接分为三种：左外连接，右外连接，全外连接。对应 SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略 outer 这个关键字。
写成：LEFT/RIGHT/FULL JOIN。
左外连接(left join): 是以左表的记录为基础的
select * from emp left join dept on emp.deptno=dept.deptno
select * from emp ,dept where emp.deptno=dept.deptno(+)
右外连接(right join): 和 left join 的结果刚好相反,是以右表(BL)为基础的
select * from emp right join dept on emp.deptno=dept.deptno
select * from emp , dept where emp.deptno(+)=dept.deptno
全外连接(full join): 左表和右表都不做限制，所有的记录都显示，两表不足的地方用 null 填充
select * from emp full join dept on emp.deptno=dept.deptno
3）交叉连接
交叉连接即笛卡儿乘积，是指两个关系中所有元组的任意组合。一般情况下，交叉查询是没有实际意义的。
select * from cross full join dept
6、常用查询
1）like 模糊查询【Oracle 通配符只支持%与_】
查询姓名首字母为 S 开始的员工信息
select * from emp where ename like \’S%\’
查询姓名第三个字母为 A 的员工信息
select * from emp where ename like \’__A%\’
2）is null/is not null 查询
查询没有奖金的雇员信息
select * from emp where comm is null
查询有奖金的雇员信息
select * from emp where comm is not null
3）in 查询
查询雇员编号为 7566、7499、7844 的雇员信息
select * from emp where empno in(7566,7499,7844)
4）exists/not exists 查询(效率高于 in)
查询有上级领导的雇员信息【查询原理:一条一条读写记录】
select * from emp e where exists
(select * from emp where empno=e.mgr)
查询没有上级领导的雇员信息
select * from emp e where not exists
(select * from emp where empno=e.mgr)
5）all 查询
查询比部门编号为 20 的所有雇员工资都高的雇员信息
select * from emp where sal > all(select sal from emp where deptno=20)
6） union 合并不重复
select * from emp where comm is not null
union
select * from emp where sal>3000
7）union all 合并重复
select * from emp where comm is not null
union all
select * from emp where sal>3000
7、子查询
当一个查询是另一个查询的条件时，称之为子查询。子查询是一个SELECT 语 句 ， 它 嵌 套 在 一 个 SELECT 、 SELECT…INTO 语 句 、INSERT…INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。
1）在 CREATE TABLE 语句中使用子查询
创建表并拷贝数据
create table temp(id,name,sal) as select empno,ename,sal from emp
在 INSERT 语句中使用子查询
当前表拷贝
insert into temp(id,name,sal) select * from temp
从其他表指定字段拷贝
insert into temp(id,name,sal) select empno,ename,sal from emp
2）在 DELETE 语句中使用子查询
删除 SALES 部门中的所有雇员
delete from emp where deptno in
(select deptno from dept where dname=\’SALES\’)
3）在 UPDATE 语句中使用子查询
修改 scott 用户的工资和 smith 的工资一致
minus:取出 2 个表中不同数据
create table emp2 as select * from emp where rownum<=5
取出 emp 中与 emp2 不同数据
select * from emp minus select * from emp2
Intersect:取出 2 个表中共有数据【交集】
select * from emp intersect select * from emp2
update emp set sal=(select sal from emp where ename=\’SMITH\’) where ename=\’SCOTT\’
修改 black 用户的工作,工资,奖金和 scott 一致
update emp set(job,sal,comm)=(select job,sal,comm from emp where ename=\’SCOTT\’) where ename=\’BLAKE\’
4）在 SELECT 语句中使用子查询
查询和 ALLEN 同一部门的员工信息
select * from emp where deptno in (select deptno from emp where ename=\’ALLEN\’)
查询工资大于部门平均工资的雇员信息
select * from emp e
(select avg(sal) sal,deptno from emp group by deptno) t
where e.deptno=t.deptno and e.sal>t.asal
TCL语言 -> 事务控制语言
1、COMMIT
commit –提交事务
2、ROLLBACK
rollback to p1 –回滚到指定的保存点
rollback –回滚所有的保存点
3、SAVEPOINT
savepoint p1 –设置保存点
4、只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它 dml 操作的事务，它的作用是确保用户只能取得某时间点的数据。
set transaction read only
六、ORACLE函数
函数的定义
具有某种功能的代码段
实现代码重用，模块化编程
分类
1.系统函数,用户自定义函数
2.参数,返回值
1)无参无返
2)无参有返
3)有参无返
4)有参有返
函数中有两个角色：主调函数，被调函数
参数：主调函数给被调函数传递的信息（数据）
参数的数量：0个或多个
形式参数(形参)：在定义函数时的参数
实际参数(实参)：在调用函数时的参数
返回值：被调函数给主调函数传递的信息（数据）
返回值的数量：0个或１个
ORACLE提供的系统函数
1、数学函数
求绝对值
select abs(-4) from dual
power(n,m):n的m次方
select power(2,3) from dual
返回大于或等于n最小整数值（3，4，5…）
select ceil(2.48) from dual ; –3
select ceil(2.68) from dual ; –3
返回小于或等于n最大整数值（2，1，0，-1…）
select floor(2.48) from dual ; –2
select floor(2.68) from dual ; –2
四舍五入
select round(2.48) from dual ; –2
select round(2.68) from dual ; –3
四舍五入，设置保留位数
select round(2.48,1) from dual ; –2.5
select round(2.163,2) from dual ; –2.16
随机数
1）小数(0 ~ 1)
select dbms_random.value from dual;
2）指定范围内的小数 ( 0 ~ 100 )
select dbms_random.value(0,100) from dual;
3）指定范围内的整数 ( 0 ~ 100 )
select round(dbms_random.value(0,100),0) from dual;
select round(dbms_random.value(0,100)) from dual;
4）长度为20的随机数字串
select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;
5）随机字符串
select dbms_random.string(opt, length) from dual;
其中：opt为选项，规则如下所示：
\’u\’,\’U\’ : 大写字母
\’l\’,\’L\’ : 小写字母
\’a\’,\’A\’ : 大、小写字母
\’x\’,\’X\’ : 数字、大写字母
\’p\’,\’P\’ : 可打印字符
length为随机字符串的长度
select dbms_random.string(\’a\’,10) from dual;
6）生成GUID：32位十六进制字符串
select sys_guid() from dual;
select length(sys_guid()) from dual;
2、字符串函数
length:求字符串的长度
print len(\’hello,world\’)
select length(ename),ename from emp ;
lower/upper:大小写
select ename,lower(ename),upper(ename) from emp ;
concat/||：字符串连接
select concat(\’hello \’,\’world\’) from dual ;
select \’hello \’ || \’world\’ from dual ;
substr(“字符串”,start,n):截取字符串，从start开始截取n个字符
select substr(\’hello,world\’,1,3) from dual;
select ename,substr(ename,1,3) from emp ;
replace:替换字符串
replace(\’字符串\’,\’被替换子字符串\’,\’替换字符串\’)
select replace(\’hello world\’,\’world\’,\’china\’) from dual ;
instr:查找字符串
instr(\’字符串\’,\’查找字符/字符串\’,\’起始位置\’),返回下标位置（从1开始）
select instr(\’abcabc\’,\’c\’,-2) from dual;
select instr(\’abcabc\’,\’c\’,0) from dual;
select instr(\’abcabc\’,\’c\’) from dual;
注：起始位置中，正数从左向右、负数从右向左查找
trim:去掉字符串左边、右边两边的空格
ltrim:去掉字符串左边空格
rtrim:去掉字符串右边空格
select \’AAA\’ || \’ BBB \’ || \’CCC\’ from dual ;
select \’AAA\’ || trim( \’ BBB \’) || \’CCC\’ from dual ;
3、日期函数 getdate()
sysdate：返回当前session所在时区的默认时间
获取当前系统时间
select sysdate from dual;
add_months：返回指定日期月份+n之后的值，n可以为任何整数
查询当前系统月份+2 的时间
select add_months(sysdate,2) from dual;
查询当前系统月份-2 的时间
select add_months(sysdate,-2) from dual;
last_day：返回指定时间所在月的最后一天
获取当前系统月份的最后一天
select last_day(sysdate) from dual;
months_between：返回月份差，结果可正可负，当然也有可能为 0
获取入职日期距离当前时间多少天
select months_between(sysdate, hiredate) from emp;
select months_between(hiredate, sysdate) from emp;
trunc（number,num_digits）
用法一：截取日期值
select trunc(sysdate) from dual –2013-01-06 今天的日期为2013-01-06
select trunc(sysdate, \’mm\’) from dual –2013-01-01 返回当月第一天.
select trunc(sysdate,\’yy\’) from dual –2013-01-01 返回当年第一天
select trunc(sysdate,\’dd\’) from dual –2013-01-06 返回当前年月日
select trunc(sysdate,\’yyyy\’) from dual –2013-01-01 返回当年第一天
select trunc(sysdate,\’d\’) from dual –2013-01-06 (星期天)返回当前星期的第一天
select trunc(sysdate, \’hh\’) from dual –2013-01-06 17:00:00 当前时间为17:35
select trunc(sysdate, \’mi\’) from dual –2013-01-06 17:35:00 TRUNC()函数没有秒的精确
用法二：截取数值
number:需要截尾取整的数字。
num_digits:用于指定取整精度的数字。Num_digits 的默认值为 0。
trunc()函数截取时不进行四舍五入
select trunc(123.458) from dual –123
select trunc(123.458,0) from dual –123
select trunc(123.458,1) from dual –123.4
select trunc(123.458,-1) from dual –120
select trunc(123.458,-4) from dual –0
select trunc(123.458,4) from dual –123.458
select trunc(123) from dual –123
select trunc(123,1) from dual –123
select trunc(123,-1) from dual –120
4、转换函数
to_char：日期转换
select to_char(sysdate, \’yyyy-mm-dd hh24:mi:ss\’) from dual
select to_char(sysdate, \’yyyy-mm-dd hh12:mi:ss\’) from dual
to_char：数字转换
select to_char(-100.789999999999,\’L99G999D999\’) from dual
select to_char(-100000.789999999999,\’L99G999D99\’) from dual
select to_char(-100000.789999999999,\’L999G999D99\’) from dual
9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)
0 强制显示该位，如果当前位有数字，显示数字，否则显示 0
. （句点） 小数点
, （逗号） 分组（千）分隔符
PR 尖括号内负值
S 带负号的负值（使用本地化）
$ 美元符号显示
L 货币符号（使用本地化）
D 小数点（使用本地化）
G 分组分隔符（使用本地化） 10,000
MI 在指明的位置的负号（如果数字 < 0）
PL 在指明的位置的正号（如果数字 > 0）
SG 在指明的位置的正/负号
RN 罗马数字（输入在 1 和 3999 之间）
TH or th 转换成序数
V 移动 n 位（小数）（参阅注解）
EEEE 科学记数,现在不支持
to_date：将字符串转换成日期对象
select to_date(\’2011-11-11 11:11:11\’, \’yyyy-mm-ddhh24:mi:ss\’) from dual
to_number：将字符转换成数字对象
select to_number(\’209.976\’)*5 from dual
select to_number(\’209.976\’, \’9G999D999\’)*5 from dua
5、空值判断函数
nvl(内容,data):空值函数，类似 SQLServer中的 null()函数,如果内容为空，则值设置为data
select ename,comm,nvl(comm,0) from emp;
nvl2(内容,data1,data2):如果内容不为空，则值设置为data1,否则设置为data2
select ename,comm,nvl2(comm,comm+200,200) from emp;
nullif(a,b):如果 a,b 的值相等，返回 null，如果不相等，返回a
select nullif(10,10) from dual; –空，神马都没有
select nullif(10,11) from dual; –返回 10
6、分析函数
row_number() over (order by 字段 asc|desc):为有序组中的每一行（划分组的行或查询行）返回一个唯一的排序值
select ename,sal,row_number() over(order by sal desc) 名次 from emp;
rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次跳过相应次数
select ename,sal,row_number() over(order by sal desc) 名次,rank() over(order by sal desc) 名次 from emp;
dense_rank() over(order by 字段 asc|desc):排名中如果出现相同的，名次相同，后面的名次不跳过相应次数
select
ename,
sal,row_number() over(order by sal desc) 名次1,
rank() over(order by sal desc) 名次2,
dense_rank() over(order by sal desc) 名次3
from emp;
七、数据库对象
概述：ORACLE 数据库是关系型数据库，同时也是面向对象关系型数据库，又称ORDBMS，因此，在 ORACLE 数据库中也有专属的 ORACLE 对象，主要有如下数据库对象：同义词 序列 表 表分区 视图 过程 索引。以下简单讲解同义词、序列、表分区、视图、索引
同义词
1、概念
同义词是数据库方案对象的一个“别名”，经常用于简化对象访问和提高对象访问的安全性。
同义词并不占用实际存储空间，只在数据字典中保存了同义词的定义。
Oracle同义词有两种类型，分别是公用 Oracle 同义词与私有 Oracle 同义词。
2、问题
select * from scott.emp;
当前登录的session是非scott用户，访问emp表时，必须指定schema.表名
3、解决：同义词
创建公用同义词
create public synonym syn_emp for scott.emp;
通过访问同义词来简化对象的访问
select * from syn_emp;
4、创建同义词的语法
create [or replace] [public] synonym sys_name
for [schema.]object_name
说明：
create:创建同义词
create or replace:没有则创建，有则替换
public:声明公用同义词，不写则为私有同义词
synonym:关键字
sys_name:用户创建同义词的名称，建议以sys_为前缀
for：关键字
schema:对象的集合，如包含tables, views, sequences, synonyms, indexes等；
一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema
object_name:对象名
eg1:公用同义词
create public synonym syn_emp for scott.emp;
select * from syn_emp;
eg2:私用同义词
create or replace synonym syn_pri_emp for scott.emp
select * from syn_pri_emp;
5、查看同义词
查看当前用户创建的私有同义词
select * from user_synonyms;
查看当前用户创建的所有同义词
select * from all_synonyms where table_owner=\’SCOTT\’;
select * from dba_synonyms where table_owner=\’SCOTT\’;
6、删除同义词
drop public synonym 公有同义词名称
drop synonym 私有同义词名称
7、注意事项
1）用户必须拥有 Create public synonym 的系统权限才能创建公共同义词;只有Create any synonym 权限才能创建私有同义词
2）用户必须拥有同义词所定义对象的权限才能进行访问，同义词不代表权限
3）同义词不仅可以查询，还可以添加，删除，修改，但都作用于物理表
序列
1、概念
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方；Oracle的序列(SEQUENCE)类似SQLServer中的自动增长列，用来生成唯一，连续的整数的数据库对象，序列通常用来生成主键或唯一值，并且可以排序。
2、语法
CREATE SEQUENCE sequence_name
INCREMENT BY 1 –每次加几个 默认 1
START WITH 1 –从 1 开始计数 默认 1
[MAXVALUE 值|NOMAXVALUE] –设置最大值 默认最大 10E27
[MINVALUE 值|NOMINVALUE] –设置最小值 默认最小-10E26
[CYCLE|NOCYCLE] –一直累加，不循环
[CACHE 10|NoCYCLE] –使序列号预分配
[Order|NoOrder 默认]
eg:
CREATE SEQUENCE seq_test
INCREMENT BY 1 –每次加几个
START WITH 1 –从1开始计数
NOMAXVALUE –不设置最大值
NOCYCLE –一直累加，不循环
CACHE 10 –使序列号预分配10个数,默认NOCACHE
3、访问序列的值
NEXTVAL:返回序列的下一个值
CURRVAL:返回序列的当前值
select 序列.nextval from dual
select 序列.currval from dual
select seq_test.nextval from dual
select seq_test.currval from dual
4、使用
insert into 表名（自动增长的字段） values (序列名称.nextval)
5、修改
alter sequence 序列名称 increment by 2;
alter sequence seq_test increment by 2;
每次加2
1）不能修改序列的初始值
2）序列的最小值不能大于当前值
3）序列的最大值不能小于当前值
5、删除序列
drop sequence 序列名称
表分区
1、概述
在ORACLE中，当表的数据不断增加后，查询数据的速度就会降低，应用程序的效率也将大大下降，每次检索数据时都得扫描整张表，浪费了极大的资源，如何处理超大表数据存储和查询带来的问题， ORACLE 提供了特有的表分区技术。
2、什么是表分区
ORACLE的表分区是一种处理超大型表，索引等对象的技术，简单可以理解为分而治之，即将一张大表分成可以管理的小块。表分区后逻辑上依然是同一张表，只是将表中的数据在物理上存储到多个(表空间)物理文件上。
3、表分区的优点
1）增强可用性【一个分区出问题，不影响其他分区】
2）维护方便【同上，只维护部分分区】
3）均衡 IO【不同分区映射到磁盘平衡 IO】
4）改善查询性能【检索自己需要的分区，提高检索速度】
4、分类
4.1）范围分区–最早,最经典,数据管理能力强,但分配不均匀
范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上
这种分区方式是最为常用的,并且分区键经常采用日期,数值
1）语法：
create table 表名
(
字段名1 类型 [约束],
…
字段名n 类型 [约束]
)
表分区的定义
partition by range (column_name)
(
partition part1 values less than (rang1) [tablespace tbs1],
partition part2 values less than (rang2) [tablespace tbs2],
…
partition partn values less than (maxvalue) [tablespace tbsn],
);
其中，
column_name: 指定分区字段
part1…partn:是表分区的名称
rang1…maxvalue:表分区的边界值，其中maxvalue表示边界最大值，每个分区的边界值必须比下一个分区的边界值小
tablespace：表空间（可选），指定表分区所在的表空间
tbs1…tbsn：表分区所在的表空间
注意：只能在创建表时创建表分区（指定相关的表分区类型），而不能对现有的表(未创建表分区)创建表分区
2）例子：
eg1:根据某个值的范围来分区
create table part_andy1
(
andy_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),
tatus char(1)
)partition by range (andy_id)(
partition part1 values less than (10000) ,
partition part2 values less than (20000)
);
说明：
andy_id字段的数值小于10000分配在part1分区，
andy_id字段的数值大于等于10000且小于20000分配在part2分区，
andy_id字段的数值大于等于20000将会出错，数据无法添加
eg2:根据日期分区
注意：如果是date类型的字段，则必须使用年份为4个字符的格式，需要使用to_date()函数指定分区边界
create table part_andy2
(
order_id number(7) not null,
order_date date,
otal_amount number,
custotmer_id number(7),
paid char(1)
)partition by range (order_date)(
partition p1 values less than (to_date(\’2014-10-1\’, \’yyyy-mm-dd\’)) ,
partition p2 values less than (to_date(\’2015-10-1\’, \’yyyy-mm-dd\’)) ,
partition p3 values less than (maxvalue)
);
说明：
order_date在2014-10-1之前的，分配在p1分区，
order_date大于或等于2014-10-1且小于2015-10-1的，分配在p2分区，
order_date大于或等于2015-10-1，分配在p3分区
3）修改分区–分区界限必须调整为高于最后一个分区界限
alter table 表名
add partition 表分区名称 values less than (值);
alter table part_andy1
add partition part3 values less than (30000)
4）截断分区–分区中数据将全部删除，但分区依然存在
alter table 表名
truncate partition 分区名;
alter table part_andy1
truncate partition part2;
5）合并分区–将两个相邻分区合并成一个新分区，继承原分区中最高上限（可重用上界限名称，下界限不可以，也可以使用新的）
alter table 表名
merge partitions 分区名1,分区名2 into partition 新分区名或原上界限名称;
alter table part_andy1
merge partitions part2,part3 into partition part3;
6）拆分分区–将一个分区在指定的 value 值处一分为二，变成 2 个分区，原分区将不存在，数据将分到相应新的分区
alter table 表名
split partition 原表分区 at (value) into (partition 拆分表分区1,partition 拆分表分区1);
查询part_andy1中，表分区part3的数据
select * from part_andy1 partition(part3);
拆分分区
alter table part_andy1
split partition part3 at (20000) into (partition part31,partition part32);
查看
select * from part_andy1 partition(part31);
select * from part_andy1 partition(part32);
7）变更分区名–将分区名称改变
alter table 表名
rename partition 原分区名 to 新分区
alter table part_andy1
rename partition part31 to part31_new
8）删除分区
alter table 表名
drop partition 分区名
alter table part_andy1
drop partition part31_new
4.2）散列分区–适合静态数据，总体性能最佳，易于实施，均匀
散列分区是在列值上使用散列算法， 通过在分区键上执行 hash 函数决定存储的分区，将数据平均地分布到不同的分区，当列的值没有合适的条件时，建议使用散列分区。
create table employee
(
emp_id number(4),
emp_name varchar2(14),
emp_address varchar2(15),
department varchar2(10)
)partition by hash (department)
(
partition p1,
partition p2,
partition p3
)
partitions 4;
select * from employee partition(p1);
select * from employee partition(p2);
select * from employee partition(p3);
4.3）列表(list)分区
列表分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区，允许用户将不相关的数据组织在一起。
create table employee
(
emp_id number(4),
emp_name varchar2(14),
emp_address varchar2 (15)
)partition by list (emp_address)(
partition north values (\’北京\’) ,
partition west values (\’成都\’,\’重庆\’) ,
partition south values (\’广州\’, \’深圳\’),
partition east values (\’杭州\’, \’苏州\’,\’温州\’)
);
添加数据
insert into employee values(1,\’zhangsan\’,\’北京\’);
insert into employee values(2,\’lucy\’,\’广州\’);
insert into employee values(3,\’petter\’,\’深圳\’);
查询数据
select * from employee partition (north); –zhangsan
select * from employee partition (south); –lucy、petter
4.4) 复合分区：
形式一：范围-散列分区
表首先按某列进行范围分区，然后再按散列算法进行散列分区，分区之中的分区被称为子分区
create table slog
(
sno number,
sinfo varchar(300)
)partition by range(sno) –范围分区
subpartition by hash(sinfo) –散列分区
subpartitions 6(
partition p1 values less than (2000),
partition p2 values less than (4000),
partition p3 values less than (6000),
partition p4 values less than (8000)
);
形式二：范围-列表分区
表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
create table slog
(
sno number,
sinfo varchar(300)
)
partition by range(sno) –范围分区
subpartition by list(sinfo) –列表分区
subpartition template(
subpartition t1 values(\’404\’,\’notfind\’),
subpartition t2 values(\’500\’,\’error\’),
subpartition t3 values(\’200\’,\’success\’)
)
(
partition p1 values less than (2000),
partition p2 values less than (4000),
partition p3 values less than (6000),
partition p4 values less than (8000)
);
4.5）interval分区
11g 版本引入的 interval 分区范围分区的一种增强功能，可实现 equi-sized 范围分区的自动化。创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引
create table test
(
id number,
order_date date
) partition by range (order_date)
interval (numtoyminterval(1,\’month\’))
(
partition p_first values less than (to_date(\’2013-06-23\’,\’yyyy-mm-dd\’))
);
insert into test values(1,to_date(\’2013-06-22\’,\’yyyy-mm-dd\’));
insert into test values(1,to_date(\’2013-06-23\’,\’yyyy-mm-dd\’));
insert into test values(1,to_date(\’2013-07-20\’,\’yyyy-mm-dd\’));
insert into test values(1,to_date(\’2013-07-24\’,\’yyyy-mm-dd\’));
select * from test partition (p_first);
……
5、分区原则
1）表的大小：当表的大小超过 1.5GB－2GB，或对于 OLTP 系统，表的记录超过 1000 万，都应考虑对表进行分区。
2）数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。
3）数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要
4）数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建立对应关系。
5）只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。
6）并行数据操作：对于经常执行并行操作（如 Parallel Insert,Parallel Update等）的表应考虑进行分区
7）表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。
视图
1、概念
1) 视图是一张虚拟的表，此表的结构从一个或多个表（或其它视图）查询的得到的结果一致
2) 视图一经定义，则以对象的方式存储在Oracle数据库中，视图中的数据是来源于查询的基表；对视图的CRUD操作，相应的基表也会发生变化
3) 对视图的更新或者插入限制很多,事实上,除非视图包含的是简单的select语句,否则不能通过它来做更新.推荐的做法还是在基表上做更新或者插入操作,一般情况下,视图只是用来方便查询的
2、优点
1) 集中用户使用的数据
2) 掩盖数据库的复杂性
3) 简化用户权限的管理
4) 重新组织数据
5) 不占物理存储空间，它只是一个逻辑对象（虚拟的表）
3、视图分类
关系视图：
关系视图(relational view)基本上就是经过存储的查询，可以将它的输出看作是一个表。它就是基于关系数据的存储对象
内嵌视图：
又称为嵌套查询，是嵌入到父查询中的查询，能够在任何可以使用表名称的地方使用
对象视图：
为了迎合数据库中对象类型而将关系表投射到特定数据类型的虚拟对象表中，视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。
物化视图：
就是在数据库中查询结果存储在视图中，并支持查询重写、刷新、提交等特性的视图
4、视图的创建和管理
步骤:
1)编写select语句
2)测试select语句
3)查询结果的正确性
4)创建视图
注: 1）普通用户并没有创建视图的权限，如果要创建视图，需要使用 dba 角色的用户赋予 create view 的权限，如： grant create view to scott;
2）查看视图：User_views,All_views,Dba_views
语法:
create [or replace] [force] view view_name [(alias[, alias]…)]
as
select_statement
[with check option [constraint constraint]]
[with read only]
其中:
or replace：若所创建的试图已经存在，oracle自动重建该视图；
force：不管基表是否存在oracle都会自动创建该视图；
noforce：只有基表都存在oracle才会创建该视图（默认）：
alias：为视图产生的列定义的别名；
select_statement：一条完整的select语句，可以在该语句中定义别名；
with check option ： 插入或修改的数据行必须满足视图定义的约束；
with read only ： 该视图上不能进行任何dml操作
a) 关系视图
语法:
create [or replace] [force] view
view_name [(alias[, alias]…)]
as select_statement
[with check option [constraint constraint]]
[with read only]
注意:普通用户并没有创建视图的权限，如果要创建视图，需要使用 dba 角色的用户赋予 create view 的权限
5、查看视图
可以通过 User_views,All_views,Dba_views 来查询视图信息
例如: Select * from user_views;–查看当前用户创建的视图
1. 创建普通关系视图[可对视图执行 DML 操作]
2. 创建只读视图
特点:顾名思义，只读，不能执行其他 DML 操作
create or replace view view_emp as select * from emp where
sal>=3000 with read only;
3. 创建检查视图
特点:执行 DML 操作时，自动检测是否满足创建视图时所建立的 where 条件，如果不满足，直接出错
create or replace view view_emp as select * from emp where
sal>=3000 with check option;
4. 创建连接视图
特点: 连接视图是指基于多个表所创建的视图，即定义视图的查询是一个连接查询。使用连接视图的主要目的是为了简化连接查询 [只能更新键保留表]
create or replace view view_emp as select e.*,d.dname from emp
e,dept d where e.deptno=d.deptno;
使用 scott 身份登录，创建视图 emp 表的简单视图
create view view_emp as select * from emp;
select * from view_emp;
update view_emp set sal=3200 where sal=3000
delete view_emp where rownum=1
5. 创建复杂视图
特点: 复杂视图是指包含函数、表达式或分组数据的视图，主要目的是为了简化
查询
create or replace view view_emp as select count(*) 人数,avg(sal+nvl(comm,0)) 平均工资,deptno 部门编号 from emp group by deptno;
6. 创建强制视图
特点:正常情况下，如果基表不存在，创建视图就会失败。但是可以使用 FORCE选项强制创建视图（前提是创建视图的语句没有语法错误），但此时该视图处于失效状态,调用会出错，直到这个基表已经存在
create or replace force view view_test
as select * from myemp;
select * from myemp; –错误
create table myemp –创建myemp表
as
select * from emp ;
select * from myemp; –正确
视图上的 DML 语句有如下限制：
1）只能修改一个底层的基表
2） 如果修改违反了基表的约束条件，则无法更新视图
3）如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或
4）GROUP BY 子句，则将无法更新视图
5）如果视图包含伪列或表达式，则将无法更新视图
b) 内嵌视图
概述：
内嵌视图就是嵌入到父查询中的查询，能够在任何可以使用表名称的地方使用内嵌视图又称为嵌套查询
嵌视图可以出现在 select 语句的 from 子句中，以及 insert into、update甚至是 delete from 语句中。内嵌视图是临时的，它只存在于父查询的运行期间
例：
select * from (select e.*,rownum rn from emp e) tab
where rn>=5 and rn<=10;
其中: select e.*,rownum rn from emp e 就是一个内嵌视图，临时有效
c) 物化视图
特点:正常情况下，如果基表不存在，创建视图就会失败。但是可以使用 force选项强制创建视图（前提是创建视图的语句没有语法错误），但此时该视图处于失效状态,调用会出错，直到这个基表已经存在
create or replace FORCE view view_test as select * from myemp;
select * from (select e.*,rownum rn from emp e) tab where rn>=5
and rn<=10;
其中: select e.*,rownum rn from emp e 就是一个内嵌视图，临时有效
物化视图简单理解就是一张特殊的物理表，预先计算并保存表连接或统计中需要耗时较多的操作的结果。物化视图也称为”快照”物化视图可以定时更新视图中的数据，对于大量数据统计查询后得出的小量结果集这种情况比较适合。物化视图可以查询表，视图和其它的物化视图我们可以通过 user_segments 查看用户创建对象所在资源情况 select * from user_segments
创建物化视图语法
create materialized view [mv_name]
[
tablespace [ts_name] — 指定表空间
build [immediate|deferred] — 创建时是否产生数据
refresh [fast|complete|force] — 快速、完全刷新
[on commit|on demand start with (start_time) next (next_time)] — 刷新方式
[with {primary key |rowid}] –快速刷新时候唯一标示一条记录
{enable|disabled} query rewrite – 是否查询重写
]
as {select_statement};
eg1:
create materialized view my_view
build immediate
refresh fast on commit
with rowid
as
select empno,ename,sal from emp where sal>=3000;
eg2:
create materialized view my_view
build immediate
refresh
on commit
enable query rewrite
as
select deptno,count(*) amount from myemp group by deptno;
eg3:
create materialized view my_view
refresh
start with sysdate next sysdate+1/48
with rowid
as
select count(*),avg(sal+nvl(comm,0)) sals from myemp;
构建一个创建时产生数据，快速刷新【限制比较多】，刷新方式为 on commit,快速刷新是唯一标示为 rowid 的案例
create materialized view my_view
build immediate
refresh fast on commit
with rowid
as
select empno,ename,sal from emp where sal>=3000;
Rowid 物化视图只有一个单一的主表，不能包括下面任何一项:
Distinct 或者聚合函数.
Group by，子查询，连接和 SET 操作
构建一个创建时产生数据，默认刷新，刷新方式为 on commit,刷新是唯一标示为 rowid，支持重写的案例
create materialized view my_view
build immediate refresh on commit enable query rewrite as
select deptno,count(*) amount from myemp group by deptno;
注意:包含聚集，group by 不能创建快速刷新
创建一个定时提交的物化视图
create materialized view log on emp with rowid
create materialized view my_view refresh start with sysdate next
sysdate+1/48 with rowid
as
select count(*),avg(sal+nvl(comm,0)) sals from myemp;
使用聚合函数和分组时，不能使用快速刷新模式，该案例使用的是默认刷新方式
6、作用
1）实现两个数据库之间的数据同步，可以存在时间差
2）如果是远程链接数据库的场景时，提高查询速度（由于查询逻辑复杂，数据量比较大，导致每次查询视图的时候，查询速度慢，效率低下）
分类:
1）包含聚集的物化视图
2）只包含连接的物化视图
3）嵌套物化视图
注: 无论哪种视图，都需要设置物化视图的创建方式、 查询重写、 刷新方式等
几个方面的功能选项
创建方式（Build Methods）
build immediate：是在创建物化视图的时候就生成数据。默认为build immediate
build deferred：是在创建时不生成数据，以后根据需要在生成数据
查询重写（query rewrite）
查询重写(enable query rewrite)：指当对物化视图的基表进行查询时，oracle 会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据
不查询重写(disable query rewrite)：指当对物化视图的基表进行查询时，oracle 会不会判断能否通过查询物化视图来得到结果，直接对基表进行查询数据而不从物化视图中读取数据。默认为disable query rewrite刷新(refresh)方式[自动 on commit，手动]
刷新的方法有四种：fast、complete、force 和 never。默认值是 force
刷新的模式有两种：on demand 和 on commit。 默认值是 on demand
7、创建视图日志文件
如果设置刷新方法为 fast，必须先构建一个基于基表的视图日志，语法如下：
create materialized view log on
<table_name>
[tablespace <tablespace_name>] –视图日志保存位置
[with [primary key｜rowid｜sequence ];–标示基表每一行
[ including new values]
注：如果设置刷新方法为 fast，必须先构建一个基于基表的视图日志
例：
create materialized view log on emp with rowid
手动刷新
begin
dbms_mview.refresh(\’MY_VIEW\’);
end
删除物化日志文件
drop materialized view log on empd
删除物化视图
drop materialized view my_view;
物化视图总结:
物化视图提供了可伸缩的基于主键或 ROWID 的视图,指定了刷新方法和自动刷新的时间。Oracle 的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中 SQL 语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新
8、常用系统视图
a) USER_*：有关用户所拥有的对象信息，即用户自己创建的对象信息。
1. select * from user_users;–查看当前用户信息
2. select * from user_tables;–查看当前用户创建表信息
3. select * from user_views;–查看当前用户创建视图信息
4. select * from user_tab_privs—查看当前用户表权限
5. select * from user_sys_privs;–查看当前用户系统
6. select * from user_role_privs;–查看当前用户角色
b) ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息。
c) DBA_*：有关整个数据库中对象的信息。这里的*可以为 TABLES，INDEXES， OBJECTS， USERS 等。
d) V$*：一般是动态视图，随着客户端或参数值设定的不同而不同
索引
1、概述
索引是一个单独的、物理的数据结构，在这个数据结构中包括表中一列或若干列的值以及相应的指向表中物理标识这些值的数据页的逻辑指针的集合。
索引是建立在表上的可选对象，设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担，进行影响系统的性能。
索引一旦建立后，当在表上进行 DML 操作时，Oracle 会自动维护索引，并决定何时使用索引。
索引的使用对用户是透明的，用户不需要在执行 SQL 语句时指定使用哪个索引及如何使用索引，也就是说，无论表上是否创建有索引，SQL 语句的用法不变。用户在进行操作时，不需要考虑索引的存在，索引只与系统性能相关。
2、索引的原理
当在一个没有创建索引的表中查询符合某个条件的记录时，DBMS 会顺序地逐条读取每个记录与查询条件进行匹配，这种方式称为全表扫描。全表扫描方式需要遍历整个表，效率很低。
3、索引的类型
Oracle 支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。
1)单列索引和复合索引[索引列多少]
2) B 树索引[结构]
3) 位图索引[结构]
4)函数索引[语法]
4、创建索引
语法：
create [unique] | [bitmap] index index_name
on table_name([column1 [asc|desc],column2
[asc|desc],…] | [express])
[tablespace tablespace_name]
[pctfree n1]
[storage (initial n2)]
[nologging]
[noline]
[nosort]
解析：
unique：表示唯一索引，默认情况下，不使用该选项。
bitmap：表示创建位图索引，默认情况下，不使用该选项
pctfree：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间
nologging：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项
online：表示在创建或重建索引时，允许对表进行 dml 操作。默认情况下，不使用该选项
nosort：默认情况下，不使用该选项。则 oracle 在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项
5、单列索引和复合索引
一个索引可以由一个或多个列组成。基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引
b 树索引
b 树索引是 oracle 数据库中最常用的一种索引。当使用 create index语句创建索引时，默认创建的索引就是 b 树索引。b 树索引就是一棵二叉树，它由根、分支节点和叶子节点三部分构成。叶子节点包含索引列和指向表中每个匹配行的 rowid 值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。b 树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，b 树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询
例：
创建B树索引，属于单列索引
create index idx_emp_job on emp(job)
创建B树索引，属于复合索引
create index idx_emp_nameorsal on emp(ename,sal)
创建唯一的B树索引，属于单列索引
create unique index idx_emp_ename on emp(ename)
创建反向索引
create index index_two on emp(mgr) reverse;
如果表已存在大量的数据，需要规划索引段
create index idx_emp_nameorsal on emp(ename,sal) pctfree 30 tablespace users
删除索引
drop index idx_emp_job
drop index idx_emp_nameorsal
drop index idx_emp_ename
6、位图索引
在 B 树索引中，保存的是经排序过的索引列及其对应的 ROWID 值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为 2（只有男和女）。因此，对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的 B 树索引
创建位图索引,单列索引
create bitmap index idx_bm_job on emp(job)
创建位图索引,复合索引
create bitmap index idx_bm_jobordeptno on emp(job,deptno)
删除位图索引
drop index idx_bm_job
drop index idx_bm_jobordeptno
7、函数索引
函数索引既可以使用 B 树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B 树索引，当函数或表达式的结果是固定的几个值时采用位图索引
例：
创建B树类型的函数索引
create index idx_fun_emp_hiredate on emp(to_char(hiredate,\’yyyy-mm-dd\’))
创建位图类型的函数索引
Create bitmap index idx_fun_emp_job on emp(upper(job))
8、合并和重建索引
表在使用一段时间后，由于用户不断对其进行更新操作，而每次对表的更新必然伴随着索引的改变，因此，在索引中会产生大量的碎片，从而降低索引的使用效率。有两种方法可以清理碎片：合并索引和重建索引。合并索引就是将 B 树叶子节点中的存储碎片合并在一起，从而提高存取效率，但这种合并并不会改变索引的物理组织结构重建索引相当于删除原来的索引，然后再创建一个新的索引，因此，CREAT INDEX 语句中的选项同样适用于重建索引。如果在索引列上频繁进行UPDATE 和 DELETE 操作，为了提高空间的利用率，应该定期重建索引
9、管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行 DML 操作后维护索引数据。在新的 SQL 标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则:
1. 小表不需要建立索引
2. 对于大表而言，如果经常查询的记录数目少于表中总记录数目的 15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比
3. 对于大部分列值不重复的列可建立索引
4. 对于基数大的列，适合建立 B 树索引，而对于基数小的列适合建立位图索引
PL/SQL编程
1、概述
是Oracle在标准的SQL语言上的扩展，实现具体的业务功能。pl/sql不仅允许嵌入SQL语言，还可以定义变量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大
2、组成
1）procedural language：过程语言
2）SQL（Struts Query language）：结构化查询语言
当我们执行 PL/SQL块时，请求发送到 ORACLE 服务器，有 PL/SQL 引擎来编译和执行 PL/SQL 块或者子程序，该引擎驻留在 Oracle 服务器中，其中 PL 与 SQL 执行不同分工，过程语句有 PL 语句执行器执行，SQL 语句有 SQL 语句执行器执行
3、PL/SQL块：PL/SQL代码的集合
1）逻辑上相关的声明和语法组合在一起
2）块（block）是PL/SQL的基本程序单元编写 pl/sql 程序实际上就是编写 pl/sql 块。要完成相对简单的应用功能，可能只需要编写一个 pl/sql 块；但是如果要想实现复杂的功能，可能需要在一个 pl/sql 块中嵌套其它的 pl/sql 块
4、PL/SQL块的分类（匿名块、非匿名块）
根据块的定义和使用，我们可以简单将块分为匿名块和非匿名块，匿名块执行时传入到 PL/SQL 引擎即可，之前没有定义，非匿名块主要包括过程，函数，触发器，程序包等，事先已经在服务器定义好，可直接根据名称执行。
注意：在块中不能直接使用 DDL 语句，但可以通过动态 SQL 来解决
5、PL/SQL块的三大机构：声明部分、可执行部分、异常部分
语法：
declare –声明部分（可省略）
变量/常量的定义
begin –可执行部分,由begin开始，end结束，end后必须加分号，实现具体的功能业务
…
exception –异常处理（可省略）
…
end;
PL/SQL编程规范
1、注释
–单行注释
/*多行注释*/
2、标识符的命名规范
定义变量：建议用 v_作为前缀 v_price
定义常量：建议用 c_作为前缀 c_pi
定义游标：建议用_cursor 作为后缀 emp_cursor
定义例外（异常）：建议用 e_作为前缀 e_error
3、各语句必须一分号结束
4、变量名称 数据类型
变量名称 数据类型 not null default 值 –not null 必须指定默认值
变量名称 数据类型 := 值 –定义变量时指定默认值（方式一）
变量名称 数据类型 default 值
注：赋值运算符为：“:=”
5、常量的定义：常量在定义时必须赋初始值
常量名 constant 数据类型 ; –错误
常量名 constant 数据类型 := 常量值
常量名 constant 数据类型 default 常量值
6、给变量赋值
方式一：使用赋值运算符,把具体的数值赋值给变量/常量
变量名称 := 值/表达式 ;
方式二:select into，把查询的数据动态的给变量赋值
select 字段 into 变量名 from 表名 [where 条件];
select 字段1,…,字段n into 变量名1,…,变量n from 表名 [where 条件];
7、&符号：代表由用户根据提示手动输入数据 -> 输入
&提示内容
declare
变量名 varchar2 = \’&提示内容\’ ;
declare
name varchar2(4); –name超过4个字符会出错，解决使用%type或定义足够长的字符
begin
select ename into name from emp where empno=&员工编号;
dbms_output.put_line(\’姓名为:\’||name);
end;
8、%type：定义变量的数据类型和长度与数据表某列的一致
语法：变量 表名.字段%type
declare
name emp.ename%type;
begin
select ename into name from emp where empno=&员工编号;
dbms_output.put_line(\’姓名为:\’||name);
end;
9、%rowtype：返回一个记录类型，其数据类型和数据表的数据结构一致
语法：变量 表名%rowtype
访问数据：
变量.字段
declare
obj emp%rowtype;
begin
select * into obj from emp where empno = 7369;
dbms_output.put_line(\’姓名=\’|| obj.ename);
dbms_output.put_line(\’岗位=\’|| obj.job);
dbms_output.put_line(\’薪水=\’|| obj.sal);
end;
10、异常
zero_divide
case_not_found
…
when 异常种类 then
PL/SQL的数据类型
每个 PL/SQL 变量都具有一个指定存储格式，值的有效范围和约束条件的数据类型， PL/SQL 提 供 了 各 种 内 置 数 据 类 型 ， 包 括varchar2，number，date，recode 引用类型，大数据类型以及用户自定义类型等。PL/SQL 将这些类型分成了 4 个大类，分别为：标量数据类型、LOB 数据类型、组合【复合】数据类型、引用【参照】数据类型
1、标量数据类型
标量类型是非常常用的一种类型，没有内部组件，仅包含单个值，主要包括 number，character,date/time,boolean 类型
语法：
declare
变量 标量数据类型
…
2、LOB数据类型
3、组合数据类型
1）record：用来存储多个值的变量称之为组合或者复合变量，其中存储的多个值可以是 PL/SQL 记录,也可以是 PL/SQL 中的表
declare
type 组合类型名称 is record –创建一个组合类型
(
变量1 数据类型 ,
…
变量n 数据类型
);
age number(3,2) ;
组合类型变量 组合类型名称 ; –定义一个组合类型的变量
begin
select 字段1,…,字段n into 组合类型的变量 from 表名 where … ;
…
end;
不足之处：一次只能存储一条记录的值
2）table
declare
type 组合类型名称 is table of 数据类型 [index by binary_integer];
组合类型变量 组合类型名称;
begin
select 字段 into 组合类型变量(下标1) from 数据表 where …;
…
select 字段 into 组合类型变量(下标2) from 数据表 where …;
end;
注：
下标可以任意的整数（负数，无上下限）
index by binary_integer : 下标自动增长，并不需要每次使用extend增加一个空间
eg1:使用by binary_integer
declare
type my_table is table of emp.ename%type index by binary_integer;
einfo my_table;–不需要初始化
begin
–不必须使用extend增加一个空间且下标可以任意整数
select ename into einfo(-1) from emp where empno=7788;
select ename into einfo(-2) from emp where empno=7900;
select ename into einfo(-3) from emp where empno=7902;
dbms_output.put_line(\’姓名 1:\’||einfo(-1)||\’姓名 2:\’||einfo(-2) ||\’姓名 3:\’||einfo(-3));
end;
eg2:不使用by binary_integer
declare
type my_table is table of emp.ename%type ;
einfo my_table := my_table() ; –必须初始化
begin
einfo.extend; –必须使用extend增加一个空间且下标从1开始
select ename into einfo(1) from emp where empno=7788;
einfo.extend;
select ename into einfo(2) from emp where empno=7900;
einfo.extend;
select ename into einfo(3) from emp where empno=7902;
dbms_output.put_line(\’姓名 1:\’||einfo(1)||\’姓名 2:\’||einfo(2) ||\’姓名 3:\’||einfo(3));
end;
eg3:可以使用bulk collect一次将符合条件的数据全部写入表中
declare
type my_table is table of emp.ename%type index by binary_integer;
einfo my_table;
begin
select ename bulk collect into einfo from emp ;
for i in 1 .. einfo.count –count返回表的记录数
loop
dbms_output.put_line(einfo(i));
end loop;
end;
eg4:record与table组合类型的混合应用
declare
–第一：自定义组合类型 – recod
type myrecord is record (
mname emp.ename%type ,
mjob emp.job%type
) ;
–第一：自定义组合类型 – table
type myType is table of myrecord index by binary_integer ;
–第二：创建组合类型的变量
einfo myType ;
begin
–第二：给组合类型变量赋值
select ename,job into einfo(1) from emp where empno=7369 ;
select ename,job into einfo(2) from emp where empno=7499 ;
select ename,job into einfo(3) from emp where empno=7521 ;
dbms_output.put_line(\’第一个姓名：\’ || einfo(1).mname || \’ 职位\’ || einfo(1).mjob) ;
dbms_output.put_line(\’第二个姓名：\’ || einfo(2).mname || \’ 职位\’ || einfo(2).mjob) ;
dbms_output.put_line(\’第三个姓名：\’ || einfo(3).mname || \’ 职位\’ || einfo(3).mjob) ;
end;
4、预定义例外
1) case_not_found 预定义例外
在开发 pl/sql 块中编写 case 语句时，如果在 when 子句中没有包含必须的条件分支，就会触发 case_not_found 例外。
2) cursor_already_open 预定义例外
当重新打开已经打开的游标时，会隐含的触发 cursor_already_open例外。
3) dup_val_on_index 预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
4) invalid_cursorn 预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
5) invalid_number 预定义例外
当输入的数据有误时，会触发该例外
6) no_data_found 预定义例外
当执行 select into 没有返回行，就会触发该例外
7) too_many_rows 预定义例外
当执行 select into 语句时，如果返回超过了一行，则会触发该例外
8) zero_divide 预定义例外
当执行 2/0 语句时，则会触发该例外
9) value_error 预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外 value_error
PL/SQL控制语句
1、条件分支语句
1. if—then
1 declare 2 3 --声明变量 4 5 v_empno emp.empno%type; 6 7 v_sal emp.sal%type; 8 9 begin 10 11 --根据雇员编号查询工资 12 13 select empno,sal into v_empno,v_sal from emp where empno=&no; 14 15 --如果工资小于2000就加100 16 17 if v_sal<2000 18 19 then 20 21 --工资加100 22 23 update emp set sal = sal+100 where empno=v_empno; 24 25 --提交 26 27 commit; 28 29 end if; 30 31 end;
2. if—then—else
1 declare 2 3 --声明变量 4 5 v_loginname varchar2(10); 6 7 v_password varchar2(10); 8 9 begin 10 11 --从控制台接收数据 12 13 v_loginname := \'&ln\'; 14 15 v_password := \'&pw\'; 16 17 if v_loginname = \'admin\' and v_password = \'123456\' 18 19 then 20 21 dbms_output.put_line(\'用户登录成功！\'); 22 23 else 24 25 dbms_output.put_line(\'用户登录失败！\'); 26 27 end if; 28 29 end;
3. if—then—elsif—else
1 declare 2 3 --声明变量 4 5 v_empno emp.empno%type; 6 7 v_job emp.job%type; 8 9 begin 10 11 --根据雇员编号查询职位 12 13 select empno,job into v_empno,v_job from emp where empno=&no; 14 15 /*如果雇员所属职位是manager工资加1000 16 17 职位是salesman工资加500 18 19 其他职位加200 20 21 */ 22 23 if v_job = \'MANAGER\' then 24 25 --MANAGER职位工资加1000 26 27 update emp set sal = sal+1000 where empno=v_empno; 28 29 elsif v_job = \'SALESMAN\' then 30 31 --SALESMAN职位工资加500 32 33 update emp set sal = sal+500 where empno=v_empno; 34 35 else 36 37 --其他职位工资加200 38 39 update emp set sal = sal+200 where empno=v_empno; 40 41 end if; 42 43 --提交 44 45 commit; 46 47 end;
4. case
1 declare 2 3 --声明变量 4 5 v_mark number(4); 6 7 v_outstr varchar2(40); 8 9 begin 10 11 --从控制台接收成绩 12 13 v_mark := &m; 14 15 case 16 17 when v_mark<=100 and v_mark>=90 then 18 19 v_outstr := \'优秀\'; 20 21 when v_mark<90 and v_mark>=80 then 22 23 v_outstr := \'良好\'; 24 25 when v_mark<80 and v_mark>=70 then 26 27 v_outstr := \'中等\'; 28 29 when v_mark<70 and v_mark>=60 then 30 31 v_outstr := \'及格\'; 32 33 when v_mark<60 and v_mark>=0 then 34 35 v_outstr := \'不及格\'; 36 37 else 38 39 v_outstr := \'成绩输入有误\'; 40 41 end case; 42 43 --控制台输出 44 45 dbms_output.put_line(v_outstr); 46 47 end;
2、循环语句
1. loop
loop 要执行的语句;
exit when <条件语句> /*条件满足，退出循环语句*/
end loop;
其中：exit when 子句是必须的，否则循环将无法停止。
1 declare 2 3 v_num number(4):=1; 4 5 begin 6 7 --从控制台接收数据并插入到account表中 8 9 loop 10 11 insert into account values(v_num,\'&name\'); 12 13 exit when v_num =10; 14 15 v_num :=v_num+1; 16 17 end loop; 18 19 end;
2. while
while <布尔表达式> loop 要执行的语句;end loop;
其中：循环语句执行的顺序是先判断<布尔表达式>的真假，如果为真则循环执行，否则退出循环在 while 循环语句中仍然可以使用 exit 或 exit when 子句
1 declare 2 3 v_num number(4):=1; 4 5 begin 6 7 --从控制台接收数据并插入到account表中 8 9 while v_num<11 10 11 loop 12 13 insert into account values(v_num,\'&name\'); 14 15 v_num :=v_num+1; 16 17 end loop; 18 19 end; 20 21 --处理复杂问题也可以使用嵌套循环，如下，打印九九乘法表 22 23 declare 24 25 v_i int:=1; 26 27 v_j int:=1; 28 29 begin 30 31 while v_i<=9 32 33 loop 34 35 v_j:=1; --每行都从第一列开始打印 36 37 while v_j<=v_i 38 39 loop 40 41 dbms_output.put(v_j||\'*\'||v_i||\'=\'||v_i*v_j||\' \'); 42 43 v_j:=v_j+1; 44 45 end loop; 46 47 v_i:=v_i+1; 48 49 dbms_output.put_line(\'\'); --打印换行 50 51 end loop; 52 53 end;
3. for
for 循环计数器 in [ reverse ] 下限.. 上限 loop 要执行的语句;end loop;
其中：每循环一次，循环变量自动加 1；使用关键字 reverse，循环变量自动减 1跟在 in reverse 后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式可以使用 exit when 子句退出循环
1 --输出1到10的数 2 3 begin 4 5 for i in 1..10 6 7 loop 8 9 --控制台输出 10 11 dbms_output.put_line(i); 12 13 end loop; 14 15 end; 16 17 --输出一组数据 18 19 declare 20 21 --声明表类型 22 23 type emp_table_type is table of varchar2(20) 24 25 index by PLS_INTEGER;--表示表按整数来排序 26 27 v_enames emp_table_type;--定义变量引用表类型 28 29 begin 30 31 --所有值赋予v_enames 32 33 select ename bulk collect into v_enames from emp; 34 35 --输出 36 37 for i in 1..v_enames.count 38 39 loop 40 41 dbms_output.put_line(v_enames(i)); 42 43 end loop; 44 45 end; 46 47 --记录类型可以和表类型结合使用 48 49 --定义一个表类型，要求存储所有的员工姓名和工资 50 51 declare 52 53 --声明记录类型 54 55 type emp_type is record 56 57 ( 58 59 v_name emp.ename%type,--存储姓名 60 61 v_sal emp.sal%type--存储工资 62 63 ); 64 65 --声明表类型 66 67 type emp_table_type is table of emp_type 68 69 index by PLS_INTEGER;--表示表按整数来排序 70 71 v_infos emp_table_type;--定义变量引用表类型 72 73 begin 74 75 --所有值赋予v_enames 76 77 select ename,sal bulk collect into v_infos from emp; 78 79 --输出 80 81 for i in 1..v_infos.count 82 83 loop 84 85 dbms_output.put_line(\'姓名\'||v_infos(i).v_name||\',工资 86 87 \'||v_infos(i).v_sal); 88 89 end loop; 90 91 end;
动态SQL语句
1、概述
在 PL/SQL 块中，可以执行 DML 和 TCL,但是不可以直接执行 DDL 以及 DCL，如果想在块中使用，必须使用动态 SQL。
–在PL/SQL块执行DDL操作
begin
drop table stu ;
end;
2、动态SQL的两种实现（分类）
本地动态 SQL
DBMS_SQL包
3、本地动态SQL
1）语法：
Execute immediate dynamic_sql_string
[into define_variable_list]
[using bind_argument_list]
说明:
dynamic_sql_string:动态执行的SQL语句
define_variable_list:用于接受 select 查询记录值的变量列表
bind_argument_list:绑定输入参数的列表
2）例子
1 --eg1:动态的执行DDL语句 2 3 declare 4 5 str_sql varchar(300) ; 6 7 begin 8 9 str_sql := \'drop table stu\' ; 10 11 execute immediate str_sql; 12 13 end; 14 15 --eg2:接受 select 查询记录值的变量列表 16 17 declare 18 19 str_sql varchar(300) ; 20 21 row_line emp%rowtype ; 22 23 begin 24 25 str_sql := \'select * from emp where empno=\' || &工号 ; 26 27 execute immediate str_sql into row_line; 28 29 dbms_output.put_line(\'姓名：\' || row_line.ename); 30 31 end; 32 33 --eg3:绑定输入参数的列表 34 35 1>设置占位符-> :1、:2、...、:n 36 37 2>动态传入参数 -> using 参数值1,...,参数值n 38 39 create table stu ( 40 41 name varchar2(30) , 42 43 age number(3) , 44 45 sex char(2) 46 47 ) 48 49 50 51 declare 52 53 str_sql varchar2(100) ; 54 55 begin 56 57 str_sql := \'insert into stu values (:1,:2,:3)\' ; 58 59 execute immediate str_sql using \'张三\',18,\'男\'; 60 61 commit; 62 63 end; 64 65 select * from stu ;
4、DBMS_SQL 包执行动态 SQL
1）步骤
第一:构建动态 SQL 语句
第二:打开游标
第三:使用 DBMS_SQL 包的 parse 过程来分析字符串
第四:使用 DBMS_SQL 包的 bind_variable 过程来绑定变量
第五:使用 DBMS_SQL 包的 execute 函数来执行语句并返回受影响的行
第六:关闭游标
2）例子
1 declare 2 3 table_name varchar2(50):=\'&table_name\'; 4 5 str_sql varchar2(500); 6 7 v_cursor number; 8 9 v_row int; 10 11 begin 12 13 --第一：构建动态DDL语句 14 15 str_sql:=\'delete from \' || table_name || \' where name=:1\'; 16 17 --第二：为处理打开游标 18 19 v_cursor:=dbms_sql.open_cursor; 20 21 --第三：分析语句 -- dbms_sql.native指定语句的行为（根据版本） 22 23 dbms_sql.parse(v_cursor,str_sql,dbms_sql.native); 24 25 --第四：绑定变量 26 27 dbms_sql.bind_variable(v_cursor,\':1\',\'张三\'); 28 29 --第五：执行语句[DDL语句，该操作可以省略] 30 31 v_row:=dbms_sql.execute(v_cursor); 32 33 --第六：关闭游标 34 35 dbms_sql.close_cursor(v_cursor); 36 37 dbms_output.put_line(\'删除表中的数据，受影响行为:\' || v_row); 38 39 end;
异常处理
一个 PL/SQL 程序，如果出现异常而没有解决，程序会自动终止整个程序的运行，所以一个优秀的程序都应该能够处理各种出现的问题，并尽可能的恢复
1、预定义异常
Oracle 把常见异常预先定义好，放在系统内部，并为每一种异常定义一个编号，为其命名，这类异常属于预定义异常。这类异常在程序出现这些问题时自动触发，并寻求解决办法，如果没有处理，就向系统抛出异常，终止整个程序。【常见预定义异常在文档前面部分由详细说明】
1 declare 2 3 v_name varchar2(30); 4 5 begin 6 7 select ename into v_name from emp; --where empno=73691; 8 9 dbms_output.put_line(\'姓名：\' || v_name) ; 10 11 exception 12 13 when TOO_MANY_ROWS then 14 15 dbms_output.put_line(\'返回多行\'); 16 17 when no_data_found then --可以有多个 when 18 19 dbms_output.put_line(\'没有查询到数据\'); 20 21 when others then --可选 22 23 dbms_output.put_line(\'未知异常,错误号\'||sqlcode||\',错误信息\'||sqlerrm); 24 25 end;
2、非预定异常
在处理异常时，我们尽可能的使用系统预定义异常，但是如果没有定义好的，我们可以使用 others，但是如果都采用 others，哪任何异常都将采用同一种处理方式，不利于异常的有效处理，此时我们可以采用非预定义异常来处理这类问题。如果要处理系统内部未命名的异常，可以使用 Pragma exception_init 指令，Pragma指令由编译器控制，是对编译器的注释，Pragma 在编译时处理，而非运行时，它可以将异常名和 Oracle 错误码结合起来使用，这样就可以通过名字引用任意的内部异常
1 declare 2 3 v_i int; 4 5 my_math exception; --定义一个异常名 6 7 pragma exception_init(my_math,-1476); --将异常名与 Oracle 错误码绑定 8 9 begin 10 11 v_i := 10/0; --将出现异常 12 13 exception 14 15 --when my_math then --除数为 0 异常将被处理 16 17 --dbms_output.put_line(\'除数不能为 0！ \'); 18 19 when others then 20 21 dbms_output.put_line(\'未知异常,错误号\'||sqlcode||\',错误信息\'||sqlerrm); 22 23 end;
3、用户定义异常
Oracle 除了系统定义的异常外，还执行用户自定义异常，程序人员可以在一些不会引发系统内部异常的程序中手动引发自定义异常，然后交由 Oracle 异常处理机制进行处理
eg1:
1 declare 2 3 myexception exception; --定义一个异常名 4 5 age int; 6 7 begin 8 9 age:=&age; 10 11 12 13 if age<18 or age>36 then 14 15 raise myexception; --手动抛出异常 16 17 else 18 19 dbms_output.put_line(\'您输入的年龄是\' || age); 20 21 end if; 22 23 exception 24 25 when myexception then --处理异常 26 27 dbms_output.put_line(\'年龄不符合标准\'); 28 29 end;
eg2:抛出应用程序异常：raise_application_error
1 declare 2 3 age int; 4 5 begin 6 7 age:=&age; 8 9 if age<18 or age>36 then 10 11 raise_application_error(-20001,\'你输入的数据\'||age||\'超出了合适的范围!\'); --手动抛出系统异常 12 13 else 14 15 dbms_output.put_line(\'您输入的年龄是\'|| age); 16 17 end if; 18 19 end;
eg3:
1 declare 2 3 age int; 4 5 myexeption exception;--定义异常名 6 7 pragma exception_init(myexeption,-20001);--将异常名与系统错误号绑定 8 9 begin 10 11 age:=&age; 12 13 if age<18 or age>36 then 14 15 raise_application_error(-20001,\'你输入的数据\'||age||\'超出了合适的范围!\'); --手动抛出系统异常 16 17 else 18 19 dbms_output.put_line(\'您输入的年龄是\'|| age); 20 21 end if; 22 23 24 25 exception 26 27 when myexeption then 28 29 --在自定义异常时，函数 sqlcode 与 sqlerrm 可以用来显示错误号与错误信息 30 31 dbms_output.put_line(\'出错了,错误号:\'||sqlcode||\',错误信息:\'||sqlerrm); 32 33 end;