Oracle常用函数(2)

间在内的。

主要的日期格式字符的含义

AM、PM:上午、下午,如:08 AM

D:数字表示的星期(1～7),如:1,2,3,4,5,6,7

DD:数字表示月中的日期(1～31),如:1,2,3,?,31 MM:两位数的月份,如:01,02,?,12

Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003 RR:解决Y2K问题的年度转换

DY:简写的星期名,如:MON,TUE,FRI,?

DAY:全拼的星期名,如:MONDAY,TUESDAY,? MON:简写的月份名,如:JAN,FEB,MAR,?

MONTH:全拼的月份名,如:JANUARY,FEBRUARY,? HH、HH12:12小时制的小时(1～12),如:1,2,3,?,12 HH24:24小时制的小时(0～23),如:0,1,2,?,23 MI:分(0～59),如:0,1,2,?,59 SS:秒(0～59),如:0,1,2,?,59 ,./-;: 原样显示的标点符号

'TEXT':引号中的文本原样显示,如:TEXT

将日期转换成带时间和星期的字符串并显示。 执行以下查询： Sql代码

1. SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

结果为： Sql代码

1. TO_CHAR(SYSDATE,'YYYY-MM-DD HH24

2. ---------------------------------------------------------- 3. 2004-02-07 15:44:48 下午 星期六

说明：该语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。“YYYY”为4位的年份，“MM”为两位 的月份，“DD”为两位的日期，“HH24”表示显示24小时制的小时,“MI”表示显示分钟，“SS”表示显示秒，“AM”表示显示上午或下午(本例中 为下午)，“DY”表示显示星期。“-”、“:”和空格原样显示，用于分割日期和时间。转换出来的系统时间为：2004年2月7日(星期六)下午15点 44分48秒。

还可以按其他的格式显示。以下查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。

将日期显示转换成中文的年月日。 输入并执行查询： Sql代码

1. SELECT TO_CHAR(sysdate,'YYYY\年\月\日\

执行结果为： Sql代码

1. TO_CHAR(SYSDAT

2. ------------------------- 3. 2003年11月18日

说明：双引号中的中文字“年”、“月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。

时间显示的大小写。 步骤1：执行以下查询： Sql代码

1. SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

结果为： Sql代码

1. SYSDATE TO_CHAR(SYSDATE,'YYYYSP')

2. ------------- ---------------------------------------------- 3.

07-2月 -04 two thousand four

步骤2：执行以下查询： Sql代码

1. SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

结果为： Sql代码

1. SYSDATE TO_CHAR(SYSDATE,'YYYYSP')

2. -------------- ----------------------------------------------- 3. Two Thousand Four

5.数值转换符

9:代表一位数字，如果是正数，前面是空格，如果是负数，前面是?号,如:9999 0:代表一位数字，在相应的位置上如果没有数字则出现0 0000 ,:逗号，用作组分隔符,如:99,999 .:小数点，分隔整数和小数,如:999.9 $:$货币符号,如:$999.9 L:本地货币符号,如:L999.99

FM:去掉前后的空格,如:FM999.99 EEEE:科学计数法,如:9.9EEEE S:负数符号?放在开头,如:S999.9

将数值转换成字符串并按新格式显示。 执行以下查询： Sql代码

1. SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp 2. WHERE ename = 'SCOTT';

结果为： Sql代码

1. SALARY

2. ------------ 3. $4,000

说明：格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字，“99，999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗 号。如果实际数值位数不足5位，则只显示实际位数，如4000实际位数为4位，则只显示4位。如果实际位数超过5位，则会填充为#号。

6.其他函数

nvl:空值转换函数,如:nvl(null, '空'),结果:空

decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男

userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35 least:返回参数的最小值,如:least(20,35,18,9),结果:9

空值的转换

如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。

使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。

如下3个函数，分别用新内容代替字段的空值： nvl(comm, 0)：用0代替空的Comm值。

nvl(hiredate, '01-1月-97')：用1997年1月1日代替空的雇佣日期。 nvl(job, '无')：用“无”代替空的职务。 使用nvl函数转换空值。 执行以下查询： Sql代码

1. SELECT ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM emp;

结果为：

Sql代码

1. ENAME NVL(JOB,'N NVL(HIREDA NVL(COMM,0)

2. ---------------- ------------------- ------------------ -------------------

3. SMITH CLERK 17-12月-80 0 4. ALLEN SALESMAN 20-2月 -81 300

说明：本例中，空日期将显示为“01-1月-97”，空职务显示为“无”，空津贴将显示为0。

7.最大、最小值函数

greatest返回参数列表中的最大值，least返回参数列表中的最小值。 这两个函数的参数是一个表达式列表，按表达式列表中的第一个表达式的类型对求值后的表达式求得最大或最小值。对字符的比较按ASCII码的顺序进行。如果表达式中有NULL，则返回NULL。

比较字符串的大小，返回最大值。 执行以下查询： Sql代码

1. SELECT greatest('ABC','ABD','abc', 'abd') FROM dual;

执行结果为： Sql代码 1. GRE

2. ------ 3. abd

说明：在上述四个字符串中，大小关系为abd>abc>ABD>ABC。在ASCII码表中，排在后边的字符大，小写字母排在大写字母之后。字符串的比较原则是，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系。

8.创建临时表

declare P_SQLTEXT1 varchar2(1000); begin

P_SQLTEXT1 := 'CREATE GLOBAL TEMPORARY TABLE zz_GetMonthAccumulation (

oildomid VARCHAR2(36), month number(18,0), year number(18,0) ) ON COMMIT DELETE ROWS';

EXECUTE IMMEDIATE P_SQLTEXT1; end;

9. 创建表值函数

在Oracle的函数中,返回表类型

在SQL Server中有表变量,可以在function中方便地返回,习惯SQL Server或者需要把脚本从SQL Server转到Oracle中的朋友可以都会碰到这个问题. Oracle的function中怎么返回表变量?

太晚了,过多的理论知识就不说了,下面简单地说实现吧!.. 1、创建表对象类型。

在Oracle中想要返回表对象，必须自定义一个表类型，如下所示：

create or replace type t_table is table of number;

上面的类型定义好后，在function使用可用返回一列的表，如果需要多列的话，需要先定义一个对象类型。然后把对象类型替换上面语句中的number； 定义对象类型：

create or replace type obj_table as object (

id int,

name varchar2(50) )

修改表对象类型的定义语句如下：

create or replace type t_table is table of obj_table;

2、 创建演示函数

在函数的定义中,可以使用管道化表函数和普通的方式,下面提供两种使用方式的代码: 1)、管道化表函数方式：

create or replace function f_pipe(s number) return t_table pipelined as

v_obj_table obj_table; begin

for i in 1..s loop

v_obj_table := obj_table(i,to_char(i*i)); pipe row(v_obj_table); end loop; return;

end f_pipe;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Oracle常用函数(2)在线全文阅读。