DB Oracle.docx
《DB Oracle.docx》由会员分享，可在线阅读，更多相关《DB Oracle.docx（21页珍藏版）》请在冰豆网上搜索。
DBOracle
OracleSQL
SQL
DDL
CREATEALTERDROPTRUNCATEGRANTCOMMENTREVOKE
DML
SELECTINSERTUPDATEDELETECALLEXPLAINPLANLOCKTABLE
DCL
COMMITSAVEPOINTROLLBACKSETTRANSACTION
Oracle的约束类型
1
PRIMARYKEY
2
FOREIGNKEY
3
UNIQUEKey
4
CHECK
5
NOTNULL
MAN
可以设定约束的范围，定义列级或表级约束。
创建表――同时创建约束
若要建立表之间的关联，就必须先创建父表，即1：
n中1的一方。
在删除数据时若不适用级联删除就必须先删除父表中的数据。
先创建父表
createtableclass（
Class_IDvarchar（10）notnull,
Class_Namevarchar（20）notnull,
Class_NumINTnotnull,
Class_Managervarchar（20）notnull,
primarykey（Class_ID）
）;
后创建子表
createtablestudent（
Stu_IDvarchar（10）notnull,
Stu_Namevarchar（20）notnull,
AgeINTnotnull,
HeightINTnotnull,
Class_IDvarchar（10）notnull,
constraintPK_STUDENTprimarykey（Stu_ID）,
foreignkey（Class_ID）referencesCLASS（Class_ID）
）;
先插入父表中的数据
insertintoclassvalues
（"20070101","高一
（1）",50,"Ludahu"）,
（"20060202","高二
（2）",60,"HenLi"）,
（"20050303","高三（3）",30,"WorkMan"）;
后插入子表中的数据
insertintostudentvalues
（"1001","AA1",23,"100","20070101"）,
（"1002","AA2",22,"120","20070101"）,
（"1003","AA3",24,"110","20070101"）,
（"1004","AA4",25,"140","20070101"）;
创建表――然后添加创建约束
ALTERTABLETNEWADDFOREIGNKEY（TNEW_ID）REFERENCESclass（Class_ID）;
PL/SQL语言（ProceduralLanguage/SQL）
PL/SQL基础
PL/SQL简介
1.PL/SQL是在标准SQL的基础上增加了过程化处理的语言.
2.是Oracle客户端访问Oracle服务器的操作语言.
3.优点:
模块化编程、良好的可移植性和可维护性、能够提升性能
PL/SQL基本结构
一般由声明部分、执行部分、异常处理部分组成，只有执行部分是必须的。
DECLARE
--声明变量、常量、用户自定义数据类型、光标
BEGIN
--主程序体，可以加入各种合法语句
EXCEPTION
--异常处理程序，程序出错时执行此部分
END;
PL/SQL分界符
名称
符号
名称
符号
算术运算符
+-*/**（指数操作符）
单行注释
--
比较运算符
=!
=~=^=<>>>=<<=
多行注释
/**/
表达式起始分界符
（
DB连接指示符
@
表达式终结分界符
）
属性指示符
%
标签起始分界符
<<
绑定变量指示符
:
标签终结分界符
>>
赋值操作符
:
=
语句终结符
;
连接操作符
=>
项目分割符
串连接操作符
||
其它
范围操作符
..
布尔表达式
AND,OR,NOT,BETWEEN..AND..,IN
PL/SQL常量和变量
定义常量：
<常量名>constant<数据类型>:
=<值>;
如：
pass_scoreconstantINTEGER:
=60;
定义变量：
<变量名><数据类型>[（宽度）:
=<初始值>];--宽度和初始值不是必须的.
如：
addressVARCHAR2（30）;
提示：
未初始化的变量值均为NULL.
提示：
PL/SQL变量和数据库变量是两个不同的概念.
提示：
内嵌过程可以使用外部的变量，但在外部不可以使用内嵌变量.
PL/SQL数据类型
分类名
类型
标量型
数字型、字符型、布尔型BOOLEAN、日期型DATE
组合型
RECORD、TABLE、VARRAY
参考型
REFCURSOR、REFobject_type
大对象型
BFILE、BLOB、CLOB、NCLOB
数字型（3种基本类型）
NUMBER（P,S）
可存储整数也存储浮点数，P（精度）和S（刻度）是可选的，若指定了S就必须也指定P
NUMBER的子类型
DEC,DECIMAL,DOUBLEPRECISION,INT,NUMBERIC,REAL,SMALLINT
PLS_INTEGER
只可存储整数
BINARY_INTEGER
只可存储整数
字符型
VARCHAR2（MaxLength）
存储变长字符串，MaxLength必须指定，其最大值为32767字节
LONG（MaxLength）
存储变长字符串，MaxLength必须指定，其最大值为32760
CHAR（MaxLength）
存储定长字符串，MaxLength可不指定，其最大值为32767，默认为1，若不足补空格
其它
NCHAR,NVARCHAR2是PL/SQL8.0后加入
提示
DB中，VARCHAR2,LONG,CHAR的长度分别为4000,2GB,2000
RECORD类型的使用语法：
TYPErecord_nameISRECORD（--定义RECORD类型
field1type1[NOTNULL][:
=expr1],
field1type2[NOTNULL][:
=expr2],
......
）;
v_namerecord_name;--定义RECORD类型的变量
使用：
v_name.field1
%TYPE可引用表中的某字段的类型：
v_FirstNamestudents.first_name%TYPE
%ROWTYPE可引用基于数据库表定义的类型：
v_StrRecStudent%ROWTYPE;
v_StrRec.student_id:
=1234;
v_StrRec.age:
=20;
TABLE类型的使用：
DECLARE
TYPEt_StuTableISTABLEOFStudent%ROWTYPWINDEXBYBINARY_INTEGER;
v_Studentt_StuTable;
BEGIN
SELECT*INTOv_Student（1001）
FROMStudentWHEREid=1001;
END;
PL/SQL控制结构
选择结构
IF条件表达式1
THEN语句序列1;
ENDIF;
IF条件表达式1
THEN语句序列1;
ELSE语句序列2;
ENDIF;
IF条件表达式1
THEN语句序列1;
ELSIF条件表达式2
THEN语句序列2;
ELSIF条件表达式3
THEN语句序列3;
ELSE语句序列4;
ENDIF
CASE检测表达式
WHEN表达式1THEN语句序列1;
WHEN表达式2THEN语句序列2;
WHEN表达式3THEN语句序列3;
WHEN表达式4THEN语句序列4;
ELSE其它语句序列;
END;
提示：
CASE中若任何一个表达式都不匹配，将产生CASE_NOT_FOUND错误号为ORA-6592
提示：
在条件表达式的判断中注意NULL的判断
循环机结构
LOOP
IF条件表达式1
THENEXIT;
ENDIF;
语句序列1;
ENDLOOP
LOOP
EXITWHEN条件表达式1
语句序列1;
ENDLOOP;
WHILE条件表达式1
LOOP
语句序列1;
ENDLOOP;
FORv_nameIN0..5
LOOP
语句序列1;
ENDLOOP;
提示：
GOTOlabel;定义label:
<>,一般不用
SQLInPL/SQL
SQL的类别及PL/SQL中的SQL
DML（DataManipulationLanguage）
select,insert,delete,settransaction,explainplan
DDL（DataDefinitionLanguage）
drop,create,alter,grant,revoke
TransactionControl
commit,rollback,savepoint
SessionControl
alertsession,setrole
SystemControl
alertsystem
ESQL
connect,declarecursor,allocate
提示：
只有DML可以直接在PL/SQL中使用，PL/SQL可以借助内置的DBMS_SQL包来执行动态SQL
PL/SQL块简介
块是PL/SQL的基本结构，块可以顺序出现也可以嵌套出现。
块类别简介
匿名块
动态生成，只可被执行一次，不能在其它块中进行调用
有名块
动态生成，只可被执行一次，但有名字，可以在其它块中进行调用
子程序
过程、包、函数，可显示的反复调用，一旦生成就不可修改，包只可以存储在数据库中，其他还可存储在本地
触发器
当触发事件出现时触发器便执行，是一种有名块，可反复调用，触发事件是由DML操作产生的
PL/SQL编程
游标简介
游标用SELECT语句从表或视图中选出数据，游标指向查询结果的首部。
游标是一个指向上下文的句柄或指针，通过游标，PL/SQL可以对上下文进行控制。
游标的种类：
显示游标（由用户定义,打开,关闭）、隐式游标（自动的）。
显示游标的基本操作
DECLARE
person_noNUMBER（5）;--定义3个变量用来存放Persons表中的内容
person_nameCHAR（10）；
person_sexCHAR
（1）;
resultVARCHAR2（30）;--记录相关的信息
CURSORpersonIS--1.定义一个游标
SELECTNo,Name,SexFROMPersonWHERENo<9999;
BEGIN
OPENperson;--2.打开一个游标即执行游标定义的SELECT语句
IFperson%ISOPEN–-3.检查游标是否已打开
THEN
WHILEperson%FOUND
LOOP--4.从游标中取值
FETCHpersonINTOperson_no,person_name,person_sex;
IFperson_sex=’M’
THENINSERTINTOManVALUES（person_no,person_name）;
ELSEINSERTINTOWomanVALUES（person_no,person_name）;
ENDIF;
ENDLOOP;
CLOSEperson;–-5.关闭游标
ELSE
result=’游标person不处于打开状态’;
ENDIF;
END;
提示：
游标只能向后移动而不能向前移动（不可滚动）。
提示：
关闭一个已关闭的游标是非法的，从关闭后的游标再取值也是非法的。
提示：
使用前必须用%ISOPEN判断游标是否打开。
提示：
使用中每次都必须用%FOUND或%NOTFOUND判断是否还可取到值。
提示：
游标还可以带参数。
提示：
游标中可以执行update和delete语句但必须再定义时指定FOR子句的编辑类型。
提示：
%ROWCOUNT此属性记录了游标抽取过的记录的行数。
隐式游标简介
在PL/SQL中用SELECT语句进行操作，则隐式的使用了游标。
隐式游标无需定义、打开、关闭。
提示：
每个隐式游标必须有一个INTO语句，每个隐式游标必须只选中一行数据。
提示：
尽量避免使用隐式游标。
动态游标变量
前面讲的游标都是静态游标，运行时只于一个SELECT语句相关，类似于PL/SQL常量。
DECLARE
TYPEt_ClassesRefISREFCURSORRETURNclasses%ROWTYPE;--定义
v_ClassesCVt_ClassesRef;--声明
BEGIN
OPENv_ClassesCVFOR–-打开
SELECT*FROMClasses;
v_ClassesCV.close（）;--关闭
END;
提示：
使用时也应该检查各种属性，以正常使用。
提示：
若没有RETURN语句则就是非受限游标变量，可以为任何查询打开。
过程
过程用来完成一系列的操作。
过程参数的3中模式
1．in输入型参数，在过程内部只可读，是缺省模式。
2．out在调用过程时，其对应的实参将被忽略，在过程内部是只可写的。
3．inout两者的综合
过程参数的2种指定方法
1．位置标示法实参与形参一一对应。
2．名字标示法给出形参和实参
提示：
两种可混用，但第一个参数必须通过位置来指定
CREATEORREPLACEPROCEDUREModeTest（
p_InParmINNUMBER:
=10,
p_OutParmOUTNUMBER,
p_InOutINOUTNUMBER）
AS
v_LocalVarNUMBER;
BEGIN
v_LocalVar:
=p_InParm;
p_OutParm:
=7;
p_InOut:
=100;
EXCEPTION
WHEN%NOTFOUNDTHENExit;
ENDModeTest;
ModeTest（12,p_OutParm=>v_var1,p_InOut=>10）;--调用过程
DROPPROCEDUREModeTest;--删除过程
函数
函数与过程很相似，有以下不同点：
1．过程调用用过程名，函数调用用表达式。
2．函数必须有一个返回值，过程没有。
提示：
函数中可以有多个返回值，但若函数结束时仍没碰到返回值将出错。
提示：
函数通常只有in类型的参数。
提示：
函数可以通过out参数返回多个值。
CREATEFUNCTIONcount_num（in_sexinPERSONS.Sex%TYPE）
RETURNNUMBER
IS
out_numNUMBER;
BEGIN
IFin_sex=’M’
THEN
SELECTcount（Sex）INTOout_numFROMPERSONSWHERESex=’M’;
ELSE
SELECTcount（Sex）INTOout_numFROMPERSONSWHERESex=’W’;
ENDIF;
RETURN（out_num）;
ENDcount_num;
DROPFUNCTIONcount_num;
包
包是可以将相关对象存储在一起的PL/SQL结构。
包的内容：
包中可以包含过程、函数、游标、变量。
组成：
包由包头和包体组成。
包头：
对包的所有部件进行一个简单的声明。
包体：
包含了包头中声明的所有过程和函数的代码，若包头中没有声明，包体可以不要。
初始化：
包在第一次被调用的时候，从数据库中调入内存并被初始化。
包内变量：
每个会话都将拥有自己的包内变量。
重载：
同一个包中的过程和函数可以重载，但参数必须是不同类型族的。
外部调用：
包名.元素名
CREATEPACKAGEmy_packageIS--包头部分
man_numNUMBER;--定义变量
woman_numNUMBER;
COURSORperson;--定义游标
CREATEFUNCTIONF_count_num（in_sexinPERSONS.Sex%TYPE）
RETURNNUMBER;--定义函数
CREATEPROCEDURE--定义过程
P_count_num（in_sexinPERSONS.SeX%TYPE,out_numoutNUMBER）;
ENDmy_package;
CREATEPACKAGEBODYmy_packageAS–－包体部分
--游标代码
CURSORpersonISSELECTNo,NameFROMPersonWHERENo<98505;
--函数代码
FUNCTIONF_count_num（in_sexinPERSONS.Sex%TYPE）RETURNNUMBER
IS
Out_numNUMBER;
BEGIN
IFin_sex=’M’
THEN
SELECTCOUNT（SEX）INTOout_numFROMPERSONSWHERESEX=’M’;
ELSE
SELECTCOUNT（SEX）INTOout_numFROMPERSONSWHERESEX=’W’;
ENDIF;
RETURN（out_num）;
ENDF_count_num;
--过程代码
PROCEDURE
P_count_num（in_sexinPERSONS.SeX%TYPE,out_numoutNUMBER）
AS
BEGIN
EndP_count_num;
ENDmy_package;
触发器
作用：
维护数据库的完整性
限制：
触发器中不能使用LONG和LONGRAW型变量
限制：
触发器中不能使用事务语句
限制：
不可以读取或修改任何变化表（表结构变化），也不可读取限制表的唯一性字段。
触发时间：
BEFORE和AFETER
触发事件：
INSERT,UPDATE,DELETE
触发类型：
行触发ROW,语句触发STATEMENT（默认）
触发相关值：
在行触发时，进行UPDATE事件同时拥有旧值和新值
提示：
一个表最多可有12个触发器
CREATETRIGGERmy_tigger
AFTERINSERTORUPDATEORDELETE
FORROW
DECLARE
infoCHAR（10）;
BEGIN
IFINSERTing
THENinfo:
=’INSERT’;
ELSIFUPDATING
THENinfo:
=’UPDATE’;
ELSE
THENinfo:
=’DELETE’;
ENDIF;
INSERTINTOSLQ_INFOVALUES（info）;
ENDmy_tigger;
DROPTRIGGERmy_trigger;
异常
系统变量：
SQLCODE返回错误号
系统变量：
SQLERRM返回异常错误信息
自定义异常：
与标准错误联系时就可产生错误号
异常错误号范围：
20000至20999
DECLARE
person_noNUMBER（5）;
person_weightNUMBER（5,1）;
TOO_FATEXCEPTION;
BEGIN
SELECTNO,WEIGHTINTOPERSON_NO,PERSON_WEIGHT
FROMCHECK_BODY
WHERENO=98999;
IFPERSON_WEIGHT>80
THENRAISETOO_FAT;--引发异常
ENDIF；
EXCEPTION
WHERETOO_FAT
THENINSERTINTOALERTVALUES（’THEPERSONISTOOFAT.’）;
END;
视图
视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。
视图基于的表称为基表。
通过创建视图可以提取数据的逻辑上的集合或组合。
视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；
语法
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview_name
[（alias[,alias]...）]
ASsubquery
[WITHCHECKOPTION[CONSTRAINTconstraint]]
[WITHREADONLY]
视图的分类
简单视图：
只从单表里获取数据，不包含函数和数据组，可以实现DML操作
复杂视图：
从多表里获取数据，包含函数和数据组，对DML有限制
视图的作用
隔离性：
对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
简化查询：
用户通过简单的查询可以从复杂查询中得到结果。
维护数据的独立性，试图可从多个表检索数据。
丰富逻辑模型：
对于相同的数据可产生不同的视图。
视图的定义规则
在没有WITHCHECKOPTION和READONLY的情况下，查询中不能使用ORDERBY子句。
如果没有为CHECKOPTION约束命名，系统会自动为之命名，形式为SYS_Cn。
在视图包含GROUP函数，GROUPBY子句，DISTINCT关键字时不能删除数据行。
索引
索引的作用
1．加快查询速度。
2．减少I/O操。
使用语法
索引的分类
按存储方法分类
B*树索引B树索引中不存在非唯一的条目。
有分支和叶两种类型的存储数据块，一般索引及唯一约束索引都使用B*树索引。
适用于：
访问表中占很小比例的行。
适用于：
根本不访问表，所需查询的数据全部在索引中。
注意：
如果列值为null,在索引中就没有相应的条目。
位图索引
主要用来节省空间，采用位图索引一般是重复值太多的表字段。
按功能分类
唯一约束索引
个是数据约束（保证数据的完整性），一个
升级会员 