Oracle教程

Oracle第一章

  1. 首先打开Oracle服务
  2. 配置监听器(这个是因为教室的电脑Oracle安装有问题,没有配置好监听器)开始菜单中找到net configration assistant添加一个监听器
  3. 用system用户登录sqlplus
  4. 解锁scott用户 :(也是因为教室的Oracle安装问题导致scott账户未解锁)
1
alter user scott account unlock;
  1. 修改scott密码:
1
alter user scott identified by tiger;
  1. 使用scott登录sqlplus, scott是oracle自带的一个实例账户,它带有四个实例表,其中重要的就是emp员工表与dept部门表
  2. 安装PL/SQL第三方工具, 因为Oracle没有自带的图形化界面管理器,所以我们需要安装PLSQL,它是oracle的一个第三方GUI工具。

介绍一下Oracle的命令

  • 连接数据库:
1
connect scoott/tiger@orcl;

用户名为scott,密码为tiger,数据库名为orcl

  • 显示当前用户:
1
show user;

也可使用查询语句:

1
select USER from dual; --dual是oracle的一个虚拟表
  • 显示表结构(以emp表为例):
1
describe emp;

可简写为:

1
desc emp;

Oracle第二章

创建表空间

(在SqlServer中称为创建一个是数据库,而在Oracle中则称为创建一个表空间)

格式: create tablespace 表空间名 datafile ‘文件路径’ size 文件大小

如:

cerate tablespace myspace datafile 'D:\myspace.dbf' size 10MB;

删除表空间:

drop tablespace myspace incluiding contents and datafile;

创建用户

格式: create user 用户名 identified by 密码 default tablespace 默认表空间

如:

create user user1 identified by user1 default tablespace system;

删除用户:

drop user user1 cascade;

给用户授权

方式一:授予角色

1、connect     //登录
2、resource    //普通权限,用于操作
3、DBA         //管理员权限(慎用)

如:

grant connect to user1;
grant connect,resource to user1;

方式二:授予单个权限

如:

grant create table to user1;           //授予user1建表的权限
grant drop table to user1;             //授予user1删表的权限

方式三:将某个对象的权限授予用户

如:

grant select on scott.emp to user1;      //将scott用户的emp表的查询权限授予user1
grant all on scott.emp to user1;       //将scott用户的emp表的所有权限授予user1 

收回权限:

格式: revoke 权限 from 用户

如:

revoke connect from user1;   //收回user1的connect权限
revoke select on scott.emp from user1;    //收回user1对emp表的查询权限  

Oracle第三章

基本查询

select格式:

1
2
3
4
5
select 列名 from 表名 ;
where 查询条件
group by 分组列
having 分组后条件
order by 排序列 asc[desc]

如:查询部门10的雇员

select * from emp where deptno=10;

行号(rownum)

每个表都有一个虚列ROWNUM,它用来显示结果中记录的行号。我们在查询中也可以显示这个列。

如:显示emp表的行号

select rownum,ename from emp;

如:显示前三行

	select * from emp where rownum<=3;

查询进行计算

如:显示雇员工资上浮20%的结果

select ename,sal,sal*(1+20%) from emp;

如:显示每个员工的总工资(工资+奖金)

update emp set comm = o where comm is null;    //因为null的特殊性,它与任何值运算都等于null,所以先要把它更新为0,后面我们会学到一个函数来处理null值

select ename,sal+comm from emp;

使用别名

如:在查询中使用列别名

select ename as 名称,sal as 工资 from emp; //建议省略as

另,在别名为关键字或有特殊符号时需要加双引号

如:

select ename as "select",sal*12+5000 as "年度工资(加年终奖)" from emp;

连接运算符

连接运算符是双竖线“||”。通过连接运算可以将两个字符串连接在一起。

如:在查询中使用连接运算

select ename||job as "雇员和职务表" from emp;

*注意:‘5’||5结果为’55’    ‘5’+5结果为 10 *

六、消除重复行(distinct)

如果在显示结果中存在重复行,可以使用关键字distinct消除重复显示

如:统计职务的数量

select count(distinct job) from emp;

排序

1、升序(默认为升序asc,所以可以忽略)

如:查询雇员姓名和工资,并按工资从小到大排序

select ename,sal from emp order by sal asc;

2、降序(desc不可忽略)

如:查询雇员姓名和雇佣日期,并按雇佣日期排序,后雇佣的先显示

select ename,hiredate from emp order by hiredate desc;

3、多列排序

可以按多列进行排序,先按第一列,然后按第二列、第三列…。

如:查询雇员信息,先按部门从小到大排序,再按雇佣时间的先后排序

select ename,deptno,hiredate from emp order by deptno hiredate;

Oracle第四章——条件查询、字符型函数

条件查询

1、模糊查询(between、in、like)

A、between:在某某之间。如,显示工资在1000~2000之间的雇员

	select * from emp where sal beteween 1000 and 2000;

B、in:在某某之间。如,显示职务为“SALMAN”,“CLEARK”和“MANAGER”的雇员信息

	select * from emp where job in ('SALMAN','CLERK','MANAGER');

