Oracle数据库下载安装卸载操作步骤

Oracle

一、Oracle下载安装

1. 下载

Oracle官网

2. 解压

注意：两个压缩包一定要解压到一起

3. 安装

右键以管理员身份运行：

注意：安装Oracle11g之前要先安装Framework3.5

打开控制面板：

开始安装：

4. Oracle服务

要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。

注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名。

5. 安装pl/sql developer

PL/SQL Developer注册码

Product Code：4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz
serial Number：601769

password：xs374ca

6. Oracle卸载

右键以管理员身份运行：

卸载教程：

https://jingyan.baidu.com/article/ce09321b2133722bff858fcc.html

二、用户、角色、权限管理

1. sys和system用户

Oracle安装会自动的生成sys用户和system用户：

1. sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限，该用户默认的密码是change_on_install
2. system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限，默认的密码是manager
3. 一般讲，对数据库维护，使用system用户登录就可以拉

注意：也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。

2. 系统管理SQL

--显示当前用户的表
select * from tab;

--显示当前用户可以访问的表
select * from all_tables;

--显示所有用户的表
select * from user_tables;

--显示用户为SCOTT的表
select * from dba_tables where owner='SCOTT';

--显示所有用户名和帐户的状态
select username,account_status from dba_users;

--显示所有用户信息
select * from dba_users;

--将SCOTT帐号解锁(加锁)
alter user scott account unlock(lock);

--当前用户的缺省表空间
select default_tablespace from dba_users where username=(select user from dual);

3. 创建表空间

create tablespace 表空间名称
datafile 'D:\oracledata\tomspace.dbf'
size 50m
autoextend on
next 50m maxsize 2048m;

--删除表空间
drop tablespace 表空间名称 including contents and datafiles;

4. 创建用户并且指定表空间

create user tom identified by 123456 default tablespace tomspace;
--修改用户密码
alter user teacher1 identified by 123456;
--删除用户
drop user 用户名;
drop user 用户名 cascade;

5. 给用户分配角色

--授予角色
grant connect,resource to 用户名;
--收回角色
revoke connect, resource from 用户名;

6. 角色管理

oracle为兼容以前版本，提供三种标准角色（role）:connect，resource和dba.

预定义角色是指Oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba。

6.1 connect角色

Connect角色是授予最终用户的典型权利，最基本的。connect角色具有以下系统权限：

权限	说明
alter session	修改会话
create cluster	建立聚簇
create database link	建立数据库链接
create session	建立会话
create view	建立视图
create sequence	建立序列
create synonym	建立同义词

6.2 resource角色

resource角色是授予开发人员的。resource角色包含以下系统权限：

权限	说明
create cluster	建立聚簇
create indextype	建立索引类型
create operator	建立操作符
create table	建表
create sequence	建立序列
create type	建立类型
create procedure	建立过程
create trigger	建立触发器

6.3 dba角色

dba角色具有所有的系统权限，默认的dba用户为sys和system他们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

dba_打头的存放数据库中所有相关对象的信息，例如你要知道当前系统中有哪些表对象，就可以查询dba_tables这个视图；user_打头的是当前用户拥有的所有对象的信息；all_打头的是当前用户有权限访问的所有对象的信息（不一定拥有该对象，只要可以访问就算）

三、数据类型

数据类型	描述
varchar，varchar2	表示一个字符串
NUMBER	NUMBER(n)表示一个整数，长度是n
NUMBER(m,n)	表示一个小数，总长度是m，小数是n，整数是m-n。例如：number(10,2):整数部分占8位，小数部分占2份
DATE	表示日期类型
CLOB	大对象，表示大文本数据类型，可存4G
BLOB	大对象，表示二进制数据，可存4G

1. varchar/varchar2

varchar：存放定长的字符数据，最长2000个字符；

varchar2：存放可变长字符数据，最大长度为4000字符;

varchar2把所有字符都占两字节处理(一般情况下,如果是utf-8,则每个字符占3个字节（bytes）)，Varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；

varchar2把空串等同于null处理，而Varchar仍按照空串处理；

varchar2字符要用几个字节存储，要看数据库使用的字符集。

目前没有本质的区别,但是：

varchar2是oracle提供的独特的数据类型，oracle保证在任何版本中该数据类型向上和向下兼容。

