数据库(二) Oracle篇-Toy模板网

这篇具有很好参考价值的文章主要介绍了数据库(二) Oracle篇。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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_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

数据库(二) Oracle篇,后端,oracle,java

概述

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开头。也是一个可选部分,作用于错误异常处理

基本语法

变量命名

为了开发过程中统一规范,在系统设计阶段需要开发人员共同遵守要求,变量命名方法建议采取如下

数据库(二) Oracle篇,后端,oracle,java

变量类型

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文章来源地址https://www.toymoban.com/news/detail-687807.html

Oracle与Mysql区别

数据类型方面
- Oracle支持更多的数据类型,包括blob、clob、nclob、bfile等,MySQL则没有这些类型
存储引擎方面
- Oracle支持多种存储引擎,MySQL也是如此,但是Oracle默认的存储引擎是ACID-compliant的,而MySQL默认使用的是不具备ACID特性的MyISAM存储引擎
查询优化方面
- Oracle具有更加成熟的查询优化器,能够更好的处理复杂查询,MySQL则需要手动进行查询优化

到了这里，关于数据库(二) Oracle篇的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

数据库(二) Oracle篇