oracle数据库常用函数

来源：互联网发布：java aes对称加密算法编辑：程序博客网时间：2024/05/14 06:31

（一）to_char

把日期或数字转换为字符串

使用TO_CHAR函数处理数字：TO_CHAR(number, '格式')
例：TO_CHAR(salary,’$99,999.99’);
使用TO_CHAR函数处理日期：TO_CHAR(date,’格式’);

SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DD')
-----------------------------
2016-01-27

SQL> select ename,to_char(hiredate,'YYYY-MM-DD hh24:mi:ss') from emp;

ENAME      TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------
SMITH      1980-12-17 00:00:00
ALLEN      1981-02-20 00:00:00
WARD       1981-02-22 00:00:00
JONES      1981-04-02 00:00:00
MARTIN     1981-09-28 00:00:00
BLAKE      1981-05-01 00:00:00
CLARK      1981-06-09 00:00:00
SCOTT      1987-04-19 00:00:00
KING       1981-11-17 00:00:00
TURNER     1981-09-08 00:00:00
ADAMS      1987-05-23 00:00:00
JAMES      1981-12-03 00:00:00
FORD       1981-12-03 00:00:00
MILLER     1982-01-23 00:00:00

SQL> select to_char(sysdate,'month') from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------
1月

SQL> select ename,sal, to_char(sal,'$99,999.00') salary from emp ;

ENAME            SAL SALARY
---------- --------- -----------
SMITH         800.00     $800.00
ALLEN        1600.00   $1,600.00
WARD         1250.00   $1,250.00
JONES        2975.00   $2,975.00
MARTIN       1250.00   $1,250.00
BLAKE        2850.00   $2,850.00
CLARK        2450.00   $2,450.00
SCOTT        3000.00   $3,000.00
KING         5000.00   $5,000.00
TURNER       1500.00   $1,500.00
ADAMS        1100.00   $1,100.00
JAMES         950.00     $950.00
FORD         3000.00   $3,000.00
MILLER       1300.00   $1,300.00

实际位数超过格式规定位数时

SQL> select ename,sal,to_char(sal,'$999.00') from emp;

ENAME            SAL TO_CHAR(SAL,'$999.00')
---------- --------- ----------------------
SMITH         800.00 $800.00
ALLEN        1600.00 ########
WARD         1250.00 ########
JONES        2975.00 ########
MARTIN       1250.00 ########
BLAKE        2850.00 ########
CLARK        2450.00 ########
SCOTT        3000.00 ########
KING         5000.00 ########
TURNER       1500.00 ########
ADAMS        1100.00 ########
JAMES         950.00 $950.00
FORD         3000.00 ########
MILLER       1300.00 ########

（二）to_date & to_number

TO_DATE
使用TO_DATE函数将字符转换为日期
TO_DATE(char[, '格式'])

TO_NUMBER
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char[, '格式'])

(三) nvl

NVL( string1, replace_with)

如果string1为NULL，则返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

SQL> select nvl('abc',2) from dual;

NVL('ABC',2)
------------
abc

SQL> select nvl(null,'me')from dual;

NVL(NULL,'ME')
--------------
me

SQL> select ename ,sal,sal+nvl(comm,0) from emp;

ENAME            SAL SAL+NVL(COMM,0)
---------- --------- ---------------
SMITH         800.00             800
ALLEN        1600.00            1900
WARD         1250.00            1750
JONES        2975.00            2975
MARTIN       1250.00            2650
BLAKE        2850.00            2850
CLARK        2450.00            2450
SCOTT        3000.00            3000
KING         5000.00            5000
TURNER       1500.00            1500
ADAMS        1100.00            1100
JAMES         950.00             950
FORD         3000.00            3000
MILLER       1300.00            1300

(四)nvl2

NVL2(expr1,expr2,expr3)

如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值

SQL> select ename,sal,comm, sal+nvl2(comm,comm,0) from emp; --不为空,返回第二个参数值,为空返回第三个参数值

ENAME            SAL      COMM SAL+NVL2(COMM,COMM,0)
---------- --------- --------- ---------------------
SMITH         800.00                             800
ALLEN        1600.00    300.00            1900
WARD         1250.00    500.00           1750
JONES        2975.00                           2975
MARTIN       1250.00   1400.00          2650
BLAKE        2850.00                           2850
CLARK        2450.00                           2450
SCOTT        3000.00                          3000
KING         5000.00                             5000
TURNER       1500.00      0.00            1500
ADAMS        1100.00                            1100
JAMES         950.00                             950
FORD         3000.00                            3000
MILLER       1300.00                            1300

(五) group by

常与聚集函数一起使用

1.求Emp表中各个部门的工资之和
2.列出部门的名称

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

DEPTNO JOB         SUM(SAL)
------ --------- ----------
    20 CLERK                1900
    30 SALESMAN        5600
    20 MANAGER         2975
    30 CLERK               950
    10 PRESIDENT       5000
    30 MANAGER         2850
    10 CLERK               1300
    10 MANAGER         2450
    20 ANALYST           6000

3.having

group by的条件查询

4.rollup

group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。

Group by rollup(A ,B)

产生的分组种数：3种；

第一种：group by A,B

第二种：group by A

第三种：group by NULL

