xugu_license/模拟oa库环境.sql

DROP  TABLE XUGU.FORMTABLE_MAIN_146;
DROP  TABLE  XUGU.FORMTABLE_MAIN_146_DT1;
DROP  TABLE XUGU.HRMRESOURCE ;
DROP  TABLE  XUGU.PRJ_PROJECTINFO;
DROP  TABLE  XUGU.WORKFLOW_REQUESTBASE;
DROP  TABLE  XUGU.WORKFLOW_SELECTITEM;

create  database oadb;
use oadb;
CREATE USER xugu LOGIN xugu IDENTIFIED BY '123QWEasd!@';

CREATE  TABLE  XUGU.formtable_main_146(
"ID" INTEGER IDENTITY(1,1) NOT NULL ,
"REQUESTID" INTEGER,
"GLXM" INTEGER,
"SQSJ" CHAR(10),
"XSJSYX" VARCHAR(100),
"JFJSYX" VARCHAR(100),
"SYDW" VARCHAR(100),
"XMXXMS" VARCHAR(4000), 
"JDS" INTEGER,
"JFRY" CLOB,
"XSRY" CLOB,
CONSTRAINT"PK_S9071172173383367" PRIMARY KEY ("ID")
);

CREATE TABLE "XUGU"."FORMTABLE_MAIN_146_DT1" (
"ID" INTEGER IDENTITY(1,1) NOT NULL ,
"MAINID" INTEGER,
"CPMC" INTEGER,
"BB" INTEGER,
"CLQ" VARCHAR(100),
"CZXT" VARCHAR(100),
"IP" VARCHAR(50),
"MAC" VARCHAR(100),
"JDS" INTEGER, CONSTRAINT"PK_S1967172181702375" PRIMARY KEY ("ID")
);


CREATE TABLE "XUGU"."HRMRESOURCE" (
"ID" INTEGER NOT NULL ,
"LASTNAME" VARCHAR(120)
);

CREATE TABLE "XUGU"."WORKFLOW_SELECTITEM" (
"SELECTVALUE" INTEGER,
"SELECTNAME" VARCHAR(1000),
"FIELDID" INTEGER
);

CREATE TABLE "XUGU"."WORKFLOW_REQUESTBASE" (
    "REQUESTNAME" VARCHAR(1000),
    "REQUESTNAMENEW" VARCHAR(4000),
    "REQUESTNAMEHTMLNEW" VARCHAR(4000),
    "REQUESTID" INTEGER,
"CREATEDATE" CHAR(10),
"CREATETIME" CHAR(8),
"LASTOPERATEDATE" CHAR(10),
"LASTOPERATETIME" CHAR(8)

);

CREATE TABLE "XUGU"."PRJ_PROJECTINFO" (
 "ID" BIGINT IDENTITY(162,1) NOT NULL ,
"NAME" VARCHAR(1000),
 CONSTRAINT"PK_S29291170022985212260" PRIMARY KEY ("ID")
);





	
--------------------
CREATE OR REPLACE PROCEDURE InsertDataLoop
AS
    v_id_formtable_main_146 INTEGER := 20;
    v_id_formtable_main_146_dt1 INTEGER := 20;
    v_id_hrmresource INTEGER := 20;
    v_id_workflow_requestbase INTEGER := 2001;
    v_id_prj_projectinfo BIGINT := 262;
    time11 INTEGER := 1;
    counter INTEGER := 0; -- 计数器，初始值为0