但oracle不保证varchar向上和向下兼容，这是因为varchar是标准sql提供的数据类型。有可能随着sql标准的变化而改变！

所以大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

2. nvarchar/nvarchar2

nvarchar2中存储中文字时，一个中文字当一个字符来处理

nvarchar2(10)是可以存进去10个汉字的，如果用来存英文也只能存10个字符。

而VARCHAR2中一个中文字当两个字符来处理

VARCHAR2(10)的话，则只能存进5个汉字，英文则可以存10个

3. 数字类型

3.1 NUMBER(p,s)

精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127;

例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位； 如果小数部分大于三位则四舍五入。

3.2 INTEGER类型

INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

4. 日期类型

4.1 DATE

这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日
date类型在数据库中的存储固定为7个字节，格式为：

• 第1字节：世纪+100
• 第2字节：年
• 第3字节：月
• 第4字节：天
• 第5字节：小时+1
• 第6字节：分+1
• 第7字节：秒+1

4.2 TIMESTAMP§

这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。
格式为：

• 第1字节：世纪+100
• 第2字节：年
• 第3字节：月
• 第4字节：天
• 第5字节：小时+1
• 第6字节：分+1
• 第7字节：秒+1
• 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形

5. char/nchar

固定长度的字符数据,其长度的最大值是2000字节,而最小值和默认值是1;

四、表的管理

1. 建表

create table 表名(
  字段1 数据类型 [default 默认值],
  字段2 数据类型 [default 默认值],
  ...
  字段n 数据类型 [default 默认值]
);

2. 表的删除

drop table 表名;

3. 表的修改

在sql中使用alter可以修改表：

--修改表名：
alter table [旧表名] rename to [新表名];

--修改字段的数据类型：
alter table [表名] modify [字段名] [数据类型];

--修改字段名：
alter table [表名] rename column 列名1 TO 列名2;

--添加字段：
alter table [表名] add[新字段名] [数据类型];

--删除字段：
alter table [表名] drop column [字段名];

--删除表的外键约束：
alter table 表名 drop constraint 外键名;

4. 约束

4.1 主键约束

值不能为空，并且不能重复,是该表中记录的唯一标示。

列级定义：

create table person(
  id number(10) primary key,
  name varchar2(10)
);

表级定义：

create table person(
  id number(10),
  name varchar2(10),
  primary key(id)
);

4.2 非空约束

值不能为空

create table person(
    id number(10) primary key,
    name varchar2(10),
    age number(4) not null
);

4.3 唯一约束

值不能重复

create table person(
    id number(10) primary key,
    name varchar2(10) unique,
    age number(4) not null
);

4.4 检查约束

使用检查约束可以来约束字段值的合法范围

create table person(
    id number(10) primary key,
    name varchar2(10) unique,
    age number(4) not null,
    gender number(1) check(gender in (1, 2))
);

4.5 外键约束

值可以为空，如果不为空，必须引用表的主键值

create table family(
    id number(10) primary key,
    name varchar2(20),
    address varchar2(50)
);

create table person(
    id number(10) primary key,
    name varchar2(10) unique,
    age number(4) not null,
    gender number(1)  check(gender in (1, 2)),
    familyId number(10),
    constraint fk_tom_person_scoreid foreign key(familyId)
    references family(id)
);

五、序列

1. 创建序列

序列: 可供多个用户用来产生唯一数值的数据库对象

• 自动提供唯一的数值
• 主要用于提供主键值
• 将序列值装入内存可以提高访问效率

语法：

CREATE SEQUENCE 序列名
　　[INCREMENT BY n]
　　[START WITH n]
　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
　　[{CYCLE|NOCYCLE}]
　　[{CACHE n| NOCACHE}];

参数说明：

1. INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。
2. START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
3. MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
4. MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是-10的26次方;对于递增序列，最小值是1。
5. CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
6. CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

例子：

create sequence seq_demo
  increment by 2
  start with 10
  minvalue 1
  nomaxvalue
  nocycle
  nocache;

2. 修改、删除序列

使用 alter 进行修改

alter sequence seq_demo
  increment by 1
  start with 1
  minvalue 1
  maxvalue 99999999
  cycle
  cache 20;

使用 drop 删除

