oracle - 为什么这个过程不返回值?
问题描述
这是我的代码:
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE fillHist_station
AS
*--filling the cursor*
CURSOR cur_newTab IS(
SELECT s.NUM_STATION, extract (year from r.DATER), cl.NB_RESERV, SUM(p.MONTANT)
FROM STATION s, HOTEL h, CHAMBRE c, RESERVATION r, CLIENTS cl, PAYEMENT p
WHERE s.NUM_STATION=h.NUM_STATION and h.NUM_HOTEL=c.NUM_HOTEL and c.IDCHAMBRE=r.IDCHAMBRE and r.NUMC=cl.NUMC and cl.NUMC=p.NUMC
GROUP BY s.NUM_STATION, extract (year from r.DATER), cl.NB_RESERV);
V_numStation STATION.NUM_STATION%TYPE;
V_anne VARCHAR2(4);
V_nbreserve CLIENTS.NB_RESERV%TYPE;
V_revenu NUMBER(5);
BEGIN
OPEN cur_newTab;
LOOP
*--fetching the values from the cursor in my variables*
FETCH cur_newTab INTO V_numStation, V_anne, V_nbreserve, V_revenu;
DBMS_OUTPUT.PUT_LINE(V_numStation||' '||V_anne||' '||V_nbreserve||' '||V_revenu);
EXIT WHEN cur_newTab%notfound;
*--inserting the values of the variables in a new table that i just created*
INSERT INTO HIST_STATION ("NUM_STATION","ANNEE","NB_RESERV","REVENU") VALUES(V_numStation,V_anne,V_nbreserve,V_revenu);
*--showing the values of the variables in the dbms output to see my results*
DBMS_OUTPUT.PUT_LINE(V_numStation||' '||V_anne||' '||V_nbreserve||' '||V_revenu);
END LOOP;
CLOSE cur_newTab;
END fillHist_station;
/
SHOW ERRORS;
并且没有检测到错误。这是脚本输出:
Procedure FILLHIST_STATION compiled
No errors.
唯一的问题是在我的 dbms 输出中我没有得到任何结果,并且新表仍然是空的。
这是应该出现的内容:
2 2018 6 1400
2 2017 1 2800
2 2018 3 8800
5 2018 2 1000
5 2018 1 950
3 2019 1 2800
2 2019 6 1400
2 2018 5 1550
3 2018 5 1550
3 2018 1 2800
1 2018 4 2300
5 2018 4 1300
解决方案
我希望您需要将变量 (V_*) 移动到声明部分。
尝试首先打印游标记录中的值以确保游标工作正常。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE fillHist_station
AS
*--filling the cursor*
CURSOR cur_newTab IS(
SELECT s.NUM_STATION,extract (year from r.DATER),cl.NB_RESERV,SUM(p.MONTANT)
FROM STATION s, HOTEL h, CHAMBRE c, RESERVATION r, CLIENTS cl, PAYEMENT p
WHERE s.NUM_STATION=h.NUM_STATION and h.NUM_HOTEL=c.NUM_HOTEL and c.IDCHAMBRE=r.IDCHAMBRE and r.NUMC=cl.NUMC and cl.NUMC=p.NUMC
GROUP BY s.NUM_STATION, extract (year from r.DATER), cl.NB_RESERV);
BEGIN
FOR cur_newTab_rec IN cur_newTab
LOOP
DBMS_OUTPUT.PUT_LINE(cur_newTab_rec.NUM_STATION);
END LOOP;
CLOSE cur_newTab;
END fillHist_station;
/
SHOW ERRORS;
推荐阅读
- c# - 如何启用 ASP.NET 对注册表项“HKEY_CURRENT_USER\Software\myAppKey\subdir”的写访问权限?
- java - Java Robot keyPress 按住一个键
- node.js - 部署 Firebase 功能时出错
- dns - 旧网站 URL 在 Google 中显示新网站
- dask - 不能用 Dask 训练 Keras 模型?
- php - 对数组键值进行排序?
- r - 控制主要网格线的长度 - ggplot2
- python - pandas,read_excel,usecols 与列表输入生成一个空数据框
- c# - 将动画剪辑添加到 Animation 组件
- python - django 中的博客计数