PLSQL programming ( Learn by examples - Part 2 )

/***
Learn by examples:
Write a PLSQL block to use Select , insert , delete , update statements in it

Purpose : Use of DML statements in Oracle PLSQL
SQL is the cursor placed in memory during DML operation in PLSQL block.
rowcount is SQL's attribute to know how many rows affected of that particular DML query.....

***/
clear screen
set serveroutput on
DECLARE
ENAME VARCHAR2(20);
BEGIN
SELECT EMP_NAME INTO ENAME FROM EMPLOYEE WHERE EMP_ID='E1'; --Syntax for Select statement in PLSQL is different. It has INTO clause to save values in variable
DBMS_OUTPUT.PUT_LINE('EMP NAME '||ENAME);
DBMS_OUTPUT.PUT_LINE('No of rows selected out of SELECT statement '||SQL%rowcount);

delete from employee
where dept_no >25;

DBMS_OUTPUT.PUT_LINE('No of rows deleted out of DELETE statement '||SQL%rowcount);

update employee
set dept_no = 10
where dept_no <=5;

DBMS_OUTPUT.PUT_LINE('No of rows updated out of UPDATE statement '||SQL%rowcount);


insert into employee values('E1141','XXXXX',6);

DBMS_OUTPUT.PUT_LINE('No of rows inserted out of INSERT statement '||SQL%rowcount);

commit;
END;

Output:

EMP NAME Susil Kumar1
No of rows selected out of SELECT statement 1
No of rows deleted out of DELETE statement 0
No of rows updated out of UPDATE statement 0
No of rows inserted out of INSERT statement 1