Mastering SQL for Data Science: A Comprehensive Guide Day-9

Mastering SQL for Data Science: A Comprehensive Guide Day-9

PL/SQL

PLSQL

 

    PL/SQL Programming language was developed by Oracle Corporation in the late of 1980s as procedural extension language for SQL and the Oracle relational database.

PL/SQL stands for “Procedural Language extensions to the Structured Query Language”.

SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS). PL/SQL is a standard and portable language for Oracle Database Development. PL/SQL is a highly structured and readable language. Besides, PL/SQL provides a more comprehensive programming language solution for building
mission –critical applications on Oracle Database.

PL/SQL is an embedded language. PL/SQL only can execute in an Oracle Database. It was not designed to use as a standalone language like java, C++ or C# etc ,which means we cannot develop PL/SQL program that runs on a system that does not have an Oracle Database.

v  Advantages of PL/SQL:

ü  SQL is the standard database language and PL/SQL is strongly integrated with SQL.

ü  PL/SQL allows sending an entire block of statements to the database at one time. This, in turn, reduces network traffic and provides higher performance for the applications.

ü  PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.

ü  PL/SQL saves time on design and debugging by strong features, such as exception handling, data hiding and object- oriented data types.

ü  Applications written in PL/SQL are fully portable with high security level.

ü  PL/SQL provides support for developing Web applications and server pages.

ü  PL/SQL provides access to predefined SQL packages.

v  PL/SQL block structure:

 PL/SQL code is grouped into structures called blocks. Every Programming environment allows the creation of structured, logical blocks of code that processes, which have to be applied to data. A single PL/SQL code block consists of a set of SQL statements clubbed together, and passed to the oracle engine entirely.

PLSQL Block

Declare

            <Declaration of local variables, constants, functions, procedures, cursors, exceptions …etc>

Begin

            <SQL statements>;

          <Other executable statements of PL>;

Exception

           < Exception Handling Messages>;

End;

/

E.g:

Declare

declare

          a number;

begin

          a:=&a;

          dbms_output.put_line(a);

end;

/

v  Data types in PL/SQL:

PL/SQL has two kinds of data types: Scalar and Composite.

·  Scalar data types: The Scalar types are types that store a single value such as number, Boolean, character, and datetime whereas the composite types are types that store multiple values, for example, record and collection.

ü  Boolean: Boolean data type can be used to store the values true, false or Null. 

ü  Binary_integer: Binary_integer is used to store signed integer. The range of the binary integer value is     .

ü  Number: It is same as SQL number data types. In addition to this it also includes ANSI standard types with which includes following data types:

Dec|Decimal

Int|Integer

Real

 ü  LOB: A company may decide that some comments about each of its various vendors must be stored along with their details. The lob types are used to store objects. A Large Object data types refer to large data items such as text, graphic images, video clips and sound waveforms.

   BLOB used to store large binary objects in the database. (8 to 128 TB)

   CLOB used to store large blocks of character data in the database. (8 to 128 TB)

·    Composite Data type:

ü  %type:

The %type attribute provides for further integration. PL/SQL can use the %type attribute to declare the variables based on the definitions of columns in a table. Hence, if a column’s attributes change, the variable’s attributes will change as well. This provides for data independence, reduces maintenance costs and allows program to adapt the changes made into the table.

Syntax: variable_name> <table_name>.<column_name>%type;

E.g: n emp.emp_no%type;

E.g:

declare

          n student.no%type;

          nm student.name%type;

begin

          n:=&n;

          select no,name

          into n,nm

          from student

          where no=n;

           dbms_output.put_line(n||‘ ‘ ||nm);

end;

/

ü  %rowtype: %type attribute is used to declare a variable’s data type as being equivalent to the specified column’s data type, while %rowtype attribute is used to declare composite variable that is equivalent to a row in the specified data. The composite variable is consists of column names and data types in the referenced table. The declaration of
%rowtype attribute with variable, which means variable inherit column and its data type information for all the columns of a table.

Syntax:
<variable_name> <table_name>%rowtype;

E.g: emp_rec emp%rowtype;

E.g:

declare

          trec temp%rowtype;

          n temp.no%type;

begin

          n:=&n;

          select no,name into trec from temp where no=n;

          dbms_output.put_line(trec.no||‘ ‘||trec.name);

end;

/

v  Control Structure:

ü  Conditional Statements

ü  Iterative Statements

·   Conditional Statements:

Within Pl/SQL block, selection/conditional control is used when the execution of a particular set of statement is based on specific condition. Sequence of statements can be executed based on some condition using if statement.

Syntax: If <condition> then

                        <action/statements>;

            Elsif

                        <action/statements>;

            End if;

 E.g:

declare

          n number;

begin

          n:=&n;

          if n<0 then

                   dbms_output.put_line(‘your no. is negative’);

          end if;

          if n>0 then

                   dbms_output.put_line(‘Your no. is positive’);

          end if;

          if n=0 then

                   dbms_output.put_line(‘your no. is zero’);

          end if;

end;

/       

 v  Iterative Statements:

 PL/SQL provides a facility for a executing statements repeatedly via loops. In PL/SQL we have three loops as follows to execute statements repetitively.

ü  Simple Loop

ü  While Loop

ü  For Loop

Let’s discuss every loop in detail;

 ü  Simple Loop:

In simple loop, the keyword loop should be placed before the first statement in the sequence and the keyword end loop should be written at the end of the sequence to end the loop.

Syntax:

Loop

      <sequence of statements>;

End
loop;

    E.g:

    declare

          i number;

          n number;

    begin

          n:=&n;

          i:=1;

          loop

                    dbms_output.put_line(i);

                    i:=i+1;

                    exit when i>n;

          end loop;

    end;

     /

 ü  While Loop:

    Syntax:

      While <condition>

      Loop

                  <sequence of statements>;

      End loop;

 Before each iteration of the loop, condition is evaluated. If it equivalents to true, sequence of statements are executed.  If condition evaluates to false or null, the loop is finished and control resumes after the end loop statement.

 The only difference between simple loop and while loop is simple execute first and then it will check the condition. So, simple loop execute at least once and in while loop first it will check condition and then execute the rest of the statements.

    E.g:

    declare

          n number;

          i number;

    begin

          n:=&n;

          i:=1;

          while i<=n

          loop

                    dbms_output.put_line(i);

                    i:=i+1;

          end loop;

    end;

/

ü  For Loop:

 For loop statement is best suitable when you want to execute a code for a known number of times rather than based on some other conditions.

 In this loop, the lower limit and upper limit will be specified and as long as the loop variable is in between this range, the loop will be executed. The loop variable is self incremental, so no explicit increment operation is needed in this loop. The sequence of statement contains all the code that needs to be executed. If the loop variable came out of the range, then control will exit from the loop.

The loop can be made to work in the reverse order by adding the keyword ‘Reverse’ before lower limit.

Syntax:

      For <loop_variable> in [reverse]
<lower_limit>..<upper_limit>

      Loop  

                  <sequence of
statements>;

      End loop;

E.g:

declare

          i number;

          n number;

begin

          n:=&n;      

          for i in 1..n    

          loop

                   dbms_output.put_line(i);

          end loop;

end;

/

 E.g:–write a program of for loop with using
reverse keyword

 declare

          i number;

          n number;

begin

          n:=&n;      

          for i in reverse 1..n  

          loop

                   dbms_output.put_line(i);

          end loop;

end;

/

Leave a Reply

Your email address will not be published. Required fields are marked *