Oralce常识
来源:互联网 发布:sql语句中降序排列 编辑:程序博客网 时间:2024/05/18 00:21
- 一.Oracle数据类型
- 1.字符数据类型
- .> char:可以存储字母数字值,长度在1到2000个字节。
- .> varchar2:存储可变长度的char类型字符串,大小在1到4000个字节范围内。
- .> long:存储可变长度的字符数据,最多存储2GB。
- long类型的使用限制:
- **************************************
- *{
- *.. 一个表中只有一列可以为long数据类型。
- *.. long列不能定义为唯一约束或主键约束。
- *.. long列上不能建立索引。
- *.. 过程或存储过程不能接受long数据类型的参数。
- *}
- ***************************************
- 2.数字数据类型
- .> number:存储正数,负数,零,定点书和精度为38位的浮点数。
- number类型的格式:
- number[(p[,s])]
- 其中p为精度,表示数字的总位数; s为范围,表示小数点右边的位数,它在-84至127之间。
- 3.日期时间数据类型
- .> date:存储表的日期和时间数据,使用7个字节固定长度,
- 每个字节分别存储世纪,年,月,日,小时,分和秒;值从公元前4712年1月1日到公元9999年12月31日。
- {
- Oracle中的sysdate函数功能是返回当前的日期和时间。
- }
- .> timestamp:存储日期的年,月,日以及时间的小时,分和秒值。其中秒值精确到小数点后6位,同时包含时区信息。
- {
- Oracle中的systimestamp函数功能是返回当前日期,时间和时区。
- }
- 4.raw和long raw数据类型(二进制数据)
- .> raw: 存储基于字节的数据。最多存储2000个字节,使用需指定大小。raw数据类型可以建立索引。
- .> long raw: 存储可变长度的二进制数据。最多能存储2GB;不能索引。与long类型的限制同效。
- 5.lob数据类型('大对象')
- ***********************************
- * lob类型可以存储多达4GB的非结构化信息。
- * Oracle中的表可以有多个lob列,每个lob列可以是不同的lob类型。
- ***********************************
- .> clob: 存储大量的单字节字符数据和多字节字符数据。例:非结构化的XML文档。
- .> blob: 存储较大的二进制对象。例:图形,视频,音频等。
- .> bfile: 文件定位器;指向位于服务器文件系统是的二进制文件(存储一个文件路径)。
-
- 6.Oracle中伪列
- .> rowid: 返回行地址,可用来定位表中的一行。可以唯一地标识数据库中的一行。
- 重要的用途
- ****************************
- *.. 能一最快的方式访问表中的一行
- *.. 能显示表的行是如何存储的
- *.. 可以作为表中行的唯一标识
- ****************************
- .> rownum: 对于一个查询返回的每一行进行标识;可用于限制查询返回的行数。(不是数据表的实际列)
-
- 二.Oracle中的sql操作符
- .>算数操作符:
- 包括: +(加),-(减),*(乘),/(除)
- *和/具有相同的优先级,+和-j具有相同的优先级,*和/的优先级高于+和-。可用小括号来控制计算顺序。
- .>比较操作符:
- 包括:=(相等),!=(不相等),<(小于),>(大于),<=(小于等于),>=(大于等于)
- between....and...(检查是否在两个值之间)
- in(与列表中的值相匹配)
- link(匹配字符模式<模糊匹配>)
- is null(检查是否为空)
- .>逻辑操作符:
- 包括:
- and(与),or(或),not(非)
- .>集合操作符:
- 包括:
- union(联合):返回两个查询选定的所有不重复的行。
- union all(联合所有):合并两个查询选定的所有行,包括重复的行。
- intersect(交集):返回两个查询共有的行。
- minux(减集):返回有第一个查询选定但是没有被第二个查询选定的行。
- .>连接操作符:
- || : 将两个或多个字符串合并成一个字符串,或者将一个字符串与一个数值合并在一起。
-
-
- 操作符的优先级
- ************************************************
- *算数操作符 高
- *连接操作符 |
- *比较操作符 |
- *not逻辑操作符 |
- *and逻辑操作符 |
- *or逻辑操作符 低
- ************************************************
-
- 三.SQL函数
- .>日期函数
- add_months(d,n)<d是日期,n是月数>:返回指定日期加上指定月数后的日期值。
- months_between(d1,d2)<d1,d2都是两个日期>:返回两个日期之间的月数。
- last_day(d)<d是日期>:返回指定日期当月的最后一天的日期值。
- round(d,[fmt])<d是日期,fmt指定格式模型>:返回日期值,此日期四舍五入为格式模型指定的单位。
- (fmt是一个选项,默认舍入为最靠近的那一天。
- 如果格式为‘year’则舍入到年的开始,即1月1日;
- 如果格式为‘month’则舍入到月的第一天;
- 如果格式为‘day’则舍入到最靠近的星期天。
- )
- next_day(d,day)<d是日期,day指定周内任何一天 如‘星期一’>:返回指定的下一个星期几的日期。
- trunc(d,[fmt])<d是日期,fmt指定格式模型>:返回日期值,指定日期截断为格式模型指定的单位的日期。
- (与round函数类似,不同的是它只舍不入。)
- extract(fmt from d)<fmt指定格式模型,d是日期>:提取日期时间类型中的特定部分。
- (fmt取值可以是:year,month,day,hour,minute,second;注意此处的格式不能使用单引号。)
- .>字符函数
- 函数 说明 输入 输出结果
- initcp(char) 首字母大写 select initcap('hell0') from dual Hello
- lower(char) 转换为小写 select lower('FUN') from dual fun
- upper(char) 转换为大写 select upper('sun') from dual SUN
- ltrim(char,set) 左剪裁 select ltrim('xyzadams','xyz') from dual adams
- rtrim(char,set) 右剪裁 select rtrim('xyzadams','ams')from dual xyzad
- translate(char,from,to) 按字符翻译 select translate('jack','abcd','1234') from dual j12k
- peplace(char,search_str,replace_str) 字符串替换 select replace('jack and jue','j','bl') from dual jack and blue
- instr(char,substr[,pos]) 查找子串位置 select instr('worldwide','d') from dual 5
- substr(char,pos,len) 取子字符串 select substr('abcdefg',3,2) from dual cd
- concat(char1,char2) 连接字符串 select concat('Hello','world') from dual Helloworld
- =================================
- .. chr(int)<int是ASCII码>:根据ASCII码返回对应的字符。
- .. lpad(str,int,char)和rpad(str,int,char)<str是源字符串,int指定总长度,char是填充的字符>:用指定的字符(左|右)填充源字符串到指定长度。
- .. trim([[leading|trailing] trim_char] from trim_sourse)<trim_char指定裁剪的字符,trim_sourse是源字符串>
- (
- 此函数组合了ltrim和rtrim的功能。
- leading选项时与ltrim相似,裁减与trim_char相等的开头字符。
- trailing选项时与rtrim相似,裁减与trim_char相等的结尾字符。
- )
- .. length(str)<str是字符串>:返回字符串的长度。
- .. decode(expr,search1,trsult1,search2,trult2...[,default])<expr是字符变量或数据表字段,search是expr的预期值,trsult是返回值,default是无匹配是返回的值>
- (
- decode函数进行逐个值的替换。
- )
- .>数字函数
- 函数 说明 输入 输出结果
- abs(n) 取绝对值 select abs(-15) from dual 15
- ceil(n) 向上取整 select ceil(44.778) from dual 45
- sin(n) 正弦 select sin(1.571) from dual 0.999999979
- cos(n) 余弦 select cos(0) from dual 1
- sign(n) 取符号 select sign(-32) from dual -1
- floor(n) 向下取整 select fllor(100.2) from dual 100
- power(n) m的n次幂 select power(4,2) from dual 16
- mod(m,n) 取余数 select mod(10,3) from dual 1
- round(m,n) 四舍五入 select round(100.256,2) from dual 100.26
- trunc(m,n) 截断 select trunc(100.256,2) from dual 100.25
- sqrt(n) 平放根 select sqrt(4) from dual 2
- .>转换函数
- to_char(d|n[,fmt])<d是日期,n是数字,fmt是指定日期或数字的格式>:将指定的日期或数字转换成字符串(varchar2)。
- to_date(char[,fmt])<char是日期格式的字符串,fmt是日期的格式>:将char或varchar数据类型转换为日期类型。
- to_number(char)<char是包含数字的字符串>:将包含数字的字符串转换为数字。
- .>其他函数
- nvl(expression1,expression2)<exprission1是变量或数据表字段,exprission2是一个值>:将空值替换为指定的值。
- (
- 如果expression1为NULL,则nvl返回expression2
- 如果expression1不为NULL,则nvl返回expression1
- <sexpression2的类型将转换为expression1的类型>
- )
- nvl2(expression1,expression2,expression3)<exprission1是变量或数据表字段,exprission2和exprission3是一个值>:与nvl类似
- (
- 如果expression1不为NULL,则nvl返回expression2
- 如果expression1为NULL,则nvl返回expression3
- )
- nullif(expr1,expr2)<expr1,expr1分别为表达式>:比较两个表达式,如果相等,则返回空值(null),否则返回expr1。
- .>分组函数
- avg(column)<column是列名>:返回参数中指定列的平均值。
- min(column)<column是列名>: 返回参数中指定列的最小值。
- max(column)<column是列名>: 返回参数中指定列的最大值。
- sum(column)<column是列名>: 返回记录集中值的总和。
- count([distinct]*|column[,column..])<distinct选项指定去除重复项,column是列名>: 返回记录集中的行数。
- <group by子句用于将信息表化分为组,having字句用来指定group by的检索条件>
- .>分析函数
- row_number() over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名,clause指定排列列名>:为有序组中的每一行(化分部分的行或查询返回的行)返回一个唯一的排序
- 值,序号由order by字句指定,从1开始。(值相同,而排位不相同)
-
- rank () over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名,clause指定排列列名>:计算一个值在一组值中的排位,排位是以1开头的连续整数,具有相等值的行排
- 位相同,序数随后跳跃相应的数值。(值相同,排位相同,排位有可能不连续)
-
- dense_rank() over ([partition by column] order by clause[,多列] [desc|esc])<column指定分组列名,clause指定排列列名>:计算一个行在一组有序行中的排位,排位是以1开头的连续整数,具有相同
- 值的排位相同,并且排位是连续的。(值相同,排位相同,排位连续)
-
- 四.Oracle锁
- 锁定是数据库用来控制共享资源并发访问的机制。
- .>行级锁(用于特定行)
- 行级锁是一中排他锁,防止其他事务修改此行,当是不会阻止读取此行的操作。
- 在使用insert,update,delete和select ...for update等语句时,Oralce会自动应用行级锁定。
- select...for update语句允许用户每次选择多行记录进行更新,这些记录会被锁定,且只能有发起查询的用户进行编辑。只有在回滚或提交事务后,锁定才会释放,其他用户才可以编辑这些记录。
- {
- select ...for update [of column_list] [wait n | nowait ]<column_list是列的列表,n是等待的秒数,nowait指定不等待>
- > of 子句用于指定即将更新的列,即锁定行上的特定列。
- > wait 子句指定等待其他用户释放锁的秒数,防止无限制的等待。
- }
- .>表级锁(用于整个表)
- 表级锁定将保护表数据,在事务处理过程中,表级锁会限制对整个表的访问。可以使用lock table语句显示地锁定表。表级锁用来限制对表执行添加,更新和删除等修改操作。
- {
- lock table <table_name> in <lock_mode> mode [nowait];<table_name是锁定表的名称,lock_mode是锁定的模式,nowait指定不等待>
- >lock_mode是锁定的模式。
- >nowait 关键字用于防止无限期的等待其他用户释放锁。
- (
- 表级锁的模式包括:
- >行共享(row share,rs): 允许其他用户访问和锁定该表,但是禁止排他锁定整个表。
- >行排他(row exclusive,rx):与行共享相同,同时禁止其他用户在此表上用共享锁。
- >共享(share, s):仅允许其他用户查询表中的行,但不允许插入,更新和删除。
- >共享行排他(share row exclusive,srx):执行比共享锁更多的限制。防止其他事物在表上应用共享锁,共享排他锁以及排他锁。
- >排他(exclusive,x):对表执行最大限制。除了允许其他用户查询该表的记录,排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。
- )
- * 执行commit或rollback命令可以释放锁定。
- }
- 五.Oracle表分区
- 表分区的优点:
- .. 改善表的查询性能。
- .. 表更容易管理。
- .. 便于备份和恢复。
- .. 提高数据安全性。
- .>范围分区
- 范围分区根据表的某一列或一组列的值范围,决定数据存储在那个区上。
- 在create table语句中增加parition子句可以创建表分区。
- 语法:
- partition by rang (column_name)
- (
- partition part1 value less than(range1) [tablespace tbs1],
- partition part2 value less than(range2) [tablespace tbs2],
- ......
- partition partn value less than(MAXVALUE) [tablespace tbsN]
- );
- 其中:
- column_name:是以其为基础创建范围分区的列,特定列的该列值称为分区键。
- part1..partn:是分区的名称。
- range1...MAXVALUE:是分区的边界值。
- tbs1...tbsn:是分区所在的表空间(可选项)。
- .>散列分区
- 散列分区通过分区键值上执行一个散列函数来决定数据的物理位置。(散列分区把记录平均分布到不同的分区,减少了磁盘I/O争用的可能性)
- 语法:
- partition by hash (column_name)
- partitions number_of_partitions [store in (tablespace_list)];
- 或
- partition by hash (column_name)
- (
- partition part1 [tablespace tbs1],
- partition part2 [tablespace tbs2],
- .....
- partition partn [tablespace tbsn]
- )
- 其中:
- column_name:是以其为基础创建散列分区的列。
- number_of_partitions:是散列分区的数目,使用这种方法系统会自动生成分区的名称。
- tablespace_list:指定分区使用的表空间,如果分区数目比表空间的数目多,分区将会以循环的方式分配到表空间中。
- .>复合分区
- 复合分区是范围分区和散列分区的结合。
- 语法:
- partition by rang (column_name1)
- subpartition by hash (column_name2)
- subpartitioins number_of_partitions [store in (tablespace_list)]
- (
- partition part1 value less than(range1) [tablespace tbs1],
- partition part2 value less than(range2) [tablespace tbs2],
- ......
- partition partn value less than(MAXVALUE) [tablespace tbsN]
- )
- 其中:
- column_name1:是以其为基础创建范围分区的列。
- column_name2:是以其为基础创建散列分区的列。
- number_of_partitions:是散列分区的数目。
- part1..partn:是分区的名称。
- range1...MAXVALUE:是分区的边界值。
- .>列表分区
- 列表分区允许用户明确地控制行到分区的映射。
- 语法:
- partition by list (column_name)
- (
- partition part1 values (values_list1),
- partition part2 values (values_list2),
- ....
- partition partn values (DEFAULT)
- )
- 其中:
- column_name:是以其为基础创建列表分区的列。
- part1..partn:是分区的名称。
- values_list:是对应分区键值的列表。
- DEFATLT:关键字允许存储前面的分区不能存储的记录。
- ***************************************************************
- *在分区表中插入记录:与在普通表中插入数据完全相同。
- *在分区表中查询记录:select * from 表名 partition (分区名)
- *删除分区中的记录: delete from 表名 partitioin (分区名)
- ***************************************************************
-
- <*>分区维护操作
- .>添加分区
- alter table ... ADD partition语句用于在现所有的最后一个分区(称为'高'端)之后添加新的分区。
- 例:
- alter table sales add partitions p4 values less than (4000);
- .>删除分区
- alter table ... drop partition 语句用于删除分区。(删除分区时,分区中的数据也随之删除)
- 例:
- alter table sales drop partition p4;
- .>截断分区
- alter table ... truncate partition 语句用于截断分区,截断分区将删除分区中的所有记录。
- 例:
- alter table sales truncate partition p3;
- .>合并分区
- 合并分区可将范围分区表或复合分区表的两个相邻分连接起来。结果分区将继承被合并的两个分区的较高上界。
- 语法:
- alter table table_name
- merge partitions partitions1_name,partitions2_name
- into partition3_name;
- 其中:
- table_name:是表名。
- partitions1_name,partitions2_name:是已有分区。
- partitions3_name:合并到分区的名称。
- .>拆分分区
- 使用split partition语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。当分区过大,可以对分区进行拆分。
- 语法:
- alter table table_name split partition partiton_name at (value)
- into (partition partiton1,partition partiton2);
- 其中:
- table_name:是表名。
- partiton_name:已有分区名。
- value:拆分分隔值。
- partition partiton1,partition partiton2:表示拆分后的新分区。
- 六.同义词
- 同义词是数据库对像的一个别名,这些对象可以是表,视图,序列,过程,函数,程序包,甚至其他同义词。
- 同义词用途:
- {
- .. 简化sql语句
- .. 隐藏对象的名称和所有者
- .. 为分布式数据库的远程对象提供了位置透明性
- .. 提供对象的公共访问
- }
- 同义词允许应用程序访问数据库对象,不论哪个用户或哪个数据库拥有该对象。但是同义词不能代替权限,在使用同义词前要确保用户已得到访问对象的权限。
- 可以通过同义词执行select,insert,update,delete,lock table,grant和revoke等语句。同义词只是表的一个别名,因此对它的所有操作都会影响到表。
- .>私有同义词
- 私有同义词只能被当前模式的用户访问。
- 私有同义词名称不可与当前模式的对象名称相同。
- 要在自身的模式创建私有同义词,用户必须拥有create sysnonym系统权限。
- 要在其他用户模式创建同义词,用户必须拥有create any synonym系统权限。
- 语法:
- create [or replace] synony [schema.]synonym_name for [schema.]object_name
- 其中:
- or relaoce:表示在同义词存在的情况下替换该同义词。
- synonym_name:表是要创建的同义词的名称。
- object_name:指定要为之创建同义词的对象的名称。
- .>公有同义词
- 公有同义词可被所有的数据库用户访问。
- 创建公有同义词,用户必须拥有greate public synonym系统权限。
- create [or replace] public synonym synonym_name for [schema.]object_name
- 其中:
- or relaoce:表示在同义词存在的情况下替换该同义词。
- synonym_name:表是要创建的同义词的名称。
- object_name:指定要为之创建同义词的对象的名称。
- <*>删除同义词
- drop synonym语句用于从数据库中删除同义词。要删除同义词用户必须有相应的权限。
- 语法:
- drop [public] synonym [schema.]synonym_name;
- 七.序列
- 序列是用来生成唯一,连续的整数的数据库对象。
- 序列通常用来自动生成主键或唯一的值。
- 序列可以按升序排列,也可以按降序排列。
- 语法:
- create sequence sequence_name
- [stare with integer]
- [increment by integer]
- [maxvalue integer|nomaxvalue]
- [minvalue integer|nominvalue]
- [cycle|nocycle]
- [cache integer|nocache];
- 其中:
- sequence_name:是创建的序列名称。
- stare with:指定要生成的第一个序列号。
- increment by:用于指定序列好之间的间隔。
- maxvalue:指定序列可以生成的最大值。
- nomaxvalue:如果指定了nomaxvalue,oracle将升序序列的最大值设为10的27次方;将降序序列的最大值设为-1。
- minvalue:指定序列可以生成的最小值。
- nominvalue:无最小值,oracle将升序序列的最小值设为1;将降序序列的最小值设为10的-26次方。
- cycle:指定序列在达到最大值或最小值后,将继续从头开始生成值。
- nocycle:指定序列在达到最大值或最小值后,将不能在继续生成值。这是默认选项。
- cyche:使用cyche选项可以预先分配一组序列号,并将其保存在内存中。这样可以更快地访问序列号,但用完缓存中的所有序列号,Oralce将生成另一组数值,并将其保留在缓存中。
- nocyche:不缓存序列号。 如果创建序列时忽略了cyche和nocyche选项,Oracle将默认缓存20个序列号。
- <*>访问序列
- 语法;
- sequence_name . nextval|currval
- 其中:
- sequence_name:是已创建的序列名称。
- nextvla:创建序列后第一次使用nextval时,将返回该序列的初始值。以后在引用nextval时,将使用increment by子句的值来增加序列值,并返回这个新值。
- currval:返回序列的当前值。
- <*>更改序列
- alter sequence 命令用于修改序列的定义。
- {
- .. 设置或删除minvalue或maxvale。
- .. 修改增量值。
- .. 修改缓存中的序列号的数目。
- }
- 语法:
- alter sequence [schema.]sequence_name
- [increnment by integer]
- [maxvalue integer|nomaxvalue]
- [minvalue integer|nomaxvalue]
- [cycle|nocycle]
- [cache ingeter|nocache];
- 注意:不能修改序列的start with参数。
- <*>删除序列
- drop sequence命令用于删除序列。
- 语法:
- drop sequence 序列名
- 八.视图
- 视图一经过定制的方式显示包含爱一个或多个表(或其他视图)中的数据。
- 语法:
- create [or replace] [force|noforce] view view_name
- [(alias[,alias]...)]
- as select_statement
- [with check option [constraint constraint]]
- [with read only];
- 其中:
- or relaoce:表示在视图存在的情况下替换该视图。
- force:无论基表是否存在,都将创建视图。
- noforce:仅当基表存在才创建视图。
- view_name:创建视图的名称。
- alias:指定有视图的查询所选择的表达式或列的别名。别名的数目必须与视图所选择的表达式的数目相匹配。
- select_statement:表示select语句。
- with check option:指定只能插入或更新视图可以访问的行。术语constraint表示check option约束指定的名称。
- with read only:确保不能在此视图上执行任何修改操作。(只渎)
- <*>在视图上使用DML语句的限制:
- > 在视图中使用DML语句只能修改一个底层的基表。
- > 如果记录的修改违反了基表的约束条件,则无法更新视图。
- > 如果创建的视图包含连接运算符,distinct运算符,集合运算符,聚合运算符和Group by子句,则无法更新视图。
- > 如果创建的视图包含伪劣或表达式,则将无法更新视图。
- <*>键保留表
- 在联结视图中,如果视图包含了一个表的主键,并且也是这个视图的主键,则这个键被保留,这个表称为键保留表。
- <*> 删除试图
- 语法:
- drop view 视图名称
- 九.索引
- 索引只是一种快速访问数据的途径,它只影响执行的速度。
- .>唯一索引
- 索引可以是唯一的,也可以是非唯一的。唯一索引可以确保在定义索引的列中,表的任意两个值都不相同。
- 语法:
- create unique index 索引名 on 表名(唯一列名);
- 其中:
- unique:指定为唯一索引。
- .>组合索引
- 组合索引是在表中的多列上创建的索引。
- 语法:
- create index 索引名 on 表名(列名,列名);
- .>反向键索引
- 可以在create index语句中指定关键字Reverse创建反向键索引。
- 语法:
- create index 索引名 on 表名(列名) reverse;
- 其中:
- reveerse:指定为反向键索引。
- 使用noreverse可以将反向键索引重建为标准索引。
- 例: alter index 索引名 rebuild noreverse;
- 注意: 不能将标准索引重建为反向键索引。
- .>位图索引
- 使用位图索引的优点在于,它最适用于低基数列,也就是不同值的数目比表的行数少的列(枚举列)。
- 语法:
- create bitmap index 索引名 on 表名 (列名);
- 其中:
- bitmap:指定为位图索引。
- <*>位图索引优点
- > 对于大批即席查询,可以减少响应时间。
- > 相比其他索引技术,占用空间明显减少。
- > 即使在配置很低的终端硬件上,也嫩个获得显著的性能。
- .>索引组织表
- 索引组织表与普通表的不同之处在于,该表的数据存储在与关联的索引中。对表数据进行的修改,如添加新行,更新和删除行,只会导致对索引的更新。
- 语法:
- create table 表名
- (
- 列名 类型 primary key,
- ...
- )
- organization index;
- 其中:
- organization index: 指定为索引组织表。
- <*>注释
- primary key是创建索引组织表所必需的。
- .>索引中分区
- > 局部分区索引
- 局部分区索引是在分区表上创建的一中索引,在局部分区中Oracle为表的每个分区建立一个独立的索引。
- 语法:
- create index 索引名 on 表名(列名) local;
- 其中:
- local:指定为本地分区创建索引。
- > 全局分区索引
- 全局分区索引是指在分区表或非分区表上创建的索引。
- 语法:
- create index 索引名 on 表名(列名) global
- partition onrange (列名)
- (
- ...分区
- )
- 其中:
- global:指定为全局分区创建索引。
- >全局非分区索引
- 全局非分区索引是在分区表上创建的全局索引,它类似于非分区表上的索引,索引的结构不会被分割。
- 十.pL/Sql编程
- 优点:
- >支持sql。
- >支持面向对象编程(oop)。
- >更好的性能。
- >可移植性。
- >与sql集成。
- >安全性。
- <*> PL/SQL块
- >声明块:声明块中使用的变量,游标和自定义异常。这些声明的作用域仅限于它们所在的块。此外,局部子程序也可以在PL/SQL块的声明部分中声明。
- >可执行部分:执行命令并操作在声明部分声明的变量和游标。
- >异常处理部分:处理执行块时引发的异常。
- pl/sql块声明语法:
- [declare
- 所有声明]
- begin
- 执行语句
- [exception
- 异常处理]
- end;
- >pl/sql对大小写不敏感,但是用户和用户的开发团队应该选择一个合适的编码标准,以确保最好的使用共享池。
- >pl/sql中一些复合符号的含义:
- := 赋值操作符
- || 连接操作符
-
- /*,*/ 多行注释
- <<,>> 标签分隔符
- .. 范围操作符
- ** 求幂操作符
- <*>数据类型
- 标量数据类型
- >数字数据类型
- 数字数据类型存储的数据为数字,用此数据类型存储的数据可用于计算。
- 数字类型包括:
- a: binary_integer
- {
- 用于存储带符号的整数。值的大小范围介于-2的31次方减1到2的31次方减1之间。
- binary_integer的子类型:
- .. natural:可以限制变量存储非负整数值,即自然数。
- .. naturaln:可以限制变量存储自然数,且非空。
- .. positive:可以限制变量存储正整数。
- .. positiven:可以限制变量存储正整数,且非空。
- .. signtype:可以限制变量只存储-1,0和1三个值。
- }
- b: number
- {
- 用于存储整数,定点数和浮点数。
- 语法:
- number[(presision,scale)]
- 其中:
- presision:是精度。
- scale:是小数位数。
- 只能用整数文字指定精度和小数位数,而不能用常量或变量指定精度和小数位数。
- number数据类型的一些子类型包括:
- .. decimal:用于声明最高精度为38位的十进制数字的定点数。
- .. float:声明最高精度为126位的二进制数字的浮点数。
- .. ingeter:声名最高精度为38为的十进制数字的整数。
- .. real: 声明最高精度为63位的二进制数字的浮点数。
- }
- c: pls_integer
- {
- 存储带符号的整数。pls_integer的大小范围介于-2的31次方到2的31次方之间。与number和binary_integer类型相比,它执行运算的速度更快。
- pls_integer运算以机器算术运算为基础,而number和binary_integer运算以库算术为基础。此外,与number数据类型相比,pls_integer需要的
- 存储空间更小。通常建议在执行处于pls_integer的数值范围类的所有计算时使用此数据类型以提高效率。
- }
- >字符数据类型
- 字符数据类型勇于存储字符串或字符数据。
- a: char
- {
- 存储固定长度的字符数据。(不超过32767个字节的最大长度)
- 语法:
- char[(maximum_size[char|btye])]
- 其中:
- maximum_size:是最大长度。
- char|byte:指定长度为字符数或字节数。
- }
- b: raw
- {
- 存储二进制数据或字节串。(类似于char,不同之处是它们不在字符集中转换)
- 语法:
- raw(maximum_size)
- 其中:
- miximum_size:是最大长度。(最大长度是32767个字节)
- }
- c:long和long Raw
- {
- pl/sql中 long类型是可变长度字符串。最大长度为32760个字节。类似Oralce中的varchar2。
- long row类型类似于raw数据类型,存储二进制数据或字节串,最大长度是32760个字节。
- }
- d: varchar
- {
- 此类型可容纳可变长度字符串。
- 语法:
- varchar2(maximum_size [char|byte])
- 其中:
- maximum_size:是最大长度。
- char|byte:指定长度为字符数或字节数。
- varchar2类型的子类型包括:
- .. string
- .. varchar
- 子类型的数值范围与基本类型相同。
- }
- >日期时间数据类型
- 日期时间数据类型用于存储日期和时间值。
- a:date
- {
- 用于存储固定长度的日期和时间数据。它支持的日期范围为:从公元前(B.C.E)4712年1月1日到公元(C.E)9999年12月31日。
- date数据类型包括时间。
- 日期函数sysdate返回当前日期和时间。
- 初使化参数NLS_DATE_FORMAT用于设置默认日期格式。
- }
- b: timestamp
- {
- 用于存储日期和时间。是date数据类型的扩展,它存储年,月,日,小时,分钟和秒。
- 日期函数SYSTIMESTAMP返回当前的日期时间信息。
- 语法:
- timestamp [(precision)]
- 其中:
- precision:是精度。它代表秒字段小数部分中的位数。必须使用0到9之间的整数文字。默认为6。
- 初使化参数NLS_TIMESTAMP_FORMAT用于设置默认的timestamp格式。
- }
- >布尔数据类型
- 用于存储逻辑值。
- boolean
- {
- 用于存储逻辑值true,false和null。
- 只允许对boolean变量执行逻辑操作。
- }
- lob类型
- 大对象(lob)数据类型用于存储非结构化数据。大小限于4GB。DBMS_LOB程序包用于操作lob数据。
- >bfile
- 用于将大型二进制对象存储在操作系统文件中。即文件定位器。
- 定位器包含一个目录的别名,用于指定目录的完整路径。
- bfile数据类型的数据是只读的,不能修改。
- 语法:
- bfilename('目录别名','文件名')
- 其中:
- 目录别名使用create directiry语句创建。
- >blob
- 用于将大型二进制对象存储在数据库中。blob数据类型可用于事务处理。
- 例:
- 要在表中插入图象,先使用create directory 目录名 as '系统目录';创建一个目录名。
- 使用grant read on directory 目录名 to 用户;授予读取权限。
- declare
- l_bfile bfile;
- l_blob blob;
- begin
- insert into 表名('blob类型的列名')
- values (EMPTY_BLOB()) return 列名 into l_blob;
- l_bfile :=BFILENAME('目录别名','文件名');
- DBMS_LOB.OPEN(l_bfile,DDMS_LOB.FILE_READONLY);
- DBMS_LOB.LOADFROMFIFE(l_blob,l_bfile,DBMS_LOB.GETLENGTH(l_bfile));
- DBMS_CLOSE(l_bfile);
- commit;
- END;
- /
- *****************************************************
- oracle中插入一个blob数据 (来自Google)
- create or replace directory utllobdir as 'c:/xxx';
- create table bfile_tab (bfile_column BFILE);
- create table t (blob_column BLOB);
-
- declare
- a_blob BLOB;
- a_bfile BFILE := BFILENAME('UTLLOBDIR','BLOB文件名');
- begin
- insert into bfile_tab values (a_bfile)
- returning bfile_column into a_bfile;
- insert into t values (empty_blob())
- returning blob_column into a_blob;
- dbms_lob.fileopen(a_bfile);
- dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
- dbms_lob.fileclose(a_bfile);
- commit;
- end;
-
- >clob
- 用于将大型字符数据存储在数据库中。clob变量中的定位器指向大型字符数据的地址。
- 插入数据到clob列与普通字符串类似。
- 读取clob数据
- DBMS_LOB.READ(clob类型变量,要读的字符数,启始位置(1为最前端),varchar2类型变量)
-
- >nclob
- 将大型 nchar数据存储在数据库中。nclob数据类型同时支持固定宽度字符和可变宽度字符(Unicode字符数据)。nclob类型的使用方 法与clob类似。
- 属性类型
- 属性用于引用变量或数据库列的数据类型,以及表示表中一行的记录类型。
- {
- 优点:
- .. 不需要知道被引用的列或表的具体数据类型。
- .. 如果更改了被引用对象的数据库定义,那么pl/sql在运行时变量的数据类型也会随之更改。
- }
- >%type
- 引用某个变量或数据库列的数据类型来声名变量。
- 语法:
- icode itemfile.itemcode%TYPE;
- 其中;
- icode:是变量名称。
- itemfile.itemcode:是表名.列名。
- **icode的数据类型与itemfile表中itemcode列的数据类型相同。
- >%ROWTYPE
- 提供表中一行的记录类型。
- 例:
- emp_rec emp%ROWTYPE;
- **emp_rec被声明为emp一行的类型。
- <*>逻辑比较
- 运算符 描述
- = 等于
- <>,!= 不等于
- < 小于
- > 大于
- <= 小于等于
- >= 大于等于
- 关系运算符用于比较sql和过程语句中的变量和常量,这些表达式称为布尔表达式。
- 布尔表达式的结果为true,false或null,通常有逻辑运算符and,or和not连接,主要用在条件控制语句中。
- 布尔表达式共有3中类型,即数字布尔型,字符布尔型和日期布尔型。
- <*>控制结构
- pl/sql提供可通过控制结构来控制命令执行的流程。
- >条件控制
- .. if 条件 then
- 执行语句....
- end if;
- .. if 条件 then
- 执行语句1....
- else
- 执行语句2....
- end if;
- .. if 条件 then
- 执行语句1....
- elsif 条件 then
- 执行语句2....
- end if;
- .. case 待比较值
- when 比较值 then 执行语句....
- when 比较值 then 执行语句....
- when 比较值 then 执行语句....
- [else when 执行语句....]
- end case;
- >循环控制
- .. loop
- 执行语句....
- [EXIT<表示强行跳出循环<|EXIT then (条件)<条件不为true就跳出>]
- end loop;
- .. while 条件 loop
- 执行语句....
- end loop;
- .. for counter in [ reverse] value1..value2
- loop
- 执行语句....
- end loop;
- 关键字reverse在for循环中属于可选项。只有在需要对值从大到小执行循环时,才会使用reverse关键字。
- >顺序控制
- >goto语句
- 无条件地将控制权转到标签指定的语句。
- 语法:
- goto 标签名
- 标签定义方法:<<标签名>>
- >null语句
- 什么都不做,只是将控制权转到下一个语句。
- <*>动态sql
- Oracle中的动态sql可以通过本地动态sql命令执行,也可以通过DBMS_SQL程序包来执行。
- 语法:
- execute immediate 动态sql语句字符串
- [into select语句选择的记录值<参数类型>]
- [using 绑定输入参数变量<参数值>]
- <*>错误处理
- >预定义异常
- pl/sql支持的预定义异常:
- 异常 说明
- ACCESS_INTO_NULL 在未初始化对象时出现
- CASE_NOTE_FOUND 在case语句中的选项与用户输入的数据不匹配是出现
- COLLECTION_IS_NULL 在给尚未初始化的表或数组赋值时出现
- CURSOR_ALREADY_OPEN 在用户试图打开已经打开的游标是出现,在重先打开游标前必须先将其关闭
- DUP_VAL_ON_INDEX 在用户试图将重复的值存储在使用唯一索引的数据库列中时出现。
- INVALID_CURSOR 在执行非法的游标运算是出现。
- INVALIE_NUMBER 在将字符串转换为数字时出现。
- LOGIN_DENIED 在输入用户名或密码无效时出现
- NO_DATA_FOUND 在表中不存在请求的行是出现。
- STORAGE_ERROR 在内存损坏或pl/sql耗尽内存时出现。
- TOO_MANY_ROWS 在执行select into语句后返回多行时出现。
- VALUE_ERROR 在产生大小限制错误时出现。
- ZERO_DIVIDE 以零做除数时出现。
- 使用异常的语法:
- exception 异常名称 then
- 执行异常处理语句....
- >用户自定义异常
- 语法:
- 声明: 自定义异常名称 EXCEPTION;
- 使用raise语句显示引发:raise 自定义异常名称
- 处理: exception 自定义异常名称 then
- 执行异常处理语句....
- >引发应用程序错误
- 过程RAISE_APPLICATION_ERROR用于创建用户定义的错误信息。
- 语法:
- RAISE_APPLICATION_ERROR(error_number,error_message)
- 其中:
- error_number:指定的异常编号,必须在-20000和-20999之间的负整数。
- error_message:异常指定的消息文本。长度可答2048字节,错误消息是与error_number表示关联的文本。
- 十一.游标管理
- 游标是构建在pl/sql中,用来查询数据,获取记录集合的指针。
- <*>静态游标
- 静态游标是在编译时知道其select语句的游标。
- >隐式游标
- Oracle预定义了一个sql的隐式游标,通过检查隐式游标的属性可以获取与最近执行的sql语句相关的信息。
- 隐式游标的属性:
- .. %found:在dml语句影响一行或多行时,%found属性在返回true。
- .. %notfound:与%found的作用正好相反,如果没有影响任何行,则返回true。
- .. %rowcount:返回dml语句影响的行数,如果没有影响行则返回0。
- .. %isopen:返回游标是否已经打开的值。在执行sql语句之后,Oracle自动关闭sql游标,所以隐式游标的%isopen属性始终为false。
- >显示游标
- 显示游标是由用户显示声明的游标。根据在游标中定义的查询,查询返回的行集合可以包含零行或多行,这些行称为活动集。游标将指向活动集中的当前行。
- 显示游标的标准操作过程:
- 1.声明游标
- 2.打开游标
- 3.从游标中获取记录
- 4.关闭游标
- 显示游标在declare部分的声明语法:
- cursor cursor_name [(parameter[,parameter..])]
- [return return_type] is select_statement;
- 其中:
- cursor_name:是游标的名称。
- parameter:用于为游标指定输入参数。
- return_type:定义游标提取的行的类型。
- select_statement:指定游标的查询语句。
- 在声明游标后可以用下列语句控制游标:
- open:打开游标。
- fetch: 从游标中提取行。
- close: 关闭游标。
- 显示游标同样有隐式游标的属性并起相同。
- 使用显示游标删除或更改:
- 如果处理过程中需要删除或更新行,在定义游标时必须使用select...for update语句,而在执行delete或update时使用where current of 字句指定游标的当前行。
- **提示:select语句必须只包括一个表,而且delete和update语句只有在打开游标并提取特定行之后才能使用。