ORACLE复习资料.docx
《ORACLE复习资料.docx》由会员分享，可在线阅读，更多相关《ORACLE复习资料.docx（19页珍藏版）》请在冰豆网上搜索。
ORACLE复习资料
ORACLE系统特点
1）支持大数据库、多用户的高性能的事务处理。
ORACLE支持最大数据库，其大小可到TB，可充分利用硬件设备。
支持大量用户同时在同一数据上执行各种数据应用，并使数据争用最小，保证数据一致性。
2）ORACLE遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准。
所以它是一个开放系统，保护了用户的投资。
3）实施安全性控制和完整性控制。
ORACLE为限制各监控数据存取提供系统可靠的安全性
4）支持分布式数据库和分布处理。
5）具有可移植性、可兼容性和可连接性。
ORACLE上所开发的应用可移植到任何操作系统，只需很少修改或不需修改。
ORACLE软件同很多工业标准相兼容，
3.Oracle数据库体系结构
Oracle数据库有两个部分组成:
数据库DB,实例Instance.
重要的高级SQL，PL/SQL（案例分析）Decode函数
decode（baseExp,exp1,value1,exp2,value2,null）;
Purpose
DECODEcomparesexprtoeachsearchvalueonebyone.Ifexprisequaltoasearch,thenOraclereturnsthecorrespondingresult.Ifnomatchisfound,thenOraclereturnsdefault.Ifdefaultisomitted,thenOraclereturnsnull.
重要的分析函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
约定：
N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式
函数名称（[参数]）OVER（analytic_clause）
analytic_clause包含：
[partition子句][order子句[window子句]]
Partition子句：
Partitionbyexp1[,exp2]...
Partition功能强大参数少，主要用于分组，可以理解成select中的groupby。
不过它跟select语句后跟的groupby子句并不冲突。
Order子句：
Orderbyexp1[asc|desc][,exp2[asc|desc]]...[nullsfirst|last]。
部分函数支持window子句。
Orderby的参数基本与select中的orderby相同。
Nullsfirst|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认orderby的时候nulls总会被排在最前面。
如果想控制值为null的列的话呢，nullsfirst|last参数就能起到作用。
Preparation：
SQL的格式化控制语句
用BREAK命令来对结果进行排列
clear breaks 清除所有的break定义break on column 在该列上中断
break on row 在每一行上中断break on Page 在每一页上中断
break on report 在每一报告上中断skip n 跳过n行
skip page 跳过未用完的页
•SQL>breakondeptnoskip2
•SQL>selectdeptno,ename,salfromemporderbydeptno;
•或者
•SQL>breakondeptnoskippage
•SQL>selectdeptno,ename,salfromemporderbydeptno;
•breakongroup_idskip1
•selectgroup_id,name,num,
•rank（）over（partitionbygroup_idorderbynumascnullslast）rk
•fromzhu.a
•breakondeptnoskip1
•select*from
•（selectdeptno,row_number（）over（partitionbydeptnoorderbysaldesc）rw,ename,salfromscott.emp）whererw<=3
SQL>selecta,b,cfrom（select*from（selecta,b,c,d,rank（）over（partitionbya,borderbyddescn
ullsfirst）rkfromtext）whererk<=1）;
ABC
------------------------------
a1b1c3
a1b2c4
a2b4c4
a2b5c5
a3b6c6
案例分析分析函数Dense_Rank（）Over（Partitonby…OrderBy…NullsFirst/Last）
•breakongroup_idskip1
•selectgroup_id,name,num,
•dense_rank（）over（partitionbygroup_idorderbynumascnullslast）rk
•fromzhu.a
流动（累计）数学统计函数
•Aggregate_function：
MIN,MAX,SUM,AVG,COUNT,VARIANCE,STDDEV.
•Aggregate_function（…）Over（Partitionby…OrderBy…NullsFirst/Last）
案例分析：
流动平均值avg（）over（partitionbycolorderbycolascNullsFirst）
•breakongroup_idskip1
•selectid,group_id,name,num,avg（num）over（partitionbygroup_idorderbyidascnullsfirst）agfromzhu.a
累计求和sum（）over（partitionbycolorderbycolascNullsFirst）
•breakongroup_idskip1
•selectid,group_id,name,num,sum（num）over（partitionbygroup_idorderbyidascnullsfirst）sumfromzhu.a
重要的分析函数
分析函数计算基于groupby的列，分组查询出的行被称为"比照（window）"，在根据over（）执行过程中，针对每一行都会重新定义比照。
比照为"当前行（currentrow）"确定执行计算的行的范围。
这点一定要理解清楚。
它是分析函数生成数据的原理。
分析函数与聚合函数非常相似，不同于聚合函数的地方在于它们每个分组序列均返回多行。
在本节示例中会同时应用两种函数做对比，以更好体现二者的差异。
部分聚合函数和分析函数是同一个命令，事实确实如此。
如果从语法格式上区分的话，没加over（）的即是聚合函数，加了over（）即是分析函数
除了orderby子句的运算外，分析函数在SQL语句中将会最后执行。
因此，分析函数只能应用于select的或orderby子句中（不可置于where、groupby、having之类的地方了）。
也正因此，同名的函数在做为聚合函数和分析函数时得出的结果可能不相同，就是因为此处运算逻辑不同造成的。
部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定orderby子句了。
PL/SQL程序设计
PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL/SQL整合到ORACLE服务器和其他工具中了，近几年中更多的开发人员和DBA开始使用PL/SQL。
PL/SQL不是一个独立的产品，他是一个整合到ORACLE服务器和ORACLE工具中的技术，可以把PL/SQL看作ORACLE服务器内的一个引擎，sql语句执行者处理单个的sql语句，PL/SQL引擎处理PL/SQL程序块。
当PL/SQL程序块在PL/SQL引擎处理时，ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句。
PL/SQL的优点如下：
1.PL/SQL是一种高性能的基于事务处理的语言，能运行在任何ORACLE环境中，支持所有数据处理命令。
通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。
2.PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型
3.PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。
4.对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。
而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤
PL/SQL块结构
PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都可以划分为三个部分。
与其他语言相同，变量在使用之前必须声明。
•声明部分（Declarationsection）
声明部分包含了变量和常量的数据类型和初始值。
这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分
•执行部分（Executablesection）
执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。
•PL/SQL块语法
[DECLARE]
---declarationstatements
BEGIN
---executablestatements
[EXCEPTION]
---exceptionstatements
END
•说明：
PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以使多行的，但分号表示该语句的结束。
一行中可以有多条SQL语句，他们之间以分号分隔。
每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。
注释由--标示
•PL/SQL块的命名和匿名
PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块。
匿名程序块可以用在服务器端也可以用在客户端。
命名程序块可以出现在其他PL/SQL程序块的声明部分，这方面比较明显的是子程序，子程序可以在执行部分引用，也可以在异常处理部分引用。
•PL/SQL程序块可背独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。
ORACLE提供了四种类型的可存储的程序：
函数，过程，包和触发器
•函数：
函数是命名了的、存储在数据库中的PL/SQL程序块。
•FUNCTIONname[{parameter[,parameter,...]）]RETURNdatatypesIS[localdeclarations]BEGINexecutestatements[EXCEPTIONexceptionhandlers]END[name]
•过程：
存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入（INPUT）或输出（OUTPUT）、或既作输入又作输出（INOUT），与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用
PROCEDUREname[（parameter[,parameter,...]）]IS[localdeclarations]
BEGIN
executestatements
END[name]
•包（package）
包其实就是被组合在一起的相关对象的集合，当包中任何函数或存储过程被调用，包就被加载入内存中，包中的任何函数或存储过程的子程序访问速度将大大加快。
包由两个部分组成：
规范和包主体（body）,规范描述变量、常量、游标、和子程序，包体完全定义子程序和游标。
•触发器（trigger）
触发器与一个表或数据库事件联系在一起的，当一个触发器事件发生时，定义在表上的触发器被触发。
PL/SQL语法
•变量和常量:
变量一般都在PL/SQL块的声明部分声明.
声明变量的语法如下：
Variable_name[CONSTANT]databyte[NOTNULL][:
=|DEFAULTexpression]
•给变量赋值
1）直接给变量赋值X:
=200;Y=Y+（X*20）;
2）通过SQLSELECTINTO或FETCHINTO给变量赋值SELECTSUM（SALARY）,SUM（SALARY*0.1）INTOTOTAL_SALARY,TATAL_COMMISSIONFROMEMPLOYEEWHEREDEPT=10
•常量
ZERO_VALUECONSTANTNUMBER:
=0;
标量（scalar）数据类型：
number.character.date/time.boolean
•LOB数据类型
LOB（大对象,Largeobject）数据类型用于存储类似图像，声音这样的大型数据对象，LOB数据对象可以是二进制数据也可以是字符数据，其最大长度不超过4G。
LOB数据类型支持任意访问方式。
LOB存储在一个单独的位置上，同时一个"LOB定位符"（LOBlocator）存储在原始的表中，该定位符是一个指向实际数据的指针。
在PL/SQL中操作LOB数据对象使用ORACLE提供的包DBMS_LOB.LOB
•对执行部分的说明：
执行部分包含了所有的语句和表达式,执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字END结束。
分号分隔每一条语句，使用赋值操作符:
=或SELECTINTO或FETCHINTO给每个变量赋值，执行部分的错误将在异常处理部分解决，在执行部分中可以使用另一个PL/SQL程序块，这种程序块被称为嵌套块
•对执行部分的说明
所有的SQL数据操作语句都可以用于执行部分，PL/SQL块不能再屏幕上显示SELECT语句的输出。
SELECT语句必须包括一个INTO子串或者是游标的一部分，执行部分使用的变量和常量必须首先在声明部分声明，执行部分必须至少包括一条可执行语句，NULL是一条合法的可执行语句，事物控制语句COMMIT和ROLLBACK可以在执行部分使用，数据定义语言（DataDefinitionlanguage）不能在执行部分中使用，DDL语句与EXECUTEIMMEDIATE一起使用或者是DBMS_SQL调用。
•执行一个PL/SQL块
SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行
declarev_comm_percentconstantnumber:
=10;
begin
updateempsetcomm=sal*v_comm_percentwheredeptno=10;
end
SQL>/PL/SQLproceduresuccessfullycompleted.
•命名的程序与匿名程序的执行不同，执行命名的程序块必须使用execute关键字
createorreplaceprocedureupdate_commission
（v_deptinnumber,v_perventinnumberdefault10）is
begin
updateempsetcomm=sal*v_percentwheredeptno=v_dept;
end
SQL>/
Procedurecreated
SQL>executeupdate_commission（10,15）;
PL/SQLproceduresuccessfullycompleted.
•如果在另一个命名程序块或匿名程序块中执行这个程序，那么就不需要EXECUTE关键字
declarev_deptnumber;beginselecta.deptno
intov_deptfromempawhere
job='PRESIDENT'update_commission（v_dept）;end
SQL>/PL/SQLproceduresuccessfullycompleted
SQL>
•控制结构：
语法：
IFconditionTHENStatements1;Statements2;ENDIF
•IF..THEN...ELSE语法:
IF..THEN..ELSIF语法
IFconditionTHENIFcondition1THEN
Statements1;statement1;
Statements2;....ELSIFcondition2THEN
ELSEstatement2;
Statements1;ELSIFcondition3THEN
Statements2;....statement3;
ENDIFELSEstatement4;
ENDIF;
statement5
•循环控制
循环控制的基本形式是LOOP语句，LOOP和ENDLOOP之间的语句将无限次的执行。
LOOP
statements;
ENDLOOP
LOOP和ENDLOOP之间的语句无限次的执行显然是不行的，那么在使用LOOP语句时必须使用EXIT语句，强制循环结束
X:
=100;
LOOPX:
=X+10;IFX>1000THENEXIT;
ENDIF
ENDLOOP;
Y:
=X;
•EXITWHEN语句将结束循环，如果条件为TRUE，则结束循环。
X:
=100;
LOOPX:
=X+10;EXITWHENX>1000;X:
=X+10;
ENDLOOP;
Y:
=X;
•FOR...LOOP
FORcounterIN[REVERSE]
start_range..end_rangeLOOP
statements;
ENDLOOP;
说明：
FOR循环的循环次数是固定的，counter是一个隐式声明的变量，他的初始值是start_range,第二个值是start_range+1,直到end_range,如果start_range等于end_range,那么循环将执行一次。
如果使用了REVERSE关键字，那么范围将是一个降序。
•X:
=100;FORv_counterin1..10loopx:
=x+10;
endloopy:
=x;
•标签
用户可以使用标签使程序获得更好的可读性。
程序块或循环都可以被标记。
标签的形式<>
<>
LOOP.........
<>
loop..........
<>
loop....
EXITouter_loopWHENv_condition=0;
endloopinnermost_loop;..........
ENDLOOPinner_loop;
ENDLOOPouter_loop;
PL/SQL复合数据类型
PL/SQL有两种复合数据结构：
记录和集合。
记录由不同的域组成，集合由不同的元素组成。
在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。
记录：
是PL/SQL的一种复合数据结构，复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。
•创建记录
两种定义方式：
显式定义和隐式定义。
一旦记录被定义后，声明或创建定义类型的记录变量，然后才是使用该变量。
隐式声明是在基于表的结构或查询上使用%TYPE属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。
•显式定义记录
显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。
使用type命令定义记录，然后在创建该记录的变量。
TYPErecord_typeISRECORD（field_definition_list）;
field_definition_list是由逗号分隔的列表。
•隐式定义记录
隐式定义记录中，我们不用描述记录的每一个域。
这是因为我们不需要定义记录的结构，不需要使用TYPE语句，相反在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录，与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。
•DECLAREaccounter_infoaccounts%ROWTYPR;CURSORxactions_cur（acct_noINVARCHAR2）ISSELECTaction,timestamp,holdingFROMportfoliosWHEREaccount_nbr='acct_no';xaction_infoxactions_cur%ROWTYPE;variable
•有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的:
old和:
new记录。
DELCARECURSORxaction_curISSELECTaction,timeamp,holdingFROMportfoliosWHEREaccount_nbr='37';
BEGIN
FORxaction_recinxactions_cur
LOOP
IFxactions_rec.holding='ORCL'THENnotify_shareholder;
ENDIF;
ENDLOOP;
•使用记录
用户可以给记录赋值、将值传递给其他程序。
记录作为一种复合数据结构意味作他有两个层次可用。
用户可
升级会员 