我正在尝试通过存储表值的光标迭代。我使用for循环迭代,如果满足其中一个条件,则将输出存储在另一个表中。我不确定我正在遵循的方法并获得错误(ORA-00933:SQL命令未正确结束)。Stats_queries是我的参考表,我在其中迭代光标。STATS_RESULT_CARD是我的输出表,我必须在其中存储结果。请帮助。
DECLARE
CURSOR c1 IS
select Stats_Queries.OBJECTTYPE, Stats_Queries.CATEGORY, Stats_Queries.QUERY
from Stats_Queries;
r1 c1%ROWTYPE;
BEGIN
FOR r1 IN c1 LOOP
If (r1.OBJECTTYPE = 'CARD') THEN
INSERT INTO STATS_RESULTS_CARD (NODETYPENAME, NODEDEFNAME , CARDTYPENAME, PROVISIONSTATUSNAME, STATDATE, CARDCOUNT)
select nt.name, nd.name, ct.name, ps.name, sysdate, count(c.cardid)
from cardtype ct, card c, node n, nodetype nt, nodedef nd, provisionstatus ps
where ct.name in ('SRA AMP', 'XLA AMP', 'SAM', 'ESAM')
and ct.cardtypeid = c.card2cardtype
and c.card2node = n.nodeid
and n.node2nodetype = nt.nodetypeid
and n.node2nodedef = nd.nodedefid
and c.card2provisionstatus = ps.provisionstatusid
group by nt.name, nd.name, ct.name, ps.name
END If;
END LOOP;
END;
除了Finbarr提供的答案之外(这是完全正确的;添加丢失的半柱子,您的过程应该起作用)根本吗?那是这样做的缓慢方法。
您只能做一个插入语句,例如:
insert into stats_results_card (nodetypename,
nodedefname,
cardtypename,
provisionstatusname,
statdate,
cardcount)
select x.nt_name,
x.nd_name,
x.ct_name,
x.ps_name,
x.statdate,
x.cnt_cardid
from (select nt.name nt_name,
nd.name nd_name,
ct.name ct_name,
ps.name ps_name,
sysdate statdate,
count (c.cardid) cnt_cardid
from cardtype ct,
card c,
node n,
nodetype nt,
nodedef nd,
provisionstatus ps
where ct.name in ('SRA AMP',
'XLA AMP',
'SAM',
'ESAM')
and ct.cardtypeid = c.card2cardtype
and c.card2node = n.nodeid
and n.node2nodetype = nt.nodetypeid
and n.node2nodedef = nd.nodedefid
and c.card2provisionstatus = ps.provisionstatusid
group by nt.name,
nd.name,
ct.name,
ps.name) x
cross join (select stats_queries.objecttype,
stats_queries.category,
stats_queries.query
from stats_queries
where objecttype = 'CARD');
n.b。这是假设原始光标与循环内部的选择语句之间确实没有任何链接。我们进行十字架连接以复制行所需的次数。
如果两个查询之间存在实际的联接,则将其代替十字架加入。
ORA-00933: SQL command not ended properly
可能是因为您错过了
之后的半分离group by nt.name, nd.name, ct.name, ps.name