PL / SQL - 修改程序 [英] PL/SQL - modifying the program

问题描述

目前，查询输出是;有2件物品价格最高：$ 80.00



我如何在下面的程序中以最高价格显示partid。？



我希望输出为：有2个物品价格最高：$ 80.00有partids（这应该是价格最高的部分）。< br $>




DECLARE



vPrice inventory.price％TYPE;

vPartid inventory.partid％TYPE;

vcnt INTEGER;



CURSOR highestprice_cur

IS

SELECT价格，vcnt，vpartid

--INTO vprice，vcnt



FROM（选择价格，COUNT （*）vcnt

FROM库存

GROUP BY价格

订购价格DESC）

WHERE ROWNUM = 1 ;

highestprice_items_cur highestprice_cur％ROWTYPE;

BEGIN

OPEN highestprice_cur;

LOOP

FETCH highestprice_cur INTO vprice，vcnt，vpartid;

EXIT

WHEN highestprice_cur％NOTFOUND ;

IF vcnt> 1

那么

DBMS_OUTPUT.put_line（

''有''

|| vcnt ||' '物品价格最高：''

|| TO_CHAR（vprice，''fm $ 999,99.00''））;

END IF;

END LOOP;

关闭highestprice_cur;

EXCEPTION

当NO_DATA_FOUND

那么

NULL;

当其他人

那么

DBMS_OUTPUT.put_line（''ERROR OTHERS''|| SQLERRM）;

END;

解决方案

80.00



如何在以下程序中以最高价格显示partid还有。



我希望输出为：有2件物品价格最高：

80.00 with partids（这应该是价格最高的部分）。





DECLARE



vPrice inventory.price％TYPE;

vPartid inventory.partid％TYPE;

vcnt INTEGER;



CURSOR highestprice_cur

IS

SELECT价格，vcnt，vpartid

--INTO vprice，vcnt < br $>


FROM（选择价格，COUNT（*）vcnt

来自库存

GROUP BY价格

ORDER BY价格DESC）

WHERE ROWNUM = 1;

highestprice_items_cur highestprice_cur％ROWTYPE;

BEGIN

OPEN highestprice_cur;

LOOP

FETCH highestprice_cur INTO vprice，vcnt，vpartid;

EXIT

WHEN highestprice_cur％NOTFOUND;

IF vcnt> 1

那么

DBMS_OUTPUT.put_line（

''有''

|| vcnt ||' '价格最高的商品：''

|| TO_CHAR（vprice，''fm

999,99.00''））;

结束IF;

END LOOP;

关闭highestprice_cur;

EXCEPTION

当NO_DATA_FOUND
那么

NULL;

当其他人

那么

DBMS_OUTPUT.put_line（''ERROR OTHERS''|| SQLERRM）;

END;

Currently, the query output is; There are 2 items with the highest price: $80.00

How can I show the partids for the highest price in the program below as well.?

I would like the output to be: There are 2 items with the highest price: $80.00 with partids(This should be the partids of the highest prices).


DECLARE

vPrice inventory.price%TYPE;
vPartid inventory.partid%TYPE;
vcnt INTEGER;

CURSOR highestprice_cur
IS
SELECT price, vcnt,vpartid
--INTO vprice, vcnt

FROM (SELECT price, COUNT(*) vcnt
FROM inventory
GROUP BY price
ORDER BY price DESC)
WHERE ROWNUM =1;
highestprice_items_cur highestprice_cur%ROWTYPE;
BEGIN
OPEN highestprice_cur;
LOOP
FETCH highestprice_cur INTO vprice, vcnt,vpartid;
EXIT
WHEN highestprice_cur%NOTFOUND;
IF vcnt > 1
THEN
DBMS_OUTPUT.put_line(
''There are ''
|| vcnt || '' items with the highest price: ''
|| TO_CHAR(vprice, ''fm$999,99.00''));
END IF;
END LOOP;
CLOSE highestprice_cur;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(''ERROR OTHERS '' || SQLERRM);
END;

解决方案

80.00

How can I show the partids for the highest price in the program below as well.?

I would like the output to be: There are 2 items with the highest price:

80.00 with partids(This should be the partids of the highest prices).


DECLARE

vPrice inventory.price%TYPE;
vPartid inventory.partid%TYPE;
vcnt INTEGER;

CURSOR highestprice_cur
IS
SELECT price, vcnt,vpartid
--INTO vprice, vcnt

FROM (SELECT price, COUNT(*) vcnt
FROM inventory
GROUP BY price
ORDER BY price DESC)
WHERE ROWNUM =1;
highestprice_items_cur highestprice_cur%ROWTYPE;
BEGIN
OPEN highestprice_cur;
LOOP
FETCH highestprice_cur INTO vprice, vcnt,vpartid;
EXIT
WHEN highestprice_cur%NOTFOUND;
IF vcnt > 1
THEN
DBMS_OUTPUT.put_line(
''There are ''
|| vcnt || '' items with the highest price: ''
|| TO_CHAR(vprice, ''fm

999,99.00''));
END IF;
END LOOP;
CLOSE highestprice_cur;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(''ERROR OTHERS '' || SQLERRM);
END;

这篇关于PL / SQL - 修改程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！