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 |
Similar Reads
PL/SQL INSERT Statement The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
PL/SQL Injection PL/SQL injection is a security issue where attackers use harmful code to exploit weak spots in Oracle's PL/SQL applications. If user input isnât properly checked, attackers can access or change sensitive data and even take control of the system.In this article, we will explain what PL/SQL injection
6 min read
PL/SQL WHERE Clause The WHERE clause in PL/SQL is essential for filtering records based on specified conditions. It is used in SELECT, UPDATE, and DELETE statements to limit the rows affected or retrieved, allowing precise control over data manipulation and retrieval.In this article, We will learn about the WHERE Claus
3 min read
PL/SQL While Loop Oracle PL/SQL provides various loop structures that help developers execute a block of code multiple times based on certain conditions. The main loop structures include LOOP ... END LOOP, WHILE ... END LOOP, and FOR ... END LOOP. In this article, we will explore the WHILE loop in detail, including i
5 min read
PL/SQL GOTO Statement PL/SQL also known as Procedural Language/Structured Query Language, PL/SQL is a powerful programming language used in Oracle databases to do interaction with data. One of its features is the GOTO statement, which helps control how a program flows by allowing it to jump to specific statements within
5 min read