drop sequence seq_demo;

3. 序列的使用

currval：表示序列的当前值，新序列必须使用一次nextval才能获取到值，否则会报错

nextval：表示序列的下一个值。新序列首次使用时获取的是该序列的初始值，从第二次使用时开始按照设置的步进递增

--查询序列的值：
select seq_name.currval from dual;
select seq_name.nextval from dual;

--SQL语句中使用：
insert into table (id) values (seq_name.nextval)

六、数据的更新

1. INSERT

语法：

INSERT  INTO表名[(列名1，列名2，...)] VALUES(值1，值2，...);

简单写法（不建议）

INSERT  INTO 表名 VALUES(值1，值2，...);

例子：

insert into person(id,name,age,gender) values(seq_demo.nextval,'张三',25,1);

注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null

insert into person values(seq_demo.nextval,'李四',28,null,null);

2. UPDATE

--全部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，....

--局部修改：
UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件;

3. DELETE

DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据

4. 事务

一个事务中可以包含多条SQL语句。要么全部成功，要么全部失败

提交语句commit：

insert into person(id,name,age,gender) values(seq_demo.nextval,'小黑',25,1);
commit;

回滚语句rollback：

insert into person(id,name,age,gender) values(seq_demo.nextval,'小白',28,0);
rollback;

七、函数

dual是oracle自定一张伪表

1. 字符函数

1. 小写转大写：upper(小写字符串)
2. 大写转小写：lower(大写字符串)
3. 首字符大写：initcap(字符串)
4. 字符串拼接：concat(字符串1, 字符串2)
   在oracle中建议使用“||”实现字符串拼接
5. 字符串的截取：substr(源字符串, 开始索引,截取长度)
6. 字符串的长度：length(‘hello’) / lengthb(‘hello’)
7. 字符串替换：replace(源字符串, 被替换的字符串,替换字符串)
8. 查找子字符串在源字符中的位置：instr(源字符串, 子字符串)
9. 左填充与右填充：
   lpad(源字符串,位数,填充字符)
   rpad(源字符串,位数,填充字符)
10. 去除前后指定的字符：trim(指定字符 from 原字符串) 默认去空格

2. 数值函数

1. 四舍五入函数：ROUND(数值,位数)
   默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
   注:位数可以是正数,也可以是负数;正数表示小数后几位;负数表示小数前几位
2. 取整：TRUNC(数值,位数)，默认全部去掉小数，也可以指定保留的位数
3. 取余数MOD(被除数，除数)

3. 日期函数

Oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律：

• 日期 – 数字 = 日期
• 日期 + 数字 = 日期
• 日期 – 日期 = 数字

获得两个时间段中的月数：MONTHS_BETWEEN(开始日期, 结束日期)

获得几个月后的日期：ADD_MONTHS(日期, 数值)

4. 转换函数

4.1 TO_CHAR：字符串转换函数

​年：y 四位使用yyyy
​月：m 两位使用mm
​日：d 两位使用dd
时：h 24小时制的两位使用hh24
分：mi
秒：s 两位使用ss

--其中sysdate表示系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR还可以给数字做格式化：

在金额的前面加上货币符号可以使用“$”代表是美元，如果要使用本地的货币单位使用“L”

select to_char(123456,'$999999'),to_char(123456,'L999999') from dual;

4.2 TO_NUMBER：数值转换函数

select to_number('123456') from dual;

4.3 TO_DATE：日期转换函数

select to_date('2013-01-10 20:15:30','yyyy-mm-dd hh24:mi:ss') from dual;

5. 通用函数

5.1 空值处理

nvl(数值,转换值) /nvl2(数值,值1，值2)

nvl(a,b) 如果a是null,那么得到b

nvl2(a,b,c) 如果a是null,那么得到c,否则得到b

5.2 decode函数

该函数类似if…else if…esle

语法：

decode(col/expression, [search1,result1],[search2, result2]....[default])

col/expression：列名或表达式

search1，search2…：用于比较的条件

result1, result2…：返回值

如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值

5.3 case when

CASE expr
  WHEN comparison_expr1 THEN return_expr1
  [WHEN comparison_expr2 THEN return_expr2
  WHEN comparison_exprn THEN return_exprn
  ELSE else_expr]
END;

