cursor for循环将o / p作为游标返回 [英] cursor for loop returns o/p as cursor
问题描述
嗨
我是游标的新手
我有一张桌子
SQL> select * from commodity_m_details;
COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ------------ ---
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4
i需要为每种商品编写不同年份的程序
o / p就像是
对于C3
年
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i写得像这样
Hi
I am new to cursors
I have a table
SQL> select * from commodity_m_details;
COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ---------------
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4
i need to write a procedure for different years for every commodity
o/p is like
For C3
year
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i have written like this
推荐答案
嗨
我是光标新手
我有一张桌子
SQL> select * from commodity_m_details;
COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ------------ ---
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4
i需要为每种商品编写不同年份的程序
o / p就像是
对于C3
年
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i写得像这样
创建或替换程序getYear（varid2中的commodityid，
结果出结果.r，
状态输出varchar2）
是
V_data数字（10）;
光标c1从commodity_m_details中选择start_year，end_year，interval_number，其中commodity_id = commodityid;
begin
V_data：= 0;
FOR记录在c1
LOOP
V_data：= V_data +（record.end_year-record.start_year）/ record.interval_number;
V_data：= record.start_year + V_data;
结束循环;
DBMS_OUTPUT.PUT_LINE（V_data）;
提交;
状态：=''0'';
例外
当别人那么
状态：=''7'';
结束getYear;
/
但它只给出了1986
请帮帮我
谢谢
Hi
I am new to cursors
I have a table
SQL> select * from commodity_m_details;
COMMODITY_ID COMMODITY_NAME STAR END INTERVAL
-------------------- ------------------------------ ---- ---- ---------------
C1 Commodity1 1996 2006 2
C2 Commodity2 1996 2000 2
C3 Commodity3 1983 1995 4
i need to write a procedure for different years for every commodity
o/p is like
For C3
year
-----------------
1983-1986
1986-1989
1989-1992
1992-1995
i have written like this
create or replace procedure getYear(commodityid in varchar2,
results out resultscur.r,
status out varchar2)
is
V_data number(10);
cursor c1 is select start_year,end_year,interval_number from commodity_m_details where commodity_id=commodityid;
begin
V_data:=0;
FOR record in c1
LOOP
V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
V_data:=record.start_year+V_data;
end loop;
DBMS_OUTPUT.PUT_LINE(V_data);
commit;
status:=''0'';
exception
when others then
status:=''7'';
end getYear;
/
But it is giving only 1986
please help me
Thank you
是的，它只会打印1986年因为：
检查以下行：
V_data：= 0;
V_data：= V_data +（record.end_year-record.start_year） /record.interval_number;
V_data：= record.start_year + V_data;
例如：
V_data：= 0 +（1995 - 1983）/ 4 - 这将给你3
V_data：= 1983 + 3 - 这将给你1986
DBMS_OUTPUT.PUT_LINE（V_data）;
为了获得1986,1989,1992,1995，你需要每年增加3次。
例如：
V_data现在= 1986
增加3将它给予V_data：= 1989然后1992然后1995你需要
所以对于光标中的每条记录，LOOP到interval no of times （即这里4次），计算（end_year - start_year）/ interval并将其存储在变量V_data中
现在执行以下操作：
Yes for C3 it will print only 1986 because:
check below lines:
V_data := 0;
V_data:=V_data+(record.end_year-record.start_year)/record.interval_number;
V_data:=record.start_year+V_data;
Eg:
V_data:= 0 + (1995 - 1983)/4 -- this will give you 3
V_data:= 1983 + 3 -- this will give you 1986
DBMS_OUTPUT.PUT_LINE(V_data);
In order to get 1986,1989,1992,1995, you need to add 3 to the year 4 times.
Eg:
V_data now = 1986
adding 3 to it will givve V_data:= 1989 then 1992 then 1995 as you require
So For each record from cursor, LOOP through "interval no of times" (ie 4 times here), calculate (end_year - start_year)/interval and store it in a variable V_data
Now do the following:
我的表结构已更改（间隔为增量）
comm_id start_year end_year increment
-------------- --------------- --------------- ---- -------------
C1 1996 2006 2
C2 2000 2004 1
C3 1990 2010 4
现在我想要每个comm_id的o / p如
对于c1
< br $> b $ b 1996
1998
2000
2002
2004
2006
for c2
2000
2001
2002
2003
2004
请给我一个查询
谢谢你
Hi, my table structure is changed(interval as increment)
comm_id start_year end_year increment
-------------- --------------- --------------- -----------------
C1 1996 2006 2
C2 2000 2004 1
C3 1990 2010 4
Now i want the o/p for every comm_id like
For c1
1996
1998
2000
2002
2004
2006
For c2
2000
2001
2002
2003
2004
Please give me a query for this
Thank u
请发布到目前为止您尝试过的内容。
Kindly post what you have tried so far.
这篇关于cursor for循环将o / p作为游标返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
