数据库(二) Oracle篇

这篇具有很好参考价值的文章主要介绍了数据库(二) 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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • java serverlets使用数据源连接oracle数据库,并执行查询操作代码

    package chap03; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.Statement; import java.util.*; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.Namin

    2024年02月08日
    浏览(55)
  • 【Oracle】收集Oracle数据库内存相关的信息

    【声明】文章仅供学习交流,观点代表个人,与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB) Oracle数据库包含多个内存区域,每个区域都包含多个子组件。 Oracle Database Memory Structures 根据具体问题的需要,可以通过如下命令收集Oracle数据库内存相关的信息。 例: 注:SET

    2024年01月21日
    浏览(70)
  • Oracle数据库面试题 精选 Oracle 面试题

    1.解释冷备份和热备份的不同点以及各自的优点 冷备份 发生在数据库已经正常关闭的情况下,将关键性文件拷贝到另外位置的一种说法。适用于所有模式的数据库。 优点 1. 是非常快速的备份方法(只需拷贝文件) 2. 容易归档(简单拷贝即可) 3. 容易恢复到某个时间点上(只

    2024年02月05日
    浏览(104)
  • 【Oracle】使用 SQL Developer 连接 Oracle 数据库

    SQL Developer 是 Oracle 官方推出的一款免费的数据库开发工具,它提供了丰富的数据库开发功能,其中包括连接 Oracle 数据库的功能。 在本文中,我们将从多个方面详细阐述如何使用 SQL Developer 连接 Oracle 数据库。 在连接 Oracle 数据库前,需要需要做一些准备工作,包括安装 SQ

    2024年02月06日
    浏览(70)
  • Oracle数据库

    ①层次型数据库 ②网状型数据库 ③关系型数据库(主要介绍) E-R图:属性(椭圆形),实体(矩形),联系(菱形-一对一、一对多、多对多) 注:有的联系也有属性 关系型数据库的设计范式: 第一范式(1NF):属性不可再分,字段保证原子性 第二范式(2NF):在满足1

    2024年02月08日
    浏览(58)
  • ORACLE数据库 开窗函数

    开窗函数 2.1开窗函数的定义及语法 开窗函数(又名:分析函数,窗口函数,OLAP函数) 聚合函数:将数据按照一定的规则分组,统一分析各组的某项情况,每个分组返回一行结果 开窗函数:将数据按照一定的规则分组,统一分析各组的某项情况,每行数据返回一行结果 (

    2024年02月11日
    浏览(50)
  • Oracle大型数据库技术

    sqlplus 记录实验过程的方法: 开启相关服务 oracleserviceORCL 控制面板–管理–服务–找到后开启 命令行方法: cmd–net start|stop oracleserviceorcl 常用操作 显示当前用户名 常用的用户名和口令 :dba用户,默认口令:change_on_install,Oraclell,必须以sysdba或者sysoper身份登录 system: 普通管

    2024年02月19日
    浏览(51)
  • 快速监控 Oracle 数据库

    Oracle 数据库在行业内应用广泛,通常存放的非常重要的数据,监控是必不可少的,本文使用 Cprobe 采集 Oracle 监控数据,极致简单,分享给大家。 安装配置 Oracle 简单起见,我使用 Docker 启动 Oracle,命令如下: 如上命令启动之后,Oracle 的监听端口是 1521,用户名/密码是 syst

    2024年01月20日
    浏览(51)
  • Oracle数据库完整卸载

    进入计算机管理,在服务中,找到oracle开头的所有服务,右击选择停止。 快捷键:ctrl+shift+esc打开任务管理器 点击开始菜单找到Oracle,然后点击Oracle安装产品,再点击Universal Installer。 点击之后稍等一会然后会进入进入下图界面,点击卸载产品。 单击“卸载产品”,选中除“Or

    2024年01月16日
    浏览(65)
  • oracle数据库巡检脚本

    用于Oracle数据库巡检的示例脚本: 

    2024年02月14日
    浏览(62)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包