Home » PL/SQL

How to input from user in PL/SQL?

In this article we learn how to take input from user? Just like we use scanf function in C and cin in C++, We will learn how to do the same thing in PL/SQL?
Submitted by Yash Kumar, on October 09, 2017

Here is the syntax of taking input from the user:

<variablename>:=:<variablename>;

Just by writing only this statement we will able to take input from user.

Let's clear this concept by taking an example:

First write the given code in your SQL command prompt

declare
i integer;
j integer;
s integer;
begin
i:=:i;      -----observe this statement. This statement will tell the machine to take input of i through user.
j:=:j;      -----observe this statement. This statement will tell the machine to take input of j through user.
s:=i+j;
dbms_output.put_line('sum of '||i||' and '||j||' is '||s);     
end;

Note: || operator is used for concatenation of strings.

When you run this code, we will get a new window like this

Now, fill values like 20 for I and 30 for J and click on submit

We will get the following output:

sum of 20 and 30 is 50

Statement processed.

0.00 seconds

It's definitely easy to understand. Important thing is that the statement i:=:i and the statement j:=:j are used to take input through user as I have already discussed about its syntax <variablename>:=:<variablename>;