1 建表语句
CREATE TABLE {模式}."SYS_DIVISIONS"
(
"TENANT" VARCHAR(50) NOT NULL,
"DIVISION_INNER_ID" VARCHAR(50) NOT NULL,
"DIVISION_INNER_NAME" VARCHAR(50),
"DIVISION_CODE" VARCHAR(50),
"ORG_INNER_NAME" VARCHAR(50),
"PARENT_INNER_NAME" VARCHAR(50),
"LEVEL_CODE" VARCHAR(50),
"SX_" VARCHAR(50),
"IS_LEAF" SMALLINT,
"IS_ABORT" SMALLINT,
"IS_DELETE" SMALLINT,
"OP_FIRST" VARCHAR(50),
"OP_FIRST_TIME" DATETIME(6),
"OP_LAST" VARCHAR(50),
"OP_LAST_TIME" DATETIME(6),
"OP_DELETE" VARCHAR(50),
"OP_DELETE_TIME" DATETIME(6),
"PARENT_INNER_NAME_ALTER" VARCHAR(50),
"DIVISION_NAME" VARCHAR(255),
"SX" INTEGER,
CLUSTER PRIMARY KEY("TENANT", "DIVISION_INNER_ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE {模式}."SYS_DIVISIONS" IS '单位表';
CREATE TABLE {模式}."SYS_ORGS"
(
"TENANT" VARCHAR(50) DEFAULT 'lcz' NOT NULL,
"ORG_INNER_ID" VARCHAR(50) NOT NULL,
"ORG_INNER_NAME" VARCHAR(50) NOT NULL,
"ORG_NAME" VARCHAR(255) NOT NULL,
"PARENT_INNER_NAME" VARCHAR(50),
"LEVEL_CODE" VARCHAR(255),
"IS_LEAF" SMALLINT DEFAULT 1 NOT NULL,
"ORG_CODE" VARCHAR(50) NOT NULL,
"IS_ABORT" SMALLINT DEFAULT 0 NOT NULL,
"OP_FIRST" VARCHAR(50),
"OP_FIRST_TIME" DATETIME(6) NOT NULL,
"OP_LAST" VARCHAR(50),
"OP_LAST_TIME" DATETIME(6) NOT NULL,
"IS_DELETE" SMALLINT DEFAULT 0 NOT NULL,
"OP_DELETE" VARCHAR(50),
"OP_DELETE_TIME" DATETIME(6),
"PARENT_INNER_NAME_ALTER" VARCHAR(50),
"IS_MANAGED" SMALLINT DEFAULT 0 NOT NULL,
"DIVISION_INNER_NAME" VARCHAR(50),
CONSTRAINT "IDX_T_INNER_NAME" UNIQUE("TENANT", "ORG_INNER_NAME"),
CONSTRAINT "IDX_T_CODE" UNIQUE("TENANT", "ORG_CODE"),
NOT CLUSTER PRIMARY KEY("TENANT", "ORG_INNER_ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE {模式}."SYS_ORGS" IS '系统-组织（部门）表-符合标准规范';
COMMENT ON COLUMN {模式}."SYS_ORGS"."DIVISION_INNER_NAME" IS '标准字段-所属机构NAME';
COMMENT ON COLUMN {模式}."SYS_ORGS"."IS_ABORT" IS '标准字段-组织禁用标记';
COMMENT ON COLUMN {模式}."SYS_ORGS"."IS_DELETE" IS '标准字段-假删标记';
COMMENT ON COLUMN {模式}."SYS_ORGS"."IS_LEAF" IS '标准字段-组织是否为叶节点';
COMMENT ON COLUMN {模式}."SYS_ORGS"."IS_MANAGED" IS '标准字段-是否机构';
COMMENT ON COLUMN {模式}."SYS_ORGS"."LEVEL_CODE" IS '标准字段-组织层码';
COMMENT ON COLUMN {模式}."SYS_ORGS"."OP_FIRST" IS '标准字段-创建人工号';
COMMENT ON COLUMN {模式}."SYS_ORGS"."OP_FIRST_TIME" IS '标准字段-创建时间';
COMMENT ON COLUMN {模式}."SYS_ORGS"."OP_LAST" IS '标准字段-修改人工号';
COMMENT ON COLUMN {模式}."SYS_ORGS"."OP_LAST_TIME" IS '标准字段-修改时间';
COMMENT ON COLUMN {模式}."SYS_ORGS"."ORG_CODE" IS '标准字段-组织代码';
COMMENT ON COLUMN {模式}."SYS_ORGS"."ORG_INNER_ID" IS '标准字段-主键-组织 ID';
COMMENT ON COLUMN {模式}."SYS_ORGS"."ORG_INNER_NAME" IS '标准字段-组织 NAME';
COMMENT ON COLUMN {模式}."SYS_ORGS"."ORG_NAME" IS '标准字段-组织 ALIAS';
COMMENT ON COLUMN {模式}."SYS_ORGS"."PARENT_INNER_NAME" IS '标准字段-上级组织的 NAME';
COMMENT ON COLUMN {模式}."SYS_ORGS"."PARENT_INNER_NAME_ALTER" IS '标准字段-上级组织Name 修改新值';
COMMENT ON COLUMN {模式}."SYS_ORGS"."TENANT" IS '标准字段-主键-租户';
CREATE TABLE {模式}."SYS_ROLES"
(
"TENANT" VARCHAR(50) DEFAULT 'lcz' NOT NULL,
"ROLE_INNER_ID" VARCHAR(50) NOT NULL,
"ROLE_INNER_NAME" VARCHAR(50) NOT NULL,
"ROLE_NAME" VARCHAR(50) NOT NULL,
"SX" SMALLINT DEFAULT 0 NOT NULL,
"IS_ABORT" SMALLINT DEFAULT 0 NOT NULL,
"OP_FIRST" VARCHAR(50),
"OP_FIRST_TIME" DATETIME(6) NOT NULL,
"OP_LAST" VARCHAR(50),
"OP_LAST_TIME" DATETIME(6) NOT NULL,
"OP_DELETE" VARCHAR(50),
"OP_DELETE_TIME" DATETIME(6),
"IS_DELETE" SMALLINT DEFAULT 0 NOT NULL,
"DIVISION_INNER_NAME" VARCHAR(50),
"TYPE_" SMALLINT,
"IS_BASIC_PROPERTY" SMALLINT,
CONSTRAINT "IDX_T_NAME" UNIQUE("TENANT", "ROLE_INNER_NAME"),
NOT CLUSTER PRIMARY KEY("TENANT", "ROLE_INNER_ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE {模式}."SYS_ROLES" IS '系统-角色表-符合标准规范';
COMMENT ON COLUMN {模式}."SYS_ROLES"."DIVISION_INNER_NAME" IS '标准字段-所属机构';
COMMENT ON COLUMN {模式}."SYS_ROLES"."IS_ABORT" IS '标准字段-禁用标记';
COMMENT ON COLUMN {模式}."SYS_ROLES"."IS_DELETE" IS '标准字段-假删标记';
COMMENT ON COLUMN {模式}."SYS_ROLES"."OP_FIRST" IS '标准字段-创建人工号';
COMMENT ON COLUMN {模式}."SYS_ROLES"."OP_FIRST_TIME" IS '标准字段-创建时间';
COMMENT ON COLUMN {模式}."SYS_ROLES"."OP_LAST" IS '标准字段-修改人工号';
COMMENT ON COLUMN {模式}."SYS_ROLES"."OP_LAST_TIME" IS '标准字段-修改时间';
COMMENT ON COLUMN {模式}."SYS_ROLES"."ROLE_INNER_ID" IS '标准字段-角色 ID';
COMMENT ON COLUMN {模式}."SYS_ROLES"."ROLE_INNER_NAME" IS '标准字段-角色 NAME';
COMMENT ON COLUMN {模式}."SYS_ROLES"."ROLE_NAME" IS '标准字段-角色 ALIAS';
COMMENT ON COLUMN {模式}."SYS_ROLES"."SX" IS '标准字段-顺序';
COMMENT ON COLUMN {模式}."SYS_ROLES"."TENANT" IS '标准字段-租户';
CREATE TABLE {模式}."SYS_TENANTS"
(
"TENANT" VARCHAR(50) NOT NULL,
"USER_CODE_PREFIX" VARCHAR(50),
"DEFAULT_ROLES" VARCHAR(255),
"OP_LAST" VARCHAR(50) DEFAULT '/',
"OP_LAST_TIME" TIMESTAMP(0) NOT NULL,
"DEFAULT_ORG" VARCHAR(255),
NOT CLUSTER PRIMARY KEY("TENANT")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON COLUMN {模式}."SYS_TENANTS"."DEFAULT_ORG" IS '默认组织';
COMMENT ON COLUMN {模式}."SYS_TENANTS"."DEFAULT_ROLES" IS '新建用户默认分配的角色，支持一个或多个角色。是 一个或多个 role_inner_name 值的逗号连接';
COMMENT ON COLUMN {模式}."SYS_TENANTS"."OP_LAST" IS '修改人';
COMMENT ON COLUMN {模式}."SYS_TENANTS"."OP_LAST_TIME" IS '修改时间';
COMMENT ON COLUMN {模式}."SYS_TENANTS"."TENANT" IS '租户，对应乐创者的 userTenantIdent';
COMMENT ON COLUMN {模式}."SYS_TENANTS"."USER_CODE_PREFIX" IS '员工工号的前缀';
CREATE TABLE {模式}."SYS_USERS"
(
"TENANT" VARCHAR(50) DEFAULT 'lcz' NOT NULL,
"USER_INNER" VARCHAR(50) NOT NULL,
"USER_NO" INT,
"USER_CODE" VARCHAR(50),
"USER_NAME" VARCHAR(50),
"LOGIN_NAME" VARCHAR(50) NOT NULL,
"password" VARCHAR(255),
"SX" INT DEFAULT 0 NOT NULL,
"ORG_INNER_NAME" VARCHAR(50) NOT NULL,
"SUPERVISOR_INNER" VARCHAR(50),
"SUPERVISOR_INNER_ALTER" VARCHAR(50),
"LEVEL_CODE_SUPERVISOR" VARCHAR(255),
"IS_LEAF_SUPERVISOR" SMALLINT DEFAULT 1 NOT NULL,
"IS_ABORT" SMALLINT DEFAULT 0 NOT NULL,
"IS_DELETE" SMALLINT DEFAULT 0 NOT NULL,
"OP_FIRST" VARCHAR(50),
"OP_FIRST_TIME" DATETIME(6) NOT NULL,
"OP_LAST" VARCHAR(50),
"OP_LAST_TIME" DATETIME(6) NOT NULL,
"OP_DELETE" VARCHAR(50),
"OP_DELETE_TIME" DATETIME(6),
"ID_CARD" VARCHAR(18),
"SEX" SMALLINT,
"IS_MARRIED" SMALLINT,
"BIRTH_DATE" DATE,
"ON_BOARD_DATE" DATE,
"ENROLL_DATE" DATE,
"QUIT_DATE" DATE,
"CONTRACT_BEGIN_DATE" DATE,
"CONTRACT_END_DATE" DATE,
"MOBILE" VARCHAR(255),
"PHONE" VARCHAR(50),
"EMAIL" VARCHAR(100),
"QQ" VARCHAR(50),
"POSTAL_CODE" VARCHAR(50),
"DING_USER_ID" VARCHAR(255),
"DING_UNION_ID" VARCHAR(255),
"DING_GOV_EMP_CODE" VARCHAR(50),
"DING_GOV_ACCOUNT_ID" VARCHAR(50),
"WECORP_USER_ID" VARCHAR(200),
"WECHAT_UNION_ID" VARCHAR(50),
"USER_NO_ALTER" INT,
"LEVEL_CODE_ORDER" INT,
"DIVISION_INNER_NAME" VARCHAR(50),
"ACCOUNT_TYPE" SMALLINT DEFAULT 0 NOT NULL,
"MAIN_ACCOUNT" VARCHAR(50),
"DEFAULT_ACCOUNT" VARCHAR(50),
CONSTRAINT "IDX_USER_LOGIN" UNIQUE("TENANT", "LOGIN_NAME"),
NOT CLUSTER PRIMARY KEY("TENANT", "USER_INNER")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE {模式}."SYS_USERS" IS '系统-用户（员工）表-符合标准规范';
COMMENT ON COLUMN {模式}."SYS_USERS"."ACCOUNT_TYPE" IS '标准字段-主子账号类型';
COMMENT ON COLUMN {模式}."SYS_USERS"."BIRTH_DATE" IS '标准字段-业务字段-生日';
COMMENT ON COLUMN {模式}."SYS_USERS"."CONTRACT_BEGIN_DATE" IS '标准字段-业务字段-合同开始日期';
COMMENT ON COLUMN {模式}."SYS_USERS"."CONTRACT_END_DATE" IS '标准字段-业务字段-合同结束日期';
COMMENT ON COLUMN {模式}."SYS_USERS"."DEFAULT_ACCOUNT" IS '标准字段-默认账号';
COMMENT ON COLUMN {模式}."SYS_USERS"."DING_GOV_ACCOUNT_ID" IS '标准字段-业务字段-政务钉账户编码';
COMMENT ON COLUMN {模式}."SYS_USERS"."DING_GOV_EMP_CODE" IS '标准字段-业务字段-政务钉人员编码';
COMMENT ON COLUMN {模式}."SYS_USERS"."DING_UNION_ID" IS '标准字段-业务字段-钉钉用户全局编码';
COMMENT ON COLUMN {模式}."SYS_USERS"."DING_USER_ID" IS '标准字段-业务字段-钉钉企业内部用户编码';
COMMENT ON COLUMN {模式}."SYS_USERS"."DIVISION_INNER_NAME" IS '标准字段-所属机构';
COMMENT ON COLUMN {模式}."SYS_USERS"."EMAIL" IS '标准字段-业务字段-电邮地址';
COMMENT ON COLUMN {模式}."SYS_USERS"."ENROLL_DATE" IS '标准字段-业务字段-转正日期';
COMMENT ON COLUMN {模式}."SYS_USERS"."ID_CARD" IS '标准字段-业务字段-身份证号码';
COMMENT ON COLUMN {模式}."SYS_USERS"."IS_ABORT" IS '标准字段-停用标记';
COMMENT ON COLUMN {模式}."SYS_USERS"."IS_DELETE" IS '标准字段-假删标记';
COMMENT ON COLUMN {模式}."SYS_USERS"."IS_LEAF_SUPERVISOR" IS '标准字段-隶属关系是否为叶节点';
COMMENT ON COLUMN {模式}."SYS_USERS"."IS_MARRIED" IS '标准字段-业务字段-是否已婚';
COMMENT ON COLUMN {模式}."SYS_USERS"."LEVEL_CODE_ORDER" IS '标准字段-业务字段-隶属关系排序字段';
COMMENT ON COLUMN {模式}."SYS_USERS"."LEVEL_CODE_SUPERVISOR" IS '标准字段-用户的隶属关系层码';
COMMENT ON COLUMN {模式}."SYS_USERS"."LOGIN_NAME" IS '标准字段-登录名-用户 NAME';
COMMENT ON COLUMN {模式}."SYS_USERS"."MAIN_ACCOUNT" IS '标准字段-关联主账号';
COMMENT ON COLUMN {模式}."SYS_USERS"."MOBILE" IS '标准字段-业务字段-手机号码';
COMMENT ON COLUMN {模式}."SYS_USERS"."ON_BOARD_DATE" IS '标准字段-业务字段-入职日期';
COMMENT ON COLUMN {模式}."SYS_USERS"."OP_FIRST" IS '标准字段-创建人工号';
COMMENT ON COLUMN {模式}."SYS_USERS"."OP_FIRST_TIME" IS '标准字段-创建时间';
COMMENT ON COLUMN {模式}."SYS_USERS"."OP_LAST" IS '标准字段-修改人工号';
COMMENT ON COLUMN {模式}."SYS_USERS"."OP_LAST_TIME" IS '标准字段-修改时间';
COMMENT ON COLUMN {模式}."SYS_USERS"."ORG_INNER_NAME" IS '标准字段-所属组织的组织 NAME';
CREATE TABLE {模式}."SYS_USERS_ROLES"
(
"USER_ROLE_ID" VARCHAR(50) NOT NULL,
"TENANT" VARCHAR(50) DEFAULT 'lcz' NOT NULL,
"USER_INNER" VARCHAR(50) NOT NULL,
"ROLE_INNER_ID" VARCHAR(50) NOT NULL,
"DIVISION_INNER_NAME" VARCHAR(50),
"ROLE_TYPE" SMALLINT,
"ORG_INNER_NAME" VARCHAR(50),
"OP_FIRST" VARCHAR(50),
"OP_FIRST_TIME" DATETIME(6),
NOT CLUSTER PRIMARY KEY("USER_ROLE_ID"),
CONSTRAINT "IDX_T_USER_ROLE" UNIQUE("TENANT", "USER_INNER", "ROLE_INNER_ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
COMMENT ON TABLE {模式}."SYS_USERS_ROLES" IS '系统-用户角色映射关系表-符合标准规范';
COMMENT ON COLUMN {模式}."SYS_USERS_ROLES"."ROLE_INNER_ID" IS '标准字段-角色 ID';
COMMENT ON COLUMN {模式}."SYS_USERS_ROLES"."TENANT" IS '标准字段-租户';
COMMENT ON COLUMN {模式}."SYS_USERS_ROLES"."USER_INNER" IS '标准字段-用户 ID';
COMMENT ON COLUMN {模式}."SYS_USERS_ROLES"."USER_ROLE_ID" IS '标准字段-映射关系主键';
2 存储过程
CREATE OR REPLACE FUNCTION {模式}.FN_BASE10_TO_BASE36 (V_NUM IN NUMBER)
RETURN VARCHAR IS RESULT VARCHAR(8);
NUM NUMBER;
TMP NUMBER;
TEMP NUMBER;
BEGIN
NUM := V_NUM;
TMP := TRUNC(NUM / 36);
WHILE NUM > 0 LOOP
TEMP := MOD(NUM, 36);
IF TEMP < 10 THEN
RESULT := TEMP || RESULT;
ELSE
RESULT := CHR(TEMP + 55) || RESULT;
END IF;
NUM := TMP;
TMP := TRUNC(NUM / 36);
END LOOP;
return RESULT;
END;
CREATE OR REPLACE FUNCTION {模式}.FN_BASE36_TO_BASE10 (v_36_data VARCHAR)
RETURN NUMBER is v_data number(18);
BEGIN
select sum(data) into v_data from
(
select (
case substr(upper(v_36_data),rownum,1)
when 'A' then '10'
when 'B' then '11'
when 'C' then '12'
when 'D' then '13'
when 'E' then '14'
when 'F' then '15'
when 'G' then '16'
when 'H' then '17'
when 'I' then '18'
when 'J' then '19'
when 'K' then '20'
when 'L' then '21'
when 'M' then '22'
when 'N' then '23'
when 'O' then '24'
when 'P' then '25'
when 'Q' then '26'
when 'R' then '27'
when 'S' then '28'
when 'T' then '29'
when 'U' then '30'
when 'V' then '31'
when 'W' then '32'
when 'X' then '33'
when 'Y' then '34'
when 'Z' then '35'
else substr(v_36_data,rownum,1) end
)*power(36,length(v_36_data)-rownum) data
from dual
connect by rownum<=length(v_36_data)
);
return v_data;
exception
when others then
return null;
END;
/***Manager***/CREATE OR REPLACE FUNCTION {模式}.REPLACE_LEVELCODE("P_LEVELCODE" IN VARCHAR2(255),"P_OLD_PARENT_LEVELCODE" IN VARCHAR2(255),"P_NEW_PARENT_LEVELCODE" IN VARCHAR2(255))
RETURN VARCHAR2(255)
AUTHID DEFINER
IS
FUNCTIONRESULT VARCHAR2(1000);
BEGIN
FUNCTIONRESULT := P_NEW_PARENT_LEVELCODE||SUBSTR(P_LEVELCODE, LENGTH(P_OLD_PARENT_LEVELCODE) + 1);
RETURN(FUNCTIONRESULT);
END REPLACE_LEVELCODE;
/***Manager***/CREATE OR REPLACE FUNCTION {模式}.SP_SYS_LEVEL_CODE("V_TENANT" IN VARCHAR(50),"V_USER_INNER" IN VARCHAR(50),"V_USER_ID" IN NUMBER)
RETURN VARCHAR(255)
AUTHID DEFINER
AS
V_SUPERVISOR_INNER NVARCHAR2(50) DEFAULT NULL;
V_LEVEL_CODE NVARCHAR2(255) DEFAULT '';
PARENT_LEVEL_CODE NVARCHAR2(255) DEFAULT '';
BEGIN
SELECT SUPERVISOR_INNER INTO V_SUPERVISOR_INNER FROM SYS_USERS
WHERE
TENANT = V_TENANT AND
USER_INNER = V_USER_INNER;
IF V_SUPERVISOR_INNER IS NOT NULL
THEN
SELECT LEVEL_CODE_SUPERVISOR INTO PARENT_LEVEL_CODE FROM SYS_USERS
WHERE
TENANT = V_TENANT AND
USER_INNER = V_SUPERVISOR_INNER;
END IF;
RETURN CONCAT(PARENT_LEVEL_CODE, LPAD(FN_BASE10_TO_BASE36(V_USER_ID), 3, '0'));
END;
/***Manager***/CREATE OR REPLACE FUNCTION {模式}.SP_SYS_NEXT_TENANT_USER_ID("V_TENANT" IN VARCHAR(50))
RETURN NUMBER
AUTHID DEFINER
AS
I_USER_ID NUMBER DEFAULT NULL;
BEGIN
SELECT MAX(USER_NO) INTO I_USER_ID FROM {模式}.SYS_USERS WHERE SYS_USERS.TENANT = V_TENANT;
IF I_USER_ID IS NULL
THEN I_USER_ID := 0;
END IF;
RETURN I_USER_ID + 1;
END;
CREATE OR REPLACE FUNCTION {模式}.SP_SYS_ORG_NEXT_LEVELCODE(V_TENANT IN VARCHAR2, PARENTORGINNERNAME IN VARCHAR2, SELFORGINNERNAME IN VARCHAR2) RETURN VARCHAR2 IS
FUNCTIONRESULT VARCHAR2(1000);
PARENT_LEVEL_CODE VARCHAR(255) :='';
MAXLEVELCODE VARCHAR(255) :='';
MAXLEVELNUM INT := NULL;
BEGIN
-- 父节点的层码
SELECT LEVEL_CODE INTO PARENT_LEVEL_CODE FROM {模式}.SYS_ORGS WHERE ORG_INNER_NAME = PARENTORGINNERNAME AND TENANT = V_TENANT;
-- 子节点的最大层码
SELECT MAX(LEVEL_CODE) INTO MAXLEVELCODE FROM {模式}.SYS_ORGS
WHERE PARENT_INNER_NAME = PARENTORGINNERNAME AND TENANT = V_TENANT AND IS_DELETE = 0 AND ORG_INNER_NAME <> SELFORGINNERNAME;
IF MAXLEVELCODE = '' THEN
MAXLEVELCODE := '000';
RETURN CONCAT(PARENT_LEVEL_CODE, MAXLEVELCODE);
END IF;
-- MYSQL用，实际该RETURN前面就RETURN了
IF MAXLEVELCODE IS NULL THEN
MAXLEVELCODE := '000';
RETURN CONCAT(PARENT_LEVEL_CODE, MAXLEVELCODE);
END IF;
--未考虑异常捕捉，且每节点最大1000个子节点（未处理成36位）
MAXLEVELNUM := MOD( TO_NUMBER(MAXLEVELCODE) , 1000);
-- SET MAXLEVELNUM = CONV(RIGHT(MAXLEVELCODE, 3),36,10);
RETURN CONCAT(PARENT_LEVEL_CODE, LPAD(MAXLEVELNUM+1,3,'0'));
END SP_SYS_ORG_NEXT_LEVELCODE;
/***Manager***/CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_ORG_ADD("V_TENANT" IN VARCHAR(50),"V_ORG_INNER" IN VARCHAR(50))
AUTHID DEFINER
AS
V_ORG_PARENT_INNER_NAME VARCHAR2(50);
V_ORG_PARENT_INNER_NAME_ALTER VARCHAR2(50);
BEGIN
SELECT PARENT_INNER_NAME,PARENT_INNER_NAME_ALTER INTO V_ORG_PARENT_INNER_NAME,V_ORG_PARENT_INNER_NAME_ALTER
FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND ORG_INNER_ID = V_ORG_INNER;
IF V_ORG_PARENT_INNER_NAME = V_ORG_PARENT_INNER_NAME_ALTER THEN
-- DO NOTHING
V_ORG_PARENT_INNER_NAME_ALTER := V_ORG_PARENT_INNER_NAME_ALTER;
ELSE
UPDATE {模式}.SYS_ORGS
SET
LEVEL_CODE = SP_SYS_ORG_NEXT_LEVELCODE(V_TENANT,V_ORG_PARENT_INNER_NAME,'NULL'),
PARENT_INNER_NAME_ALTER = V_ORG_PARENT_INNER_NAME
WHERE
TENANT = V_TENANT AND ORG_INNER_ID = V_ORG_INNER;
END IF;
-- 上级处理是否叶子
UPDATE {模式}.SYS_ORGS SET IS_LEAF = 0 WHERE TENANT = V_TENANT AND ORG_INNER_NAME = V_ORG_PARENT_INNER_NAME;
END;
/***Manager***/CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_ORG_DELETE("V_TENANT" IN VARCHAR(50),"V_ORG_INNER" IN VARCHAR(50),"V_PARENT_ORG_INNER" IN VARCHAR(50))
AUTHID DEFINER
AS
V_PARENT_LEAFCOUNT INT;
V_LEVELCODE VARCHAR2(255);
BEGIN
SELECT LEVEL_CODE INTO V_LEVELCODE FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND PARENT_INNER_NAME = V_ORG_INNER;
-- 查询更新上级层码的是否叶子
SELECT COUNT(ORG_INNER_ID) INTO V_PARENT_LEAFCOUNT FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND PARENT_INNER_NAME = V_PARENT_ORG_INNER;
IF V_PARENT_LEAFCOUNT = 0 THEN
UPDATE {模式}.SYS_ORGS SET IS_LEAF = 1
WHERE TENANT = V_TENANT AND ORG_INNER_NAME = V_PARENT_ORG_INNER;
END IF;
-- 删除子用户 及 子组织 及子组织下的用户
IF V_LEVELCODE IS NOT NULL AND V_LEVELCODE != '' THEN
SP_SYS_ORG_DEL_CURSOR(V_TENANT, V_LEVELCODE);
END IF;
END;
CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_ORG_DEL_CURSOR(V_TENANT IN VARCHAR2, V_DEL_ORG_LEVELCODE IN VARCHAR2 ) IS
ROW_ORG_NAME VARCHAR2(50);
CURSOR W_CURSOR IS
SELECT ORG_INNER_NAME FROM {模式}.SYS_ORGS WHERE LEVEL_CODE LIKE V_DEL_ORG_LEVELCODE || '%' AND IS_DELETE = 0 AND TENANT = V_TENANT;
--游标变量
W_CURSOR_ROW W_CURSOR%ROWTYPE;
BEGIN
-- 删除子用户 及 子组织 及子组织下的用户
OPEN W_CURSOR;
LOOP
FETCH W_CURSOR INTO W_CURSOR_ROW;
EXIT WHEN W_CURSOR%NOTFOUND;
-- 先删除用户
UPDATE {模式}.SYS_USERS SET IS_DELETE = 1 WHERE ORG_INNER_NAME = W_CURSOR_ROW.ORG_INNER_NAME AND TENANT = V_TENANT;
-- 删除组织
UPDATE {模式}.SYS_ORGS SET IS_DELETE = 1 WHERE ORG_INNER_NAME = W_CURSOR_ROW.ORG_INNER_NAME AND TENANT = V_TENANT;
END LOOP;
CLOSE W_CURSOR;
END SP_SYS_ORG_DEL_CURSOR;
CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_ORG_MODIFY(V_TENANT IN VARCHAR2, V_ORG_INNER IN VARCHAR2, V_OLD_INNER_NAME IN VARCHAR2 ) IS
V_PARENT VARCHAR2(255) :='';
V_PARENT_NAME VARCHAR2(255) :='';
V_PARENT_NAME_ALTER VARCHAR2(255) :='';
V_ORG_INNER_NAME VARCHAR2(50) :='';
V_OLD_PARENT_LEAFCOUNT INT;
V_OLD_LEVEL_CODE VARCHAR2(255) :='' ;
V_NEW_LEVEL_CODE VARCHAR2(255) :='' ;
BEGIN
SELECT ORG_INNER_NAME, LEVEL_CODE, PARENT_INNER_NAME, PARENT_INNER_NAME_ALTER INTO V_ORG_INNER_NAME, V_OLD_LEVEL_CODE, V_PARENT_NAME, V_PARENT_NAME_ALTER
FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND ORG_INNER_ID = V_ORG_INNER;
SELECT PARENT_INNER_NAME INTO V_PARENT FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND ORG_INNER_NAME = V_ORG_INNER_NAME;
-- 如果父编码变化了则需要修改层码，否则不需要
IF V_PARENT_NAME != V_PARENT_NAME_ALTER THEN
V_NEW_LEVEL_CODE := SP_SYS_ORG_NEXT_LEVELCODE(V_TENANT,V_PARENT_NAME,V_ORG_INNER_NAME);
-- 更新层码
UPDATE {模式}.SYS_ORGS
SET
LEVEL_CODE = REPLACE_LEVELCODE(LEVEL_CODE,V_OLD_LEVEL_CODE,V_NEW_LEVEL_CODE)
WHERE
TENANT = V_TENANT AND LEVEL_CODE LIKE V_OLD_LEVEL_CODE || '%' AND IS_DELETE = 0;
-- 更新PARENT_INNER_NAME_ALTER字段
UPDATE {模式}.SYS_ORGS
SET
PARENT_INNER_NAME_ALTER = PARENT_INNER_NAME
WHERE
TENANT = V_TENANT AND ORG_INNER_ID = V_ORG_INNER;
-- 查询更新老上级层码的是否叶子
SELECT COUNT(ORG_INNER_ID) INTO V_OLD_PARENT_LEAFCOUNT FROM {模式}.SYS_ORGS WHERE TENANT = V_TENANT AND PARENT_INNER_NAME = V_PARENT_NAME_ALTER;
IF V_OLD_PARENT_LEAFCOUNT = 0 THEN
UPDATE {模式}.SYS_ORGS SET IS_LEAF = 1
WHERE TENANT = V_TENANT AND ORG_INNER_NAME = V_PARENT_NAME_ALTER;
END IF;
-- 更新当前上级为非叶子节点
UPDATE {模式}.SYS_ORGS SET IS_LEAF = 0
WHERE TENANT = V_TENANT AND ORG_INNER_NAME = V_PARENT_NAME AND IS_LEAF = 1;
END IF;
-- 如果内部名称INNER_NAME变化了则需要子组织和用户表，否则不需要
IF V_ORG_INNER_NAME != V_OLD_INNER_NAME AND V_OLD_INNER_NAME IS NOT NULL AND V_OLD_INNER_NAME != '' THEN
-- 更新子组织
UPDATE {模式}.SYS_ORGS
SET
PARENT_INNER_NAME = V_ORG_INNER_NAME
WHERE
TENANT = V_TENANT AND PARENT_INNER_NAME = V_OLD_INNER_NAME AND IS_DELETE = 0;
UPDATE {模式}.SYS_ORGS
SET
PARENT_INNER_NAME_ALTER = V_ORG_INNER_NAME
WHERE
TENANT = V_TENANT AND PARENT_INNER_NAME_ALTER = V_OLD_INNER_NAME AND IS_DELETE = 0;
-- 更新组织下用户
UPDATE {模式}.SYS_USERS
SET
ORG_INNER_NAME = V_ORG_INNER_NAME
WHERE
TENANT = V_TENANT AND ORG_INNER_NAME = V_OLD_INNER_NAME AND IS_DELETE = 0;
END IF;
END;
/***Manager***/CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_USER_ADD("V_TENANT" IN VARCHAR(32767),"V_USER_INNER" IN VARCHAR(32767))
AUTHID DEFINER
AS
V_USER_CODE VARCHAR2(50) :=NULL;
V_SUPERVISOR_INNER VARCHAR2(50) :=NULL;
I_USER_NO INT :=NULL;
BEGIN
SELECT USER_CODE,USER_NO INTO V_USER_CODE,I_USER_NO FROM {模式}.SYS_USERS WHERE TENANT = V_TENANT AND USER_INNER = V_USER_INNER;
IF I_USER_NO IS NOT NULL AND I_USER_NO != '' THEN
-- START TRANSACTION;
--设置本员工的字段
UPDATE {模式}.SYS_USERS
SET
LEVEL_CODE_SUPERVISOR = SP_SYS_LEVEL_CODE(V_TENANT,USER_INNER,I_USER_NO),
SUPERVISOR_INNER_ALTER = SUPERVISOR_INNER,
USER_NO_ALTER = USER_NO,
IS_LEAF_SUPERVISOR = 1
WHERE
TENANT = V_TENANT AND USER_INNER = V_USER_INNER;
-- 获取直接主管的用户 ID
SELECT SUPERVISOR_INNER INTO V_SUPERVISOR_INNER
FROM {模式}.SYS_USERS
WHERE TENANT = V_TENANT AND USER_INNER = V_USER_INNER;
-- 设置直接主管的 IS_LEAF_SUPERVISOR 字段
UPDATE {模式}.SYS_USERS SET IS_LEAF_SUPERVISOR = 0 WHERE TENANT = V_TENANT AND USER_INNER = V_SUPERVISOR_INNER; --赋予默认角色
--原始20位UUID
INSERT INTO {模式}.SYS_USERS_ROLES(USER_ROLE_ID,TENANT,USER_INNER,ROLE_INNER_ID)
SELECT SYS_GUID(),V_TENANT,V_USER_INNER,ROLE_INNER_ID
FROM {模式}.SYS_ROLES
WHERE TENANT = V_TENANT
AND INSTR(
(SELECT ','||DEFAULT_ROLES||','
FROM {模式}.SYS_TENANTS
WHERE TENANT = V_TENANT), ',' || ROLE_INNER_ID || ','
) > 0;
COMMIT;
END IF;
END;
/***Manager***/CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_USER_DELETE("V_TENANT" IN VARCHAR(50),"V_SUPERVISOR_INNER" IN VARCHAR(50))
AUTHID DEFINER
AS
V_SUPERVISOR_LEAFCOUNT NUMBER;
BEGIN
SELECT COUNT(USER_INNER) INTO V_SUPERVISOR_LEAFCOUNT FROM {模式}.SYS_USERS
WHERE
TENANT = V_TENANT AND
SUPERVISOR_INNER = V_SUPERVISOR_INNER;
IF V_SUPERVISOR_LEAFCOUNT = 0
THEN
UPDATE {模式}.SYS_USERS SET IS_LEAF_SUPERVISOR = 1
WHERE
TENANT = V_TENANT AND
USER_INNER = V_SUPERVISOR_INNER;
COMMIT;
END IF;
END;
/***Manager***/CREATE OR REPLACE PROCEDURE {模式}.SP_SYS_USER_MODIFY("V_TENANT" IN VARCHAR(32767),"V_USER_INNER" IN VARCHAR(32767))
AUTHID DEFINER
AS
V_SUPERVISOR_INNER_OLD VARCHAR2(50):= NULL;
V_SUPERVISOR_INNER_NEW VARCHAR2(50):= NULL;
V_LEVEL_CODE_SUPERVISOR_OLD VARCHAR2(255):= NULL;
V_LEVEL_CODE_SUPERVISOR_NEW VARCHAR2(255):= NULL;
V_USER_NO INT :=NULL;
V_USER_NO_ALTER INT :=NULL;
I_COUNT_CHILD INT :=0;
BEGIN
SELECT SUPERVISOR_INNER,SUPERVISOR_INNER_ALTER,LEVEL_CODE_SUPERVISOR, USER_NO, USER_NO_ALTER
INTO V_SUPERVISOR_INNER_NEW,V_SUPERVISOR_INNER_OLD,V_LEVEL_CODE_SUPERVISOR_OLD, V_USER_NO, V_USER_NO_ALTER
FROM {模式}.SYS_USERS
WHERE TENANT = V_TENANT AND USER_INNER = V_USER_INNER;
IF V_SUPERVISOR_INNER_OLD IS NULL OR V_SUPERVISOR_INNER_OLD <> V_SUPERVISOR_INNER_NEW OR V_USER_NO_ALTER <> V_USER_NO THEN
-- START TRANSACTION;
--更新本员工及其下属的隶属关系层码
V_LEVEL_CODE_SUPERVISOR_NEW := SP_SYS_LEVEL_CODE(V_TENANT,V_USER_INNER, V_USER_NO);
UPDATE {模式}.SYS_USERS
SET LEVEL_CODE_SUPERVISOR = REPLACE(
LEVEL_CODE_SUPERVISOR,V_LEVEL_CODE_SUPERVISOR_OLD,V_LEVEL_CODE_SUPERVISOR_NEW
)
WHERE
TENANT = V_TENANT
AND LEVEL_CODE_SUPERVISOR LIKE V_LEVEL_CODE_SUPERVISOR_OLD || '%';
--更新直接主管的 IS_LEAF_SUPERVISOR
UPDATE {模式}.SYS_USERS SET IS_LEAF_SUPERVISOR = 0
WHERE TENANT = V_TENANT AND USER_INNER = V_SUPERVISOR_INNER_NEW;
-- 用到了 V_SYS_USERS_SUPERVISOR_TREE
-- 直接用 SYS_USERS 也是可以的，但是可能会包含一些已经无效的人员导致误差
SELECT COUNT(USER_INNER) INTO I_COUNT_CHILD FROM {模式}.SYS_USERS
WHERE TENANT = V_TENANT AND SUPERVISOR_INNER = V_SUPERVISOR_INNER_OLD AND IS_DELETE = 0;
IF 0 = I_COUNT_CHILD THEN
UPDATE {模式}.SYS_USERS SET IS_LEAF_SUPERVISOR = 1
WHERE TENANT = V_TENANT AND USER_INNER = V_SUPERVISOR_INNER_OLD;
END IF;
UPDATE {模式}.SYS_USERS
SET SUPERVISOR_INNER_ALTER = SUPERVISOR_INNER,
USER_NO_ALTER = USER_NO
WHERE TENANT = V_TENANT AND USER_INNER = V_USER_INNER;
-- COMMIT;
END IF;
END;
作者：wangjingxin 创建时间：2025-01-14 10:09
最后编辑：wangjingxin 更新时间：2025-04-24 11:27
最后编辑：wangjingxin 更新时间：2025-04-24 11:27
扫码关注网盛数新公众号，获取更多帮助资料
上一篇：无
