当前位置： 首页 > news >正文

人大金仓(KingBaseEs)数据库操作手册

news 2024/12/13 21:49:58

人大金仓数据库（KingbaseES）是由北京人大金仓信息技术股份有限公司（简称人大金仓）自主研发的、具有自主知识产权的通用关系型数据库管理系统。

官方下载地址：KingbaseES 人大金仓数据库

KES技术文档在线手册：KingbaseEs产品手册

Windows安装步骤：kingbaseES 安装过程

PS: 下载的授权文件许可过期了，只需要更换安装目录下的License证书即可！KingbaseES 数据库下载完成后，安装包路径下（KingbaseES\KESRealPro\V008R006C008B0014\doc）存放了大量数据库使用手册，可根据需求查阅！

一、用户管理和授权

Kingbase 数据库初始化完成之后，会创建三个用户：

  • 系统管理员（SYSTEM）

主要负责执行数据库日常管理的各种操作和自主存取控制。(数据库管理员用户不能创建和修改安全员和审计员，也不能将一个普通用户修改为安全员或者审计员)

  • 安全管理员（SSO）

主要负责强制访问规则的指定和管理，监督审计管理员和普通用户的操作，不能创建和操作普通对象。(安全管理员只能创建和修改安全员并且不能将安全员修改为非安全员)

  • 审计管理员（SAO）

主要负责数据库的审计，监督系统管理员和安全管理员的操作，不能创建和操作普通对象。(审计管理员只能创建和修改审计员并且不能将审计员修改为非审计员。)

Kingbase 将管理特权三权分立为三个管理员，三权分立的安全管理体制是为了解决数据库超级用户权力过度集中的问题。还可以创建普通用户来访问数据库，运行数据库应用。若要查找这些用户的状态，请查询相关的数据字典视图。

  • 查看当前登录用户
    SELECT USER; 或者使用 SELECT CURRENT_USER;

1.1 创建用户

使用 CREATE USER 创建用户，用户账户可以具有默认角色和表空间限制等限制！

每个数据库中的用户名都是唯一的，不能创建同名的账号。 可以通过系统**视图 sys_user **查询当前数据库已经存在的用户账号信息。KingbaseES 数据库不区分用户名大小写，默认用户名都会转为小写。

  • 创建用户的权限：

通过 授予用户 CREATEROLE 的系统权限的用户，可以创建用户操作，授权操作 ALTER USER user_name CREATEROLE;

  • 新用户的默认权限

创建新用户账户时，允许此用户访问数据库：CREATE USER user_name WITH PASSWORD '用户登录密码';

创建用户时默认拥有 LOGIN 权限，也可以使用sql授权：ALTER USER user_name LOGIN;

1.2 删除用户

DORP USER 语句可以删除用户。

删除用户需要有 CREATEROLE 特权。如果一个用户对象在任何数据库中存在引用，它就不能被删除。需要删除依赖对象后才能删除用户。DROP OWNED 常常被用来为移除一个或者多个用户做准备：DROP OWNED BY user_name CASCADE;

当数据库中还有用户连接的对话时，也无法删除此用户。必须先终止会话或者用户退出会话后，才能删除用户。
查询动态会话视图 sys_stat_activity ,找到用户的会话 PID ,然后使用函数call sys_terminate_backend('pid') 强行终止会话。用户与数据库断开连接后，就可以使用 DROP USER 语句删除该用户了。

1.3 用户的数据字典视图

KingbaseES 数据库提供了一组数据字典视图，这些视图提供有关与创建用户和配置用户的信息。

视图描述
ALL_OBJECTS描述当前用户可以访问的所有对象
ALL_USERS列出当前用户可见的用户
DBA_OBJECTS描述数据库中的所有对象
DBA_USERS描述数据库中的所有用户
USER_OBJECTS描述当前用户拥有的所有对象
USER_USERS仅描述当前用户
sys_user描述关于数据库用户的信息
sys_roles描述关于数据库角色的信息
sys_stat_activity列出当前数据库会话的会话信息

二、模式对象管理 SCHEMA

一个数据库可以包含一个或多个命名的模式(SCHEMA)，一个模式内可以包含多个表。不同模式中的表名可以相同，但是用一个模式中的表不能同名。模式类似于操作系统的目录，但是不能嵌套！

用户只要有权限访问数据库，就可以访问所连数据库中的任何模式中的对象。当用户没用自己的模式并且其创建或者使用数据库对象没有指定模式时，默认就会使用 PUBLIC 模式。模式通常在一下情况使用：

  • 多个用户使用同一个数据库，而不会互相影响
  • 对数据中的对象进行逻辑分组，更便于管理。
  • 各个应用分别使用各自的模式，以避免命名冲突。
