Post
Ask Question

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.

    learn

    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.

    Architecture
                                                                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.
    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:
    Anonymous Block Structure
                                                 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.

    Sql developer Homepage
                                           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.

    Connections
    Figure 4:Connections

    Right Click on Connection

     • New add new connection

    Connection
                                     Figure 5:Adding New Connection to the Sql Developer
    connection Name
                                                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:

    connection
    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:

    Output
                                                          Figure 8:Enabling DBMS Output.

    Click on Dbms Output.

    option
                                                       Figure 9:DBMS Output option
    DBMS Output Window will pop up as shown below,

    output
                                              Figure 10:DBMS OUTPUT windows

    Click on + sign Tab,

    Output
                       Figure 11:Enable Dbms Output
    Specify your DB for which you want to enable.

    output
                                           Figure 12:Attaching DBMS OUTPUT to the connection.

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

    run
                                                   Figure 13:Running PL/SQL Anonymous block

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

    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:


Similar Articles