Oracle数据库相关

Oracle数据库常用函数

 

  转换函数 

转换函数将值从一种数据类型转换为另外一种数据类型。在Oracle中常用的转换函数主要有三个：TO_CHAR、TO_DATE和TO_NUMBER。 

TO_CHAR()

本函数又可以分三小类，分别是 

<1> 转换字符‐>

字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型； 

例如：SELECT TO_CHAR('AABBCC') FROM dual;

<2> 转换时间‐>

字符TO_CHAR(d[,fmt])：将指定的时间按照指定格式转换为varchar2类型； 

例如：SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;

<3> 转换数值‐>

字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回； 

例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM dual;

TO_DATE(c[,fmt[,nls]])

将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。 

例如： 

SELECT TO_DATE(2454336, 'J') FROM dual;

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM dual;

TO_NUMBER(c[,fmt[,nls]])

将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。 

例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM dual;

附录：TO_DATE中用到的格式 YYYY表示年 MM表示月 

DD表示日期 

HH24表示0‐23小时 

MI表示分钟 

SS表示秒 

不区分大小写

 

聚合函数 

oracle函数分两类：单行函数、多行函数 。多行函数也称为聚合函数、组函数，参数为数组，数据大小为记录数，这种数组不是普通高级语言的数组，是一种虚拟数组，当记录数大时，会将数据写入硬盘，内存中放的只是影像。 

为了演示常用聚合函数，首先创建一个测试表： 

CREATE TABLE temp

(

col1 VARCHAR2(20),

col2 NUMBER

);

INSERT INTO temp (col1, col2) VALUES ('AA1', 435);

INSERT INTO temp (col1, col2) VALUES ('AA1', 831);

INSERT INTO temp (col1, col2) VALUES ('AA1', 788);

INSERT INTO temp (col1, col2) VALUES ('AA1', 765);

INSERT INTO temp (col1, col2) VALUES ('AA2', 367);

INSERT INTO temp (col1, col2) VALUES ('AA2', 193);

INSERT INTO temp (col1, col2) VALUES ('AA2', 459);

INSERT INTO temp (col1, col2) VALUES ('AA2', 813);

INSERT INTO temp (col1, col2) VALUES ('AA3', 812);

INSERT INTO temp (col1, col2) VALUES ('AA3', 787);

INSERT INTO temp (col1, col2) VALUES ('AA3', 728);

INSERT INTO temp (col1, col2) VALUES ('AA4', 207);

INSERT INTO temp (col1, col2) VALUES ('AA4', 278);

INSERT INTO temp (col1, col2) VALUES ('AA4', 548);

INSERT INTO temp (col1, col2) VALUES ('AA5', 471);

INSERT INTO temp (col1, col2) VALUES ('AA5', 53);

INSERT INTO temp (col1, col2) VALUES ('AA5', 510);

INSERT INTO temp (col1, col2) VALUES ('AA5', 895);

INSERT INTO temp (col1, col2) VALUES ('AA5', 752);

1、AVG(n)求取记录集中的平均值。 

例如：求temp表中col2字段数据的平均值 

SELECT AVG(col2) FROM temp;

例如：根据col1分组，分别求每组col2字段数据的平均值 

SELECT col1,AVG(col2) FROM temp GROUP BY col1;

2、SUM(n)求取记录集中的总和 

例如：求temp表中col2字段数据的总和 

SELECT SUM(col2) FROM temp;

例如：根据col1分组，分别对每组col2字段数据求和 

SELECT col1,SUM(col2) FROM temp GROUP BY col1;

3、COUNT(n)对记录集中的指定列记数 

例如：查询temp表的总记录数 

SELECT COUNT(*) FROM temp;

例如：根据col1分组，分别求每组数据的记录数 

SELECT col1,COUNT(*) FROM temp GROUP BY col1;

4、MAX(n)求取记录集中指定列的最大值 

例如：求temp表中col2字段数据的最大值 

SELECT MAX(col2) FROM temp;

例如：根据col1分组，分别求每组col2字段数据的最大值 

SELECT col1,MAX(col2) FROM temp GROUP BY col1;

