时间戳

首页 > 解决方案 > ORA-01722: 无效号码 ORA-06512: 在第 17 行 01722. 00000 - “无效号码”

plsql - ORA-01722: 无效号码 ORA-06512: 在第 17 行 01722. 00000 - “无效号码”

问题描述

我在编码时遇到这种类型的错误：

ORA-01722: 无效号码 ORA-06512: 在第 17 行 01722. 00000 - “无效号码”

declare
dname varchar(15);
mname varchar(20);
sal number(20);
mid number(20);
row_worker worker%rowtype;
cursor work_cur(manid number) is select w.* from worker w ,manager m where w.manager_id = manid and w.manager_id=m.manager_id;
begin
dname:='&dname';
for x in(select m.department,m.name,m.salary,m.manager_id into dname,mname,sal,mid from manager m, worker w where m.department=dname and m.manager_id=w.manager_id)
loop
DBMS_OUTPUT.PUT_LINE('Department Name:'||dname||'       Manager Name:'||mname||'        Salary:'||sal);
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Worker_id    Name     Specialized_in     Salary       Join_date       Dept_name');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
open work_cur(mid);
fetch work_cur into row_worker;
--exit when work_cur%notfound;
DBMS_OUTPUT.PUT_LINE(''||row_worker.worker_id||'    '||row_worker.name||'   '||row_worker.specialized_in||'     '||row_worker.salary||'     '||row_worker.join_date);
end loop;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
--DBMS_OUTPUT.PUT_LINE(1/n)
close work_cur;
end;

我希望记录的输出与两个表的经理 ID 匹配，并且将部门名称与用户给出的经理表匹配

标签： plsql

解决方案

您的光标 for 循环看起来不正确 - 您没有选择光标中的变量 - 您使用 for 循环中隐式声明的记录，如下所示：

DECLARE
  dname      VARCHAR(15);
  mname      VARCHAR(20);
  sal        NUMBER(20);
  mid        NUMBER(20);
  row_worker worker%ROWTYPE;
  CURSOR work_cur(manid NUMBER) IS
    SELECT w.*
    FROM   worker  w,
           manager m
    WHERE  w.manager_id = manid
    AND    w.manager_id = m.manager_id;
BEGIN
  dname := '&dname';
  FOR x IN (SELECT m.department,
                   m.name,
                   m.salary,
                   m.manager_id
            FROM   manager m,
                   worker  w
            WHERE  m.department = dname
            AND    m.manager_id = w.manager_id)
  LOOP
    dbms_output.put_line('Department Name:' || x.department || '       Manager Name:' || x.name || '        Salary:' || x.salary);
    dbms_output.put_line('------------------------------------------------------------------------------');
    dbms_output.put_line('Worker_id    Name     Specialized_in     Salary       Join_date       Dept_name');
    dbms_output.put_line('------------------------------------------------------------------------------');

    OPEN work_cur(x.manager_id);
    FETCH work_cur
      INTO row_worker;
    --exit when work_cur%notfound;
    dbms_output.put_line('' || row_worker.worker_id || '    ' || row_worker.name || '   ' || row_worker.specialized_in || '     ' ||
                         row_worker.salary || '     ' || row_worker.join_date);
  END LOOP;
  dbms_output.put_line('------------------------------------------------------------------------------');
  --DBMS_OUTPUT.PUT_LINE(1/n)
  CLOSE work_cur;
END;
/

注意我已经对您的代码进行了格式化，使其更易于阅读（尽管我使用的特定格式并不是全部格式！）。

推荐阅读