C、like:与通配符使用

通配符:% 代表0个或任意个字符    —_ 代表1个字符

如:显示姓名以“S”开头的雇员信息。

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

显示姓名第二个字符为“A”的雇员信息

	select * from emp ename like '_A%';

2、空值查询

空:is null     非空: is not null

如:查询奖金为空的雇员信息

	select * from emp where comm is null;

函数

1、数学函数

函数 功能 实例 结果
abs 求绝对值函数 abs(-5) 5
sqrt 求平方根 sqrt(2) 1.414
power 求幂函数 power(2,3) 8

使用求绝对值函数abs

	select abs(-5) from dual;

使用求平方根函数sqrt。

	select sqrt(2) from dual;

使用ceil函数。

	select ceil(2.35) from dual;

使用floor函数。

	select floor(2.35) from dual;

2、使用四舍五入函数round 格式:round(数字,保留的位数)

select round(45.923,2), round(45.923,0), round(45.923,-1) from dual;

3、字符型函数

ascii 返回与ASCII码相应的字符 Ascii('A') 65
char 返回与ASCII码相应的字符 char(65) A
lower 将字符串转换成小写 lower ('SQL Course') sql course
upper 将字符串转换成 upper('SQL Course') SQL COURSE
initcap 将字符串转换成每个单词以大写开头 initcap('SQL course') SQL Course
concat 连接两个字符串 concat('SQL', ' Course') SQL Course
substr 给出起始位置和长度,返回子字符串 substr('String',1,3) Str
length 求字符串的长度 length('Wellcom') 7
trim 在一个字符串中去除另一个字符串 trim('S' FROM 'SSMITH') MITH
replace 用一个字符串替换另一个字符串中的子字符串 replace('ABC', 'B', 'D') ADC

如果不知道表的字段内容是大写还是小写,可以转换后比较。

	select empno, ename,deptno from emp where lower(ename)='blake';

显示名称以“W”开头的雇员,并将名称转换成以大写开头。

	select empno,initcap(ename),job from emp wher substr(ename,1,1)='W';

显示雇员名称中包含“S”的雇员名称及名称长度。

	select empno,ename,legth(ename) from emp where instr(ename,'S',1,1)>0;

Oracle第五章——函数

日期型函数

Oracle使用内部数字格式来保存时间和日期,包括世纪、年、月、日、小时、分、秒。缺省日期格式为 DD-MON-YY,如“08-05月-03”代表2003年5月8日。

  • SYSDATE:返回系统日期和时间的虚列函数。

如:返回系统的当前日期。

	SELECT sysdate FROM dual;
  • 对两个日期相减,得到相隔天数。

通过加小时来增加天数,24小时为一天,如12小时可以写成12/24(或0.5)。

如:例1 假定当前的系统日期是2003年2月6日,求再过1000天的日期。

	SELECT sysdate+1000 AS "NEW DATE" FROM dual;

例2:两个日期相减

	 select to_date('1-1月-2000') - to_date('1-8月-1999') from dual;
  • 其它日期函数
函数 功能 实例 结果
months_between 返回两个日期间的月份 months_between (‘04-11月-05’,‘11-1月-01’)57.7741935
add_months 返回把月份数加到日期上的新日期 add_months(‘06-2月-03’,1),add_months(‘06-2月-03’,-1) 06-3月-03,06-1月-03
next_day 返回指定日期后的星期对应的新日期 next_day(‘06-2月-03’,‘星期一’) 10-2月-03
last_day 返回指定日期所在的月的最后一天 last_day(‘06-2月-03’) 28-2月-03
round 按指定格式对日期进行四舍五入 round(to_date(‘13-2月-03’),‘YEAR’),round(to_date(‘13-2月-03’),‘MONTH’),round(to_date(‘13-2月-03’),‘DAY’) 01-1月-03,01-2月-03,16-2月-03(按周四舍五入)

如:返回2003年2月的最后一天。

	SELECT last_day('08-2月-03') FROM dual;

假定当前的系统日期是2003年2月6日,显示部门10雇员的雇佣天数。

	SELECT ename, round(sysdate-hiredate) DAYS FROM emp WHERE  deptno = 10;

转换函数

函数 功能 实例 结果
To_char 转换成字符串类型 To_char(1234.5, ‘$9999.9’) $1234.5
To_date 转换成日期类型 To_date(‘1980-01-01’, ‘yyyy-mm-dd’) 01-1月-80
To_number 转换成数值类型 To_number(‘1234.5’) 1234.5
  • 自动类型转换
1
2
SELECT '12.5'+11 FROM dual;    //结果为:23.5
Select ‘12.5’||11 from dual; //结果为:’12.511’
  • 日期类型转换
