CODEWITHSUNDEEP
sqloracle-databaseplsql
Sam
Sam

Reputation: 227

PL/SQL Procedure with SELECT return value

New to PL/SQL and I've tried to mess around with this and figure it out, but at a loss. Hoping for you guy's expertise and guidance.

Here's the code I have, hopefully you can kind of see where I am trying to get to. I know I need a cursor or a loop, maybe to get this to work properly. However, I am not sure where placement goes and how to get this to work.

Any help is appreciated.

create or replace
procedure customer_credit(cust_ID IN NUMBER) AS
    credit_num NUMBER;
    begin
       select CUSTOMER_ID, CREDIT_LIMIT from OE.CUSTOMERS WHERE CUSTOMER_ID=cust_ID;
       dbms_output.put_line('Credit Limit = ', || credit_num);
end;
/
exec customer_credit(145);

Upvotes: 1

Views: 21920

Answers (5)

Nikhil
Nikhil

Reputation: 3950

this will work :

create or replace
procedure customer_credit(cust_ID IN NUMBER) AS
    credit_num NUMBER;
    begin
       select CREDIT_LIMIT into credit_num from OE.CUSTOMERS WHERE CUSTOMER_ID=cust_ID;
       dbms_output.put_line('Credit Limit = ', || credit_num);
end;
/
exec customer_credit(145);

Upvotes: 0

Abdul M
Abdul M

Reputation: 1

I believe Your Procedure is compiled with warnings, you've declared the variable credit_num, but are not passing any value into this variable.(So How Can This Variable will return Something if it doesn't have anything.)

If you want to get the CREDIT_LIMIT from Your Table Then You should Write Your code using Select Into for ex:-

select CREDIT_LIMIT 
into credit_num 
from OE.CUSTOMERS 
where CUSTOMER_ID=cust_id;

Just change your select statement and you're good to go with your Program.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You may use a Cursor, alternatively :

create or replace procedure customer_credit( cust_ID IN NUMBER) as
begin
  for c in ( select CUSTOMER_ID, CREDIT_LIMIT from CUSTOMERS WHERE CUSTOMER_ID=cust_ID )
  loop
    dbms_output.put_line('Credit Limit = '|| c.CREDIT_LIMIT);
  end loop;
end;

By the way, there's a typo (comma) which should be removed inside dbms_output.put_line() clause. As @OldProgrammer stated, set serveroutput on should be issued to see the results .

Upvotes: 0

PaW
PaW

Reputation: 669

If you want the value to be returned, it is better to define this as a FUNCTION and then invoke the function in your code. This will allow you to use it in both PL/SQL procedures and SELECT statements. You should also use exception handlers to handle situations. I have modified your example to illustrate the changes needed:

create or replace
function customer_credit(cust_ID IN NUMBER)
return number
IS
    credit_num NUMBER;
begin
       select CREDIT_LIMIT
       into   credit_num
       from OE.CUSTOMERS WHERE CUSTOMER_ID=cust_ID;
       dbms_output.put_line('Credit Limit = ', || credit_num);
       return(credit_num);
exception
  when NO_DATA_FOUND then
    return(0);
end;
/
select customer_credit(145) from dual;

Hope that helps.

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

There are a variety of options. One is to use a SELECT... INTO:

create or replace
procedure customer_credit(cust_ID IN NUMBER) AS
    credit_num NUMBER;
    begin
       select CREDIT_LIMIT 
       into credit_num
       from OE.CUSTOMERS WHERE CUSTOMER_ID=cust_ID;
       dbms_output.put_line('Credit Limit = ', || credit_num);
end;

It the row is not found, this will generate an exception. You can fix that either by adding an exception handler, or use an explicit open/fetch/close cursor method. If you are executing the procedure from sqlplus, you need to first call

set serveroutput on

to see the results of dbms_output.

Upvotes: 1

Related Questions