Recent Posts

Saturday, 9 July 2016

PL/SQL Basics

PL/SQL
1. It stands for procedural language/structure query language in SQL we can’t execute the same statement repeatedly to a particular no of times but where as in PL/SQL we can execute the same statement repeatedly because it supports looping mechanism.

2. In SQL we can’t execute more than one statement at a time. But where as in PL/SQL we can execute more than one statement concurrently.

3. PL/SQL program is a combination of procedural language statements and structure query language statements.

4. All PL statements are executed by PL engine where as all the SQL statements are executed by PL engine.

5. PL/SQL supports all the principles of procedure language such as procedural functions, control statements, conditional statements e.t.c., and also it supports some principles of OOPS.

6. PL/SQL is not a consecutive language and every statement of PL/SQL program should ends with ;(semicolon).

7. PL/SQL is the high performance transaction processing language.

Data types in PL/SQL
     The data types which are using in SQL same data types are supported in PL/SQL.

Operators in PL/SQL
     The operators which are using in SQL same operators are by PL/SQL except assignment operator.

Input statements in PL/SQL
1. There is no input statements in PL/SQL to input the values at run time.
2. If we want input values at run time then we use insertion operator (&).

Output statements in PL/SQL
Dbms_output.put_line()/ dbms_output.put()
     This is other statement in PL/SQL which is used to print the values on the standard output device.
Syntax
dbms_output.put_line(‘message’);
E.g
dbms_output.put_line(‘SRKREC’);
Syntax
dbms_output.put_line(‘message’||variable);
E.g
dbms_output.put_line(‘sum is ‘||C);
Syntax
dbms_output.put_line(Variable);
E.g
dbms_output.put_line(c);

Variable declaration in PL/SQL
Syntax
variable data type
E.g
a number(10);

Block
     Block is grouped code or set of statements, it is classified into two types.
1. Anonymous Blocks
* Anonymous blocks are those blocks which we can’t store permanently in database once the user logout from the database these blocks are completely destroyed.

* These blocks can’t have any prototype that is those blocks can be called as unnamed blocks.

2. Subprograms
* Sub programs are those blocks which we can store permanently in database so that, the user can access these blocks at a particular point of time.

* These blocks refer with same proper name or specific name that is so that these blocks as named PL/SQL.

Structure of anonymous blocks
Declare
Declaration of variables
Begin
   Statement 1;
   Statement 2;
   .
   .
   Statement n;
Exception
   Exception handling statements;
End;
     PL/SQL anonymous block contains four sections those are declare, begin, exception and end. Here declare and exception sections are optional.

Comments in PL/SQL
     -- it is single line comment in PL/SQL no multi line comments in PL/SQL.

Write a pl/sql program input any numbers and find out their sum.
declare
   a number:=&a;
   b number:=&b;
   c number;
begin 
   c:=a+b;
   dbms_output.put_line('Sum is'||c);
end;
Write a PL/SQL block input two numbers and interchange them.
declare
   a number:=&a;
   b number:=&b;
   c number;
begin
   c:=a;
   a:=b;
   b:=c;
   dbms_output.put_line('value of a is'||a);
   dbms_output.put_line('Value of b is'||b);
end;

No comments:

Post a Comment