代码 代表的格式 例子
AM、PM 上午、下午 08 AM
D 数字表示的星期(1~7) 1,2,3,4,5,6,7
DD 数字表示月中的日期(1~31) 1,2,3,…,31
MM 两位数的月份 01,02,…,12
Y、YY、YYY、YYYY 年份的后几位 3,03,003,2003
RR 解决Y2K问题的年度转换
DY 简写的星期名 MON,TUE,FRI,…
DAY 全拼的星期名 MONDAY,TUESDAY,…
MON 简写的月份名 JAN,FEB,MAR,…
MONTH 全拼的月份名 JANUARY,FEBRUARY,…
HH、HH12 12小时制的小时(1~12) 1,2,3,…,12
HH24 24小时制的小时(0~23) 0,1,2,…,23
MI 分(0~59) 0,1,2,…,59
SS 秒(0~59) 0,1,2,…,59
,./-;: 原样显示的标点符号
‘TEXT’ 引号中的文本原样显示 TEXT

如:1、日期型转字符型

将日期转换成带时间和星期的字符串并显示。

	SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

将日期显示转换成中文的年月日。

	SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

2.字符型转日期型

往emp表中插入一条记录

insert into emp values(8888,'张三','CLERK',7369,to_date('1-1月-2000'),1000,10,10);
insert into emp values(8889,'李四','CLERK',7369,to_date('2000-01-01','YYYY-MM-DD'),1000,10,10);

其他常用函数

函数 功能 实例 结果
nvl 空值转换函数 nvl(null, ‘空’)
decode 实现分支功能 decode(1,1, ‘男’, 2, ‘女’)
userenv 返回环境信息 userenv(‘LANGUAGE’) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest 返回参数的最大值 greatest(20,35,18,9) 35
least least返回参数的最小值 least(20,35,18,9) 9

1.空值的转换

如果对空值NULL不能很好的处理,就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是,在查询雇员表时,将工资sal字段和津贴字段comm进行相加,如果津贴为空,则相加结果也为空,这样容易引起误解。

使用nvl函数,可以转换NULL为实际值。该函数判断字段的内容,如果不为空,返回原值;为空,则返回给定的值。

如下3个函数,分别用新内容代替字段的空值:

	nvl(comm, 0):用0代替空的Comm值。
	nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。
	nvl(job, '无'):用“无”代替空的职务。

使用nvl函数转换空值。

	SELECT	ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM	 emp;

2.decode函数

decode函数可以通过比较进行内容的转换,完成的功能相当于分支语句。 在参数的最后位置上可以存在单独的参数,如果以上比较过程没有找到匹配值,则返回该参数的值,如果不存在该参数,则返回NULL。

将职务转换成中文显示。

	SELECT	ename,decode(job, 'MANAGER', '经理', 'CLERK','职员', 'SALESMAN','推销员', 'ANALYST','系统分析员','未知') FROM emp;

3.最大、最小值函数

greatest返回参数列表中的最大值,least返回参数列表中的最小值。

如果表达式中有NULL,则返回NULL。

Oracle第六章——相等、外连接

相等连接

1、三个步骤

A、先列出要显示的列: select ename,job,comm,emp,deptno,dname

B、列出查询的表: from emp,dept

C、列出多表相连条件(主外键):where emp.deptno=dept.deptno

注意:如果两个表有同名列,那么前面必须接表名 如: emp.deptno ,如果不是同名字段则表名可以省略

2、inner join 的写法

select enaem,job,sal,comm,emp.deptno,dname from emp inner join dept on emp.deptno = dept.deptno;

3、三表或三表以上的写法

select 字段1,字段2 , 字段3 。。。。from 表1,表2,表3.。。where 表1.外键 = 表2.主键  and 表1.外键 = 表3.主键 and 。。。

注意:两个表有一个条件 ,三个表有两个条件 ,四个表有三个条件 以此类推

外连接(不等连接)

左外连接即在内连接的基础上,左边表中有但右边表中没有的记录也以null的形式显示出来,右外连接则反之

1、写法1

(右外连接)

select ename,d.deptno,dname from emp e,dept d where e.deptno(+) = d.deptno

(左外连接)

select ename,d.deptno,dname from emp e,dept d where d.deptno = e.deptno(+)    

2、写法2

select ename,d.deptno,dname from emp e right join dept d on e.deptno = d.deptno  

Oracle第七章——连接、分组查询

不等连接

拿一个表作为另一表的查询条件或范围

如:显示雇员名称,工资和所属工资等级。

select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

自连接

自连接就是一个表,同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本),可以通过不同的别名区别两个相同的表(其它就是内连接)

如:显示雇员名称和雇员的经理名称

select worker.ename||'的经理是'||manager.ename as 雇员经理 from emp worker,emp manager where worker.mgr=manager.empno;

组函数

  • 组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。
  • 组函数也可以称为统计函数。
  • 组函数忽略列的空值。
  • 对组可以应用组函数。
  • 在组函数中可使用DISTINCT或ALL关键字。
  • ALL表示对所有非NULL值(可重复)进行运算。
  • DISTINCT 表示对每一个非NULL值,如果存在重复值,则组函数只运算一次。如果不指明上述关键字,默认为ALL。
函数 说明
AVG 求平均值
COUNT 求计数值,返回非空行数,*表示返回所有行
MAX 求最大值
MIN 求最小值
SUM 求和
SIDDEV 求标准偏差,是根据差的平方根得到的
VARIANCE 求统计方差

分组查询

1、如:按职务统计工资总和。

