英文:
how to fix this "Function created with compilation errors"
问题
创建或替换函数 invoice_total_price_function
(p_invoice_num IN invoice_line.invoice_num%TYPE)
返回 NUMBER
是
v_price book.price%TYPE :=0;
v_order_quantity invoice_line.order_quantity%TYPE :=0;
开始
选择 price, order_quantity
INTO v_price, v_order_quantity
FROM book, invoice_line
WHERE book.isbn_code = invoice_num.isbn_code
AND invoice_num = p_invoice_num;
返回 SUM(v_price * v_order_quantity);
结束 invoice_total_price_function;
希望找出所有发票行的总价格，汇总到单个发票中。
我如何知道应该编辑哪一行？谢谢
英文:
Create or replace FUNCTION invoice_total_price_function
(p_invoice_num IN invoice_line.invoice_num%TYPE)
RETURN NUMBER
IS
v_price book.price%TYPE :=0;
v_order_quantity invoice_line.order_quantity%TYPE :=0;
BEGIN
SELECT price, order_quantity
INTO v_price, v_order_quantity
FROM book, invoice_line
WHERE book.isbn_code = invoice_num.isbn_code
AND invoice_num = p_invoice_num;
RETURN SUM(v_price * v_order_quantity);
END invoice_total_price_function;
hoping to find out the total price of all the invoice line into a single invoice.
how can i see which line i should be edited? Thank you
答案1
得分: 0
- 你的
SUM放错地方了。如果你想要对多行进行聚合，请在查询中进行。 - 不要使用隐式连接。使用 ANSI 连接。
你的最终函数应该类似于这样：
Create or replace FUNCTION invoice_total_price_function
(p_invoice_num IN invoice_line.invoice_num%TYPE)
RETURN NUMBER
IS
v_total_price NUMBER :=0;
BEGIN
SELECT SUM(price * order_quantity)
INTO v_total_price
FROM book
JOIN invoice_line on (isbn_code = invoice_line.isbn_code)
WHERE invoice_line.invoice_num = p_invoice_num;
RETURN v_total_price;
END invoice_total_price_function;
请注意，我在这里假设 v_total_price 的类型是 NUMBER（因为这是函数设计的返回类型），但你应该根据你的需要进行检查和适应。
英文:
- Your
SUMis out of place. If you want aggregation of multiple rows - do it in your query. - Don't use implicit joins. Use ANSI join.
Your final function should look something like this:
Create or replace FUNCTION invoice_total_price_function
(p_invoice_num IN invoice_line.invoice_num%TYPE)
RETURN NUMBER
IS
v_total_price NUMBER :=0;
BEGIN
SELECT SUM(price * order_quantity)
INTO v_total_price
FROM book
JOIN invoice_line on (isbn_code = invoice_num.isbn_code)
WHERE invoice_num = p_invoice_num;
RETURN v_total_price;
END invoice_total_price_function;
Please note, that here I assumed that type of v_total_price would be NUMBER (since that's what functions designed to return), but you should check and suite it to your needs.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论