Oracle学习札记Word格式文档下载.docx
《Oracle学习札记Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《Oracle学习札记Word格式文档下载.docx（118页珍藏版）》请在冰豆网上搜索。
存储32精度的浮点数。
需要添加后缀F如35.3f，存储5个字节
与BINARY_DOUBLE一样是是NUMBER类型的补充，其优点如下：
1、需要的存储的空间更小。
（NUMBER最多要占用22个字节的空间）
2、可以表示的数字范围更大
3、执行运算速度更快（其计算在硬件上执行，而number需要软件转换）
4、运算封闭（也就是说运算总会返回一个值，而不是报错）
5、取整透明（由于其是以二进制保存数据，所以是以二进制进行取整，而number类型以十进制行保存数据，并以十进制进行取整）
6、BINARY_FLOAT、BINARY_DOUBLE类型存在一些特殊值：
详情见符表1
数字类型，
如果涉及到大量使用数据计算的开发可以用本类型
BINARY_DOUBLE
存储64精度的浮点数。
需要添加后缀d如35.3d，存储9个字节
是number类型的一种补充，详细情况可查看BINARY_FLOAT类型。
RAW
可以用于存储基于字节的数据，如二进制数据或字符串，精度1-2000个字节。
使用本类型时要指定大小，可以在此类型列上使用索引。
主要用于存储二进制数据
LONGRAW
可以用于存储基于字节的数据，最多可存储2G的数据。
此类型不能索引，所有LONG类型的限制对LONGRAW有效。
CLOB
可以存储大量字符类型的数据。
LOB大对象,一个表可以有多个大对象数据类型
LOB大对象
BLOD
可以存储大量二进制类型的数据。
BFILE
能将二进制文件存储在服务器文件系统上的二进制文中。
BFILE列上存一个文件定位器。
它指向服务器上的二进制文件。
支持文件最大为4G
Nvarchar与varchar之间的转换用tonchar（varchar）
符表1
BINARY_FLOAT,BINARY_DOUBLE类型的特殊值
特殊值
描述
BINARY_FLOAT_NAN
非BINARY_FLOAT类型的数字
BINARY_FLOAT_INFINITY
BINARY_FLOAT类型的无穷大
BINARY_DOUBLE_NAN
非BINARY_DOUBLE类型的数字
BINARY_DOUBLE_INFINITY
BINARY_DOUBLE类型的无穷大
NULL类型数据
用NULL类型进行计算或比较，均返回NULL,就算是两个空值进行比较也只会返回空。
可以认为是NULL不参与任何运算。
包括Like,in,+等等。
但可以用ISNULL或ISNOTNULL或NVL（）进行处理或判定。
Oracle内置有三个函数对NULL进行处理decode（）,NVL（）,replace（）.
运算符
符号标识
:
=
赋值符
V_NUM:
=10;
也可以用selectintoV_NUMFROMDUAL;
+、-、*、/、**（求幂）、+-（正负符号）、
算术运算符
算术运算符能操作数字与日期类型
||
连接运算符使用连接符用于合并两个列。
进行连接。
以支持更有意义的输出。
在执行||并操作时
如果两个值是char类型则返回的是char类型，如果是CLOB类型则返回的是CLOB类型，除此之外均返回varchar2类型。
例：
自动生成SQL语句
select‘truncatetable’||TABLE_NAME||’;
’fromUSER_TABLESt;
--批量截断指定表
=、<
>
或!
、>
、<
=、>
=、~=、^=
常规比较运算符
ANY
在where条件中可以使用ANY将一个值与列表中的任何一个值进行比较。
此时必须在ANY之前使用=、<
=。
例:
Select*fromTB_USERSwhereUSER_AGE<
ANY（23,45,48）–用户年龄不等于23,45,48中的任何一个，则表达式成立。
作用与notin相同,但速度更快。
ALL
在where条件中可以使用ALL将一个值与列表中的所有值进行比较。
此时必须在ALL之前使用=、<
例
Select*fromTB_USERwhereUSER_AGE<
ALL（23,45,48）–用户年龄大于23,45,48中的所有的值时，则表达式成立。
Like/notlike
使用通配符来进行模糊查询。
1、%0或多个字符（为了加快查询速度，请尽量不要写成like‘%匹配字符%’）
2、_1个字符
如果想匹配‘%’或‘_’时，可以通过Escape选项进行说明。
Select*fromTB_CITYSwhereCITY_NAMElike‘C\_BEBING%’Escape‘\’--此时以‘\’作为转义符就会将对‘_’进行文本匹配了，_不再具有通配符的作用。
本查询所C_BEBING开始的城市名称
IN/NotIN
使用in操作符可以检索一个值在指定的列表值是否存在。
而notin则正好相反。
如果列表中存在空值，IN也会过行匹配（空匹配空）。
如果列表中存在空值，notIN将返回false。
为了提高性能可以用Exists/notexists进行替换
1、Select*fromTB_USERSwhereUSER_AGEin（23,34,56,null）;
--此语句将匹配年龄为null或23或34或56的所有记录
2、Select*fromTB_USERSwhereUSER_AGEnotin（23,34,56,null）;
--此语句将匹配不到一条数据
Between/notbetween
使用Between/notbetween将用于检索（或排除）在指定区间（包括边界值）的数据行。
IsNull/isnotnull
空值：
并不是一个空字符串，更不是一个空格。
而是一个特殊值。
可以通过NVL，NVL2函数，isnull,isnotnull操作符进行判定。
IsNAN/isnotNAN
IsNAN和isnotNAN是oracle10g新特性，匹配NAN这个特殊值，意思是“非数字”
IsINFINITE/isnotinfinite
IsINFINITE和isnotinfinite是oracle10g新特性，匹配无穷的BINARY_FLOAT和BINARY_DOUBLE值
Exists/notexists
判定值是否存在于指定的列表值中。
其功能与用法与in和notin一样
逻辑运算符
And
所有条件均成立，则返回true
优先级not>
and>
or，为了代码的可读性，一般是通过显示的（）来指明优先级
Or
只要一个条件均成立则返回true
Not
取反
@
在命令窗口中可以导入一个sql文件
@D:
//aaa.sql;
表达式
示例
数据类型表达式
数字布尔表达式的比较是以数据的定量比较（值大小比较）
字符布尔表达式
在默认情况下，是以字符串中的每个字节的二进制字进行比较
日期布尔型
在日期布尔表达式用年月日时分秒的方式进行比较
集合操作符
Union联合
用于返回两个查询所有不重复的行，并进行排序，联合的两张表的字段类型、数目完全一样
Selectuser_age,user_sexfromtb_users
Union
Selectop_age,op_sexfromtb_oper;
Unionall（联合所有）
用于返回两个查询所有行，不再去重复行。
也不会进行排序。
按两表原样输出。
联合的两张表的字段类型、数目完全一样
Intersect（交集）
用于返回两个查询的相同行，去重复行。
会进行排序。
联合的两张表的字段类型、数目完全一样
Minus（差集）
用于返回第一张表有而第二表没有的行，去重复行。
联合的两张表的字段类型、数目完全一样
PL/SQL控制语句
条件控制
IF结构的条件控制
If--then…endif;
If--then…ELSE…endif;
If--then…ELSIF--THEN….ELSE…ENDIF;
CASE结构的条件控制
方式1
CASEWHENV_NUM=1THEN…
WHENV_NUM=2THEN…
ELSE…
END;
方式2
CASEV_NUM
WHEN1THEN…
WHEN2THEN…
END;
循环控制
在所有的循环控制语句中可以用exit进行退出当前的循环，可以用GOTO+标签实现类似高级语言的CONTINUE;
方式1：
NUMASINTDEFAULT0;
DO
V_NUM:
=V_NUM+1;
//演示break功能
IFV_NUM=10THEN
EXIT;
ENDIF;
//演示CONTINE功能
IFV_NUM=5THEN
GOTOLABAL1;
//直接跳转到标签LABAL1中去，实现了CONTINUE的功能
<
LABAL1>
NULL;
//空语句，不处理任何事，如果在支持CONTINE功能应该要写在LOOP之前，其他业务代码之后
LOOP;
方式2：
WHILE（CONDITION）LOOP
……//BREAK与CONTINUE功能的实现与方式1一样
ENDLOOP;
方式1：
FORV_NUMIN[RESERVER]VALUE1..VALUE2
LOOP
ENDLOOP;
顺序控制
GOTO语句
语句将程序控制权无条件的转移到标签后指定代码。
也就是说这样将不会再回过头来执行GOTO后面的代码。
与标签一起使用，标签以<
LABALNAME>
进行定义；
不可以嵌套定义，系统会认为下一个标签重新开始。
标签没有结束符号。
直到遇到return;
或代码结束。
DECLARE
V_NUMNUMBER:
BEGIN
IFV_NUM=10THEN
GOTOLABAL1;
=V_NUM+1;
//这句代码是永远不会执行
ELSE
RETURN;
--这个是必要的，其他情况就退出；
否则程序会一直向下执行所以的代码。
<
LABAL1>
DBMS_OUTPUT.PUT_LINE（‘执行了LABLAL1标签’）;
//执行这句后，控制权不会再回去，因此程序会远行结束。
NULL语句
什么也不做，只是将控制权转到下一条语句，可以用于需要解决一些语法格式，但又不需要执行任何代码的语句。
例如上面提到的循环中的CONTINUE功能。
RETURN语句
可以用于函数中的返回值，也可以用于在过程或存储过程中的强制退出的功能。
PL/SQL的语法结构
…//变量定义与初值赋予
…//业务代码
异常处理
简介
ORACLE中的异常可分为系统异常和自定义异常，并拥有像JAVA、C#的异常捕捉的功能,
在捕捉时通过OTHERS来处理程序确保不会遗漏任何异常，同时可以使用SQLCODE和SQLERRM来返回错误代码和错误信息。
用户自定义异常一定要以显示的方式进行raise抛出。
可以通过RAISE_APPLICATION_ERROR（ERROR_NUMER,ERROR_MESSAGE）;
程序会自动回滚所有没有提交的事务。
其中消息最长可达2048个字节，编号必须在-2000到-20999之间的负数。
在ORACLE中可以通过SQLCODE,SQLERRM分别得到异常代码各异常原因描述.
用户自定义异常定义格式如下
MY_EXCEPTIONEXCEPTION;
//定义自动异常
BGEIN
RAISEMY_EXCEPTION;
//使用自定义异常。
这样的异常没有异常错误信息，但可以组织某一类的异常
AISE_APPLICATION_ERROR（ERROR_NUMER,ERROR_MESSAGE）;
//如果直接抛出应用程序异常，这样程序将会直接退出，并自动回滚所有的没有提交的事务
在ORACLE中定义一个可以由系统自动触化的异常。
使用命令用自定义的异常支占用一个系统预定义的一个异常号
例如
MYEXCEPTIONEXCEPTION;
PAGAMEXCEPTION_INIT（MYEXCEPTION,-1400）;
--向不能为空的列中插入NULL
INSERTINTOA（USER_NAME）VALUES（NULL）;
COMMIT;
EXCEPTION
WHENMYEXCEPTIONTHEN
DBMS_OUTPUT.PUT_LINE（‘向不能为空的列中插入NULL’）;
END
在ORACLE中的异常处也有如获至宝C#/JAVA中的异常处理块TRY{}CATCH（）{}其结构是:
---执行代码
RAISEEXCEPTION_NAME;
--
EXCEPTON
WHENEXCEPTION_NAMETHEN
--处理
--用SQLCODE/SQLERRM显示错误信息
WHENEXCEPTION_NAME2THEN
--处理
WHENOTHERSTHEN
异常可以是用户自定义的也可是系统预定义的
自定义异常
MYEXCEPTIONEXCEPTION;
使用如：
RAISEMYEXCEPTION
RAISE_APPLICATION_ERROR（ERROR_CODE,
ERROR_MSG）
TRY{
---执行代码
THROWNEWEXCEPTIONOBJ（）;
}
CATCH（SUBEXCEPTIONE）{
}CATCH（SUBEXCEPTIONE）{
}CATCH（EXCEPTIONE）{
CLASSMYEXCEPTION:
EXCEPTION;
THROWNEWMYEXCEPTION（）
THROWNEWAPPLICATION（）
常用的异常系统
（PL/SQL的预定义异常在STANDARD程序包中声明）
异常
ACCESS_INTO_NULL
在操作未初始化对象时出现
CASE_NOT_FOUND
在CASE语句中的选项与用户录入的数据不匹配时出现
COLLECTION_IS_NULL
在给尚未初始化的表或数组赋值时出现
CURSOR_ALREADY_OPEN
在用户试图打开已经打开的游标时出现
DUP_VAL_ON_INDEX
唯一约束异常
INVALID_CURSOR
执行非法游标进出现
INVALID_NUMBER
在将字符串转换成数据时，无法转换时现
LOGIN_DENIED
在录入用记名与密码不正确时出现
NO_DATE_FOUND
当表中不存在请求的数据时出现
STORAGE_ERROR
内存不够出现
TOO_MANY_ROWS
在执行SELECTINTO语句返回多行时出现
VALUE_ERROR
在产生大小限制错误时出现。
例如：
超过数据类型的精度范围
ZERO_DIVIDE
0作除数时出现
游标
简介:
游标是构建在PL/SQL中，用来查询数据，得到记录集合的指针。
游标可以以编程的方式访问数据并进行处理。
游标可分为隐式游标和显示游标。
隐式游标的是SQL语句产生的；
显示游标是用户自定义的。
显示游标又分：
静态游标与动态游标。
静态游标：
是在编译时就知道其SELECT语句的游标，静态游标又分为隐式游标和显式游标。
动态游标:
是在运行时才知道其SELECT语句的游标。
动态游标分强类型游标与弱类型游标。
为了使用动态游标，必须要声明游标变量。
静态游标:
PL/SQL为所有的SQL数据操纵语句隐式的声明游标，用户不能直接命名此类游标。
用户可以通过检查隐式游标中的判断最近执行的SQL语句相关的信息。
隐式游标的属性包括:
1、SQL%FOUND;
只有在执行DML语句影响一行或多行时才会返回TRUE
2、SQL%NOTFOUND;
与SQL%FOUND正好相反
3、SQL%ROWCOUNT;
返回执行DML语句影响行数
4、SQL%ISOPEN;
返回游标是否已经打开，由于ORACLE在执行SQL后为自动关闭隐式游标，所以此属性值始终为FALSE
显示游标（以下均用游标表示显示游标）:
使用游标的四步：
声明，打开，提取，关闭游标．只能向前进行提取数据。
一行一行的进行提取数据。
声明形式：
1CURSORMY_CURSORISSELECT*FROMTABLE_NAME;
//常规方式
2CURSORMY_CURSOR（PARAM_LIST）ISSELECT*FROMTABLE_NAMETWHERET.COLOUMN=PARAM_LIST;
//参数游标方式
V_AGETB_QC120%ROWTYPE;
CURSORMY_CURSOR（USER_NAMEVARCHAR2）ISSELECT*FROMTB_QC120TWHERET.USER_NAMELILE'
%'
||USER_NAME||'
;
OPENMY_CURSOR（'
不放弃'
）;
FETCHMY_CURSORINTOV_AGE;
LOOP
EXITWHENMY_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE（V_AGE.USER_NAME）;
CLOSEMY_CURSOR;
END
关于ORACLE的游标的一次只能提取一条记录.用FETCH读取当前记录后自动指向下一行。
如果要用ORACLE的游标一次提取所有的记录，只能将数据暂时写入嵌套表中（也叫交BLUK大块COLLECT集合的数据）。
DECLARE
TYPENAMESISTABLEOFVARCHAR2（30）;
TYPECLASSESISTABLEOFSTUDENTS.CLASENO%TYPE;
CURSORMYCURSORISSELECTNAMES,CLASSES_NOFROMSTUDENTS;
V_NAMESNAMES;
V_CLASSES_NOCLASSES;
--OPEN
OPENMYCURSOR;
--FETCH
FETCHMYCURSORBULKCOLLECTINTOV_NAMES,V_CLASSES_NO;
--CLOSE
CLOSEMYCURSOR;
--USERTABLE_ZONE
FORIIN1..V_NAMES.COUNT
DBMS_OUTPUT.PUT_LINE（V_NAMES（I）||'
++'
||V_CLASSES_NO（I））;
在包头中声明游标时，一要定义返回值，除非使用动态游标声明。
1、CURSORMY_CURSORRETURNSTUDENTS%ROWTYPEISSELECT*FROMSTUDENTST;
2、TYPEMY_CURSORISREFCURSOR;
为了保证数据的真实性，在使用游标进行数据操作时，应该使用FORUPDATE对表的记录进行锁定。
CURSORMY_CURSORISSELECT*FROMSTUDENTSTWHERET.NAMELIKE‘%A%’FORUPDATEOF（NAME,USER_AGE）；
表示只进行锁定两列。
CURSORMY_CURSORISSELECT*FROMSTUDENTSTWHERET.NAMELIKE‘%A%’FORUPDATE；
表示只进行锁定STUDENTS表的查询行。
动态游标：
强类型引用游标：
指定的返回类型定义如下:
TYPEMY_CURSORISREFCURSORRETURNSTUDENTS%ROWTYPE;
弱类型引用游标：
没指定的返回类型定义如下:
TYPEMY_CURSORIS
升级会员 