select deptno,job,sum(sal) from emp group by deptno,job;

2、多列分组

如:按部门和职务分组统计工资总和:

select deptno,job,sum(sal) from emp group by deptno,job;

3、HAVING

HAVING从句过滤分组后的结果,它只能出现在GROUP BY从句之后,而WHERE从句要出现在GROUP BY从句之前。

如:统计各部门的最高工资,排除最高工资小于3000的部门。

select deptno,max(sal) from emp group by deptno having max(sal)>=3000;

4、分组统计结果排序

可以使用ORDER BY从句对统计的结果进行排序,ORDER BY从句要出现在语句的最后。

如:按职务统计工资总和并排序。

select job 职务, sum(sal) 工资总和 from emp group by job order by sum(sal);

5、组函数的嵌套使用

如:求各部门平均工资的最高值。

select max(avg(sal)) from emp group by deptno;

Oracle第八章——子查询

子查询

通过把一个查询的结果作为另一个查询的一部分,子查询一般出现在SELECT语句的WHERE子句中,Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行,结果作为主查询的条件,在书写上要用圆括号扩起来,并放在比较运算符的右侧。

1、单行子查询

如:查询比SCOTT工资高的雇员名字和工资。

select ename,sal from emp where sal>(select sal from emp where empno=7788);

2、多行子查询*

如果子查询返回多行的结果,则我们称它为多行子查询。多行子查询要使用不同的比较运算符号,它们是IN、ANY和ALL。

如:查询工资低于任意一个“CLERK”的工资的雇员信息。

select empno,ename,job,sal from emp where sal < any (select sal from emp where job = 'CLERK') and job <> 'CLERK';

如: 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。

select empno,ename,job from emp where job in (select job from emp where deptno = 10) and deptno = 20;

3.多列子查询

**如果子查询返回多列,则对应的比较条件中也应该出现多列,这种查询称为多列子查询。以下是多列子查询的训练实例。 ** 如: 查询职务和部门与SCOTT相同的雇员的信息。

select empno, ename,sal from emp where (job,deptno) = (select job,deptno from emp where empno = 7788);

4.在FROM从句中使用子查询

在FROM从句中也可以使用子查询,在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员,很容易想到的是使用rownum虚列。比如我们要求显示雇员表中6~9位置上的雇员,可以用以下方法

如:查询雇员表中排在第6~9位置上的雇员。

select ename, sal, from (select rownum as num,ename,sal from emp where rownum<=9) where num>=6;

集合运算

操作 描述
union 并集,合并两个操作的结果,去掉重复的部分
union all 并集,合并两个操作的结果,保留重复的部分
minus 差集,从前面的操作结果中去掉与后面操作结果相同的部分
intersect 交集,取两个操作结果中相同的部分

如:查询部门10和部门20的所有职务。

select job from emp where deptno = 10 
union
select job from emp where deptno = 20;

如:查询只在部门表中出现,但没有在雇员表中出现的部门编号。

select deptno from dept
minus
select deptno from emp;

Oracle第九章——增删改、序列、事务

增删改

增: insert into 表名(列名) values (值);

删: delete from 表名 where 条件;

改: update 表名 set 列名1=值1,列名2=值2... where 条件;

复制数据

1、通过一条查询语句创建一个新表(要求目标表不存在)

create table manager as select empno,ename,sal, from emp where job= 'CLERK';

2、通过一条查询语句复制数据(要求目标表必须已建好)

insert into manager select empno,ename,sal from emp where job = 'CLERK';

序列

1、创建序列

如:创建从2000起始,增量为1 的序列abc:

create sequence abc increment by 1 start with 2000
maxvalue 99999 cycle nocache;

2、使用序列

序列名.nextval: 代表下一个值

序列名.currval: 代表当前值

如:

insert into manager values(abc.nextval,'小王',2500);
insert into manager values(abc.nextval,'小赵',2800);

事务

    两次连续成功的COMMIT或ROLLBACK之间的操作,称为一个事务。在一个事务内,数据的修改一起提交或撤销,如果发生故障或系统错误,整个事务也会自动撤销
数据库事务处理可分为隐式和显式两种。显式事务操作通过命令实现,隐式事务由系统自动完成提交或撤销(回退)工作,无需用户的干预。

1、隐式提交的情况包括:

    当用户正常退出SQL*Plus或执行CREATE、DROP、GRANT、REVOKE等命令时会发生事务的自动提交。

2、显示事务:

COMMIT	        数据库事务提交,将变化写入数据库
ROLLBACK	数据库事务回退,撤销对数据的修改
SAVEPOINT	创建保存点,用于事务的阶段回退

Oracle第十章————建表

建表

格式:

create table 表名
      (
	  列名1   类型   约束,
	  列名2   类型   约束,
	  ......			
      );

如:

– 创建出版社表

create table 出版社(
	编号 varchar2(2),
	出版社名称 varchar2(30),
	地址 varchar2(30),
	联系电话 varchar2(20)
);

– 创建图书表