BEGIN
    FOR i IN 1..100 LOOP
        -- 每3次插入一次 WORKFLOW_REQUESTBASE
        IF counter = 0 THEN
            -- 插入到 WORKFLOW_REQUESTBASE 表
            INSERT INTO XUGU.WORKFLOW_REQUESTBASE (
                "REQUESTNAME" ,
                "REQUESTNAMENEW" ,
                "REQUESTNAMEHTMLNEW" ,
                "REQUESTID", 
                "CREATEDATE", 
                "CREATETIME", 
                "LASTOPERATEDATE", 
                "LASTOPERATETIME"
            ) VALUES (
                '申请-勾童-' || v_id_workflow_requestbase,
                '申请-勾童-' || v_id_workflow_requestbase,
                '申请-勾童-' || v_id_workflow_requestbase,
                v_id_workflow_requestbase,  -- REQUESTID
                TO_CHAR(SYSDATE + time11, 'YYYY-MM-DD'),  -- CREATEDATE
                TO_CHAR(SYSDATE + time11, 'HH24:MI:SS'),  -- CREATETIME
                TO_CHAR(SYSDATE + time11, 'YYYY-MM-DD'),  -- LASTOPERATEDATE
                TO_CHAR(SYSDATE + time11, 'HH24:MI:SS')  -- LASTOPERATETIME
  
            );
              time11 := time11 + 1 ;
                 -- 插入到 formtable_main_146 表
        INSERT INTO XUGU.formtable_main_146 (
                    "id",
            "REQUESTID", 
            "GLXM", 
            "SQSJ", 
            "XSJSYX", 
            "JFJSYX", 
            "SYDW", 
            "XMXXMS", 
            "JDS", 
            "JFRY", 
            "XSRY"
        ) VALUES (
            v_id_formtable_main_146,
            v_id_workflow_requestbase,  -- REQUESTID，基于唯一 requestbase
            v_id_prj_projectinfo + i,  -- GLXM
            TO_CHAR(SYSDATE, 'YYYY-MM-DD'),  -- SQSJ
            'user' || i || '@example.com',  -- XSJSYX
            'user' || i || '@example.com',  -- JFJSYX
            '使用单位测试名 ' || i,  -- SYDW
            '项目详细测试名 ' || i,  -- XMXXMS
            100 + i,  -- JDS
            TO_CHAR(1),  -- JFRY (数字 + 字符串)
            TO_CHAR(1)  -- XSRY (数字 + 字符串)
        );
        END IF;

   

        -- 插入到 formtable_main_146_dt1 表
        INSERT INTO XUGU.FORMTABLE_MAIN_146_DT1 (
            "MAINID", 
            "CPMC", 
            "BB", 
            "CLQ", 
            "CZXT", 
            "IP", 
            "MAC", 
            "JDS"
        ) VALUES (
            v_id_formtable_main_146,  -- MAINID
            1,  -- CPMC
            1,  -- BB
            'cpu测试名字 ' || i,  -- CLQ
            '操作系统测试名 ' || i,  -- CZXT
            '192.168.1.' || i,  -- IP
            '00:11:22:33:44:' || LPAD(TO_CHAR(i), 2, '0'),  -- MAC
            100 + i  -- JDS
        );

        -- 更新其他变量，准备下一次循环

        v_id_formtable_main_146_dt1 := v_id_formtable_main_146_dt1 + 1;
        v_id_hrmresource := v_id_hrmresource + 1;
        v_id_prj_projectinfo := v_id_prj_projectinfo + 1;

        -- 增加计数器的值，每 3 次重置
        counter := counter + 1;
        IF counter = 3 THEN
            v_id_workflow_requestbase := v_id_workflow_requestbase + 1;
              v_id_formtable_main_146 := v_id_formtable_main_146 + 1;
            counter := 0; -- 重置计数器
        END IF;
    END LOOP;
    
    COMMIT;
END;
/



        -- 插入到 WORKFLOW_SELECTITEM 表
        INSERT INTO XUGU.WORKFLOW_SELECTITEM (
            "SELECTVALUE", 
            "SELECTNAME", 
            "FIELDID"
        ) VALUES (
            1,  -- SELECTVALUE
            '虚谷v12 ' ,  -- SELECTNAME
             14627  -- FIELDID
        );
        INSERT INTO XUGU.WORKFLOW_SELECTITEM (
            "SELECTVALUE", 
            "SELECTNAME", 
            "FIELDID"
        ) VALUES (
            1,  -- SELECTVALUE
            '分布式 ' ,  -- SELECTNAME
             14628  -- FIELDID
        );
        -- 插入到 HRMRESOURCE 表
        INSERT INTO XUGU.HRMRESOURCE (
            "ID", 
            "LASTNAME"
        ) VALUES (
           1,  -- ID
            '勾童测试'  -- LASTNAME
        );

InsertDataLoop();


--------