-- ======【模式】=====
--1.创建模式
CREATE SCHEMA tempSchema;--2.删除模式
DROP SCHEMA tempSchema;--3。修改模式名称
ALTER SCHEMA tempSchema RENAME TO tempSchema2;--4.查询数据库下模式
SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES; 
SELECT COUNT(DISTINCT TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES;--5.查看当前所处数据库
SELECT current_database();--6.查看当前所处数据库的模式：
SELECT CURRENT_SCHEMA();
--7.切换其它模式：
ALTER SESSION SET SEARCH_PATH = <schema name>;

三、数据类型

KingbaseES 数据库提供了常用的数据类型分为以下几类：字符数据类型、数值数据类型 和 日期时间数据类型。

PL/SQL 有常量和变量的数据类型，包括 BOOLEAN、引用类型、复合类型（RECORDS）和用户自定义的类型。部分数据类型支持按指定模板进行格式化

3.1 字符数据类型

字符数据类型将数据存储在字符串中。最常见的字符数据类型是 VARCHAR2。

VARCHAR2 是可变长度的字符数据类型。当使用 VARCHAR2 列创建表时，可以减少空间消耗。

KingbaseES 数据库中可以通过参数 CHAR_DEFAULT_TYPE 设置字符串类型的长度单位为

CHAR 或 BYTE, 默认为字符 。

PS字符集：字符值对应的字符编码 ！

数据库字符集是在数据库创建时建立的。KingbaseES 字符集有 GBK、UTF-8、EUC_CN 等。

[编码的发展历程]

  • 美国： ASCLL => 大小写字母、小写字母、数字、一些符号，共计128个；
  • 欧洲：ISO 8859-1 => 在ASCLL 基础上，扩充了一些希腊字符等，共计256个。
  • 中国（国标）：GB2312 => 继续扩充，收录了6763个常用汉字、682个字符。
  • （国标扩充）GBK =>收录的汉字和符号达到了 20000+，支持繁体中文。
  • 万国码 UTF-8 => 万国码，包含世界上所有语言的所有文字和符号。(很常用)！

与 VARCHAR2 相反，CHAR 是存储固定长度的字符串。当您创建带有 CHAR 字符类型列的表时，需要固定字符串长度，CHAR 字符串长度默认为 1 个字符。数据库使用空格将值填充到指定的长度。KingbaseES 数据库中，NCHAR、NVARCHAR2 与 CHAR、VARCHAR2 的含义相同！

3.2 数值数据类型

  • 数字类型

NUMBER 数据类型存储定点数和浮点数。数据库可以存储几乎任意数量级的数字。这些数据能在不同操作系统中运行的 KingbaseES 数据库之间进行移植。所以对于必须存储数字数据的大多数情况，都建议使用 NUMBER 数据类型。

使用 NUMBER(精度，标度)；
精度：指定总位数。如果未指定精度，则列存储的值与程序提供的值完全一致，而不进行任何舍入。
标度：指定从小数点到最低位有效数字的位数。

  • 浮点数类型

为浮点数提供数字数据类型有：FLOAT 和 DOUBLE。这些类型支持 NUMBER 数据类型提供的所有基本功能。但是 NUMBER 类型使用十进制精度计算，BFLOAT 和 DOUBLE 使用二进制精度计算，这使得算术计算更快并且降低了存储需求。FLOAT 和 DOUBLE 是近似数值数据类型。它们存储小数值的近似数，而不是精确数。

3.3 日期时间数据类型

日期时间数据类型为 DATE、TIMESTAMP 和 DATETIME。

  • DATE 数据类型

DATE 类型存储日期和时间。虽然日期时间可以用字符或数字数据类型表示，但 DATE 具有特殊的关联属性。

日期存储在固定长度字段中，对应 世纪、年、月、日、小时、分钟和秒。

数据库根据指定的格式显示日期,以 24 小时格式存储时间 (HH24:MI:SS), 如果没有输入时间部分，那么默认情况下，DATE 字段中的时间为 00:00:00。

  • TIMESTAMP 数据类型

TIMESTAMP 和 DATATIME 数据类型是 DATE 数据类型的扩展。除了存储在 DATE 数据类型中的信息外，TIMESTAMP 还存储分秒。TIMESTAMP 数据类型用于存储精确的时间值。这种数据类型对于需要使用跨地理区域的日期信息的业务程序很有用。

3.4 格式化模型

ps: 就是指 数据库中，数据类型的格式化和转换 的函数！

格式化模型是描述存储在字符串中的日期时间或数字数据格式的字符文字。格式化模型不会改变数据库内部值的含义。当将字符串转换为日期或数字时，格式模型将确定数据库如何显示字符串。

在 SQL 中，您可以使用格式化模型作为 TO_CHAR 和 **TO_DATE **函数的参数来格式化要从数据库返回的值或格式化要存储在数据库中的值。

四、KingbaseES内置函数

3.1 数学函数

-- KingbaseES 数学函数 -------- 返回大于或等于 n 的最小整数。
SELECT CEIL(-14.2354)
-- 返回等于或小于 n 的最大整数。
SELECT FLOOR(-42.3);
-- 取 0<=v<=1之间的小数
SELECT RAND(); 
-- 如果我现在想取i<=R<=j之间的整数R  公式  SELECT FLOOR(i + RAND()*(j-i+1));
-- 5-20之间的随机整数
SELECT FLOOR(5 + RAND()*16)
-- 生成范围 0.0 <= x < 1.0 中的随机值。
SELECT random()-- 聚合函数/分析函数
SELECT count(*) Total  FROM departments d -- 返回 y 除以 x 的整数商。
SELECT div(9,4);
-- mod(y,x) 返回 y 除以 n 的余数。n 如果为 0，则返回 y。 
SELECT MOD(11,4)
-- remainder(n1 number, n2 number) 计算 n1 除以 n2 的余数。
SELECT remainder(3.5, 2);--SCALE 返回参数的精度（小数点后的位数）。
SELECT scale(8.41);-- 四舍五入
SELECT round(12.1245,3);

  • 随机生成id ： SELECT SYS_GUID();

  • 字段求和并且转换类型

-- 1.通过 SUN(字段) 函数对字段进行求和
-- 2.然后 通过CAST( 数据 AS 转换类型) 对数据类型进行转换
CAST(SUM(字段) AS DECIMAL(20.2));
-- 这里的  DECIMAL(20.2) 类型 对应sqlserver中的 NUMERIC(20.2)类型！ 总共有 20 位有效数字，其中有 2 位小数。-- 人大金仓数据库中同样支持 NUMERIC 类型
CAST(SUM(字段) AS NUMERIC(20.2))

3.2 字符串函数

SELECT * FROM "departments" d 
-- KingbaseES 数学函数 -------- 返回大于或等于 n 的最小整数。
SELECT CEIL(-14.2354)
-- 返回等于或小于 n 的最大整数。
SELECT FLOOR(-42.3);-- 聚合函数/分析函数
SELECT count(*) Total  FROM departments d -- 返回 y 除以 x 的整数商。 
SELECT div(9,4);
-- mod(y,x) 返回 y 除以 n 的余数。n 如果为 0，则返回 y。 
SELECT MOD(11,4)
-- remainder(n1 number, n2 number) 计算 n1 除以 n2 的余数。
SELECT remainder(3.5, 2);-- SCALE 返回参数的精度（小数点后的位数）。
SELECT scale(8.41);
-- 生成范围 0.0 <= x < 1.0 中的随机值。
SELECT random()SELECT * FROM "departments" d ;
SELECT * FROM "employees";
-- KingbaseES 字符串函数 -------- 字符串处理函数--
-- 返回 string 中的位数。
SELECT bit_length('jose');
-- 字符串函数，返回字符串 string 的字符数。
SELECT char_length('hello');
SELECT character_length('hello');
SELECT LENGTH('CANDIDE');-- SELECT charindex('A', 'B', 5);返回A,B中的位置，从 5 位置开始查找，小于等于 0 或者为空时默认为 1。不存在则返回 0。
SELECT charindex('bar', 'foobarbar');
SELECT charindex('bar', 'foobarbar',5);
SELECT instr('bcaaaaabbc', 'a');-- 函数从一个任意的数字表达式列表里选取最大的数值。
SELECT greatest('abc','abb','aba','bbb');
-- least 函数从一个任意的数字表达式列表中选取最小的值
select least(3,5,9,2,1);-- 字符串函数，串接所有参数的文本表示。NULL 参数被忽略
SELECT concat('abcde',2, NULL, 22);
-- 将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。
SELECT concat_ws(',','abcde', 2,NULL, 22);-- 返回字符串中的前 n 个字符。当 n 为负时，将返回除了最后 |n | 个字符之外的所有字符。
select left('abcde',2);
-- 获取给定字符串的从右向左数给定位数的子字符串。
select right('abcde',2);-- 从字符串包含的所有字符的左端删除 set。如果不指定 set，则默认为一个空白。
SELECT ltrim(' dfd ');-- 函数返回将给定的字符串重复给定的次数后
SELECT REPEAT('abc', 3);-- 函数返回将指定字符串反转后的新字符串。
SELECT REVERSE('ABCDEF');-- 返回字符串 expr1 中，第一次出现 expr2 字符串的起始位置
SELECT STRPOS('abcdefgb','de');-- [字符串判断相关] --
-- 如果为true，返回 T 作为结果，否则返回 F。
SELECT if(TRUE,'T', 'F') result
-- 不是 NULL，IFNULL 返回 “expr1“。否则它返回 “expr2“
SELECT ifnull(NULL,'exp2');
SELECT ISNULL(NULL, TO_DATE('05 Dec 2000', 'DD Mon YYYY')) AS RESULT2
-- NULLIF 比较 expr1 和 expr2。如果它们相等，则函数返回 null。如果它们不相等，则函数返回 expr1。
SELECT nullif('expr1', 'expr1')
-- 如果表达式 expr1 不为空，返回表达式 expr1; 如果 expr1 为空，返回表达式 expr2。
select nvl('a', 4) 
-- NVL2 允许根据指定表达式是否为空来确定查询返回的值。如果表达式 expr1 不为空，返回表达式 expr2, 如果expr1 为空，返回表达式 expr3。
select nvl2('a', 4, 5);-- [字符串转换] --
-- 每个单词的第一个字母大写，所有其他字母小写。
SELECT initcap('hi THOMAS');
-- 字符串转为小写
SELECT LCASE('AAFcdEF');
SELECT LOWER('MR. SCOTT MCMILLAN')
-- 将字符串转换成大写形式。
select ucase('abcdefG5');-- 将二进制数据 data 编码成一个文本表达。 支持的格式有：base64 、hex、escape。escape
SELECT encode('123000001','base64');
-- 把十六进制字符串转换为相应的字节串。
SELECT hextoraw('abcd');-- 【分组聚合函数】 string_agg(name,',') 
SELECT * FROM employees;
SELECT department_id,STRING_AGG(employee_name,',') FROM employees  GROUP BY "department_id";-- 字符串替换
SELECT REPLACE('Hello   World', ' ', '') AS NoSpaces;-- 字符串截取
SELECT SUBSTR(SYSDATE(),1,4)  -- 截取时间中的年份！
  • 去字符串空格函数：
-- 去除两边空格
SELECT TRIM('   Hello World   ') AS TrimmedString;
-- 去除左边空格
SELECT LTRIM('  hi,你好   ');
-- 去除末尾空格
SELECT RTRIM('  hi,你好   ');
-- 去除中间空格数据
SELECT REPLACE('Hello   World', ' ', '') AS NoSpaces;-- 去除末尾空格后在计算长度！
SELECT LENGTH(LTRIM(' hi,你好 '));
  • 判空函数 ISNULL、IFNULL 的使用

注意：在人大金仓中 ISNULL、IFNULL 两个函数都必须携带两个参数才能使用！

-- 判断表达式是否为NULL，为NULL返回1，否则返回0。
SELECT ISNULL(null,3);
SELECT IFNULL(NULL,3);

ps: 注意一个坑点：

SELECT ISNULL(NULL,‘’)，IFNULL(‘’,‘’);
当使用判空函数时，第二位数据为’’ 时，得出的结果就是 NULL值！ 这和达梦数据库有所区别！
在人大金仓数据库中(不确定是本机中数据库设置的原因还因为人大金仓数据库默认的)！

在使用 IF 条件循环语句时需要注意这点 :

DECLARE p_sqlwhere VARCHAR(20) :=''; --#{sqlwherenew}
BEGINSELECT  IFNULL(p_sqlwhere,'') AS "结果11！";SELECT  TRIM(IFNULL(p_sqlwhere,'')) AS "结果22！";	SELECT  IFNULL('','') AS "结果33！";IF TRIM(IFNULL(p_sqlwhere,'')) = '' THEN SELECT 'VVVV';ELSESELECT 'XXXX';END IF; 
END;-- 这里无论我的 p_sqlwhere 变量取任何值，这个循环永远都是走的 ELSE 部分！
-- 因为当 IFNULL(xx,'') 进行判空时，如果为null 最终值不会是''，而是null！ 这导致在 IF 判断条件处是永远不等于''的
-- 正确做法是 使用 p_sqlwhere IS NOT NULL 进行判断！

3.3 类型转换函数

-- CAST函数,用于将一个表达式转换成指定的数据类型。
CAST(EXPRESSION AS target_data_type)
SELECT CAST ('123.45' AS FLOAT);-- 字符串转换为浮点数
SELECT CAST (100 AS VARCHAR); --整数转换为字符串-- 使用 :: 操作符进行类型转换，简洁明了
SELECT '123' :: INTEGER AS int_value;     -- 将整数转换为字符串
SELECT '123.45' :: FLOAT;      -- 将字符串转换为整数-- GOUND(目标数,小数位数) : 四舍五入函数；
SELECT ROUND(123.124,2);
-- TRUNCATE():截断小数，不进行四舍五入；
SELECT TRUNC(123.4567, 2);      -- 输出 123.45-- ABS():取绝对值
SELECT abs(-1234);
-- sign( ):返回一个数值的符号，-1 表示负数，0 表示零，1 表示正数。
SELECT SIGN(-100);-- 日期格式化
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS');
  • IIF 判断函数的替换

达梦数据库和人大金仓数据库中，IIF() 函数并不存在（存在 if(条件,‘true值’, ‘false值’)函数）。也可以使用** CASE 语句**来实现条件判断。CASE 是 SQL 标准的一部分，因此在多种数据库中都适用。

示例代码：

SELECT CASE--达梦数据库中支持 1为true  0为false WHEN COUNT(1)>0 THEN 'true' ELSE 'false' END  AS RESULT 
FROM ACT_RU_TASK WHERE ID_='004236ae-fd6b-11ec-b195-ecb1d7b4def0';

ps:

CASE 语句的 WHEN 子句要求条件是布尔类型的表达式，人大金仓数据库中，不支持 1为TRUE，0为FALSE ，所有不能直接使用 COUNT() 函数的返回值。因此，你需要将 COUNT() 函数的结果与 0 进行比较，而不是直接将其作为条件。

3.4 日期函数

-- ===【时间相关函数】--
SELECT current_date();
SELECT NOW();
SELECT SYSDATE();-- 获取当前年度
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS CurrentYear;
-- 获取上一年度
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP - INTERVAL '1' YEAR) AS PreviousYear;-- 时间类型转换操作、时间格式化
SELECT to_char(now(),'yyyy'); 
SELECT to_char(SYSDATE,'YYYYMMDD');
SELECT to_char(CURRENT_TIMESTAMP,'YYYYMMDD');
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS');-- EXTRACT 从时间中提取数据：
SELECT CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS INTEGER)

3.5 其它

  • 关于字符串的拼接方式

在KES数据库的SQL语句中，数据库之间进行拼接时，不能像 SQLSERVER 数据库一样直接使用 + ' '+ 的形式来拼接，而是采用 || 来进行拼接：

select gz.GSDM, gz.LBMC-- 不支持直接使用 + 的形式来拼接数据-- , (gz.LBDM + ' ' + gz.LBMC)                        as LB, (gz.LBDM || gz.LBMC)                             as LB, (gz.LBDM || ' ' ||  gz.LBMC)                     as LB2, cast(sum(gz.SJTS) as NUMERIC(20, 2))             as SJTS
from GZ_GZDATA gz
group by gz.GSDM, gz.LBDM, gz.LBMC, gz.FFND, gz.FFYF, gz.FFYCS
  • 将查询结果中的值合并为一个字符串

使用函数 LISTAGG(字段名称，' 连接方式 '); 类似sqlserver中的 FOR XML PATH('') 语法

动态SQL的实现：

不支持像 SQL Server 那样直接进行跨数据库查询！

不支持 EXECUTE IMMEDIATE 直接获取并输出结果集！

五、表的管理

要在所属模式中创建新表，需要有 CREATE TABLE 数据库权限；要在其它用户的模式中创建新表，则需要有 CREATE ANY TABLE 数据库权限。

  • 创建表
    创建表时，如果给定了模式名，那么该表就会被创建在指定的模式中，否则它被创建在当前模式中。
    创建表格到数据库的指定模式之中，同时指定表所存储的表空间示例：

5.1 临时表 TEMPORARY_TABLE

临时表在需要缓冲结果集中很有用，因为它是通过运行多个 DML 操作构建的。

临时表数据仅在事务或 会话期间存在。临时表中的数据是会话私有的。每个会话只能看到和修改自己的数据。