create table 图书 (
	图书编号 VARCHAR2(5),
	图书名称 VARCHAR2(30),
	出版社编号 VARCHAR2(2),
	作者 VARCHAR2(10),
	出版日期 DATE,
	数量 NUMBER(3),
	单价 NUMBER(7,2)	
);

通过子查询建表

步骤1:完全复制图书表到“图书1”

create table 图书1 as select * from 图书;

步骤2:创建新的图书表“图书2”,只包含书名和单价

create table 图书2(书名,单价) as seelct 图书名称,单价 from 图书;

步骤3:创建新的图书表“图书3”,只包含书名和单价,不复制内容

create table 图书3(书名,单价) as select 图书名称,单价 from 图书 where 1=2;

添加表的约束

主键     primary key      PK
唯一     unique           UQ
默认值   default          DF
检查约束 check            CK
外键约束 foreign key      FK

方法一:建表的同时添加约束

如:

create table stuinfo(
 sno int primary key not null,       --主键
 sname varchar2(10) unique not null,       --唯一
 sex char(2) default '男' check(sex='男' or sex = '女') not null,   --默认及检查
 saddress varchar2(50) not null,
 phone char(11),
 email varchar2(50)
);
create table stumarks(
 marksId int,
 sno int references stuinfo(sno) not null,     --外键
 score number(5,1),
 examDate date default sysdate
);

方法二:建表完成后,再添加约束

如:(之前已建好了出版社表及图书表)

–主键约束

alter table 出版社 add constraint PK_编号 primary key (编号);

–唯一约束

alter table 出版社 add constraint UQ_地址 unique (地址);

–检查约束

alter table 出版社 add constraint CK_联系电话 check (联系电话 like '1%');

–默认值

alter table 出版社 modify 地址 default '湘潭';

–外键约束

alter table 图书 add constraint FK_图书编号 foreign key (图书编号) references 出版社(编号);

–外键约束

alter table 图书 add constraint FK_图书编号 foreign key (图书编号) references 出版社(编号);

查看约束条件

数据字典USER_CONSTRAINTS中包含了当前模式用户的约束条件信息。其中,CONSTRAINTS_TYPE 显示的约束类型为:

		
C:CHECK约束。
P:PRIMARY KEY约束。
U:UNIQUE约束。
R:FOREIGN KEY约束。

其他信息可根据需要进行查询显示,可用DESCRIBE命令查看USER_CONSTRAINTS的结构。

如:检查表的约束信息:

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCHCONDITON
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='图书';

删除约束条件

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

表的操作

1、删除表    drop table 表名
2、重命名表  RENAME 表名 TO 新表名;
3、查看表

可以通过对数据字典USER_OBJECTS的查询,显示当前模式用户的所有表。

如: 显示当前用户的所有表。

SELECT object_name FROM user_objects WHERE object_type='TABLE';

修改表

1、增加新列:

如: 为“出版社”增加一列“电子邮件”:

ALTER TABLE 出版社		
ADD 电子邮件 VARCHAR2(30) CHECK(电子邮件 LIKE '%@%');

2、修改列

修改列定义有以下一些特点:

(1) 列的宽度可以增加或减小,在表的列没有数据或数据为NULL时才能减小宽度。
(2) 在表的列没有数据或数据为NULL时才能改变数据类型,CHAR和VARCHAR2之间可以随意转换。
(3) 只有当列的值非空时,才能增加约束条件NOT NULL。
(4) 修改列的默认值,只影响以后插入的数据。如:修改“出版社”表“电子邮件”列的宽度为40。
ALTER TABLE 出版社 MODIFY 电子邮件 VARCHAR2(40);

3、删除列

如:删除“出版社”表的“电子邮件”列。

ALTER TABLE 出版社 DROP COLUMN 电子邮件;

Oracle第十一章————视图

分区表

在某些场合会使用非常大的表,比如人口信息统计表。如果一个表很大,就会降低查询的速度,并增加管理的难度。一旦发生磁盘损坏,可能整个表的数据就会丢失,恢复比较困难。根据这一情况,可以创建分区表,把一个大表分成几个区(小段),对数据的操作和管理都可以针对分区进行,这样就可以提高数据库的运行效率。分区可以存在于不同的表空间上,提高了数据的可用性。例:创建和使用分区表。

如:创建按成绩分区的考生表,共分为3个区:

		
CREATE TABLE 考生 (
	考号 VARCHAR2(5),
	姓名 VARCHAR2(30),
	成绩 NUMBER(3)
	)
PARTITION BY RANGE(成绩)
(PARTITION A VALUES LESS THAN (300)
TABLESPACE USERS,
PARTITION B VALUES LESS THAN (500)
TABLESPACE USERS,
PARTITION C VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS	
);

步骤3:检查A区中的考生:

SELECT *  FROM  考生 PARTITION(A);

步骤4:检查全部的考生:

SELECT *  FROM  考生;

视图

1、视图的概念

视图不同于表,视图本身不包含任何数据。而视图只是一种定义,对应一个查询语句。视图的数据都来自于某些表,这些表被称为基表。

视图可以在表能够使用的任何地方使用,但在对视图的操作上同表相比有些限制,特别是插入和修改操作。对视图的操作将传递到基表,所以在表上定义的约束条件和触发器在视图上将同样起作用。2、视图的创建

