人大金仓——匿名代码块（ANONYMOUS BLOCK）示例

环境：

ksql (Kingbase) V008R006C006B0013

需求：

检查（多张）表是否有tenantid字段，以及tenantid字段值是否满足条件

SQL：

DECLARE 
-- 输入要检查的租户ID
v_tenantid number := 104;
-- 输入要检查的表，多个表用,分隔
v_tables_str varchar := 't1,t2, t3 ';
v_sql varchar;
v_table_name varchar;
v_count_err number;
BEGIN 
    FOR tablesList IN (
        SELECT
        regexp_substr(v_tables_str, '[^,]+', 1, LEVEL) name
    FROM
        dual
    CONNECT BY
        LEVEL <= LENGTH(v_tables_str)-LENGTH(REPLACE(v_tables_str, ','))+ 1
    )
    LOOP 
        v_table_name := REPLACE(tablesList.name, ' ', '');
        v_sql := '';
        -- raise notice 'Name：%', v_table_name;
        
        if (select count(*) as ct1 from information_schema.columns where table_name = v_table_name  ) = 0 THEN
            raise notice '>>>>>>>>>表不存在，表名【%】', v_table_name;
        ELSEif (select count(*) as ct1 from information_schema.columns where table_name = v_table_name and column_name = 'tenantid' ) = 0 then
            raise notice '>>>>>>>>>没有tenantid字段，表名【%】', v_table_name;
        ELSE 
            v_sql := 'select count(1) from ' || v_table_name || ' where tenantid is null or tenantid <> ' || v_tenantid;
            EXECUTE IMMEDIATE v_sql INTO v_count_err;
            
            IF v_count_err > 0 THEN 
                raise notice '>>>>>>>>>tenantid字段值非法，表名【%】，查询sql【%】', v_table_name, v_sql;
            END IF;
        end if;
    END LOOP;
END;

---

自定义RECORD类型示例：

DECLARE 

-- 自定义Record类型
TYPE MyRecord IS RECORD(ID VARCHAR(36),TreeInfo_Layer INT, TreeInfo_Path VARCHAR(80));
v_MyRecord MyRecord;


BEGIN 
	SELECT ID,TreeInfo_Layer,TreeInfo_Path INTO v_MyRecord.ID,v_MyRecord.TreeInfo_Layer,v_MyRecord.TreeInfo_Path FROM Xxx WHERE Xxx.ID='唯一标识';	
END;