oracle中for循环变量的where条件参数

wqsoz72f  于 2022-12-18  发布在  Oracle
关注(0)|答案(2)|浏览(209)
declare
  cursor abc is
    select USER_NAME from dba_users;
begin
  for i in abc
  loop
    select status
      into person_status
      from dba_users
     where USER_NAME := i;

    insert status into table_1;
    commit;
  end loop;
end;
/

它给出的错误如下:

select status into person_status  from dba_users where USER_NAME := i;
ERROR at line 18:
ORA-06550: line 18, column 77:
PL/SQL: ORA-00920: invalid relational operator
ORA-06550: line 18, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 82:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored

有人能帮我把for循环变量传递给选择语句的where条件吗?
谢谢。

x33g5p2x

x33g5p2x1#

它不仅仅是无效的where子句;还有很多其他的错误
假设这是一个目标表:

SQL> CREATE TABLE table_1
  2  (
  3     user_name   VARCHAR2 (128),
  4     status      VARCHAR2 (32)
  5  );

Table created.

PL/SQL代码(阅读注解):

SQL> DECLARE
  2     CURSOR abc IS SELECT username FROM dba_users; --> USERNAME, not USER_NAME
  3
  4     person_status  dba_users.account_status%TYPE; --> declare a local variable
  5  BEGIN
  6     FOR i IN abc
  7     LOOP
  8        SELECT account_status                      --> it is ACCOUNT_STATUS, not just STATUS
  9          INTO person_status                       --> select value into a local variable
 10          FROM dba_users
 11         WHERE username = i.username;              --> reference column name in cursor variable
 12
 13        INSERT INTO table_1 (user_name, status)    --> valid INSERT statement
 14             VALUES (i.username, person_status);
 15     END LOOP;
 16
 17     COMMIT;                                       --> commit outside of the loop
 18  END;
 19  /

PL/SQL procedure successfully completed.

结果:

SQL> select * from table_1 where rownum <= 5;

USER_NAME            STATUS
-------------------- --------------------------------
VAL_ADV              OPEN
VAL_SEP              OPEN
ISPOL                OPEN
LGRE                 OPEN
TADMIN               OPEN

SQL>


另一方面(正如Jain已经说过的),您应该直接插入行,而不用PL/SQL:

SQL> INSERT INTO table_1 (user_name, status)
  2     SELECT username, account_status FROM dba_users;

83 rows created.

SQL>
twh00eeo

twh00eeo2#

您在where条件中使用了:=
用户名:= i;
使用时不要:喜欢

USER_NAME = i;

或者您可以使用单个命令来完成此工作。

Create table table_1 (name varchar2(30),sts varchar2(100));
insert into table_1 (name,sts) (select USER_NAME,PERSON_STATUS FROM DBA_USERS);
COMMIT;

但是,我在我的DB上描述了DBA_USERS,其中没有Person_status字段,而是有ACCOUNT_STATUS字段。

相关问题