PL/SQL | User Input

Prerequisite – PL/SQL Introduction
In PL/SQL, user can be prompted to input a value using & character. & can be used to prompt input for different data types. Consider, following table:


Table: GFG

id author likes
1 sam 10
2 maria 30
3 ria 40



Following queries will create a new table named GFG in the database.

SQL> create table GFG (id number(4), author varchar2(50), 
                                          likes number(4)) 
Table created.
SQL> insert into GFG values(1, 'sam', 10);
1 row created.
SQL> insert into GFG values(2, 'maria', 30);
1 row created.
SQL> insert into GFG values(3, 'ria', 40);
1 row created. 
SQL> select * from GFG;

id author likes
1 sam 10
2 maria 30
3 ria 40

1. Numeric value –
& is used to input numeric values from the user.

Syntax:

&value 

Example-1: Consider, the table GFG. Let us select a record with a given id. (Here, id is a numeric value)

SQL> select * from GFG where id=&id;
Enter value for id: 2
old 1: select * from GFG where id=&id
new 1: select * from GFG where id=2 

id author likes
2 maria 30

Example-2: Let us update a record with a given id. (Here, id is a numeric value)

SQL> update GFG set likes=50 where id=&id;
Enter value for id: 1
old 1: update GFG set likes=50 where id=&id
new 1: update GFG set likes=50 where id=1
1 row updated. 
SQL> select * from GFG; 



id author likes
1 sam 50
2 maria 30
3 ria 40

2. Text Value –
& can also be used to input text values from the user.

Syntax:

'&value' 

Example-1: Consider, the table GFG. Let us select a record with a given author. (Here, author is a text value)

SQL> select * from GFG where author='&author';
Enter value for author: maria
old 1: select * from GFG where author='&author'
new 1: select * from GFG where author='maria' 

id author likes
2 maria 30

Example-2: Let us update a record with a given author. (Here, author is a text value)

SQL> update GFG set likes=10 where author='&author';
Enter value for author: sam
old 1: update GFG set likes=10 where author='&author'
new 1: update GFG set likes=10 where author='sam' 
1 row updated. 
SQL> select * from GFG; 

id author likes
1 sam 10
2 maria 30
3 ria 40



My Personal Notes arrow_drop_up

A keen and enthusiastic learnerfull stack webdeveloper fascinated by data structure and algorithms

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


2


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.