運行此查詢時出現此錯誤。
PLS-00428：此 SELECT 陳述句中需要 INTO 子句
詢問：
DECLARE
v_flag_tipo_factu varchar2(20);
BEGIN
SELECT valor_param INTO v_flag_tipo_factu FROM t_param WHERE cod_param =
'TIPOFAC';
if v_flag_tipo_factu = 'FN' THEN
SELECT
substr('00' || to_char(cta.cod_correo), -2) ||
substr('000000000' || to_char(cta.num_cta_cte), -9) ||
substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
FROM
t_acc_const cta,
t_fac fac
WHERE
cta.num_cta_cte = fac.num_cta_cte AND
fac.num_factura < 900000000
GROUP BY cta.cod_correo, cta.num_cta_cte;
end if;
END;
我預計會有 240,000 個這樣的結果：
13000291956000774037
06000167340000223372
13000089241000246480
13000057283000105163
06001632092003471840
13000093581000257191
12000252313001947873
06000120216000066999
06000309294001948770
13000192054000285006
uj5u.com熱心網友回復：
如果您不喜歡 PL/SQL 規則，其中查詢必須存盤在每個步驟的變數中，您可以使用這樣的查詢，它應該回傳相同的
with v as (
SELECT valor_param FROM t_param WHERE cod_param = 'TIPOFAC'
)
SELECT
substr('00' || to_char(cta.cod_correo), -2) ||
substr('000000000' || to_char(cta.num_cta_cte), -9) ||
substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
FROM
t_acc_const cta,
t_fac fac
WHERE
cta.num_cta_cte = fac.num_cta_cte AND
fac.num_factura < 900000000 AND
EXISTS (select 1 from v where valor_param = 'FN')
GROUP BY cta.cod_correo, cta.num_cta_cte;
uj5u.com熱心網友回復：
正如你被告知的，SELECT（里面的那個IF）缺少 thenINTO子句。您必須再宣告一個區域變數并使用它：
DECLARE
v_flag_tipo_factu varchar2(20);
--
v_result varchar2(50); --> this
BEGIN
SELECT valor_param INTO v_flag_tipo_factu FROM t_param WHERE cod_param =
'TIPOFAC';
if v_flag_tipo_factu = 'FN' THEN
SELECT
substr('00' || to_char(cta.cod_correo), -2) ||
substr('000000000' || to_char(cta.num_cta_cte), -9) ||
substr('000000000' || to_char(max(distinct fac.num_fac)),- 9)
INTO v_result --> this
FROM
...
如果select回傳單個值，它將起作用；否則，查詢將引發
no_data_found如果沒有滿足條件的行too_many_rows. 如何處理？這取決于你想做什么......
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/371586.html
下一篇：在表單上將記錄復制為新記錄