可以创建全局临时表 GLOBAL ( 表定义是持久的，表数据是临时的 ) 或者 局部临时表 LOCAL(默认，表定义和表数据都是临时的，会在会话退出后被删除）。简单来看全局临时表定义是持久的(除非显示的去删除它)，而表数据是临时的，而本地临时表的表定义和表数据都是临时的，会在会话退出后被删除。

  • 临时表事务控制
    临时表在一个事务块结束时的行为由** ON COMMIT **控制。三种选项：
  1. PRESERVE ROWS
    表示在事务提交时，临时表中的数据将保持不变，直到会话结束。是本地临时表采用的默认行为。

适用于需要在同一会话中多次使用数据且想要保留数据的场景。这样一来，即使多次提交事务，数据依然可以被访问。

  1. DELETE ROWS
    在每个事务块结束时，将删除临时表中的所有行数据。这是全局临时表的默认行为。

适用于需要在每个事务中处理数据但不需要在事务间保留数据的场景。即每个事务独立使用数据。

  1. DROP
    在当前事务块结束时将删除临时表。
  • 创建全局临时表

全局临时表是存储在磁盘上的永久数据对象。全局临时表的定于对所有会话都是可见的，但是全局临时表中的数据

只对向表中插入数据的会话可见。

语法;CREATE GLOBAL TEMPORARY/TEMP TABLE....

示例：

  • 创建局部临时表

局部临时表的元数据喝内容仅在创建该临时表的会话中可见。

使用 CREATE LOCAL TEMPORARY/TEMP TABLE语句创建一个局部临时表。 LOCAL 可以不指定，因为默认创建 TEMPORARY 就是局部临时表。

5.2 修改表操作（略）

  • 增加列操作

新增列时，新列的值将会被默认值填充。如果没有指定 DEFAAULT 子句，则填充空值。

-- 增加列操作
ALTER TABLE new_emp ADD COLUMN description TEXT;
-- 增加列，添加约束条件
ALTER TABLE new_emp ADD COLUMN description3 TEXT CHECK (description3 <> NULL);
  • 移除列操作

移除列操作会将列中的数据 和 涉及到该列的表约束也会移除。

ALTER TABLE new_emp DROP COLUMN description2;

如果该列被另一个表的外键所引用，King-baseEs 不会移除该约束。我们可以通过 CASCADE 来授权移除任何依赖删除列的所有东西；

ALTER TABLE new_emp DROP COLUMN description CASCADE;

  • 修改列的默认值 : ALTER TABLE new_emp ALTER COLUMN temp1 SET DEFAULT '这是设置的默认值！';
  • 修改列的数据类型：

只用当列中的每一个项都能转换为新的类型时该操作才能成功！

ALTER TABLE new_emp ALTER COLUMN temp1 TYPE number(10,2);

  • 重命名列：ALTER TABLE new_emp RENAME COLUMN temp1 TO new_temp;
  • 重命名表名称： ALTER TABLE new_emp RENAME TO temp_table;
  • 删除表：DROP TABLE new2_emp;
  • 快速删除表中的所有记录 : TRUNCATE TABLE table_name;

关于Sql编辑的操作中，在数据库开发管理工具中存"SQL 模板" 管理工具：

5.3 其它表相关操作

  • 使用 CREATE TABLE AS 语句

CREATE TABLE AS 创建一个表，并且由一个 SELECT 命令计算出来的数据填充该表。该表的列具有和SELECT 的输出列相关的名称和数据类型(不过可以通过给出要给显式的新列名列表来覆盖这些列名)。

  • 使用 INSERT 语句加载表数据

INSERT 语句允许您通过指定列值 或者 指定从另一个现有表选择数据的子查询向表中添加行。

  • 表的数据视图
    KingBaseES提供了一组兼容 Oracle 的视图，可以退查询数据字典来获取表信息。
视图描述
DBA_TABLES
ALL_TABLES
USER_TABLES		DBA ：描述数据库中所有关系表；
ALL：描述用户可以访问的所有表；
USER：仅限于用户拥有的表。
DBA_TAB_COLUMNS
ALL_TAB_COLUMNS
USER_TAB_COLUMNS
描述当前所在数据库中，所有表、视图的非隐藏列信息
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS		描述表、视图 的注释信息。
DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS		描述表、视图 对应的列 的注释信息。

六、索引(INDEX)

创建索引会增加数据库系统开销，创建数据库索引注意一下几点：

  • 经常用于查询的字段创建索引
  • 经常用于连接的字段创建索引
  • 经常需要根据范围来查询的列上创建索引
  • 尽量不要在数据量很少的表上创建索引
  • 尽量不要在数据取值区分度很小的列上创建缩影，如“性别”；

KingbaseEs 数据库提供了几种功能的索引方案：

  • Btree索引，使用Btree结构来存储索引数据，可以处理等值查询和范围查询，还可用于查询结果集排序。默认也是最常用的索引。
  • Hash索引，基于Hash表实现，只能用于等值查询。
  • GiST索引，一种平衡的树形结构访问方法，其适用于多维数据和集合数据类型。
  • SP-GiST索引，作为一种基础模板来实现多种搜索方法。
  • GIN索引，一般用其搜索全文或JSON键值。
  • BRIN索引，用于存储连续相邻的数据块统计信息，可减少索引占用空间

6.1 创建索引

创建索引需要用户在自己的模式中创建索引，同时被索引的表上有 CREATE INDEX 权限。

  • 显式创建索引：

使用 CREATE INDEX 显式的创建索引，并可以将索引存放在指定的 表空间中。

CREATE INDEX 索引名称 ON 表名(字段名)
[TABLESPACE 表空间];
  • 创建唯一索引：

唯一索引保证表中没有两行在键列中有重复的值。使用 CREATE UNIQUE INDEX 语句创建唯一索引：CREATE UNIQUE INDEX index_name ON table_name(column_name)

  • 创建指定顺序的 B树索引：

默认情况 下，CREATE INDEX 命令创建适合于大部分情况的 B-tree 索引。

在KingbaseEs当前支持的索引类型中，只有 B-tree 可以产生排序后的输出。B-tree 索引默认将表项以升序方式存储，并将空值放在最后。我们可以在创建 B-tree 索引时通过下列选项来改变索引的排序

  1. ASC：指定上升排序（默认）。
  2. DESC：指定下降排序。
  3. NULLS FIRST，指定把空值排序在非空值前面。在指定 DESC 时，这是默认行为。
  4. NULLS LAST，指定把空值排序在非空值后面。在没有指定 DESC 时，这是默认行为。

  • 创建 Hash 索引

Hash索引只能处理简单等值比较。当索引涉及到一个使用了 = 操作符比较时，查询规划器考虑使用一个Hash索引。

CREATE INDEX index_name ON table_name USING HASH (column);

  • 创建基于函数的索引

基于函数的索引时将索引建立在某个函数或者表达式的基础上。一个索引列并不一定要是底层表的一个列，也可以是从表的一列或者是多列计算而来的一个函数或者标量表达式。

CREATE INDEX index_name ON table_name(lower(column1));

  • 创建多列索引

一个索引可以定义在表的多个列上。 如果经常根据多列的相交(通常为多个and操作) 或者联合操作(通常为多个 or 操作) 等做查询时，就可以在对应的表上创建多列索引。

相比于在每个列上创建索引，多列索引占用的磁盘更少，索引数据更新时也更快。

目前，只有 B-tree、Bitmap、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定 512 个列。

  • 创建本地和全局索引

在分区表上创建的索引可分为全局索引和本地索引。
全局索引：包括全局非分区索引（Global Nonpartitioned Indexes）和全局分区索引（Global Partitioned Indexes），是指与分区表有不同分区方式的索引，它是在分区表的所有分区数据基础上创建的索引。

本地索引：（本地分区索引，Local Partitioned Indexes），是指在每个表分区上单独创建的索引，是一种局部索引，也是一种分区索引，某一个索引分区只能索引到一个表分区。

6.2 索引的其它操作

  • 索引的视图（字典表）
视图描述
DBA_INDEXES ALL_INDEXES USER_INDEXESDBA视图会显示数据库中所有表上的索引信息。 ALL 视图显示用户可以访问的所有表上的索引信息。 USER 视图仅限于用户拥有的索引。
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS这些视图显示表上索引列的信息。
  • 修改索引操作

可以使用 ALTER INDEX 或者 REINDEX 语句修改索引，两者的使用场景有区别。

ALTER INDEX 用于更改一个现有索引的属性定义，如修改索引名称，变更表空间等。而 REINDEX 则根据表重建一个索引，并且替换旧索引。

  • 删除索引

使用 DROP INDEXS语句删除索引，但是执行该命令你必须是该索引的拥有者。

DROP INDEX index_name;

删除表也能实现删除所有，将自动删除了所有与该表相关的索引。

6.3 索引管理指南

  1. 在插入数据后在创建索引

一般情况下，在插入或者加载数据之后，再为表创建索引会更有效率。因为数据库必须在插入每一行数据时更新索引，导致插入效率降低。

  1. 适合创建索引的表和列：

若干适合表和列创建索引的指导原则，可以通过下面原则来决定合适为表创建索引：

  • 如果需要经常地检索大表中的少量的行，就为查询键创建索引；

  • 为了改善多个表的连接的性能，可为连接列创建索引；

  • 主键和唯一键自动具有索引，在外键上很多情况下也创建索引；

    选择表中的索引列时可以参考以下几点原则，越多符合越适合作为索引列：

  • 列中的值相对比较唯一；

  • 取值范围大，适合建立索引 (适合普通索引)；

  • 列包含许多空值，但是查询通常会选择所有具有值的行。

反之，以下情况不应创建索引

  • 如果该表频繁的进行 DML 操作，不应建立索引，或者建立少量索引；
  • 有很多重复值的列，一般不建议作为索引列；
  • 太小的表，不用建立索引，也没有必要。

  1. 排序索引列改善性能

在CREATE INDEX 语句中列的排序会影响查询的性能，通常将最常用的列放在最前面。

查询中有多个字段时，应创建组合索引，组合索引中当两个或者多个字段是等值查询时，索引列前后关系就无关紧要。

  1. 限制每个表的索引数量

表的索引数量是没有限制的，但是索引越多，修改表数据的开销就越大，占用的磁盘空间也越大。当插入或删除行时，表上的所有索引都要被更改；更改一个列时，包含该列的所有索引也要被更改。

  1. 删除不再需要的索引

当不在需要索引时，应及时将其清除。一方面能避免修改表数据的额外索引开销，另一方面也能减少 磁盘空间占用。

七、视图(VIEW)

使用视图的目的是为了方便用户访问基表数据，更重要的是确保用户对基表的安全访问。基表可以是实际的表，也可以是其它视图。

7.1 创建视图

在模式中创建视图，必须要具有CREATE VIEW权限。如果在其它模式中创建视图，必须具有 CREATE ANY VIEW 系统权限。

视图的功能依赖于视图所有者的权限，比如所有者只有test表的INSERT权限，那么视图就只能向test表中插入新行，而不能用于SELECT、UPDATE、DELETE。

  • 创建视图

通过 CREATE VIEW语句创建视图。示例：

-- 创建视图
CREATE VIEW student_age_view AS 
SELECT student_id,student_name,gender,age
FROM student 
WHERE age>20 AND gender='男'
-- 可选 用来限定DML操作，并且根据当前视图的WHERE 条件进行限定
[WITH LOCAL CHECK OPTION;] -- 向视图插入数据
INSERT INTO student_age_view VALUES (1726,'王猛','男',21);
INSERT INTO student_age_view VALUES (1739,'王萌','女',19);
SELECT * FROM student_age_view;
  • 创建连接视图

可以在 CREATE VIEW 语句的 FROM 子句中指定多个基表或视图创建。从而简化连接查询。示例：

-- 创建连接视图
CREATE VIEW view2 AS 
SELECT  emp.employee_name,emp.hire_date, dept.department_name,dept.location
FROM employees emp INNER JOIN departments dept
ON emp.department_id = dept.department_idSELECT * FROM view2;

如果在视图中使用了GROUP BY子句和DISTINCT关键字，则将不能通过该视图 进行DML 相关操作。 不来自单表或单视图的视图不能自动更新。

  • 创建 FORCE 视图

如果 CREATE VIEW 语句带有 FORCE 关键字，意味着无论视图的依赖检查是否成功，只要语句没有语法错误，数据库也会创建该视图。如果依赖检查成功则为有效视图，否则创建出来的就是无效视图。

比如视图中引用了一个不存在的表或现有表中的无效列，或者视图所有者没有所需的权限时，数据库依然能创建该视图并将其保存到数据字典中。只是该视图还不能使用。CREATE FORCE VIEW AS ...;

7.2 视图数据字典

与视图相关的数据字典有三个：

  • USER_VIEWS，当前用户有关的视图信息
  • DBA_VIEWS，系统中所有的视图信息
  • ALL_VIEWS，当前用户可以访问的所有视图信息

查询指定视图中，创建视图时使用的 SELECT 语句数据，存储在表中的 text 字段中：

7.3 视图的管理操作

  • 替换视图：

可以通过删除视图并重新创建视图，或者通过执行带有 OR REPLACE 子句的 CREATE VIEW 语句实现替换现有的视图定义：

CREATE OR REPLACE VIEW view_name AS ......

  • 视图的操作语句(DML)

在KingbaseES中可以在视图上执行数据库操作语句(DML)相关的操作，但是有一些限制条件。

要对视图执行查询 或者 INSERT、UPDATE 或 DELETE 语句。必须显式地或通过角色分别为视图拥有 对应的对象权限。同时还需要满足以下限制：

  1. 如果视图是包含了 DISTINCET 操作符、GROUP BY 子句、HAVING、LIMIT 或 OFFSET 的查询定义，则不能使用该视图向基表中插入、更新或者删除操作。
  2. 如果视图是用 WTTH CHECK OPTION 定义的，则查询必须满足一定条件，如果视图不能从基表中选择行，则不能使用视图向基表插入或者更新行。
  3. 如果不包含DEFAULT 子句的NOT NULL 列在视图中的新数据不满足该约束，则不能使用视图将行插入到基表中。
  4. 视图的选择不能包含任何聚合、窗口函数或者集合返回函数。
  • 修改视图

使用 ALTER VIEW 语句只能显式地重新编译无效的视图。但是前提条件是使用 ALTER VIEW 语句，视图必须在你的模式中，或者必须具有 ALTER ANY TABLE 系统权限。

  • 删除视图

使用 DROP VIEW view_name; 删除指定名称的视图。

可以删除模式中包含的任何视图，如果要删除其它用户模式中的视图，就必须具有 DROP ANY VIEW 系统权限。

[ 过程化语言 PL\SQL 部分内容]：

八、触发器(TRIGGER)

触发器就像一个存储过程，只要发生指定事件，数据库就会自动调用它。

CREATE TRIGGER 创建一个新触发器。该触发器将被关联到指定的表或视图并且在表上发生特定操作时将执行指定的函数 “function_name“或匿名 PL/SQL 块。

8.1 DML触发器：

DML 触发器在表或视图上创建，其触发事件由 DML 语句 DELETE、INSERT 和 UPDATE 组成。

DML数据库语法如下：


CREATE [OR REPLACE] TRIGGER trigger_name
-- 指明触发器在事件发生前还是后触发
-- 指明触发器响应的操作类型
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} 
-- 指明触发器所关联的表
ON table_name
-- 指明触发器是行级触发器。如果省略，则为语句级触发器。
[FOR EACH ROW]
-- 可选，指定触发器的条件，只有在条件满足时才会触发。
[WHEN (condition)]
BEGIN-- 触发器逻辑
END;

