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用户:
- sys用户是超级用户,具有最高权限,具有sysdba角色,有create database的权限,该用户默认的密码是change_on_install
- system用户是管理操作员,权限也很大。具有sysoper角色,没有create database的权限,默认的密码是manager
- 一般讲,对数据库维护,使用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}];
参数说明:
- INCREMENT BY用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表Oracle序列的值是按照此步长递减的。
- START WITH 定义序列的初始值(即产生的第一个值),默认为1。
- MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增Oracle序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
- MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是-10的26次方;对于递增序列,最小值是1。
- CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
- 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. 字符函数
- 小写转大写:upper(小写字符串)
- 大写转小写:lower(大写字符串)
- 首字符大写:initcap(字符串)
- 字符串拼接:concat(字符串1, 字符串2)
在oracle中建议使用“||”实现字符串拼接 - 字符串的截取:substr(源字符串, 开始索引,截取长度)
- 字符串的长度:length(‘hello’) / lengthb(‘hello’)
- 字符串替换:replace(源字符串, 被替换的字符串,替换字符串)
- 查找子字符串在源字符中的位置:instr(源字符串, 子字符串)
- 左填充与右填充:
lpad(源字符串,位数,填充字符)
rpad(源字符串,位数,填充字符) - 去除前后指定的字符:trim(指定字符 from 原字符串) 默认去空格
2. 数值函数
- 四舍五入函数:ROUND(数值,位数)
默认情况下ROUND四舍五入取整,可以自己指定保留的位数。
注:位数可以是正数,也可以是负数;正数表示小数后几位;负数表示小数前几位 - 取整:TRUNC(数值,位数),默认全部去掉小数,也可以指定保留的位数
- 取余数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,'9999'),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. 子查询应注意的问题
- 合理的书写风格 (换行问题)
- 写在括号里
- 可以在主查询的where select having from后面使用子查询
- 不可以在group by后面使用子查询
- 强调from后面的子查询
- 主查询和子查询可以不是同一张表,只要子查询返回的结果,主查询可以使用即可
- 单行子查询只能使用单行操作符,多行子查询只能使用多行操作符
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 的标识符定义的要求相同。要求和限制有:
- 标识符名不能超过30字符;
- 第一个字符必须为字母;
- 不分大小写;
- 不能用中划线;
- 不能是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;