Data Retrieval statement using PL/SQL

Today we will know how to retrive data from Oracle database using PL/SQL.PL/SQL provide three ways to extract data from Oracle database.These ways are as follow:-

1. Data Retrieval Statement
2. PL/SQL Records
3. PL/SQL Tables

Data Retrieval Statement:-

It is basic way to extract data from database.In this way we store retrieval data to plsql variables.After that we print it.

Select Statement Syntax:-

SELECT SlectList                                                               

[INTO VariableList]

FROM TableList

WHERE [WhereClaus]

[ORDER by ColoumList]

Example:-

DECLARE

 v_empno number(5):=&employee_no;   --in v_empno i insert user input so i use this statement

 v_ename varchar2(30);

 v_job varchar(30);

 v_sal number(10,2);

 v_comm number(10,2);      --all are variableList

BEGIN

 select fname ||' '||lname "employee name",job,sal,comm into v_ename,v_job,v_sal,v_comm from emp where empno=v_empno;

DBMS_OUTPUT.PUT_LINE('The Name : ' || v_ename);

DBMS_OUTPUT.PUT_LINE('The Job : ' || v_job);

DBMS_OUTPUT.PUT_LINE('The Sal : ' || v_sal);

DBMS_OUTPUT.PUT_LINE('The commission : ' || NVL(TO_CHAR(v_comm),'Not Declared'));

END;

/

Explanation :extract column values from table and assign these values to variableList.These variables are printed by DBMS_OUTPUT.PUT_LINE() function.NVL(TO_CHAR(v_comm),'Not Declared) used for if comm column of 'emp' table is NULL then print 'not declared' as you shown in figure.If you want to print 0 then simply replace 'not declared' to 0 with single quote.