CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperplsqldeveloper
tuneKawaii UwU
tuneKawaii UwU

Reputation: 73

Retrieve data by user input with a message (PL/SQL)

I want to how to retrieve data from the table I have added here by inserting C_Id as the user input with defined variables and exceptions. If any customer is not available it has to display a message showing "No customer found". Please help me to understand this one.

Thank you!

Upvotes: 1

Views: 565

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

If it is a stored procedure (should be; it accepts a parameter) and you just want to display what you found, then this might be one option:

create or replace procedure p_test (par_c_id in customer_details.c_id%type)
is
  l_row customer_details%rowtype;
begin
  select *
    into l_row
    from customer_details
    where c_id = par_c_id;
    
  dbms_output.put_line(l_row.customer_name ||', '||
                          l_row.address       ||', '||
                          to_char(l_row.date_of_joined, 'dd.mm.yyyy')
                         );
exception
  when no_data_found then
    dbms_output.put_line('No customer found');
end;
/

Then run it as

set serveroutput on
begin
  p_test(121);
end;
/

What does it do?

  • accepts a parameter
  • declares local variable which is equal to table's rowtype
  • select everything into the variable
  • using dbms_output.put_line, display elements you want
  • if nothing has been found, exception (no_data_found) is raised and handled in a way that you display appropriate message

Note that such an option works if tool you use (e.g. SQL*Plus, SQL Developer, TOAD, ...) allow displaying result of dbms_output.put_line. Otherwise, if you use e.g. Oracle Apex, procedure will still work, but you won't see anything.

Upvotes: 2

Related Questions