Learn Basics Of Oracle PL/SQL From Scratch In 7 Days - Day One

Saillesh Pawar
8y
12.7k
0
3

Article

Abstract

This article is based on learning the basics of Oracle and SQL concepts in order to be an Oracle PL/SQL developer.

Introduction

After great feedback from the C# Corner community users for Learn Tiny Bit of C# in seven days I am starting a new article series on learning the basics of PL/SQL in 7 days.

What we will do here

I hope you all have abasic understanding of SQL queries because it is necessary to have a basic knowledge of SQL before diving into the PL/SQL.

Agenda for Day 1

1. Understanding the features of PL/SQL.
2. Understanding the PL/SQL Block structure.
3. Recognizing various PL/SQL types, variables and constants.
4. Creating an Anonymous PL/SQL Block.
5. Printing value of a variable using Anonymous Block.

1.1 PL/SQL

• PL/SQL stands for “Procedural language Extension to SQL”.

• PL/SQL is Oracle Corporation’s standard data access language for relational database.

• PL/SQL allow us to integrate procedural constructs with SQL (i.e. with the help of PL/SQL we can apply programming logic, loops and provide other constructs). Pl/SQL has many features and error handling mechanism giving us the control for business execution.

1.2 Needs of PL/SQL

Pl/SQL extends SQL by adding powers found in other procedural languages, such as,

• Variables and data types.
• Control structure such as if -then -else, loops.
• Procedures and functions.
• Working with Object Types (Object help us model real world objects).
• PL/SQL improves the performance when multiple SQL statements are to be executed to avoid unnecessary server trips.
• Program that contain multiple SQL statements requires multiple call to database resulting significant network and performance overhead.
• With the help of PL/SQL we can send a block of statements at one time to Oracle.

Figure 1: Architecture

1.3 Advantages of PL/SQL

• Tight Integration with SQL
• Better Performance
• Higher Productivity
• Full Portability
• Tight Security
• Support for Object Oriented Programming

1.4 Tools

You can download Oracle Express free tool from the following link as well as we will be using SQL developer which is a free IDE for Oracle.

• Download Index - Oracle Software
• Download SQL Developer
Anonymous block

Is the basic unit of the PL/SQL program, it allows us to combine a bunch of SQL statements. We can brief up the anonymous block like a simple program i.e. piece of instructions.

Structure of Anonymous block

Declare Variables, cursor, user-defined exceptions, etc.
1. Begin
2. SQL and PL/SQL Statements
3. Exception
4. Trap errors while executing SQL and PL/SQL statements
5. End;
Note: Begin and End are compulsory statements.

Sample:

Figure 2: Anonymous Block Structure

In Anonymous Block Declaration and Exception section are optional. They are executed only once.

2.1 Comments

As we know, good documentation of code is great for a developer it makes it easy to understand code. So, comments are always important.

There are two types of comments in PL/SQL,

• Single Line Comments: The single comments are mentioned using --
• Multi Line Comments: The multi-line comments can be mentioned using /* */
1. /*l_count:=3;
2. DBMS_OUTPUT.PUT_LINE(l_count); -- is used to print the output to the screen
3. EXCEPTION
4. WHEN OTHERS THEN NULL
5. */
Let us jump to SQL Developer for executing the block step by step.

                                       Figure 3 : Sql developer Homepage

Once you open the SQL developer for the first time, we need to add connection of the db to which we want to connect.

Figure 4:Connections

Right Click on Connection

• New add new connection

                                 Figure 5:Adding New Connection to the Sql Developer

                                            Figure 6:Providing connection Name of the DB

As you can see in the following figure 6.

• ConnectionName: The name of the connection you want to give.
• UserName and Password: DB UserName and Password

• TNS:

I have used TNS Option and it means it will take the connection from the TnsName.ora file. Once you install the oracle software, search for the installed directory and go to folder ~/product/11.2.0/server/network/ADMIN.

• tnsnames.ora file will be present over the location, You can set your desired connection with IP details and service name as shown below:
1. XE =
2. (DESCRIPTION =
3. (ADDRESS = (PROTOCOL = TCP)(HOST = Developer-VAIO)(PORT = 1521)) –local db
4. (CONNECT_DATA =
5. (SERVER = DEDICATED)
6. (SERVICE_NAME = XE) //service name
7. )
8. )
Once you input your UserName and Password correctly connection will be added as shown below:

Figure 7:Connected to DB with connection name localDb

Let’s run our anonymous block and check its output as shown below. In order to check the output we need to enable DBMS_OUTPUT in SQL Developer as shown below:

Click on View:

                                                      Figure 8:Enabling DBMS Output.

Click on Dbms Output.

                                                   Figure 9:DBMS Output option

DBMS Output Window will pop up as shown below,

                                          Figure 10:DBMS OUTPUT windows

Click on + sign Tab,

                   Figure 11:Enable Dbms Output

Specify your DB for which you want to enable.

                                     Figure 12:Attaching DBMS OUTPUT to the connection.

Let’s run our first PL/SQL Anonymous block of code,

                                               Figure 13:Running PL/SQL Anonymous block

As soon as we run our PL/SQL statement we will get the following output:

                                          Figure 14:Output of PL/SQL Anonymous block

Script Output: It is like a compiler windows and tells if there is any error or not, if there is no error in your block it means the PL/SQL procedure successfully completed.

Worksheet: PL/SQL has worksheet where we write our DB related queries, PL/SQL command etc. We can have multiple worksheets in SQL developer.

Single line Comments: You can see use single line comments --.

Null: Null is the keyword in PL/SQL which means null.