oracle数据库入门学习

不得不说这个排版真心难弄啊，明明都在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;