存储过程 循环

CREATE OR REPLACE PROCEDURE test_loop as

CURSOR emp_cur IS
SELECT name,name1,weights,score FROM FACTOR where score is not null order by name;
  --声明变量接收游标中的数据
  v_name  FACTOR.name%TYPE;
  v_name1  FACTOR.name1%TYPE;
  v_weights  FACTOR.weights%TYPE;
  v_score  FACTOR.score%TYPE;
  sql2 varchar2(2000);
  v_name_const  varchar2(200):= '10000';
BEGIN
OPEN emp_cur;

  LOOP
   --获取游标中的数据   如果有的话赋值给变量
    FETCH emp_cur INTO v_name,v_name1,v_weights,v_score;
    IF v_name_const<>v_name and v_name_const<> '10000' THEN  sql2:=sql2|| 'ELSE 0.5 END),'||CHR(10);END IF;
    IF v_name_const<>v_name THEN  v_name_const:=v_name; sql2:=sql2|| 'CASE T.'||v_name||CHR(10);END IF;
    sql2:= sql2||'WHEN'||v_name1||'THEN'||CHR(10)||v_weights * v_score||CHR(10);
    EXIT WHEN emp_cur%NOTFOUND;  
  END LOOP;
  sql2:=sql2|| 'ELSE 0.5 END),';
  dbms_output.put_line(sql2);
CLOSE emp_cur;
END test_loop;'