PL/SQL in Oracle and basic Programs in PL/SQL #Diksha Oracle Training Centre

             PL/SQL in Oracle with basic Programs.

Hello Everyone,

Today I will discuss about PL/SQL in Oracle .In Previous Blog we discussed about cursor and anonymous block used in cursor as well as uses of %TYPE and %ROWTYPE attribute. Before starting this topic please go through my below blog.

https://dikshaoracletraining.blogspot.com/2020/10/cursor-and-types-of-cursor-in-oracle.html

https://dikshaoracletraining.blogspot.com/2020/11/benefits-of-type-and-rowtype-in-oracle.html

PL/SQL is basically a Procedural Language in SQL and in PL/SQL we use separate two parts first is Anonymous Block and second is Named Block. The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL relational database. PL/SQL high-performance transaction-processing language.PL/SQL is completely portable.  PL/SQL provides a built-in, interpreted and OS independent programming environment.  PL/SQL can directly be called from the command-line in SQL.  PL/SQL direct call any external programming language like Java ,C++ etc  to database.  Apart from Oracle, PL/SQL is available in other RDBMS like Sybase, DB2, Microsoft SQL Server etc.     Features of PL/SQL Follows object-oriented programming.  PL/SQL is integrated with SQL.  PL/SQL offers extensive error checking.  PL/SQL offers various type of data types.  PL/SQL offers a variety of programming structures.  PL/SQL supports structured programming through functions and procedures.  PL/SQL supports the development of web applications and server pages.     Main Advantages of PL/SQL  SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL blocks.  PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.  PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.  PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.  PL/SQL provides high security level and PL/SQL provides access to predefined SQL packages.  

  PL/SQL has two blocks

  1.     Anonymous Block (without name work on session only) 

  2.     Named Block  (Procedure, Functions, Packages etc which we can use anytime)    

PL/SQL block basic Syntax:  PL/SQL is a block structured language, below are the parts of block when we try to write any small piece of code.

  DECLARE     <declarations section>

  BEGIN     <executable command(s)>

 EXCEPTION     <exception handling> 

 END;

Note: Below is the Anonymous block where we not use any create statement so this syntax can be used in sessions only.

 

DECLARE

i NUMBER := 1;

BEGIN

LOOP

EXIT WHEN i>10;

DBMS_OUTPUT.PUT_LINE('WELCOME TO DIKSHA ORACLE TRAINING CENTRE');

i:= i+1;

END LOOP;

END;

/

 

Below is the output of above PL/SQL code.

 

 

For Printing 1 to 10 value in PL/SQL Anonymous Block we use below code.

 

DECLARE

i NUMBER := 1;

BEGIN

LOOP

EXIT WHEN i>10;

DBMS_OUTPUT.PUT_LINE(i);

i:= i+1;

END LOOP;

END;

/

 

Below is the output of above PL/SQL code.

 

DECLARE

v_empid number;

v_name  employee.name%type;

v_designation employee.designation%type;

begin

select emp_id , name , designation

into v_empid ,v_name , v_designation

from employee

where salary=27000;

dbms_output.put_line(v_empid||' '||v_name || ' '||v_designation);

end;

/

Here we not use loop so the above block will give only 1 records.

Below is the output of above code:

 

For getting all the records we need to use loop and below is the code:

 DECLARE

  CURSOR c1

  IS

    SELECT

        emp_id, designation

    FROM

        employee

    ORDER BY

        emp_id DESC;

BEGIN

  FOR i IN c1

  LOOP

    dbms_output.put_line( i.emp_id ||' ' || 'DOTC' || ' '||  i.designation );

  END LOOP;

END;

 

Below is the output of above PL/SQL anonymous code.

 

This Blog is important to write some small piece of code to start PL/SQL anonymous block .In next blog when we start  Named block like creating procedures , functions , packages etc it’s easy to understand all the  things like usage of cursors , %type , %rowtype  , exceptional handling etc . Later on we will use some dynamic SQL , Collection records in PL/SQL.    

Please read this blog carefully as this blog is important for writing PL/SQL block   so that in next blog you can easily understand to create some small procedures using cursors, exceptional handling etc and see the reusability of Procedures with various examples from PL/SQL.    

Thanks.