（null即没有分组，也就是所有数据做一个统计。例如聚合函数是SUM的话，那就是对所有满足条件的数据进行求和。)

Group by rollup(A ,B,C)

产生的分组种数：4种；

第一种：group by A,B,C

第二种：group by A,B

第三种：group by A

第四种：group by NULL

SQL> select deptno ,job,avg(sal) from emp group by rollup(deptno,job);

DEPTNO JOB         AVG(SAL)
------ --------- ----------
    10 CLERK               1300
    10 MANAGER          2450
    10 PRESIDENT        5000
    10                          2916.66666
    20 CLERK                  950
    20 ANALYST            3000
    20 MANAGER         2975
    20                             2175
    30 CLERK                   950
    30 MANAGER           2850
    30 SALESMAN       1400
    30                        1566.66666
                               2073.21428

5.cube

带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。

例：Group by cube(A ,B,C)

产生的分组种数：8种；

第一种：group by A,B,C

第二种：group by A,B

第三种：group by A,C

第四种：group by B,C

第五种：group by C

第六种：group by B

第七种：group by A

第八种：group by NULL

SQL> select deptno ,job,avg(sal) from emp group by cube(deptno,job);

DEPTNO JOB         AVG(SAL)
------ --------- ----------
                 2073.21428
       CLERK         1037.5
       ANALYST         3000
       MANAGER   2758.33333
       SALESMAN        1400
       PRESIDENT       5000
    10           2916.66666
    10 CLERK           1300
    10 MANAGER         2450
    10 PRESIDENT       5000
    20                 2175
    20 CLERK            950
    20 ANALYST         3000
    20 MANAGER         2975
    30           1566.66666
    30 CLERK            950
    30 MANAGER         2850
    30 SALESMAN        1400

(六) SQL PLUS命令

SQL> set echo on—————————————————设置运行命令是是否显示语句
SQL> set feedback on———————————————-设置显示“已选择XX行”
SQL> set colsep | —————————————————设置列与列之间的分割符号
SQL> set pagesize 10———————————————–设置每一页的行数
SQL> SET SERVEROUTPUT ON——————————-设置允许显示输出类似dbms_output.putline
SQL> set heading on————————————————设置显示列名
SQL> set timing on————————————————–设置显示“已用时间：XXXX”
SQL> set time on—————————————————–设置显示当前时间
SQL> set autotrace on———————————————–设置允许对执行的sql进行分析

SQL> set timing on
SQL> select * from dual;

DUMMY
-----
X

Executed in 0.047 seconds

SQL> set time on
0:15:07 SQL> select * from dual;

1. describe 命令

查看表的结构

格式：desc [tablename]

2 edit /ed

打开编辑器

3 run /r 或者 /

执行ed里内容

4.save

保存当前session最近的sql语句至指定的文件中

C>save g:\a.sql;

如果已经存在此文件要加 replace
save h:\my.sql replace;

5 get

get g:\a.sql

补允:
@ g:\b.sql;也可以执行一个sql块

6.spool

sqlplus屏幕的文件输入输出命令

输入：c> spool g:\a.txt;

输出：

SQL> spool on
Started spooling to C:\Users\Administrator\Desktop\on.lst

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980/12/17     800.00               20
7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
7839 KING       PRESIDENT       1981/11/17    5000.00               10
7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
7900 JAMES      CLERK      7698 1981/12/3      950.00               30
7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected

SQL> spool off
Stopped spooling to C:\Users\Administrator\Desktop\on.lst

7.Formatting Columns

SQL> TTITLE abc      --添加页眉“abc”
SQL> BTITLE def      --添加页脚“def”
SQL> TTITLE OFF      --取消页眉显示

【本段来自CSDN博客，转载请标明出处：http://blog.csdn.net/qiuliangjun/archive/2009/05/05/4152402.aspx】

1、HEADING--改变字段名
SQL> COLUMN id HEADING 'XX|YY' --使用'|'可将列名显示为两行

2、FORMAT--改变字符长度 & 格式化数值
SQL> COLUMN id FORMAT a20 --此字段长20个字符，只能针对字符，若为数字则无法正常显示
SQL> COLUMN id FORMAT $999.00 --前缀$，小数点前3位，小数点后2位四舍五入

3、JUSTIFY--改变字段名显示位置

SQL> COLUMN id JUSTIFY center --居中显示，默认的都是靠右(r )显示

4、NOPRINT--不显示

SQL> COLUMN id NOPRINT          --不显示出来，可用PRINT设置回来

5、NULL--设置NULL值显示
SQL> COLUMN COMM NULL 0.00      --将NULL值设置位0.00 注意必须加上COMM

6、WRAPPED--设置回绕方式
SQL> COLUMN id FORMAT a5        --设置长度为5
SQL> COLUMN id WRAPPED         --表示直接按长度回绕
SQL> COLUMN id WORD_WRAPPED   --按单词回绕
SQL> COLUMN id TRUNCATED      --直接按长度截断

7、COLUMN--显示当前列所有属性
SQL> COLUMN id                  --后面不加列名时显示所有的COLUMN

8、OFF|ON--设置某一字段关闭

SQL> COLUMN id OFF

9、CLEAR--清空所有字段属性
SQL> CLEAR COLUMNS

10.set pagesize 100 和set linesize 1000;

11.替代变量

0 0