DB Oracle.docx
- 文档编号:11935399
- 上传时间:2023-04-16
- 格式:DOCX
- 页数:21
- 大小:24.37KB
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,在索引中就没有相应的条目。
位图索引
主要用来节省空间,采用位图索引一般是重复值太多的表字段。
按功能分类
唯一约束索引
个是数据约束(保证数据的完整性),一个
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB Oracle