PL / SQL practice programs

 

COGNIZANT Freshers  ORACLE Training @ Pune 2014

What is Embedded SQL in DBMS?

 When we talk about embedded SQL, it can be understood as small SQL queries put into high-level languages to get meaningful outputs.

While embedding SQL queries into other high-level languages, we need to make sure that we have a working database connector on our system.

By using the connectors we can easily run SQL queries on the existing databases in our RDBMS or we can even create new ones. Let us now dive deep into the embedded SQL concepts and learn more about it.

What is Embedded SQL?

As we have seen in our previous tutorials, SQL is known as the Structured Query Language. It is the language that we use to perform operations and transactions on the databases.

When we talk about industry-level applications we need properly connected systems which could draw data from the database and present to the user. In such cases, the embedded SQL comes to our rescue.

We embed SQL queries into high-level languages such that they can easily perform the logic part of our analysis.

Some of the prominent examples of languages with which we embed SQL are as follows:

• C++
• Java
• Python etc.

Why do we need Embedded SQL?

Embedded SQL gives us the freedom to use databases as and when required. Once the application we develop goes into the production mode several things need to be taken care of.

We need to take care of a thousand things out of which one major aspect is the problem of authorization and fetching and feeding of data into/from the database.

With the help of the embedding of queries, we can easily use the database without creating any bulky code. With the embedded SQL, we can create API’s which can easily fetch and feed data as and when required.

How to Embed SQL in High-Level Languages?

For using embedded SQL, we need some tools in each high-level language. In some cases, we have inbuilt libraries which provide us with the basic building block.

While in some cases we need to import or use some packages to perform the desired tasks.

For example, in Java, we need a connection class. We first create a connection by using the connection class and further we open the connection bypassing the required parameters to connect with the database.

Example: How to connect to a database (using JAVA).
Code[with embedded SQL]:

Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/DataFlair","user","root");

Statement statement = connection.createStatement();

Here, DataFlair is the name of the database and user is the username and root is the password.

Advantages of Embedded SQL

Some of the advantages of using SQL embedded in high-level languages are as follows:

• Helps to access databases from anywhere.
• Allows integrating authentication service for large scale applications.
• Provides extra security to database transactions.
• Avoids logical errors while performing transactions on our database.
• Makes it easy to integrate the frontend and the backend of our application.

Summary

SQL can be embedded in almost all high-level languages due to the vast support it has from almost all developers. Languages like C, C++, Java etc, support SQL integration.

Some languages like python have inbuilt libraries to integrate the database queries in the code. For python, we have the SQLite library which makes it easy to connect to the database using the embedding process.

Embedding queries sometimes creates problems as it would be clumpy and lead to processing errors if used in a large-scale application that is under production.

Use of embedded SQL should be done with care as a slight mistake could expose the critical data as well.

CURSOR

SQL Cursor

• A cursor is a private SQL work area.

• There are two types of cursors:

– Implicit cursors

– Explicit cursors

• The Oracle server uses implicit cursors to parse

and execute your SQL statements.

• Explicit cursor are explicitly declared by the

programmer.

Whenever you issue a SQL statement, the Oracle server opens an area of memory in which the

command is parsed and executed. This area is called a cursor.

When the executable part of a block issues a SQL statement, PL/SQL creates an implicit cursor, which

PL/SQL manages automatically. The programmer explicitly declares and names an explicit cursor.

There are four attributes available in PL/SQL that can be applied to cursors.

JDBC connection

ODBC	JDBC
ODBC Stands for Open Database Connectivity.	JDBC Stands for java database connectivity.
Introduced by Microsoft in 1992.	Introduced by SUN Micro Systems in 1997.
We can use ODBC for any language like C,C++,Java etc.	We can use JDBC only for Java languages.
We can choose ODBC only windows platform.	We can Use JDBC in any platform.
Mostly ODBC Driver developed in native languages like C,C++.	JDBC Stands for java database connectivity.
For Java applications it is not recommended to use ODBC because performance will be down due to internal conversion and applications will become platform Dependent.	For Java application it is highly recommended to use JDBC because there are no performance & platform dependent problem.
ODBC is procedural.	JDBC is object oriented.

code for JDBC connection to Oracle database :-

import java.sql.*;

class OracleCon{

public static void main(String args[]){

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con=DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery("select * from emp");

while(rs.next())

System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));

con.close();

}catch(Exception e){ System.out.println(e);}

}

}