行级触发器（Row-level Trigger）：

当相关的 DML（数据操纵语言）操作（如 INSERT、UPDATE 或 DELETE）影响到表中的一行时，触发器会为该行执行一次。这意味着对于每一被影响的行，触发器逻辑都会被执行。在这种情况下，可以访问和使用 NEW 和 OLD 伪记录来引用新行和旧行的数据。

语句级触发器（Statement-level Trigger）：语句级触发器只在 DML 语句执行时触发一次，无论该操作影响了多少行。

  • 触发器触发顺序

如果为同一张表上的同一语句定义了两个或多个具有不同时间点的触发器，则它们按以下顺序触发：

  1. 所有 BEFORE STATEMENT 触发器
  2. 所有 BEFORE EACH ROW 触发器
  3. 所有 AFTER EACH ROW 触发器
  4. 所有 AFTER STATEMENT 触发器

示例：

ps : **：NEW** 和 **:OLD**** **的作用：

  1. NEW 只出现在 INSERT 和 UPDATE 时；在 INSERT 时 NEW 表示新插入的行数据，UPDATE 时 NEW 表示要替换的新数据。
  2. OLD 只出现在 UPDATE 和 DELETE 时；在UPDATE 时 OLD 表示要被更改的原数据行，DELETE 时 OLD 表示要被删除的数据。

8.2 触发器的管理

  1. 触发器信息视图：SELECT * FROM USER_TRIGGERS;
  2. 启用 / 禁用 触发器： ALTER TRIGGER [schema.]tirgger_name ENABLE / DISABLE;
  3. 要启用或禁用在特定表上创建的所有版本中的所有触发器，请使用以下语句:ALTER TABLE table_name {ENABLE | DISHABLE} ALL TRIGGER;
  4. 更新触发器：CREATE OR REPLACE TRIGGER....
  5. 删除触发器：DROP TRIGGER trigger_name;

九、变量的声明与使用

9.1 KES 变量的使用

在人大金仓数据库中，创建和使用变量的语法通常是与 PL/SQL 相似的。语法模板如下：

DECLARE variable1  VARCHAR(20) ; [ variable2  INT; ][ variable3  INT:= 5]  -- 直接赋值 -- 另一种不常用的方式 (直接声明一个和对应表的对应字段相同类型的变量！)[ 变量名称v3 表名.字段名%TYPE;]
BEGINvariable1 := 变量赋值;[variable1 := 变量赋值;]-- 输出变量的值... 其它Sql语句中使用 INTO 关键字给变量进行赋值...RAISE NOTICE 'The value of num is %', variable1; 
END

在sql中 对声明的变量进行赋值时，采用的是SELECT … INTO 关键字 来进行赋值； 或者是直接使用 := 对变量进行赋值；示例：

示例代码二：

DECLARE P_DATE VARCHAR(20) :='';p_boole int :=1;
BEGINIF P_DATE IS NULL OR P_DATE ='' THENP_DATE := REPLACE(TO_CHAR(SYSDATE,'YYYYMMDD'),'-','');END IF;SELECT P_DATE AS "TIME",CASE WHEN p_boole THEN '值为1' ELSE '值为2' END AS "判断值";
END;

9.2 PL\SQL(KES)异常捕获

BEGIN-- 语句块中的异常捕获EXCEPTION --处理没有数据找到的异常！WHEN NO_DATA_FOUND THEN  [执行其它SQl.....]--处理其它异常WHEN OTHERS THEN --重新抛出异常，或返回一个特定的错误值RAISE; 
END

9.3 达梦数据库中事务的使用

事务控制语句管理 DML 语句所做的更改， 和将多个 DML 语句按事务分组。

事务控制语句可以执行以下任务：

- <font style="color:black;">使一个事务的更改持久化（COMMIT）。</font>
- <font style="color:black;">撤消在一个事务中自事务开始以来的更改（ROLLBACK） ，或自一个保存点以来的更改(ROLLBACK TO SAVEPOINT)。 保存点是在一个事务上下文中由用户声明的中间标记。</font>
- <font style="color:black;">设置一个你可以回滚到的点（SAVEPOINT）。</font>
- <font style="color:black;">设定一个事务的特性（SET TRANSACTION） 。</font>
- <font style="color:black;">指定是在每个 DML 语句之后、 还是在事务时提交后，执行可延迟完整性约束检查（SET CONSTRAINT）。</font>

--  创建测试表格
CREATE TABLE orders
(orderid INT4 CONSTRAINT pk_orders PRIMARY KEY,customerid VARCHAR(10) NOT NULL,employeeid INT4,orderdate DATE
);-- 来开始一个事务，所有后续的操作将被视为一个单元。
BEGIN;
-- 插入第一条数据
INSERT INTO orders(orderid,customerid,orderdate) VALUES(99,'KINGBASE','2000-01-01');-- 设置一个回滚点
SAVEPOINT my_savepoint;
-- 事务中插入数据
INSERT INTO orders(orderid,customerid,orderdate) VALUES(102,'KINGBASE','2000-03-01');
-- 回滚到指定事务点
ROLLBACK TO SAVEPOINT my_savepoint;
-- 提交事务
COMMIT;
SELECT * FROM orders;

示例代码：

-- 事务开始
BEGIN;-- 设置一个回滚点SAVEPOINT my_savepoint;-- 插入第一条数据INSERT INTO orders(orderid,customerid,orderdate) VALUES(123,'KINGBASE','2000-01-01');-- 设置一个回滚点SAVEPOINT my_two_savepoint;-- 插入第二条数据INSERT INTO orders(orderid,customerid,orderdate) VALUES(456,'KINGBASE','2000-01-01');ROLLBACK TO SAVEPOINT my_two_savepoint;
COMMIT;SELECT * FROM orders;

注：在 Kingbase 数据库中，如果你在 PL/SQL 块中使用 BEGIN … END; 结构，那么在这个块内部就不能再使用 BEGIN; 、SAVEPOINT… 、ROLLBACK TO… 语句来开启新的事务，PL/SQL中不支持的事务命令

你必须在块外部统一管理事务。在 PL/SQL 块中所有的 SQL 操作都在一个事务上下文中！

ROLLBACK TO 的使用是有限制的，通常我们在处理异常时并不需要使用 ROLLBACK TO，因为整个事务在异常发生时会自动回滚到游标的起始状态。因此，简单的 ROLLBACK 在异常捕获时更为恰当。

示例代码：


DECLARE p_hasaccesstoken INT := 0;p_accesstoken VARCHAR2(1000);p_accesstoken_date VARCHAR2(30);v_tran_error INT:=0;v_errmsg VARCHAR(500) := '';
BEGIN--设置传入参数数据：--accesstoken := :accesstoken; --accesstoken_date := :accesstoken_date;p_accesstoken := '123';p_accesstoken_date := '123456';BEGIN-- 任何操作前设置保存点（kES 中的 PL/SQL中不在支持的事务命令）-- SAVEPOINT sp1;SELECT COUNT(1) INTO p_hasaccesstoken FROM WXSP_GZZD_ACCESSTOKEN WHERE THREELOGINTYPE = 'wxqyh';IF p_hasaccesstoken <=0 THENINSERT INTO WXSP_GZZD_ACCESSTOKEN (ID,ACCESSTOKEN,ACCESSTOKEN_DATE,THREELOGINTYPE) VALUES (sys_guid(),p_accesstoken,p_accesstoken_date,'wxqyh');ELSE UPDATE WXSP_GZZD_ACCESSTOKEN SET ACCESSTOKEN=p_accesstoken,ACCESSTOKEN_DATE = p_accesstoken_date WHERE THREELOGINTYPE='wxqyh';END IF;	--模拟抛出异常RAISE NO_DATA_FOUND;COMMIT;-- 异常捕获EXCEPTIONWHEN OTHERS THEN-- ROLLBACK TO cs1;  不在支持使用该方式 直接使用 ROLLBACK即可回滚所有操作！ROLLBACK;--RAISE ; --抛出捕获的异常v_tran_error := v_tran_error + 1;v_errmsg := '设置企业微信access_token发生异常！';	END;SELECT v_tran_error ERRCODE,v_errmsg ERRMSG;SELECT * FROM WXSP_GZZD_ACCESSTOKEN;
END;

9.4 数据未找到(P0002)异常处理

在 DQL 语句中使用 INTO 关键字为变量进行赋值，如下：

DECLARE v1 VARCHAR(100);
BEGINSELECT field1 INTO v1 FROM table1 WHERE ...条件...;-- 其它 DQL 语句！
END;

当使用 SELECT … INTO 语句进行变量赋值时，如果没有查询到数据，会报错并返回 “SQL 错误 [P0002]: 错误: 查询没有返回记录” 的错误(异常)信息。

