PL/SQL函数

 PL/SQL概述:

　　PL/SQL是Oracle对SQL规范的扩展，是一种块结构语言，即构成一个PL/SQL程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套子块。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：
　　DECLARE
　　---说明
　　BEGIN
　　---语句序列
　　EXCEPTION
　　---例外处理程序
　　END；
　　它有以下优点:
　　l 支持SQL;
　　l 生产率高;
　　l 性能好;
　　l 可移植性;
　　l 与ORACLE集成.
　　PL/SQL机制可执行过程性语句，而将SQL语句发送到ORACLE服务器上的SQL语句执行器。在ORACLE预编译程序，永久地存储在数据库中，准备执行。
　　在通常的开发中采用存储过程,函数,包来实现商业逻辑算法和一些需要大量计算的工作．存储过程,函数,包是Oracle数据库的一种对象，是一种带名的PL/SQL过程程序块，它在创建后，被数据库服务器进行语法和句法分析，以编译了的形式存储在数据库中，可以被有权用户在任何需要的地方调用。当客户端应用程序调用时，只需发送一条调用命令，数据库服务器就会执行该过程。与一般的PL/SQL块主要的不同是：无需在网上传送大量的源程序代码，只传送一条调用命令，这就大大降低了网络通信的负担；而且只在刚创建时分析编译一次，每次调用直接执行编译了的代码，因此运行速度较快。
　　　　在实际开发时，对于具有共同特性的功能模块最好使用存储过程,函数,包，调用时通过使用不同的实际参数值来实现某一具体的处理。可大大提高系统的运行性能。

　　缺点或不足:
    这跟我们的PPT有出入,其实大家调试的过程中也知道,psql的查错定位并不准确!

　　维护和修改不容易（如果初期设计不够好的话．）
　　较大系统中多人团队开发时，如果结构层次不清楚，会产生包之间互相调用的现象容易造成死锁．增加调试的困难程度．
　　修改处在下层的包会是一件很痛苦的事。

PL/SQL常用函数

一下一些都是比较常用的函数.不是用来背的,当要用的时候就来查一下!

2.常用函数
　　 1)处理字符的函数
　　 ||
　　 CONCAT ---并置运算符。
　　 格式∶CONCAT(STRING1, STRING2)
　　 例：’ABC’|| ’DE’=’ABCDE’
　　 CONCAT(‘ABC’,’DE’) =’ABCDE’
　　
　　 ASCII---返回字符的ASCII码。
　　 例：ASCII(‘A’) = 65
　　
　　 CHR---返回指定ASCII码的字符。
　　 例：CHR(65) = ‘A’
　　
　　 INSTR---搜索子串位置
　　格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]])
　　 例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
　　
　　 INITCAP---将字符串每个单词首字母均变为大写
　　例: INITCAP(‘this is a test’)=’ This Is A Test’’
　　
　　 LENGTH----计算串长
　　 格式∶ LENGTH(string)
　　
　　 RPAD,LPAD---右填充、左填充。默认为填充空格。
　　 格式: RPAD(字符串 , 字符个数 , 填充字符)
　　例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
　　
　　 LTRIM,RTRIM-----左右截断。默认为删除空格。
　　格式∶ LTRIM(STRING[,’SET’])
　　例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
　　
　　 LOWER----将字符串转换为小写
　　 格式∶LOWER(string)
　　
　　UPPER---将字符串转换为大写
　　 格式∶UPPER(string)
　　
　　SUBSTR----提取子串。START为正数时从左开始 、为负数时从右开始
　　格式∶ SUBSTR(STRING , START [ , COUNT])
　　例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
　　
　　 REPLACE---搜索指定字符串并替换
　　 格式∶REPLACE(string , substring , replace_string)
　　 例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
　　
　　TRIM---删除字符串前缀或尾随字符
　　格式∶TRIM( [LEADING | TRAILING ｜BOTH] [ trimchar FROM ] string)
　　 LEADING---删除前缀字符
　　 TRAILING---删除后缀字符
　　 BOTH---前后缀字符均删除(默认方式)
　　 Trimchar---指定删除的字符
　　
　　注：INSTR,LENGTH,SUBSTR加Ｂ时针对字节．
　　2)处理数字的函数
　　LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
　　格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
　　例∶ LEAST(1,2,5,-10,9)= -10
　　
　　SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
　　格式∶SIGN(value)
　　
　　 CELL---返回大于等于特定值的最小整数
　　 格式∶CELL(value)
　　 例∶ CELL(-10,9)= -10
　　3）处理日期
　　 SYSDATE---系统时间。精确至秒
　　
　　 ADD_MONTHS—加减月份。numvalue为负数时减去相应月份
　　 格式: ADD_MONTHS (data1,numvalue)
　　
　　MONTHS_BETWEEN---返回两日期之间的月数,当data1　　 格式: MONTHS_BETWEEN(data1,data2)
　　
　　 LAST_DAY---返回指定日期的最后一天。
　　 格式∶LAST_DAY(date)
　　
　　 TRUNC---将日期按照format格式截短，缺省为DD(不是四舍五入)
　　　　格式：TRUNC（date, format）
　　例∶
　　 TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
　　
　　　 4）分组函数
　　　　　　　返回基于多个行的单一结果．
　　　　　　　常用函数：
　　 AVG---求平均值
　　 COUNT---返回查询的行数
　　 MAX---返回查询列的最大值
　　 MIN---返回查询列的最小值
　　 SUM---返回查询列的总和
　　 MAX, MIN常与GROUP BY配套使用
　　5）转换函数
　　 TO_DATE---转换字符串为日期型
　　格式∶ TO_DATE(STRING[,’FORMAT’])
　　
　　TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一．其FORMAT格式多种多样
　　格式∶TO_CHAR(DATE [,’FORMAT’])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　P836 日期格式元素
　　
　　 TO_NUMBER---转换字符串为数字
　　 格式∶TO_NUMBER(string [ , format])
　　FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
　　
　　
　　6)其他
　　DECODE---IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
　　格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
　　例∶ DECODE(VALUE,1,100,2,300,500)
　　当VALUE=1时返回100
　　当VALUE=2时返回300
　　否则返回500
　　
　　DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
　　当VALUE<100时返回-10
　　当VALUE>100时返回10
　　当VALUE=100时返回0
　　
　　 SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
　　SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
　　SUM(DECODE(EST_INT_KEY,77773,1,0)) C
　　FROM PMS_BLK
　　
　　 NVL---空值置换
　　 格式: NVL(value,替换值)
　　例: NVL(value,’nullvalue’)当value为NULL值时返回nullvalue否则返回value的值
　　
　　VSIZE---以字节为单位返回数据类型尺寸
　　格式∶VSIZE(数据类型)
　　例∶ VSIZE(SYSDATE)=8
　　
　　USER---得到当前用户名
　　
顺便把minus加上,^_^
　　ROWNUM--- oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
　　例∶ select rownum,month,sell from sale where rownum=1
　　 或select rownum,month,sell from sale where rownum<2(返回第一条记录)
　　 返回rownum在4—10之间的记录
　　select rownum,month,sell from sale where rownum<10
　　minus
　　select rownum,month,sell from sale where rownum<5;
　　
　　 以下均错误:
　　select rownum,month,sell from sale where rownum=2(1以上)
　　select rownum,month,sell from sale where rownum>2
　　
　　ROWID---数据行在物理磁盘上的物理地址，Oracle通过ROWID来定位数据的具体位置，这是存取表中数据的最快的方法
　　
　　RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
　　SQLCODE---返回ORACLE错误号。
　　SQLERRM---返回ORACLE错误信息。