Oracle SQL常用函数
概述
SQL函数有单行函数和多行函数,其区别为：
- 单行：输入一行,返回一行,如字符、数字、转换、通用函数等
- 多行：输入多行,返回一行,也称为分组函数、组函数、聚合函数,且多行函数会自动滤空
单行函数
字符函数
- CONCAT(X,Y)： 连接字符串X和Y
- INSTR(X,STR)： 后面STR在前面字符串X第一次出现的位置,一般用于判断STR是否存在于X中,若存在,则结果肯定大于0,否则结果为0表示不存在
- LOWER(X)： X转换成小写
- UPPER(X)： X转换成大写
- INITCAP(X)： X转换首字母大写
数字函数
- 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
- 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
转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有
- TO_CHAR(d|n[,fmt]): 把日期和数字转换为制定格式的字符串。Fmt是格式化字符串
- TO_CHAR对日期的处理
- SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;
- TO_CHAR对日期的处理
- TO_DATE(X,[,fmt]): 把一个字符串以fmt格式转换成一个日期类型
- TO_NUMBER(X,[,fmt]): 把一个字符串以fmt格式转换为一个数字
通用函数
- NVL函数: 将空值转换为已知值,可以使用的数据类型为：数字、日期、字符,数据类型必须匹配
- NVL2(expr1,expr2,expr3): 如果参数1非空not null,则返回参数2的值,否则返回参数3的值
- 条件表达式:在使用if-then-else逻辑时可以通过如下两种方式表示
- decode: 条件表达式,在使用if-then-else逻辑时可以通过decode(col/expression,search1,result1,search2,result2,....,,.........,default)
- case expr when comparison_expr1 then return_expr1 when comparison_expr2 then return_expr2... else_expr
多行函数
嵌套函数distinct
distinct可用来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回显示不重记录的所有值。一般和count配合使用,作为统计非空且不重复的记录数
SELECT COUNT(DISTINCT(Column)) FROM TableName;注意:DISTINCT关键字效率会比较低,如果仅仅是为了显示不重复的记录,建议使用group by,
因为distinct只有用二重循环查询来解决,而这样对于一个数据量非常大的表来说,无疑是会直接影响到效率的
PLSQL
概述
PL/SQL(Procedure Language/Structured Query Language)是一种高级数据库程序设计语言,同时PL/SQL也是块结构语言;也是对SQL语言存储过程语言的扩展,也称为过程处理语言。专门用于在各种环境下对ORACLE数据库进行访问,该语言已经集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理
PL/SQL块结构和组成元素
块结构
PL/SQL程序由三个块组成,即声明部分DECLARE、执行部分BEGIN(必须存在)、异常处理部分EXCEPION、结束END
- DECLARE: 以关键字DECLARE开头,属于一个可选部分,在此声明PL/SQL用到的变量、类型、游标，以及局部的存储过程和函数
- BEGIN: 包含在关键字BEGIN和END之间,这是一个强制性部分。由程序的可执行PL/SQL语句组成,并且应该有至少一个可执行代码行,它可以只是一个NULL命令,表示不执行任何操作
- EXCEPION: 以关键字EXCEPTION开头。也是一个可选部分,作用于错误异常处理
基本语法
变量命名
为了开发过程中统一规范,在系统设计阶段需要开发人员共同遵守要求,变量命名方法建议采取如下
变量类型
PL/SQL中常用的变量类型包括标量、记录类型(记录类型是把逻辑相关的数据作为一个单元存储起来,称为PL/SQL RECORD的域,其作用就是存放互不相同但逻辑相关的信息)、游标变量等
DECLARE
v_deptname VARCHAR2(10); --定义标量变量
v_loopcounter BINARY_INTEGER; --使用PL/SQL类型定义标量变量
--定义记录类型
TYPE t_employee IS RECORD(
empname VARCHAR2(20),
empno NUMBER(7),
job VARCHAR2(20)
);
v_employee t_employee; --定义记录类型变量
TYPE csor IS REF CURSOR; --定义游标变量
v_date DATE NOT NULL DEFAULT SYSDATE; --定义变量并指定默认值
BEGIN
NULL;
END;
流程控制
条件判断
- if...then elsif then...else...end if;
- case...when...then...end
循环结构
- loop...exit when...end loop
- while...loop...end loop
- for i in ...loop...end loop
其他
- goto:PL/SQL编程语言中的GOTO语句在同一子程序中提供从GOTO到标记语句的无条件跳转,但不推荐使用GOTO语句,因为它难以追踪程序的控制流程,使程序难以理解和难以修改
- exit
- 当循环中遇到EXIT语句时,循环将立即终止,继而执行循环后面的下一个语句
- 如果使用嵌套循环(即在另一个循环中有一个循环),则EXIT语句将停止执行最内循环,转而执行最外层循环的下一次循环
游标(类似于java中的Iterator)
在PL/SQL程序中,对于处理多行记录的事务经常使用游标来实现,游标是一个指向上下文的句柄或指针,通过游标PL/SQL可以单独操纵结果集中的每一行,即游标可以把集合操作转换成对单个记录进行不同处理的方式
显示游标
- 定义游标: CURSOR cursor_name is select_statement --关联SQL语句
- 打开游标: OPEN cursor_name(PL/SQL程序不能用OPEN语句重复打开一个游标)
- 提取游标数据: FETCH cursor_name INTO var_name(fetch会自动移动游标指针)
- 关闭游标: CLOSE cursor_name
/** 该PL/SQL语句含义为:通过定义一个存储过程DeleteStationDRI,根据输入的PMILESOTNEID删除TBLSTATIONDRI表中对应的记录 1.定义了一个游标 C_1,用于查询满足条件FMILESONEID = PMILESOTNEID的记录的FSTATIONDRIID;并声明一个变量V_STATIONDRIID用于存储查询结果 2.打开游标C1,获取id并根据id删除表中对应记录。 注意:如果删除操作发生异常，忽略异常并继续循环 **/ create or replace procedure DeleteStationDRI(PMILESOTNEID in NUMBER) is CURSOR C_1 IS SELECT T.FSTATIONDRIID FROM TBLSTATIONDRI T WHERE T.FMILESONEID =PMILESOTNEID; VSTATIONDRIID NUMBER; begin OPEN C_1 ; LOOP FETCH C_1 INTO VSTATIONDRIID; EXIT WHEN C_1%NOTFOUND; BEGIN DELETE FROM TBLSTATIONDRI T WHERE T.FSTATIONDRIID = VSTATIONDRIID; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; CLOSE C_1; RETURN; end DeleteStationDRI;
隐式游标
BEGIN
FOR c IN (SELECT * FROM employees WHERE ROWNUM<=10) LOOP
DBMS_OUTPUT.PUT_LINE(c.employee_id||' '||c.first_name||' '||c.last_name);
END LOOP;
END;异常处理
异常情况处理是用来处理正常执行过程中未预料的事件,包括异常处理预定义错误(大约24个,由ORACLE自动触发)和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行
存储函数(有返回值)/存储过程(无返回值)
概述
ORACLE提供可以把PL/SQL程序存储在数据库中,并可以在任何地方来运行它,称为存储函数或者存储过程,它们统称为PL/SQL子程序且属于被命名的PL/SQL块,它们都是为了完成特定功能的程序;过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据
- 如果存储过程或存储函数带参数的话我们需要指明是输入参数(in,默认值)还是输出参数(out)
- 存储过程和存储函数都可以通过out参数指定一个或多个输出参数,我们可以利用out参数在过程或函数中实现返回一个或多个值(即存储过程本来不能有返回值,但利用out参数我们就可以实现存储过程返回值)
- 一般如果需要返回多个值,我们优先使用存储过程,如果只要返回一个值我们优先使用存储函数
存储函数
create [or replace] function 函数名(paramName in|out typeName,...)
return 函数值类型
as|is--相当于PL/SQL块的declare，这里不可省略
PL/SQL子程序体;创建
create or replace function queryEmpIncome(eno in number)
return number
as
--定义变量保存员工的薪水和奖金
v_sal emp.sal%type;
v_bonus emp.comm%type;
begin
--得到员工的月薪和奖金
select sal,comm into v_sal,v_bonus from emp where empno=eno;
--直接返回年收入
return v_sal*12+nvl(v_bonus,0);
end;调用
declare
v_sal number;
begin
--得到员工7891的年收入
v_sal:=queryEmpIncome(7891);
dbms_output.put_line(v_sal);
end;存储过程
--创建或替换一个存储过程参数列表需要指明输入或者输出参数
create [or replace] procedure 过程名(paramName in|out typeName,...)
as|is--相当于PL/SQL块的declare，这里不可省略
PL/SQL子程序体;创建
create or repalce procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的薪水
v_sal emp.sal%type;
begin
--得到员工的涨前的薪水
select sal into v_sal emp where empno=eno;
--给员工涨100
update emp set sal = sal+100 where empno = eno;
--这里进行了update,一般不在存储过程和存储函数中进行提交事务,一般由调用者进行提交
--打印涨前和涨后的薪水
dbms_output.put_line('涨前:'||v_sal||'涨后'||(psal+100));
end;调用
begin
raisesalary(7839); --员工号为7839涨工资
raisesalary(7566); --员工号为7566涨工资
end;触发器Trigger
概述
触发器是许多关系型数据库系统都提供的一项技术,在ORACLE系统里,触发器类似过程和函数,都有声明、执行、异常处理过程的PL/SQL块。触发器在数据库里以独立的对象存储,它不同于存储过程通过其他程序启动,触发器是由一个事件来触发运行,即触发器是当某个事件发生时自动地隐式运行,常用于数据库表在进行INSERT、UPDATE、DELETE操作或者对视图进行类似操作
项目开发实战之oracle使用序列和触发器来实现主键自动递增的功能
创建TBLROOM表
create table TBLROOM
(
room_id INTEGER not null,
floor_id INTEGER,
room_name NVARCHAR2(20),
)
tablespace XXXX
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column TBLROOM.room_id
is '附房ID。主键，自增';
comment on column TBLROOM.floor_id
is '楼层位置ID。外键，连TblFloor';
comment on column TBLROOM.room_name
is '附房名称。';
alter table TBLROOM
add constraint PK_TBLROOM primary key (ROOM_ID)
using index
tablespace XXXX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);创建表序列
create sequence S_ROOM_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 221
increment by 1
cache 20; 创建表的触发器(每次向 tblroom 表插入新数据之前,自动生成一个唯一的roomID)
CREATE OR REPLACE TRIGGER TIB_TblRoom
before insert on tblroom
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;
begin
select S_Room_ID.NEXTVAL INTO :new.room_id from dual;
-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);-- PL/SQL内置的存储过程,用于抛出一个应用程序错误
end TIB_TblRoom;Mybatis.xml调用实现添加记录时主键自增
<insert id="add">
<selectKey keyProperty="roomId" resultType="java.lang.Long" order="AFTER">
SELECT S_ROOM_ID.CURRVAL FROM DUAL
</selectKey>
INSERT INTO TBLROOM
(
FLOOR_ID,
ROOM_NAME,
)
VALUES
(
#{floorEntity.floorID},
#{roomName},
)
</insert>其他
开发扩展
- || 连接符,在连接符中的字符串或者日期必须使用单引号,不能使用双引号;
- escape转义字符,用于特殊字符查询
- select * form table where column like '%#_%' escape '#‘,用于查询包含_字符的数据
-
在Oracle中,join=inner join,left join=left outer join,right join=right outer join
Oracle与Mysql区别
- 数据类型方面
- Oracle支持更多的数据类型,包括blob、clob、nclob、bfile等,MySQL则没有这些类型
- 存储引擎方面
- Oracle支持多种存储引擎,MySQL也是如此,但是Oracle默认的存储引擎是ACID-compliant的,而MySQL默认使用的是不具备ACID特性的MyISAM存储引擎
- 查询优化方面
- Oracle具有更加成熟的查询优化器,能够更好的处理复杂查询,MySQL则需要手动进行查询优化
1971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