而在 SQLSERVER 中，出现未查询到数据时，会默认将NULL 赋值给对应变量，而不是抛出异常信息！

为了避免这种情况，可以采取以下几种方法进行处理：

  • 使用 EXISTS 先判断数据是否存在

在执行 SELECT … INTO 之前，可以先检查数据是否存在，确保赋值时不会出现错误。示例：

DECLARE v1 VARCHAR(100);
BEGINIF EXISTS (SELECT 1 FROM table1 WHERE ...条件...) THENSELECT field1 INTO v1 FROM table1 WHERE ...条件...ELSEv1 := NULL; -- 或者给这个变量赋其他默认值END IF;-- 其它 DQL 语句！
END;
  • 使用异常处理

在 PL/SQL 块中包装查询语句，并使用异常处理来捕获没有数据的情况：

DECLARE v1 VARCHAR(100);
BEGINSELECT field1 INTO v1 FROM table1 WHERE ...条件...;-- 使用异常捕获EXCEPTION WHEN NO_DATA_FOUND THEN v1 := NULL; -- 或者给这个变量赋其他默认值-- 其它 DQL 语句！ 
END;

十、PL/SQL的控制结构

人大金仓数据库（DM Database）的 PL/SQL 语言支持多种控制结构。这些控制结构允许你使用条件语句和循环来控制程序的执行流程。以下是一些常用的控制结构的简要概述和示例：

10.1 条件控制结构

  • IF 语句

语法: 根据条件执行不同的代码块

IF condition1 THEN statements1
[ELSEIF condition2 THEN statements2;]
......
[ELSE statements;]
END IF;

示例：

  • CASE 语句

根据不同的条件值执行不同的代码块。

CASE test_value 
WHEN V1 THEN statements1
WHEN V2 THEN statements2
......
[ELSE statements]
END CASE;

示例：

方式二：

CASE WHEN  布尔类型
THEN 为真时的值
ELSE 为假时的值
END;
-- 这里末尾可以不加CASE 即可实现 CASE语句块的结束！
-- 在kingbaseEs数据库中CASE WHEN .. END 后面不支持使用 CASE，达梦数据库中可以！

示例代码2：

DECLARE P_DATE VARCHAR(20) :='';p_boole int :=1;
BEGINIF P_DATE IS NULL OR P_DATE ='' THENP_DATE := REPLACE(TO_CHAR(SYSDATE,'YYYYMMDD'),'-','');END IF;SELECT P_DATE AS "TIME",CASE WHEN p_boole THEN '值为1' ELSE '值为2' END AS "判断值";
END;

10.2 循环控制结构

  • LOOP

语法：无限循环，直至通过 EXIT 语句退出。

LOOP循环体内执行的语句;
EXIT [WHEN condition]; -- 退出循环的条件
END LOOP;

示例：

  • WHILE

语法：在条件为真时重复执行。

WHILE condition LOOP循环体内执行的语句;
END LOOP;

示例：

  • FOR 循环

语法：在一个范围内进行迭代。

FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP-- 循环体代码
END LOOP;

示例：

[退出循环操作] ：在 PL/SQL 中，循环结构会使用 EXIT 语句来退出循环。可以通过设置特定条件来控制何时退出循环。(在SQLSERVER数据库中 ，跳出循环使用的关键字为 break )！

  • 使用 LOOP … END LOOP 跳出循环！
DECLAREv_counter INT := 0;
BEGINLOOPv_counter := v_counter + 1;SELECT ('Counter: ' || v_counter);-- 当计数达到5时退出循环IF v_counter = 5 THENEXIT;  -- 退出循环END IF;END LOOP;
END;
  • 使用 WHILE … LOOP 跳出循环！
DECLAREv_counter INT := 0;
BEGINWHILE v_counter < 5 LOOPv_counter := v_counter + 1;SELECT ('Counter: ' || v_counter);-- 还可以使用 EXIT 语句退出，比如根据某个条件IF v_counter = 3 THENEXIT;  -- 强制退出循环END IF;END LOOP;
END;
  • 使用 FOR … LOOP 跳出循环！
DECLAREv_total INT := 0;
BEGINFOR i IN 1..10 LOOPv_total := v_total + i;-- 当总计达到15时退出循环IF v_total >= 15 THENEXIT;  -- 退出循环END IF;END LOOP;SELECT ('Total: ' || v_total);
END;


十一、存储过程/存储函数

11.1 存储过程(PROCEDURE)

  • 创建存储过程，语法模板
CREATE PROCEDURE procedure_name[ (parameter_name data_type [, ...]) ]
AS
BEGIN-- SQL语句-- 可以是SELECT、INSERT、UPDATE、DELETE等操作
END;

存储过程示例：

  • 存储过程信息视图： pg_proc

可以通过执行SQL查询来获取当前用户下的存储过程。以下是一个常用的查询示例：

SELECT proname AS procedure_name,proargtypes,"proargnames" ,prokind 
FROM pg_proc
WHERE pronamespace = (SELECT oidFROM pg_namespace-- 仅获取当前模式下的存储过程WHERE nspname = current_schema() 
)-- pg_proc 是存储过程的系统目录，用于存储所有存储过程的信息。
-- pg_namespace 则用于获取命名空间（schema）的信息，其中 current_schema() 函数会返回当前用户所使用的模式。
-- proname NOT LIKE 'pg_%' 是用来排除以 pg_ 开头的系统存储过程。

在人大金仓数据库（KingbaseES）中，pg_proc 表存储了所有存储过程和存储函数的信息。要区分存储函数和存储过程，可以通过 prokind 字段来判断。prokind 字段的值表示对象的类型：

  • ‘f’ 表示存储函数（function）
  • ‘p’ 表示存储过程（procedure）
  • 删除存储过程

在删除存储过程时，用户必须是该过程的拥有者。删除存储过程使用 DROP PROCEDURE 语句。

DROP PROCEDURE [IF EXISTS] procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
[ CASCADE | RESTRICT ]-- IF EXISTS 如果该过程不存在也不抛出一个错误。这种情况下会发出一个提示。
-- procedure_name 现有过程的名称（可以是被方案限定的）。如果没有指定参数列表，则该名称在其所属的方案中必须是唯一的。
-- argmode 参数的模式：IN 或者 VARIADIC。如果省略，默认为 IN。
-- argname 参数的名称。注意，其实 DROP PROCEDURE 并不在意参数名称，因为只需要参数的数据类型来确定过程的身份。
-- argtype 该过程如果有参数，参数的数据类型（可以是被方案限定的）。
-- CASCADE 自动删除依赖于该过程的对象，然后接着删除依赖于那些对象的对象（见 依赖跟踪)。
-- RESTRICT 如果有任何对象依赖于该过程，则拒绝删除它。这是默认选项。

当存储过程名称不唯一时，删除存储过程的参数类型必须指定，因为可能存在多个不同的过程具有相同名称和不同参数列表。由于存储过程的名称可能相同但参数不同，因此需要通过参数列表来区分它们。以下是删除指定存储过程的示例：

假设您有两个存储过程，名称相同但参数不同：

my_procedure(integer, text)

my_procedure(text, integer)

要删除这两个存储过程，可以使用以下命令：

  1. 删除第一个存储过程

DROP PROCEDURE my_procedure(integer, text);

  1. 删除第二个存储过程

DROP PROCEDURE my_procedure(text, integer);

11.2 存储函数(FUNCTION)

  • 创建存储函数
-- 存储函数-FUNCTION 定义模板：  
CREATE OR REPLACE FUNCTION 函数名称(参数1 IN 参数1类型,参数2 IN 参数2类型
)RETURN 返回值类型 AS-- 局部变量的声明 变量1 变量类型;
BEGIN-- 函数主体逻辑，可包含一些 SQL 查询或其它逻辑,将结果赋值给变量RETURN 变量1; --返回最终结果！-- 异常捕获模块(可以省略.... )[EXCEPTION WHEN NO_DATA_FOUND THEN  --处理没有数据找到的异常！RETURN NULL; WHEN OTHERS THEN --处理其它异常RAISE; --重新抛出异常，或返回一个特定的错误值]
END 函数名;

存储过程示例代码：

  • 查看存储函数：

在人大金仓数据库（KingbaseES）中，存储函数（Stored Functions）的信息同样存储在 pg_proc 系统视图中。pg_proc 视图包含了所有存储过程和存储函数的相关信息。

要查询存储函数的信息，可以使用与查询存储过程类似的方法。以下是一个示例查询，用于获取当前用户模式下的所有存储函数及其参数信息：

SELECTprokind,proname AS function_name,proargtypes AS arg_types_oids,proargnames AS arg_names,array_to_string(proargnames, ', ') AS arg_names_string,array_to_string(ARRAY(SELECT format_type(unnest(proargtypes), NULL)),', ') AS arg_types_string
FROMpg_proc
WHEREpronamespace = (SELECT oidFROM pg_namespaceWHERE nspname = current_schema())AND prokind = 'f';  -- 过滤出存储函数

prokind 表示对象的类型(p 存储过程、f 存储函数)

proname 是存储函数的名称。

proargtypes 是存储函数的参数类型OID数组。

proargnames 是存储函数的参数名称数组。

array_to_string(proargnames, ', ') 将参数名称数组转换为逗号分隔的字符串。

array_to_string(ARRAY(SELECT format_type(unnest(proargtypes), NULL)), ', ') 将参数类型OID数组转换为类型名称字符串。

prokind = ‘f’ 用于过滤出存储函数（f 表示函数）。

  • 删除存储函数 ：
DROP FUNCTION [ IF EXISTS ] name 
[ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] 
[, ...] 
[ CASCADE |RESTRICT ]-- IF EXISTS 如果该函数不存在则不要抛出一个错误，而是发出一个提示。
-- name 一个现有函数的名称（可以是模式限定的）。如果未指定参数列表，则该名称在其模式中必须是唯一的。
-- argmode 一个参数的模式：IN、OUT、INOUT 或者 VARIADIC。如果被忽略，则默认为 IN。注意 DROP FUNCTION 并不真正关心 OUT 参数，因为决定函数的身份时只需要输入参数。因此列出 IN、INOUT 和 VARIADIC 参数足以。
-- argname 一个参数的名称。注意 DROP FUNCTION 并不真正关心参数名称，因为决定函数的身份时只需要参数的数据类型。
-- argtype 如果函数有参数，这是函数参数的数据类型（可以是模式限定的）。
-- CASCADE 自动删除依赖于该函数的对象（例如操作符和触发器），然后删除所有依赖于那些对象的对象。
-- RESTRICT 如果有任何对象依赖于该函数，则拒绝删除它。这是默认值。

与存储过程（PROCEDURE）类似：

当存储函数名称不唯一时，删除存储函数的参数类型必须指定，因为可能存在多个不同的函数具有相同名称和不同参数列表。由于存储函数的名称可能相同但参数不同，因此需要通过参数列表来区分它们。

示例：存在两个相同名字的存储函数时，仅通过函数名称进行删除对应存储函数出现错误异常：

十二、游标(CURSOR)

游标是一种数据库对象，允许程序逐行处理SQL查询的结果集。通过游标，应用程序可以灵活、高效地访问和处理查询数据。

  • 作用：
  1. 逐行处理结果集：适用于需要逐行分析和处理的数据。
  2. 灵活的数据更新：支持对结果集进行修改、删除等操作。
  3. 复杂查询的控制流：在处理复杂逻辑时，游标可以提供更多的控制和条件处理。
  • 分类：
  1. 隐式游标：自动创建，由系统管理，适用于简单的SQL语句。
  2. 显示游标：用户自定义的游标，提供更大的灵活性和控制能力。
  • 游标的基础语法：
