如何根据字符串获取Cursor字段

[kaoziji]

Cursor有col1，col2两个字段，根据传入的字符串值"1"或者"2"，返回col1或者col2的值。
以上是我简化了实际情况的描述。
实际情况是，Cursor有许多column，我要在FOR-LOOP中column value作为参数，传给EXECUTE IMMEDIATE。

CREATE TYPE ColList IS TABLE OF VARCHAR2(2000);

DECLARE
names ColList;
BEGIN
names := ColList('Com');
tmp_value VARCHAR2(2000);

FOR one_row IN c_111 LOOP
		FOR i IN 1..names.COUNT LOOP
			EXECUTE IMMEDIATE 'SELECT one_row.' || names(i) || ' FROM DUAL '
				INTO tmp_value;
			EXECUTE IMMEDIATE 'UPDATE one_table r '
			|| '   SET r.cause = r.cause || (SELECT ''' || names(i) || ':'' || l.' || names(i)
			|| '                               FROM table2 l '
			|| '                              WHERE l.colA = '' || tmp_value || '''';
		END LOOP;
END LOOP;

[zhone]

等待高手解答
mark

[cosio]

FOR one_row IN c_111 LOOP
FOR i IN 1..names.COUNT LOOP
sqlstr:=sqlstr || '后面的字符串'
END LOOP;
END LOOP;
然后在一次提醒:
execute immediate sqlstr!

[kaoziji]

[Quote=引用 2 楼 cosio 的回复:]
FOR one_row IN c_111 LOOP
FOR i IN 1..names.COUNT LOOP
sqlstr:=sqlstr ¦ ¦ '后面的字符串'
END LOOP;
END LOOP;
然后在一次提醒:
execute immediate sqlstr!
[/Quote]
?
cosio想表达什么？
我拼的sqlstr可能不正确。
我现在想请教的是如何获取cursor中字段的值。谢谢！

[oracledbalgtu]

你的需求恐怕无法完成,只能改别的思路了.不能实现的原因是execute immediate 不接受非内置常规变量(我们这里就是cursor的一行数据).

DECLARE
  TYPE COLLIST IS TABLE OF VARCHAR2(2000);
  NAMES COLLIST;
  CURSOR C_111 IS
    SELECT * FROM TEST2;
  TMP_VALUE VARCHAR2(2000);
  STR_SQL   VARCHAR2(2000);
  COLL      VARCHAR2(30);
BEGIN
  NAMES := COLLIST('ID', 'NAME');

  FOR ONE_ROW IN C_111 LOOP
    FOR I IN 1 .. NAMES.COUNT LOOP
      /* --用这个plsql是可以把值赋给tmp_value的
      BEGIN
        TMP_VALUE := ONE_ROW.id;
      END;*/

      /*--这里无法把ONE_ROW 传给plsql块,因为oracle不允许.会报pls-00457 错误
      EXECUTE IMMEDIATE 'begin :x :=:y.'||names(i)||';end;' USING IN OUT tmp_value,one_row;
      */
      /*--你写的这个根本不符合oracle的语法,更是错误
       EXECUTE IMMEDIATE 'SELECT one_row.' || names(i) || ' FROM DUAL '
                INTO tmp_value;
      */
    
      STR_SQL := 'UPDATE one_table r ' ||
                 '   SET r.cause = r.cause || (SELECT ''' || NAMES(I) ||
                 ':'' || l.' || NAMES(I) || ' FROM test2 l ' ||
                 ' WHERE l.colA = ''' || TMP_VALUE || ''')';
      DBMS_OUTPUT.PUT_LINE(STR_SQL);
      /*EXECUTE IMMEDIATE 'UPDATE one_table r ' ||
      '   SET r.cause = r.cause || (SELECT ''' ||
      NAMES(I) || ':'' || l.' || NAMES(I) ||
      '                               FROM table2 l ' ||
      '                              WHERE l.colA = ''' ||
      TMP_VALUE || '''';*/
    END LOOP;
  END LOOP;
END;
/

[Quote=引用楼主 kaoziji 的帖子:]
Cursor有col1，col2两个字段，根据传入的字符串值"1"或者"2"，返回col1或者col2的值。
以上是我简化了实际情况的描述。
实际情况是，Cursor有许多column，我要在FOR-LOOP中column value作为参数，传给EXECUTE IMMEDIATE。

SQL codeCREATE TYPE ColList IS TABLE OF VARCHAR2(2000);

DECLARE
names ColList;
BEGIN
names := ColList('Com');
tmp_value VARCHAR2(2000);

FOR one_row IN c_111 LOOP
FOR…
[/Quote]

[kaoziji]

多谢oracledbalgtu！

DECLARE
  TYPE COLLIST IS TABLE OF VARCHAR2(2000);
  NAMES COLLIST;
  TMP_VALUE VARCHAR2(2000);
  STR_SQL   VARCHAR2(2000);
  COLL      VARCHAR2(30);
BEGIN
  NAMES := COLLIST('ID', 'NAME');

  FOR I IN 1 .. NAMES.COUNT LOOP
    -- 利用动态游标，只取出一个列
    -- performance就不考虑，反正是一次执行，慢点就慢点吧
    OPEN cur_111 FOR 'SELECT ' || names(i) || ' AS rValue FROM TEST2';
    LOOP
      FETCH cur_111 INTO one_remote_value;
      EXIT WHEN cur_111%NOTFOUND;
      -- 下面就不改了。思路表达清楚就可以了
      STR_SQL := 'UPDATE one_table r ' ||
                 '   SET r.cause = r.cause || (SELECT ''' || NAMES(I) ||
                 ':'' || l.' || NAMES(I) || ' FROM test2 l ' ||
                 ' WHERE l.colA = ''' || TMP_VALUE || ''')';
      DBMS_OUTPUT.PUT_LINE(STR_SQL);
      /*EXECUTE IMMEDIATE 'UPDATE one_table r ' ||
      '   SET r.cause = r.cause || (SELECT ''' ||
      NAMES(I) || ':'' || l.' || NAMES(I) ||
      '                               FROM table2 l ' ||
      '                              WHERE l.colA = ''' ||
      TMP_VALUE || '''';*/
    END LOOP;
  END LOOP;
END;
/