ORACLE学习笔记整理Word格式文档下载.docx
《ORACLE学习笔记整理Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《ORACLE学习笔记整理Word格式文档下载.docx（21页珍藏版）》请在冰豆网上搜索。
也可以看出ORACLE中的字符串连接运行符非常得单“||”。
2.语言基础
2.1PL/SQL数据类型
在PL/SQL程序中定义变量、常量和参数时，必须要为它们指定数据类型，在编写PL/SQL时，可以使用标量类型、复合类型、参照类型和LOB（LargeObject大对象类型）类型等。
如下表所示：
2.1.1标量（SCALAR）
合法的标量类型与数据库的列所使用的类型相同，此外它还有一些扩展。
它又分为七个组：
数字、字符、行、日期、行标识、布尔和可信。
1.数字
它有三种基本类型--NUMBER、PLS_INTEGER和BINARY_INTENER。
NUMBER可以描述整数或实数，而PLS_INTEGER和BINARY_INTENER只能描述整数。
（1）NUMBER，是以十进制格式进行存储的，它便于存储，但是在计算上，系统会自动的将它转换成为二进制进行运算的。
它的定义方式是NUMBER（P，S），P是精度，最大38位，S是刻度范围，可在-84127间取值。
例如：
NUMBER（5，2）可以用来存储表示-999.99999.99间的数值。
P、S可以在定义是省略，例如：
NUMBER（5）、NUMBER等;
（2）BINARY_INTENER用来描述不存储在数据库中，但是需要用来计算的带符号的整数值。
它以2的补码二进制形式表述。
循环计数器经常使用这种类型。
（3）PLS_INTEGER和BINARY_INTENER唯一区别是在计算当中发生溢出时，BINARY_INTENER型的变量会被自动指派给一个NUMBER型而不会出错，PLS_INTEGER型的变量将会发生错误。
（4）Number补充
在Oracle中Number类型可以用来存储0，正负定点或者浮点数，可表示的数据范围在1.0*10-130——9.99*10125的数字，当Oracle中的数学表达式的值超过这个范围时，Oracle就会报错。
Number的数据声明如下：
1）Number（p,s）声明一个定点数p（precision）为精度，s（scale）表示小数点右边的数字个数，精度最大值为38，scale的取值范围为-84到127
2）Number（p）声明一个整数相当于Number（p,0）
3）Number声明一个浮点数其精度为38，要注意的是scale的值没有应用，也就是说scale的指不能简单的理解为0，或者其他的数。
定点数的精度（p）和刻度（s）遵循以下规则：
当s>
0时（s表示小数点右边的数字的个数）：
当一个数的整数部分的长度>
p-s时，Oracle就会报错
当一个数的小数部分的长度>
s时，Oracle就会舍入。
当s<
0时（s表示小数点左边的数字的个数）：
Oracle就对小数点左边的s个数字进行舍入。
当s>
p时,p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入例如：
Value
Datatype
StoredValue
123.2564
NUMBER
123.2564（不限制）
1234.9876
NUMBER（6,2）
1234.99（精确到小数点后两位）
12345.12345
Error（小数点左边的有效数字长度5小数点右边的有效数字长度2>
规定精度6）
NUMBER（6）
1235（等价于NUMBER（6,0））
12345.345
NUMBER（5,-2）
12300（小数点左边两位做四舍五入）
12345678
Error（8-2>
5）
123456789
NUMBER（5,-4）
123460000
1234567890
Error
12345.58
NUMBER（*,1）
12345.6
0.1
NUMBER（4,5）
0.01234567
0.01235
0.09999
0.09999
0.099996
2．字符
包括CHAR、VARCHAR2（VARCHAR）、LONG、NCHAR和NVARCHAR2几种类型。
（1）CHAR，描述定长的字符串，如果实际值不够定义的长度，系统将以空格填充。
它的声明方式如下CHAR（L），L为字符串长度，缺省为1，作为变量最大32767个字符，作为数据存储在ORACLE8中最大为2000。
（2）VARCHAR2（VARCHAR），描述变长字符串。
它的声明方式如下VARCHAR2（L），L为字符串长度，没有缺省值，作为变量最大32767个字节，作为数据存储在ORACLE8中最大为4000。
在多字节语言环境中，实际存储的字符个数可能小于L值，例如：
当语言环境为中文（SIMPLIFIEDCHINESE_CHINA.ZHS16GBK）时，一个VARCHAR2（200）的数据列可以保存200个英文字符或者100个汉字字符。
（3）LONG，在数据库存储中可以用来保存高达2G的数据，作为变量，可以表示一个最大长度为32760字节的可变字符串。
（4）NCHAR、NVARCHAR2，国家字符集，与环境变量NLS指定的语言集密切相关，使用方法和CHAR、VARCHAR2相同。
（varchar2是oracle提供的独特的数据类型，oracle保证在任何版本中该数据类型向上和向下兼容，但不保证varchar。
因为varchar是标准sql提供的数据类型，有可能随着sql标准的变化而改变，推荐用varchar2。
）
（CHAR如果存放字母数字占1个字节，存放GBK编码的汉字存放2个字节，存放UTF-8编码的汉字占用3个字节；
NCHAR根据所选字符集来定义存放字符的占用字节数，一般都为2个字节存放一个字符（不管字符或者汉字）。
同理VARCHAR2与NVARCHAR2的区别。
3．行
包括RAW和LONGRAW两种类型。
用来存储二进制数据，不会在字符集间转换。
（1）RAW，类似于CHAR，声明方式RAW（L），L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。
（2）LONGRAW，类似于LONG，作为数据库列最大存储2G字节的数据，作为变量最大32760字节。
（raw这种格式可以用来保存较小的图形文件或带格式的文本文件，如MiceosoftWord文档。
raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
longraw无可变长二进制数据，最大长度是2GB。
Oracle用这种格式来保存较大的图形文件或带格式的文本文件，如MiceosoftWord文档，以及音频、视频等非文本文件。
在同一张表中不能同时有long类型和longraw类型，longraw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代）
4．其他
（1）日期，只有一种类型--DATE，用来存储时间信息，站用7个字节（从世纪到秒），绝对没有“千年虫”问题。
（2）行标识，只有一种类型--ROWID，用来存储“行标识符”，可以利用ROWIDTOCHAR函数来将行标识转换成为字符。
（3）布尔，只有一种类型--BOOLEAN，仅仅可以表示TRUE、FALSE或者NULL。
（4）可信，只有一种类型--MLSLABEL，可以在TRUSTEDORACLE中用来保存可变长度的二进制标签。
在标准ORACLE中，只能存储NULL值。
5．复合（COMPOSITE）与引用（REFERENCE）
后面专门介绍。
6．LOB
LOB变量主要是用来存储大量数据的数据库字段，最大可以存储4G字节的内容。
主要有：
（1）CLOB：
和LONG类型相似，存储单字节字符数据。
（别用来存中文）
（2）NCLOB：
用来存储定宽多字节字符数据。
（3）BLOB：
和LONGRAW相似，用来存储无结构的二进制数据。
（4）BFILE：
它用来允许ORACLE对数据库外存储的大型二进制文本进行只读形式的访问。
2.1.2复合类型
复合变量是指用来存放多个值的变量，当定义复合变量时，必须要使用PL/SQL的复合数据类型，复合数据类型包括PL/SQL记录、PL/SQL表、嵌套表以及变长数组。
（1）PL/SQL记录
PL/SQL记录是由一组相关的记录成员组成的，类似于C语言中的结构体。
语法格式有为：
Type记录类型名isrecord（
成员名1类型,
成员员2类型,
……
成员名n类型）;
定义记录变量格式为：
记录变量名记录类型名
也可以直接用数据表的行定义记录变量，格式为：
记录变量名表%rowtype
示例1：
declare
typexsisrecord（
xh学生信息.学号%type,
xm学生信息.姓名%type
）;
xxs;
begin
select学号,姓名intoxfrom学生信息where学号='
9512101'
;
dbms_output.put_line（'
学号:
'
||x.xh）;
姓名:
||x.xm）;
end;
示例2：
x学生信息%rowtype;
select*intoxfrom学生信息where学号='
||x.学号）;
||x.姓名）;
（2）PL/SQL表
为了处理单行单列的数据，可以使用标题变量；
为了处理单行多列的数据，可以使用PL/SQL记录；
而为了处理单列多行数据，就可以使用集合类型（PL/SQL表、嵌套表、变长数组）了。
这里只介绍PL/SQL表。
PL/SQL表类似于C语言中的数组，但其下表可以是负数，且PL/SQL表的个数没有限制，下标没有上下限。
声明语法如下：
TypePL/SQL表名istableof表元素的数据类型indexby表元素下标的类型
注：
表元素下标的类型为binary_integer、pls_integer或varchar2
typexxistableof学生信息.姓名%typeindexbybinary_integer;
yxx;
select姓名intoy
（2）from学生信息where学号='
||y
（2））;
代码y
（2）中的2可以是任意整数，这里表示下标为2的那个元素。
2.2PL/SQL控制结构
2.2.1条件分支语句
语法如下：
If条件then
语句；
Elsif条件then
Else
语句
Endif;
这里注意：
elsif是一个单词，而endif是两个单词。
2.2.2循环语句
（1）Loop循环
Loop
语句块;
Exitwhen条件;
Endloop;
示例：
iint:
=1;
loop
dbms_output.put_line（i）;
i:
=i+1;
exitwheni=10;
endloop;
没有exit语句将会死循环。
（2）while循环
语法如下：
While条件Loop
Endloop;
whilei<
10loop
（3）for循环
For循环控制变量in[reverse]最小值..最大值loop
语句组;
注意：
循环控制变量每次递增1（递减1，如果有reverse选项的话）。
foriinreverse1..10loop
2.3游标
游标的概念这里不就再多讲了。
ORACLE中分为隐含游标和显式游标，以下只涉及显式游标。
2.3.1显示游标
1．定义游标
语法格式如下：
Cursor游标名is游标所对应的select语句；
游标声明段定义。
2．打开游标
Open游标名;
打开游标的实际上就是执行定义游标语句中的select语句，并将结果暂存到结果集中。
3．提取数据
（1）一次提取一行数据
Fetch游标名into变量名1，变量名2……；
（2）一次提取多行数据
Fetch游标名intobulkcollectinto集合变量1，集合变量2……；
4．关闭游标
Close游标名；
5．显式游标属性
显式游标属性用于返回显式游标的执行信息，有4个属性：
（1）%found
若当前fetch语句成功取出一行数据，则属性值为true；
否则返回值为false。
该属性可用来判断是否应关闭游标，在循环变量中常用该属性决定循环的结束。
（2）%notfound
与
（1）相反
（3）%isopen
当游标已经打开且尚未关闭时，属性值为true，该属性可用来判断游标的状态。
（4）%rowcount
返回游标已检索到的数据行个数。
使用游标提取“计算机系”学生的姓名和出生日期
cursorx_cursorisselect姓名,出生日期from学生信息where系别='
计算机系'
xm学生信息.姓名%type;
sr学生信息.出生日期%type;
openx_cursor;
fetchx_cursorintoxm,sr;
exitwhenx_cursor%notfound;
dbms_output.put_line（xm||'
:
||sr）;
closex_cursor;
示例2：
使用记录变量
x_recordx_cursor%rowtype;
--定义基于游标类型的记录变量
ifnotx_cursor%isopenthen
endif;
fetchx_cursorintox_record;
dbms_output.put_line（x_record.姓名||'
||x_record.出生日期）;
查询的记录条数为:
||x_cursor%rowcount）;
2.3.2参数游标
语法格式：
Cursor游标名（参数名参数类型）isselect语句
显示指定系别所有学生的姓名及出生日期
cursorx_cursor（xb学生信息.系别%type）is
select姓名,出生日期from学生信息where系别=xb;
openx_cursor（'
信息系'
）;
2.3.3使用游标更新或删除数据
更新数据格式为：
Cursor游标名[（参数名参数类型）]isselect语句forupdate[of列][nowait]
Forupdate子句用于在游标结果集上加共享锁，如果省略了of子句，则对全表加锁，否则只对指定列加锁。
在提取了游标数据之后，为了更新或删除当前游标行数据，必须在update或delete语句中引用wherecurrentof子句。
语法格式下：
Update表名set列名=表达式wherecurrentof游标名；
Deletefrom表名wherecurrentof游标名；
将成绩低于60分的减5分，高于80分的加5分。
cursorx_cursorisselect成绩from选课信息forupdateof成绩;
exitwhenx_cursor%notfound;
ifx_record.成绩<
60then
dbms_output.put_line（x_record.成绩）;
update选课信息set成绩=成绩-5wherecurrentofx_cursor;
elsifx_record.成绩>
80then
update选课信息set成绩=成绩+5wherecurrentofx_cursor;
删除成绩为NULL的记录。
cursorx_cursorisselect成绩from选课信息forupdate;
x_recordx_cursor%rowtype;
ifx_record.成绩isnullthen
deletefrom选课信息wherecurrentofx_cursor;
2.3.4游标的FOR循环
游标的FOR循环简化了对游标的处理，使用游标FOR循环时，ORACLE会隐含打开游标，提取游标数据并关闭游标。
For记录变量名in游标名loop
语句组；
使用游标FOR循环显示“计算机系”学生名单
cursorx_cursorisselect姓名from学生信息where系别='
forx_recordinx_cursorloop
第'
||x_cursor%rowcount||'
个学生:
||x_record.姓名）;
可以看出，在FOR循环中使用游标会更加方便。
3．过程与函数
过程和函数是被命名的PL/SQL块的子程序，可以在不同的应用程序中多次调用。
过程用于执行特定操作，函数用于返回特定的数据。
合理应用过程和函数，可以简化客户端的开发和维护，并提高应用程序的性能。
3.1存储过程
编写一个向学生信息表中插入一条记录的存储过程。
createorreplaceprocedureaddnewstu（
xm学生信息.姓名%type,
xb学生信息.系别%type）as
insertinto学生信息（学号,姓名,系别）values（xh,xm,xb）;
endaddnewstu;
调用时格式为：
Call过程名（实际参数列表）；
Exec过程名（实际参数列表）；
3.2触发器
Createorreplacetrigger触发器名
<
before|after|insteadof>
触发事件on
database|用户模式名|表名|视图名|
referencing<
oldas旧值别名>
|<
newas新值别名>
|
<
parentas父表别名>
[foreachrow][when触发条件]
触发器体；
升级会员 