一 。要求写一个存储过程,使用MERGE语法,将T40_ORGAN增量数据导入到T17_ORGAN,
源表结构:
CREATE TABLE "FRP"."T40_ORGAN"
("ORGANNO" VARCHAR(12) NOT NULL,
"ORGANNAME" VARCHAR(256),
"ORGANLEVEL" CHARACTER(1),
"UPORGANKEY" VARCHAR(12),
"FLAG" CHARACTER(1)
);
COMMENT ON TABLE "FRP"."T40_ORGAN"
IS 'T17机构表';
COMMENT ON "FRP"."T40_ORGAN"
("ORGANNO" IS '机构代码',
"ORGANNAME" IS '机构名称 机构中文名称',
"ORGANLEVEL" IS '机构级别0 总行1 分行2 支行3虚拟支行下的支行 参考数据字典[1025]',
"UPORGANKEY" IS '上级机构,-1为总行',
"FLAG" IS '标志位 0:禁用1:正常2:删除 参考数据字典[0001]'
);ALTER TABLE "FRP"."T40_ORGAN"
ADD CONSTRAINT "P_PK_T40_ORGAN" PRIMARY KEY
("ORGANNO"
); 目标表结构:
CREATE TABLE "FRP"."T17_ORGAN"
("ORGANNO" VARCHAR(12) NOT NULL,
"ORGANNAME" VARCHAR(256),
"ORGANLEVEL" CHARACTER(1),
"UPORGANKEY" VARCHAR(12),
"FLAG" CHARACTER(1)
);
COMMENT ON TABLE "FRP"."T17_ORGAN"
IS 'T17机构表';
COMMENT ON "FRP"."T17_ORGAN"
("ORGANNO" IS '机构代码',
"ORGANNAME" IS '机构名称 机构中文名称',
"ORGANLEVEL" IS '机构级别0 总行1 分行2 支行3虚拟支行下的支行 参考数据字典[1025]',
"UPORGANKEY" IS '上级机构,-1为总行',
"FLAG" IS '标志位 0:禁用1:正常2:删除 参考数据字典[0001]'
);ALTER TABLE "FRP"."T17_ORGAN"
ADD CONSTRAINT "P_PK_T17_ORGAN" PRIMARY KEY
("ORGANNO"
);
二 、根据交易流水表中找对手信息,要求写一个存储过程,通过算法实现批量匹配ODS_TRANS表中”对方账号”字段,然后将所有对方账号匹配成功的交易数据插入到目标表T40_TRANS中;
要求:1.采用Session表处理
2.不能使用Update语法
3. 算法最优,可以调用执行源表结构:
CREATE TABLE "FRP"."ODS_TRANS"
(
"TRANS_ID" VARCHAR(50),
"TRANS_DT" DATE,
"CURR_CD" VARCHAR(5),
"DR_CR_FLAG" VARCHAR(2),
"CASH_TALLY_FLAG" VARCHAR(2),
"BASE_TRANS_AMT" DECIMAL(31, 9),
"ACCT_NUM" VARCHAR(50),
"CUST_NUM" VARCHAR(20),
"OPP_ACCT_NUM" VARCHAR(50)
);
COMMENT ON TABLE "FRP"ODS_TRANS" IS '交易信息';
COMMENT ON "FRP"."ODS_TRANS"
(
"TRANS_ID" IS '交易流水号',
"TRANS_DT" IS '交易日期',
"CURR_CD" IS '标准货币',
"DR_CR_FLAG" IS '借贷标记',
"CASH_TALLY_FLAG" IS '现转标志',
"BASE_TRANS_AMT" IS '交易金额',
"ACCT_NUM" IS '账户号',
"CUST_NUM" IS '客户号',
"OPP_ACCT_NUM" IS '对方账号'
);源表测试数据:(同一交易流水号与同一交易日期,通常情况下借贷标识配对(一借一贷为一组),可以通过借方账号找到同组贷方账号,然后交叉更新对方账号)交易流水号 交易日期 币种 借贷
标识0:借 1:贷 现转
标识0:现金 1:转账 交易金额 账号 客户号 对方账号
563301000007 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000017 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000027 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000037 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000027 '2011-04-15' 'RMB' 1 1 100000 50000000000000076284 6000000672 '[Null]'
563301000037 '2011-04-15' 'RMB' 1 1 100000 50000000000000076292 6000000672 '[Null]'
563301000007 '2011-04-15' 'RMB' 1 1 100000 50000000000000076250 6000000672 '[Null]'
563301000017 '2011-04-15' 'RMB' 1 1 100000 50000000000000076276 6000000672 '[Null]'
669501119649 '2011-04-16' 'RMB' 0 1 400 3503014430006237 1242717258 '[Null]'
669501119649 '2011-04-16' 'RMB' 1 1 400 3501014430088913 1242235146 '[Null]'目标表结构:
CREATE TABLE "FRP"."T40_TRANS"
(
"TRANS_ID" VARCHAR(50),
"TRANS_DT" DATE,
"CURR_CD" VARCHAR(5),
"DR_CR_FLAG" VARCHAR(2),
"CASH_TALLY_FLAG" VARCHAR(2),
"BASE_TRANS_AMT" DECIMAL(31, 9),
"ACCT_NUM" VARCHAR(50),
"CUST_NUM" VARCHAR(20),
"OPP_ACCT_NUM" VARCHAR(50)
);
COMMENT ON TABLE "FRP" T40_TRANS" IS '交易信息';
COMMENT ON "FRP"." T40_TRANS"
(
"TRANS_ID" IS '交易流水号',
"TRANS_DT" IS '交易日期',
"CURR_CD" IS '标准货币',
"DR_CR_FLAG" IS '借贷标记',
"CASH_TALLY_FLAG" IS '现转标志',
"BASE_TRANS_AMT" IS '交易金额',
"ACCT_NUM" IS '账户号',
"CUST_NUM" IS '客户号',
"OPP_ACCT_NUM" IS '对方账号'
);
存储
源表结构:
CREATE TABLE "FRP"."T40_ORGAN"
("ORGANNO" VARCHAR(12) NOT NULL,
"ORGANNAME" VARCHAR(256),
"ORGANLEVEL" CHARACTER(1),
"UPORGANKEY" VARCHAR(12),
"FLAG" CHARACTER(1)
);
COMMENT ON TABLE "FRP"."T40_ORGAN"
IS 'T17机构表';
COMMENT ON "FRP"."T40_ORGAN"
("ORGANNO" IS '机构代码',
"ORGANNAME" IS '机构名称 机构中文名称',
"ORGANLEVEL" IS '机构级别0 总行1 分行2 支行3虚拟支行下的支行 参考数据字典[1025]',
"UPORGANKEY" IS '上级机构,-1为总行',
"FLAG" IS '标志位 0:禁用1:正常2:删除 参考数据字典[0001]'
);ALTER TABLE "FRP"."T40_ORGAN"
ADD CONSTRAINT "P_PK_T40_ORGAN" PRIMARY KEY
("ORGANNO"
); 目标表结构:
CREATE TABLE "FRP"."T17_ORGAN"
("ORGANNO" VARCHAR(12) NOT NULL,
"ORGANNAME" VARCHAR(256),
"ORGANLEVEL" CHARACTER(1),
"UPORGANKEY" VARCHAR(12),
"FLAG" CHARACTER(1)
);
COMMENT ON TABLE "FRP"."T17_ORGAN"
IS 'T17机构表';
COMMENT ON "FRP"."T17_ORGAN"
("ORGANNO" IS '机构代码',
"ORGANNAME" IS '机构名称 机构中文名称',
"ORGANLEVEL" IS '机构级别0 总行1 分行2 支行3虚拟支行下的支行 参考数据字典[1025]',
"UPORGANKEY" IS '上级机构,-1为总行',
"FLAG" IS '标志位 0:禁用1:正常2:删除 参考数据字典[0001]'
);ALTER TABLE "FRP"."T17_ORGAN"
ADD CONSTRAINT "P_PK_T17_ORGAN" PRIMARY KEY
("ORGANNO"
);
二 、根据交易流水表中找对手信息,要求写一个存储过程,通过算法实现批量匹配ODS_TRANS表中”对方账号”字段,然后将所有对方账号匹配成功的交易数据插入到目标表T40_TRANS中;
要求:1.采用Session表处理
2.不能使用Update语法
3. 算法最优,可以调用执行源表结构:
CREATE TABLE "FRP"."ODS_TRANS"
(
"TRANS_ID" VARCHAR(50),
"TRANS_DT" DATE,
"CURR_CD" VARCHAR(5),
"DR_CR_FLAG" VARCHAR(2),
"CASH_TALLY_FLAG" VARCHAR(2),
"BASE_TRANS_AMT" DECIMAL(31, 9),
"ACCT_NUM" VARCHAR(50),
"CUST_NUM" VARCHAR(20),
"OPP_ACCT_NUM" VARCHAR(50)
);
COMMENT ON TABLE "FRP"ODS_TRANS" IS '交易信息';
COMMENT ON "FRP"."ODS_TRANS"
(
"TRANS_ID" IS '交易流水号',
"TRANS_DT" IS '交易日期',
"CURR_CD" IS '标准货币',
"DR_CR_FLAG" IS '借贷标记',
"CASH_TALLY_FLAG" IS '现转标志',
"BASE_TRANS_AMT" IS '交易金额',
"ACCT_NUM" IS '账户号',
"CUST_NUM" IS '客户号',
"OPP_ACCT_NUM" IS '对方账号'
);源表测试数据:(同一交易流水号与同一交易日期,通常情况下借贷标识配对(一借一贷为一组),可以通过借方账号找到同组贷方账号,然后交叉更新对方账号)交易流水号 交易日期 币种 借贷
标识0:借 1:贷 现转
标识0:现金 1:转账 交易金额 账号 客户号 对方账号
563301000007 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000017 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000027 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000037 '2011-04-15' 'RMB' 0 1 100000 3501014430177004 1239631523 '[Null]'
563301000027 '2011-04-15' 'RMB' 1 1 100000 50000000000000076284 6000000672 '[Null]'
563301000037 '2011-04-15' 'RMB' 1 1 100000 50000000000000076292 6000000672 '[Null]'
563301000007 '2011-04-15' 'RMB' 1 1 100000 50000000000000076250 6000000672 '[Null]'
563301000017 '2011-04-15' 'RMB' 1 1 100000 50000000000000076276 6000000672 '[Null]'
669501119649 '2011-04-16' 'RMB' 0 1 400 3503014430006237 1242717258 '[Null]'
669501119649 '2011-04-16' 'RMB' 1 1 400 3501014430088913 1242235146 '[Null]'目标表结构:
CREATE TABLE "FRP"."T40_TRANS"
(
"TRANS_ID" VARCHAR(50),
"TRANS_DT" DATE,
"CURR_CD" VARCHAR(5),
"DR_CR_FLAG" VARCHAR(2),
"CASH_TALLY_FLAG" VARCHAR(2),
"BASE_TRANS_AMT" DECIMAL(31, 9),
"ACCT_NUM" VARCHAR(50),
"CUST_NUM" VARCHAR(20),
"OPP_ACCT_NUM" VARCHAR(50)
);
COMMENT ON TABLE "FRP" T40_TRANS" IS '交易信息';
COMMENT ON "FRP"." T40_TRANS"
(
"TRANS_ID" IS '交易流水号',
"TRANS_DT" IS '交易日期',
"CURR_CD" IS '标准货币',
"DR_CR_FLAG" IS '借贷标记',
"CASH_TALLY_FLAG" IS '现转标志',
"BASE_TRANS_AMT" IS '交易金额',
"ACCT_NUM" IS '账户号',
"CUST_NUM" IS '客户号',
"OPP_ACCT_NUM" IS '对方账号'
);
存储
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货