-- 1.创建和声明 游标
DECLARE CURSOR  cursor_name IS DQL语句;BEGIN
-- 2.打开游标OPEN cursor_name -- 3.提取游标中的数据FETCH current_name INTO 变量1,变量2,......;-- 4.检查游标状态（使用 %NOTFOUND 判断是否还有数据）
-- 5.关闭游标CLOSE cursor_name;-- 6.释放游标(游标在 CLOSE 后会自动释放资源。DEALLOCATE CURSOR 一般用于动态游标，或者在需要显式释放游标资源的情况下使用。)DEALLOCATE CURSOR cursor_name;END

游标具有多种属性，这些属性用于检查游标的状态和控制其行为得开发者能够更好地控制游标的行为，以及监测从游标中提取数据的过程。以下是常见的游标属性：

  1. %FOUND：

表示在最近一次FETCH操作中是否成功找到记录。如果成功获取到一条记录，返回TRUE；否则返回FALSE

  1. %NOTFOUND：表示在最近一次FETCH操作中是否没有找到记录。如果没有更多记录可供读取，返回TRUE；否则返回FALSE。
  2. %ROWCOUNT：表示从游标打开到当前为止，通过FETCH读取的记录行数。可以用来获取已经处理的行数。
  3. %ISOPEN：用于检查游标是否处于打开状态。如果游标当前是打开的，返回TRUE；否则返回FALSE。
  • 游标属性的使用案例
DECLARECURSOR c_employee IS SELECT employee_id, employee_name FROM employees;v_employee_id employees.employee_id%TYPE;
BEGINOPEN c_employee;LOOPFETCH c_employee INTO v_employee_id;-- 检查游标状态IF c_employee%NOTFOUND THENEXIT;  -- 如果没有更多记录，退出循环END IF;SELECT v_employee_id AS "Employee ID", c_employee%ROWCOUNT AS "Rows fetched so far";END LOOP;CLOSE c_employee;  -- 关闭游标
END;

12.1 显式游标

显式游标允许开发者定义特定的查询，从而可以在后续的程序中打开、提取和操作查询结果集。需要用户在PL/SQL代码中显式声明和管理。提供对查询结果集的更多控制，适用于复杂的查询和处理。

  • 显示游标的使用案例
DECLARECURSOR c_employee IS SELECT employee_id, employee_name FROM employees;v_employee_id employees.employee_id%TYPE;v_employee_name employees.employee_name%TYPE;
BEGINOPEN c_employee;-- 使用loop循环控制结构LOOPFETCH c_employee INTO v_employee_id, v_employee_name;EXIT WHEN c_employee%NOTFOUND;  -- 如果没有更多数据则退出-- 处理数据-- DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);SELECT v_employee_id AS "Employee ID", v_employee_name AS "Name" ;END LOOP;CLOSE c_employee;  -- 关闭游标
END;
  • 游标用于更新的案例
DECLARECURSOR c_salary ISSELECT employee_id, salary FROM employees WHERE department_id = 10 FOR UPDATE;v_employee_id employees.employee_id%TYPE;v_salary employees.salary%TYPE;
BEGINOPEN c_salary;LOOPFETCH c_salary INTO v_employee_id, v_salary;EXIT WHEN c_salary%NOTFOUND;-- 更新工资v_salary := v_salary * 1.10;  -- 加10%-- 使用CURRENT OF更新当前行UPDATE employees SET salary = v_salary WHERE CURRENT OF c_salary;  END LOOP;CLOSE c_salary;  -- 关闭游标
END;
  • 游标用于删除的案例
DECLARECURSOR c_delete ISSELECT employee_id FROM employees WHERE hire_date < '2020-01-01' FOR UPDATE;
BEGINOPEN c_delete;LOOPFETCH c_delete INTO v_employee_id;EXIT WHEN c_delete%NOTFOUND;-- 删除员工DELETE FROM employees WHERE CURRENT OF c_delete;END LOOP;CLOSE c_delete;  -- 关闭游标
END;

  • UNION 属性的使用示例：

**PS:**UNION 是一种用于合并两个或多个 SELECT 语句结果集的操作符。使用 UNION 时，要求每个 SELECT 语句必须返回相同数量的列，并且对应的列数据类型要兼容。

在达梦数据库（DM）和人大金仓数据库（KingbaseES）中，UNION 操作的语法与 SQL Server 中是相同的。可以使用** UNION** 或 UNION ALL 来合并两个或多个 SELECT 语句的结果集。以下是使用 UNION 的基本示例和注意事项。

-- UNION 默认会去掉重复的记录
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;-- 如果你想保留所有记录（包括重复的），可以使用 UNION ALL！
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;-- *注意：第一条查询语句末尾不要加；直接连接 UNION 关键字！！

示例代码：

DECLAREp_assignee  VARCHAR(255);
BEGIN-- 声明游标DECLARE cur CURSOR FORSELECT USER_ID_ AS ASSINGEE FROM ACT_HI_IDENTITYLINK WHERE USER_ID_ IS NOT NULL AND PROC_INST_ID_ = 'fa15d3c8-09bc-11ee-a8da-ecb1d7b4def0' -- UNION 是一种用于合并两个或多个 SELECT 语句结果集的操作符。使用 UNION 时，要求每个 SELECT 语句必须返回相同数量的列，并且对应的列数据类型要兼容。UNIONSELECT CAST(CZYID AS VARCHAR(255)) AS ASSINGEE FROM GL_CZY_ROLE WHERE ROLEID ='443';BEGIN-- 打开游标OPEN cur;-- 提取数据FETCH cur INTO p_assignee;-- 处理数据： 可以使用 WHILE 循环来逐行处理数据，直到游标中的数据处理完毕。 WHILE(cur%FOUND) LOOPSELECT p_assignee,(cur%FOUND) AS "结果1",(cur%NOTFOUND) AS "结果2";FETCH cur INTO p_assignee;END LOOP;SELECT p_assignee,(cur%FOUND) AS "最后一次结果1",(cur%NOTFOUND) AS "最后一次结果2";-- 关闭游标CLOSE cur;END;
END;

  • 游标中退出循环操作
    在游标中，使用了WHILE 循环来逐行处理数据时，当满足要求需要退出游标时，可以使用 EXIT 关键字进行退出操作（在sqlserver数据库中使用的是 break 关键字）！
    示例代码如下：
-- 创建游标
DECLARE cursorQueryLastAssignee CURSOR IS 
SELECT ASSIGNEE_ FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ = 'P_PROCINSTID' ORDER BY START_TIME_ DESC;
BEGIN-- 打开游标OPEN cursorQueryLastAssignee;-- 提取数据FETCH cursorQueryLastAssignee INTO P_LASTASSIGNEE;--使用 while循环  处理数据WHILE(cursorQueryLastAssignee%FOUND)LOOPP_I := P_I +1;SELECT P_LASTASSIGNEE,P_I;IF(P_I =1 AND P_LASTASSIGNEE IS NOT NULL) THENEXIT;  -- 使用 EXIT 退出循环END IF;FETCH cursorQueryLastAssignee INTO P_LASTASSIGNEE;END LOOP;-- 关闭游标！CLOSE cursorQueryLastAssignee;
END;

12.2 隐式游标

在每次执行DML语句（如SELECT、INSERT、UPDATE、DELETE）时，数据库系统自动创建。由数据库系统在执行DML（数据操作语言）语句时隐式生成。用户无需单独声明或管理，适用于简单的SQL操作。

隐式游标使用上较为方便，省去了游标定义、打开、关闭等操作，但是使用场景上有局限性，程序中限于 INSERT、UPDATE、DELETE、SELECT…INTO 等语句。

示例：

BEGINUPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;IF SQL%NOTFOUND THENDBMS_OUTPUT.PUT_LINE('No rows updated.');END IF;
END;

12.3 动态游标

动态游标（Dynamic Cursor）是一种能够在运行时生成并执行SQL查询的游标。这种游标为开发者提供了更大的灵活性，可以根据运行环境的不同需求动态调整SQL语句。

  • 动态游标的操作步骤：
  1. 声明游标类型： 使用REF CURSOR定义游标类型。
  2. 声明游标变量： 声明动态游标变量。
  3. 构建动态SQL： 使用字符串变量构建要执行的SQL查询。
  4. 打开游标： 使用OPEN FOR语句执行构建的SQL。
  5. 提取数据： 使用FETCH从动态游标中获取数据。
  6. 关闭游标： 使用CLOSE语句关闭游标，释放资源。

动态游标的示例：

DECLARETYPE t_cursor IS REF CURSOR;  -- 定义游标类型c_dynamic t_cursor;            -- 声明动态游标v_sql VARCHAR2(1000);          -- 用于存储动态SQL语句v_employee_id employees.employee_id%TYPE;v_employee_name employees.employee_name%TYPE;
BEGIN-- 构建动态SQL语句v_sql := 'SELECT employee_id, employee_name FROM employees WHERE department_id = :dept_id';-- 打开游标并执行动态SQLOPEN c_dynamic FOR v_sql USING 10;  -- 查询部门ID为10的员工-- 循环提取数据LOOPFETCH c_dynamic INTO v_employee_id, v_employee_name;EXIT WHEN c_dynamic%NOTFOUND;-- 处理数据（这里打印）DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);END LOOP;-- 关闭游标CLOSE c_dynamic;
END;

***注意：**这里的动态参数采用 :xxx 的形式，而不是使用 ？ 来代替！

示例2：

动态游标特别适合以下场景：

  1. 用户输入条件的不同查询：需要根据用户的输入动态构建不同的查询。
  2. 复杂的业务逻辑：在处理复杂的查询时，可以灵活构建多个逻辑路径。
  3. 动态表名或字段名：在需要根据某些条件更改查询表名或字段时，非常有用。
  4. 批量数据处理：可以通过参数化的动态SQL高效处理批量插入或更新。

十三、SpringBoot 集成KingbaseES

KingbaseES 的 JDBC 驱动程序存放在数据库安装程序目录的 JDBC 文件夹内（或者前往官网下载对于驱动 Jar。），应用程序只需把数据库 的 JDBC驱动程序和数据库连接字符串替换为 KingbaseES 的对应内容即可。

驱动包支持最低版本
kingbase8-8.6.0.jre6.jar JDK1.6
kingbase8-8.6.0.jre7.jar JDK1.7
kingbase8-8.6.0.jar JDK1.8

13.1 项目中导入数据库依赖

  • 方式一： 将KingbaseES 数据库Jar依赖导入到项目中后，在pom文件中进行引用：
  1. 在springboot项目根目录下创建lib文件夹，放入kingbaseES数据库驱动Jar文件。

  1. 在项目的pom文件中导入 人大金仓数据库驱动：
<!-- ################## 人大金仓数据库依赖，需要在达梦安装目录中将相关依赖导入到本机/项目  ##################--><dependency><groupId>com.kingbase</groupId><artifactId>kingbase8</artifactId><version>8.6.0</version><scope>system</scope><systemPath>${project.basedir}/src/libs/DmJdbcDriver18.jar</systemPath></dependency>
  • 方式二： 将KingbaseEs JDBC驱动上传到本地Maven仓库后，项目在通过本地仓库直接进行引用
  1. 在cmd窗口( 或者在 IDEA中操作 )使用命令将 Jar 安装到本地仓库：

使用命令：mvn install:install-file -DgroupId=com.kingbase8 -DartifactId=kingbase8 -Dversion=8.6.0 -Dpackaging=jar -Dfile=D:\database\Database_KingbaseEs\KingbaseES\KESRealPro\V008R006C008B0014\Interface\jdbc\kingbase8-8.6.0.jar

