PL/SQL
Block Structure
Declare
//here variable declaration is to be done
Begin
//plsql body structure
Exception
//here all error can be handle
End;
Declare Block
-> It contains declaration statement used to declare variables.
So these variables get memory space from the database engine
based on their datatypes and size.
Syntax
Variable_name datatype (size);
Begin Block
-> Used to store values into the declared variables
by using assignment operator (:=) or
by using SELECT statement with INTO keyword
Method 1: By Using Assignment Operator (:=)
Syntax
Variable_name := Value;
Method 2: By Using SELECT statement with INTO keyword
Syntax
SELECT attb1, attb2,...,attbn
INTO var1, var2, ... , varn
From Table_name
Where <Condition>;
Output Statement
-> Used to display value of variables & normal messages.
Oracle provides a predefined output function as follows
Syntax
dbms_output.put_line ('Normal Message' or var_name);
Data Processing Statement
-> Any SQL query is known as data processing statement
Exception Block
User to display user friendly error messages instead of
system generated error messages
END;
-> Indicates end of program
/
-> Used to compile & execute the PL/SQL program in SQLPLUS enviroment
Why we use SET Serveroutput on ?
-> When you use the DBMS_OUTPUT.PUT_LINE procedure,
the procedure will write the passing string into the Oracle buffer.
In order to print the content of the Oracle buffer,
you should use the SET SERVEROUTPUT command to display
the content of the Oracle buffer into your screen.
Dynamic Variable declaration
1) Using %type
var_name1 table_name.attb1_name%type
var_name2 table_name.attb2_name%type
.
.
.
var_namen table_name.attbn_name%type
Assignment statement
SELECT attb1, attb2,...,attbn
INTO var1, var2, ... , varn
From Table_name
Where <Condition>;
2) Using %rowtype
var_name table_name%rowtype
Assignment statement
SELECT *
INTO Var_name
From Table_name
Where <Condition>;
While accessing in O/P statement use
var_name.attb_name