从Oracle 12c函数返回多个值 [英] Returning multiple values from an Oracle 12c function

问题描述

我正在Oracle 12c中编写一个函数(必须是一个函数)，该函数应返回3个值，order_line.o_id，在该函数中创建的变量total和一个我创建的discounted_amount变量在函数中创建.

I am writing a function in Oracle 12c (and it has to be a function) that should return 3 values, order_line.o_id, a variable total that I create in the function, and a discounted_amount variable that I create in the function.

我已经获得了创建Discounted_amount变量并将其返回的函数，但是我不确定如何获取它以返回其他两个值.

I have gotten my function to create the discounted_amount variable and return it but I am not sure how to get it to return these other two values.

CREATE OR replace FUNCTION DiscountsReport (input_o_id IN REAL) 
RETURN REAL 
IS 
  output_o_id      NUMBER; 
  total            REAL; 
  discounted_total REAL; 
  percent          REAL; 
BEGIN 
    output_o_id := input_o_id; 

    SELECT SUM(o.ol_quantity * o.ol_price) 
    INTO   total 
    FROM   order_line o 
    WHERE  o.o_id = input_o_id; 

    IF total > 100 THEN 
      percent := 0.1; 
    ELSIF total > 200 THEN 
      percent := 0.2; 
    ELSE 
      percent := 0.0; 
    END IF; 

    discounted_total := ( total * ( 1 - percent ) ); 

    RETURN discounted_total; 
END; 

推荐答案

创建新类型:

CREATE OR REPLACE TYPE new_type AS OBJECT(v1 type1, v2 type2, v3 type3);

并在RETURN之后使用它(调用结果output-类型为new_type).

and use it after RETURN (call the result output - its type is new_type).

您可以使用以下命令访问这些值:

You can access those values using:

output.v1
output.v2
output.v3

这篇关于从Oracle 12c函数返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！