我正在嘗試在 oracle 中顯示記錄行型別，我得到了
SQL 錯誤 [900] [42000]: ORA-00900: 第 7 行的 SQL 陳述句無效
這里 ：
intraftrecord xyz%rowtype;
我似乎無法弄清楚。
CREATE OR REPLACE PROCEDURE intraftresponseprocedure
IS
CURSOR xyz IS
select REGEXP_REPLACE(RESPONSE_PARAM , '-', '') from (
select RESPONSE_PARAM from AUDIT_LOG WHERE ACTIVITY ='Intra Bank Funds Transfer' order by id asc
);
intraftrecord xyz%rowtype;
BEGIN
OPEN xyz;
LOOP
FETCH xyz INTO intraftrecord;
EXIT WHEN intraftrecord%notfound;
dbms_output.put_line(intraftrecord);
END LOOP;
END;
uj5u.com熱心網友回復：
三個問題——
- 具有
%found屬性的是游標，而不是記錄。 dbms_output.put_line()將單個 varchar2 作為引數，而不是xyz%rowtype- 您需要參考要
intraftrecord顯示的特定屬性，因此游標需要為其命名。
固定版本：
create or replace procedure intraftresponseprocedure
as
cursor xyz is
select regexp_replace(response_param, '-', '') as response
from ( select response_param
from audit_log
where activity = 'Intra Bank Funds Transfer'
order by id asc );
intraftrecord xyz%rowtype;
begin
open xyz;
loop
fetch xyz into intraftrecord;
exit when xyz%notfound;
dbms_output.put_line(intraftrecord.response);
end loop;
end;
游標也可以簡化，不需要顯式宣告和獲取的游標和記錄。簡化版：
create or replace procedure intraftresponseprocedure
as
begin
for r in (
select regexp_replace(response_param, '-', '') as response
from audit_log
where activity = 'Intra Bank Funds Transfer'
order by id asc
)
loop
dbms_output.put_line(r.response);
end loop;
end;
uj5u.com熱心網友回復：
您必須在列印陳述句中指定列名 -
CREATE OR REPLACE PROCEDURE intraftresponseprocedure
IS
CURSOR xyz IS
select REGEXP_REPLACE(RESPONSE_PARAM , '-', '') col1 from (
select RESPONSE_PARAM from AUDIT_LOG WHERE ACTIVITY ='Intra Bank Funds Transfer' order by id asc
);
intraftrecord xyz%rowtype;
BEGIN
OPEN xyz;
LOOP
FETCH xyz INTO intraftrecord;
EXIT WHEN xyz%notfound;
dbms_output.put_line(intraftrecord.col1);
END LOOP;
END;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/425320.html
上一篇：從C#應用程式執行時，OracleSQL陳述句不回傳任何行
下一篇：Webpack差異構建