从 Oracle 游标的下一行获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3043124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting data from the next row in Oracle cursor
提问by Daniel Ganiev
I'm building nested tree and I need to get data for the next row in cursor, using Oracle. And I still need current row, so looping forward is not a solution. Example:
我正在构建嵌套树，我需要使用 Oracle 获取游标中下一行的数据。而且我仍然需要当前行，因此向前循环不是解决方案。例子：
OPEN emp_cv FOR sql_stmt;
LOOP
FETCH emp_cv INTO v_rcod,v_rname,v_level;
EXIT WHEN emp_cv%NOTFOUND;
/*here lies the code for getting v_next_level*/
if v_next_level > v_level then
/*code here*/
elsif v_next_level < v_level then
/*code here*/
else
/*code here*/
end if;
END LOOP;
CLOSE emp_cv;
回答by Bharat
Use LEAD and LAG Functions
使用 LEAD 和 LAG 函数
LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:
LEAD 能够计算下一行（将在当前行之后的行）的表达式并将值返回到当前行。LEAD 的一般语法如下所示：
LEAD (sql_expr, offset, default) OVER (analytic_clause)
LEAD (sql_expr, offset, default) OVER (analytic_clause)
sql_expris the expression to compute from the leading row.
sql_expr是从前导行计算的表达式。
offsetis the index of the leading row relative to the current row.offset is a positive integer with default 1.
offset是前导行相对于当前行的索引。offset 是一个正整数，默认为 1。
defaultis the value to return if the points to a row outside the partition range.
如果指向分区范围之外的行，则默认值是要返回的值。
The syntax of LAG is similar except that the offset for LAG goes into the previous rows.
LAG 的语法类似，只是 LAG 的偏移量进入前几行。
SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC) NEXT_LOW_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC) PREV_HIGH_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;
DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
10 7839 5000 2450 0
10 7782 2450 1300 5000
10 7934 1300 0 2450
20 7788 3000 3000 0
20 7902 3000 2975 3000
20 7566 2975 1100 3000
20 7876 1100 800 2975
20 7369 800 0 1100
8 rows selected.
回答by Daniel Ganiev
I did it this way. I'm building a tree in a step backwards. I have to check for the first iteration.
我是这样做的。我正在倒退一步建一棵树。我必须检查第一次迭代。
OPEN emp_cv FOR sql_stmt;
LOOP
if emp_cv%notfound then
/*some code*/
exit;
end if;
FETCH emp_cv INTO v_new_level;
if not b_first_time then
if v_new_level > v_level then
/*some code*/
elsif v_new_level < v_level then
/*some code*/
else
/*code*/
end if;
else
b_first_time:=false;
end if;
v_level:=v_new_level;
END LOOP;
CLOSE emp_cv;
回答by potatopeelings
would it be better to store the previouslevel and use that. something along the lines of
存储以前的级别并使用它会更好。类似的东西
/* set to a value lesser than the lowest value possible for level
am assuming 0 is the lowest value possible */
v_previous_level := -1;
OPEN emp_cv FOR sql_stmt;
LOOP
FETCH emp_cv INTO v_rcod,v_rname,v_level;
EXIT WHEN emp_cv%NOTFOUND;
/* you'd probably have to update v_previous_level in one of
these conditions (depends on your logic) */
if v_previous_level > v_level then
/*code here*/
elsif v_previous_level < v_level then
/*code here*/
else
/*code here*/
end if;
END LOOP;
CLOSE emp_cv;