5、MIN(n)求取记录集中指定列的最小值 

例如：求temp表中col2字段数据的最小值 

SELECT MIN(col2) FROM temp;

例如：根据col1分组，分别求每组col2字段数据的最小值 

SELECT col1, MIN(col2) FROM temp GROUP BY col1;

日期函数 

日期函数主要是操作DATE 类型的数据，对日期值进行运算，根据函数的用途产生日期数据类型或数值类型的结果。 

常用日期函数 函数 

说明 

输入 

输出 

sysdate

获得当前日期和时间

SELECT SYSDATE FROMdual;

2012‐4‐9 10:21:30

last_day(d)

返回指定日期所在月份的最后一天 

SELECT LAST_DAY(TO_DATE(

'2011-2-1',

'YYYY-MM-DD'

))

FROM dual;

2011‐2‐28

add_months(d,n)

 

得到某一时间之前或之后n个月的时间，n可以为任何整数 

select add_months(sysdate,6) from dual;

该查询的结果是当前时间半年前的时间

months_between(D,D)

返回两个日期间的月份 

SELECT MONTHS_BETWEEN(

'04-11月-05',

'11-1月-01')

FROM dual;

57.7741935483871

next_day(d,n)

返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同 

SELECT NEXT_DAY(

SYSDATE,

'星期一')

FROM dual;

SELECT NEXT_DAY(

SYSDATE,

2)

FROM dual;

2012‐4‐16 10:49:55

2012‐4‐16 10:49:55

round(d,fmt)

按指定格式对日期进行四舍五入 

SELECT

ROUND(TO_DATE(

'13-2月-03'),

'YEAR')

FROM dual;

SELECT

