Oracle常用函数记录

Oracle函数

--schema:hcf

--不带任何参数

http://www.cnblogs.com/wuyisky/archive/2010/05/11/oracle_function.html

--1.  单行函数

--常用的单行函数有：

--字符函数：对字符串操作，字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

ASCII(X)  --返回字符X的ASCII码

SELECT ASCII('a') FROM dual;

CONCAT(X,Y)  --连接字符串X和Y

SELECT CONCAT('Hello','world') FROM dual;

INSTR(X,STR[,START][,N)       ---从X中查找str，可以指定从start开始，也可以指定从n（x中的字符）开始，输出n是x中的第几位

SELECT INSTR('Hello world','or') FROM dual;

LENGTH(X)       ---返回X的长度

SELECT LENGTH('Hello') FROM dual;

LOWER(X)      ---X转换成小写

SELECT LOWER('Hello') FROM dual;

UPPER(X)     ---X转换成大写

SELECT UPPER('hello') FROM dual;

LTRIM(X[,TRIM_STR])     ---把X的左边截去trim_str字符串，缺省截去空格

SELECT LTRIM('=Hello=','=') FROM dual;

TRIM([TRIM_STR  FROM]X)      --把X的两边截去trim_str字符串，缺省截去空格

SELECT TRIM('='FROM'=Hello=') FROM dual;

REPLACE(X,old,new)       --在X中查找old，并替换成new

SELECT REPLACE('ABCDE','CD','AAA')FROM dual;

SUBSTR(X,start[,length])        --返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾

SELECT SUBSTR('ABCDE',2,3) FROM dual;

--数字函数：数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。

ABS(X)    --X的绝对值

select ABS(-3) from dual;

ACOS(X)   --X的反余弦

select ACOS(1) from dual;

COS(X)   --余弦

select COS(1) from dual;

CEIL(X)   --大于或等于X的最小值

select CEIL(5.4) from dual;

FLOOR(X)     --小于或等于X的最大值

select FLOOR(5.8) from dual;

LOG(X,Y)    ---X为底Y的对数

select LOG(2，4) from dual;

MOD(X,Y)    --X除以Y的余数

select MOD(8，3) from dual;

POWER(X,Y)    ---X的Y次幂

select POWER(2，3) from dual;

ROUND(X[,Y])    --X在第Y位四舍五入

select ROUND(3.456，2) from dual;

SQRT(X)     --X的平方根

select SQRT(4) from dual;

TRUNC(X[,Y])   --X在第Y位截断

select TRUNC(3.456，2) from dual;

1.  ROUND(X[,Y])，四舍五入。

在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

2.  TRUNC(x[,y])，直接截取，不四舍五入。

在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

--日期函数：日期函数对日期进行运算。常用的日期函数有：

--ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

--LAST_DAY(d)，返回指定日期当月的最后一天

SELECT SYSDATE,last_day(SYSDATE) FROM dual;

--ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。

如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。

如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。

默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

--EXTRACT(fmt FROM d)，提取日期中的特定部分

SELECT SYSDATE "date",EXTRACT(YEAR FROM SYSDATE)"year",

    EXTRACT(MONTH FROM SYSDATE)"month",

    EXTRACT(DAY FROM SYSDATE)"day",

    EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",

    EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",

    EXTRACT(SECOND FROM SYSTIMESTAMP)"second"

FROM dual;

--转换函数，转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有：

--TO_CHAR(d|n[,fmt])，把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

--TO_DATE(X,[,fmt])，把一个字符串以fmt格式转换成一个日期类型

--TO_NUMBER(X,[,fmt]),把一个字符串以fmt格式转换为一个数字

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

--其它单行函数

--NVL(X,VALUE),如果X为空，返回value，否则返回X

select id,nvl(addr,'上海') from addr_c where id=3;

--NVL2(x,value1,value2),如果x非空，返回value1，否则返回value2

select id,nvl2(addr,'上海','茂名') from addr_c where id=1;

select id,nvl2(addr,'上海','茂名') from addr_c where id=3;

--聚合函数，聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

--sum函数

SELECT SUM(sal) FROM emp;

--AVG函数下的分组查询

 SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO; 

--MIN、MAX ，最小值、最大值，MIN(表达式)、MAX(表达式)

--COUNT，数据统计，COUNT（表达式）

--自定义函数

--没有参数的函数

create or replace function get_user return varchar2 is

  v_user varchar2(50);

begin

  select username into v_user from user_users;

  return v_user;

end get_user;  

--测试  方法一

select get_user from dual;  

--方法二

SQL> var v_name varchar2(50)

SQL> exec :v_name:=get_user;

PL/SQL 过程已成功完成。

SQL> print v_name  

方法三

SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user); 

--带有IN参数的函数

create or replace function get_empname(v_id in number) return varchar2 as

  v_name varchar2(50);

begin

  select name into v_name from employee where id = v_id;

   return v_name;

exception

  when no_data_found then

    raise_application_error(-20001, '你输入的ID无效！');

