PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language

Browse my blog to read the overview of PLSQL if you need it.


Anonymous block


.............
........... This is where you declare Variables that you are going to use it in PLSQL block
............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number.
Begins .....
.........
.........
.......... This is where you implement programming logic and get the desired outcome.
..........
..........
Ends.....

We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects)

Learn from examples

Example 1:

declare
var1_num number; --Declaring number type variable
var2_str varchar2;
begin
var1_num := 10; --Assigning 'Number' Value to the variable
var2_str := 'I am a string value';
end;

Example 2: Write anonymous block code in oracle to print your name, age, profession. Assign your DOB as Date type and find your age.

First, write down the logic :
1. Assign name to Char variable
2. Assign DOB to date variable
3. Find age
4 . Print name, age , profession


SET serveroutput ON
DECLARE
name VARCHAR2(40); --To assign name
profession VARCHAR2(40); --To assign profession
dob DATE; --To assign Date of birth as date
salary FLOAT; --To assign salary
age NUMBER; --to find age and assign it to number variable
BEGIN --Begin implementation part
--Implemenation section
name := 'Susil Kumar';
profession :='Software Professional';
dob := '13/mar/1986';
age := sysdate - dob;
salary := 10000.00;
--Printing the details
--To print , we make use of the Oracle supplied package DBMS_OUTPUT
dbms_output.put_line('My name is '||name||'.
I am a '||profession||'. I was born on '||dob||' and i am '||ROUND(age/365,0) ||' now.'||'My profession earns me '||salary||'.');
END;
/


Exercise:
1. Write PLSQL anonymous block to print the following message.
Today is _____(Today's date). The day is ____(Day).