CODEWITHSUNDEEP
oracle-databaseplsql
Alex Fields
Alex Fields

Reputation: 67

asking for user input in PL/SQL

I am new to PL/SQL and I am stuck on some code. I am wanting to ask the user for a number and then I want to be able to use this number in an IF THEN statement to verify if the number is greater than or less than 20.

I am stuck on how to obtain user input and once I have it, I can store it and give it back to the user and verify its greater than or less than 20. 

DECLARE 
a number(2) := 10; 
BEGIN 
a:= 10; 
-- check the boolean condition using if statement  
IF( a < 20 ) THEN 
  -- if condition is true then print the following   
  dbms_output.put_line('a is less than 20 ' ); 
END IF; 
dbms_output.put_line('value of a is : ' || a); 
END; 
/

Upvotes: 0

Views: 3999

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65313

SQL> set serveroutput on; -- for "dbms_output.put_line" to take effect
SQL> DECLARE 
a number := &i_nr; -- there's no need to restrict a non-decimal numeric variable to a length
BEGIN 
--a:= 10; --no need this when initialization is in declaration section 
-- check the boolean condition using if statement  
IF( a < 20 ) THEN 
  -- if condition is true then print the following   
  dbms_output.put_line('a is less than 20 ' ); 
END IF; 
dbms_output.put_line('value of a is : ' || a); 
END; 
/
-- it prompts you for value of &i_nr "enter a numeric value, for example 10", for string values it must be in quotes '&i_str'

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142778

In SQLPlus, you'd use "&"; in my example, it is && so that I wouldn't have to enter the same value several times (i.e. every time "a" is referenced):

SQL> begin
  2    if &&a < 20 then
  3      dbms_output.put_line('a is less than 20');
  4    end if;
  5    dbms_output.put_line('value of a is: ' || &&a);
  6  end;
  7  /
Enter value for a: 5
old   2:   if &&a < 20 then
new   2:   if 5 < 20 then
old   5:   dbms_output.put_line('value of a is: ' || &&a);
new   5:   dbms_output.put_line('value of a is: ' || 5);
a is less than 20
value of a is: 5

PL/SQL procedure successfully completed.

SQL>

Though, I'd say that you'd rather create a procedure with an IN parameter, such as

SQL> create or replace procedure p_test (par_a in number) is
  2  begin
  3    if par_a < 20 then
  4       dbms_output.put_line('a is less than 20');
  5    end if;
  6    dbms_output.put_line('value of a is: ' || par_a);
  7  end;
  8  /

Procedure created.

SQL> exec p_test(15);
a is less than 20
value of a is: 15

PL/SQL procedure successfully completed.

SQL> exec p_test(34);
value of a is: 34

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions