oracle

最新推荐文章于 2020-10-19 12:01:33 发布

等风来z

最新推荐文章于 2020-10-19 12:01:33 发布

阅读量366

点赞数

分类专栏：关系型数据库文章标签：数据库/oracle

本文链接：https://blog.csdn.net/qq_39586685/article/details/89494617

版权

关系型数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Oracle（一）

Oracle 是一个数据库管理系统，是Oracle公司的核心产品。其在数据安全性与安整性控制方面的优越性能，以及跨操作系统、跨硬件平台的数据操作能力。基于“客户端/服务　　　　　　器”(Client/Server)系统结构。

主要特点：

1.支持多用户、大事务量的事务处理。
2.在保持数据安全性和完整性方面性能优越。
3.支持分布式数据处理。将公布在不同物理位置的数据库用通信网络连接起来，
组成一个逻辑上统一的数据库，完成数据处理任务。
4.具有可移植性。Oracle可以在Windows、Linux等多个操作系统平台上使用。

1、基本概念

数据库

这里的数据库是磁盘上存储数据的集合，在物理上表现为数据文件、日志文件和控制文件等。在逻辑上以表空间形式存在。必须首先创建数据库，然后才能用Oracle。可以在Database Configuation Assistant上创建。

全局数据库名

用于区分一个数据库的标识。由数据库名称和域名构成，类似网络中的域名，使数据库的命名在整个网络环境唯一。

数据库实例

每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中的数据。

表空间

每个数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间中。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个表空间。

数据文件

扩展名是.dbf，是用于存储数据库数据的文件。一个数据文件中可能存储很多个表的数据，而一个表的数据也可以存放在多个数据文件中。数据文件和数据库表不存在一对一的关系。

控制文件

技展名.ctl，是一个二进制文件。控制文件是数据库启动及运行所必需的文件。存储数据文件和日志文件的名称和位置。Oracle 11g默认包含三个控制文件。

日志文件

扩展名.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现后重新恢复数据库。在工作过程中，多个日志文件组之间循环使用。

模式和模式对象

模式是数据库对象(表、索引等，也称模式对象)的集合。

2、Windows下数据库服务

2-1、基本概念

OracleServiceSID服务是Oracle数据库服务。此服务对应名为SID(系统标识符)的数据库实例创建的。必须启动。

OracleOraDb11g_homelTNSListener服务是监听器服务。要远程连接数据库服务器，必须连接监听进程，该服务只有在数据库需要远程访问时候才需要。(PL/SQL等第三方工具连接就相当于远程连接)。

OracleDBConsoleSID服务是数据库控制台服务，是采用浏览器方式打开的，用于使用Oracle企业管理器的程序。如果进入基于Web形式的企业管理(EM)控制平台，必须要启动OracleDBConsoleSID服务。

2-2、连接数据库

Sys和System用户都是Oracle的系统用户Scott用户是Oracle数据库的一个测试账户，里男包含一些测试例表。每个用户下所有的对象称为模式对象。

1、用SQL Plus工具登录：

1)cmd->sqlplus->用户名：->密码:

2)直接打开程序SQL Plus->用户名：->密码

3)在输入用户名处->用户名/密码@网络服务名

4)命令窗口：sqlplus /nolog 这样就只是打开了sqlplus而并没有连接数据库，之后要连接数据库得继续敲：connect 用户名/密码@数据库名;

2、PL/SQL Developer工具：

1)用户名：输入System.

2)口令：输入对应密码。

3)数据库：输入已经配置好的网络服务名,如orclDB。

4)连接为:Normal普通用户;sysOper数据库操作员;sysDBA数据库管理员。

DUAL伪表，在Oracle的查询语句中select和from关键字一定有值，但在某些情况下不需要from,如查询常量或函数，为

了满足要求，Oracle使用DUAL伪表来实现要求。

如: select sysdate from dual;–查询出当前系统日期和时间

3、Oracle数据类型

3-1、字符数据类型

char

当需要固定长度字符串时，使用char。长度1~2000字节。如果没有指定大小，默认1字节。如果用户输入的值小于指定的大小，系统用空格填充。如果大于，报错。

varchar2

支持可变长度的字符串。大小为1~4000字节。如果用户输入的值小于指定的大小，不用填充，varchar2数据类型可以节省磁盘空间。如果varchar2想根据定义的长度存储相应的汉字个数，可以声明为：varchar2(10 char)

nchar

即国家字符集，使用方法和char相同。用来存储Unicode字符集类型，即双字节字符数据。最小单位1字符。

注意：nvarchar2与nchar类似，只是可变长度的。

3-2、数值数据类型number

number数据类型可以存储正数、负数、零、定点数、精度为38信的浮点数。

**语法：**number(p,s)

p 为精度，表示数字的有效位数，在1~38之间，从左边第一个不为0的数算起，小数点和负号不计入有效位数。

s 为范围，表示小数右边数字的位数，在-84~+127之间。

**规则：**首先精确到小数中右边s位，并四舍五入。如果精确后值的有效位数<=p，则正确;否则报错。

3-3、日期时间数据类型

date

用于存储表中的日期和时间数据。使用7字节固定长度，每个字节分别存储世纪、年、月、日、小时、分和秒。sysdate函数的功能是返回当前的日期和时间。

timestamp

用于存储日期的年、月、日以及时间的小时、分和秒。秒值精确到小数小后6位，包含时区信息。systimestamp函数的功能是返回当前日期、时间和时区。

改变日期格式

Oracle中的SQL窗口：alter session set nls date format=’YYYY-MM-DD HH24:MI:SS’;//只对当前窗口有效。

环境变量：键：nls_date_format

值:YYYY-MM-DD HH24:MISS

3-4、游标类型

SYS_REFCURSOR类型是Oracle提供的系统游标类型。

如下，过程返回一个游标类型：

CREATE OR REPLACE PROCEDURE get_sals(cur_salary OUT SYS_REFCURSOR)
IS
BEGIN
    OPEN our_salary FOR 
    SELECT empno, sal FROM employee;
END;

3-5、LOB数据类型

LOB又称为“大对象”数据类型。我叫存储4GB的非结构化信息，如声音剪辑和视频剪辑。LOB允许对数据进行高效、随

机、分段的访问。一个表中可以有多个列被定义为LOB数据类型。可以通过PL/SQL中提供的程序包DBMS_LOB修改。

1.CLOB(Character LOB,字符LOB)存储大量字符数据。可以存储单字节字符数据和多字节字符数据，主要用于存储非结

构化的XML多档。如新闻、内容介绍等含大量文字内容的文档。

2.BLOB(Binary LOB,二进制LOB)可以存储较大的二进制对象，如图形、视频剪辑和声音剪辑等。

3.BFILE(Binary File，二进制文件)能够将二进制文件存储在数据库外部的操作系统文件中。BFILE列存储一个BFILE

定位器，指向位于服务器文件系统上的二进制文件。最大4GB。

4.NCLOB数据类型用于存储大的nchar字符数据。支持固定宽度字符和可变宽度字符(Unicode字符数据)。使用方法同

CLOB类似。

早期使用Long数据类型存储大数据。

3-6、伪列

1.rouid，数据库中每一行都有一个行地址，rowid伪列返回该行地址。rowId值可以唯一标识数据库中的一行。

2.rounum,返回一个数值代表行的次序。通过使用rownum，用户可以限制查询返回的行数。

rownum对于等于某值的查询条件，可以使用rownum=1作为条件，但是不能等于大于1的值。

rownum对于大于某值的查询条件，可以使用rownum>1，但是不能大于1以上的值。

rownum对于小于某值的查询条件，可以rownum<大于1的整数。

例:

select * from(
	select a.*,rownum rn from ( 
		select * from employee order by sal desc
	) a
)	where rn between 5 and 9;

4、SQL语言分类

数据定义语言(DDL Data Definition)：create创建、alter更改、truncate截断、drop删除

数据操纵语言(DML Data Manipulation Language)：insert插入、select选择、delete删除、update更新

事务控制语言(TCL Transaction Control Language)：commit提交、savepoint保存点、rollback回滚

数据控制语言(DCL Data Control Language)：grant授予、revoke回收

4-1、DDL

create table

create table [schema.] table_name(
    column_name datatype,
    column_name datetype,
    ....
)

schema表示对象的所有者，即模式的名称。如果用户在自己的模式中创建表，则可以不指定所有者名称。
table表示表的名称。
column表示列的名称。
datatype表示该列的数据类型及其宽度。

表名命名规则：

1)表名首字符应该为字母。

2)不能使用Oracle保留字来为表命名。

3)表各的最大长度为30个字符。

4)同一用户模式下的不同表不能具有相同的名称。

5)可以使用下划线、数字、字母，但不能使用空格和单引号。

truncate table

truncate table <table_name>;--可以只删除表中的记录而不删除表结构，删除表中的所有行而不记录日志。

4-2、DML

1.选择无重复的行，在select命令中包含distinct子句。

select distinct stuName,stuAge from stuInfo;

2.使用列别名，列别名不会影响列的实际名称。列别名位于列表达式后面。

select stuName as “姓 名”,stuAge as 年龄,stuNo 编号 from stuInfo;

如果列别名中指定有特殊字符（如空格）的列标题使用双引号括起来。

3.利用现有表创建新表。

create table <newTable_name>
 as
select {* | column(s)} from <oldTable_name> [where <condition>];

复制表结构和记录：

create table newStuInfo
as
select * from stuInfo;

复制表指定列和记录：

create table newStuInfo
as
select stuName,stuAge,stuNo from stuInfo;

只复制表结构：

create table newStuInfo
as
select * from stuInfo where 1=2;

4.查看表中行数：

select count(1) from stuInfo; --1比*号效率较高

5.取出stuName,stuAge列不存在重复的数据记录：

select stuName,stuAge from stuInfo group by stuName,stuAge having(count(stuName||stuAge));
//“||”在这里是连接操作符，类似于“+”，意思为将两部分内容连接在一起，因为count()里面只能有一个列，所以连接起来。

6.删除stuName、stuAge列重复的行（相同数据只保留一行）

delete from stuInfo where rowid not in(
select max(rowid) from stuInfo group by stuName,stuAge 
having (count(stuName||stuAGe)>1)
union
select max(rowid) from stuInfo group by stuName,stuAge
having (count(stuName||stuAge)=1)
);

7.根据当前用户所有数据量>100万的表的信息

select tabke_name from user_all_tables a where a.num_rows>1000000;

--user_all_tables为系统提供的数据视图，使用者可以通过查询该视图获得当前用户表中描述。

4-3、TCL

commit:提交事务,提事务中对数据库的修改进行永久保存。
rollback:回滚事务，即取消对数据库所做的作何修改。
savepoint <savepoint_name>:在事务中创建存储点。
rollback to savepoint <savepoint_name>:将事务回滚到存储点。即savepoint 创建的存储点

开启事务：在Oracle中，上一次事务结束以后，数据第一次被修改时自动开启。

结束事务：
①数据被提交:发出commit命令;执行DDL或DCL语句后，当前事务自动commit;与Oracle分离。
②数据被撤销：发出rollback命令;服务器进程异常结束;DBA停止会话。

insert into dept values (10,’ACCOUNTING’,’NEW YORK’);
commit;
savepoint a;--a只是一个名称，设置保存点
insert into dept values (20,’SALES’,’NEW YORK’);
rollback to savepoint a;--回退到a保存点
rollback;--结果只有第一条记录插入成功

4-4、DCL

数据控制语言为用户提供权限控制命令。数据库对象（如表）的所有者对这些对象拥有控制权限。所有者可以根据自己的意愿决定其他用户如何访问对象，授予其他用户权限（insert,select,update,delete,…）

5、SQL操作符

5-1、算术操作符

算术表达式由number数据类型的列名、数值常量和连接它们的算术操作符组成。

+(加)、-(减)、*(乘)、/(除)。

5-2、比较操作符

=、!=、<>、<、<=、>、>=、between… and 、in、not in、like、is null、is not null。

Oracle中的between-and语句，包含前也包含后。如：rn between 5 and 9;结果是5,6,7,8,9

5-3、逻辑操作符

and , or , not。

5-4、集合操作符

将多个查询的结果组合成一个结果集。

规则：

1）通过集合操作符连接的条个查询具有相同的列数，且对应列的数据类型必须兼容。

2）这种查询不应含有long类型的列。列标题来自第一个select语句。

union (并集)操作符返回两个查询选定的所有不重复的行。使用order by 子句时，必须放在最后一个select后。

select empno from employee
union
select rempno from retireEmp
order by empno;

union all (并集all)操作符合并两个查询选定的所有行。包括重复的行。

intersect (交集)操作符返回两个查询都有的行。

select empno from employee
intersect
select rempno from retireEmp;

minus (减集)操作符只返回由第一个查询选定而未被第二个查询选定的行，即在第一个查询结果中排除在第二个查询

结果中出现的行。

select empno from employee
minus
select rempno from retireEmp;

5.连接操作符(||),用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。

select job||’ ‘||ename from employee;--输出的时候将岗位和姓名连接在一起输出。

6、SQL函数

大致分为单行函数、聚合函数、分析函数。

单行函数：字符函数、日期函数、数字函数、转换函数及其他函数。

聚合函数(Aggregate Function)：也称为分组函数是基于数据库表的多行进行运算，返回一个结果。

分析函数：是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。

6-1、字符函数

操作结果可能是字符数据类型，也可能是数字类型。

函数	说明	输入	结果
initcap(char)	首字母大写	initcap(‘hello’)	Hello
lower(char)	转换为小写	lower(‘FUN’)	fun
upper(char)	转换为大写	upper(‘sun’)	SUN
ltrim(char,set)	左剪裁	ltrim(‘xyzadams’,’xyz’)	zdams
rtrim(char,set)	右剪裁	rtaim(‘xyzadams’,’ams’)	xyzad
translate(char,from,to)	按字符翻译	translate(‘Jack’,’abcd’,’1234’)	J13k
replace(char,search_str, replace_str)	字符串替换	replace(‘jack and jue’,’j’,’bl’)	black and blue
instr(char,substr[.pos])	查找子串位置	instr(‘worldwide’,’d’)	5
substr(char,pos,len)	取子字符串,下标从1开始	substr(‘abcdefg’,3,2)	cd
concat(char1,char2)	连接字符串	concat(‘Hello’,’world’)	Helloworld

6-2、数字函数

接受数字输入并返回数字作为输出结果，返回的值可以精确到小数点后38位。

函数	说明	输入	结果
abs(n)	取绝对值	abs(-15)	15
ceil(n)	向上取整	ceil(44.178)	45
floor(n)	向下取整	floor(44.99)	44
round(m,n)	四舍五入	round(100.256,2)	100.26
trunc(m,n)	截断	trunk(100.256,2)	100.25(小数后几位?)
sin(n)	正弦	sin(1.571)	.999999979
cos(n)	余弦	cos(0)	1
power(m,n)	m的n次幂	power(4,2)	16
sqrt(n)	平方根	sqrt(4)	2
sign(n)	取符号	sign(-32)	-1
mod(m,n)	取m%n的结果	mod(10,3)	1 (Oracle中没有%运算符)

6-3、日期函数

对日期值进行运算，根据函数的用途产生日期数据类型或数值类型的结果。

函数	功能	实例	结果
sysdate	返回当前日期和时间	select sysdate from dual;	当前日期和时间
systimestamp	返回当前日期、时间、时区	select systimestamp from dual;	当前日期、时间和时区
months_between( prevdate,nextdate)	返回两个日期间的月份	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月-3’,’星期一’)	10-2月03
last_day	返回指定日期所在月的最后一天	last_day(‘06-2月-03’)	28-2月-03
round	按指定格式对日期进行四舍五入对年份四舍五入，日期>6月30日，则年份+1;	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
trunc	对日期按指定方式进行截断	trunc(to_date(‘06-2月-03’),’YEAR’) trunc(to_date(‘06-2月-03’),’MONTH’) trunc(to_date(‘06-2月-03’),’DAY’)	01-1月-03 01-2月-03 02-2月-03
extract(year from date)	返回date中的年份，也可以是month day,	extract(year from sysdate)	2014

6-4、转换函数

将值从一种数据类型转换为另一种数据类型。Oracle中没有convert函数。

函数	功能	实例	结果
to_char	转换成指定字符串类型	to_char(123.45,’ $9999.9 ’) (只有$ 符号可以)	$123.5
to_date	转换成指定日期类型	to_date(‘1980-01-01’,’yyyy-mm-dd’)	01-1月-80
to_number	转换成指定数值类型	to_number(‘1234.5’)	数字：1234.5

to_char()

to_char(d|n[,fmt]);--其中d是日期，n是数字，fmt是指定日期或数字的格式 。转换成的是varchar2数据类型
例: 
	select to_char(sysdate,’YYYY”年”fmMM”月”fmDD”日” HH24:MI:SS’) from dual;
		--使用了填充模式”fm”格式掩码来避免空格填充和数字零填充
		--格式中的小数有多少位，结果就会四舍五入进多少位。

to_date()

to_date(char[,fmt]);--将char或varchar数据类型转换为日期数据类型。fmt指定字符的形式。
例：
	select to_date(‘2013-07-18’,’yyyy-mm-dd’) from dual;
		--结果：18-7月-13 这Oracle中mm是月份，mi是分种数
		--这里的fmt是指定Oracle服务器该如果解析这个char字符串的格式，返回的结果统一是：yyyy-mm-dd;

to_number()

函数将包含数字的字符串转换为number数据类型。通常不必这样做，因为Oracle可以对数字字符串进行隐式转换。
例：
	select sqrt(to_number(‘100’)) from dual;--结果：100

6-5、其他函数

除去已介绍函数还不其他一些单行函数，在此称为其他函数。

函数	功能
nvl(exp1,exp2)	如果exp1的值为null,则返回exp2的值，否则返回exp1的值
nvl2(exp1,exp2,exp3)	如果exp1的值为null,则返回exp2的值，否则返回exp3的值
decode(value,if1,then1 ,if2,then2,…,else	如果value的值为if1，则返回then1的值;如果value的值为if2，则返回then2的值，… 否则返回else的值

select 
	ename,
	sal+nvl(comm,0) sal1,
	nvl2(comm,sal+comm,sal) sal2,
	decode(to_char(hiredate,'MM'),
	'01','一月','02','二月','03','三月','04','四月','05','五月','06','六月','下半年') mon
from employee;

6-6、聚合函数

能基于列进行计算，将多个直合并为一个值。对一组值进行计算，并返回计算后的值。

使用聚合函数进行统计值时，将忽略NULL值，但不忽略重复项。

sum()

返回表达式中所有数值的总和，只能用于数字列，只能返回一个数值，不能够直接与可能返回多行的列一起使用来进行查询。

select sum(score) from result where studentid=33 --查询学号为33的学生的成绩总和

avg()

返回表达式中所有数值的平均值，只能用于数值列。

select avg(score) from result where score>=60 --查询及格的平均值。

max()、min()

max()返回表达式中的最大值，min()返回表达式中的最小值，它们都可以用于数字型，字符型以及日期/时间类型的列。

select avg(score),max(score),min(score) from result where score>=60
	--查询及格线上的平均分、最高分、最低分

count()

返回提供的组成或记录龄中的计数。可以用于数字和字符类型的列，也可以使用星号’*’作为count()的表达式。

select count(*) from score where score>=60 --查询及格人数的语句 
select count(1000) from score where score>=60  --结果将和上面的一样
	--小提示：count(distinct 表达式),遇到重复数据，只记录一次

6-7、分析函数

Oracle从8.1.6版本开始提供分析函数。分析函数是对一组查询结果进行运算，然后获行结果。与聚合函数的区别在于返回多行，聚合函数每组返回一行。用于对分组后组内进行排序。

语法：

函数名([参数]) over ([分区子句][ 排序子句]);–分区子句(partition by)表示将查询结果分为不同的组，功能

类似于group by语句。默认将所有结果作为一个分组。排序子句(order by)默认是asc升序。

rank、dense_rank、row_number函数用于为每条记录产生一个从1开始至N的自然数，N的值可能小于记录的总数。这

3个函数用于解决排名问题。

1)row_number函数返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

2)dense_rank函数返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。

3)rank函数返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和

下一条不同记录的排名之间空出排名。

select ename,deptno,sal,
       rank() over (partition by deptno order by sal desc) rank,
       dense_rank() over (partition by deptno order by sal desc) "dense_rank",
       row_number() over (partition by deptno order by sal desc) "row_number"
from employee;

Oracle（二）

1、表空间

是数据库逻辑结构的一个重要组件，可存放各种应用对象，如表、索引。每一个表空间由一个或多个数据文件组成。

1-1、表空间分类

类别	说明
永久性表空间	一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的
临时性表空间	只用于保存系统中短期活动的数据，如排序数据等
撤销表空间	用来帮助回表未提交的事务数据，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

表空间的目的：

（1）对不同用户分配不同的表空间，对不同的模式对象分配不同的表空间，方便对用户数据的操作，对模式对象管理

（2）可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间，有利于提高I/O性能。

1-2、表空间的使用

创建表空间

create tablespace tablespace_name

datafile ‘filename’ [ size integer [K|M]]

[autoextend [off|on]];

--datafile指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。

--filename是数据文件的路径和名称。

--size指定文件的大小，用K指定千字节大小，用M指定兆字节大小。

--autoextend子句用来启用或禁用数据文件的自动扩展，设置为on则空间使用完毕会自动扩展，设置为off则很容

易出现表空间剩余容量为0的情况，使数据不能存储到数据库中。

例: create tablespace worktbs--表空间名

datafile ‘d:\orcle\worktbs.dbf’

size 10M autoextend on;

删除表空间

drop tablespace tablespace_name;

修改表空间

alter database --修改表空间文件大小 

datafile ‘F:\oracle\product\10.1.0\oradata\orcl\hello.dbf’

resize 10M;

alter tablespace hello  --为表空间hello添加一个数据文件

add datafile 'F:\oracle\product\10.1.0\oradata\orcl\helloAdd.dbf'

size 10M

2、自定义用户管理

当创建一个新数据库时，系统默认创建一些数据库用户，如Sys、System和Scott等。Sys和System都是Oracle的系统用户，Scott是Oracle数据库的一个示范账户，里面有一些测试样例表。

1.Sys，是Oracle中的一个超级用户。数据库中的所有数据字典和视图都存储在SYS模式中。数据存储了用来管理数据库对象的所有信息。主要用来维护系统信息和管理实例。Sys只能以SYSOPER或SYSDBA角色登录系统。

2.System,是Oracle中默认的系统管理员，它拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。管理Oracel数据库的用户、权限和存储等。不建议在System模式中创建用户表。System不能以SYSOPER或SYSDBA角色登录系统，只能以默认方式登录。

3.Scott，用户是一个示范用户包含4个示范表，其中一个是Emp表。使用USERS表空间存储模式对象。每个用户都有一个默认表空间和一个临时表空间。Oracle将USERS设为默认表空间，将TEMP设为临时表空间。

创建用户

create user user_name

identified by password

[default tablespace tablespace_name]

[temporary tablespace tablespace_name]

--user_name是用户名，用户名必须是一个标识符。

--password是用户口令，必须是一个标识符，且不区分大小写。

--default或temporary tablespace 为用户确定默认表空间或临时表空间。如果没有确定default表空间，则系

统默认把users表空间当作默认表空间。

例: create user martin--用户名

　　identified by martinpwd--口令

　　default tablespace worktbs--默认表空间

　　temporary tablespace temp;--临时表空间

修改口令(密码)

alter user martin　　--要修改的用户martin

identified by mpwd;--新口令

删除用户

drop user命令可以用于删除用户，但当用户拥有模式对象时则无法删除用户，而必须使用cascade选项删除用户和用户模式对象。

drop user martin;--没有模式对象的情况

drop user martin cascade;--用模式对象的情况

3、数据库权限管理

系统权限

是指被授权用户是否可以连接到数据库上及在数据库中可以进行哪些系统操作;系统权限是在数据库中执行某种系统级别的操作，或者针对某一类的对象执行某种操作的权利。

常用的系统权限：

create session :连接到数据库。

create table :创建表。

create view :创建视图。

create sequence:创建序列。

create public synonym:创建公有同义词

对象权限

是指用户对数据库中具体对象所拥有的权限。对象权限是针对某个特定的模式对象执行操作的权利。只能针对样式对象来设置和管理对象权限，如数据库中的表、视图、序列、存储对程、存储函数等。

Oracle数据库用户有两种途径获得权限：

1）管理员直接向用户授予权限。

2）管理员将权限授予角色，然后再将角色授予给一个用户。通常使用角色向用户授予权限。

Oracle中常用的系统预定义角色如下：

connect :需要连接上数据库的用户，特别是那些不需要创建表的用户，通常授予该角色。

resource :更为可靠和正式的库用户可以授予该角色，可以创建表、触发器、过程等。

dba :数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以给其他用户、角色授权，可以撤销任何别的用户甚至别的dba权限。所以不要轻易授予该权限。

（1）授予权限语法：grant 权限|角色 to 用户名;

（2）撤销权限语法：revoke 权限|角色 from 用户名;

例:

grant connect,resource to martin;--授予connnect和resource两个角色

revoke connect,resource from martin;--撤销connect和resource两个角色

grant select on scott.emp to martin; --允许用户查看scott模式下emp表的记录

授予表的访问权限

例：grant select on dept to public; --授予所有连接数据库的用户都可以访问这个表的权限

数据库用户安全设计原则：

1）数据库用户权限授权按照最小分配原则。

2）数据库用户要分为管理、应用、维护、备份四类用户。

3）不允许使用Sys和System用户建立数据库应用对象。

4）禁止grant dba to user;–授予dba角色

4、序列(sequence)

是用来生成唯一、连续的速递的数据库对象。序列通常用来自动生成主键或唯一键的值，可以是升序，也可以是降序。

创建序列

create sequence sequence_name

[start with integer]

[increment by integer]

[maxvalue integer|nomaxvalue]

[minvalue integer|nominvalue]

[cycle|nocycle]

[cache integer|nocache];

--start with :指定要生成的第一个序列号。对于升序，其默认值为序列最小值。对于降序，其默认值为序列最大值

--increment by :用于指定序列号之间的间隔，默认值为1。如果n为正值，则是升序序列。如果n为负值，则是降

序序列。

--maxvalue :指定序列可以生成的最大值。

--nomaxvalue :Oracle将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。 这是默认选项。

--minvalue :指定序列的最小值。必须小于或等于start with的值，并且必须小于maxvalue。

--nominvalue :Oracle将升序序列的最小值设为1,将降序序列的最小值设为-10的26次幂。这是默认选项。

--cycle :指定序列在达到最大值或最小值后，将继续从头开始生成值。

--nocycle :指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认值。

--cache :使用cache选项可以预先分配一组序列号，并将其保存在内存。当用完缓存中的所有序列号时，Oracle将

生成另一组数值，并将其保留在缓存中。

--nocache :使用nocache选项，则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了cache和

nocache选项，Oracle将默认缓存20个序列号。

例: create sequence myseq

start with 10 --从10开始

increment by 1 --间隔1

maxvalue 2000 --最大值2000

nocycle

cache 30; --缓存30个序列号

访问序列

nextval:创建序列后第一次使用nextval时，将返回该序列的初始值。以后将使用incrememt by 子句来增加序

列值，并返回这个新值。

currval:返回序列的当前值，即最后一次用nextval时返回的值。当序列是新建的，没有用过。则查询会报错

例:

 insert into toys(toyid,toyname,toyprice)

　　values (myseq.nextval,’TWENTY’,25);

　　select myseq.currval from dual;

更改序列

alter sequence 命令用于修改序列的定义。如果执行下列操作，则会修改序列：

设置或删除minvalue或maxvalue;

修改增量值;

修缓存中序列号的数目;

语法：

alter sequence [schema.]sequence_name

[increment by integer]

[maxvalue integer|nomaxvalue]

[minvalu integer|nominvalue]

[cycle|nocycle]

[cache integer|nocache];

– 不能修改序列的start with参数。注意最小值应小于最大值。

删除序列

drop sequence [schema.]sequence_name;

如：drop sequence myseq;

sys_guid函数生成32位的唯一编码，可以用来作为主键值。它源自不需要对数据库进行访问的时间戳和机器标识符。

select sys_guid() from dual;//每次都是不一样的

