网硕互联帮助中心文章纲要
- 一、建表与基础结构：创建 TimeZoneMappings 表，包含 IANA 时区、Windows 时区、显示名称、排序字段等，并设置主键、唯一约束与自增 RowVersion。
- 二、触发器与索引：实现 UpdatedAt 自动更新触发器，以及用于分页和全文检索的各类索引。
- 三、分页存储过程：提供传统页码分页（sp_timezone_page）与游标分页（sp_timezone_cursor）两种方案，适配不同数据量场景。
- 四、全文检索实现：基于 Oracle Text 创建中英文全文索引，并实现全文检索游标分页存储过程（sp_timezone_search_ft）。
- 五、Python 调用示例：使用 python-oracledb 连接 Oracle 21c，演示页码分页、游标分页与全文检索分页的完整调用方式。
- 六、运行结果：展示分页与全文检索的实际输出效果。
— ============================================================
— Oracle 21c: TimeZoneMappings 建表 + 432 条数据 + 分页存储过程
— 共 432 条唯一 IANA 时区, Sort 1..432（与 MySQL 版数据完全一致）
— 执行工具: SQL*Plus / PL/SQL Developer / DBeaver 均可
— ============================================================
— 0) 清理旧表（首次执行可注释掉）
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TimeZoneMappings CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
— 1) 建表（VARCHAR2 用 CHAR 语义，兼容中文；RowVersion 用 IDENTITY 自增）
CREATE TABLE TimeZoneMappings (
TimeZoneId RAW(16) PRIMARY KEY,
IanaTimeZoneId VARCHAR2(100 CHAR),
WindowsTimeZoneId VARCHAR2(100 CHAR) NOT NULL,
DailyResetOffset NUMBER(10) NOT NULL,
Sort NUMBER(10) DEFAULT 0 NOT NULL, — 修复：DEFAULT 必须在 NOT NULL 之前
DisplayName VARCHAR2(200 CHAR),
DisplayNameHK VARCHAR2(200 CHAR),
DisplayNameEN VARCHAR2(200 CHAR),
IsActive NUMBER DEFAULT 1 NOT NULL, — 修复：DEFAULT 必须在 NOT NULL 之前
CreatedAt TIMESTAMP DEFAULT SYSTIMESTAMP,
UpdatedAt TIMESTAMP,
RowVersion NUMBER(20) GENERATED BY DEFAULT AS IDENTITY NOT NULL,
CONSTRAINT UQ_IanaTimeZoneId UNIQUE (IanaTimeZoneId)
);
/
select * from TimeZoneMappings;
— 2) UpdatedAt 自动更新触发器（Oracle 无 ON UPDATE）
CREATE OR REPLACE TRIGGER trg_tz_updated
BEFORE UPDATE ON TimeZoneMappings
FOR EACH ROW
BEGIN
:NEW.UpdatedAt := SYSTIMESTAMP;
END;
/
— 3) 游标分页核心索引
CREATE INDEX IX_IsActive_Sort ON TimeZoneMappings (IsActive, Sort, RowVersion);
/
— 5) 存储过程: 传统页码分页（管理后台/小表）
CREATE OR REPLACE PROCEDURE sp_timezone_page(
p_keyword IN VARCHAR2,
p_page_number IN NUMBER,
p_page_size IN NUMBER,
p_total OUT NUMBER,
p_cur OUT SYS_REFCURSOR
) AS
v_offset NUMBER;
v_kw VARCHAR2(220 CHAR) := '%';
BEGIN
IF p_page_number IS NULL OR p_page_number < 1 THEN p_page_number := 1; END IF;
IF p_page_size IS NULL OR p_page_size < 1 THEN p_page_size := 20;
ELSIF p_page_size > 200 THEN p_page_size := 200;
END IF;
v_offset := (p_page_number – 1) * p_page_size;
IF p_keyword IS NOT NULL AND p_keyword <> '' THEN
v_kw := '%' || REPLACE(REPLACE(REPLACE(p_keyword, '\\', '\\\\'), '%', '\\%'), '_', '\\_') || '%';
END IF;
SELECT COUNT(*) INTO p_total
FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\');
OPEN p_cur FOR
SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN
FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\')
ORDER BY Sort, RowVersion
OFFSET v_offset ROWS FETCH NEXT p_page_size ROWS ONLY;
END;
/
— 6) 存储过程: 游标分页（亿级主推，与 MySQL 版同语义）
CREATE OR REPLACE PROCEDURE sp_timezone_cursor(
p_keyword IN VARCHAR2,
p_last_sort IN NUMBER,
p_last_row_version IN NUMBER,
p_page_size IN NUMBER,
p_has_more OUT NUMBER,
p_cur OUT SYS_REFCURSOR
) AS
v_first_page NUMBER := 0;
v_kw VARCHAR2(220 CHAR) := '%';
BEGIN
IF p_page_size IS NULL OR p_page_size < 1 THEN p_page_size := 20;
ELSIF p_page_size > 200 THEN p_page_size := 200;
END IF;
IF p_last_sort IS NULL OR p_last_row_version IS NULL THEN v_first_page := 1; END IF;
IF p_keyword IS NOT NULL AND p_keyword <> '' THEN
v_kw := '%' || REPLACE(REPLACE(REPLACE(p_keyword, '\\', '\\\\'), '%', '\\%'), '_', '\\_') || '%';
END IF;
IF v_first_page = 1 THEN
OPEN p_cur FOR
SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN
FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\')
ORDER BY Sort, RowVersion
FETCH FIRST p_page_size ROWS ONLY;
ELSE
OPEN p_cur FOR
SELECT TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN
FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\')
AND (Sort > p_last_sort
OR (Sort = p_last_sort AND RowVersion > p_last_row_version))
ORDER BY Sort, RowVersion
FETCH FIRST p_page_size ROWS ONLY;
END IF;
— 是否还有下一页（EXISTS 短路）
IF v_first_page = 1 THEN
SELECT CASE WHEN EXISTS (
SELECT 1 FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\')
) THEN 1 ELSE 0 END INTO p_has_more FROM DUAL;
ELSE
SELECT CASE WHEN EXISTS (
SELECT 1 FROM TimeZoneMappings
WHERE IsActive = 1
AND (v_kw = '%' OR DisplayName LIKE v_kw ESCAPE '\\'
OR DisplayNameHK LIKE v_kw ESCAPE '\\'
OR DisplayNameEN LIKE v_kw ESCAPE '\\')
AND (Sort > p_last_sort
OR (Sort = p_last_sort AND RowVersion > p_last_row_version))
) THEN 1 ELSE 0 END INTO p_has_more FROM DUAL;
END IF;
END;
/
— ============================================================
— 验证: SELECT COUNT(*) FROM TimeZoneMappings; — 应返回 432
— SELECT COUNT(DISTINCT IanaTimeZoneId) FROM TimeZoneMappings; — 432
— ============================================================
CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN) INDEXTYPE IS CTXSYS.CONTEXT;
CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId) INDEXTYPE IS CTXSYS.CONTEXT; — 如果报错，执行以下
— 查看是否存在 CTXSYS 用户
SELECT * FROM all_users WHERE username = 'CTXSYS';
— 解锁 CTXSYS 用户（如果存在但被锁定）
ALTER USER CTXSYS ACCOUNT UNLOCK;
GRANT ctxapp TO C##GEOVINDU;
GRANT EXECUTE ON ctxsys.ctx_ddl TO C##GEOVINDU;
— 如果执行 GRANT ctxapp TO YOUR_USER; 时提示角色不存在，说明你的 Oracle 21c 数据库在安装时没有勾选 Oracle Text 组件。此时需要以 SYS 身份运行初始化脚本来安装：
— 以 SYS 登录
— @$ORACLE_HOME/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK;
BEGIN
— 创建一个中文分词器偏好
ctxsys.ctx_ddl.create_preference('chinese_lexer', 'chinese_vgram_lexer');
END;
/
— 使用指定的中文分词器创建全文索引
CREATE INDEX idx_tz_iana_text
ON TimeZoneMappings(IanaTimeZoneId)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer chinese_lexer');
CREATE OR REPLACE PROCEDURE sp_timezone_page(
p_page_num IN NUMBER, — 页码（从1开始）
p_page_size IN NUMBER, — 每页大小
p_order_by IN VARCHAR2, — 排序字段，如 'Sort ASC, RowVersion ASC'
p_total_count OUT NUMBER, — 输出：总记录数
p_cursor OUT SYS_REFCURSOR — 输出：当前页数据游标
) AS
BEGIN
— 1. 获取总记录数
SELECT COUNT(*) INTO p_total_count FROM TimeZoneMappings;
— 2. 获取当前页数据 (使用 12c+ 的 OFFSET … FETCH 语法)
OPEN p_cursor FOR
SELECT
TimeZoneId,
IanaTimeZoneId,
WindowsTimeZoneId,
DailyResetOffset,
Sort,
DisplayName,
DisplayNameHK,
DisplayNameEN,
IsActive,
CreatedAt,
UpdatedAt,
RowVersion
FROM TimeZoneMappings
ORDER BY Sort ASC, RowVersion ASC — 默认按 Sort 和 RowVersion 排序
OFFSET (p_page_num – 1) * p_page_size ROWS
FETCH NEXT p_page_size ROWS ONLY;
END;
/
CREATE OR REPLACE PROCEDURE sp_timezone_cursor(
p_last_sort IN NUMBER, — 上一页最后一条记录的 Sort 值
p_last_row_version IN NUMBER, — 上一页最后一条记录的 RowVersion 值
p_page_size IN NUMBER, — 每页大小
p_has_more OUT NUMBER, — 输出：是否还有下一页 (1=有, 0=无)
p_cursor OUT SYS_REFCURSOR — 输出：当前页数据游标
) AS
— 1. 变量声明必须放在 AS 和 BEGIN 之间
v_temp_cursor SYS_REFCURSOR;
v_row_count NUMBER := 0;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
— 2. 打开临时游标，多取 1 条用于判断是否还有下一页
OPEN v_temp_cursor FOR
SELECT
TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
Sort, DisplayName, DisplayNameHK, DisplayNameEN,
IsActive, CreatedAt, UpdatedAt, RowVersion
FROM TimeZoneMappings
WHERE (p_last_sort IS NULL AND p_last_row_version IS NULL) — 首页查询
OR (Sort > p_last_sort) — 展开的元组比较
OR (Sort = p_last_sort AND RowVersion > p_last_row_version)
ORDER BY Sort ASC, RowVersion ASC
FETCH NEXT p_page_size + 1 ROWS ONLY; — 关键：多取一条
— 3. 循环临时游标，计算实际返回的行数
LOOP
FETCH v_temp_cursor INTO v_rec;
EXIT WHEN v_temp_cursor%NOTFOUND;
v_row_count := v_row_count + 1;
END LOOP;
CLOSE v_temp_cursor;
— 4. 如果取出的行数 > 页面大小，说明还有下一页
p_has_more := CASE WHEN v_row_count > p_page_size THEN 1 ELSE 0 END;
— 5. 打开正式游标返回给应用层（只取刚好一页的数据）
OPEN p_cursor FOR
SELECT
TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
Sort, DisplayName, DisplayNameHK, DisplayNameEN,
IsActive, CreatedAt, UpdatedAt, RowVersion
FROM TimeZoneMappings
WHERE (p_last_sort IS NULL AND p_last_row_version IS NULL)
OR (Sort > p_last_sort)
OR (Sort = p_last_sort AND RowVersion > p_last_row_version)
ORDER BY Sort ASC, RowVersion ASC
FETCH NEXT p_page_size ROWS ONLY;
END;
/
CREATE OR REPLACE PROCEDURE sp_timezone_search_ft(
p_search_expr IN VARCHAR2,
p_last_sort IN NUMBER,
p_last_row_ver IN NUMBER,
p_page_size IN NUMBER,
p_has_more OUT NUMBER,
p_cursor OUT SYS_REFCURSOR
) AS
v_row_count NUMBER := 0;
v_rec TimeZoneMappings%ROWTYPE;
v_temp_cur SYS_REFCURSOR;
BEGIN
— 1. 打开临时游标，多取 1 条用于判断是否还有下一页
OPEN v_temp_cur FOR
SELECT * FROM (
SELECT * FROM (
— 英文索引 1
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayNameEN, p_search_expr, 1) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
— IANA 索引 2
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(IanaTimeZoneId, p_search_expr, 2) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
— 简体中文索引 3
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayName, p_search_expr, 3) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
— 繁体中文索引 4
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayNameHK, p_search_expr, 4) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
)
ORDER BY Sort ASC, RowVersion ASC
)
FETCH NEXT p_page_size + 1 ROWS ONLY;
— 2. 循环临时游标，计算实际返回的行数
LOOP
FETCH v_temp_cur INTO v_rec;
EXIT WHEN v_temp_cur%NOTFOUND;
v_row_count := v_row_count + 1;
END LOOP;
CLOSE v_temp_cur;
— 3. 判断是否还有下一页
p_has_more := CASE WHEN v_row_count > p_page_size THEN 1 ELSE 0 END;
— 4. 打开正式游标返回给应用层
OPEN p_cursor FOR
SELECT * FROM (
SELECT * FROM (
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayNameEN, p_search_expr, 1) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(IanaTimeZoneId, p_search_expr, 2) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayName, p_search_expr, 3) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
UNION
SELECT * FROM TimeZoneMappings
WHERE CONTAINS(DisplayNameHK, p_search_expr, 4) > 0
AND ((p_last_sort IS NULL AND p_last_row_ver IS NULL) OR (Sort > p_last_sort) OR (Sort = p_last_sort AND RowVersion > p_last_row_ver))
)
ORDER BY Sort ASC, RowVersion ASC
)
FETCH NEXT p_page_size ROWS ONLY;
END;
/
— 将现有的 RowVersion 全部替换为基于 Sort 排序的唯一递增数字
MERGE INTO TimeZoneMappings t
USING (
SELECT TimeZoneId, ROW_NUMBER() OVER (ORDER BY Sort ASC) AS new_rv
FROM TimeZoneMappings
) src ON (t.TimeZoneId = src.TimeZoneId)
WHEN MATCHED THEN
UPDATE SET t.RowVersion = src.new_rv;
SELECT Sort, RowVersion, IanaTimeZoneId, DisplayNameEN
FROM TimeZoneMappings
ORDER BY Sort ASC;
— 如果索引不存在，这两句会报错，忽略即可
DROP INDEX idx_tz_en_text;
DROP INDEX idx_tz_iana_text;
— 为英文名称创建全文索引
CREATE INDEX idx_tz_en_text
ON TimeZoneMappings(DisplayNameEN)
INDEXTYPE IS CTXSYS.CONTEXT;
— 为 IANA 时区ID创建全文索引
CREATE INDEX idx_tz_iana_text
ON TimeZoneMappings(IanaTimeZoneId)
INDEXTYPE IS CTXSYS.CONTEXT;
— 同步两个索引
EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text');
EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text');
— 测试是否能搜到 Pacific
SELECT IanaTimeZoneId, DisplayNameEN
FROM TimeZoneMappings
WHERE CONTAINS(IanaTimeZoneId, 'Pacific', 1) > 0;
DROP INDEX idx_tz_en_text;
DROP INDEX idx_tz_iana_text;
BEGIN
— 如果偏好已存在，会报错，这里直接忽略即可
CTXSYS.CTX_DDL.CREATE_PREFERENCE('chinese_lexer', 'chinese_vgram_lexer');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
— 为 DisplayName 创建中文全文索引
CREATE INDEX idx_tz_en_text
ON TimeZoneMappings(DisplayNameEN)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer chinese_lexer');
— 为 IanaTimeZoneId 创建中文全文索引（虽然主要是英文，但加上中文分词器也能兼容）
CREATE INDEX idx_tz_iana_text
ON TimeZoneMappings(IanaTimeZoneId)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer chinese_lexer');
— 手动同步索引
EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text');
EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text');
— 1. 删除可能存在的旧中文索引（如果报错不存在，忽略即可）
DROP INDEX idx_tz_cn_text;
DROP INDEX idx_tz_hk_text;
— 2. 创建中文分词器偏好（防止重名报错）
BEGIN
CTXSYS.CTX_DDL.DROP_PREFERENCE('my_chinese_vgram');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
CTXSYS.CTX_DDL.CREATE_PREFERENCE('my_chinese_vgram', 'chinese_vgram_lexer');
END;
/
— 3. 创建中文全文索引
CREATE INDEX idx_tz_cn_text
ON TimeZoneMappings(DisplayName)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_chinese_vgram');
CREATE INDEX idx_tz_hk_text
ON TimeZoneMappings(DisplayNameHK)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_chinese_vgram');
— 4. 【关键】手动同步索引
EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_cn_text');
EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_hk_text');
SELECT token_text
FROM DR$idx_tz_cn_text$I
WHERE token_text LIKE '%北%' OR token_text LIKE '%岛%';
SELECT token_text
FROM DR$idx_tz_hk_text$I
WHERE token_text LIKE '%島%' OR token_text LIKE '%北%';
SELECT IanaTimeZoneId, DisplayName
FROM TimeZoneMappings
WHERE CONTAINS(IanaTimeZoneId, '留尼汪', 1) > 0;
— 测试中文搜索（注意：chinese_vgram_lexer 支持部分匹配，搜“标准”或“中国”应该都能命中）
SELECT IanaTimeZoneId, DisplayName
FROM TimeZoneMappings
WHERE CONTAINS(DisplayName, '标准', 1) > 0;
SELECT DisplayName, LENGTH(DisplayName), LENGTHB(DisplayName)
FROM TimeZoneMappings
WHERE DisplayName LIKE '%岛%';
SELECT DisplayNameHK, LENGTH(DisplayNameHK), LENGTHB(DisplayNameHK)
FROM TimeZoneMappings
WHERE DisplayNameHK LIKE '%島%';
DROP INDEX idx_tz_en_text;
DROP INDEX idx_tz_iana_text;
BEGIN
CTXSYS.CTX_DDL.DROP_PREFERENCE('my_chinese_vgram');
EXCEPTION WHEN OTHERS THEN NULL; — 如果不存在则忽略
END;
/
BEGIN
CTXSYS.CTX_DDL.CREATE_PREFERENCE('my_chinese_vgram', 'chinese_vgram_lexer');
END;
/
CREATE INDEX idx_tz_en_text
ON TimeZoneMappings(DisplayNameEN)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_chinese_vgram');
CREATE INDEX idx_tz_iana_text
ON TimeZoneMappings(IanaTimeZoneId)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_chinese_vgram');
EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_en_text');
EXEC CTXSYS.CTX_DDL.SYNC_INDEX('idx_tz_iana_text');
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
— 补救现有数据的 RowVersion (使用 Sort 值作为初始版本号，保证唯一递增)
CREATE OR REPLACE TRIGGER trg_tz_rowversion
BEFORE INSERT ON TimeZoneMappings
FOR EACH ROW
BEGIN
— 仅在未提供 RowVersion 时，自动分配下一个序列值
— 假设你的表名是 TimeZoneMappings，Oracle 自动生成的序列通常命名为 ISEQ$$_XXXXX
— 如果不确定序列名，可以使用以下通用方式：
IF :NEW.RowVersion IS NULL THEN
— 注意：如果你的建表语句使用了 IDENTITY，这里可以直接不写触发器，
— 或者使用如下方式安全赋值：
SELECT TimeZoneMappings_SEQ.NEXTVAL INTO :NEW.RowVersion FROM DUAL;
— (注：如果不知道自动生成的序列名，建议在建表时显式指定序列)
END IF;
END;
/
— 1. 删除旧索引
DROP INDEX idx_tz_en_text;
DROP INDEX idx_tz_iana_text;
— 2. 重新创建索引 (使用中文分词器兼容英文)
BEGIN
ctxsys.ctx_ddl.create_preference('my_lexer', 'chinese_vgram_lexer');
EXCEPTION WHEN OTHERS THEN NULL; — 偏好已存在则忽略
END;
/
CREATE INDEX idx_tz_en_text ON TimeZoneMappings(DisplayNameEN)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_lexer');
CREATE INDEX idx_tz_iana_text ON TimeZoneMappings(IanaTimeZoneId)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('lexer my_lexer');
— 3. 【关键】手动同步索引，否则刚插入的数据搜不到
EXEC CTX_DDL.SYNC_INDEX('idx_tz_en_text');
EXEC CTX_DDL.SYNC_INDEX('idx_tz_iana_text');
DECLARE
v_cursor SYS_REFCURSOR;
v_total_count NUMBER;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
sp_timezone_page(
p_page_num => 1,
p_page_size => 5,
p_order_by => 'Sort ASC',
p_total_count => v_total_count,
p_cursor => v_cursor
);
DBMS_OUTPUT.PUT_LINE('=== sp_timezone_page 测试结果 ===');
DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_total_count);
LOOP
FETCH v_cursor INTO v_rec;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId);
END LOOP;
CLOSE v_cursor;
END;
/
DECLARE
v_cursor SYS_REFCURSOR;
v_has_more NUMBER;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
sp_timezone_cursor(
p_last_sort => NULL,
p_last_row_version => NULL,
p_page_size => 5,
p_has_more => v_has_more,
p_cursor => v_cursor
);
DBMS_OUTPUT.PUT_LINE('=== sp_timezone_cursor 测试结果 ===');
DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more);
LOOP
FETCH v_cursor INTO v_rec;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId);
END LOOP;
CLOSE v_cursor;
END;
/
SET SERVEROUTPUT ON;
DECLARE
v_cursor SYS_REFCURSOR;
v_has_more NUMBER;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
sp_timezone_search_ft(
p_search_expr => '北',
p_last_sort => NULL,
p_last_row_ver => NULL,
p_page_size => 5,
p_has_more => v_has_more,
p_cursor => v_cursor
);
DBMS_OUTPUT.PUT_LINE('=== 测试 1：搜索简体中文 北 ===');
DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more);
LOOP
FETCH v_cursor INTO v_rec;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', CN: ' || v_rec.DisplayName);
END LOOP;
CLOSE v_cursor;
END;
/
DECLARE
v_cursor SYS_REFCURSOR;
v_has_more NUMBER;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
sp_timezone_search_ft(
p_search_expr => '島',
p_last_sort => NULL,
p_last_row_ver => NULL,
p_page_size => 5,
p_has_more => v_has_more,
p_cursor => v_cursor
);
DBMS_OUTPUT.PUT_LINE('=== 测试 2：搜索繁体中文 島 ===');
DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more);
LOOP
FETCH v_cursor INTO v_rec;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', HK: ' || v_rec.DisplayNameHK);
END LOOP;
CLOSE v_cursor;
END;
/
DECLARE
v_cursor SYS_REFCURSOR;
v_has_more NUMBER;
v_rec TimeZoneMappings%ROWTYPE;
BEGIN
sp_timezone_search_ft(
p_search_expr => 'Pacific',
p_last_sort => NULL,
p_last_row_ver => NULL,
p_page_size => 5,
p_has_more => v_has_more,
p_cursor => v_cursor
);
DBMS_OUTPUT.PUT_LINE('=== 测试 3：搜索英文 Pacific ===');
DBMS_OUTPUT.PUT_LINE('是否还有下一页 (1=是, 0=否): ' || v_has_more);
LOOP
FETCH v_cursor INTO v_rec;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sort: ' || v_rec.Sort || ', RV: ' || v_rec.RowVersion || ', IANA: ' || v_rec.IanaTimeZoneId || ', EN: ' || v_rec.DisplayNameEN);
END LOOP;
CLOSE v_cursor;
END;
/
# encoding: utf-8
# 版权所有 2026 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：Oracle 21c
# Author : geovindu,Geovin Du 涂聚文.
# IDE : PyCharm 2024.3.6 python 3.11
# os : windows 10
# database : mysql 9.0 sql server 2019, postgreSQL 17.0 Oracle 21c Neo4j
# Datetime : 2026/9/12 9:51
# User : geovindu
# Product : PyCharm
# Project : Pysimple
# File : oracelpaingtwo.py
# -*- coding: utf-8 -*-
import uuid
import oracledb
============ 连接参数（改成你自己的） ============
ORACLE = {
"user": "C##GEOVINDU",
"password": "geovindu",
"dsn": "localhost:1521/TechnologyGame",
}
================================================
conn = oracledb.connect(**ORACLE)
cur = conn.cursor()
def fmt_uuid(raw):
return str(uuid.UUID(bytes=raw)) if raw else None
def page(keyword=None, page_number=1, page_size=20):
"""传统页码分页"""
p_total = cur.var(oracledb.NUMBER)
p_cur = cur.var(oracledb.CURSOR)
cur.callproc("sp_timezone_page", [keyword, page_number, page_size, p_total, p_cur])
total = p_total.getvalue()
rows = p_cur.getvalue().fetchall()
return rows, (total or 0)
def cursor_page(keyword=None, last_sort=None, last_row_version=None, page_size=20):
"""游标分页"""
p_has_more = cur.var(oracledb.NUMBER)
p_cur = cur.var(oracledb.CURSOR)
cur.callproc("sp_timezone_cursor", [keyword, last_sort, last_row_version, page_size, p_has_more, p_cur])
has_more = int(p_has_more.getvalue() or 0)
rows = p_cur.getvalue().fetchall()
return rows, bool(has_more)
================= 【新增】全文检索游标分页 =================
def search_page(keyword, last_sort=None, last_row_version=None, page_size=20):
"""全文检索分页: 返回 (rows, has_more)"""
if not keyword:
raise ValueError("search_page 必须提供 keyword")
p_has_more = cur.var(oracledb.NUMBER)
p_cur = cur.var(oracledb.CURSOR)
cur.callproc("sp_timezone_search_ft", [keyword, last_sort, last_row_version, page_size, p_has_more, p_cur])
has_more = int(p_has_more.getvalue() or 0)
rows = p_cur.getvalue().fetchall()
return rows, bool(has_more)
============================================================
def print_rows(rows):
for r in rows:
print(f" sort={r[4]:>3} rv={r[5]:>4} {r[1]:<28} {r[6]}")
def demo_page():
print("=" * 70)
print("[1] 传统页码分页: 第 1 页, 每页 5 条, 关键词='北'")
print("=" * 70)
rows, total = page(keyword="北", page_number=1, page_size=5)
print(f"总命中: {total} 条")
print_rows(rows)
def demo_cursor():
print("=" * 70)
print("[2] 游标分页: 关键词='島', 每页 3 条")
print("=" * 70)
keyword = "島"
last_sort, last_rv = None, None
page_no = 1
while True:
rows, has_more = cursor_page(keyword, last_sort, last_rv, 3)
if not rows: break
print(f"– 第 {page_no} 页 –")
print_rows(rows)
last_sort, last_rv = rows[-1][4], rows[-1][11]
if not has_more: break
page_no += 1
================= 【新增】全文检索 Demo =================
def demo_search():
print("=" * 70)
print("[3] 全文检索分页: 关键词='Pacific', 每页 5 条")
print("=" * 70)
keyword = "Pacific"
last_sort, last_rv = None, None
page_no = 1
while True:
rows, has_more = search_page(keyword, last_sort, last_rv, 5)
if not rows: break
print(f"– 搜索 '{keyword}' 第 {page_no} 页 –")
print_rows(rows)
last_sort, last_rv = rows[-1][4], rows[-1][11]
if not has_more: break
page_no += 1
=========================================================
if name == "main":
try:
demo_page()
demo_cursor()
demo_search() # 【新增】运行全文检索测试
finally:
cur.close()
conn.close()
输出：
评论前必须登录！
注册