2、格式:

create [or replace] view 视图名 
as
select 语句;

例:创建图书作者视图:

CREATE VIEW 图书作者(书名,作者) 		
AS SELECT 图书名称,作者 FROM 图书;

查询视图全部内容

SELECT * FROM 图书作者;    

查询部分视图:

SELECT 作者 FROM 图书作者;

删除视图:

DROP VIEW 清华图书;

3.创建只读视图

创建只读视图要用WITH READ ONLY选项。

例:创建emp表的经理视图:

CREATE OR REPLACE VIEW manager 
AS SELECT * FROM emp WHERE job= 'MANAGER'
WITH READ ONLY;

4.使用WITH CHECK OPTION选项

使用WITH CHECK OPTION选项。使用该选项,可以对视图的插入或更新进行限制,即该数据必须满足视图定义中的子查询中的WHERE条件,否则不允许插入或更新。

例:

CREATE OR REPLACE VIEW 清华图书 		
AS SELECT * FROM 图书 WHERE 出版社编号= '01'

WITH CHECK OPTION;注:插入数据时,由于带了with check option的选项,则只能插入出版社编为’01’的数据

5.来自基表的限制

除了以上的限制,基表本身的限制和约束也必须要考虑。如果生成子查询的语句是一个分组查询,或查询中出现计算列,这时显然不能对表进行插入。另外,主键和NOT NULL列如果没有出现在视图的子查询中,也不能对视图进行插入。在视图中插入的数据,也必须满足基表的约束条件。

6.视图的查看

USER_VIEWS字典中包含了视图的定义。

USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、删除。

USER_OBJECTS字典中包含了用户的对象。

可以通过DESCRIBE命令查看字典的其他列信息。

例:查看用户拥有的视图:

SELECT object_name FROM user_objects WHERE object_type='VIEW';

Oracle第十二章——索引、同义词、数据库链接、PL/SQL语句

索引

索引(INDEX)是为了加快数据的查找而创建的数据库对象,特别是对大表,索引可以有效地提高查找速度,也可以保证数据的惟一性

创建索引一般要掌握以下原则:只有较大的表才有必要建立索引,表的记录应该大于50条,查询数据小于总行数的2%~4%。虽然可以为表创建多个索引,但是无助于查询的索引不但不会提高效率,还会增加系统开销。因为当执行DML操作时,索引也要跟着更新,这时索引可能会降低系统的性能。

创建索引:

 CREATE INDEX 索引名 ON 表名(列名);

删除索引:

  DROP INDEX 索引名;

同义词

同义词(SYNONYM)是为模式对象起的别名,可以为表、视图、序列、过程、函数和包等数据库模式对象创建同义词。

创建私有同义词:

  CREATE SYNONYM BOOK FOR 图书;

创建公有同义词(先要获得创建公有同义词的权限):

  CREATE PUBLIC SYNONYM BOOK FOR SCOTT.图书;

删除同义词:

DROP SYNONYM 同义词名;

数据库链接

数据库链接(DATABASE LINK)是在分布式环境下,为了访问远程数据库而创建的数据通信链路。

格式:

 CREATE DATABASE LINK 链接名 CONNECT TO 账户 IDENTIFIED BY 口令 USING 服务名;

数据库链接一旦建立并测试成功,就可以使用以下形式来访问远程用户的表。

	表名@数据库链接名

PL/sql

1、块结构和基本语法要求

块中各部分的作用解释如下:

	(1)  DECLARE:声明部分标志。
	(2)  BEGIN:可执行部分标志。
	(3)  EXCEPTION:异常处理部分标志。
	(4)  END;:程序结束标志。

2、输出

第一种形式:

	DBMS_OUTPUT.PUT(字符串表达式);

第二种形式:

	DBMS_OUTPUT.PUT_LINE(字符串表达式);

第三种形式:

	DBMS_OUTPUT.NEW_LINE;

3、变量赋值:

第一种形式:

SELECT 列名1,列名2... INTO 变量1,变量2... FROM 表名 WHERE 条件;第二种形式:变量名:=值

例:查询雇员编号为7788的雇员姓名和工资。

SET SERVEROUTPUT ON		--在命令行界面必须写
DECLARE--定义部分标识		
 v_name  VARCHAR2(10);	--定义字符串变量v_name
 v_sal   NUMBER(5);	--定义数值变量v_sal
BEGIN			--可执行部分标识SELECT	 ename,sal INTO v_name,v_sal 
 FROM emp 
 WHERE empno=7788;--在程序中插入的SQL语句
DBMS_OUTPUT.PUT_LINE('7788号雇员是:'||v_name||',工资为:'||to_char(v_sal));
		--输出雇员名和工资		
END;	

4、结合变量的定义和使用(即全局变量)

该变量是在整个SQLPlus环境下有效的变量,在退出SQLPlus之前始终有效,所以可以使用该变量在不同的程序之间传递信息。结合变量不是由程序定义的,而是使用系统命令VARIABLE定义的。**

例:定义并使用结合变量