expr：列名或表达式

comparison_expr1…：用于比较的条件

return_expr1…：返回值

如果expr和omparison_expr匹配就返回return_expr,否则返回else_expr

八、单表查询

1. 查询语法

--所有字段
select * from 表名;
--指定字段
select 字段列表 from 表名;

2. 别名查询

在查询的结果列中可以使用别名

select  列名 [as] 别名，列名 别名，... from 表名;

3. 去除重复

--所有列去重
select distinct * from 表名;
--指定列去重
select distinct 列名1,列名2, ... from 表名;

4. 条件查询

select *|字段列表 from 表名 where 条件

4.1 比较运算

select * from emp where empno=7369;
select * from emp where deptno <> 20;

4.2 非空和空的限制

is not null/is null

select * from emp where comm is not null;
select * from emp where mgr is null;

4.3 范围限制

between…and/in/not in

select * from emp where sal between 1100 and 2450;
select * from emp where deptno in (10,20);

4.4 模糊查询

like,_表示一个字符，%表示任意字符

select * from emp where ename like '%S%';
select * from emp where ename like 'S__';

4.5 逻辑运算

and/or

select * from emp where sal >= 1100 and sal <=2450;
select * from emp where deptno=10 or deptno=20;

4.6 排序

order by

select * from emp order by deptno asc;
select * from emp order by deptno desc;
select * from emp order by deptno,sal desc,empno;

4.7 聚合函数

--统计记录数count()：
select count(*) from emp;

--求和sum()：
select sum(sal) from emp;

--平均值avg()：
select avg(sal) from emp;

--最大值max()：
select max(sal) from emp;

--最小值min()：
select min(sal) from emp;

4.8 分组查询

group by

在oracle中查询字段列表中出现的字段名称，必须放在group by关键词之后,除非该字段使用聚合函数。
mysql中没有此限制

select deptno,max(sal) from emp group by deptno;
select deptno,count(*) from emp group by deptno;
select job,count(*) from emp group by job;
select deptno,job,count(*) from emp group by deptno,job;

分组查询中的条件having

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

注意：where查询条件中不允许出现聚合函数

九、多表查询

1. 等值连接

查询结果集为符合条件的数据

select * from emp e,dept d where e.deptno=d.deptno;

2. 内连接

inner join…on…/join…on…
与等值连接相同，查询结果集为符合条件的数据

select * from emp e inner join dept d on e.deptno=d.deptno;
select * from emp e join dept d on e.deptno=d.deptno;

3. 左外连接

left outer join…on…/left join…on…
查询结果集为符合条件的数据与左边表中不符合条件的数据

select * from emp e left outer join dept d on e.deptno=d.deptno;
select * from emp e left join dept d on e.deptno=d.deptno;

4. 右外连接

right outer join…on…/right join…on…
查询结果集为符合条件的数据与右边表中不符合条件的数据

select * from emp e right outer join dept d on e.deptno=d.deptno;
select * from emp e right join dept d on e.deptno=d.deptno;

5. 全连接

full join…on…
查询结果集为符合条件的数据与左右两表中不符合条件的数据（非笛卡尔乘积）

select * from emp e full join dept d on e.deptno=d.deptno;

6. 自连接

一种虚拟方式。允许多次引用同一各表，该表就像独立的表一样，实际上创建了表的一个虚拟视图，允许多次使用这个虚拟视图。常用在自身是自引用的表，这类的表的一列指向了同一个表中的另一列。

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;

7. Oracle支持的连接查询

--左外连接：
select * from emp e,dept d where e.deptno=d.deptno(+);

--右外连接：
select * from emp e,dept d where e.deptno(+)=d.deptno;

十、子查询

在查询语句中嵌套查询语句被称为子查询。

1. 子查询应注意的问题

1. 合理的书写风格 (换行问题)
2. 写在括号里
3. 可以在主查询的where select having from后面使用子查询
4. 不可以在group by后面使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可
7. 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符

2. where后面的子查询

子查询结果作为主查询的条件

select * from emp where sal > (select sal from emp where ename='MARTIN');

3. from后面使用子查询

子查询结果作为主查询的表

select t.* from (select * from emp where deptno=10) t where sal<=3000;

4. select后面使用子查询

子查询结果作为主查询的字段