PS: -Dfile 是Jar 包所在位置，需要切换为自己的jar包存放位置！

  1. 本地Maven仓库 导入 KingBaseEs 数据库驱动后，在项目pom文件中直接引用数据库依赖

***注意：**groupId 和 artifactId 的具体位置要和上一步导入本地仓库时的位置相同！

  <!-- ################## 人大金仓数据库依赖，需要在达梦安装目录中将相关依赖导入到本机/项目  ##################--><dependency><groupId>com.kingbase8</groupId><artifactId>kingbase8</artifactId><version>8.6.0</version></dependency>

13.2 项目中的yaml 文件配置

URL 连接地址格式：

  • jdbc:kingbase8://host:port/database
  • jdbc:kingbase8://host:port/database?para1=val1&para2=val2…

如果 host 是 IPv6 地址，则必须用中括号把地址括起来，如下所示：<font style="color:rgb(0,0,0);">jdbc:kingbase8://[IPv6host]:port/database?para1=val1&para2=val2…</font>

*注意： 如果连接串中包含 (’%’,’?’,’&’,’/’)等字符，会对JDBC分析连接串造成影响。可以使用URLEncoder.encode 转义字符处理！

spring:datasource:# ###############本地SqlServerJDBC驱动包\数据源配置：###############
#    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
#    url: jdbc:sqlserver://127.0.0.1:1433;databasename=pisp;trustServerCertificate=true;encrypt=false;SelectMethod=cursor;
#    username: sa
#    password: lcj1024..# ###############本地MysqlJDBC驱动包\数据源配置：############### 
#    driverClassName: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://127.0.0.1:3306/pisp?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
#    username: lcj
#    password: lcjMysql123..# ###############本地人大金仓KingBase8驱动包\数据源配置：###############driverClassName: com.kingbase8.Driverurl: jdbc:kingbase8://127.0.0.1:54321/mysqlpisp?currentSchema=pisp&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8username: pispuser2password: lcjPisp123..# ###############本地达梦数据库DM8驱动包\数据源配置：###############
#    driverClassName: dm.jdbc.driver.DmDriver
#    url: jdbc:dm://127.0.0.1:5236/MysqlPISP #?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8Shanghai
#    username: SYSDBA
#    password: lcjDm123..# ###############本地oracle数据库JDBC驱动包\数据源配置：###############
#    driverClassName: oracle.jdbc.driver.OracleDriver
#    url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
#    username: system
#    password: lcjOracle123
#    type: com.alibaba.druid.pool.DruidDataSource

13.3 在JDBC中使用 KingBaseEs

示例代码：

package com.gzzd.test;import org.junit.Test;
import org.springframework.boot.test.context.SpringBootTest;import java.sql.*;/*** @ClassName : TestKingBaseEs* @Description : KingBaseEs数据库的 JDBC使用* @Author : AD*/
@SpringBootTest
public class TestKingBaseEs {/** 定义DM JDBC驱动串 */String jdbcString = "com.kingbase8.Driver";/** 定义 DM URL 连接串 (TEST为模式名称) */String urlString ="jdbc:kingbase8://127.0.0.1:54321/booklist?currentSchema=public&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8";/** 定义连接用户名 */String userName = "lcj";/** 定义连接用户口令 */String password = "lcjRdjc123..";/** 定义连接对象 */Connection conn = null;/*** Description: 加载 JDBC 驱动*/public void loadJdbcDriver() throws SQLException {try {System.out.println("加载 JDBC 驱动...");Class.forName(jdbcString);/** 或者使用 *///DriverManager.registerDriver(new com.kingbase8.Driver());} catch (ClassNotFoundException e) {throw new SQLException("Load JDBC Driver Error : " + e.getMessage());}}/*** Description: 连接 DM 数据库*/public void connect() throws SQLException{try {System.out.println("连接 DM 数据库...");conn = DriverManager.getConnection(urlString, userName, password);} catch (SQLException e) {throw new SQLException("Connect to DM Server Error : " + e.getMessage());}}/*** Description: 断开 DM 数据库连接*/public void disConnect() throws SQLException{try {System.out.println("断开 DM 数据库连接...");if (conn!= null) {conn.close();}} catch (SQLException e) {throw new SQLException("DisConnect to DM Server Error : " + e.getMessage());}}/*** Description: 查询员工信息测试方法* */@Testpublic void queryEmployee() throws SQLException{loadJdbcDriver();connect();// 查询语句String sql = "SELECT id,name FROM book_name";//创建语句对象Statement statement = conn.createStatement();//执行查询ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {int bookId = resultSet.getInt("id");String bookName = resultSet.getString("name");System.out.println("book ID: " + bookId + ", Name: " + bookName);}// 关闭资源resultSet.close();disConnect();}
}

执行查询功能结果示例:

十四、数据迁移

安装好人大金仓数据库后，同样会携带 人大金仓 的数据迁移工具，该工具的存储位置在安装目录下的 ...\KingbaseES\KESRealPro\V008R006C008B0014\ClientTools\guitools\KDts\KDTS-WEB\bin 目录下：

KDTS 数据库迁移工具的产品形态有以下两种：

  • BS 版本：通过浏览器以可视化界面方式设置配置项，完成数据迁移。(这里只演示 BS 版本的数据迁移)
  • SHELL 版本：通过手动修改配置文件的方式设置配置项，完成数据迁移。

ps: [这里提前统一注释，后续会涉及到相关功能的使用]

  1. 查看当前 KES 数据库版本 SELECT VERSION();

