- PL/SQL 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.
- 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.
- PL/SQL program is a combination of procedural language statements and structure query language statements.
- All PL statements are executed by PL engine where as all the SQL statements are executed by PL engine.
- 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.
- PL/SQL is not a consecutive language and every statement of PL/SQL program should ends with ;(semicolon).
- 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
- There is no input statements in PL/SQL to input the values at run time.
- If we want input values at run time then we use insertion operator(&).
Output statements in PL/SQL
This is other statement in PL/SQL which is used to print the values on the standard output device.
dbms_output.put_line(‘message’); dbms_output.put_line(‘message’||variable); dbms_output.put_line(Variable);
dbms_output.put_line(‘SRKREC’); dbms_output.put_line(‘sum is ‘||C); dbms_output.put_line(c);
Variable declaration in PL/SQL
variable data type
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.
- 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;