Oracle SQLPlus运行环境、Oracle常用系统函数实例讲解

1.简介SET命令

set命令设置SQL*Plus的运行环境，set命令的语法格式为：

SET system_variable value

参数system_variable表示变量名，参数value表示变量值。

如：set time on set time on是临时性的操作退出登录重进后会恢复

2.使用SET命令设置运行环境

PAGESIZE变量 set pagesize能更改一页中的行数 通过show pagesize显示行数

NEWPAGE变量

LINESIZE变量

PAUSE变量

NUMFORMAT变量

ORACLE字符类函数

1 ASCII(arg1)

返回参数arg1的十进制数字表示.如果数据库设置为ASCII,则采用的是ASCII码字符.如果设置为EBCDIC,则采用的EBCDIC字符

select ascii('A') from dual;

ASCII('A')

----------

65

2 CHR(arg1,[using nchar_cs])

返回由参数arg1的代码所指定的字符.所返回的字符依赖于oracle所使用的底层字符编码设置.

select chr(65) from dual;

CHR(65)

-------

A

3 CONCAT(arg1,arg2)

返回arg1与arg2的拼接结果.等同于arg1||arg2

select concat('ABC','DEF') from dual;

CONCAT('ABC','DEF')

-------------------

ABCDEF

4 INITCAP(arg1)

将参数arg1转换为每个单词的首字母大写的格式.

select INITCAP('hello world') from dual;

INITCAP('HELLOWORLD')

---------------------

Hello World

5 INSTR(arg1,to_find,pos,occurrence)

在arg1中查找to_find子字符串,并返回一个整数表示它出现的位置.参数pos指定在参数arg1中开始搜索的位置.如果pos为正,则在该数字指定的位置开始搜索.如果pos为负,则在该数字指定的位置开始搜索,但位置指定是从字符串结尾处开始的.并且反向搜索.参数occurrence指定搜索第几次出现的了字符串.

--从左面第1个字符开始搜索,第一次出现ABC的位置

select instr('HABCHABC','ABC',1,1) from dual;

INSTR('HABCHABC','ABC',1,1)

---------------------------

2

--从左面第3个字符开始搜索,第一次出现ABC的位置

select instr('HABCHABC','ABC',3,1) from dual;

INSTR('HABCHABC','ABC',3,1)

---------------------------

6

----从左面第1个字符开始搜索,第二次出现ABC的位置

select instr('HABCHABC','ABC',1,2) from dual;

INSTR('HABCHABC','ABC',1,2)

---------------------------

6

--从右面第1个字符开始搜索,第二次出现ABC的位置

select instr('HABCHABC','ABC',-1,2) from dual;

INSTR('HABCHABC','ABC',-1,2)

----------------------------

2

6 LENGTH(arg1)

返回参数arg1的长度.arg1可以是char,varchar2,nchar,nvarchar2,clob或nclob

select length('abcdefg') from dual;

LENGTH('ABCDEFG')

-----------------

7

7 lower(arg1)

返回参数arg1的小写形式.

select lower('AbCdEfG') from dual;

LOWER('ABCDEFG')

----------------

Abcdefg

8 LPAD(arg1,n,arg3)

返回在arg1左边填充若干个arg3组成长度为n新字符串.如果n小于arg1的长度,则变为 将arg1从左侧截取N字符.

--在ABC前面连续添加Ha直到总长度为10

select lpad('ABC',10,'Ha') from dual;

LPAD('ABC',10,'HA')

-------------------

HaHaHaHABC

select lpad('ABC',2,'Ha') from dual;

LPAD('ABC',2,'HA')

------------------

AB

9 LTRIM(arg1,arg2)

在arg1左面删除连续出现的arg2.不指定arg2则默认删除空格.

select ltrim('AAAASA','A') from dual;

LTRIM('AAAASA','A')

-------------------

SA

select ltrim(' AAA') from dual;

LTRIM('AAA')

------------

AAA

10 REPLACE(arg1,search,replace)

该函数用replace 参数替换出现的所有search参数.

select replace('abcdabce','abc','x') from dual;

REPLACE('ABCDABCE','ABC','X')

-----------------------------

Xdxe

11 SUBSTR(arg1,pos.len)

从arg1中指定的位置pos开始向右侧截取指定长度len的子字符串.如果pos为正则从字符串arg1左侧开始计数,如果为负则从字符串右侧开始计数.

如果未指定len,则返回至该字符串从位置pos开始到串尾的子串.

SQL> --从左侧第3位开始向后截取5个字符.

SQL> select substr('1234567890',3,5) from dual;

SUBSTR('1234567890',3,5)

------------------------

34567

--从右侧第3位开始向后截取5个字符.

SQL> select substr('1234567890',-3,5) from dual;

SUBSTR('1234567890',-3,5)

-------------------------

890

--从左侧第3位开始向后截取到字符串尾.

select substr('1234567890',3) from dual;

SUBSTR('1234567890',3)

----------------------

34567890

数字类函数

1.CEIL(n)函数 返回大于等于n的最小整数

2.ROUND(n1，n2)函数

3.POWER(n1，n2)函数

日期和时间类函数

1.SYSDATE()函数

2.ADD_MONTHS(d,i)函数

转换类函数

1.TO_CHAR()函数

2.TO_NUMBER(s[,format[lan]]函数

聚合类函数

1.AVG(X[DISTINCT|ALL])函数

2.COUNT(x[DISTINCT|ALL])函数

3.SUM(x[DISTINCT|ALL])函数