想用 NCLOSEST('GRETA', 2) 从CONNECTION表中找到DISTANCE 最近的2个城市;
GRETA 表示,,与GRETA 最近的城市。 2表示,要找到2个城市;CONNECTION 表 大致为:
FROM TO DISTANCE
GRETA BELDA 10
DMET GRETA 5
BELDA DMET 12
SDFT GRETA 8
GRETA CSFWE 7那么运行NCLOSEST('GRETA', 2) 得到的结果应该是 DMET, CSFWE ‘CREATE OR REPLACE FUNCTION NCLOSEST(
PARAMETE VARCHAR(300),
NUM NUMBER)
RETURN VARCHAR IS CITY VARCHAR(300);DECLARE
TIME NUMBER :=1;
MINDISTANCE NUMBER:=0; BEGIN
CREATE TABLE CONNECTIONS AS SELECT * FROM CONNECTION;
CREATE OR REPLACE VIEW CITYS(CITY1, CITY2, DISTANCE) AS
SELECT *
FROM CONNECTIONS
WHERE FROMCITY = 'GRETA' OR TOCITY = 'GRETA'
ORDER BY DISTANCE; FRO TIME IN 1..NUM
LOOP
MINDISTANCE :=
(SELECT MIN(DISTANCE)
FROM CITYS);
CREATE OR REPLACE VIEW MINCITY(CITY1, CITY2, DISTANCE) AS
SELECT CITY1, CITY2, DISTANCE
FROM CITYS
WHERE DISTANCE = MINDISTANCE;
CREATE OR REPLACE VIEW MINCITYS(ONECITY) AS
SELECT CITY1 FROM MINCITY
UNION
SELECT CITY2 FROM MINCITY;
CITY := (SELECT *
FROM MINCITYS
WHERE NOT (ONECITY = 'GRETA')); DELETE FROM CITYS WHERE DISTANCE = MINDISTANCE; END LOOP;RETURN CITY;
DROP TABLE CONNECTIONS;
END NCLOSEST;
/我暂时写的是这样,不知道正确与否,无法编译运行,求帮助
GRETA 表示,,与GRETA 最近的城市。 2表示,要找到2个城市;CONNECTION 表 大致为:
FROM TO DISTANCE
GRETA BELDA 10
DMET GRETA 5
BELDA DMET 12
SDFT GRETA 8
GRETA CSFWE 7那么运行NCLOSEST('GRETA', 2) 得到的结果应该是 DMET, CSFWE ‘CREATE OR REPLACE FUNCTION NCLOSEST(
PARAMETE VARCHAR(300),
NUM NUMBER)
RETURN VARCHAR IS CITY VARCHAR(300);DECLARE
TIME NUMBER :=1;
MINDISTANCE NUMBER:=0; BEGIN
CREATE TABLE CONNECTIONS AS SELECT * FROM CONNECTION;
CREATE OR REPLACE VIEW CITYS(CITY1, CITY2, DISTANCE) AS
SELECT *
FROM CONNECTIONS
WHERE FROMCITY = 'GRETA' OR TOCITY = 'GRETA'
ORDER BY DISTANCE; FRO TIME IN 1..NUM
LOOP
MINDISTANCE :=
(SELECT MIN(DISTANCE)
FROM CITYS);
CREATE OR REPLACE VIEW MINCITY(CITY1, CITY2, DISTANCE) AS
SELECT CITY1, CITY2, DISTANCE
FROM CITYS
WHERE DISTANCE = MINDISTANCE;
CREATE OR REPLACE VIEW MINCITYS(ONECITY) AS
SELECT CITY1 FROM MINCITY
UNION
SELECT CITY2 FROM MINCITY;
CITY := (SELECT *
FROM MINCITYS
WHERE NOT (ONECITY = 'GRETA')); DELETE FROM CITYS WHERE DISTANCE = MINDISTANCE; END LOOP;RETURN CITY;
DROP TABLE CONNECTIONS;
END NCLOSEST;
/我暂时写的是这样,不知道正确与否,无法编译运行,求帮助
解决方案 »
- 时间转换
- 有cognos商业智能的中文教程或视频吗?
- 关于更改Oracle Home的名称的问题!!!
- 急急,帮忙写个触发器
- oracle 多表更新问题
- Hibernate不能正确生成主键的问题
- oracle9i 如何将一个表中的long raw 数据插入到另一个表中的long raw 列?
- 请如何备份,恢复ORACLE的数据库?
- 代码最后一行出错,为什么调用不了? 求大神们可以解答
- 请教各位高人oracle中tnsnames.ora,listener.ora,sqlnet.ora三个文件的作用?最好能容易理。数据库启动时为何会分为三个状态?
- Oracle 11gR2 服务器端安装
- 游标变量不能定义参数和传参数的吗?
--暂时找到一个错误:去掉declare
--编译看看,若有其它的错误你show errors看看具体是什么
CREATE OR REPLACE FUNCTION NCLOSEST(
PARAMETE VARCHAR(300),
NUM NUMBER)
RETURN VARCHAR
IS
CITY VARCHAR(300);
--去掉这里的DECLARE
TIME NUMBER :=1;
MINDISTANCE NUMBER:=0; ...
...
...
CREATE OR REPLACE FUNCTION NCLOSEST(
PARAMETE VARCHAR2,
NUM NUMBER)
变量不用declare
赋值
建表
建视图
for循环
都有错
MINDISTANCE :=(SELECT MIN(DISTANCE)FROM CITYS);
这样赋值是不行地,Oracle中要这样:
SELECT MIN(DISTANCE) into MINDISTANCE FROM CITYS;楼主慢慢改下吧
可以用pl/sql dev,语法编译一遍
看看都有哪些错误
execute immediate 'CREATE TABLE CONNECTIONS AS SELECT * FROM CONNECTION';
哎哟
算了 重新给你修改下:CREATE OR REPLACE FUNCTION NCLOSEST(
PARAMETE VARCHAR2,
NUM NUMBER)
RETURN VARCHAR2
IS
CITY VARCHAR2(300);
R_CITY VARCHAR2(300);
TIME NUMBER :=1;
MINDISTANCE NUMBER:=0; BEGIN
execute immediate 'CREATE TABLE CONNECTIONS AS SELECT * FROM CONNECTION';
execute immediate 'CREATE OR REPLACE VIEW CITYS(CITY1, CITY2, DISTANCE) AS
SELECT * FROM CONNECTIONS
WHERE FROMCITY = ''GRETA'' OR TOCITY = ''GRETA''
ORDER BY DISTANCE';
FOR TIME IN 1..NUM LOOP
SELECT MIN(DISTANCE) into MINDISTANCE FROM CITYS;
execute immediate 'CREATE OR REPLACE VIEW MINCITY(CITY1, CITY2, DISTANCE) AS
SELECT CITY1, CITY2, DISTANCE
FROM CITYS WHERE DISTANCE = '||MINDISTANCE;
execute immediate 'CREATE OR REPLACE VIEW MINCITYS(ONECITY) AS SELECT CITY1 as ONECITY FROM MINCITY
UNION
SELECT CITY2 as ONECITY FROM MINCITY';
SELECT ONECITY into CITY FROM MINCITYS WHERE ONECITY != 'GRETA';
DELETE FROM CITYS WHERE DISTANCE = MINDISTANCE;
commit;
R_CITY:=CITY||',';
END LOOP;
execute immediate 'DROP TABLE CONNECTIONS';
SELECT SUBSTR(R_CITY,1,LENGTH(R_CITY)-1) INTO R_CITY FROM DUAL;
RETURN R_CITY;
END NCLOSEST;