1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

The function returns only one value repeatedly

Discussion in 'SQL PL/SQL' started by duongthuan, Oct 6, 2017.

  1. duongthuan

    duongthuan Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    USA
    Hi all,

    I am not good in pl/sql and I know this code is not well structure but could just someone tell me what is wrong in this code? It returns just one value each time repeatedly instead of right values for each element. Where should I put return to have the right "area value" for my objects?

    Here is the code:

    "
    CREATE OR REPLACE FUNCTION AREA_CAL_1ST
    RETURN NUMBER
    IS


    -- constant declaration
    pi CONSTANT NUMBER := 3.141592654;
    -- other declarations
    P_ID NUMBER;--ID.PARAM%TYPE;
    P_Inner_boundary NUMBER;--Inner_boundary.PARAM%TYPE;
    P_Surface_Type VARCHAR2(4000);--Surface_Type.PARAM%TYPE;
    P_R_HEIGHT NUMBER(10,3);--R_HEIGHT.PARAM%TYPE;
    P_R_LENGTH NUMBER(10,3);--R_LENGTH.PARAM%TYPE;
    P_T_edgeA NUMBER(10,3);--T_edgeA.PARAM%TYPE;
    P_T_edgeB NUMBER(10,3);--T_edgeB.PARAM%TYPE;
    P_T_edgeC NUMBER(10,3);--T_edgeC.PARAM%TYPE;
    P_C_Central_ANGLE NUMBER(10,3);--C_Central_ANGLE.PARAM%TYPE;
    P_C_RADIUS NUMBER(10,3);--C_RADIUS.PARAM%TYPE;
    AREA_CAL NUMBER(10,3);
    p NUMBER(10,3);

    CURSOR param_cur
    IS
    SELECT "ID", "Inner_Boundary", "Surface_Type", "R_HEIGHT", "R_LENGTH", "T_edgeA", "T_edgeB", "T_edgeC", "C_Central_ANGLE", "C_RADIUS"
    FROM Param;

    BEGIN

    OPEN param_cur;
    LOOP
    FETCH param_cur into P_ID, P_Inner_boundary, P_Surface_Type, P_R_HEIGHT, P_R_LENGTH, P_T_edgeA, P_T_edgeB, P_T_edgeC, P_C_Central_ANGLE, P_C_RADIUS;

    EXIT WHEN param_cur%notfound;
    BEGIN
    CASE
    WHEN P_SURFACE_TYPE = 'Rectangle' THEN
    area_cal := P_R_HEIGHT * P_R_LENGTH;

    WHEN P_SURFACE_TYPE = 'Circle_segment' THEN
    area_cal := (P_C_Central_ANGLE * pi * POWER(P_C_RADIUS,2))/360;

    WHEN P_SURFACE_TYPE = 'Triangle' THEN
    P := (P_T_edgeA + P_T_edgeB + P_T_edgeC)/2;
    area_cal := SQRT(P * (P-P_T_edgeA) * (P-P_T_edgeB) * (P-P_T_edgeC));

    ELSE area_cal := 0;
    DBMS_OUTPUT.PUT_LINE('No area was calculated');
    END CASE;
    END;

    END LOOP;
    CLOSE param_cur;
    RETURN area_cal;

    END AREA_CAL_1ST;
    "

    Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,528
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Functions are supposed to return ONE value, based on the input parameter or parameters. Loops have no place in a function; they belong in procedures that are intended to process data in bulk. This is what you should be creating:

    Code (SQL):
    BING @ quanghoo > CREATE TABLE param(surface_type varchar2(50),
      2  r_height   NUMBER,
      3  r_length   NUMBER,
      4  t_edgea            NUMBER,
      5  t_edgeb            NUMBER,
      6  t_edgec            NUMBER,
      7  c_central_angle NUMBER,
      8  c_radius   NUMBER);

    TABLE created.

    BING @ quanghoo >
    BING @ quanghoo > INSERT ALL
      2  INTO param(surface_type, c_central_angle, c_radius)
      3  VALUES('Circle_segment', 45, 12)
      4  INTO param(surface_type, r_height, r_length)
      5  VALUES('Rectangle', 12, 24)
      6  INTO param(surface_type, t_edgea, t_edgeb, t_edgec)
      7  VALUES('Triangle',3,4,5)
      8  SELECT * FROM dual;

    3 ROWS created.

    BING @ quanghoo >
    BING @ quanghoo > commit;

    Commit complete.

    BING @ quanghoo >
    BING @ quanghoo > CREATE OR REPLACE FUNCTION AREA_CALC_F(p_surface_type IN varchar2, p_r_height IN NUMBER, p_r_length IN NUMBER, p_t_edgea IN NUMBER,
    p_t_edgeb IN NUMBER, p_t_edgec IN NUMBER, p_c_central_angle IN NUMBER, p_c_radius IN NUMBER)
      2  RETURN NUMBER
      3  IS
      4
      5  -- constant declaration
      6  pi CONSTANT NUMBER := 3.141592654;
      7  -- other declarations
      8  AREA_CAL NUMBER(10,3);
      9  p NUMBER(10,3);
    10
    11  BEGIN
    12  CASE
    13  WHEN UPPER(P_SURFACE_TYPE) = 'RECTANGLE' THEN
    14  area_cal := P_R_HEIGHT * P_R_LENGTH;
    15
    16  WHEN UPPER(P_SURFACE_TYPE) = 'CIRCLE_SEGMENT' THEN
    17  area_cal := (P_C_Central_ANGLE * pi * POWER(P_C_RADIUS,2))/360;
    18
    19  WHEN UPPER(P_SURFACE_TYPE) = 'TRIANGLE' THEN
    20  P := (P_T_edgeA + P_T_edgeB + P_T_edgeC)/2;
    21  area_cal := SQRT(P * (P-P_T_edgeA) * (P-P_T_edgeB) * (P-P_T_edgeC));
    22
    23  ELSE area_cal := 0;
    24  DBMS_OUTPUT.PUT_LINE('No area was calculated');
    25  END CASE;
    26
    27  RETURN area_cal;
    28
    29  END AREA_CALC_F;
    30  /

    FUNCTION created.

    BING @ quanghoo >
    BING @ quanghoo > SELECT surface_type, area_calc_f(surface_type, r_height, r_length, t_edgea, t_edgeb, t_edgec, c_central_angle, c_radius) area
      2  FROM param;

    SURFACE_TYPE                                             AREA
    -------------------------------------------------- ----------
    Circle_segment                                         56.549
    Rectangle                                                 288
    Triangle                                                    6

    BING @ quanghoo >
    You use a function to select from a table and process the results based on the columns selected, as shown in the example above. Notice that each surface type has its own area.