求助:PLSQL使用动态变量的问题

liao9416 2009-05-30 07:44:02

要实现如下一个触发器,这个是已经测试好了的.现在我想使用两个FOR循环代替
IF (c1.RedBall1 =:new.RedBall1) THEN red:=red+1; END IF; 语句.请问怎么实现?

考虑过使用动态SQL 但是一直通不过 ,参数 C1 传不进去.
FOR i IN 1..6 LOOP
FOR j IN 1..6 LOOP
v_sql:='
BEGIN
IF (:c1.RedBall'|| :i ||' =:new.RedBall'|| :j ||') THEN :red:=:red+1; END IF;
END;
';
EXECUTE IMMEDIATE v_sql
USING c1,i,j,IN OUT red;
END LOOP;
END LOOP;

===================================================================
CREATE OR REPLACE TRIGGER t_drawresult
AFTER INSERT OR UPDATE ON drawresult
FOR EACH ROW
DECLARE
CURSOR c_issue IS
SELECT * FROM lottery
WHERE code_issue=:new.code_issue;
red int;
blue int;
v_sql long;
BEGIN
FOR c1 IN c_issue LOOP
red:=0;
blue:=0;
IF (c1.RedBall1 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall1 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall1 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall1 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall1 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall1 =:new.RedBall6) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall2 =:new.RedBall6) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall3 =:new.RedBall6) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall4 =:new.RedBall6) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall5 =:new.RedBall6) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall1) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall2) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall3) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall4) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall5) THEN red:=red+1; END IF;
IF (c1.RedBall6 =:new.RedBall6) THEN red:=red+1; END IF;
IF(c1.BlueBall1=:new.BlueBall1) THEN
blue:=blue+1;
END IF;
UPDATE lottery
SET result=red||'+'||blue
WHERE code_ticket=c1.code_ticket;
END LOOP;
END t_drawresult;
/
======================================================================

...全文