根据交易流水表中找对手信息,要求写一个存储过程,通过算法实现批量匹配ODS_TRANS表中”对方账号”字段,然后将所有对方账号匹配成功的交易数据插入到目标表T40_TRANS中;(建议采用Session表处理)源表结构:
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 '对方账号'
);要求写一个存储过程,使用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"
);
存储过程 session Oracle
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 '对方账号'
);要求写一个存储过程,使用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"
);
存储过程 session Oracle
create or replace procedure test ()
As
begain
create table B1 as select * from ODS_TRANS where DR_CR_FLAG='0';/*得出借钱的帐号*/
create table B2 as select * from ODS_TRANS where DR_CR_FLAG='1';/*得出贷钱的帐号*/
Update B2 set B2.OPP_ACCT_NUM (select TRANS_ID from B1) where B2.TRANS_DT=B1.TRANS_DT and B2.TRANS_ID=B1.TRANS_ID;
Update B1 set B1.OPP_ACCT_NUM (select TRANS_ID from B2) where B1.TRANS_DT=B2.TRANS_DT and B1.TRANS_ID=B2.TRANS_ID;
Update ODS_TRANS set ODS_TRANS.OPP_ACCT_NUM (select B1.OPP_ACCT_NUM where ODS_TRANS.TRANS_DT=B1.TRANS_DT);
insert into T40_TRANS select * from B1;
End;
End test;
Commit;
第二题
create or replace procedure test ()
as
begain
Insert into T17_ORGAN(ORGANNO,ORGANNAME,ORGANLEVEL,UPORGANKEY,FLAG) select * from T40_ORGAN
end;
end test;
Commit;
session表?oracle好像没这个概念。。只有session级的临时表。。
AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ODS_TRANS_B';
INSERT INTO ODS_TRANS_B
SELECT T.TRANS_ID,T.TRANS_DT,T.CURR_CD,
CASE T.DR_CR_FLAG
WHEN '0' THEN '1'
ELSE '0'
END,
T.CASH_TALLY_FLAG,
T.BASE_TRANS_AMT,
T.ACCT_NUM,
T.CUST_NUM,
T.OPP_ACCT_NUM -
FROM ODS_TRANS T;
CREATE VIEW VIEW_ODS_TRANS AS
SELECT W.TRANS_ID,
W.TRANS_DT,
W.CURR_CD,
W.DR_CR_FLAG,
W.CASH_TALLY_FLAG,
W.BASE_TRANS_AMT,
W.ACCT_NUM,
W.CUST_NUM,
T.ACCT_NUM
FROM ODS_TRANS W, TEMP_ODS_TRANS T
WHERE W.TRANS_ID = T.TRANS_ID
AND W.DR_CR_FLAG = T.DR_CR_FLAG
AND W.TRANS_DT = T.TRANS_DT;
INSERT INTO T40_TRANS
SELECT * FROM VIEW_ODS_TRANS;
COMMIT;
EXCEPTION
WHEN VALUE_ERROR THEN
ROLLBACK;
END INSERT_T40_TRANS;