sql: Oracle 21c paging

 

-- ============================================================
-- 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()

 

 

输出：