有没有一种方法可以通过使用参数来选择几行? [英] Is there a way to select several rows by using parameters?
问题描述
我正在尝试编写一个代码,该代码可以从图形表中找到所有可能的路线.该代码应通过使用单个输入值来工作.例如.我想找到从A点到B点的所有可能路线.
I am trying to write a code which can find all possible routes from a graph table. The code should be worked by using a single input value. E.g. I want to find all possible routes from point A to Point B.
我认为某个过程也许可以解决问题,但是我得到了错误消息.
I think that a procedure can maybe solve the problem, but I get the error message.
图形表格(所有路线的概述)为
The graph tabel (a overview of all routes) is
P_FROM P_TO DISTANCE
A B 4
A C 7
B C 10
C D 15
B D 17
A D 23
B E 22
C E 29
过程是
CREATE OR REPLACE PROCEDURE p_find_all_routes (
p_start IN VARCHAR2 DEFAULT '%',
p_end IN VARCHAR2 DEFAULT '%',
p_via IN VARCHAR2 DEFAULT '%')
AS
BEGIN
-- =======================================================================
-- Author: Coilin P. Boylan Jeritslev (CTBJ)
-- Description: Find all possible routes between two different points
-- "p_start" and "p_end" via the choosen point "p_via" in a graph-tabel.
-- =======================================================================
WITH multiroutes (p_from, p_to, full_route, total_distance)
AS (SELECT p_from,
p_to,
p_from || '->' || p_to full_route,
distance total_distance
FROM graph
WHERE p_from LIKE p_start
UNION ALL
SELECT M.p_from,
n.p_to,
M.full_route || '->' || n.p_to full_route,
M.total_distance + n.distance total_distance
FROM multiroutes M JOIN graph n ON M.p_to = n.p_from
WHERE n.p_to <> ALL (M.full_route))
SELECT *
FROM multiroutes
WHERE p_to LIKE p_end
AND ( full_route LIKE ('%->' || p_via || '%')
OR full_route LIKE ('%' || p_via || '->%'))
ORDER BY p_from, p_to, total_distance ASC;
END;
/
当我使用以下输入执行过程时:
When I'm executing the procedure with the following inputs:
EXEC p_find_all_routes('A','E','%')
我希望程序已编译并得到结果
I expect, the procedure is compiled and I get the result
P_FROM P_TO FULL_ROUTE TOTAL_DISTANCE
A E A->B->E 26
A E A->C->E 36
A E A->B->C->E 43
但是我收到PLS-00428错误消息.关于INTO.
But I get the PLS-00428 error message. Something about INTO.
我不想将输出值插入表格.我只想查看输出值.我该怎么办?
I don't want to insert output values into a tabel. I want just to see the output values. How can I do it?
推荐答案
您不能仅使用查询在Oracle中返回结果集.您需要使用Ref光标.您可以尝试以下代码-
You cannot return the result set in Oracle by using only Query. You need to use Ref cursor for the same. You can try below code -
CREATE OR REPLACE PROCEDURE p_find_all_routes (
p_start IN VARCHAR2 DEFAULT '%',
p_end IN VARCHAR2 DEFAULT '%',
p_via IN VARCHAR2 DEFAULT '%',
multiroutes OUT SYS_REFCURSOR)
AS
BEGIN
-- =======================================================================
-- Author: Coilin P. Boylan Jeritslev (CTBJ)
-- Description: Find all possible routes between two different points
-- "p_start" and "p_end" via the choosen point "p_via" in a graph-tabel.
-- =======================================================================
OPEN multiroutes FOR
WITH multiroutes (p_from, p_to, full_route, total_distance)
AS (SELECT p_from,
p_to,
p_from || '->' || p_to full_route,
distance total_distance
FROM graph
WHERE p_from LIKE p_start
UNION ALL
SELECT M.p_from,
n.p_to,
M.full_route || '->' || n.p_to full_route,
M.total_distance + n.distance total_distance
FROM multiroutes M JOIN graph n ON M.p_to = n.p_from
WHERE n.p_to <> ALL (M.full_route))
SELECT *
FROM multiroutes
WHERE p_to LIKE p_end
AND ( full_route LIKE ('%->' || p_via || '%')
OR full_route LIKE ('%' || p_via || '->%'))
ORDER BY p_from, p_to, total_distance ASC;
END;
/
随后您可以通过声明Ref游标变量来调用此过程.
You can then call this procedure later by declaring Ref cursor variable.
DECLARE
Result SYS_REFCURSOR;
BEGIN
p_find_all_routes('A','E','%', Result);
END;
这篇关于有没有一种方法可以通过使用参数来选择几行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!