步骤1:输入和执行下列命令,定义结合变量g_ename:

--SET SERVEROUTPUT ON 
	VARIABLE  g_ename VARCHAR2(100)		
BEGIN
	:g_ename:=:g_ename|| 'Hello~ ';			--在程序中使用结合变量
	DBMS_OUTPUT.PUT_LINE(:g_ename);                --输出结合变量的值
END;

5.记录变量的定义

还可以根据表或视图的一个记录中的所有字段定义变量,称为记录变量。记录变量包含若干个字段,在结构上同表的一个记录相同,定义方法是在表名后跟%ROWTYPE。记录变量的字段名就是表的字段名,数据类型也一致。

如:

 v_name emp.ename%TYPE;

Oracle第十三章——PL/SQL空值语句、游标

IF语句

1、IF-THEN-END IF形式

   IF 条件 then 
       语句集;
   END IF;

2、IF-THEN-ELSE-END IF形式

   IF 条件 then 
       语句集1;
   ElSE
       语句集2
   END IF;

3.IF-THEN-ELSIF-ELSE-END IF形式

   IF 条件1 THEN
	语句集1;
   ELSIF 条件2 THEN
	语句集2;
   ELSIF 条件3 THEN
	语句集3;
   ...
   ELSE
	语句集n;
   END IF;

CASE语句

1.基本CASE结构

   CASE 变量或表达式
   When 值1 then 结果1;
   When 值2 then 结果2;
   When 值3 then 结果3;
   ...
   ELSE 结果n;
   END CASE;

2.搜索CASE结构

  CASE 
   When 条件1 then 结果1;
   When 条件2 then 结果2;
   When 条件3 then 结果3;
   ...
   ELSE 结果n;
   END CASE;

循环

1.基本LOOP循环

   loop
      语句集;
   exit when 条件
      语句集;
   end loop;

2.FOR LOOP循环

FOR循环是固定次数循环,格式如下:

	
FOR 控制变量 in [REVERSE] 下限..上限 		
LOOP  		 
	语句集;  		
END LOOP;

注:循环控制变量是隐含定义的,不需要声明。

下限和上限用于指明循环次数。正常情况下循环控制变量的取值由下限到上限递增,REVERSE关键字表示循环控制变量的取值由上限到下限递减。

3.WHILE LOOP循环

   while 条件 loop
       语句集;
   end loop;

游标

1、概念

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

游标有两种类型:显式游标和隐式游标。

在前述程序中用到的SELECT…INTO…查询语句,一次只能从数据库中提取一行数据,系统都会使用一个隐式游标。

显式游标对应一个返回结果为多行多列的SELECT语句。

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

2、隐式游标属性

隐式游标的属性 返回值类型 意义
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假

如:使用隐式游标的属性,判断对雇员工资的修改是否成功。

SET SERVEROUTPUT ON 		
BEGIN  		
UPDATE emp SET sal=sal+100 WHERE empno=1234;		 
IF SQL%FOUND THEN  		
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!');  		
COMMIT;  		
ELSEDBMS_OUTPUT.PUT_LINE('修改雇员工资失败!');		 
END IF; 		
END;

3、显式游标

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

a.声明游标

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

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

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

b.打开游标

在可执行部分,按以下格式打开游标:

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

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

c.提取数据

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

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

FETCH 游标名 INTO 记录变量;

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

定义记录变量的方法如下:

     变量名 表名|游标名%ROWTYPE;

d.关闭游标

CLOSE 游标名;

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

【例1】 用游标提取emp表中7788雇员的名称和职务。

SET SERVEROUTPUT ON	     --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
DECLARE		
    v_ename VARCHAR2(10);	
    v_job VARCHAR2(10);	
    CURSOR emp_cursor IS 
 SELECT ename,job FROM emp WHERE empno=7788;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO v_ename,v_job;	
    DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
    CLOSE emp_cursor;	
END;

【例2】 用游标提取emp表中7788雇员的姓名、职务和工资。

SET SERVEROUTPUT ON	     --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
DECLARE	 
	CURSOR emp_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;
	emp_record emp_cursor%ROWTYPE;
	    --用游标定义记录变量	
BEGIN 
	OPEN emp_cursor;	
	FETCH emp_cursor INTO emp_record;  
	 DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);	
	 CLOSE emp_cursor;	
END;

【例3】 显示工资最高的前3名雇员的名称和工资。

SET SERVEROUTPUT ON		   --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
DECLARE	
	 V_ename VARCHAR2(10);	
	V_sal NUMBER(5);	
	CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;	
BEGIN	
	 OPEN emp_cursor;
	 FOR I IN 1..3 LOOP  
		 FETCH emp_cursor INTO v_ename,v_sal;
		 DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
  END LOOP;
   CLOSE emp_cursor;
END;

4、游标循环(重点)

方法一:使用特殊的FOR循环形式显示全部雇员的编号和名称(省略掉定义记录变量、打开游标、提取数据、关闭游标)。

SET SERVEROUTPUT ON     --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
DECLARE
 	CURSOR emp_cursor IS 
 SELECT empno, ename FROM emp;
