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.
Comments
Post a Comment