ROUND(TO_DATE(

'13-2月-03'),

2003‐1‐1

2003‐2‐1

2003‐2‐16

字符函数 

字符函数是Oracle中广泛使用的函数，顾名思义就是专门用于字符处理的函数，函数一般接受字符作为参数，并且可以返回字符或数字。除了特别说明的之外，这些函数大部分返回VARCHAR2类型的值。字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的，比如：VARCHAR2数值被限制为2000字符(ORACLE 8中为4000字符)，而CHAR数值被限制为255字符(在ORACLE8中是2000)。 

常用的字符函数 函数 

说明 

输入 

输出 

chr(x)

返回在数据库字符集中与X拥有等价数值的字符。CHR和ASCII是一对反函数。经过CHR转换后的字符再经过ASCII转换又得到了原来的字 

SELECT CHR(97) FROM dual;

a

LOWER(string)

返回小写的string

SELECT LOWER('A') FROM dual;

SELECT LOWER('字') FROM dual;

a

字 

UPPER(string)

返回大写的string

SELECT UPPER('a') FROM dual;

SELECT UPPER('字') FROM dual;

A

字 

CONCAT（string1,string2）或者||

返回string1，并且在后面连接string2

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

Helloworld

ltrim（string1,string2）

从字符串string1的左边删除字符串String2。String2被缺省设置为单个的空格。 

SELECT LTRIM('ABA','A') FROM dual;

BA

rtrim（string1,string2）

从字符串string1的右边删除字符串String2。String2被缺省设置为单个的空格。 

SELECT RTRIM('ABA','A') FROM dual;

AB

replace(string, search_str [,replace_str])

把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除 

SELECT REPLACE('Hello','H','h') FROM dual;

hello

initcap（string） 

返回字符串的每个单词的第一个字母大写而单词中的其他字母小写的string。如果

SELECT INITCAP('hello') FROM dual;

Hello

Hello World

不是两个字母连在一起，则认为是新的单词 

SELECT INITCAP('hello world') FROM dual;                                             

INSTR(string,set[,start [,occurrence ] ] )

返回指定的字符串所在的位置，如果指定start，oracle则跳过前面所有字符串到该位置开始搜索；occurence，是强迫instr跳过前几次与字符串匹配，给出下一次匹配 的位置，如果occurence指定3,那就是匹配第三次的位置了。 例INSTR('ABACAAA','A',2,2) 从ABACAAA中匹配A这个字符串，从2个位置开始匹配，匹配第2次A所在的位置。如果set中不止有一个字符而是有几个字符组成的，则INSTR给出该字符集中的第一个字符的位置 

SELECT INSTR('ABACAAA','A',2,2) FROM dual;

5

length（string） 

返回string的字节单位的长度。因为CHAR数值是填充空格类型的，所以如果string是数据类型CHAR，则它的结尾的空格都被计算到字符串长度中间.。如果string是NULL，返回结果是NULL，而不是0

SELECT LENGTH('Hello') FROM dual;

SELECT LENGTH(NULL) FROM dual;

5

NULL

 

数字函数 

数字函数是对数字数据进行一些算术运算的函数，函数的输入参数是数字类型的，并且输出参数也是数字类型，比如三角函数中的求正弦余弦等。 

常用数字函数 函数 

说明 

输入 

输出 

ABS(n)

返回数值n的绝对值

SELECT ABS(-13) FROM dual;

13

ACOS(n)

返回数值n的反余弦值。输入数值范围在‐1~1之间，返回值为弧度 

SELECT ACOS(0.2) FROM dual;

1.36943840600457

COS(n)

返回数值n的余弦值。返回值为弧度 

SELECT COS(6) FROM dual;

0.960170286650366

SIN(n)

返回数值n的正弦值。返回值为弧度 

SELECT SIN(1.6) FROM dual;

0.999573603041505

ASIN(n)

返回数值n的反正弦值。输入数值范围在‐1~1之间，返回值为弧度 

SELECT ASIN(0.6) FROM dual;

0.643501108793284

EXP(n)

返回e的n次冥。(e=2.71828183…)

SELECT exp(6) FROM dual;

403.428793492735

FLOOR(n)

返回小于等于数值n的最大整数 

SELECT FLOOR(9.6) FROMdual;

9

MOD(m,n)

返回m/n后的余数，若n=0，则返回m（求模运算） 

SELECT MOD(9,6) FROM dual;

3

POWER(m,n)

返回m的n次冥 

SELECT POWER(2,3) FROMdual;

8

ROUND(n,[m])

执行四舍五入运算，m可以省略，当省略m时，四舍五入到整数位；当m为正数时，四舍五入到小数点后m位；当m为负数时，四舍五入到小数点前m位 

SELECT ROUND(2.936,2) FROM dual;

2.94

TRUNC(n,[m])

截取数值n,m可以省略，当省略m时则截

SELECT TRUNC(2.936,2)

2.93

取n的小数部分；当m为正数时则将n截取到小数点后m位；当m为负数时则将n截取到小数点前m位

FROM dual;

SIGN(n)

检测数值的正负，当n<0则返回‐1；当n>0则返回1，当n=0返回0

SELECT SIGN(6.698),

SIGN(-9),

SIGN(0)

FROM dual;

1 ‐1 0

SQRT(n)

返回数值n的平方根。(n>=0)

SELECT SQRT(4) FROM dual;

2

 

 

 

数据表

插入数据

Insert  into 表名 [列名]Values  插入的数据

更新数据

Update 表名 set列名=’数据’where条件

经验：一般为主键列名like(还有between and ,in)模糊查询字符%

数据排列

Order by  desc(默认升序,desc为降序)

去除重复关键字

Distinct

删除记录

Delete from 表名where条件表达式

ｓｑｌ编程

函数

虚拟表dual

截取函数trunc

四舍五入round

函数类型有单行函数,聚合函数,分析函数(详见资料)

高级查询

查询中的比较运算符

=,!=0,<,>,<=,>=,BETWEEN ….AND (检查是否在两个值之间)IN（与列表中的值相匹配），ＬＩＫＥ（匹配字符模式）IS NULL(检查是否为空)

逻辑操作符

ＡＮＤ　（与）　ＯＲ　（或）　ＮＯＴ（非）

ｌｉｋｅ的用法

 查询name字段中包含有“明”字的。
select * from table1 where name like '%明%'

    查询name字段中以“李”字开头。
select * from table1 where name like '李%'

 

Rownum伪列,在物理上不存在,是动态生成的. Rownum的固化,用于分页查询,后加一个列名即可!

如果排序,要把结果集当成虚拟表,在虚拟表查询,在虚拟表里截取相应部分,

高级查询大体分为子查询,连接查询,集合查询等

当我们要合并或检索多个表中的数据时要用到下面的三个方法

子查询:将一个查询包含到另一个查询中

子查询的查询条件不依赖父查询,成为不相关子查询,一般是关键字in

相关子查询一般用比较运算符(返回单值)或者any,all(多值) NOT   EXISTS

等特殊符号

 

连接:合并多个数据表中的列

内连接

等值连接:关联条件的运算符是用等号来连接的

不等值连接

在两个表中没有列的值直接对应相等就用到等值连接条件是除=号以外的操作符!

自然连接

在两张表中寻找那些数据类型和列名都相同的字段,然后自动地将他们连接起来,并返回所有符合条件的结果(数据库自行会连接)

 

外连接

又分左右外连接,左外连接是指在关联过程中以左边表为准,即使右边表没有满足连接条件的对应记录,左边也会出现查询的查询结果里,右边以空值出现,而有连接则以右边为准

自连接

使用自连接可以将自身表的一个镜像当作另一个表对待,即自连接的本意就是将一个表看成多张表来做连接

交叉连接

交叉连接返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合条件的数据行数,也称笛卡尔积

 

集合:合并多个数据表中的行

集合查询包含UNION(并集),ＩＮＴＥＲＳＥＣＴ（交集）及ＭＩＮＵＳ（差集）三种

UNION返回两个以上的表的相类似的查询结果放在一起

ＩＮＴＥＲＳＥＣＴ返回两个表中的相同的信息

ＭＩＮＵＳ返回一个表中出现的信息

ｐｌ／ｓｑｌ编程

ｐｌ／ｓｑｌ结构块结构

ｄｅｃｌａｒｅ，ｂｅｇｉｎ　ｅｘｃｅｐｔｉｏｎ，ｅｎｄ　

ｐｌ／ｓｑｌ块分类

命名块：名称即标签

子程序：包含存储过程函数等

触发器：当数据库发生操作时会触发一些事件，达到自动执行相应程序

程序包：存储在数据库中的一组子程序，变量定义．在包中的子程序可以被其他程序包或子程序调用．

常量与变量的声明及变量类型

Ｖ－ｎａｍｅ　ｖａｒｃｈａｒ2；

控制语句

ｉｆ　，　ｔｈｅｎ　，　ｅｎｄ　ｉｆ　

ｌｏｏｐ　，ｅｘｉｔ　ｗｈｅｎ，ｅｎｄ　ｌｏｏｐ　

异常的处理

ｅｘｃｅｐｔｉｏｎ预定义　，　自定义

数据库备份与恢复，游标及事务处理

数据泵技术对数据的备份与恢复

为什么使用游标

程序语言是面向记录的，一组变量一次只能存放一个变量或者一条记录，无法直接接收数据库中的查询结果集引入游标就解决了这个问题

游标的分类

静态游标：又分隐式游标和显式游标前提是查询语句确定

动态游标：又分强类型游标和弱类型游标，查询语句在不确定的情况下．

详细用法参照示例！

游标属性

％FOUND

事务控制语句

COMMIT :提交事务

ROLLBACK:回滚事务

SAVEPOINT:在事务中创建存储点

ROLLBACK　ＴＯ＜ＳａｖｅＰｏｉｎｔ　＿Ｎａｍｅ＞：将事务回滚到存储点

SET ＴＲＡＮＳＡＣＴＩＯＮ设置事务的属性

 

子程序和触发器

存储过程，　函数，及触发器的使用参照具体的例子！

Orcale创建序列

CREATE SEQUENCE emp_sequence --序列名

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

CACHE 10;

 

 

得到序列的SQL语句

select seq_newsid.nextval from sys.dual;

--删除序列

drop sequence 序列名

 

 

create sequence seq_newsId

increment by 1

start with 1

maxvalue 999999999;

 

 

 

@SequenceGenerator(name = "generator",sequenceName="sequence_test" )

@Id

@GeneratedValue(strategy = SEQUENCE, generator = "generator")