如果没有发现结果,sql跳过插入语句
我有一些插入语句,如下面显示的那样,我尝试在一个大块中运行。有无论如何,我告诉查询跳过该特定的插入语句,并继续在其中一个选择语句的列表返回null?如果没有发现结果,sql跳过插入语句
insert into V1144engine.T_edges(edge_id, version, node1_id, node2_id, edge_type_id, created_at, weight,deleted_at) VALUES(V1144ENGINE.S_PK_EDGES.NEXTVAL,0,(Select node_id from V1144engine.T_nodes where node_name = 'Writing an Equation of a Perpendicular Line' and rownum=1),(select node_id from V1144engine.T_nodes where node_name = 'slope'and node_type_id =11),5,SYSDATE,5.318,null);
insert into V1144engine.T_edges(edge_id, version, node1_id, node2_id, edge_type_id, created_at, weight,deleted_at) VALUES(V1144ENGINE.S_PK_EDGES.NEXTVAL,0,(Select node_id from V1144engine.T_nodes where node_name = 'Writing an Equation of a Perpendicular Line' and rownum=1),(select node_id from V1144engine.T_nodes where node_name = 'vertic'and node_type_id =11),5,SYSDATE,5,null);
我把它包装成一个select ... from dual
或限制在node1|2_id IS NOT NULL
:
INSERT INTO v1144engine.t_edges (edge_id
,version
,node1_id
,node2_id
,edge_type_id
,created_at
,weight
,deleted_at
)
SELECT *
FROM (
SELECT
v1144engine.s_pk_edges.NEXTVAL edge_id
,0 version
, (SELECT node1_id
FROM v1144engine.t_nodes
WHERE node_name = 'Writing an Equation of a Perpendicular Line'
AND ROWNUM = 1) node1_id
, (SELECT node_id
FROM v1144engine.t_nodes
WHERE node_name = 'slope'
AND node_type_id = 11) node2_id
,5 edge_type_id
,SYSDATE created_at
,5.318 weight
,NULL deleted_at
FROM dual
)
WHERE node1_id IS NOT NULL
AND node2_id IS NOT NULL
;
为什么不直接把选择放到临时表中,然后从中生成动态SQL?
,因为我们所有的node_id都是从每个机器上不同的序列生成的,所以它必须从这个插入语句中进行选择,静态ID将不起作用。 – 2012-07-25 19:11:24
你可以把这些语句放到一个表中,并循环使用一个游标来检查行数。如果在插入后行计数没有增加,就知道它失败了。 – 2012-07-25 19:17:51
好吧,这样的工作,但它仍然非常乏味,因为我添加更新和插入语句,当我发现一个错误,我仍然需要手动删除它。任何想法如何解决这个问题? – 2012-07-25 19:31:18
你可以复制嵌套SELECT
S的谓词EXISTS
条款:
INSERT INTO v1144engine.t_edges (
edge_id
, version
, node1_id
, node2_id
, edge_type_id
, created_at
, weight
, deleted_at
)
SELECT v1144engine.s_pk_edges.NEXTVAL
, 0
, (SELECT node_id
FROM v1144engine.t_nodes
WHERE node_name = 'Writing an Equation of a Perpendicular Line'
AND ROWNUM = 1)
, (SELECT node_id
FROM v1144engine.t_nodes
WHERE node_name = 'slope'
AND node_type_id = 11)
, 5
, SYSDATE
, 5.318
, TO_DATE(NULL)
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM v1144engine.t_nodes
WHERE node_name = 'Writing an Equation of a Perpendicular Line')
AND EXISTS (SELECT NULL
FROM v1144engine.t_nodes
WHERE node_name = 'slope'
AND node_type_id = 11);
我不知道你有多少这些,但是,作为替代方案,或许像下面这样的转变将起作用,因为更多的是基于集合的方法?
INSERT INTO v1144engine.t_edges (
edge_id
, version
, node1_id
, node2_id
, edge_type_id
, created_at
, weight
, deleted_at
)
SELECT v1144engine.s_pk_edges.NEXTVAL
, 0
, MAX(CASE
WHEN node_name = 'Writing an Equation of a Perpendicular Line'
THEN node_id
END)
, MAX(CASE
WHEN node_name = 'slope'
AND node_type_id = 11
THEN node_id
END)
, 5
, SYSDATE
, 5.318
, TO_DATE(NULL)
FROM v1144engine.t_nodes
WHERE node_name IN ('Writing an Equation of a Perpendicular Line','slope')
HAVING MAX(CASE
WHEN node_name = 'Writing an Equation of a Perpendicular Line'
THEN node_id
END) IS NOT NULL
AND MAX(CASE
WHEN node_name = 'slope'
AND node_type_id = 11
THEN node_id
END) IS NOT NULL
;
(警告:未经测试的代码。)
当你说一个大的块,你的意思只是一堆单一的INSERT语句?如果这是一个PL/SQL过程,你可以使用'IF'语句和光标循环(如果这些将适用/有帮助) – Dan 2012-07-25 19:08:24
是的,我的意思是一堆单插入语句,但它不会作为PL/SQL过程运行,感谢您的建议,尽管 – 2012-07-25 19:12:38
您使用什么程序来执行SQL语句?在SQL * Plus? SQL Developer?还有别的吗?如果你不使用PL/SQL块,该应用程序将控制一个语句失败时发生的情况。默认的SQL * Plus和SQL Developer的行为将会继续下一个声明,这听起来像是你想要的行为。 – 2012-07-25 19:38:25