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

Abstract

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

1. 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
   
   
2. 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.
   

   Begin
   SQL and PL/SQL Statements
   Exception
   Trap errors while executing SQL and PL/SQL statements
   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  /*   */
   

   /*l_count:=3;
   DBMS_OUTPUT.PUT_LINE(l_count); -- is used to print the output to the screen
   EXCEPTION
   WHEN OTHERS THEN NULL
   */

   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:
   

   XE =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = Developer-VAIO)(PORT = 1521)) –local db
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = XE) //service name
   )
   )

   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.

Read more articles on Databases:

• Introduction To Graph Databases
• Getting Started With LiteDB
• Learn MongoDB