–在一个并行的环境里或者希望避免使用序列的情况下，才选择sys_guid来设置主键值。

5、同义词

用途

（1）简化SQL语句。

（2）隐藏对象的名称和所有者。

（3）为分布式数据库的远程对象提供了位置透明性。

（4）提供对对象的公共访问。

分类

1）私有同义词，只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同，要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限。如果在其他用户模式下创建私有同义词，必须拥有

create any synonym系统权限。

语法: create [or replace] synonym [schema.]synonym_name

for [schema.]object_name;

–or replace: 表示在同义词存在的情况下替换该同义词。

–object_name: 指定要为之创建同义词的对象名称。

例： create sysnonym sy_emp for a_hr.employee;–创建同义词

select * from sy_emp; --访问同义词

2)公有同义词，可被所有的数据库用户访问。公有同义词可以隐藏数据库对象的所有者和名称，并降低SQL语句的复杂性。创建它用户必须有create public synonym系统权限。

语法: create [or replace] public synonym synonym_name

for [schema.]object_name;

例: --在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名

create public synonym public_sy_emp for employee;

–在A_oe模式下访问公有同义词

select * form public_sy_emp;

注：使用同义词前，要获得同义词对应对象的访问权限。

对与私有同义词不能同名; 对象和公有同义词同名时，数据库优先选择对象作为目标，私有同义词和公有同义词同

名时，数据库优先选择私有同义词作为目标。

删除同义词：要删除同义词，用户必须拥有相应的权限。

drop [public] synonym [schema.]synonym_name;

例：drop synonym A_oe.sy_emp;–删除私有同义词

drop public synonym A_hr.public_sy_emp;–删除公有同义词

此命令只会删除同义词，不会删除对应的对象。

6、索引

索引是与表关联的可选结构，是一种快速访问数据的途径，可提高数据库性能。当索引键作为查询条件时，该索引将直接

指向包含这些值的得的位置。

一个列只能创建一种索引。

索引分类

物理分类	逻辑分类
分区或非分区索引	单列或组合索引
B树索引(标准索引)	唯一或非唯一索引
政常或反向键索引	基于函数索引
位图索引

1.B树索引，通常也称为标准索引。索引的顶部为根，其中包含揭向索引其中包含指向索引中下一级的项。

语法： create [unique] index index_name on table_name(column_list)

[tablespace tablespace_name];

–unique: 用于指定唯一索引，默认情况下为非唯一索引。

–index_name: 索引名称

–table_name: 表示为之创建索引的表名。

–column_list: 在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分割。

–tablespace_naem: 为索引指定表空间。

2.唯一索引和非唯一索引

唯一索引：定义索引的列中任何两行者没有重复值。唯一索引中的索引关键字只能指向表中的一行。在创建主键统束和创建唯一约束时者会创建一个与之对应的唯一索引。

非唯一索引：单个关键字可以有多个与其关联的行。

例：create unique index index_unique_grade on salgrade(grade);

3.反向键索引，与常规B树索引相反，反向键索引在保存列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。优点：对于连续增长的索引列，反转索引列，可以将索引数据分散在多个索引块间，减少I/O瓶颈的发生。

反向键索引通常建立在一此值连续增长的列上，如系统生成的员工编号，但不能执行范围搜索。

例：create index index_reverse_empno on employee(empno) reverse;

4.位图索引，优点在于它最适合低基数列（即该列的值是有限的，理论上不会是无穷大)。

优点：

1）对于大批即时查询，可以减少响应时间。

2）相比其他索引技术，点用空间明显减少。

3）即使在配置很低的终端硬件上，也能获得显著的性能。

例：create bitmap index index_bit_job on employee(job);

5.其他索引

组合索引：在表内多列上创建。索引中的列不必与表的中碰钉子顺序一致，也不必相互邻接，最多包含32列。

创建组合索引时，应将唯一性高的列放在第一位。

基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引列创建为B树或位图索引。

例：–在员工employee表中，为员工名称ename列创建大写函数索引

create index index_ename on employee(upper(ename));

创建索引

原则：

1）频繁搜索的列可以作为索引。

2）经常排序、分组的列。

3）经常用作连接的列（主键/外键）。

4）将索引放在一个单独的表空间中，不要放在有回退段、临时段和表的表空间中。

5）对大型索引而言，考虑使用nologging子句创建大型索引。

6）根据业务发生的频率，定期重新生成或重新组织索引，并进行碎片整理。

7）仅包含几个不同值的列不可以创建为B树索引，根据需要创建位图索引。

8）不要在仅包含几行的表中创建索引。

删除索引

1）使用drop index语句用于删除索引

drop index index_bit_job;–在Oracle中索引名在用户账户中是唯一的，删除时不需要指定表名。

2）何时应删除索引：

（1）应用程序不再需要索引。

（2）执行批量加载前删除索引。大量加载数据前删除索引，加载后再重建索引有以下好处：①提高加载性能;

②更有效地使用索引空间。

（3）索引国损坏。

重建索引

1.alter index…rebuild 语句用于重建索引

alter index index_reverse_empno rebuild noreverse;

2.何时应重建索引

（1）用户表被移动到新的表空间后，表上的索引不是自动转移，需将索引移到揭定表空间。

alter index index_name rebuild tablespace tablespace_name;

（2）索引中包含很多已删除的项。对表进行频繁删除，造成索引空间浪费，可以重建索引。

（3）需将现有正常索引转换成反向键索引。

7、表分区

Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每一个部分称为一个分区。

优点

1）改善表的查询性能，在对表进行分区后，用户执行SQL查询时可以只访问表中的特定分区而非整个表。

2）表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。

3）便于备份和恢复，可以独立地备份和恢复每个分区。

4）提高数据安全性，将不同的分区分布在不同的磁盘，可以减小民有分区的数据同时损坏的可能性。

分区条件

1）数据量大于2GB。

2）已有的和新添加的数据有明显的界限划分。

3）要分区的表不能具有long和long raw数据类型的列。

表分区分类

分区表的分类：范围分区、列表分区、散列分区、复合分区、间隔分区和、虚拟列分区等。

1.范围分区(range)是应用范围比较广的表分区方式，它以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

可以在分区后加上(tablespace tablespace_name)来指定该分区在哪个表空间中。这样可以提高查询性能和安全性。

一般创建范围分区时，都会将最后一个分区设置为maxvalue，使其他落入此分区。一旦需要某一数据时，可以利用拆分分区的技术将需要的从最后一个分区分离出去，单独形成一个分区，如果没有创建足够大的分区，插入

的数据超出范围就会报错。如果插入的数据就是分区键上的值，则该数据落入下一分区。在按时间分区时，如果某些记录暂时无法预测范围，则可以创建maxvalue分区，所有不在指定范内的记录都会被存储到maxvalue所在的分区中。

例:

[ 复制代码 ](javascript:void(0)?

 create table sales1
(
    sales_id number,
    product_id varchar2(5),
    sales_date date not null,
    ....
)
partition by range (sales_date)
(
partition p1 values less than (to_date(‘2013-04-1’,’yyyy-mm-dd’)),

partition p2 values less than (to_date(‘2013-07-1’,’yyyy-mm-dd’)),

partition p3 values less than (to_date(‘2013-10-1’,’yyyy-mm-dd’)),

partition p4 values less than (to_date(‘2014-01-1’,’yyyy-mm-dd’)),

partition p5 values less than (maxvalue)

);

[ 复制代码 ](javascript:void(0)?

–查看第三季度的数据：select * from sales1 partition(p3);

–删除第三季度的数据：delete from sales1 partition(p3);

2.间隔分区(Interval)是Oracle 11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自

动化。优点为，在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加合划分更多的分区，并自动自

动创建新的分区。

例: --创建间隔分区表

[ 复制代码 ](javascript:void(0)?

create table sales2
(
    sales_id number,

    product_id varchar2(5),

    sales_date date not null,

    ....
)
    partition by range(sales_date)

    interval(numtoyminterval(3,’MONTH’))

    (partition p1 values less than (to_date(‘2013-05-1’,’yyyy/mm/dd’)));

[ 复制代码 ](javascript:void(0)?

–插入数据

insert into sales2 values (1,’a’,to_date(‘2013-08-1’),’1’);

–获得分区情况

select table_name,parttion_name

from user_tab_partitions

where table_name=uppper(‘sales2’);

–查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”

TABLE_NAME PARTITION_NAME

SALES2 P1

SALES2 SYS_P82

–查询分区数据

select * from sales2 partition(sys_p82);

1）只需创建第一个开始分区。

2）interval(numtoyminterval(3,’MONTH’))语句中，interval代表“间隔”，即按照后面括号中的定义间隔添加分区。

3）numtoyminterval(3,’MONTH’)表示每3个月为一个分区。

numtoyminterval(n, ‘interval_unit’)函数用于将n转换成interval_unit所指定的值。

interval_unit的值可以为YEAR或MONTH。

与该类型相关的函数还有numtodsinterval(n, ‘interval_unit’)，用于将n转换成interval_unit所指

定的值。这里的interval_unit的值可以设为DAY，HOUR，MINUTE，SECOND。但不支持YEAR和MONTH

4）系统会根据自动创建分区。

经验：可以利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表。代码如下：

[ 复制代码 ](javascript:void(0)?

create table sales3

    partition by range(sales_date)

    interval(numtoyminterval(3,’MONTH’))

    partition p1 values less than (to_date(‘2013-04-1’,’yyyy/mm/dd’)))

    as select * from sales; --sales表为已经创建的表

[ 复制代码 ](javascript:void(0)?

3.列表分区列的值可以枚举的

如:

[ 复制代码 ](javascript:void(0)?

create table   sales(

　　sid number,

　　sarea varchar2(10),

　　total number

)

partition by list (sarea)(

    partition part1 values('北京') tablespace space1,

    partition part2 values('上海') tablespace space2,

    partition part3 values('广东') tablespace space3

);

[ 复制代码 ](javascript:void(0)?

4.散列分区，服务器自动给每一行给一个hash值，然后自动分区。只需要指定有哪些分区就可以了

[ 复制代码 ](javascript:void(0)?

create table employee(

empno number,

ename varchar2(10)

)

partition by hash(ename)(

   artition part1 tablespace space1,

   partition part2 tablespace space2,

   partition part3 tablespace space3

);

select * from  employee partition (part1);

select * from  employee partition (part2);

select * from  employee partition (part3);

PL/SQL

1、PL/SQL组成及其体系结构

1-1、 PL/SQL的体系结构

什么是PL/SQL?

定义： PL/SQL是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL引擎驻留在Oracle服务器中，该引擎接受PL/SQL块并对其进行编译执行，PL/SQL引擎包括过程语句执行器和SQL语句执行器

PL/SQL的优点

a1.支持SQL:SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库中的数据，PL/SQL支持所有的SQL命令，游标控制命令，事务控制命令，SQL函数，运算符和伪列等

a2.PL/SQL支持所有的SQL数据类型和NULL值

a3.支持面向对象编程

a4.性能更好：SQL是非过程语言，只能一条一条的执行，而PL/SQL可以将过程语句和SQL语句的多行代码块统一编译，然后执行。同时还可以把编译好的PL/SQL块存储起来，以备以后再用，减少应用程序和服务器之间的通信时间。所以PL/SQL执行效率会更高

a5.可移植性好：使用PL/SQL编写的应用程序语言，可以移值到任何操作平台的oracle服务器上。同时还可以编写可移植性的程序库，在不同的环境中使用

PL/SQL块

PL/SQL块是构成PL/SQL程序的基本单元，它将逻辑上相关的声明和语句组合在一起

PL/SQL分为三个部分：声明部分，可执行部分和异常部分

语法：

[declare 声明部分]

begin

可执行语句

[Exception 异常部分]

end;

注意：

a1. PL/SQL是一种编程语言，有自已独有的数据类型，变量的声明以及流程控制语句，

a2.对大小写不敏感，为了规范，关键字全部大写，其余部分小写

a3.每一条语句以分号结束

1-2、变量的声明和常量

声明变量方法一：使用 := 来给变量赋值，

语法：变量名数据类型(长度)[:=初始值];

声明常量语法：变量名 CONSTANT 数据类型(长度):=常量值;

如下例子：

declare
 v_stuno number(5,0); --声明变量
 v_stuname varchar(20);
 v_sex char(2):='男'; --声明变量时就赋值
 c_num constant number(5,0):=5; --声明常量，并给常量赋值
 begin
  v_stuno:=100;  --给变量赋值
  v_stuname:='张三';
  dbms_output.put_line('学号：'||v_stuno||'  姓名：'||v_stuname||' 性别：'||v_sex||' 常量为:'||c_num);
 end;

声明变量并赋值方法二：select … into …

declare
 v_stuname varchar(20) ;
 v_sex char(4);
begin
  select stuname,sex into v_stuname,v_sex from student where stuno=101;
  dbms_output.put_line('姓名：'||v_stuname||' 性别：'||v_sex);
  end;

优化上面的变量的数据类型：

  declare
 v_stuname student.stuname%type ;
 v_sex student.sex%type;
begin
  select stuname,sex into v_stuname,v_sex from student where stuno=101;
  dbms_output.put_line('姓名：'||v_stuname||' 性别：'||v_sex);
  end;

如果查询表中的列比较多的话，声明变量就很多，挨个select into赋值会很繁琐，再次优化

 declare
  v_stu student%rowtype; --声明一个变量为表中数据行的类型
  begin
     select * into v_stu from student where stuno=101 ;
     dbms_output.put_line('学号：'||v_stu.stuno||'姓名：'||v_stu.stuname);
    end;

1-3、PL/SQL中的特殊符号

类型	符号	说明
赋值运算符	:=	PL/SQL中表示给变量赋值
连接字符	\|\|	字符串连接操作符，相当于Java中的+号
注释	–	PL/SQL中的单行注释
注释	/**/	PL/SQL中的多行注释，不可嵌套
分隔符	<<>>	标签分隔符，只为标识程序特殊位置
范围操作符	…	如 1…5 表示从1到5
算术运算符	+,-,*,/
幂运算符	**	求幂运算如3**2=9
关系运算符	>,<,>=,<=,=	其中=表示相等，不是赋值
关系运算符	<> ，！=	不等关系
逻辑运算符	AND,OR,NOT

2.了解变量的数据类型

2-1、内置数据类型

数据类型:

标量类型：数字类型，字符类型，布尔类型，日期时间

LOB类型：BLOB,CLOB,NCLOB,BFILE

属性类型: %type :表示某个变量或数据库表中列的数据类型

%rowtype:表示表中一行的记录类型

2-2、字符数据类型

	char

varchar2

long

raw

long raw

2-2、SQL与PL/SQL

数据类型	SQL类型（字节）	PL/SQL类型（字节）
CHAR	1…2000	1…32767
LONG	1…32760	1…2147483647
LONG RAW	1…2GB 固定长度二进行数据	1…32760
RAW	1…2000	1…32767
VARCHAR2	1…4000	1…32767

类型	说明
NVARCHAR2	unicode编码可变长度数据
NCHAR	unicode编码固定长度数据
DATE	日期类型数据,不包含毫秒，7字节的列
TIMESTAMP	DATE子类型，包含日期和时间，时间包括毫秒
NUMBER(精度，小数位)	数字类型
BOOLEAN	布尔类型，可设：TRUE,FALSE,NULL
CLOB	字符型大对象，最多4G
NCLOB	存unicode编码大文本数据，最多4G
BLOB	二进制数据，最大4G
BFILE	存储外部文件，最大4G

2-4、属性类型

用于引用数据库列的数据类型以及表中记录行的类型

-%TYPE

-%ROWTYPE

使用属性类型的优点：

a1.不需要知道被引用的表列的具体类型和长度

a2.如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变

3、流程控制

3-1、条件控制

IF语句

--统计一年级学生的人数
declare
    v_num number; --人数
    v_gradename grade.gradename%type:='一年级';      
begin
    select gradename ,count(*) into v_gradename,v_num from student s inner join grade g
    on s.gradeid=g.gradeid 
    group by gradename
    having gradename='一年级';
    dbms_output.put_line(v_gradename||'的人数为：'||v_num);--输出人数
end;

优化

 --统计一年级学生的人数，改为用户手动输入数据
  declare
       v_num number;
       v_gradename grade.gradename%type:=&gradename;  --动态输入值以&变量名    
      begin
        select gradename ,count(*) into v_gradename,v_num from student s inner join grade g
        on s.gradeid=g.gradeid 
        group by gradename
        having gradename=v_gradename;
        if v_num>0 then
           dbms_output.put_line(v_gradename||'的人数为：'||v_num);
          else
            raise NO_DATA_FOUND;  --找不到记录抛出异常        
            end if; 
            exception
              when NO_DATA_FOUND then
                 dbms_output.put_line(v_gradename||'暂无学生');
              when OTHERS then
                 dbms_output.put_line('未知异常');
                    
        end;

  --求学生的平均分的等级---多分支选择结构
      declare
        v_level char(10);--平均分的等级
        v_avgscore number; --平均分
        v_stuno number:=&stuid; --手动输入学号
      begin
        select avg(stuscore) into  v_avgscore
         from score 
         where stuno=v_stuno;
        if v_avgscore>90 then
          v_level:='优秀';
          elsif v_avgscore>80 then
            v_level:='中等';
            elsif v_avgscore>60 then
              v_level:='一般';
              else
                v_level:='差';
            
        end if;
        dbms_output.put_line(v_stuno||'的平均分为：'||v_avgscore||'等级：'||v_level); 
        end;

CASE语句

     declare
        v_level char(10);--平均分的等级
        v_avgscore number; --平均分
        v_stuno number:=&stuid; --手动输入学号
      begin
        select avg(stuscore) into  v_avgscore
         from score 
         where stuno=v_stuno;
         case
           when v_avgscore>90 then
           v_level:='优秀';
            when  v_avgscore>80 then
            v_level:='中等';  
            when  v_avgscore>60 then
            v_level:='中等'; 
            else
            v_level:='差'; 
            end case;
               dbms_output.put_line(v_stuno||'的平均分为：'||v_avgscore||'等级：'||v_level); 
       end;

3-2、循环控制

LOOP循环

  --求1+2+3+...+100=?
       declare
        v_i number:=1; --循环变量
        v_sum number:=0; --和
       begin
         loop
             v_sum:=v_sum+v_i;
             v_i:=v_i+1;
             exit when v_i>100; --结束循环
           end loop;
           dbms_output.put_line('1+2+..+100='||v_sum);
         end;

WHILE循环

 --求1+2+3+...+100=? while循环
       declare
        v_i number:=1; --循环变量
        v_sum number:=0; --和
       begin
         while v_i<=100 loop
             v_sum:=v_sum+v_i;
             v_i:=v_i+1;
           end loop;
           dbms_output.put_line('1+2+..+100='||v_sum);
         end;

FOR循环

     --求1+2+3+...+100=? for循环
       declare
        v_i number:=1; --循环变量
        v_sum number:=0; --和
       begin
        for v_i in 1..100 loop
             v_sum:=v_sum+v_i;
            -- v_i:=v_i+1;
           end loop;
           dbms_output.put_line('1+2+..+100='||v_sum);
         end;

3-3、顺序控制

GOTO语句

NULL语句

--需求：循环显示变量v_count的值，如果该变量大于10则结束循环，并显示该变量的最终值
declare
	v_count number:=1;
begin
	loop
        dbms_output.put_line('v_count='||v_count);
        v_count:=v_count+1;
		if v_count>10 then
           GOTO mark; --跳转到标签处
		else
			null; --此处没意义，只是保证语句完整
		end if;
	end loop;
       <<mark>>
         dbms_output.put_line('v_count当前值='||v_count);
        end;

4、异常

在运行程序时出现的错误叫做异常，当发生异常后，语句将停止执行，控制权转移动PL/SQL块的异常处理部分。

4-1、异常分类

预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发;用户不需要在程序中定义

非预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发;用户需要在程序中定义

4-2、预定义的异常

TOO_MANY_ROWS : SELECT INTO返回多行
INVALID_CURSOR :非法指针操作(关闭已经关闭的游标)
ZERO_DIVIDE :除数等于零
DUP_VAL_ON_INDEX :违反唯一性约束
ACCESS_INTO_NULL: 未定义对象 
CASE_NOT_FOUND: CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 
COLLECTION_IS_NULL: 集合元素未初始化 
CURSER_ALREADY_OPEN: 游标已经打开 
INVALID_NUMBER: 内嵌的 SQL 语句不能将字符转换为数字 
NO_DATA_FOUND: 使用 select into 未返回行，或应用索引表未初始化的元素时 
SUBSCRIPT_BEYOND_COUNT:元素下标超过嵌套表或 VARRAY 的最大值 
SUBSCRIPT_OUTSIDE_LIMIT: 使用嵌套表或 VARRAY 时，将下标指定为负数  
VALUE_ERROR: 赋值时，变量长度不足以容纳实际数据 
LOGIN_DENIED: PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 
NOT_LOGGED_ON: PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 
PROGRAM_ERROR: PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 
ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 
SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象方法 
STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间 
SYS_INVALID_ID: 无效的 ROWID 字符串 
TIMEOUT_ON_RESOURCE: Oracle 在等待资源时超时 

others可以代表所有异常，oracle预定义的异常在20000以内。

语法：

第一步：定义异常

<异常情况> EXCEPTION;

第二步：将定义好的异常情况与标准的oracle错误联系起来,使用EXCEPTION_INIT语句

PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);

第三步：有EXCEPTION语句中处理异常

例：

--向部门表中插入已存在的部门，会报主键约束的异常
declare
  v_deptno dept_bak.deptno%type:=&deptno;
  v_dname dept_bak.dname%type:=&dname;
  v_loc dept_bak.loc%type:=&loc;
  --定义一个非预定性异常
  dept_error exception;
  --将错误代码与指定异常关联
  pragma exception_init(dept_error,-1);
 begin
    insert into dept_bak values (v_deptno,v_dname,v_loc);
    --delete from dept_bak where deptno=v_deptno;
 exception 
   when dept_error then
     dbms_output.put_line('违返主键约束异常');
   when others then
     dbms_output.put_line('其它异常');   
   end;

--删除部门时先删除员工
declare
  v_deptno dept_bak.deptno%type:=&deptno;
  --定义一个非预定性异常
  dept_error exception;
  --将错误代码与指定异常关联
  pragma exception_init(dept_error,-2292);
 begin  
   delete from emp_bak where deptno=v_deptno;--先删除员工
   delete from dept_bak where deptno=v_deptno;--再删除部门
   commit;
 exception 
   when dept_error then
     dbms_output.put_line('违返外键约束异常');
     rollback;--回滚事务
   when others then
     dbms_output.put_line('其它异常');  
     
   end;

4-3、用户定义异常

需要用户在程序中定义，显示地在程序中将其引发

a1.第一步：在PL/SQL中定义异常情况；异常变量 exception;

a2.RAISE <异常情况>;

a3.在PL/SQL的EXCEPTION 部分处理异常

例子：

--更新的记录找不到的自定义异常
declare
  v_empno emp_bak.empno%type:=&empno;
  v_ename emp_bak.ename%type:=&aa;
  no_result_error exception; --自定义异常类型
  begin
     update emp_bak set ename=v_ename where empno=v_empno;    
     if SQL%ROWCOUNT<>1 then
       raise no_result_error;
       end if;
       commit;
     exception 
       when no_result_error then
         dbms_output.put_line('没有找到要更新的记录');
         rollback;
       when others then
         dbms_output.put_line('其它异常');     
    end;

   ----需求：查询编号为7788的雇员的福利（comm列）,如果福利为空，则手动抛出异常
   --并给员工加100元福利
   declare
   v_empno emp_bak.empno%type:=&empno; --查询员工编号
   v_comm emp_bak.comm%type; --福利
   v_not_comm_exception exception; --自定义异常
   begin
      select comm into v_comm from emp_bak 
      where empno=v_empno;
      if v_comm is null then
        raise v_not_comm_exception; --手动抛出异常
        end if;
      exception 
        when v_not_comm_exception then
          dbms_output.put_line('没有福利的异常,默认增加200元福利');
          update emp_bak set comm=200 where empno=v_empno;
          commit;
        when others then
          dbms_output.put_line('未知异常');    
     end;

5、游标

5-1、为什么使用游标

因为程序只能接收单个变量或一条记录，不能接收结果集，如何将结果集中的数据通过程接收呢？此时就需要游标，通过游标逐条读取结果集中的数据

5-2、什么是游标

游标是系统为用户开设的一个数据缓存区，用来存放执行后的结果每个游标区都有一个名字，用户可以通过游标逐一获得记录

5-3、游标的分类

静态游标分为：隐式游标和显示游标

隐式游标:pl/sql执行的DML(即增，删，改，查等操作)时自动创
建隐式游标，自动声明，打开和关闭

显示游标：用来处理返回多行的查询

动态游标分为：强类型和弱类型

强类型：必须指定游标变量的返回值类型

弱类型：定义时不指定游标变量的返回值类型

动态游标和静态游标的区别：
a1.静态游标是在声明时就已知游标绑定的sql查询;而动态游标在运行时(open 时)才绑定sql查询
a2.静态游标只能表示指定sql查询的结果，而动态可以多次指定不同sql查询的结果
a3.动态游标要先定义游标类型，再定义游标变量，绑定sql,而静态声明时一次绑定sql,语法不同

5-4、游标的使用

隐式游标：

  --用户输入学号，查询指定学生的学号，姓名，性别
  declare
       v_stuno student.stuno%type:=&stuno;
       v_stu student%rowtype;
      begin
        select * into v_stu from student where stuno=v_stuno;       
        dbms_output.put_line(v_stu.stuno||v_stu.stuname||v_stu.sex);
        end;

游标的属性：

%FOUND – SQL 语句影响了一行或多行时为 TRUE

%NOTFOUND – SQL 语句没有影响任何行时为TRUE

%ROWCOUNT – SQL 语句影响的行数

%ISOPEN - 游标是否打开，始终为FALSE

显示游标的使有以下操作:

a1. 声明游标
a2.打开游标
a3.提取游标
a4.关闭游标

 --查询所有的学生信息
  declare
    v_stu student%rowtype;
    --声明一个显示静态游标
   cursor cursor_stu is  select * from student; --声明一个游标
  begin
     open cursor_stu; --打开游标
     loop
       fetch cursor_stu into v_stu; --提取游标数据
       dbms_output.put_line(v_stu.stuno||v_stu.stuname);
       exit when cursor_stu%NOTFOUND;
     end loop;
     close cursor_stu;--关闭游标
    end;

 --通过游标更新，薪水低于3000的雇员的薪水增加500元，增加后超出3000元，则薪水为3000.
 --方法一：
   declare
      cursor cur_emp is select * from emp_bak where sal<3000;
      v_emp emp_bak%rowtype; --员工行记录类型
   begin
     if not cur_emp %ISOPEN then
       open cur_emp ; --打开游标
     end if;
      loop 
       fetch cur_emp into v_emp; --提取游标的记录
       if v_emp.sal+500<3000 then
           update emp_bak set sal=sal+500 where empno=v_emp.empno;
       else
           update emp_bak set sal=3000 where empno=v_emp.empno;
          end if;
        exit when cur_emp%NOTFOUND;
       end loop;
       close cur_emp; --关闭游标    
     end;

--方法二：通过游标更新，薪水低于3000的雇员的薪水增加500元，增加后超出3000元，则薪水为3000.
   declare
      cursor cur_emp is select * from emp_bak where sal<3000 for update;
      v_emp cur_emp%rowtype; --员工行记录类型
   begin
     if not cur_emp %ISOPEN then
       open cur_emp ; --打开游标
     end if;
      loop 
       fetch cur_emp into v_emp; --提取游标的记录
       exit when cur_emp%NOTFOUND;
         if v_emp.sal+500<3000 then
           update emp_bak set sal=sal+500 where  CURRENT  of cur_emp;
         else
           update emp_bak set sal=3000 where  CURRENT  of cur_emp;
          end if;   
       end loop;
       close cur_emp; --关闭游标    
     end;

动态游标：

动态游标使用步骤：
第一步：定义一个动态游标类型
   type 游标类型名称  is ref cursor;
第二步：定义一个游标变量
   游标变量名   游标类型名称;
 第三步:动态绑定sql到游标
   open 游标变量名 for select语句
 第四步：fetch 提取记录
 第五步：关闭游标

 --动态游标
    declare
    --定义一个动态游标类型
     type emp_cursor_type is ref cursor;
     --定义一个游标变量
     c1 emp_cursor_type;
     v_emp emp_bak%rowtype;
     v_dept dept_bak%rowtype;
    begin
      --查询部门编号为20的员工
      open c1 for select * from emp_bak where deptno=20;
      loop
         fetch c1 into v_emp ;
         dbms_output.put_line('编号：'||v_emp.empno||'名字'||v_emp.ename);
         exit when c1%NOTFOUND; 
       end loop; 
       --查询所有部门信息
       open c1 for select * from dept_bak;
       loop 
          fetch c1 into v_dept;
          dbms_output.put_line('部门编号：'||v_dept.deptno||'部门'||v_dept.dname||'位置：'||v_dept.loc);
          exit when c1%NOTFOUND;
         end loop;
         close c1;--关闭游标
     end;

--显示部门编号为20号部门的员工信息和部门是10和20部门信息
declare
  type cursor_emp_dept is ref cursor; --定义一个动态游标
  c cursor_emp_dept ; --声明一个游标变量
  v_dept dept_bak%rowtype;
  v_emp emp_bak%rowtype;
  v_deptno dept_bak.deptno%type:=&deptno;
begin
   open c for select * from emp_bak where deptno=v_deptno;
   loop
      fetch c into v_emp ;
      exit when c%NOTFOUND;
      dbms_output.put_line(v_emp.empno||v_emp.ename);
     end loop;
     open c for select * from dept_bak where deptno=10 or deptno=20;
     loop
        fetch c into v_dept;
        exit when c%NOTFOUND;
        dbms_output.put_line(v_dept.deptno||v_dept.dname);
       end loop;
    close c;
  end;

6、存储过程

6-1、创建存储过程语法

CREATE [OR REPLACE] PROCEDURE <procedurename> [(<parameter list>)]
IS|AS
<LOCAL variable declaration>
BEGIN
  <executeable statements>
  [EXCEPTION
  <Exception handlers>
  ]
END;

例子：

--使用存储过程增加部门
 create or replace procedure pro_addDept(v_dname varchar2,--输入参数 v_loc varchar2 --输入参数)
 is
 begin   
   insert into dept_bak values (dept_bak_seq.nextval,v_dname,v_loc);
   commit;
  end;

6-2、调用存储过程

--pl/sql执行存储过程
begin
  pro_addDept('开发部','开发部办公室');
  end;

 --增加异常处理
 create or replace procedure pro_addDept(
  v_deptno number,--输入参数
  v_dname varchar2,--输入参数，部门名
  v_loc varchar2 --输入参数，位置   
 )
 is
 deptno_isnull exception;
 pragma exception_init(deptno_isnull,-1400);--违返非空异常
 dname_notUQ  exception;
 pragma exception_init(dname_notUQ,-1); --非预定异常，违反唯一约束
 
 begin   
   insert into dept_bak values (v_deptno,v_dname,v_loc);
   commit;
   exception 
     when deptno_isnull then
     dbms_output.put_line('部门编号不能为空');
    when dname_notUQ then
     dbms_output.put_line('部门名称不能重复');
     when others then
     dbms_output.put_line('未知异常');
  end;

6-3、存储过程例子

--PL/SQL执行存储过程
begin
  pro_addDept('开发部','开发部办公室');
  end;
--sqlplus执行存储过程
exec  pro_addDept('开发部','开发部办公室'); 
或
 exec pro_addDept(45,v_dname=>'测试',v_loc=>'测试部');
 
 sqlplus下执行存储过程:可以设控制台上输出
 set serveroutput on;
 declare
 v_stuname varchar2(30);
 v_sex char(4);
 begin
    add_grade_pro(1000,v_stuname,v_sex);
    dbms_ouptput.put_line(v_stuname||'  '||v_sex);
 end;

6-4、传递参数三种模式

IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值

 ----查询学号为7788的员工信息,用存储过程来实现
  create or replace procedure pro_getEmpByNo(
    v_empno number, --输入参数,注意是逗号隔开参数
    v_emp out emp_bak%rowtype --输出参数
  )is
  begin
      select * into v_emp from emp_bak where empno=v_empno;
    end;

调用存储过程：
 declare
  v_emp emp_bak%rowtype;
  begin
     pro_getEmpByNo(7788,v_emp);
     dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);
    end;

--存储过程执行查询，如果是多行多列的结果，需要用游标
  --查询编号为20的员工信息
     create or replace procedure pro_getEmpByDeptno(v_deptno in out number,cursor_emp out sys_refcursor)
     is
     begin
        open cursor_emp for select * from emp_bak where deptno=v_deptno;
        v_deptno:=10;     
       end; 
    --执行存储过程
    declare
      v_deptno emp_bak.deptno%type:=&deptno;
      v_emp emp_bak%rowtype;
      cursor_emp  sys_refcursor;
      begin
         pro_getEmpByDeptno(v_deptno,cursor_emp);
         loop
           fetch cursor_emp into v_emp;
            exit when cursor_emp%NOTFOUND;
            dbms_output.put_line(v_deptno||' '||v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);        
           end loop;
           close cursor_emp;
       end;

6-5、授权其他用户

SQL> GRANT EXECUTE ON find_emp TO MARTIN;
SQL> GRANT EXECUTE ON swap TO PUBLIC;

6-6、删除存储过程

DROP PROCEDURE procedure_name;

7、触发器

7-1、触发器简介

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。

7-2、触发器的分类

a1.行级触发器：行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。

a2.语句级触发器：语句级的触发器可以在某些语句执行前或执行后被触发。

a3.INSTEAD OF触发器

具体举例：

1、在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。

7-3 、触发器语法

语法如下：

create [or replace] trigger 触发器名 
before（之前） | after（之后） |  instead of（替换）
on 表名
for each row（行级触发器）
declare 
 声明
begin
 pl/sql语句
end

其中:

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途

触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
instead of：表示该触发器替换数据库的操作

触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。

表名：数据库触发器所在的表。

for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

7-4、触发器使用

触发器实现主键自增

1、创建一个测试表：

create table tab_user(
  id number(11) primary key,
  username varchar(50),
  password varchar(50)
);

2、创建一个序列：

create sequence myseq;

3、创建一个触发器：

CREATE OR REPLACE TRIGGER MY_TGR
 BEFORE INSERT ON TAB_USER
 FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
 NEXT_ID NUMBER;
BEGIN
 SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
 :NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;

4、向表插入数据:

insert into tab_user(username,password) values('admin','admin');
insert into tab_user(username,password) values('fgz','fgz');
insert into tab_user(username,password) values('test','test');
COMMIT;

增删改操作的日志记录

1、创建两张表用于测试：

--创建测试表
CREATE TABLE test(
  t_id  NUMBER(4),
  t_name VARCHAR2(20),
  t_age NUMBER(2),
  t_sex CHAR
);

--创建记录测试表
CREATE TABLE test_log(
  l_user  VARCHAR2(15),
  l_type  VARCHAR2(15),
  l_date  VARCHAR2(30)
);

2、创建触发器：

--创建触发器
CREATE OR REPLACE TRIGGER TEST_TRIGGER
 AFTER DELETE OR INSERT OR UPDATE ON TEST
DECLARE
 V_TYPE TEST_LOG.L_TYPE%TYPE;
BEGIN
 IF INSERTING THEN
  --INSERT触发
  V_TYPE := 'INSERT';
  DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
 ELSIF UPDATING THEN
  --UPDATE触发
  V_TYPE := 'UPDATE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
 ELSIF DELETING THEN
  --DELETE触发
  V_TYPE := 'DELETE';
  DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
 END IF;
 INSERT INTO TEST_LOG
 VALUES
  (USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
END;

3、执行增，删，改操作：

INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;

行级触发器的使用

--行级触发器:当增加，修改，删除表中记录时，每操作一条记录都是会触发一次触发器
	--例子：使用触发器监控emp_bak表的增，删，改操作，给出相应提示
    create or replace trigger  trg_emp_update
    after insert or delete or update on emp_bak --监控增，删，改查操作
    for each row --行级触发器
      begin
         if updating then
             dbms_output.put_line(user||'正在修改'||:old.empno||'的记录');
           elsif deleting then
              dbms_output.put_line(user||'正在删除'||:old.empno||'的记录');
           elsif inserting then
              dbms_output.put_line(user||'正在插入'||:old.empno||'的记录');
           end if;         
        end;

删除emp_bak表中的记录，观察触发器是否调用：
 delete from emp_bak where empno<7700; --删除员工编号为7700的员工，会删除多条记录
 显示结果为:
SCOTT正在删除7521的记录
SCOTT正在删除7566的记录
SCOTT正在删除7654的记录
SCOTT正在删除7698的记录

语句级触发器的使用

create or replace trigger  trg_emp_update2
    after insert or delete or update on emp_bak --监控增，删，改查操作
      --此处不写行级还是语句级时，就是语句级触发器,不要写:new 或:old
      begin
         if updating then
             dbms_output.put_line(user||'正在修改的记录');
           elsif deleting then
              dbms_output.put_line(user||'正在删除的记录');
           elsif inserting then
              dbms_output.put_line(user||'正在插入的记录');
           end if;         
        end;
        --注意：语句级触发器中不能使用:new  和  :old

INSTEAD OF触发器：替代触发器

使用场合：

对于简单视图，可以直接执行INSERT，UPDATE和DELETE操作
但是对于复杂视图，不允许直接执行INSERT，UPDATE和DELETE操作。
为了在具有以上情况的复杂视图上执行DML操作需要替代触发器来完成

--创建复杂的视图
create or replace view view_dept_emp
as
select empno,ename,sal,e.deptno,dname from emp_bak e
inner join dept_bak d on e.deptno=d.deptno

使用视图向原表中插入记录：执行结果：失败

--向视图中插入记录
insert into view_dept_emp values (8000,'zhangsan',5000,10,'开发部');
/*执行失败原因，因为该视图涉及到2个表，emp是键保留表，dept_bak是非键保留表。如果插入键保留表的数据，则不会报错，如果插入非键保留表的数据，则报错。
--什么是键保留表，是复杂视图（使用多个表创建的视图）中的其中一个表，该表的主键在视图中都是唯一且非空的，也是视图中的键，则该表是一个键保留表，如emp_bak表。*/

--通过键保留表修改记录，发现成功
update view_dept_emp set ename='lisi' where empno=7900

通过对视图的插入语句触发INSTEAD OF触发器实现：
如果添加的部门不存在，则插入部门表
如果添加的雇员不存在，则插入雇员；如果存在，则报主键重复错误

create or replace trigger trg_view_deptemp
 instead of 
 insert on view_dept_emp
for each row --行级触发
   declare 
    v_deptno dept_bak.deptno%type;
    v_empno emp_bak.empno%type;
    error_msg exception;--定义一个异常变量
   begin
     --查询是否有要插入的部门编号，没有则可以插入部门信息
      select count(*) into v_deptno from dept_bak where deptno=:new.deptno;
     if v_deptno=0 then
       insert into dept_bak (deptno,dname) values (:new.deptno,:new.dname);
     else
       raise error_msg;--抛出异常
     end if;
     select count(*) into v_empno from emp_bak where empno=:new.empno;
     if v_empno= 0 then
         insert into emp_bak( empno,ename,sal) values (:new.empno,:new.ename,:new.sal);
      else
       raise error_msg;--抛出异常
     end if;
     end;

创建好触发器后，再调用视图向两张表插入记录

 --向视图中插入记录
insert into view_dept_emp values (8000,'zhangsan',5000,10,'开发部')
select * from emp_bak;  --发现8000员工成功插入到emp_bak表中
select * from dept_bak;  --发现10，开发部成功插入到dept_bak表中

7-5、注意

1、触发器不接受参数。

2、一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

3、在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。 

4、触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

5、在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。 

6、触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

7、在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。 

8、在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。  9、不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

8、函数的使用

8-1、函数的创建和调用

函数也需要先定义，再调用，定义语法如下：

create or replace function 函数名字(
参数1 数据类型(没有长度),
参数2 数据类型(没有长度),
参数3 数据类型(没有长度)
)return 返回值类型(不允许有长度)
is
参数4 数据类型（可以有长度）;
参数5 数据类型（可以有长度）;
begin
函数内容;
return 结果;
end;

--这里根据运动员编号,查询运动员信息，函数创建如下：
create or replace function findSporterFun(
       v_id sporter.sporterid%type
)return sporter%rowtype
is
        v_sp sporter%rowtype;
        cursor sporterinfo is select * from sporter where sporterid=v_id;
begin
        open sporterinfo;
        fetch sporterinfo into v_sp;
        close sporterinfo;
        return v_sp;
end;

创建好的函数，也需要调用：

declare
    v_id sporter.sporterid%type;
    v_s  sporter%rowtype;
begin
    v_id:=&运动员编号;
    v_s:=findSporterFun(v_id);
    dbms_output.put_line('运动员编号：'||v_s.sporterid||',名字：'||v_s.sportername||',性别：'||v_s.sex||',部门：'||v_s.department);
end;

8-2、存储过程和函数的区别

1.关键字不一样,过程使用procedure,函数使用function
2.在过程中，所有的参数都放在小括号里面，通过in来表示传入的参数，通过out来表示返回的数据;
在函数中，小括号中放的都是传入的参数，也没有in/out的写法，返回的数据都通过return 来完成
3.在过程中，对于需要返回的结果，只需要存放到out修饰的参数里面就可以了
在函数中，过程语句执行完之后，必须通过return 返回执行结果
4.调用过程时，如果有返回数据，需要把变量放到过程的小括号中来接收结果；
调用函数的时候，则通过给变量赋值的方式来接收函数执行的结果
5.过程一般供程序调用，而函数一般由其他过程或者函数来调用

9、结构体

oracle中的结构体类型：当提取的列来源于多张表时，用指定某个表的类型无法保存多张表中所需要列时，可以有自定义结构体类型，类似于C中的自定义类型，可用于定义某表的字段集合。

9-1、定义格式

type recordName is Record(
        字段名称 字段类型，
        字段名称 字段类型
	);

9-2、使用步骤

1）声明结构体 2）定义结构体变量 3）使用。

--查询每个课程学生的平均分,打印课程号，平均分
   DECLARE
	--1.声明结构体类型 
		TYPE avg_type IS RECORD(
           v_courseid course.courseid%TYPE,
           v_coursename course.coursename%TYPE,
           v_avgScore NUMBER(5,1)
        ); 
        
        v_test avg_type;  --2.定义结构体变量
        TYPE v_cursor_type  IS REF CURSOR; --定义动态游标
        v_cur v_cursor_type ; --定义游标变量
        
   BEGIN
      OPEN v_cur FOR
       SELECT ch.courseid,coursename,AVG(score) FROM chenji ch 
       INNER JOIN course c ON ch.courseid = c.courseid
       GROUP BY ch.courseid,coursename;
  --循环提取游标中的记录       
       LOOP
         FETCH v_cur INTO  v_test; --3.提取游标中的记录到结构体变量中
         EXIT WHEN v_cur%NOTFOUND;
           dbms_output.put_line(v_test.v_courseid||'  课程名:'||v_test.v_coursename||' 平均分：'||v_test.v_avgScore); 
         END LOOP;
         CLOSE v_cur;
     END;

10、事务与锁

10-1、事务

事务就是一组包含一条或多条语句的逻辑单元，每个事务都是一个原子单位。在事务中的语句被作为一个整体，要么一起提交，作用在数据库上，使数据库数据永久的修改；要么一起撤销，对数据库不做任何修改。

事务控制语句

set transaction :设置事务的属性。
commit :提交事务。
savepoint :设置事务保存点。
rollback :回滚事务。
rollback to savepoint :回滚至保存点。

设置事务是否自动提交,用来控制DML语句

set autocommit on/off;		--DDL语句是自动提交事务的。

事务的特性

1、原子性：事务对数据的修改要么完全执行，要么完全不执行。
2、一致性：事务在执行前后数据库都必须处于一致性。也就是只要事务提交成功后，其他用户才可以看到修改后的数据。
3、分离性：分离性是指并发事务之间不能互相的干扰。一个事务操作的数据不会被其他事务看到和操作。
4、持久性：事务一旦提交完成，数据就已经永修被修改。

10-2、锁

锁的介绍
锁可以防止事务之间的破坏行交互。约束了最大程度的并发性。数据的完整型。

锁的分类
1、排他锁(X锁)。防止资源共享，也就是当一个事务正在操作的数据时，其他事务不可以操作这个事务的数据。
2、共享锁(S锁)。被锁主的数据只能被读取，不能被修改。也不可以添加X锁。但可以添加S锁。
锁的类型
DML锁：也叫数据锁，用于保护数据。
DDL锁:可以保护模式中对象的结构。
内部闩锁：保护数据库的内部结构，完全自动调用。

行级锁(TX锁)，也叫事务锁。防止记录同时被两个事务修改。
表级锁(TM锁)，防止在修改表数据时，表的结构发生变化。

DDL锁
Exclusive DDL lock :排他DDL锁定，如果对象被添加了此锁，就被能被其他会话修改，也不能在增加其他DDL锁。如果是表，此时表数据只可以读取。
Shared DDL lock :共享DDL锁定，保护对象的结构，其他会话不能修改该对象的结构，但可以修改数据。
Breakable Parsed Lock : 此类锁可以被打断，不能禁止DDL操作。

锁等待和死锁
锁等待也叫锁冲突。锁等待会严重地影响数据库性能和日常工作。
死锁，死锁也就是，锁等待的一种，但死锁会让事务一直处于锁等待的状态。

解决死锁的情况
1、登录OEM管理器。
2、进如实例锁界面。
3、查找到阻塞锁，就终止掉次锁，也可以使用Kill 关键字杀死次会话。

--查看是否有死锁
select username, lockwait, status, machine, program
  from v$session
 where sid in (select session_id from v$locked_object);

--如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。

--用dba用户执行以下语句，可以查看到被死锁的语句
select sql_text
  from v$sql
 where hash_value in
       (select sql_hash_value
          from v$session
         where sid in (select session_id from v$locked_object))

--杀死死锁
alter system kill session 'sid';
--（其中sid=l.session_id）

--如果还不能解决： 
select pro.spid
  from v$session ses, v$process pro
 where ses.sid = XX
   and ses.paddr = pro.addr;

--其中sid用死锁的sid替换: 
exit
   ps -ef | grep spid ;

--其中spid是这个进程的进程号，kill掉这个Oracle进程。

等风来z

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle

Oracle（一）Oracle 是一个数据库管理系统，是Oracle公司的核心产品。其在数据安全性与安整性控制方面的优越性能，以及跨操作系统、跨硬件平台的数据操作能力。基于“客户端/服务　　　　　　器”(Client/Server)系统结构。主要特点： 1.支持多用户、大事务量的事务处理。 2.在保持数据安全性和完整性方面性能优越。 3.支持分布式数据处理。将公布在...
复制链接

扫一扫

专栏目录