如何在Oracle 11g中测试以下SQL脚本
如何在Oracle 11g中测试以下SQL脚本
提问于 2013-08-23 23:33:35
以下是用Oracle 11g编写的脚本：
MERGE INTO tblbio t
USING (SELECT e.id, tblduplicate.cpid, e.bdt,e.LN, e.FN
FROM tblduplicate, entities where
trim(e.id) = trim(tblduplicate.id)) source
ON (t.cpid = source.cpid and trim(t.bdt) = trim(source.bdt))
WHEN MATCHED
THEN
UPDATE SET t.id = source.id, t.stat = '4'
WHERE t.cmp = 'HHCC'
AND t.thn = '2013'
AND trim(lower(source.LN)) = trim(lower(t.LN))
AND trim(lower(source.FN)) = trim(lower(t.FN))
AND nvl(trim(t.bdt), ' ') <> ' '
AND t.bdt <> '00000000'
AND nvl(trim(source.bdt), ' ') <> ' '
and source.bdt <> '00000000'
AND t.stat <> '4'由于我的数据完整性问题，这个脚本偶尔会生成1条以上的记录。其中此脚本将生成错误。我想在此脚本运行之前创建验证。当我的验证生成超过1条记录时，我会停止运行这个脚本。我该怎么做呢？
我试图为我的验证编写以下代码来捕获记录计数，但Oracle就是不喜欢它。
select* from tblbio t <-----
USING (SELECT e.id, tblduplicate.cpid, e.bdt,e.LN, e.FN
FROM tblduplicate, entities where
trim(e.id) = trim(tblduplicate.id)) source
ON (t.cpid = source.cpid and trim(t.bdt) = trim(source.bdt))
WHEN MATCHED
THEN
select * from tblbio t <-----
WHERE t.cmp = 'HHCC'
AND t.thn = '2013'
AND trim(lower(source.LN)) = trim(lower(t.LN))
AND trim(lower(source.FN)) = trim(lower(t.FN))
AND nvl(trim(t.bdt), ' ') <> ' '
AND t.bdt <> '00000000'
AND nvl(trim(source.bdt), ' ') <> ' '
and source.bdt <> '00000000'
AND t.stat <> '4'回答 1
Stack Overflow用户
发布于 2013-08-24 12:32:36
将原始MERGE查询转换为从tbiblio中选择
MERGE INTO ... USING-->select from ... joinWHEN MATCHED THEN UPDATE SET ... WHERE-->WHERE
最终查询：
select * from tblbio t
JOIN (SELECT e.id, tblduplicate.cpid, e.bdt,e.LN, e.FN
FROM tblduplicate, entities where
trim(e.id) = trim(tblduplicate.id)) source
ON (t.cpid = source.cpid and trim(t.bdt) = trim(source.bdt))
WHERE t.cmp = 'HHCC'
AND t.thn = '2013'
AND trim(lower(source.LN)) = trim(lower(t.LN))
AND trim(lower(source.FN)) = trim(lower(t.FN))
AND nvl(trim(t.bdt), ' ') <> ' '
AND t.bdt <> '00000000'
AND nvl(trim(source.bdt), ' ') <> ' '
and source.bdt <> '00000000'
AND t.stat <> '4'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18406660
复制相关文章
相似问题
腾讯云开发者
