[oracle]发现了 sql insert 语句跳过,如果没有结果

标签: sql Oracle
发布时间: 2017/4/15 19:04:45
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有大量的 insert 语句如下所示,我想在一大块中运行的一样。有什么我告诉要跳过那个特定的 insert 语句,继续下一个 select 语句的列表的查询将返回 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 = '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);

解决方法 1:

我将包它入 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
;
赞助商