oracle数据库入门学习 -

Ripin_Yan

浏览: 4946 次
性别:
来自: 深圳

最近访客更多访客>>

ssgemail

nameshane

yufangwu111

Cobain_LI

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2015-01 ( 3)
更多存档...

oracle数据库入门学习

博客分类：

数据库

oracle入门语法基础数据库 pl/sql

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

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

)

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

连接到: 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

连接到: 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

连接到: 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

. . 正在导入表 "STUDENT"导入了 3 行

成功终止导入, 没有出现警告。

导出后可以删除用户及表空间

dropuser user_ripin cascade;

droptablespace ts_ripin includingcontentsanddatafiles;

oracle入门.zip (564.3 KB)
下载次数: 0

查看图片附件

分享到：

maven插件入门学习中碰到的问题解决

2015-01-16 17:24
浏览 1206
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

oracle数据库入门学习

1.常见的数据库

2. Oracle关键服务

3. 表空间

3.1 概念

3.2 分类

3.3 表空间的使用

4. 用户与权限

4.1系统用户

4.2用户与模式

4.3 创建用户与权限

4.4 解锁scott用户以及修改密码

5. oracle数据类型

5.1 dual表

5.2字符数据类型

5.3数值数据类型

5.4日期时间数据类型

5.5 其它数据类型

6.伪列

6.1Rowid

6.2Rownum

7. SQL语言简介

7.1 数据定义语言

7.2 数据操作语言

7.3 事务操作语言

7.4 数据控制语言

8.运算符

8.1算术运算符

8.2比较（关系）运算符

8.3逻辑运算符

8.4连接运算符 -- ||

8.5集合运算符

9. 常用函数

9.1多行函数(处理多行数据的函数)

9.2转换函数(单行函数)

9.2.1 to_char

9.2.2 to_date

9.2.3 to_number

9.3分析函数

9.4其它函数

10. 索引

11. 序列

12. 同义词（别名）

13.分区表

13.1作用

13.2范围分区（按照字段的值的大小范围）

13.3列表分区（按照字段的值）

14. PL/SQL

14.1常用运算符

14.2变量与常量

14.3 控制语句

14.3.1 条件控制

14.3.2 循环控制

14.3.3 顺序控制

14.4.异常处理

14.4.1预定义异常

14.4.2自定义异常

15.1游标概念

15.2隐式游标（针对增删改）

15.3显式游标（针对查询）

16.存储过程

16.1存储过程类型

16.2创建与调用语法

17. 触发器

17.1 备份删除的数据

17.2 表级触发器，删除整张表时，备份

17.3 实现主键自增长

18. 视图

19. 数据闪回

20. 数据的备份与恢复

20.1数据备份

20.1.1完整备份

20.2恢复

评论

最近访客更多访客>>