我正在努力解决第二天的以下问题。我有一个程序包，想从另一个函数中调用一个函数。我有一张带有坐标的地方表。因此，第一个函数返回两个城市之间的距离。第二个必须返回一个数字表，该数字表示从一个城市到其他城市的距离。因此，我不知道我在这里做错了什么：因此，Entfernung函数不起作用，但是abstand函数可以正常工作。
我收到以下编译错误：Fehler（11,20）：PLS-00103：遇到以下情况之一时遇到了符号“（”：。成批量Fehler（12,7）：PLS-00103：遇到了符号“退出”预期以下之一时：。（，％from
set serveroutput on
set echo on
CREATE OR REPLACE PACKAGE Geo AS
TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
FUNCTION Abstand(Stadt1 IN Rheinland_Staedte.Stadtname%TYPE, Stadt2 IN Rheinland_Staedte.Stadtname%TYPE) RETURN NUMBER;
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE) RETURN entfernungTab;
END Geo;
/
CREATE OR REPLACE PACKAGE BODY Geo AS
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
RETURN entfernungTab
IS
TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR cur_ent IS SELECT * FROM Rheinland_Staedte;
BEGIN
OPEN cur_ent;
LOOP
FETCH Abstand(Stadt, cur_ent.Stadtname) INTO entfernungTab
EXIT WHEN cur_ent%NOTFOUND;
END LOOP;
RETURN (entfernungTab);
END;
FUNCTION Abstand (Stadt1 IN Rheinland_Staedte.Stadtname%TYPE, Stadt2 IN Rheinland_Staedte.Stadtname%TYPE)
RETURN NUMBER
IS
v_dis NUMBER;
v_s1 Rheinland_Staedte%ROWTYPE;
v_s2 Rheinland_Staedte%ROWTYPE;
v_yAbstandMin NUMBER;
v_xAbstandMin NUMBER;
BEGIN
SELECT * INTO v_s1 FROM Rheinland_Staedte WHERE Stadtname = Stadt1;
SELECT * INTO v_s2 FROM Rheinland_Staedte WHERE Stadtname = Stadt2;
v_yAbstandMin := ((v_s1.GradNB * 60) + v_s1.MinNB)
- ((v_s2.GradNB * 60) + v_s2.MinNB);
v_xAbstandMin := ((v_s1.GradOL * 60) + v_s1.MinOL)
- ((v_s2.GradOL * 60) + v_s2.MinOL);
v_dis := SQRT((v_yAbstandMin * v_yAbstandMin)) + ((v_xAbstandMin * v_xAbstandMin));
RETURN (v_dis);
END;
END Geo;
希望你能给我一些建议。
你有几个问题。立即出现的错误是您试图以的形式调用函数fetch，这是不允许的。
这样编译；我已经在注释中添加了一些注释。
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
RETURN entfernungTab
IS
-- do not redelare the type in the function (or the package body)
-- TYPE entfernungTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR cur_ent IS SELECT * FROM Rheinland_Staedte;
-- you need a record to fetch into
cur_row cur_ent%ROWTYPE;
-- you need a variable of the return type, and to initialise it
returnTab entfernungTab;
-- you need an index variable for the table;
indx pls_integer := 0;
BEGIN
OPEN cur_ent;
LOOP
-- fetch into a record, and you can't call the function at this point
-- FETCH Abstand(Stadt, cur_ent.Stadtname) INTO entfernungTab
FETCH cur_ent INTO cur_row;
-- check this condition straight after fetching
EXIT WHEN cur_ent%NOTFOUND;
-- add the function result to the table, based on the record field value
indx := indx + 1;
returnTab(indx) := Abstand(Stadt, cur_row.Stadtname);
END LOOP;
-- close the cursor
CLOSE cur_ent;
-- return the variable, not the type
-- RETURN (entfernungTab);
RETURN returnTab;
END;
您也可以在游标查询中调用该函数，如果这是游标查询中唯一的表达式/列，则可以直接选择集合中的内容而无需循环：
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
RETURN entfernungTab
IS
-- call the function in the cursor query
CURSOR cur_ent IS SELECT Abstand(Entfernung.Stadt, Rheinland_Staedte.Stadtname)
FROM Rheinland_Staedte;
returnTab entfernungTab;
BEGIN
OPEN cur_ent;
-- bulk collect straight into your collection
FETCH cur_ent BULK COLLECT INTO returnTab;
CLOSE cur_ent;
RETURN returnTab;
END;
或者，如果您有很多记录，则可以添加一个限制：
FUNCTION Entfernung(Stadt IN Rheinland_Staedte.Stadtname%TYPE)
RETURN entfernungTab
IS
-- call the function in the cursor query
CURSOR cur_ent IS SELECT Abstand(Entfernung.Stadt, Rheinland_Staedte.Stadtname)
FROM Rheinland_Staedte;
returnTab entfernungTab;
BEGIN
OPEN cur_ent;
LOOP
-- bulk collect straight into your collection with limit
FETCH cur_ent BULK COLLECT INTO returnTab LIMIT 10;
EXIT WHEN cur_ent%NOTFOUND;
END LOOP;
CLOSE cur_ent;
RETURN returnTab;
END;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句