select empno,ename,job,deptno,(select dname from dept where dept.deptno=emp.deptno) ename from emp;

5. create/insert中的子查询

复制表结构以及数据

create table d_table_name as select * from s_table_name;

仅复制表结构

create table d_table_name as select * from s_table_name where 1=2;

仅复制数据，两个表结构必须一致

insert into d_table_name select * from s_table_name;

复制部分列，两个表的结构可以不一样

insert into d_table_name (column1,column2,column3) select column1x,column2x,column3x from s_table_name;

十一、分页查询

1. rownum伪列

oracle数据库在进行表中数据查询时，为结果集生成的伪列，简单的说ROWNUM是对符合条件结果的序列号。它总是从1开始排起的，所以你选出的结果不可能没有1，而有其他大于1的值。

• rownum永远按照默认的顺序生成
• rownum只能使用<或<=，而不能使用>，>=或between…and

注意：rownum不能以任何基表的名称作为前缀

2. 通过rownum实现分页查询思路

2.1 排序查询

select * from emp order by empno;

2.2 子查询

将排序查询作为子查询，再查询带有rownum的数据，并且伪列值小于等于最大值

select t.*,rownum rn from (select * from emp order by empno) t where rownum<=10;

2.3 再次子查询

将查询结果集再作为子查询，并且上一步结果中的伪列值大于等于最小值

select tt.* from
  (select t.*,rownum rn from
    (select * from emp order by empno) t
  where rownum<=10) tt
where tt.rn>=6;

十二、视图

视图是一种虚表。建立在已有表的基础上，视图赖以建立的这些表称为基表。

向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句。视图向用户提供基表数据的另一种表现形式

优点:

• 为用户集中数据，简化用户的数据查询和处理。
• 屏蔽数据库的复杂性，用户不必了解数据库的复杂性。
• 简化用户权限的管理，只授予用户使用视图的权限。
• 便于数据共享，多个用户不必都定义所需的数据。
• 可以重新组织数据，以便关联到其他应用中。

1. 创建视图

1.1 权限

要在当前用户中创建视图，用户必须具有create view系统权限

grant create view to tom;

1.2 语法

create [ or replace ] [ force | noforce] view  view_name
​         [ (column1,column2,...) ]
​         as select ...
​         [ with check option ]
​         [ with read only ];

参数说明：

or replace：如果存在同名的视图，则使用新视图"替代"已有的视图

force/noforce: "强制"创建视图，不考虑基表是否存在，也不考虑是否具有使用基表的权限。默认是不强制noforce

column1,column2,…：视图的列名，列名的个数必须与select查询中列的个数相同。如果select查询包含函数或表达式，则必须为其定义列名。此时，既可以用column1，column2指定列名，也可以在select查询中指定列名。

with check option：指定对视图执行的dml操作必须满足“视图子查询”的条件即，对通过视图进行的增删改操作进行"检查"，要求增删改操作的数据，必须是select查询所能查询到的数据，否则不允许操作并返回错误提示。默认情况下，在增删改之前"并不会检查"这些行是否能被select查询检索到。

with read only：创建的视图只能用于查询数据，而不能用于更改数据。

1.3 创建简单视图

简单视图定义：是指基于单个表建立的，不包含任何函数、表达式和分组数据的视图。

create view v_emp as select empno,ename,job,hiredate,deptno from emp;

注意：对简单视图进行DML操作，基表也发生了相应的更改。

1.4 创建只读视图

只能查询，无法进行更改：

create view v_emp_readonly as select empno,ename,job,hiredate,deptno from emp with read only;

1.5 创建检查约束视图

create view v_emp_check as
select empno,ename,job,hiredate,deptno from emp where deptno=10
with check option;

--正常插入没有问题
insert into vw_emp_check values ('2','c','cc',to_date('2018/12/11','yyyy/mm/dd'),10);

--插入数据失败

insert into vw_emp_check values ('3','d','dd',to_date('2018/12/11','yyyy/mm/dd'),20);

注：20号部门不在查询范围内，违反检查约束，所以无法插入

1.6 创建复杂视图

复杂视图定义：是指包含函数、表达式、或分组数据的视图。主要目的是为了简化查询。主要用于执行查询操作，并不用于执行DML操作。

