Updated April 5, 2023
Definition of PL/SQL Queries
PL/SQL provides the different queries, or we can say commands to the user. Basically, PL/SQL is similar to the SQL language. But PL/SQL introduces the different methods that consist of the block structure, and it is helpful for well suitable development. PL/SQL also provides the variable in which we can perform the variable deceleration as well as we can assign the name as well. PL/SQL structure divides into different sections, and we can write the different queries into an execution section as per user requirement. We can perform the update, create, insert and delete, etc., as per requirement.
List of all PL/SQL Queries
Now let’s see different types of queries in PL/SQL as follows.
1. Create definition query:
Variables declared in any code block can be subjected to constraints. A constraint is a condition that needs to be applied to a certain variable. Constant and non-null constraints are the most often utilized constraints. Constants are used to ensure that a value does not change after it is given for the first time. Not null ensures that the variable has a value at all times. An error will occur if any value attempts to assign a null value. Unique, check, main key, foreign key, and other restrictions are among the others.
Syntax:
BEGIN
create table specified table name (required colm name 1specify data type(size), required colm name 2specify data type(size),….required colm name N specify data type(size));
END;Explanation
In the above syntax, we use create table statement to create a new table; here, the specified table name means the actual table name that we need to create. Inside the bracket, we can write different column names as per our requirement with different data types as shown.
Examples
create table studentA(stud_id number(10) not null, stud_name varchar2(30) not null, stud_city varchar2(30));Explanation
By using create table statement, we created a new table name student A, with different attributes and different data types. The final output of the above statement we illustrated by using the below screenshot is as follows.
2. Data Insertion query:
This is the second query, or we can say that command in PL/SQL. By using this command, we can insert the different records into the specified table that we want. When we need to insert the records into the table, we must require the table name, column name, and values for a column.
Syntax:
BEGIN
insert into specific table name(colm 1, colm 2, ……colm N) values(col value1, colm value 2,….colm value 3);
END;Explanation
In the above syntax, we use to insert into a statement to insert the records into the specified table, here insert into keyword is mandatory, specified table name means actual table name, and inside the bracket, we need to provide the column names and finally, we need to provide the different values with respect the column name as per our requirement as shown in the above syntax.
Example
insert into studentA(stud_id, stud_name, stud_city) values(101,'Jenny','Mumbai');
insert into studentA(stud_id, stud_name, stud_city) values(102,'Johan','Mumbai');
insert into studentA(stud_id, stud_name, stud_city) values(103,'Pooja','London');
insert into studentA(stud_id, stud_name, stud_city) values(104,'Sameer','London');
insert into studentA(stud_id, stud_name, stud_city) values(105,'Rohit', 'London');
select * from studentA;Explanation
By using the above statement, we inserted five records as shown in the below screenshot as follows.
3. PL/SQL Update Query:
In PL/SQL procedure, we can easily update the records by using an update query or command. It is useful to update any value from the specified table as per our requirements. It must require the table name, column name, and new value for that column.
Syntax:
BEGIN
update specific table name set colm name 1 = colm value 1, colm name 2 = colm value 2, ……colm name N = colm value N where [specified condition];
END;Explanation
In the above syntax, we use an update query to update the records from the specified table; here, the specified table name means actual name, and after that, we need to use the set keyword as shown. By using update query, we can update more than one column from a specified table, but one more important point here, without the “where” keyword, we are not able to perform the update query, so for that reason, we must specify the condition inside the where clause as per our requirement.
Example
update studentA set stud_city='Pune' where stud_id=101;Explanation
In the above example, we use the update command; here, we need to update the city of Jenny Pune instead of Mumbai by using stud_id. One more thing is that here we update only a single column. The final output of the above statement we illustrated by using the below screenshot is as follows.
4. PL/SQL Deletion Query:
PL/SQL also provides the delete query to the user, in which we can easily delete the particular records from the specified table by providing the where clause as per our requirement.
Syntax:
BEGIN
delete from specified table name where [specified condition] ;
END;Explanation
Syntax of delete query is very simple as shown in above syntax, here we specified table name means actual table name that we already created, and inside the where clause we need to specify the condition to delete the particular records from the specified table.
5. PL/SQL Select:
PL/SQL provides the selection data query to the user, in which we can fetch the records from the table. It is a very simple PL/SQL query statement.
Syntax:
BEGIN
select * from specified table name; or
END;Explanation
Basically, there are two ways to perform the select query in PL/SQL, as shown in the above syntax. In the first syntax, we can fetch all records from the specified table. 6. PL/SQL exception handling:
In PL/SQL, we can also perform exception handling to identify the error condition that means we can easily handle the error, and it also gives the user-defined message as per requirement.
Syntax:
BEGIN
Expression
EXCEPTION
Message;
END;Explanation
In the above syntax, we wrote the exception section separately, as shown in the above syntax.
When an expression is not executed, and it shows the error message that we wrote in the exception section as shown.
7. Arithmetic Query:
Normally all arithmetic operators are supported to the PL/SQL, such as addition, subtraction, multiplication, and divide.
Syntax:
select * from specified table name where [specified condition];Explanation
In the above syntax, we use a select statement with specified table names with the specified arithmetic operators.
PL/SQL also provides some intermediate Query to the user as follows.
1. Currval and Nextval: It is used to generate the sequential number in increment order.
2. Rowid: It is used to return the rowid of any specified table.
3. Rownum: It displays the row from a specified table in which row may get selected.
Similarly, we have Comparison operators, Set Operator, %ISOPEN, Taking input from the user, Index-By table, Calling a Function, and %ROWCOUNT.
Conclusion
We hope from this article you learn PL/SQL queries. From the above article, we have learned the basic syntax of all queries, and we also see different examples of the queries. From this article, we learned how and when we use PL/SQL queries.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL Queries” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