BEGIN	
	FOR Emp_record IN emp_cursor LOOP   
		 DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
	END LOOP;
END;

方法二:最简单方式

SET SERVEROUTPUT ON      --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
BEGIN	 FOR re IN (SELECT ename FROM EMP)  LOOP
		DBMS_OUTPUT.PUT_LINE(re.ename)
	END LOOP;
END;

5、利用游标属性做循环条件

【训练1】 使用游标的属性练习。

SET SERVEROUTPUT ON         --在命令行界面是必须的,在第三方工具的SQL界面中无需此语句
DECLARE
  V_ename VARCHAR2(10);
  CURSOR emp_cursor IS 
  SELECT ename FROM emp;
BEGIN	
  OPEN emp_cursor;	 
  IF emp_cursor%ISOPEN THEN
  LOOP
    FETCH emp_cursor INTO v_ename;	        
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);	        END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!');
  END IF;         
  CLOSE  emp_cursor;
END;

Oracle第十四章——游标、存储过程

游标参数的传递

例:

SET SERVEROUTPUT ON
DECLARE
    V_empno NUMBER(5);
	V_ename VARCHAR2(10);
	CURSOR 	emp_cursor(p_deptno NUMBER,p_job VARCHAR2) IS SELECT empno,ename FROM emp WHERE	deptno = p_deptno AND job = p_job;
BEGIN
 	OPEN emp_cursor(10, 'CLERK');
  	LOOP
  	   FETCH emp_cursor INTO v_empno,v_ename;
  	EXIT WHEN emp_cursor%NOTFOUND;
  	   DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
	END LOOP;
END; 

异常处理

错误处理的语法如下:

     EXCEPTION
	WHEN 错误1[OR 错误2] THEN 语句序列1;
	WHEN 错误3[OR 错误4] THEN 语句序列2;
	...
	WHEN OTHERS 语句序列n;
      END;

例:SET SERVEROUTPUT ON

DECLARE
    v_name VARCHAR2(10);
BEGIN
    SELECT	ename  INTO v_name  FROM emp  WHERE	empno = 1234;
DBMS_OUTPUT.PUT_LINE('该雇员名字为:'|| v_name);
EXCEPTION
	WHEN NO_DATA_FOUND THEN	DBMS_OUTPUT.PUT_LINE('编号错误,没有找到相应雇员!');
	WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其他错误!');
END;

存储过程

创建和删除存储过程

格式:

 CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
	[说明部分]   --定义需要使用的临时变量
BEGIN
	语句集;
	[EXCEPTION]
	    [错误处理部分]
END [过程名];

删除:

drop procedure 存储过程名;

调用存储过程

方法1:

  EXECUTE 模式名.存储过程名[(参数...)];   (适用于命今行窗口及sql窗口)

方法2: (适用于sql窗口)

	BEGIN
	      模式名.存储过程名[(参数...)];
	END;

例:编写显示雇员信息的存储过程EMP_LIST,并引用EMP_COUNT存储过程(无参存储过程 )。

CREATE OR REPLACE PROCEDURE EMP_LIST
AS
       CURSOR emp_cursor IS SELECT empno,ename FROM emp;
BEGIN
	FOR Emp_record IN emp_cursor LOOP   
		DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
	END LOOP;
	EMP_COUNT;
END;

调用:begin
	EMP_LIST;
          end;

参数传递

a.输入参数: 参数名 IN 数据类型 DEFAULT 值;

例:编写给雇员增加工资的存储过程CHANGE_SALARY,通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。

	CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)  --形参P_EMPNO及P_RAISE
	AS
	   V_ENAME VARCHAR2(10);
	   V_SAL NUMBER(5);
	BEGIN
	   SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
	   UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
	   DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));
	   COMMIT;
	EXCEPTION
	  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');
	  ROLLBACK; --如果出了异常则撤消
	END;

调用:begin
	CHANGE_SALARY(7788,80)
      end;

b.输出参数: 参数名 OUT 数据类型 DEFAULT 值;

    --例:统计雇员的人数
       CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)   --P_TOTAL为输出参数
	AS
   BEGIN
	SELECT COUNT(*) INTO P_TOTAL FROM EMP;
   END;

调用:DECLARE
	 V_EMPCOUNT NUMBER;   --定义变量接收过程求出的结果
      BEGIN
	 EMP_COUNT(V_EMPCOUNT);
	 DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);
      END;

c.输入输出参数: 参数名 IN OUT 数据类型 DEFAULT 值;

	--例:使用IN OUT类型的参数,给电话号码增加区码。
	CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
	AS
	BEGIN
	   P_HPONE_NUM:='024-'||P_HPONE_NUM;
	END;

调用: DECLARE
	 V_PHONE_NUM VARCHAR2(15);
       BEGIN
	 V_PHONE_NUM:='26731092';
	 ADD_REGION(V_PHONE_NUM);
	 DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);
       END;



本文链接: http://home.meng.uno/articles/33c755f8/ 欢迎转载!

© 2018.02.08 - 2020.10.14 Mengmeng Kuang  保留所有权利!

UV : | PV :

:D 获取中...

Creative Commons License