create or replace view v_emp_job_sal(job,avgsal,sumsal,maxsal,minsal)
as select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;

注意：当视图的select查询中包含函数或表达式时，必须为其定义列别名。

2. 修改视图

执行create or replace view语句。这种方法代替了先删除(“权限也将随之删除”)后创建的方法，会保留视图上的权限，但与该视图相关的存储过程和视图会失效。

3. 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

视图被删除后，该视图的定义会从词典中被删除，并且在该视图上授予的“权限”也将被删除。视图被删除后，其他引用该视图的视图及存储过程等都会失效。

drop view 视图名;

十三、索引

1. 索引简介

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o 次数，从而提高数据访问性能。一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中。

索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引。

索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度。

在删除一个表时，所有基于该表的索引会自动被删除。

2. 索引原理

为什么添加了索引之后，会加快查询速度呢？

图书馆：如果杂乱地放书的话检索起来就非常困难，所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询，按书名查或者类别查，这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。

3. 创建索引

创建索引的语法：

CREATE INDEX INDEX_NAME ON table (column [,column]....);

判断sql语句的优劣可以通过执行计划来看。

生成执行计划：

explain plan for sql语句;

查看上一句生成的执行计划：

select * from table(dbms_xplan.display);

1） 单列索引

单列索引是基于单个列所建立的索引。比如：

create index myindex on emp(deptno);

索引创建成功之后，我们再来查看执行计划：

2） 复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：

create index emp_idx1 on emp(ename,job);

4. 索引的使用

以下情况可以创建索引：

• 列中数据值分布范围很广
• 列经常在WHERE子句或连接条件中出现
• 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

以下情况不要创建索引：

• 表很小
• 列不经常作为连接条件或出现在WHERE子句中
• 查询的数据大于2%到4%
• 表经常更新

5. 删除索引

使用DROP INDEX命令删除索引

十四、触发器

触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用。

语法：

CREATE [OR REPLACE] TIGGER触发器名
触发时间（BEFORE | AFTER）
触发事件（DELETE | INSERT | UPDATE）
　　ON表名/视图名
　　[FOR EACH ROW]
BEGIN
    pl/sql语句

END;

例子：

CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定触发时机为修改操作前触发
   ON emp
   FOR EACH ROW   --说明创建的是行级触发器
BEGIN
   --将删除前数据插入到日志记录表 emp_log ,以供监督使用。
   INSERT INTO emp_log
​     (empno,ename,job,mgr,sal,comm,hiredate,deptno)
   VALUES
​     (:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate);
END;

注意：

:new为一个引用最新的列值

:old为一个引用以前的列值

这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在。

且update语句两个都有，而insert只有:new，delete只有:old。

删除触发器：

drop trigger tr_del_emp;

十五、PL/SQL编程

SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL/SQL（Procedure Language/SQL），是Oracle对SQL语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

1. 程序语法

DECLARE

    声明部分：在此声明变量，类型，游标，异常等
BEGIN
    执行部分：过程以及SQL语句，是程序的主要部分
EXCEPTION
    执行异常部分：错误处理
END;

其中，执行部分不能省略。

例子：使用pl/sql输出Hello World

begin
    dbms_output.put_line('hello world');
end;

2. 标识符

PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有：

1. 标识符名不能超过30字符；
2. 第一个字符必须为字母；
3. 不分大小写；
4. 不能用中划线；
5. 不能是SQL保留字。

3. 变量

3.1 变量声明

在程序的声明部分可以来定义常量和变量。

变量的基本类型就是ORACLE中的建表时字段的变量如char，varchar2，date，number等

v_num number;
v_char varchar2(20);
v_date date;

说明变量名、数据类型和长度后用分号结束说明语句。

非空变量：

v_num number not null := 100;

常量：

v_num constant number not null := 200;

引用变量：引用型变量，即v_name的类型与emp表中ename列的类型一样

v_name  emp.ename%type;

记录型变量：代表一行

emp_row  emp%rowtype;

3.2 变量赋值

变量名:=值;

:= 赋值符号等价于java中的=号

v_name := 'tom';

SQL语句赋值：将查询到的某个数值或某条记录赋值到变量中

select 字段名 into 变量名 from 表名;
select * into 变量名 from 表名;

