Oracle 全拼和首字母查询

发表于 更新于 分类于 本文字数： 1.6k 阅读时长 ≈ 1 分钟
最近项目中有个助记码的需求, 预期效果是用户在文本框中输入时, 会出现一个下拉框提示, 随着用户输入会不断地更新提示, 基本功能倒是实现了, 但是当用户输入字符很多的时候就会特别卡~ 简直卡到绝望~ 于是换了一种新的思路之后, 一切犹如行云流水般丝滑... 真的有这么丝滑吗? 是的, 真的有这么丝滑!!

转换方法

首先在 Oracle 数据库中建立 "汉字转全拼" 和 "汉字转首字母" 的方法, 网上的方法基本全部不可信, 支持的汉字很少, 我使用的是从 "ItPub" 论坛上下载的, 目前没有发现不支持的汉字.

itpub

下载地址 解压密码是: https://kuroha.vip/

解压之后, 直接执行里面的 sql 文件, 会建立一个 package 和一个 package body, 之后就可以使用 hzpy.gethzfullpy() 和 hzpy.hzpycap() 方法获取汉字的拼音和首字母了.

1
2
3
4
select hzpy.hzpycap('又双叒叕') from dual;
select hzpy.gethzfullpy('又双叒叕') from dual;
select lower(hzpy.hzpycap('又双叒叕')) from dual;
select lower(hzpy.gethzfullpy('又双叒叕')) from dual;

新思路

之前的思路是对人员表中的所有人员姓名进行转换, 将转换后的结果和用户的实时输入做 like 对比, 但是项目中的人员数动辄就近万条, 再加上用户如果使用全拼方式查询, 那速度真是~ 就像电脑要死机一样. 于是后来转换思路, 在人员表中加入两列, 一列用来保存姓名所对应的首字母形式, 另一列用来保存姓名所对应的全拼形式.

使用 update 语句更新已存在人员的全拼和首字母信息, 新增人员使用触发器进行自动更新. 下面是触发器使用时的一些要点.

SQL 语句分类

  • DDL: 全称 Data Definition Languages, 译为 "数据定义语言", 定义了不同的数据段, 数据库, 表, 列, 索引等数据库对象, 简单来说就是对数据库内部的对象进行创建, 删除和修改的操作语言, 常用的语句关键字主要包括 create, drop, alter.

  • DML: 全称 Data Manipulation Language, 译为 "数据操作语言", 用于添加, 删除, 更新和查询数据库记录, 并检查数据完整性, 简单来说就是对表内部数据的增删改查操作, 不涉及到表定义, 表结构的修改, 更不会涉及到其他对象, 常用的语句关键字主要包括 insert, delete, udpate, select.

  • DCL: 全称 Data Control Language, 译为 "数据控制语言", 用于控制不同数据段的访问许可和访问级别的语句, 这些语句定义了数据库, 表, 字段, 用户的访问权限和安全级别. 主要的语句关键字包括 grant, revoke.

DML 触发器

  1. 触发器不能接收参数.

  2. 触发器的执行顺序:

    • 执行 before 语句级触发器
    • 执行 before 行级触发器
    • 执行 DML 语句
    • 执行 after 行级触发器
    • 执行 after 语句级触发器

  1. 语句级触发器中, 不得使用 :new 和 :old, 行级触发器中, :old 为 "操作前" 的整行记录, :new 为 "操作后" 的整行记录.

    --- insert update delete
    old 不允许使用 更新前记录 删除前记录
    new 插入后记录 更新后记录 不允许使用

  2. 只有 before 行级触发器可以修改 :new 记录的值.

  3. 在行级触发器中, 不得将 insert, update, delete 语句作用于 "自身触发表"!

示例

1
2
3
4
5
6
7
8
--在插入新纪录之前, 修改这条待插入记录的 szm 字段和 qp 字段.
create or replace trigger trigger_ryb_pinyin
before insert on ryb
for each row
begin
:new.szm := lower(hzpy.hzpycap(:new.xm));
:new.qp := lower(hzpy.gethzfullpy(:new.xm));
end;

参考链接

  • 详解ORACLE中的trigger

  • 关于oracle触发器中修改自身表字段的问题