不得不说这个排版真心难弄啊,明明都在word里弄好了,放过来就惨不忍睹啊,光是弄图片就整死人,上传图片附件还只能一张一张上,第一篇博客发出来不容易。看到那些排的漂漂亮亮的博文,此刻才体会到他们发帖的不容易!原文word版在附件。
1.常见的数据库
1. 关系型数据库:db2,oracle,mysql,MS access,MS sql server
2. 嵌入型数据库:sqlite
3. OOP的数据库:db4o
4. 大型分布式数据库:Hadoop
2. Oracle关键服务
*通配符
OracleService*: 核心服务(必须启动)
*TNSListener: 监听器服务,核心服务,在使用远程访问或PL/SQL Developer工具时必须启动
*Controleorcl:数据库控制台服务,需要使用企业管理器的时候必须启动
*RecoveryService:恢复、闪回等操作需要开启该服务
*ClrAgent:Oracle数据库.NET扩展服务的一部分。 (非必须启动)
3. 表空间
3.1 概念
数据库由若干表空间构成,表空间由一到多个数据文件组成,每个数据文件只能属于同一表空间。
3.2 分类
永久性表空间:一般保存表、视图、过程和索引等的数据
临时性表空间:只用于保存系统中短期活动的数据
撤销表空间:用来帮助回退未提交的事务数据
3.3 表空间的使用
-- 创建表空间 createtablespace test1 datafile'D:\Oracle\data_ripin\test01.dbf' size20M autoextendonnext10M;
-- 调整表空间大小,想表空间内添加数据文件 ALTERTABLESPACE test1 ADDDATAFILE'D:\Oracle\data_ripin\test02.dbf' SIZE20M AUTOEXTENDON;
-- 查看表空间 select file_name,tablespace_name,bytes,autoextensible FROM dba_data_files WHERE tablespace_name='TEST1';
-- 删除表空间 DROPTABLESPACE TEST1; DROPTABLESPACE TEST1 INCLUDINGCONTENTSANDDATAFILES; |
4. 用户与权限
4.1系统用户
sys用户:超级用户,主要用来维护系统信息和管理实例,以SYSDBA或SYSOPER角色登录。
system用户: 默认的系统管理员,拥有DBA权限,通常用来管理Oracle数据库的用户、权限和存储,以Normal方式登录。
scott用户:示范用户,使用users表空间。
4.2用户与模式
模式为数据库对象(如表、索引等)的集合,oracle会为每个用户创建一个模式,和用户名一样。
4.3 创建用户与权限
-- 创建ripin用户 CREATEUSER RIPIN IDENTIFIEDBY ripin123 DEFAULTTABLESPACE TEST1 TEMPORARYTABLESPACE TEMP;
-- 查询ripin用户 SELECT*FROM DBA_USERS WHERE USERNAME='RIPIN';
-- 给用户授权及撤销权限 GRANTCONNECT,RESOURCETO RIPIN;-- 授予连接和资源2个角色 GRANTSELECTON SCOTT.Emp TO RIPIN;-- 允许用户查看scott用户的emp表的记录 GRANTUPDATEON SCOTT.Emp TO RIPIN;-- 授予用户更新scott用户的emp表记录权限 REVOKESELECTON SCOTT.EMP FROM RIPIN;-- 撤销用户查看emp表的权限 REVOKECONNECTFROM RIPIN;-- 撤销连接角色 撤销ripin用户的查询scott用户的emp表权限后: 撤销ripin用户的connect权限后:
-- 修改用户密码 ALTERUSER RIPIN IDENTIFIEDBY ripin925;
-- 删除用户(注意:正在连接的用户无法删除) DROPUSER RIPIN CASCADE; |
4.4 解锁scott用户以及修改密码
sys 登陆 sqlplus sys/orcl as sysdba
-- 解锁scott用户 alteruser scott accountunlock; -- 锁定指定用户 alteruser scott accountlock; -- 修改用户密码 alteruser scott identifiedby tiger; |
5. oracle数据类型
5.1 dual表
理解成一张只有一行记录的表.不存主题数据,我们也称为“伪表”,便于select特定对象.
5.2字符数据类型
CHAR:存储固定长度(无论存储的数据多长,都是占用初始化指定的空间)的字符串,单字节字符,长度为1-2000。
VARCHAR2:存储可变长度(存的数据多长就是占多少空间)的字符串,单字节字符,长度为1-4000。
NCHAR和NVARCHAR2:存储Unicode字符集类型(双字节字符),NCHAR长度为1-1000,NVARCHAR2长度为1-2000,(实际和char、varchar2储存的数据量是一样的)
Java的字符占用2个字节,是使用unicode编码的。
对于char(1)和varchar2(1)单字节,是存不了一个中文的
-- 创建表 CREATETABLE STUDENT( stu_no CHAR(5)PRIMARYKEYNOTNULL,-- 学号,主键,非空 stu_name VARCHAR2(1)NOTNULL,-- 姓名,非空 stu_id VARCHAR2(18),-- 身份证号,18位 stu_age NUMBER(3,0)-- 年龄,整数 ); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES(0321,'商','23423424',19); |
5.3数值数据类型
NUMBER:存储整数和浮点数,格式为NUMBER(p)或NUMBER(p, s),P为有效数位(小数点和-号不计),长度为1-38,s为小数点右边的数字位数(则有效的整数位‘‘不为0开始算’’为p-s位)。
5.4日期时间数据类型
DATE:存储日期和时间数据,设置格式:环境变量添加: nls_date_format的值为YYYY-MM-DD HH24:MI:SS
插入日期示例:insert into 表名 values(列值, ……,to_date( '2014-10-10 12:12:22','YYYY-MM-DD HH24:MI:SS'));可以此基础上去掉(值和格式都要去掉)秒,分,时,日,月等,分秒去掉默认为0,时分秒去掉,就只有年月日,日去掉默认为1号,月去掉默认为系统当前月。
TIMESTAMP:秒值精确到小数点后6位,
插入数据示例:insert into tt12 values(1,0.333333333,to_timestamp('2014-10-10 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF '));
可以此基础上去掉(值和格式都要去掉)秒,分,时,日,月等,分秒去掉默认为0,时分秒去掉,时默认为12点(可能跟设置有关),日去掉默认为1号,月去掉默认为系统当前月。
5.5 其它数据类型
VARCHAR、 INTEGER、FLOAT、DOUBLE、lob(BLOB、CLOB、BFILE、NCLOB)
lob:大对象,如文件、视频
6.伪列
末存储在表中,只能查询,不能增删改。
6.1Rowid
数据库中行的地址(唯一),可快速定位行。
插入一条数据的时候数据库会自动产生一个唯一的id,无论是哪个表中的id都不会重复,因为生成策略是机器码+时间戳
SELECT ROWID,字段名……FROM 表名;
rowid 可以在查询时通过行ID去查找,尤其是部分重复数据行,用行ID查找就很方便
删除重复行:
DELETEFROM表 WHEREROWIDNOTIN (SELECT MIN(ROWID) FROM表 GROUPBY所有字段); |
6.2Rownum
给查询结果返回一个数值表示次序,可以用来限制返回的条数,例如分页查询。
SELECT E.*,ROWNUMFROM SCOTT.EMP E WHEREROWNUM<10;
SELECTROWNUM,E.*FROM SCOTT.EMP E WHEREROWNUM<10ANDROWNUM>3;-- 结果是一条数据都没有 SELECT *FROM(SELECTROWNUM RN,E.*FROM SCOTT.EMP E WHEREROWNUM<10)WHERE RN>3; 这样写将判断条件全部写到最后也行: SELECT*FROM(SELECTROWNUM RN,E.*FROM SCOTT.EMP E)WHERE RN>3AND RN<10;
|
注意:
查询表数据时,不能先进行ROWNUM>整数值的条件查询,此查询不被支持
使用ROWNUM分页查询数据时(即要求显示第几行到第几行),大于和小于判断不能在同一次查询中实现,首先执行小于(不能大于)条件查询,得到的数据再进行大于判断查询,这样子才能正确查询出结果(行数4~9)。
7. SQL语言简介
7.1 数据定义语言
数据定义语主要是表的create\alter\truncate\drop的使用
7.2 数据操作语言
数据操作语言是:对表的记录进行crud
-- 创建表 CREATETABLE STUDENT( stu_no CHAR(5)PRIMARYKEYNOTNULL,-- 学号,主键,非空 stu_name VARCHAR2(20)NOTNULL,-- 姓名,非空 stu_id VARCHAR2(18),-- 身份证号,18位 stu_age NUMBER(3,0)-- 年龄,整数 ); -- 插入记录 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0001','夏','23423424',419); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0002','商','23423425',219); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0003','周','23423426',319); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0004','秦','23423427',19); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0005','汉','23423428',419); -- 姓名和年龄都相同 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0006','汉','23423428',419); -- 姓名相同,年龄不同 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0007','汉','23423428',418);
-- 修改记录 UPDATE student set stu_name='三国'WHERE stu_no='0007';
-- 给student表添加字段 ALTERTABLE student ADD( stu_tel VARCHAR2(11), stu_address VARCHAR2(20) );
-- 修改表的字段的定义 ALTERTABLE student MODIFY( stu_address VARCHAR(50) );
-- 删除表的字段(注意:用的是drop而不是delete) ALTERTABLE student DROP( stu_tel );
-- 查询去重的数据,对于distinct后面接的所有字段对应的值全部相同的记录(有一个不同就会显示),只显示一条 SELECTDISTINCT stu_name,stu_age FROM student; -- 查询表 SELECT*FROM student;
--copy现有的表结构和数据,select后有多少字段决定新表有多少字段,主键不被copy CREATETABLE newStudent ASSELECT stu_no,stu_name,stu_age FROM STUDENT;
SELECT*FROM newStudent;
-- 删除表 DROPTABLE student;-- 删除表结构和数据 DROPTABLE newStudent; TRUNCATETABLE student;-- 删除表的所有数据,包括自增长的主键记录,但是表结构还在 |
7.3 事务操作语言
事务控制语句主要是由rollback、savepoint savepoint_name、rollback to savepoint savepoint_name、commit组成。
注意:回滚点命名不能使数字(无效)。
-- 插入记录 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0001','夏','23423424',419); SAVEPOINT A; INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0002','商','23423425',219); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0003','周','23423426',319); INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0004','秦','23423427',19); SAVEPOINT B; INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0005','汉','23423428',419);
SELECT*FROM student; ROLLBACKTOSAVEPOINT A;-- 记录回到回滚点A处,只有一条记录'0001' SELECT*FROM student;
-- 姓名和年龄都相同 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0006','汉','23423428',419);
SELECT*FROM student; ROLLBACK;-- 数据清空,记录数为0 SELECT*FROM student;
-- 姓名相同,年龄不同 INSERTINTO STUDENT (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0007','汉','23423428',418); -- 修改记录 UPDATE student set stu_name='三国'WHERE stu_no='0007';
COMMIT; SELECT*FROM student;-- 一条记录 |
7.4 数据控制语言
即权限问题,授权(GRANT...TO)用户操作指定表的权限以及取消授权(REVOKE ...FROM)。
-- 取消用户查询表student的权限,执行此操作,需管理员权限 REVOKESELECTON student FROM RIPIN; |
在system用户中,执行以上命令后,ripin用户失去了查询自己的student表的权限,太牛逼了
8.运算符
8.1算术运算符
+、-、*、/等略。
8.2比较(关系)运算符
=、!=、< 、 > 、 <= 、 >= 、 between...and... 、 in 、 like 、 is null。
is null判断:
存入的值为:null(空), ’’(空字符串)两种情况下,数据库字段的值为null
存入的值为:’null’(字符串), ’ ‘(多个空格的字符串)情况下,数据库字段的值不为null
8.3逻辑运算符
and | or | not。
8.4连接运算符 -- ||
-- 连接运算符 SELECT stu_name||'的统治时间是:'||stu_age FROM student;
|
8.5集合运算符
union(并集无重复)
union all(并集有重复)
intersect(交集,共有部分)
minus(减集,第一个查询具有,第二个查询不具有的数据)
-- 求并集 SELECT*FROM student UNIONSELECT*FROM student1; -- 两张表所有数据一起查出,有ORDER BY 时不能使用select *,必须是具体的字段 SELECT stu_no,stu_name,stu_id,stu_age FROM student UNIONALLSELECT stu_no,stu_name,stu_id,stu_age FROM student1 ORDERBY stu_no; SELECT*FROM student; SELECT*FROM student1; -- 求交集,得到数据没有改变的记录 SELECT*FROM student INTERSECTSELECT*FROM student1; --求减集,得到前一张表有,后一张表没有的数据,包括新增的记录和修改过的数据都是后一张表所没有的 SELECT*FROM student MINUSSELECT*FROM student1; -- 找到新增的记录,如果select 后接的不是值唯一的字段,那么就可能将修改的记录也查出来 SELECT stu_no FROM student1 MINUSSELECT stu_no FROM student; |
使用注意:
列数相关,对应列的数据类型兼容,不能含有Long类型的列,第一个select语句的列或别名作为结果标题
9. 常用函数
9.1多行函数(处理多行数据的函数)
sum:求和
avg:求平均数
count:计数
max:求最大值
min:求最小值
9.2转换函数(单行函数)
9.2.1 to_char
一般用来格式化日期数据或数值
-- 查询当前系统时间 SELECTSYSDATEFROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')FROM DUAL;
SELECT TO_CHAR(1234.55,'$9,999.0')FROM DUAL;
|
9.2.2 to_date
将字符串转为日期类型。
示例:insertinto表名 values(列值,……,to_date( '2014-10-10 12:12:22','YYYY-MM-DD HH24:MI:SS'));可以此基础上去掉(实测时间格式不去也没关系,但是涉及到日期时就必须去掉格式了)秒,分,时,日,月等,分秒去掉默认为0,时分秒去掉,就只有年月日,日去掉默认为1号,月去掉默认为系统当前月。
-- 字符串转成日期格式 SELECT TO_DATE('2014-11-1 1','YYYY-MM-DD HH24:MI:SS')FROM DUAL;
SELECT TO_DATE('2014','YYYY')FROM DUAL;
|
9.2.3 to_number
将字符串转为数字(通常能隐式转换)
-- 将字符串转换为数值 SELECT TO_NUMBER('112')+4FROM DUAL; SELECT'112'+4FROM DUAL;
|
9.3分析函数
分析函数可以每个组返回多行
rank:两个并列第一,然后就是第三名了,没有第二名
dense_rank:两个并列第一,然后就是第二名
row_number:没有并列第一,即使分数一样,仍然依次排序
-- 分析函数RANK 普通排序:1,1,3... SELECT s.*,RANK()OVER(PARTITIONBY stu_age ORDERBY stu_id)AS MYRANK FROM student s;
-- 分析函数DENSE_RANK 紧密排序:1,1,2,3.. SELECT s.*,DENSE_RANK()OVER(PARTITIONBY stu_age ORDERBY stu_id)AS MYRANK FROM student s;
-- 分析函数ROW_NUMBER 像rownum一样排序:1,2,3,4.. SELECT s.*,ROW_NUMBER()OVER(PARTITIONBY stu_age ORDERBY stu_id)AS MYRANK FROM student s;
|
9.4其它函数
NVL(EXP1,EXP2): EXP1的值不为null,返回自己,否则返回EXP2;
NVL2(EXP1,EXP2,EXP3): EXP1的值不为null,将返回EXP2,否则返回EXP3;
DECODE(VALUE,IF1,THEN1,IF2,THEN2,...,ELSE):如果value等于if1,则返回then1,如果value等于if2,则返回then2,...否则返回else的值.
10. 索引
创建索引后,增删改速度变慢,查询速度会加快
单列索引:给一个字段建立索引
组合索引:给几个字段建立索引
-- 创建唯一索引,该字段的数据若存在重复,则不能成功创建
CREATEUNIQUEINDEX index_stu_stu_age ONstudent(stu_age);-- 删除表的时候会自动把索引删除 -- 创建非唯一索引 CREATEINDEX index_stu_stu_age ON student(stu_age); |
11. 序列
语法:
CREATE SEQUENCE sequence_name [START WITH integer] [INCREMENT BY integer] [MAXVALUE integer| NOMAXVALUE] [MINVALUE integer| NOMINVALUE] [CYCLE| NOCYCLE] [CACHE integer|NOCACHE]; [order] --单实例不用设置,rac时候使用,保证不同实例上产生的序列号被取用的时候按顺序进行 |
NEXTVAL:第一次访问时,返回序列的初始值,后继每次调用时,按步长增加的值返回。
CURRVAL:返回序列当前的值,创建新序列后,不能直接使用CURRVAL访问序列,使用过NEXTVAL访问序列后才能使用。
-- 创建序列 CREATESEQUENCE seq_01 STARTWITH1 INCREMENTBY1 MAXVALUE100000000 MINVALUE1 CACHE30 NOCYCLE; -- 创建默认设置的序列 CREATESEQUENCE seq_01; -- 查询序列的下一个值 SELECT seq_01.nextval FROM DUAL; -- 查询序列的当前值 SELECT seq_01.currval FROM dual; INSERTINTO student1 (stu_no,stu_name)values(seq_01.nextval,'大师傅'); -- 删除序列 DROPSEQUENCE seq_04; --查询数据,如果发现表中的数据从2开始,可以修改deferred segment creation(创建延迟片断)的参数 --执行alter session set deferred_segment_creation=false; 把参数的值高为false,此参数为11gR2版本新参数 |
12. 同义词(别名)
在system管理员下: -- 给ripin用户授予创建同义词的权限 GRANTCREATESYNONYMTO RIPIN; -- 共有同义词,一般由管理员来创建 CREATEPUBLICSYNONYM STT1 FOR ripin.student1; 在ripin用户下: -- 创建私有同义词,只能在自己用户下才能查询 CREATESYNONYM st1 FOR student1; |
13.分区表
oracle把表中的行分为不同部分,存储在不同的位置,每一部分称为一个分区,分区的表称为分区表。
13.1作用
安全:分区存放于不同的磁盘,减少同时损坏
查询:查询可按分区
管理:可按分区加载、删除
备份和恢复:针对分区备份与恢复,方便
注意:
数据量大的表,一般大于2GB;数据有明显的界限划分;对于Long和Long Raw类型列不能使用分区。
13.2范围分区(按照字段的值的大小范围)
语法:创建表分区是在创建表的时候就进行
在Create Table语句后增加 PARTITION BY RANGE(column_name) ( PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1], PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2], .... PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN] );
CREATETABLE STUDENT( stu_no CHAR(5)PRIMARYKEYNOTNULL,-- 学号,主键,非空 stu_name VARCHAR2(20)NOTNULL,-- 姓名,非空 stu_id VARCHAR2(18),-- 身份证号,18位 stu_age NUMBER(3,0)-- 年龄,整数 )
-- 表分区,按范围分区,注意语法 PARTITIONBYRANGE(STU_ID)( PARTITIONONEVALUESLESSTHAN('23423427'), PARTITIONTWOVALUESLESSTHAN('23423429'), PARTITION THREE VALUESLESSTHAN(MAXVALUE) );
-- 查询第一,第二,第三分区 SELECT*FROM student PARTITION(ONE); SELECT*FROM student PARTITION(TWO); SELECT*FROM studentPARTITION(THREE); |
13.3列表分区(按照字段的值)
语法:
PARTITION BY LIST(column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), .... PARTITION partN VALUES (DEFAULT) ); 其中:column_name是以其为基础创建列表分区的列。 part1...partN是分区的名称。 values_list是对应分区的分区键值的列表。 DEFAULT关键字允许存储前面的分区不能存储的记录。
-- 创建表 CREATETABLE STUDENT( stu_no CHAR(5)PRIMARYKEYNOTNULL,-- 学号,主键,非空 stu_name VARCHAR2(20)NOTNULL,-- 姓名,非空 stu_id VARCHAR2(18),-- 身份证号,18位 stu_age NUMBER(3,0)-- 年龄,整数 )
-- 按列表分区,就是按照一个字段的某个确定的值进行分区 PARTITIONBYLIST(STU_AGE)( PARTITION P1 VALUES(319), PARTITION P2 VALUES(418), PARTITION P3 VALUES(DEFAULT) );
SELECT*FROM student PARTITION(P1); SELECT*FROM student PARTITION(P2); SELECT*FROM student PARTITION(P3); |
14. PL/SQL
pl/sql:块结构语言,是sql(Structured Query Language)语言的一种扩展,结合了oracle过程语言(procedural language)进行使用。
pl/sql块由三部分构成:声明部分、执行部分、异常部分。
[DECLARE]
--声明变量等
BEGIN
--程序主要部分,一般用来执行过程语句或SQL语句
[EXCEPTION]
--异常处理
END;
14.1常用运算符
= | 等于 | 比较运算符 |
<>,!=,~=,^= | 不等于 | |
< | 小于 | |
> | 大于 | |
<= | 小于或等于 | |
>= | 大于或等于 | |
+ | 加号 | 算术运算符 |
- | 减号 | |
* | 乘号 | |
/ | 除号 | |
:= | 赋值号 | 赋值运算符 |
=> | 关系号 | 关系号 |
.. | 范围运算符 | 范围运算符 |
|| | 字符连接符 | 连接运算符 |
is null | 是空值 | 逻辑运算符 |
between and | 介于两者之间 | |
in | 在一系列值中间 | |
and | 逻辑与 | |
or | 逻辑或 | |
not | 取反 |
14.2变量与常量
声明语法:
变量:variable_name data_type[(size)][:=init_value]
常量:variable_name CONSTANT data_type[(size)] :=init_value
-- 定义常量、变量、 DECLARE s_class CONSTANTVARCHAR2(20):='0901班'; s_name VARCHAR2(20); s_score NUMBER(3,1); BEGIN -- 通过 select into 赋值 SELECT STU_NAME INTO s_name FROM student WHERE stu_no='0004'; -- 通过 := 赋值 s_score:=78.4; -- 输出信息 DBMS_OUTPUT.put_line('班级:'||s_class||',姓名:'||s_name||',分数:'||s_score); END;
|
数据类型:
常用标准类型:CHAR(CHARATER,NCHAR),VARCHAR2,NUBER(P,S),DATE,BOOLEAN等
属性类型:%TYPE与%ROWTYPE
%TYPE:可以用来定义数据变量的类型与已定义的数据变量(表中的列)一致。
-- %TYPE 的使用 DECLARE s_class CONSTANTVARCHAR2(20):='0902班';-- 常量 s_name student.stu_name%TYPE; s_score NUMBER(3,1); BEGIN SELECT stu_name INTO s_name FROM student WHERE stu_no='0001'; s_score:=98; DBMS_OUTPUT.put_line(S_CLASS||S_NAME||S_SCORE); END; 结果:0902班夏98 |
%ROWTYPE:与某一数据库表的结构一致(修改数据库表结构,可以实时保持一致).
-- ROWTYPE 的使用 DECLARE S_CLASS CONSTANTVARCHAR(20):='0903班'; stu_info student%ROWTYPE; s_score NUMBER(3,1); BEGIN SELECT*INTO stu_info FROM student WHERE stu_no='0002'; s_score:=88.9; DBMS_OUTPUT.PUT_LINE('班级:'||s_class||',姓名:'||stu_info.stu_name||',分数:'||s_score); END;
结果:班级:0903班,姓名:商,分数:88.9 |
14.3 控制语句
注意:有IF 就有END IF,有LOOP就有END LOOP;
14.3.1 条件控制
和java中不同的是else if 写法变成了 ELSIF 没了e
-- 条件控制 DECLARE s_class CONSTANTVARCHAR2(20):='IF班'; s_age student.stu_age%TYPE; s_score NUMBER(3,1); BEGIN SELECT stu_age INTO s_age FROM student WHERE stu_no='0002'; s_score:=78.3; IF s_age<=219THEN DBMS_OUTPUT.PUT_LINE('存在时间'||s_age||'年是简单王朝'); ELSIF s_age<320THEN DBMS_OUTPUT.PUT_LINE('存在时间'||s_age||'年是一般王朝'); ELSE DBMS_OUTPUT.PUT_LINE('存在时间'||s_age||'年是帝国'); ENDIF; -- 注意结束if END; |
14.3.2 循环控制
要实现4,3,2,1依靠 in 4..1 loop 是行不通的,需要用到反转REVERSE
-- 循环控制loop,while,for DECLARE status NUMBER(2,0):=1; status2 NUMBER(2):=1; status3 NUMBER(2):=2; BEGIN LOOP-- loop循环 DBMS_OUTPUT.PUT_LINE('status='||status); status:=status+1; EXITWHEN status=6;-- 退出循环条件 ENDLOOP;-- 结束loop循环 -- while循环 WHILE status2<=5LOOP DBMS_OUTPUT.PUT_LINE('status2='||status2); status2:=status2+1; ENDLOOP; -- for循环,正序 FOR status3 IN1..5 LOOP DBMS_OUTPUT.PUT_LINE('正序status3='||status3); ENDLOOP; -- for循环,逆序,注意reverse放置的位置 FOR status3 INREVERSE1..5 LOOP DBMS_OUTPUT.PUT_LINE('逆序status3='||status3); ENDLOOP; END; |
14.3.3 顺序控制
用于按指定顺序执行的语句。主要包括 null语句和goto语句,goto语句建议不要使用。 null语句:是一个可执行语句,相当于一个占位符或不执行操作的空语句。主要用来提高程序语句的完整性和程序的可读性。不写就报错
-- 顺序控制,null和goto DECLARE numNUMBER(2):=0; BEGIN WHILEnum<=8LOOP IFNUM=4THEN NULL;-- num=4时,没有打印数据了 ELSE DBMS_OUTPUT.PUT_LINE('数字:'||num); ENDIF; NUM:=NUM+1; ENDLOOP; END;
-- goto DECLARE i NUMBER(2):=0; BEGIN <<start1>> WHILE i<8LOOP i:=i+1; IF i=4THEN GOTO start1; ENDIF; DBMS_OUTPUT.PUT_LINE('数字是:'||i); ENDLOOP; END; |
14.4.异常处理
14.4.1预定义异常
预定义异常指PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发(由oracle自动引发)。
常见的预定义异常:
ZERO_DIVIDE:以零作为除数时出现
DUP_VAL_ON_INDEX:试图将重复的值存储在使用唯一索引的数据库列中
INVALID_NUMBER:试图将一个非有效的字符串转换成数字
TOO_MANY_ROWS :在执行SELECT INTO语句后返回多行时出现
VALUE_ERROR:变量的列值超出变量大小
CURSOR_ALREADY_OPEN:试图打开已经打开的游标
ACCESS_INTO_NULL:试图给一个没有初始化的对象赋值
LOGIN_DENIED :使用无效的用户名和口令登录Oracle
NO_DATA_FOUND :语句无法返回请求的数据
14.4.2自定义异常
自定义异常:程序在运行过程中,编程人员根据业务等情况,认为非正常情况,可以自定义异常。对于这种异常,主要分三步来处理:
第一、定义相关异常;在声明部分定义相关异常,格式:<自定义异常名称> EXCEPTION;
第二、抛出异常;在出现异常部分抛出异常,格式:RAISE <异常情况>;
第三、处理异常;在异常处理部分对异常进行处理,
格式:when <自定义异常名称> then...处理异常也可以使用RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE)存储过程进行处理,其中参数ERROR_NUMBER取值为-20999~-20000的负整数,参数ERROR_MESSAGE为异常文本消息。
DECLARE SCHOOL_NAME CONSTANTVARCHAR2(20):='明德中学'; s_name student.stu_name%TYPE; s_id student.stu_no%TYPE; s_temp NUMBER(2,0); s_age student.stu_age%TYPE; AgeOutOfBoundException EXCEPTION;--声明异常 BEGIN -- 没加where条件查出来的数据不止一条,此时就会报错:实际返回的行数超出请求的行数 SELECT stu_name,stu_no,stu_age INTO s_name,s_id,s_age FROM student WHERE stu_age=418; s_temp:=-7; IF s_id<8THEN DBMS_OUTPUT.PUT_LINE(s_name||'是小班的'); ELSE DBMS_OUTPUT.PUT_LINE(s_name||'是大班的学生'); ENDIF; IF s_age>620OR s_age<0THEN RAISE AgeOutOfBoundException;-- 异常条件判断,抛出异常 ENDIF; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('异常代码号'||SQLCODE||'异常信息'||SQLERRM); WHEN AgeOutOfBoundException THEN—捕获异常,显示异常信息 RAISE_APPLICATION_ERROR(-20999,'年龄太夸张了'); WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('异常代码号'||SQLCODE||'异常信息'||SQLERRM); END; |
注意:错误号不能随便写,需要在-20999 - -20000之间
15.游标
15.1游标概念
游标是指oracle在执行增删改查操作时,会把执行结果放在内存分配的缓冲区中,游标就是指向该区的一个指针,可以对结果集的每一行数据分别进行处理。
游标属性:
%found 是否存在结果集或影响的行数,如果存在返回true
%notfound 是否存在结果集或影响的行数,如果不存在返回true
%rowcount 返回受影响的行数
%isopen 游标是否已经打开。隐式游标中一般是自动打开和关闭的,查询都返回False
15.2隐式游标(针对增删改)
由Oracle在内部声明,数据库自动创建,管理。主要用途是用于增删改数据时,可以返回一个操作成功或失败的相关信息,隐式游标的名称是(SQL)
DECLARE-- 隐式游标 BEGIN INSERTINTO student (stu_no,Stu_Name,Stu_Id,Stu_Age)VALUES('0009','唐朝','23423430',440); IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE('存在影响的行'); ENDIF; IFSQL%NOTFOUNDTHEN DBMS_OUTPUT.PUT_LINE('不存在影响的行'); ENDIF; IFSQL%ISOPENTHEN DBMS_OUTPUT.PUT_LINE('游标已打开'); ENDIF; DBMS_OUTPUT.PUT_LINE(' 影响的行数'||SQL%ROWCOUNT); END; 结果:存在影响的行,影响的行数1 |
15.3显式游标(针对查询)
使用步骤:
1. 声明游标
2. 打开游标
3. 提取游标
4. 关闭游标
-- 显式游标 DECLARE s_name student.stu_name%TYPE; CURSOR stu_cursor ISSELECT stu_name FROM student;-- 创建游标 BEGIN OPEN stu_cursor;-- 打开游标 LOOP FETCH stu_cursor INTO s_name;-- 将游标对应的值注入给s_name DBMS_OUTPUT.PUT_LINE('姓名:'||s_name); EXITWHEN stu_cursor%NOTFOUND; ENDLOOP; CLOSE stu_cursor;-- 关闭游标 END; 结果: 姓名:周 姓名:秦 姓名:三国
/* 使用显式游标(for),查询并输出所有学生的学号和姓名, for循环游标会隐式打开游标、自动创建%ROWTYPE类型变量对应记录行, 处理完所有行后会自动关闭游标.使用起来较方便。 */
-- 使用显式游标修改学生年龄 DECLARE CURSOR stu_cur ISSELECT*FROM student FORUPDATE;-- 注意:若表进行了分区,则此操作不成功 s_name student.stu_name%TYPE; s_age student.stu_age%TYPE; BEGIN FOR stu_info IN stu_cur LOOP s_name:=stu_info.stu_name; s_age:=stu_info.stu_age; IF s_age=418THEN update student set stu_age=428WHERE stu_no=stu_info.stu_no; ELSE UPDATE student SET stu_age=s_age+1WHERE stu_no = stu_info.stu_no; ENDIF; ENDLOOP;
END; |
16.存储过程
存储过程:属于已命名的pl/sql程序块,封装数据业务操作,具有模块化、可重用、可维护、更安全特点。
固定式数据处理(每天统计报表),追求效率,而不是灵活性,就需要用到存储过程。
16.1存储过程类型
1. 不带参数
2. 带输入参数
3. 带输出参数
4. 带输入输出参数
16.2创建与调用语法
CREATE [OR REPLACE] PROCEDURE procedure_name[(param_list)] IS|AS [DECLARE] BEGIN 执行语句; [EXCEPTION] 异常处理; END[procedure_name]; OR REPLACE:如果系统已存在该存储过程,将被替换 procedure_name:存储过程名称 param_list:参数列表,参数不需要声明长度,可选 DECLARE:局部声明,可选 |
调用语法:
命令行调用:
exec[ute] procedure_name(paramlist);
pl/sql块调用:
begin
procedure_name(paramlist);
end;
-- 创建无参存储过程 CREATEORREPLACEPROCEDURE stu_update_pro IS BEGIN UPDATE student set stu_name='元朝'WHERE stu_no='0011'; END;
-- 调用存储过程 BEGIN stu_update_pro; END;
命令行调用: execute stu_update_pro; exec stu_update_pro;
-- 创建有输入有输出的存储过程 CREATEORREPLACEPROCEDURE pro_stu_add2( s_no student.stu_no%TYPE, s_name student.stu_name%TYPE, s_code OUTNUMBER, s_message OUTVARCHAR2 ) IS BEGIN INSERTINTO student (stu_no,stu_name)VALUES(s_no,s_name); s_code:=1; s_message:='添加成功'; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- 可以没有这个,直接就OTHERS s_code:=3; s_message:='添加失败,已存在该生'; WHENOTHERSTHEN s_code:=SQLCODE; s_message:=SQLERRM; END;
-- 调用有输入有输出的存储过程 DECLARE p_code NUMBER(10,0); p_message VARCHAR2(50); BEGIN -- 注意:当传递的参数顺序和声明的不一致时,需要用到 => pro_stu_add2('0010','元',s_message => p_message,s_code => p_code); DBMS_OUTPUT.PUT_LINE('返回状态码:'||p_code||',提示信息:'||p_message); end;
-- 删除存储过程 DROPPROCEDURE stu_update_pro; |
输入参数定义时: flag in student.stu_name%TYPE;这里的in可以省略
输出参数定义:error out number;这里的out不能省略,参数的长度不需要定义
17. 触发器
触发器是在事件发生时隐式的自动运行的PL/SQL程序块,不能接受参数,不能被调用,一般监听的事件是数据的增删改(insert/delete/update)事件。
语法(”[]可不写,”{}”必写):
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} –-只是代表触发器语句执行的时机,不影响:new和:old的值
{INSERT|DELETE|UPDATE}
ON{table_name}
[FOR EACH ROW] -- 不写就是表级触发器,:old 和:new不能用于表级触发器中
trigger_body; -- 即PL/SQL块
注意:若写了两个删除触发器,一个备份一条数据,另一个备份整张表,那么都会执行
:old代表执行数据操作之前的值,:new 代表操作完后的值
17.1 备份删除的数据
删除一条记录时,要把删除的数据备份,以便于以后对数据的查找恢复。
-- 创建备份表 CREATETABLE stu_del_rec(-- 不需要主键 stu_no CHAR(5)NOTNULL,-- 学号,主键,非空 stu_name VARCHAR2(20)NOTNULL,-- 姓名,非空 stu_id VARCHAR2(18),-- 身份证号,18位 stu_age NUMBER(3,0),-- 年龄,整数 stu_operation_date DATE )
-- 备份删除的数据 CREATEORREPLACETRIGGER tri_stu_del AFTERDELETEON student FOREACHROW BEGIN INSERTINTO stu_del_rec(stu_no,stu_name,stu_id,stu_age,stu_operation_date) VALUES(:OLD.stu_no,:OLD.stu_name,:OLD.stu_id,:OLD.stu_age,SYSDATE); END; |
17.2 表级触发器,删除整张表时,备份
-- 整表备份 CREATEORREPLACETRIGGER tri_stu_delAll BEFOREDELETEON student BEGIN INSERTINTO stu_del_rec(stu_no,stu_name,stu_id,stu_age) SELECTstu_no,stu_name,stu_id,stu_age FROM student; END; |
17.3 实现主键自增长
-- 创建序列 CREATESEQUENCE seq_stu_no; -- 实现主键自增长 CREATEORREPLACETRIGGER tri_stu_add BEFOREINSERTON student FOREACHROW BEGIN -- UPDATE student SET stu_no=seq_stu_no.nextval;这么写是无法注入的,因为拿不到序列的值,需要用到伪表 SELECT seq_stu_no.nextval INTO:new.stu_no FROM dual; END;
-- 测试 SELECT*FROM student ; SELECT*FROM stu_del_rec; DELETEFROM student WHERE stu_no='0002'; DELETEFROM student; -- 主键测试 insertinto student (stu_name,stu_age)values('可若',34); |
18. 视图
也称虚表, 不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候只是重新执行SQL.
视图是从一个或多个实际表中获得的,这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
create view 视图名 as 查询语句;
drop view 视图名;
表的列经常被进行查询,可以考虑建立视图。
19. 数据闪回
oracle可以在删除之后进行数据和表对象的闪回,任何恢复技术都是基于系统的某一个时间点的,因此oracle的闪回也一样,
SCN: System Change Number,和系统的时间值一一对应,SCN可以作为恢复的时间点。
sys用户:不是system@orcl -- 使用管理员sys,用dba角色查看undo表空间的参数 SQL> show parameter undo;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 默认的情况下有效的闪回时间为900(15分钟),但是管理员可以通过设置语句来修改闪回的有效值时间。单位是秒 60 -- 修改当前系统的undo表空间的参数(可以不修改) SQL> alter system set undo_retention=900 scope=both;
--闪回刚才删除数据,需要先修改表的列可以移动:登陆sys用户
SQL> alter table ripin.student enable row movement;
注意:若不是sys用户,提示ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
ripin用户 -- 1:ripin用户,查看当前时刻的SCN号 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),timestamp_to_scn(sysdate)from dual;
-- 数据闪回 flashbacktable student toscn(1480234); |
20. 数据的备份与恢复
20.1数据备份
20.1.1完整备份
E:\Software\ALTRun>exp ripin/ripin925@orcl file=D:\Oracle\data_bak\oracle_ripin_ bak.dmp full=y
Export: Release 11.2.0.1.0 - Production on 星期一 11月 3 18:58:53 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options EXP-00023: 必须是 DBA 才能执行完整数据库或表空间导出操作 (2)U(用户), 或 (3)T(表): (2)U >
导出权限 (yes/no): yes > yes
导出表数据 (yes/no): yes > yes
压缩区 (yes/no): yes > yes
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 . 正在导出 pre-schema 过程对象和操作 . 正在导出用户 RIPIN 的外部函数库名 . 导出 PUBLIC 类型同义词 . 正在导出专用类型同义词 . 正在导出用户 RIPIN 的对象类型定义 即将导出 RIPIN 的对象... . 正在导出数据库链接 . 正在导出序号 . 正在导出簇定义 . 即将导出 RIPIN 的表通过常规路径... . . 正在导出表 NEWSTUDENT导出了 7 行 . . 正在导出表 STUDENT导出了 3 行 . . 正在导出表 STUDENT1导出了 7 行 . . 正在导出表 STU_DEL_REC导出了 17 行 . . 正在导出表 SYS_TEMP_FBT . 正在导出同义词 . 正在导出视图 . 正在导出存储过程 . 正在导出运算符 . 正在导出引用完整性约束条件 . 正在导出触发器 . 正在导出索引类型 . 正在导出位图, 功能性索引和可扩展索引 . 正在导出后期表活动 . 正在导出实体化视图 . 正在导出快照日志 . 正在导出作业队列 . 正在导出刷新组和子组 . 正在导出维 . 正在导出 post-schema 过程对象和操作 . 正在导出统计信息 导出成功终止, 但出现警告。 |
指定表备份
E:\Software\ALTRun>exp ripin/ripin925@orcl file=D:\Oracle\data_bak\oracle_ripin student_bak.dmp tables=(student)
Export: Release 11.2.0.1.0 - Production on 星期一 11月 3 19:05:35 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径... . . 正在导出表 STUDENT导出了 3 行 成功终止导出, 没有出现警告。 |
20.2恢复
-- 指定表数据恢复 DROPTABLE student; SELECT*FROM student; dos命令 C:\>imp ripin/ripin925@orcl ignore=y file=D:\Oracle\data_bak\oracle_ripin_studen t_bak.dmp tables=(student)
Import: Release 11.2.0.1.0 - Production on 星期一 11月 3 19:15:33 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V11.02.00 创建的导出文件 已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入 . 正在将 RIPIN 的对象导入到 RIPIN . 正在将 RIPIN 的对象导入到 RIPIN . . 正在导入表 "STUDENT"导入了 3 行 成功终止导入, 没有出现警告。 |
导出后可以删除用户及表空间
dropuser user_ripin cascade;
droptablespace ts_ripin includingcontentsanddatafiles;