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 |
Last Updated :
03 Oct, 2018
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...