一、Oracle介绍
ORACLE数据库系统是美国ORACLE公司提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S（Browser/Server）体系结构的数据库之一。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。
二、Oracle10g安装
1.安装
解压Oracle数据库安装包，如果是win7或者win8系统右键点击setup.exe选择兼容性，以xp方式，并且以管理员方式运行，以及其他所有用户都按着此规则如图。
双击setup.exe，输入口令和确认口令，点击下一步，出现进度条。注：此口令即是管理员密码。检查先决条件，选中红框所示的选择框，如下图：
点击“下一步”，出现“概要”界面，点击“安装”。出现安装进度条，等待安装完成。安装完成后，自动运行配置向导，等待其完成。完成后，出现“口令管理”界面，点击“口令管理”，如下图：
将SCOTT和HR用户的√去掉（解锁这两个账户），Scott密码设置为tiger，hr密码设置为hr，然后点击“确定”。回到“口令管理”界面，点击“确定”，安装结束，点击“退出”。
2.登录
运行cmd进入命令行：sqlplus 用户名/密码@ip地址:1521/orcl [as sysdba]，加上as sysdba可以用系统管理员身份登录。登录普通用户不需要加as sysdba。在远程电脑上，因为没有安装oracle，所以如果直接输入上面的命令是没用的，需要安装一下oracleInstanceClient，并且添加到系统变量中去。
在远程主机上测试：C:\Users\HP>sqlplus scott/tiger@192.168.146.128:1521/orcl
在本地主机上测试：C:\Users\HP>sqlplus scott/tiger
3.查看用户
show user
4.用户切换
在登录的状态下输入：conn 用户名/密码 [as sysdba]
5.查看用户下的表
为了防止命令行中数据展示表格杂乱的情况可以设计行宽和列宽
set linesize 300 每行展示300个字符
col 列名 for a[数字] 某一列占几个字符（for是format的缩写）
在用户登录的情况下输入：select * from tab;
查看表中的数据，先设置好列宽，再查询数据。
Col ename for a8
Col mgr for 9999
Col sal for 9999
Select * from emp
如果使用dba用户登录查询该表无法查看到，因为sys用户下没有这个emp表，但是由于超级管理员的权限很大，可以查看Scott用户下的表：select * from scott.emp;
6.查看表的结构
Desc 表名
| Number(4) | 最大长度为4为的数值类型 |
| Varchar2(10) | 最大长度为10的字符串，varchar2用于存储可变长度的字符串，varchar2把所有字符都占两字节处理（一般情况下），varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节，VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；建议在Oracle中使用varchar2。 |
| Number(7,2) | 数值类型整数位占5位，小数位占2位，一共7位。 |
| Date | 时间类型 |
7.PLSQL Developer安装
傻瓜式安装即可，不建议汉化，最后会提示输入序列号，打开plSQL-sn.txt复制序列号，输入即可。重要提示：不要把plSQL developer安装在有括号的目录下，否则会出现连不上数据库的情况，安装目录也不要出现中文。
PlSQL developer是最好用也是公司里面大量使用的数据库图形化工具，功能十分强大，在开发人员中广受欢迎，基本上是使用Oracle数据库的必备神器。Oracle支持命令行方式操作，在实际的开发中很少使用命令行，绝大多数使用图形化的工具PlSQL developer。
三、Oracle体系结构
Oracle DataBase是一款关系型数据库管理系统，同类的产品还有mySQL，SQLServer等，很多时候，会把承载核心数据的系统笼统地成为数据库服务器，但从严格意义上来讲Oracle DataBase是由两个部分组成：
实例：实例是数据库启动时初始化的一组进程和内存结构。
数据库：数据库则指的是用户存储数据的一些物理文件。
从实例和数据库的概念上来看，实例暂时的，它不过是一组逻辑划分的内存结构和进程结构，它会随着数据库的关闭而消失，而数据库它其实就是一堆物理文件（控制文件，数据文件，日志文件等等），它是永久存在的（除非磁盘损坏）。数据库和实例通常是一对一的，这种结构我们成为单实例体系结构；当然还有一些复杂的分布式的结构，一个数据库可以对多个实例，像Oracle的RAC。
下面的图描述了单实例体系结构大致的交互流程：
1.用户和用户进程交互
用户进程可以是一般的客户端软件，像Oracle的SQLplus，SQL developer,或者是一些驱动程序等等都属于用户进程。
2.用户进程和服务器进程交互
服务器进程有时会称为前台进程，当然是相对于后台进程（后面会提到的数据库写入器，日志写入器等）来说的，服务器进程的主要作用就是处理连接到当前实例的用户进程的请求，对客户端发来的SQL进行执行并返回执行结果。在专有服务器结构中，用户进程和服务器进程是一对一的，也就是说，当监听程序监听到客户端来了一个请求，会为其分配一个对应的服务器进程。还有一种结构为共享服务器，这种结构就不是一个用户进程对应一个服务器进程了，会通过调度程序进行协调处理。
3.服务器进程和实例进程交互
4.实例和数据库进程交互
1.概念
数据库：
Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。
实例：
一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。Oracle的表文件，读到内存中就称为实例。Oracle的集群如下所示。
用户：
用户是在实例下建立的。不同实例可以建相同名字的用户。
表空间：
表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。
数据文件（dbf、ora）:
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。
由于Oracle的数据库不是普通的概念，Oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！
2.创建表空间
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。
create tablespace itcast001
datafile 'c:\itcast001.dbf'
size 100m
autoextend on
next 10mitcast 为表空间名称
datafile 指定表空间对应的数据文件
size 后定义的是表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 后指定的是一次自动增长的大小。
3.创建用户
1）创建用户
create user itcastuser
identified by itcast
default tablespace itcast001identified by 后边是用户的密码
default tablespace 后边是表空间名称
Oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。
2）用户赋权限
新创建的用户没有任何权限，登陆后会提示错误。
4.三个角色
Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。
CONNECT角色是授予最终用户的典型权利，最基本的。
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图RESOURCE角色是授予开发人员的。
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型DBA角色拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除。
grant dba to itcastuser进入system用户下给用户赋予dba权限，否则无法正常登陆。
5.中文乱码问题
查看服务器端编码
select userenv('language') from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
执行语句 select * from V$NLS_PARAMETERS
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是，需要设置环境变量。否则PLSQL客户端使用的编码和服务器端编码不一致，插入中文时就会出现乱码。
设置环境变量：计算机->属性->高级系统设置->环境变量->新建
设置变量名：NLS_LANG,变量值:第1步查到的值，我的是：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
重新启动PLSQL，插入数据正常。
四、数据类型
| No | 数据类型 | 描述 |
| 1 | Varchar， varchar2 | 表示一个字符串 |
| 2 | NUMBER | NUMBER(n)表示一个整数，长度是n NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n |
| 3 | DATE | 表示日期类型 |
| 4 | CLOB | 大对象，表示大文本数据类型，可存4G |
| 5 | BLOB | 大对象，表示二进制数据，可存4G |
五、表的管理
1.创建表
方式一：
create table 表名（
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
...
字段n 数据类型 [default 默认值]
）;
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday) values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));方式二：
create table emp1 as select * from emp where 1=2;因为1不等于2所以这种写法只会拷贝表结构。2.表删除
语法：DROP TABLE 表名
表没有真正删除，只是标记被删除，相当于放入回收站。
show recyclebin;查看回收站
purge recyclebin;清空回收站，将会真正删除表查找原来的表，会显示错误，但是按照回收站中表的编号查找就可以查到数据，编号要加双引号。
闪回删除：flashback table 表名 to before drop;恢复删除的表。管理员没有闪回删除的操作，所以要特别小心。
3.表修改
添加列：ALTER TABLE 表名称 ADD(列名 类型 [DEFAULT 默认值]，列名 类型 [DEFAULT 默认值]...)
修改列的值类型：ALTER TABLE 表名称 MODIFY(列名 类型 [DEFAULT 默认值]，列名 类型 [DEFAULT 默认值]...)
修改列名：ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2
范例：在person表中增加列address
alter table person add(address varchar2(10));
范例：把person表的address列的长度修改成20长度
alter table person modify(address varchar2(20));
删除列：alter table person drop column address ;
4.截断表
在person表中使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源，需要使用截断表的语法。
truncate table person;
5.更新
1）INSERT
INSERT INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)
标准写法：
insert into person(pid,name,gender,birthday,address) values(1,'张三',1,'9-5月-1981','北京家');
简单写法：
insert into person values(1,'张三',1,'9-5月-1981','北京北七家');
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null。
insert into person values(2,'李四',1,null,'北京育新');
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal.&deptno);这样可以之后再赋值。
一次插入多条数据：
insert into emp2 select * from emp where deptno =10;
2）事务
Oracle是自动开启事务。Oracle的事务是对数据库的变更的处理，必须做提交事务才能让数据真正的插入到数据库中。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback
Oracle中事务的标志：
1.起始标志：事务中第一条DML语句
2.结束标志：提交：显式 commit，隐式 正常退出exit，DDL，DCL
回滚：显式 rollback，隐式 非正常退出，掉电，宕机
3）事务保存点与回滚
语法：savepoint保存点名字
savepoint a1; --设置一个保存点 a1;
update person set name='张三' where pid='101' --修改一条数据
savepoint a2; --设置一个保存点 a1;
update tmp set name='李四' where id='102 --再修改一条数据
rollback to a2; --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。
rollback to a1; --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。
rollback; --回滚全部。即撤消至上一次提交事务后的所有数据修改。
commit; --提交事务 将回滚后的事务提交，并会删除所有保存点。注意：我们可以从a2向前再回滚到a1 ，但无法从a1回滚到a2。也就是只能向前回滚，不能从前面的点再向后回滚！
4）事务的隔离级别
待更新
5）UPDATE
全部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....
局部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；
6）DELETE
语法: DELETE FROM 表名WHERE 删除条件;
在删除语句中如果不指定删除条件的话就会删除所有的数据
比较truncat与delete实现数据删除
1.delete逐条删除，truncate先摧毁表再重建
2.delete是DML（可以回滚），truncate是DDL（不可以回滚）
3.delete不会释放空间，truncate会
4.delete可以闪回（flashback），truncate不可以
5.delete会产生碎片（数据插入会一直叠加，不会插入到删除的位置，执行alter table 表名 move，可以清除碎片），truncate不会
6.delete的删除速度比truncate快。因为delete只是标记删除，并没有真正删除。
六、约束
在数据库开发中，约束是必不可少，使用约束可以更好的保证数据的完整性。
1.主键约束
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。
创建一张表，把pid作为主键
create table person(
pid number(10) primary key,
name varchar2(10),
gender number(1) default 1,
birthday date
);主键不可重复，主键不可为空。我们可以自己来指定主键约束的名字
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date,
constraint person_pk_pid primary key(pid))person_pk_pid为自己起的名字，当违反唯一约束时，将会弹出：ORA-00001: 违反唯一约束条件 (SCOTT. person_pk_pid)
2.非空约束
使用非空约束，可以使指定的字段不可以为空。
范例：建立一张pid和name不可以为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) ,
birthday date,
);3.唯一约束
表中的一个字段的内容是唯一的。
范例：建表一个name是唯一的表
create table person(
pid number(10) ,
name varchar2(10) unique,
gender number(1) ,
birthday date
);唯一约束的名字也可以自定义
Create table person (
PID number(10) ,
name varchar2(10),
gender number(1) ,
birthday date,
constraint person_name_uk unique(name)
);4.检查约束
使用检查约束可以约束字段值的合法范围。
范例：创建一张表性别只能是1或2
create table person(
pid number(10) ,
name varchar2(10),
gender number(1) check(gender in (1, 2)),
birthday date
);检查约束也可以自定义名称
create table person(
pid number(10) ,
name varchar2(10),
gender number(1),
birthday date,
constraint person_gender_ck check(gender in (1,2))
);5.外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。
范例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id)
);insert into orders values(1, 200, to_date('2015-12-12','yyyy-MM-dd'));
insert into order_detail values(1, 2, 'java',1);我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。
我们再次建表
create table orders(
order_id number(10) ,
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
);create table order_detail(
detail_id number(10) ,
order_id number(10),
item_name varchar2(10),
quantity number(10),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);外键关联一定注意：外键一定是主表的主键；删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题，但是可以强制删除drop table orders cascade constraint;(不建议)
删除主表的数据可以先删除子表的关联数据，再删主表，也可以使用级联删除。级联删除在外键约束上要加上on delete cascade 如：
constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id) on delete cascade
这样删除主表数据的时候会把字表的关联数据一同删除。
七、SQL查询语句
1.SQL简介
结构化查询语言(Structured Query Language)简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
DML(数据库操作语言): 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
DDL(数据库定义语言): 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表，为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
DCL(数据库控制语言)：确定单个用户和用户组对数据库对象的访问权限。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
2.SQL优化查询
1.尽量使用列名，因为用*需要一定的解析时间。
2.SQL的where解析，从右往左，所以尽量把可以很快排除的条件放在最后面。
3.尽量使用where，不使用having。因为where是先分组再过滤。
4.尽量使用多表查询，在子查询中，多数都会转化为多表查询。（执行计划）
5.尽量使用unin all，因为union=distinct+union all。
6.尽量不要使用集合运算
3.简单查询
1）查询语法
Select * |列名 from 表名2）别名用法
在查询的结果列中可以使用别名
Select 列名 别名，列名 别名，... from emp;
这里要注意，别名可以用双引号，也可以不用双引号，区别在于，双引号之间可以出现关键字，而如果没用双引号就不能出现关键字。
3）修改语句
两种方法。
1.使用c，首先定位到错误行，然后用c(change)关键字，最后重新执行 /
2.输入ed，然后修改内容，最后重新执行/
4）空值问题
1.包含null的表达式结果都是null。
2.null永远不等于null，要查询null的时候，要用is null来判断。
3.nvl(comm,0)采用虑空函数，将是空值的一项置为数字0，这样才能做运算。
4.如果集合中含有null，不能使用not in，但是可以使用in。
5.排序中如果有空值，升序没问题，但是在降序中desc后面需要加nulls last。
5）消除重复的数据
Select distinct *|列名, ... from emp;
使用distinct可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复
6）字符串连接查询
字符串的连接使用‘||’，或者concat。
select concat(concat(empno,'的工作名称是'),job) from emp;
select empno||'的工作名称是'||job from emp;注意这里是单引号，双引号代表列的别名。
7）查询中四则运算
查询每个雇员的年薪
select ename, sal*12 from emp;
select ename, sal*12 income from emp;SQL中支持四则运算“+，-，*，/”。
4. 条件查询
语法：select *|列名 from 表名 where 条件
1）非空和空的限制
示例：查询每月能得到奖金的雇员
分析：只要字段中存在内容表示不为空，如果不存在内容就是null。
语法：列名 IS NOT NULL
select * from emp where comm is not null;
范例：查询工资大于1500并且有奖金领取的雇员
分析：多个查询条件同时满足之间使用‘AND’
select * from emp where comm is not null and sal >1500;
范例:查询工资大于1500或者有奖金的雇员
分析：多个查询条件或满足，条件之间使用“OR”
select * from emp where comm is not null or sal >1500;
范例：查询工资不大于1500和没有奖金的人
语法：NOT(查询条件)
select * from emp where comm is null and not(sal>1500);
2）范围限制
范例：基本工资大于1500但是小于3000的全部雇员
分析：Between and等于 sal > =1500 and sal <= 3000
select * from emp where sal between 1500 and 3000;
范例：查询1981-1-1到1981-12-31号入职的雇员
分析：between and 不仅可以使用在数值之间，也可以用在日期的区间
select * from emp where hiredate between '1-1月-1981' and '31-12月-1981';
范例：查询雇员名字叫smith的雇员
分析：在Oracle中的查询条件中查询条件的值是区分大小写的
select * from emp where ename=’SMITH’;
范例:查询雇员编号是7369,7499,7521的雇员编号的具体信息
如果使用之前的做法可以使用OR关键字，实际上，此时指定了查询范围，那么SQL可以使用IN关键字，语法: 列名 IN (值1，值2，....)，其中的值不仅可以是数值类型也可以是字符串
select * from emp where empno in(7369,7499,7521);
范例:查询雇员姓名是’SMITH’,’ALLEN’,’WARD’的雇员具体信息
select * from emp where ename in('SMITH','ALLEN','WARD');
3）模糊查询
在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内容全部的查询出来，在SQL中使用LIKE语句完成。
在LIKE中主要使用以下两种通配符：
“%”：可以匹配任意长度的内容
“_”:可以匹配一个长度的内容
范例：查询出所有雇员姓名中第二个字符包含“M”的雇员
select * from emp where ename like '_M%';
在LIKE中如果没有关键字表示查询全部
select * from emp where ename like '% %';
查询名字中带有“M”的雇员
select * from emp where ename like '%M%';
在Oracle中不等号的用法可以有两种形式“<>”和“!=”
范例：查询雇员编号不是7369的雇员信息
select * from emp where empno <> 7369;
查询含有下划线的名字，此时需要用到转义字符。
select * from emp where ename like '\_%';
4）日期查询
select * from v$nls_parameters;从数据字典中，查询日期的格式
alter session set nls_date_format=’yyyy-mm-dd’;将日期格式进行更改，这种方式是临时修改
如果要修改系统的设置，需要先登陆sys用户，session关键字要修改为system。之后查询就按照设置的格式来。
5.对结果集排序
1）排序的语法
SELECT * |列名 FROM 表名 {WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC
范例：查询雇员的工资从低到高
分析：ORDER BY 列名 默认的排序规则是升序排列，如果按着降序排列必须指定DESC，order by 也可以加数字，代表第几列。如果存在多个排序字段可以用逗号分隔。
select * from emp order by job desc,deptno desc;
注意ORDER BY语句要放在SQL的最后执行。
2）排序中的空值问题
当排序时存在null时就会产生问题，解决办法：设定空值的位置，nulls first , nulls last。
--查询雇员的工资从低到高
select * from emp order by sal nulls first;
select * from emp order by sal desc nulls last ;
八、函数
1.字符函数
接收字符输入，返回字符或者数值。
把小写的字符转换成大小的字符：upper('smith')
把大写字符变成小写字符：lower('SMITH')
把首字符大写：initcap('smith')
字符串的连接可以使用concat，也可以使用“||”，建议使用“||”：concat('hello', 'world')
字符串的截取，使用substr，第一个参数是源字符串，第二个参数是开始索引，第三个参数长度，开始的索引使用1和0效果相同：substr('hello', 1,3)
获取字符串的长度：length('hello')
字符串替换，第一个参数是源字符串，第二个参数被替换的字符串，第三个是替换字符串：replace('hello', 'l','x')
左填充lpad，右填充rpad：lpad(‘abc’,10,’*’)
去掉制定的字符：trim(‘h’ from ‘heello’)
2.数值函数
四舍五入函数：ROUND()，默认情况下ROUND四舍五入取整，可以自己指定保留的位数。这里也可以对日期进行四舍五入，括号的第二个参数指定是对年还是月等等。
SQL> select round(12.53343,2) from dual; dual是伪表，没有什么意义。
取整：TRUNC()，默认全部去掉小数，也可以指定保留的位数。
取余数MOD()：select mod(10,3) from dual;
3.日期函数
| 函数 | 描述 |
| MOTHS_BETWEEN | 两个日期相差的月数 |
| ADD_MONTHS | 向指定日期中加上若干月数 |
| NEXT_DAY | 指定日期的下一个日期 |
| LAST_DAY | 本月的最后一天 |
| ROUND | 日期四舍五入 |
| TRUNC | 日期截断 |
Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律：
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数
select ename, round(sysdate-hiredate)/7 from emp;
获得两个时间段中的月数：MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数
select ename, round(MONTHS_BETWEEN(sysdate,hiredate)) from emp;
获得几个月后的日期：ADD_MONTHS()
范例：求出三个月后的日期
select ename, round(ADD_MONTHS (sysdate,3)) from emp;
4.转换函数
TO_CHAR：字符串转换函数
年：y，年是四位使用yyyy
月：m，月是两位使用mm
日：d，日是两位使用dd
星期几：day
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
在结果中10以下的月前面被补了前导零，可以使用fm去掉前导零
select to_char(sysdate,'fmyyyy-mm-dd') from dual;
1.TO_CHAR还可以给数字做格式化
范例：把雇员的工资按三位用“,”分隔，在Oracle中“9”代表一位数字
select ename,to_char(sal,’L99,9’) from emp;
如果在钱的前面加上国家的符号可以使用“$”代表是美元，如果要使用本地的钱的单位使用“L”
2.TO_NUMBER:数值转换函数，可以把字符串转换成数值
select to_number('19') from dual;
3.TO_DATE:日期转换函数，可以把字符串的数据转换成日期类型
select to_date('1983-04-22','yyyy-mm-dd') from dual;
5.通用函数
NVL(expr1,expr2)
NVL2(expr1,expr2,expr3)，expr1是非空时，返回第二参数，否则返回第三参数
NULLIF(expr1,expr2)
COALESCE(expr1,expr2,…,exprn)
以上这些适用于任何数据类型，也适用于空值。
nullif(a,b)如果a的值等于b，返回null，否则返回a。有一个实际的应用，例如防止除0操作的放生，可以使用a/NULLIF(b,0)，这样就不怕b是0了，当然除0操作也可以通过别的方式判断。
coalesce从左往右找到第一个不为空的值。
6.空值处理nvl
范例：查询所有的雇员的年薪
select ename,sal*12+nvl2(comm,comm,0) from emp;当不为空时候返回第二个参数，否则返回第三个参数。
7.decode
语法：DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果col/expression和Searchi匹配就返回resulti，否则返回default的默认值。
select decode(2,1,'w1',2,'w2','o') from dual;
范例：查询出所有雇员的职位的中文名
8.case when then
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
select t.empno,t.ename,
case
when t.job = 'CLERK' then '业务员'
when t.job = 'MANAGER' then '经理'
when t.job = 'ANALYST' then '分析员'
when t.job = 'PRESIDENT' then '总裁'
when t.job = 'SALESMAN' then '销售'
else '无业'
end
from emp t
9.聚合函数
聚合函数也叫分组函数，作用于一组数据并且返回一个值。
1）统计记录数count()
范例：查询出所有员工的记录数
select count(*) from emp;
不建议使用count(*)，可以使用一个具体的列以免影响性能。
2）最小值查询min()
范例：查询出来员工最低工资
select min(sal) from emp;
3）最大值查询max()
范例：查询出员工的最高工资
select max(sal) from emp;
4）查询平均值avg()
范例：查询出员工的平均工资
select avg(sal) from emp;
avg无法对空值进行处理，相当于count(comm)，如果comm中包含空值，那么就不计入其中，所以必须用count（*）计算总的人数。
5)求和函数sum()
范例：查询出20号部门的员工的工资总和
select count(sal) from emp where emptno = 20;
10.分组数据group by
group by
语法：select a,b,c,组函数（x） from table group by a,b,c;
select deptno,avg(sal) from emp group by deptno;
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
break on deptno skip 2;表示设置成不同的部门号只显示一次，每个部门之间跳过两行。
如果想取消，可以break on null。
11.过滤分组数据having
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;
where后面不能使用组函数，但是having可以。如果没有组函数则两个可以通用，但是where必须写在group by前面。
select deptno,avg(sal) from emp group by deptno having deptno ='10';
九、集合操作
1.并集
UNION=union all + distinct，即相同的地方只取一次
UNION ALL，将全部的结果进行展示，不论是否重复。尽量使用union all。
select * from emp where deptno = 10
union all
select * from emp where deptno =20;
select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp;
set timing on
等同于之前的select deptno,job,sum(sal) from emp group by rollup(deptno,job);
2.交集
INTERSECT
显示薪水同时位于700-1300和1200到1400的员工
select ename,sal from emp where sal in(700,1300)
intersect
select ename,sal from emp where sal in(1200,1400);
3.差集
MINUS
显示薪水位于700-1300但是不属于1200到1400的员工
select ename,sal from emp where sal in(700,1300)
minus
select ename,sal from emp where sal in(1200,1400);
4.注意事项总结
1.参与运算的各个集合必须列数相同且类型一样。
2.采用第一个集合作为最后的表头
3.order by 在最后
十、多表查询
多表查询其实就是多张表根据查询条件进行拼接，有四种。
等值连接（含等号）、不等值连接（不含等号，可能是大于或者小于，也可以是一个区间）、自连接、外连接
1.笛卡尔积
SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名1 别名 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}
范例：查询员工表和部门表
select * from emp,dept;
发现产生的记录数是56条，还会发现emp表是14条，dept表是4条，56正是emp表和dept表的记录数的乘积，称其为笛卡尔积。如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。
2.关联查询
在两张表中发现有一个共同的字段是depno，depno就是两张表的关联的字段，可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。
select * from emp,dept where emp.deptno = dept.deptno;
关联之后我们发现数据条数是14条，不在是56条。我们可以为每一张表起一个别名。
select * from emp e,dept d where e.deptno = d.deptno;
3.等值连接
范例：查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
范例：查询出每个员工的上级领导
分析：emp表中的mgr字段是当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，可以使用mgr字段和empno来关联
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
范例：在上一个例子的基础上查询该员工的部门名称
分析：只要在上一个例子基础上再加一张表的关联，使用deptno来做关联字段即可
select e1.empno,e1.ename,e2.empno,e2.ename,d.dname from emp e1,emp e2,dept d where e1.mgr = e2.empno and e1.deptno = d.deptno;
4.不等值连接
范例：查询每个员工部门号，姓名，工资等级
select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;范例：查询出每个员工编号，姓名，工资等级，部门名称，他的上级领导的姓名，工资等级
select e.empno,e.ename,
decode(s.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') grade,
d.dname,
e1.empno,
e1.ename,
decode(s1.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') grade
from emp e, emp e1, dept d, salgrade s, salgrade s1
where e.mgr = e1.empno
and e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal5.外连接
1）基本查询的问题
范例：查询部门号，部门名称，人数
select d.deptno,d.dname,count(*)
from emp e,dept d
where e.deptno = d.deptno group by d.deptno,d.dname右连接：在做基本连接查询的时候，查询出所有的部门下的员工，编号为40的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的。
2）左右连接
当希望把40号部门即使没有记录也显示出来就需要进行左右连接。
使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是右连接，如果是在右边表的关联条件字段上就是左连接。可以简单的理解为，当哪个表格的字段必须要出现时，就在另外一张表的约束条件加上(+)。因此上面的例子，正确的查询为如下。
select d.deptno,d.dname,count(*)
from emp e,dept d
where e.deptno(+) = d.deptno group by d.deptno,d.dname6.自连接
一个表和自己相连接。
范例：查询出所有员工的上级领导
分析：我们发现使用我们以前的做法发现KING的上级领导没有被展示（虽然King没有上级领导），我们需要使用左右连接把他查询出来。
select e.ename,e1.ename from emp e,emp e1 where e.mgr = e1.empno(+);
7.层次查询
只有一张表，不会产生笛卡儿积。
格式：
select level(伪列)
from emp
connect by prior ***（上一层的值=当前level的值）
start with 起始条件，从这个开始往下遍历
select level,empno,ename,mgr
from emp
connect by prior empno = mgr
start with mgr is null
order by 1十一、子查询
1.子查询
在一个查询的内部还包括另一个查询，则此查询称为子查询。SQL的任何位置都可以加入子查询。
范例：查询比scott工资高的雇员
分析：查询出scott员工的工资是多少，把它作为条件
select * from emp where sal >(select sal from emp where ename='SCOTT');
范例：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工
select * from emp e where e.sal>(select sal from emp where empno=7654) and e.job=(select job from emp where empno=7788);
范例：要求查询每个部门的最低工资和最低工资的雇员和部门名称
select e1.ename, d.dname,e.minsal
from (select min(sal) minsal,deptno from emp group by deptno) e,dept d,emp e1
where e1.deptno = d.deptno and e1.sal =e.minsal
2.注意问题
1）合理的书写风格
2）所有的子查询必须在“（）”中编写
3）可以在where select having from 后使用子查询
select ：
select ename,empno,(select job from emp where empno =7369) from emp;这里需要注意select后必须是单行子查询，即返回的结果只能为一条记录（多行查询即返回的记录为多条），否则会报错。这是因为oracle是关系型数据库，只能按照行和列进行存储，在一个单元上不能存储多条数据。
having：
select deptno,min(sal) from emp group by deptno
having min(sal) >(select min(sal)
from emp where deptno =20)
from：
select * from (select empno,ename,sal from emp);
4）不可以在group by后面
5）很多都是通过from后面的子查询来解决问题
6）主查询和自查询可以是不同表，只要子查询返回的结果主查询可以使用。
7）一般不在子查询中排序，但是在top_n中必须排序
8）一般先执行子查询，当相关子查询例外
9）单行子查询只能使用单行操作符，多行子查询自能使用多行。
all、in、any
in:
select * from emp
where deptno in (select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING')
any:任意一个
select * from emp
where sal >any (select sal from emp where deptno = 30)
all:所有
select * from emp
where sal >all (select sal from emp where deptno = 30)
10）子查询中的null
查询非老板的员工
select * from emp where empno not in (select mgr from emp);
但是结果没有记录，因为子查询中存在null，而null不能在not in 中。需要修改为
select * from emp where empno not in (select mgr from emp where mgr is not null);
3.相关子查询
范例：找到工资大于本部门平均工资的员工。
select e.empno,e.ename,e.sal,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal;
一般情况下都是先执行子查询，再执行主查询，相关子查询将主查询中的值作为参数传递给子查询。
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);
范例：统计每年入职的员工数
select count(*) Total,
sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp;
4.rownum
找到排名前三的工资
1）rownum永远按照默认的顺序生成，即使按照其他列进行排序，这个数字还是会跟着原来的行，不会自动更新。如果要更新就可以使用子查询。
2）rownum只能使用< <=;不能使用> >=
select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<4 ;
3）rownum永远从1开始
范例：查询月薪第5-8名的员工
select * from (select rownum r,e1.* from (select * from emp order by sal) e1 where rownum <=8) e2 where r >=5;
5.临时表
1.手动: create global temporary table *****
2.自动：排序
特点：当事务或者会话结束的时候，表中数据自动删除
基于事务的临时表：
create global temporary table temptest1
(tid number,tname varchar2(20))
on commit delete rows;
基于会话的临时表：
create global temporary table temptest1
(tid number,tname varchar2(20))
on commit preserve rows;
6.exists和not exists关键字
exists （SQL 返回结果集为真）
not exists (SQL 不返回结果集为真）
范例：查询出有员工的部门有哪些
select * from dept d where d.deptno in(select distinct deptno from emp)
范例：查出没有员工的部门
select * from dept d where not exists(select * from emp e where e.deptno= d.deptno)
7.Insert中加入子查询
语法：
Insert into 表名(列1,列2，列3,….)
Select 列1,列2，列3,….
From 表名
Where条件
注意:
1. 此时不要写values
2. 插入的列名与查询的列名要一致
8.Update与子查询
待更新
9.Delete与子查询
待更新
十二、PL/SQL
PL/SQL（Procedure Language/SQL），是Oracle对SQL语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。
范例1：为职工涨工资，每人涨10％的工资。
update emp set sal=sal*1.1;
范例2：按职工的职称长工资,总裁涨1000元,经理涨800元，其他人员涨400元。
这样的需求我们就无法使用一条SQL来实现，需要借助其他程序来帮助完成。
1.pl/SQL程序语法
首先set serveroutput on，打开输出开关，这样执行语句才能看到结果。
程序语法：
declare
说明部分 （变量说明，游标申明，例外说明）
begin
语句序列 （DML语句）
exception
例外处理语句
End;
2.常量和变量定义
在程序的声明阶段可以来定义常量和变量。变量的基本类型就是ORACLE中的建表时字段的变量，如char, varchar2, date, number, boolean, long。
定义语法：varl char(15);
psal number(9,2);
常量定义：married constant boolean:=true
引用变量：myname emp.ename%type;即my_name的类型与emp表中ename列的类型一样
在SQL中使用into来赋值：
declare emprec emp.ename%type;
begin
select t.ename into emprec from emp t where t.empno = 7369;
dbms_output.put_line(emprec);
end;
将查询到的语句赋值给emprec
记录型变量：
emp_rec emp%rowtype 代表一行
emp_rec.ename:='ADAMS';
declare
p emp%rowtype;
begin
select * into p from emp t where t.empno = 7369;
dbms_output.put_line(p.ename || ' ' || p.sal);
end;
:= 赋值符号等价于java中的=号；=判断两个值是否相等，等价于java中的==号。
3.if分支
语法1：
IF 条件 THEN 语句1;
语句2;
END IF;
语法2：
IF 条件 THEN 语句序列1；
ELSE 语句序列 2；
END IF；
语法3：
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
…
ELSE 语句;
END IF;
范例1:如果从控制台输入1则输出我是1
accept num prompt ‘请输入一个数字’;
declare
pnum number := #
begin
if pnum = 1 then dbms_output.put_line('我是1');
end if;
end;
&代表地址符，意思是指向num的值；接收的是字符串类型，此处会发生类型转换
范例2:判断人的不同年龄段18岁以下是未成年人，18岁以上40以下是成年人，40以上是老年人
accept num prompt ‘请输入一个年龄’;
declare
mynum number := #
begin
if mynum < 18 then dbms_output.put_line('未成年人');
elsif mynum >= 18 and mynum < 40 then dbms_output.put_line('中年人');
elsif mynum >= 40 then dbms_output.put_line('老年人');
end if;
end;
4.LOOP循环语句
语法1：
WHILE 条件 LOOP
.. .
total : = total + salary;
END LOOP;
语法2：
Loop
EXIT when 条件;
……
End loop;
语法3：
FOR I IN 1 . . 3 LOOP
语句序列 ;
END LOOP ;
范例:使用语法1输出1到10的数字
declare
step number := 1;
begin
while step <= 10 loop
dbms_output.put_line(step);
step := step + 1;
end loop;
end;
5.游标
在写java程序中有集合的概念，那么在pl/SQL中也会用到多条记录，就要用到游标，游标可以存储查询返回的多条数据。
语法：CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT语句;
例如：cursor c1 is select ename from emp;
游标的使用步骤：
打开游标： open c1; (打开游标执行查询)
取一行游标的值： fetch c1 into pjob; (取一行到变量中)
关闭游标： close c1;(关闭游标释放资源)
游标的结束方式 exit when c1%notfound
注意： 上面的pjob必须与emp表中的job列类型一致：pjob emp.empjob%type;
范例1：使用游标方式输出emp表中的员工编号和姓名
declare
cursor pc is select * from emp;
pemp emp%rowtype;
begin
open pc;
loop
fetch pc into pemp;
exit when pc%notfound;
dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
end loop;
close pc;
end;
范例2：写一段PL/SQL程序，为部门号为10的员工涨工资。
declare
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
rollback;
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp%notfound;
if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
带参数的光标
范例3：查询某个部门的员工姓名
declare
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
open cemp(20);这里将参数传入，则就会查询部门号为20的员工
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
6.异常
异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
系统定义异常
no_data_found (没有找到数据)
too_many_rows (select …into语句匹配多个行)
zero_divide (被零除)
value_error (算术或转换错误)
timeout_on_resource (在分布式数据库中，在等待资源时发生超时)
范例1：写出被0除的异常的plSQL程序
declare
pnum number;
begin
pnum := 1 / 0;
exception
when zero_divide then dbms_output.put_line('被0除');
when value_error then dbms_output.put_line('数值转换错误');
when others then dbms_output.put_line('其他错误');
end;
用户也可以自定义异常，在声明中来定义异常
DECLARE
My_job char(10);
v_sal emp.sal%type;
No_data exception;
cursor c1 is select distinct job from emp order by job;
如果遇到异常我们要抛出raise no_data;
范例2：查询部门编号是50的员工
declare
no_emp_found exception;
cursor pemp is select t.ename from emp t where t.deptno = 50;
pename emp.ename%type;
begin
open pemp;
fetch pemp into pename;
if pemp%notfound then raise no_emp_found;
end if;
close pemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put_line('其他错误');
end;
process monitor 这个可以在程序抛出异常时候启动，将之前的垃圾给清理干净。
7.存储过程
存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
创建存储过程语法：
创建procedure文件
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体；
End;
in或者out指定是输入参数还是输出参数。As或者is都可以，在这后面定义变量。
或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;
范例1：给指定的员工涨100工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程
create or replace procedure addSal1(eno in number)
is
pemp myemp%rowtype; 用来存储涨前的工资
begin
select * into pemp from myemp where empno = eno;
update myemp set sal = sal + 100 where empno = eno;
dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100));
end addSal1;
调用
begin
-- Call the procedure
addSal1(eno => 7902);
commit;
end;
也可以在命令行中，exec hello()。
8.存储函数
create or replace function 函数名(Name in type, Name out type, ...) return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end[函数名];
存储过程和存储函数的区别：过程没有返回值。 但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。
范例：使用存储函数来查询指定员工的年薪
create or replace function empincome(eno in emp.empno%type) return number is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal into psal from emp t where t.empno = eno;
return psal * 12 + nvl(pcomm, 0);
end;
使用存储过程来替换上面的例子
create or replace procedure empincomep(eno in emp.empno%type, income out number) is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
income := psal*12+nvl(pcomm,0);
end empincomep;
调用：
declare
income number;
begin
empincomep(7369, income);
dbms_output.put_line(income);
end;
9.触发器
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
1）触发器作用
数据确认；实施复杂的安全性检查；做审计，跟踪表上所做的数据操作等；数据的备份和同步 。
2）触发器的类型
语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器（有FOR EACH ROW）：触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量，识别值的状态。
语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
declare
……
begin
PLSQL 块
End 触发器名
范例：插入员工后打印一句话“一个新员工插入成功”
create or replace trigger testTrigger
after insert on person
declare
-- local variables here
begin
dbms_output.put_line('一个员工被插入');
end testTrigger;
范例：不能在休息时间插入员工
1. 周末: to_char(sysdate,'day') in ('星期六','星期日')
2. 上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期日') or
to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
-20001是错误代码，必须在-20001~-20999之间。
范例：数据的确认，涨后的薪水不能少于涨前的薪水
create or replace trigger checksalary
before update
on emp
for each row
begin
--if 涨后的薪水 < 涨前的薪水 then
if :new.sal < :old.sal then
raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||' 涨后:'||:new.sal);
end if;
end;
十三、对象
1.视图
视图就是封装了一条复杂查询的语句，即视图不存储数据，它是基于其他表的虚表，只是为了方便下次查询。首先用管理员赋予Scott权限。
grant create view to scott;
语法1.：CREATE VIEW 视图名称 AS 子查询
create or replace view empinfoview
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno
with read only;只能查询语句
下次查询这个语句，直接select * from empinfoview;
视图可以限制数据访问，简化复杂查询。
create view view10
as
select * from emp where deptno = 10
with check option;
insert into view10 values(***,10)
insert into view10 values(***,20)
如果没有with check option，则两条语句都可以，但是如果有with check option，那么视图只能操作看得到的数据，即部门号等于10的数据，而对部门号为20的数据无法插入成功。
如果修改视图，视图所查询的表的字段值也会被修改。一般不会去修改视图。
我们可以设置视图为只读。
2.序列
在很多数据库中都存在一个自动增长的列，要想在Oracle 中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n] 增长的间隔
[START WITH n] 从什么值开始
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] 最大值是多少
[{CYCLE|NOCYCLE}] 循环或者不循环，默认不循环
[{CACHE n|NOCACHE}];缓存即缓存序列，如果没有缓存只保留一个值。
范例:创建一个seqpersonid的序列，验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后，所有的自动增长应该由用户自己处理，所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用：
insert into person values(seqpersonid.nextval,7,null,null);
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是一般一张表用一个序列。序列的管理一般使用工具来管理。
序列要避免回滚操作，否则可能会造成序列不连续，因为序列是无法回到上一个值，这样就会造成中间一个值缺失。
序列在下列情况下出现裂缝：
回滚、系统异常、多个表同事使用一个序列。
3.索引
索引是用于加速数据存取的数据对象，一种独立于表的模式对象，可以存储在与表不同的磁盘或者空间中，即使被破坏也不会影响原来的表。合理的使用索引可以大大降低i/o 次数，从而提高数据访问性能。维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。
通过执行计划，可以看到查询是通过全表扫描。
SQL> explain plan for select * from emp where deptno=10;
已解释。执行计划。
SQL> select * from table(dbms_xplan.display);
SQL> create index myindex on emp(deptno);创建索引后，可以对比执行计划。
1.单列索引
单列索引是基于单个列所建立的索引，比如:
CREATE index 索引名 on 表名(列名)
2.复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同，比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);
范例：给person表的name建立索引
create index pname_index on person(name);
范例：给person表创建一个name和gender的索引
create index pname_gender_index on person(name, gender);
4.同义词
进入sys用户，赋予Scott用户查询hr用户表的权限。
grant select on hr.employees to scott;
给Scott用户创建同义词的权限。
grant create synonym to scott;
回到Scott用户下，为hr的employees表创建同义词。
SQL> create [public] synonym hremp for hr.employees;如果没有加public，则只能是Scott用户自己使用，否则其他用户也可以使用这个同义词。
SQL> select count(*) from hremp;
十四、Java代码访问Oracle对象
1.java连接Oracle的jar包
在Oracle安装目录下找到jar包：ojdbc14.jar，导入到程序中。
2.数据库连接字符串
String driver="Oracle.jdbc.OracleDriver";
String url="jdbc:Oracle:thin:@192.168.56.10:1521:orcl";
String username="scott";
String password="tiger";
3.实现过程与函数的调用
1）调用过程
--统计年薪的过程
create or replace procedure proc_countyearsal(eno in number,esal out number)
as
begin
select sal*12+nvl(comm,0) into esal from emp where empno=eno;
end;
--调用
declare
esal number;
begin
proc_countyearsal(7839,esal);
dbms_output.put_line(esal);
end;
1）java过程调用
@Test
public void testProcedure01(){
String driver="Oracle.jdbc.OracleDriver";
String url="jdbc:Oracle:thin:@192.168.56.10:1521:orcl";
String username="scott";
String password="tiger";
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");
callSt.setInt(1, 7839);
callSt.registerOutParameter(2, OracleTypes.NUMBER);
callSt.execute();
System.out.println(callSt.getObject(2));
} catch (Exception e) {
e.printStackTrace();
}
}
2）调用函数
--统计年薪的函数
create or replace function fun_countyearsal(eno in number)
return number
as
esal number:=0;
begin
select sal*12+nvl(comm,0) into esal from emp where empno=eno;
return esal;
end;
--调用
declare
esal number;
begin
esal:=fun_countyearsal(7839);
dbms_output.put_line(esal);
end;
2）java函数调用
@Test
public void testFunction01(){
String driver="Oracle.jdbc.OracleDriver";
String url="jdbc:Oracle:thin:@192.168.56.10:1521:orcl";
String username="scott";
String password="tiger";
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");
callSt.registerOutParameter(1, OracleTypes.NUMBER);
callSt.setInt(2, 7839);
callSt.execute();
System.out.println(callSt.getObject(1));
} catch (Exception e) {
e.printStackTrace();
}
}
4.游标引用的java测试
1.定义过程，并返回引用型游标
--定义过程
create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)
as
begin
open empList for select * from emp where deptno = dno;
end;
--pl/SQL中调用
declare
mycursor_c sys_refcursor;
myempc emp%rowtype;
begin
proc_cursor_ref(20,mycursor_c);
loop
fetch mycursor_c into myempc;
exit when mycursor_c%notfound;
dbms_output.put_line(myempc.empno||','||myempc.ename);
end loop;
close mycursor_c;
end;
2.java代码调用游标类型的out参数
@Test
public void testFunction(){
String driver="Oracle.jdbc.OracleDriver";
String url="jdbc:Oracle:thin:@192.168.56.10:1521:orcl";
String username="scott";
String password="tiger";
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, username, password);
CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");
callSt.setInt(1, 20);
callSt.registerOutParameter(2, OracleTypes.CURSOR);
callSt.execute();
ResultSet rs = ((OracleCallableStatement)callSt).getCursor(2);
while(rs.next()){
System.out.println(rs.getObject(1)+","+rs.getObject(2));
}
} catch (Exception e) {
e.printStackTrace();
}
}
扫一扫
4631
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
