CRUD operation in PL/SQL:

In this tutorial, we are getting to find out how to use SQL in PL/SQL. SQL is that the actual component that takes care of fetching and updating of knowledge within the database whereas PL/SQL is that the component that processes these data. Further, during this article, we’ll also discuss the way to combine the SQL within the PL/SQL block.

 

 

 

 

 

DML Transactions in PL/SQL

DML stands for Data Manipulation Language. These statements are mainly wont to perform the manipulation activity. It deals with the below operations.

• Data Insertion

• Data Update

• Data Deletion

• Data Selection

In PL/SQL, we will do the info manipulation only by using the SQL commands.

Data Insertion

In PL/SQL, we will insert the info into any table using the SQL command INSERT INTO. This command will take the table name, table column and column values because the input and insert the worth within the base table.

SYNTEX	BEGIN  INSERT INTO <table_name>(<column1 >,<column2>,…<column_n>)     VALUES(<valuel><value2>,…:<value_n>);END;
EXAMPLE

 

• The above example shows the INSERT INTO command. The table name and values are a compulsory fields, whereas column names aren’t mandatory if the insert statements have values for all the column of the table.
• The keyword ‘VALUES’ is mandatory if the values are given separately as shown above.

 

The INSERT command also can take the values directly from another table using ‘SELECT’ statement instead of giving the values for every column. Through ‘SELECT’ statement, we will insert as many rows because the base table contains.

SYNTEX	BEGIN  INSERT INTO <table_name>(<columnl>,<column2>,…,<column_n>)     SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;END
EXAMPLE

 

• The above example shows the INSERT INTO command that takes the values directly from the using the SELECT command.
• The keyword ‘VALUES’ should not be present in this case as the values are not given separately.

 

Data Selection:

Data projection/fetching means to retrieve the specified data from the database table. We can do it by using the command ‘SELECT’ with ‘INTO’ clause. The ‘SELECT’ command will fetch the values and ‘INTO’ clause will assign these values to the local variable of the PL/SQL block.

 

Below are the points that require to be considered in ‘SELECT’ statement.

 

1. ‘SELECT’ statement should return just one record while using ‘INTO’ clause together variable can hold just one value. If the ‘SELECT’ statement returns quite one value than ‘TOO_MANY_ROWS’ exception are going to be raised.
2. ‘SELECT’ statement will assign the worth to the variable within the ‘INTO’ clause, so it must get a minimum of one record from the table to populate the worth . If it didn’t get any record, then the exception ‘NO_DATA_FOUND’ is raised.
3. The number of columns and their datatype in ‘SELECT’ clause should match with the amount of variables and their datatypes within the ‘INTO’ clause.
4. The values are fetched and populated within the same order as mentioned within the statement.
5. ‘WHERE’ clause is optional that permits to having more restriction on the records that are getting to be fetched.
6. ‘SELECT’ statement are often utilized in the ‘WHERE’ condition of other DML statements to define the values of the conditions.
7. The ‘SELECT’ statement when using ‘INSERT’, ‘UPDATE’, ‘DELETE’ statements shouldn’t have ‘INTO’ clause because it won’t populate any variable in these cases.

 

SYNTEX	BEGIN  SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n>    FROM <table_name>   WHERE <condition to fetch the required records>;END;
EXAMPLE

 

• The above syntax shows the SELECT-INTO command. The keyword ‘FROM’ is mandatory that identifies the table name from which the info must be fetched.
• ‘WHERE’ clause is optional. If this clause isn’t given, then the info from the whole table are going to be fetched.

 

Data Update:

Data update simply means an update of the worth of any column within the table. This can be done using ‘UPDATE’ statement. This statement takes the table name, column name and value because the input and updates the info .

SYNTEX	BEGIN    UPDATE <table_name>  SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n>   WHERE <condition that uniquely identifies the record that needs to be update>; END;
EXAMPLE

 

1. The above syntax shows the UPDATE. The keyword ‘SET’ instruct that PL/SQL engine to update the worth of the column with the worth given.
2. ‘WHERE’ clause is optional. If this clause isn’t given, then the worth of the mentioned column within the entire table are going to be updated.

 

Data Deletion:

Data deletion means to delete one full record from the database table. The ‘DELETE’ command is used for this purpose.

SYNTEX	BEGIN  DELETE  FROM  <table_name>  WHERE <condition that uniquely identifies the record that needs to be update>; END;
EXAMPLE

 

1. The above syntax shows the DELETE command. The keyword ‘FROM’ is optional and with or without ‘FROM’ clause the command behaves within the same way.
2. ‘WHERE’ clause is optional. If this clause isn’t given, then the whole table are going to be deleted.