PL/SQL normal declaration

***********************************************
To see the output of a PL/SQL program output
***********************************************
set serveroutput on

An Complete Pl/SQl program with 3 block
DECLARE
v_StudentID NUMBER(5) := 7369; -- Numeric variable initialized
-- to 10,000
v_FirstName VARCHAR2(20); -- Variable length character string
-- with maximum length of 20
BEGIN
/* Start of executable section */
-- Retrieve first name of student with ID 10,000
SELECT ename
INTO v_FirstName
FROM emp
WHERE empno=v_StudentID;
dbms_output.put_line (v_Firstname);
EXCEPTION
/* Start of exception section */
WHEN NO_DATA_FOUND THEN
-- Handle the error condition
dbms_output.put_line ('There is an error');
END;


****************************************************

Using BIND Variable

****************************************************
set serveroutput on
VARIABLE g_salary NUMBER
BEGIN
SELECT sal
INTO :g_salary
FROM emp
WHERE ename = 'SMITH';
END;
/
PRINT g_salary

******************************************************

Using Non PL/SQL variable

******************************************************
set serveroutput on
VARIABLE g_monthly_sal NUMBER
DEFINE p_annual_sal = 50000
SET VERIFY OFF
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
:g_monthly_sal := v_sal/12;
END;
/
PRINT g_monthly_sal
*****************************************************

****************************************************

DECLARATION OF VARIABLE

****************************************************
set serveroutput on buffer 25600000
DECLARE
a varchar2(30):='Arka Bhattacharya';
b number(3):=10;
c date:= '15-sep-1999';
C1 DATE := SYSDATE + 7;
d NUMBER(6) DEFAULT 100;
e VARCHAR2(30) NOT NULL := 'Oxford';
f char:='P';
g long:=3200;
h BINARY_INTEGER:=1;
I CONSTANT NUMBER(3,2) := 8.25;
J BOOLEAN NOT NULL := TRUE;
K emp.ename%TYPE;
l b%TYPE:=10;
m boolean := FALSE;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
end;
--------------------------------------------------------------------

****************************************************

DETAILED EXAMPLE
****************************************************

DECLARE
a varchar2(20):='Arka Bhattacharya';
b number(3):=10;
c date:= '15-sep-1999';
C1 DATE := SYSDATE + 7;
d NUMBER(6) DEFAULT 100;
e VARCHAR2(30) NOT NULL := 'Oxford';
f char:='P';
g long:=3200;
h BINARY_INTEGER:=1;
I CONSTANT NUMBER(3,2) := 8.25;
K emp.ename%TYPE:='SMITH';
l b%TYPE:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(c1);
DBMS_OUTPUT.PUT_LINE(d);
DBMS_OUTPUT.PUT_LINE(e);
DBMS_OUTPUT.PUT_LINE(f);
DBMS_OUTPUT.PUT_LINE(g);
DBMS_OUTPUT.PUT_LINE(h);
DBMS_OUTPUT.PUT_LINE(i);
DBMS_OUTPUT.PUT_LINE(K);
DBMS_OUTPUT.PUT_LINE(L);

end;
*******************************************************