提示：通过&可接收控制台输入的数据

4. 判断语句

4.1 单分支

IF  条件  THEN
    语句;
END IF;

4.2 双分支

IF  条件  THEN
    语句1;  
ELSE
    语句2;
END IF;

4.3 多分支

IF  条件  THEN
    语句1;
ELSIF  条件  THEN
    语句2;
ELSIF  条件  THEN
    语句3;
…
ELSE
    语句;
END IF;

5. 循环语句

5.1 while循环

当条件成立,执行循环

WHILE  条件  LOOP
    语句;
END LOOP;

5.2 loop循环

当条件成立，退出循环

LOOP
  EXIT  WHEN  条件;
  语句;
END LOOP;

5.3 for循环

FOR  i  IN  起始值..结束值  LOOP
  语句;
END  LOOP;

6. 异常

异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

6.1 语法规则

EXCEPTION
    WHEN exc1 THEN
    语句1;
    WHEN exc2 THEN
    语句2;
    WHEN OTHERS THEN
    语句3；

6.2 系统定义异常

7. 游标

7.1 游标的概念

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

7.2 隐式游标

DML操作和单行SELECT语句会使用隐式游标。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

游标的属性有四种：

隐式游标的属性	返回值类型	意义
SQL%ROWCOUNT	整型	DML语句成功执行的数据行数
SQL%FOUND	布尔型	DML和单行查询语句执行是否成功，true成功
SQL%NOTFOUND	布尔型	DML和单行查询语句执行是否失败，true失败
SQL%ISOPEN	布尔型	DML和单行查询语句是否在执行中，true执行，false结束

7.3 显式游标

显式游标的使用分成以下4个步骤。

1）声明游标

在DECLEAR部分按以下格式声明游标：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] IS SELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

2）打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。

3）提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...];

--或

FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

下面对这两种格式进行说明：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。

4）关闭游标

CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

十六、存储过程

1. 定义

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

**注:**存储过程和存储函数：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数；

子程序是使用PL/SQL语言写的；

2. 创建存储过程

创建存储过程语法：

CREATE [OR REPLACE] PROCEDURE 过程名[(参数名 in/out/in out 数据类型,...)]  
AS[IS]
BEGIN
    PLSQL子程序体；
END [过程名];

3. 存储过程参数模式

• 参数IN：表示输入参数，是参数的默认模式。IN输入参数不能被重新赋值。
• 参数OUT：表示输出参数，类型可以使用任意Oracle中的合法类型。OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程中。
• 参数IN OUT：表示该参数可以向该过程中传递值，也可以将某个值传出去。

4. 调用存储过程

call 过程名();

5. 存储函数

函数(Function)为一命名的存储程序，可以带参数，并返回一个值。函数和过程结构类似，但是必须有一个return子句，用于返回函数值。函数说明要指定函数名，结果值类型，以及参数类型等。

create or replace function 函数名(Name in type, Name out type, ...) return 函数值类型 is[as] 结果变量 数据类型;
begin
    return 结果变量;
end;

。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

2）打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。

3）提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...];

--或

FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

下面对这两种格式进行说明：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。

4）关闭游标

CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

十六、存储过程

1. 定义

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

**注:**存储过程和存储函数：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数；

子程序是使用PL/SQL语言写的；

2. 创建存储过程

创建存储过程语法：

CREATE [OR REPLACE] PROCEDURE 过程名[(参数名 in/out/in out 数据类型,...)]  
AS[IS]
BEGIN
    PLSQL子程序体；
END [过程名];

3. 存储过程参数模式

• 参数IN：表示输入参数，是参数的默认模式。IN输入参数不能被重新赋值。
• 参数OUT：表示输出参数，类型可以使用任意Oracle中的合法类型。OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程中。
• 参数IN OUT：表示该参数可以向该过程中传递值，也可以将某个值传出去。

4. 调用存储过程

call 过程名();

5. 存储函数

函数(Function)为一命名的存储程序，可以带参数，并返回一个值。函数和过程结构类似，但是必须有一个return子句，用于返回函数值。函数说明要指定函数名，结果值类型，以及参数类型等。

create or replace function 函数名(Name in type, Name out type, ...) return 函数值类型 is[as] 结果变量 数据类型;
begin
    return 结果变量;
end;