Oracle学习札记.docx
- 文档编号:8411982
- 上传时间:2023-01-31
- 格式:DOCX
- 页数:118
- 大小:277.46KB
Oracle学习札记.docx
《Oracle学习札记.docx》由会员分享,可在线阅读,更多相关《Oracle学习札记.docx(118页珍藏版)》请在冰豆网上搜索。
Oracle学习札记
基本语法
简单数据类型
1数值类型2字符类型3日期类型4BOOLEAN类型
类型名称
说明
备注
BOOLEAN
是与否,TRUE或者FALSE
PL/SQL类型
Char(Length)
固定长度的字符串。
精度1-2000字符
如果用户录入的值小于指定的长度,数据会用空格进行填充。
大于指定长度时会报错
字符类型
Varchar2(Length)
支持可变的字符串存储。
精度1-4000个字符。
在PL/SQL语法中精度1-32767个字符。
精度以2000为界分别在执行效率与占用内存上作了优化.
字符类型
LONG
支持可变的字符串存储。
精度2GB。
用于存储varchar2所不能存储的长文本类型值。
1、一个表中只能有一个LONG字段;
2、LONG列上不能定义唯一或主键约束,不能建立索引;
3、过程或存储过程不能接受LONG类型参数。
字符类型
Date
用于存储时间或日期。
使用7个字节的固定长度可保存日期范围公元前4172-1-1到9999-12-31。
时间类型
TIMESTAMP
与Date类型一样。
但该类型还包含时区信息。
SYSTEMSTAMP函数返回当前日期、时间和时区
Integer
只存储整数。
数字类型
Number(precision,scale)
存储浮点型数。
精度38位
Precision整数位位数+小数位位数(不包括小数点),scale小数位位数
Number123.2312123.2312
Number(6,2)123.2312123.23
Number(6,2)11123.2312报错
数字类型
BINARY_FLOAT
存储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大对象
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…
ELSE…
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的功能
ENDIF;
<
NULL;//空语句,不处理任何事,如果在支持CONTINE功能应该要写在LOOP之前,其他业务代码之后
LOOP;
方式2:
WHILE(CONDITION)LOOP
……//BREAK与CONTINUE功能的实现与方式1一样
ENDLOOP;
方式1:
FORV_NUMIN[RESERVER]VALUE1..VALUE2
LOOP
……//BREAK与CONTINUE功能的实现与方式1一样
ENDLOOP;
顺序控制
GOTO语句
语句将程序控制权无条件的转移到标签后指定代码。
也就是说这样将不会再回过头来执行GOTO后面的代码。
与标签一起使用,标签以<
标签没有结束符号。
直到遇到return;或代码结束。
例:
DECLARE
V_NUMNUMBER:
=10;
BEGIN
IFV_NUM=10THEN
GOTOLABAL1;
V_NUM:
=V_NUM+1;//这句代码是永远不会执行
ELSE
RETURN;--这个是必要的,其他情况就退出;否则程序会一直向下执行所以的代码。
ENDIF;
<
DBMS_OUTPUT.PUT_LINE(‘执行了LABLAL1标签’);//执行这句后,控制权不会再回去,因此程序会远行结束。
END;
NULL语句
什么也不做,只是将控制权转到下一条语句,可以用于需要解决一些语法格式,但又不需要执行任何代码的语句。
例如上面提到的循环中的CONTINUE功能。
RETURN语句
可以用于函数中的返回值,也可以用于在过程或存储过程中的强制退出的功能。
PL/SQL的语法结构
DECLARE
…//变量定义与初值赋予
BEGIN
…//业务代码
END;
异常处理
简介
ORACLE中的异常可分为系统异常和自定义异常,并拥有像JAVA、C#的异常捕捉的功能,
在捕捉时通过OTHERS来处理程序确保不会遗漏任何异常,同时可以使用SQLCODE和SQLERRM来返回错误代码和错误信息。
用户自定义异常一定要以显示的方式进行raise抛出。
可以通过RAISE_APPLICATION_ERROR(ERROR_NUMER,ERROR_MESSAGE);程序会自动回滚所有没有提交的事务。
其中消息最长可达2048个字节,编号必须在-2000到-20999之间的负数。
在ORACLE中可以通过SQLCODE,SQLERRM分别得到异常代码各异常原因描述.
用户自定义异常定义格式如下
DECLARE
MY_EXCEPTIONEXCEPTION;//定义自动异常
BGEIN
RAISEMY_EXCEPTION;//使用自定义异常。
这样的异常没有异常错误信息,但可以组织某一类的异常
AISE_APPLICATION_ERROR(ERROR_NUMER,ERROR_MESSAGE);//如果直接抛出应用程序异常,这样程序将会直接退出,并自动回滚所有的没有提交的事务
END;
在ORACLE中定义一个可以由系统自动触化的异常。
使用命令用自定义的异常支占用一个系统预定义的一个异常号
例如
DECLARE
MYEXCEPTIONEXCEPTION;
PAGAMEXCEPTION_INIT(MYEXCEPTION,-1400);
BEGIN
--向不能为空的列中插入NULL
INSERTINTOA(USER_NAME)VALUES(NULL);
COMMIT;
EXCEPTION
WHENMYEXCEPTIONTHEN
DBMS_OUTPUT.PUT_LINE(‘向不能为空的列中插入NULL’);
END
在ORACLE中的异常处也有如获至宝C#/JAVA中的异常处理块TRY{}CATCH(){}其结构是:
BEGIN
---执行代码
RAISEEXCEPTION_NAME;--
EXCEPTON
WHENEXCEPTION_NAMETHEN
--处理
--用SQLCODE/SQLERRM显示错误信息
WHENEXCEPTION_NAME2THEN
--处理
WHENOTHERSTHEN
--处理
END;
异常可以是用户自定义的也可是系统预定义的
自定义异常
MYEXCEPTIONEXCEPTION;
使用如:
RAISEMYEXCEPTION
RAISE_APPLICATION_ERROR(ERROR_CODE,
ERROR_MSG)
TRY{
---执行代码
THROWNEWEXCEPTIONOBJ();
}
CATCH(SUBEXCEPTIONE){
--处理
--用SQLCODE/SQLERRM显示错误信息
}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;//参数游标方式
例:
DECLARE
V_AGETB_QC120%ROWTYPE;
CURSORMY_CURSOR(USER_NAMEVARCHAR2)ISSELECT*FROMTB_QC120TWHERET.USER_NAMELILE'%'||USER_NAME||'%';
BEGIN
OPENMY_CURSOR('不放弃');
FETCHMY_CURSORINTOV_AGE;
LOOP
EXITWHENMY_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_AGE.USER_NAME);
ENDLOOP;
CLOSEMY_CURSOR;
END
关于ORACLE的游标的一次只能提取一条记录.用FETCH读取当前记录后自动指向下一行。
如果要用ORACLE的游标一次提取所有的记录,只能将数据暂时写入嵌套表中(也叫交BLUK大块COLLECT集合的数据)。
例:
DECLARE
TYPENAMESISTABLEOFVARCHAR2(30);
TYPECLASSESISTABLEOFSTUDENTS.CLASENO%TYPE;
CURSORMYCURSORISSELECTNAMES,CLASSES_NOFROMSTUDENTS;
V_NAMESNAMES;
V_CLASSES_NOCLASSES;
BEGIN
--OPEN
OPENMYCURSOR;
--FETCH
FETCHMYCURSORBULKCOLLECTINTOV_NAMES,V_CLASSES_NO;
--CLOSE
CLOSEMYCURSOR;
--USERTABLE_ZONE
FORIIN1..V_NAMES.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V_NAMES(I)||'++'||V_CLASSES_NO(I));
ENDLOOP;
END;
在包头中声明游标时,一要定义返回值,除非使用动态游标声明。
例:
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 学习 札记