PL/SQL simple task-CodePudding

I want to create a procedure whith two arguments. It should check if the arguments are values of family table and if both are not the same value. I planned this code but I noted it doesn't work

create or replace procedure Compare(first_value values.value%type, second_value values.value%type)
as
    begin   
    
    if second_value not exists(select values from family) then
    
        dbms.output.put_line('The second values doesn't exist');
        
        else if first_value = second_value then
        
        dbms.output.put_line('Both values are the same');
        
        else
              
        dbms.output.put_line('Great Job');
                        
     end if;   
    end;
/

I'll appreciate any help.

CodePudding user response：

Here's one option.

SQL> set serveroutput on

Sample data:

SQL> select * from family;

C_VALUES
--------
Little
Foot

Procedure:

SQL> create or replace procedure p_compare
  2    (par_value_1 in family.c_values%type,
  3     par_value_2 in family.c_values%type
  4    )
  5  as
  6    l_cnt_1 number;
  7    l_cnt_2 number;
  8  begin
  9    select count(*)
 10      into l_cnt_1
 11      from family
 12      where c_values = par_value_1;
 13
 14    select count(*)
 15      into l_cnt_2
 16      from family
 17      where c_values = par_value_2;
 18
 19    if l_cnt_1 = 0 then
 20       dbms_output.put_line('The first value does not exist');
 21    elsif l_cnt_2 = 0 then
 22       dbms_output.put_line('The second value does not exist');
 23    elsif par_value_1 = par_value_2 then
 24       dbms_output.put_line('Both values are the same');
 25    else
 26       dbms_output.put_line('Great job');
 27    end if;
 28  end;
 29  /

Procedure created.

Testing:

SQL> exec p_compare('Little', 'Foot');
Great job

PL/SQL procedure successfully completed.

SQL> exec p_compare('Little', 'Little');
Both values are the same

PL/SQL procedure successfully completed.

SQL> exec p_compare('Big', 'Foot');
The first value does not exist

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Here is a not so well written, but somehow alternative take on the task:

CREATE OR REPLACE PROCEDURE COMPARE(FIRST_VALUE  VALUES.VALUE%TYPE,
                                    SECOND_VALUE VALUES.VALUE%TYPE)
AS
  l_message VARCHAR2(40);
BEGIN
  SELECT CASE WHEN test_val < 2               THEN 'The first value does not exist'
              WHEN test_val < 3               THEN 'The second value does not exist'
              WHEN first_value = second_value THEN 'Both values are the same'
              ELSE 'Great job'
         END
    INTO l_message    
    FROM (SELECT NVL(SUM(val),0) AS test_val
            FROM (SELECT 2 AS val
                    FROM family
                   WHERE val    = first_value
                     AND ROWNUM = 1
                   UNION ALL
                  SELECT 1 AS val
                    FROM family
                   WHERE val    = second_value
                     AND ROWNUM = 1));

  DBMS_OUTPUT.PUT_LINE(l_message);
END;
/