end get_empname;

create table employee(id int,name varchar2(50));

-- 建立无参数的函数

CREATE OR REPLACE FUNCTION cur_datetime

RETURN VARCHAR2

IS

BEGIN

RETURN TO_CHAR(sysdate,

'YYYY"年"MM"月"DD"日"HH24″时"MI"分"SS"秒"');

END;  

--建立带有输入参数的函数

CREATE OR REPLACE FUNCTION get_sal(name VARCHAR2)

RETURN NUMBER

AS

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name);

RETURN v_sal;

END;  

-- 建立带有输出参数的函数

CREATE OR REPLACE FUNCTION get_info

(eno NUMBER,title OUT VARCHAR2) RETURN VARCHAR2

AS

name emp.ename%TYPE;

BEGIN

SELECT ename,job INTO name,title FROM emp

WHERE empno=eno;

RETURN name;

END;  

-- 建立带有输入输出参数的函数

CREATE OR REPLACE FUNCTION get_upd_info

(eno NUMBER,sal_chg IN OUT NUMBER) RETURN VARCHAR2

AS

name emp.ename%TYPE;

BEGIN

UPDATE emp SET sal=sal+sal_chg WHERE empno=eno

RETURNING ename,sal INTO name,sal_chg;

RETURN name;

END;  

-- 建立结果缓存函数

CREATE OR REPLACE FUNCTION get_name1(no VARCHAR2)

RETURN NUMBER RESULT_CACHE RELIES_ON(emp)

AS

v_name emp.ename%TYPE;

BEGIN

SELECT ename INTO v_name FROM emp WHERE empno=no;

RETURN v_name;

END;  

--调用无参数的函数

BEGIN

dbms_output.put_line(SYSDATE);

END;  

--调用带有输入参数的函数

BEGIN

dbms_output.put_line('工资:'||get_sal('&name'));

END;  

--调用带有输出参数的函数

DECLARE

v_name emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

v_name:=get_info(&eno,v_job);

dbms_output.put_line('姓名:'||v_name||',岗位:'||v_job);

END;  

--调用带有输入输出参数的函数

DECLARE

v_empno emp.empno%TYPE;

v_name emp.ename%TYPE;

v_salchg emp.sal%TYPE;

BEGIN

v_empno:=&eno;

v_salchg:=&incre;

v_name:=get_upd_info(v_empno,v_salchg);

dbms_output.put_line('姓名:'||v_name||',新工资:'||v_salchg);

END;  

--使用位置传递为参数传递变量和数据

SELECT get_sal('&name') 工资 FROM dual;

-- 19-11:使用名称传递为参数传递变量和数据

VAR salary NUMBER

EXEC :salary:=get_sal(name=>'&name')

-- 19-12:使用组合传递为参数传递变量和数据

VAR name VARCHAR2(10)

VAR sal_chg NUMBER

EXEC :sal_chg:=200

EXEC :name:=get_upd_info(&eno,:sal_chg)

PRINT name sal_chg  

--在sql语句中调用pl/sql函数

SELECT get_sal(name=>'scott') salary FROM dual;

--使用异常处理

CREATE OR REPLACE FUNCTION get_sal1(name VARCHAR2)

RETURN NUMBER

AS

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal FROM emp

WHERE upper(ename)=upper(name);

RETURN v_sal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');

END;

/

-- 使用纪录类型作为返回类型

CREATE OR REPLACE FUNCTION get_info1

(eno NUMBER) RETURN emp%ROWTYPE

IS

emp_record emp%ROWTYPE;

BEGIN

SELECT * INTO emp_record FROM emp WHERE empno=eno;

RETURN emp_record;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');

END;

/

DECLARE

emp_record emp%ROWTYPE;

BEGIN

emp_record:=get_info(&eno);

dbms_output.put_line('姓名:'||emp_record.ename||',部门号:'||emp_record.deptno);

END;

/

--使用集合类型作为返回类型

CREATE OR REPLACE TYPE ename_table_type IS TABLE OF VARCHAR2(10);

/

CREATE OR REPLACE FUNCTION get_name

(dno NUMBER) RETURN ename_table_type IS

ename_table ename_table_type;

BEGIN

SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=dno;

RETURN ename_table;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20099,'该部门不存在');

END;

/  ---没创建成功

DECLARE

ename_table ename_table_type;

BEGIN

ename_table:=get_name(&dno);

FOR i IN 1..ename_table.COUNT LOOP

dbms_output.put_line('姓名：'||ename_table(i));

END LOOP;

END;

/

--删除函数

DROP FUNCTION get_name;

--:显示编译错误

SHOW ERRORS

--确定函数状态

SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='FUNCTION';

-- 编译函数

ALTER FUNCTION get_info COMPILE;

--查看函数代码

SELECT text FROM user_source WHERE name='GET_INFO';

http://blog.csdn.net/smartsmile2012/article/details/8242841
巴特西

Oracle常用函数记录

Oracle函数

最新文章

热门文章