  2. KDMS
    KDMS是金仓提供一款SAAS服务，是人大金仓首创的数据库迁移评估工具，人大金仓对所有用户免费开放使用。其功能：可在迁移项目未开始前预评估Oracle、SQLServer、DB2、MySQL等主流数据库的对象（触发器，函数、存储过程等）、SQL语句向金仓KES迁移工作量一一用户可通过评估报告即可准确了解迁移项目工作量，做到对迁移工作量心中有数，做好项目计划；接着，用户可使用KDMS智能翻译技术对Oracle等源库的对象、SQL语法自动识别翻译转换为KES支持的SQL/PLSQL脚本，通过自动翻译技术，高效、高质完成源数据库迁移到KES工作，自动正确转换率可高达98%，此工具被誉为数据库语言的“翻译家”，数据库对象的“搬运工”（行业中有其他厂商模仿，但自动正确转换率维持在低水平，不具备可用性，依旧主要依赖高水平人工来解决语法翻译、迁移问题）。
    ( 前提是需要登录官网后才能访问 ）https://dms.kingbase.com.cn/dashboard

  1. 出现失败数据太多的情况下 ，目前只能通过 在线工具 去调整每一条失败的数据内容

(前提是需要登录官网后才能访问) https://dms.kingbase.com.cn/migrate/tools

14.1 源端数据库支持说明

数据库数据迁移平台 (KDTS: KingBaseEs Data Transformation Service) 是为用户提供 Oracle、Mysql、SQLServer、Gbase、PostgreSQL、DM、PhotonBase 数据库数据迁移到 人大金仓 数据库的数据迁移工具。

下面展诉几种常用的数据库：

源端数据库支持版本版本查询方式支持迁移对象
Oracle9i、10g、11g、12c、19cselect * from v$version;表、视图、序列、函数、存储过程、程序包、同义词、触发器、用户自定义类型、注释
MySQL5.X、8.Xselect version();表、视图、函数、存储过程、触发器、用户自定义类型、注释
SQLServer2000、2005、2008、2012、2014、2016、2017、2019select @@version;表、视图、序列、函数、存储过程、程序包、同义词、触发器、用户自定义类型、注释
DMDM7、DM8select * from v v e r s i o n ; s e l e c t ∗ f r o m v version; select * from v version;select∗fromvinstance;表、视图、序列、函数、存储过程、程序包、同义词、触发器、用户自定义类型、注释

14.2 BS版数据迁移

  • 进入到KDTS工具文件夹的 bin 目录下，startup 启动数据迁移工具
  • 访问迁移地址：localhost:54523/

登录数据库迁移中心 账号密码会自动加载进入，会话保持策略保存时间为24小时！

  • 选择数据源、目标数据库 进行数据迁移操作

14.2.1 数据源管理

  1. 源数据库的配置：管理源数据库信息，在数据迁移时作为被迁移数据库使用。

新建源数据库连接，填写需要的相关数据源连接信息，包括：“连接名称”、“数据库类型”、“数据库版本”、“服务器地址”、“端口”、“用户名”、“密码”、“数据库”、“驱动”、“URL”、“连接参数”。

  • 连接名称： 输入自定义的连接名称，用于标注此数据源连接。
  • 数据库类型：在源数据库中支持 “Oracle”、“MySQL”、“SQLServer”、“Db2”、“Gbase”、“Post-greSQL”、“DM”、“SHENTONG”、“Sybase”、“PhotonBase”数据库类型。
  • 数据库版本：与数据库类型相关联的迁移工具支持迁移的数据库版本。
  • 服务器地址：数据库所在服务器 IP 地址。
  • 端口：数据库端口号。
  • 用户名：操作数据库的用户名。
  • 密码：与操作数据库用户名相匹配的密码。
  • 数据库：操作数据库的名称。
  • 驱动：数据库驱动信息，会根据数据库类型及数据库版本动态调整。
  • URL：完成上面相关参数的填写后，url会自动生成！
  • 连接参数：根据数据库做相应适配，可自行添加或删除参数。

测试新建的数据源是否能够连接成功！

其它修改、查询、删除操作这里就不多叙述！

  1. 目标数据库：管理目标数据库信息，在数据迁移时作为存放迁移数据的数据库使用。

新建目标数据库连接。完成新建目标数据库需要填写相关数据源连接信息，包括：“连接名称”、“数据库类型”、“数据库版本”、“KES兼容模式”、“服务器地址”、“端口”、“用户名”、“密码”、“数据库”、“驱动”、“URL”、“连接参数”。

  • KES兼容模式：KES兼容模式的选择主要是为了支持不同数据库的SQL语法和特性。选择合适的模式可以减少迁移过程中的语法问题和兼容性问题，根据源数据库的类型选择相应的KES兼容模式是最佳实践。
  • 数据库版本：这里通过自己的KingBaseEs 数据库使用命令 SELECT VERSION(); 进行查询即可！
  • 数据库：迁移到目标数据库的名称，这里需要先在 KingBaseEs 数据库中创建好对应数据库！

根据数据库查询结果显示的版本信息：

KingbaseES V008R006C008B0014：分解为 V8（主版本）、R6（次版本）、C8（修订版本）、B0014（构建号）。

测试连接目标数据库：

14.2.2 迁移任务管理

新建迁移任务以进行数据迁移。新建任务包含四步：“选择数据源”、“选择模式”、“选择迁移对象”、“配置参数”。

  1. 选择数据源

填写自定义任务名称（任务名称不能重复），选择“源数据库”和“目标数据库”，或者选择“新建数据源”后使用。

  1. 选择模式

根据您的数据迁移所需选择对应模式（如需选择模式在系统模式中可选中“包含系统模式”复选框）的表、视图、序列、函数、存储过程、程序包、同义词、触发器、用户自定义类型、注释及配置搜索路径。

当模式较多时也可以通过左上方的查询框进行检索。请您至少选择一种模式，否则将收到错误提示，以至于不能完成新建任务。

  1. 选择迁移对象

通过已选模式选择您需要迁移的对象。

可迁移此模式下全部对象，也可以指定或排除部分对象，当您选择“包含指定对象”或“排除指定对象”时，请您通过“从列表选择”或者“从文件导入”将迁移对象添加到包含列表中，如您未添加数据，则会提示错误导致无法进行下一步并完成新建任务。

点击“包含指定对象”时可选择两种方式。“从列表选择”可在模式中选择指定迁移对象；如您要“从文件导入”，可点击“下载导入模板”，根据导入模板规则填写，然后从文件导入该模板。当您需要“排除指定对象”时，同指定部分对象相同操作，但结果相反。

从列表选择迁移对象时，可选择对象类型及对象名称进行快速检索对应的表。点击“添加”按钮后加入到已选列表，当您想要移除部分迁移对象时可以选择对应的表点击“移除”按钮取消选中迁移对象。选择完成后点击确定。

  1. 配置参数

可以通过对参数的更改获得预估的数据迁移结果。其中迁移配置包括“源数据库配置”及“目标数据库配置”两大类。

注意1ps: 迁移数据时，对象的标识大小写设置问题：

这里对象标识符设置为小写，后续在数据库开发工具中进行表数据查询时，就不会在区分大小写！否则后续在进行SQL 语句编写时，会出现大小写不兼容（致使SQL语句中需要大量使用"" 来精准的编写表名称），就可能会导致查询表格不存在的问题出现！ 所以这里可以默认使用小写！

因为的人大金仓数据库中，在不设置大小写敏感的情况下所以的对象名称都会转换为小写的方式存储（包括SQL中的对象名称在不加""的情况下都会转为小写后在处理），详细规则请看 15.4 小结的描述！

PS注意3：

关于表中字段的兼容性设置： 该设置可以将表中字段的名称也忽略大小写，从而便于在sql语句中的查询，否则必须要使用“”来大小写对应进行查询！

例如：下表中迁移时没有设置‘非对象设置-自动转换对象名’时

那么在人大金仓中进行数据查询时，对应字段 必须要使用 “CzyCode”，否则就会报错‘未查询到该字段’lcjY

数据类型映射包括“源数据类型”、“目标数据类型”、“源库最小版本”、“源库最大版本”、“目标库最小版本”、“目标库最大版本”及“配置”。

注意3PS:

当数据迁移过程出，出现数据转换错误的问题时，如果当前表的字段非常多，很难定位到具体的长度问题所在位置时！ 可以通过自定义配置类型映射关系来临时解决数据迁移字段长度不符合的问题！

保存并迁移：

“处理中”功能的工作区显示正在进行的迁移任务。可点击“停止”按钮取消该任务（停止任务将导致迁移任务失败）。

点击“进度”按钮可查看该处理中的任务完成进度。在“进度查看”中也可以点击“停止”按钮取消此任务，或点击“删除”按钮，直接删除此任务。

14.2.3 迁移结果

“迁移结果”功能的工作区包括“任务执行批次”、“迁移对象”、“总数”、“成功数”、“失败数”、“略过数”、“操作”。您可以查看历史迁移任务执行的每次记录，以及每次迁移的对象、成功数、失败数、查看失败任务的错误日志。

迁移日志打印迁移任务执行后的日志，具体可分为“系统日志”、“Error日志”、“Info日志”。

  • “Error日志”仅显示error级别的日志。在迁移任务失败时有助于帮您找到错误原因。如需自动滚动日志可以点击“自动滚动”复选框。
  • “Info日志”仅显示info级别的日志。如需自动滚动日志可以点击“自动滚动”复选框。

14.3 迁移失败数据处理办法

  • 表数据迁移失败的情况

这里处理办法比较多样，需要根据自己迁移失败的问题所在进行解决！这种情况大多数是由于表结构问题导致的（非空约束、字符长度等问题），导致后续表数据迁入失败！

  1. 根据表数据迁移错误信息，重新调整表格定义结构
  2. 然后进行二次迁移，需要注意在迁移时，就不要在迁移表结构，只需要迁移表格数据即可！

首先可以通过 详情来判断错的的原因是那些！然后在详情里面进行调试代码，调试成功后就继续执行代码！

根据问题所在，可以在数据库管理工具中进行调整修改（比如表约束、表字段长度问题等），这类问题会导致数据迁移失败，最终导致该表中一个数据都没有。

所在这种问题需要先将表结构调整好之后，在进行 编辑 对应的二次迁移！

在二次迁移时需要注意在配置迁移任务的过程中，需要勾选‘表存在就不删除表格，仅迁入数据’、‘不迁移表结构，仅迁移数据’ 等操作（因为这里是调整后的表格才能正确的迁入数据内容）！

这部分没办法举例，根据实际情况灵活处理即可，使用多了就能想到对应的处理方案！

十五、KingBaseEs 注意点：

15.1 关键字/字符串 的处理

  • Kingbase8: sql中字段存在关键字用 “关键字” 双引号来标识，‘标识字符串’！

15.2 字段值空格

字段中后置空格问题：后置空格没有自动处理，在传入参数时也需要代入末尾空格！！ 或者通过 TRIM(字段) 函数处理前后空格

Where后面条件语句 空字符串（‘’）的判断 where ifnull(field,‘’)<>‘’;

KingBase中 Where语句包含了对空字符串（‘’）的判断时，查询不出任何结合！！！
可能是数据库字符编码设置问题！！
SELECT IFNULL(wx_appid,‘xxxx’) wxappid,IFNULL(wx_shmc,‘xxshmc’) wxshmc,isNULL(wx_shh,‘44444’) wxshh,* FROM zdsf_sfdt_shxx ;
● bugxxxx:如果空字符串（‘’） 就是 为NULL，但是这里的 isNULL(wx_shh,‘44444’) 没有替换值成功！
SELECT * FROM zdsf_sfdt_shxx WHERE TRIM(wx_appid) IS NOT NULL;

-- 常规写法：
SELECT  * FROM  zdsf_sfdt_shxx where ISNULL(wx_appid,'') &lt;&gt; '';
-- 使用了ISNULL()函数来处理NULL值，但是直接比较的方式有误。
-- 在SQL中，正确的处理方式应该是将ISNULL函数的结果与空字符串进行比较，
-- 但在KingBaseEs条件逻辑需要调整以达到效果。--  这样写也没用!!!!!
SELECT  * FROM  zdsf_sfdt_shxx where (wx_appid &lt;&gt; '') AND (wx_appid IS NOT NULL);--  这样即能查询出数据来！！ 
SELECT * FROM zdsf_sfdt_shxx  WHERE TRIM(wx_appid) IS NOT NULL;-- 总结：
SELECT wx_appid,* FROM zdsf_sfdt_shxx;
-- 这里设置的空字符串（''）会自动转换为null，可通过 IFNULL操作，可通过where xx IS NOT NULL 操作！！！！！！！
UPDATE zdsf_sfdt_shxx SET wx_appid='' WHERE &quot;shmc&quot; ='志达四方' 
SELECT IFNULL(wx_appid,123456),* FROM zdsf_sfdt_shxx;
SELECT wx_appid,* FROM  zdsf_sfdt_shxx WHERE wx_appid IS NOT NULL;
-- 原本数据存在的空字符串（''）无法被 IFNULL、IS NOT NULL 识别! [不是一个null]
SELECT IFNULL(wx_shh,123456),* FROM zdsf_sfdt_shxx; -- IFNULL 无法排除原有数据的空字符串（''）
SELECT wx_shh,* FROM zdsf_sfdt_shxx WHERE &quot;wx_shh&quot; IS NOT NULL; -- IS NOT NULL无法排除原有数据的空字符串（''）
SELECT wx_shh,* FROM zdsf_sfdt_shxx WHERE &quot;wx_shh&quot;&lt;&gt;'';  -- 条件使用'' 会导致任何数据查询不出来
-- 能够排除原有数据空字符串（''）的方式1
SELECT wx_shh,* FROM zdsf_sfdt_shxx WHERE trim(wx_shh) IS NOT NULL;
</code></pre></details>

15.3 Varchar类型数据传入 0开头的 INT数据

  • 字段、变量类型为Varchar() ,变量传参时传入INT数据 [避坑] [同达梦]
    字段、变量类型为Varchar() ,变量传参时传入INT数据
    这里可以看出传入 int 类型的值 0 会自动抹掉。
    请添加图片描述
    这里就导致变量作为参数时，和实际传入不同查询结果也就不同了！！

查询结果1：
请添加图片描述
查询结果2：
请添加图片描述



  • varchar类型字段作为where条件传入参数时需要标识为字符串’ xxx ’



15.4 大小写敏感问题

在人大金仓数据库（KingbaseES）中，表名和列名的大小写处理遵循以下规则：

  • 默认大小写不敏感：

在人大金仓中，如果在创建表时没有使用双引号，表名和列名会被转换为小写 (这里与达梦数据库相反) ；

这种情况下在查询表时，表名、字段名都被转换为小写形式，可以在查询时使用任何形式的(大写、小写)方式检索表和列名；

  • 使用双引号的情况：

如果在创建表时使用双引号包裹表名或列名，那么该名称会保持定义时的大小写。

在这种情况下，您必须在查询时也使用双引号和完全相同的大小写！

  • 查询表名的视图 PG_TABLES

当您在查询系统表（如 pg_tables）时，通常使用小写来检查表是否存在。如果创建时使用了双引号，您也应确保查询时使用相同的方式。

总结：

如果不需要区分大小写，建议在创建表和列时避免使用双引号，这样可以在查询时灵活使用大小写。

如果需要保留特定的大小写，记得在创建和查询时使用双引号，并严格遵循定义时的大小写。

15.5 其它

  1. U8Cloud初始化数据库内容：

连接 KingbaseES 出现异常：

com.kingbase8.util.KSQLException:The authentication type 10 is not supported.Check that you have configured the sys_hba.conf file to include the client’s IP address or subnet,and that it is using an authentication scheme supported by the driver.

  • 首先在 KES 数据库的 sys_hba.conf 配置文件中修改下面所示信息：

  • 在对应KingbaseEs 安装目录下 重启 KingbaseES 服务！

或者是在服务中重新启动！

  • 使用ip地址连接 不要使用 127.0.0.1

查询对应服务器ip地址信息


http://www.mrgr.cn/news/79304.html

相关文章：

  • 【ETCD】[源码阅读]深度解析 EtcdServer 的 processInternalRaftRequestOnce 方法
  • 泷羽Sec学习笔记-zmap搭建炮台
  • ctfshow-web 151-170-文件上传
  • 活着就好20241213
  • 视频安防监控平台：Liveweb视频监控管理云平台方案
  • 高效利用资源：分布式有状态服务的高可靠性设计
  • Docker-Compose环境变量
  • web基础和http协议
  • 「Mac畅玩鸿蒙与硬件43」UI互动应用篇20 - 闪烁按钮效果
  • Mysql数据库基础篇笔记
  • LearnOpenGL学习（高级OpenGL -- 深度测试，模板测试，）
  • xss学习前的基础
  • 期末复习-Hadoop名词解释+简答题+代码题hive
  • WordPress阅读文章显示太慢的处理
  • vue 一行显示的动态消息
  • 鸿蒙应用获取wifi连接的ip地址(官方文档获取的格式转换成192.168.1.xxx格式)
  • Linux-实用操作
  • Redis的五种数据类型（String、Hash、List）
  • centos 常见问题处理
  • Qt 面试题学习13_2024-12-1
  • ScribblePrompt 医学图像分割工具，三种标注方式助力图像处理
  • 构建万能 MOCK-API
  • Ubuntu显卡驱动安装
  • 阿里云ECS服务器域名解析
  • 在 MacOS 上为 LM Studio 更换镜像源
  • 使用MATLAB从Excel文件读取数据并绘制堆叠柱状图