Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Only native data types are supported in PLSQL programming

create or replace package check1 is procedure p1(a1 integer); procedure p1(a1 number); end check1; / create or replace package body check1 is procedure p1(a1 integer) as begin dbms_output.put_line('Using Integer'); end p1; procedure p1(a1 number) as begin dbms_output.put_line('Using Number'); end p1; end; / --The above code gets compiled successfully. --But when you try to execute it .. begin check1.p1(8779); end; --See what happens.............. This concludes ONLY different native datatypes can be used in PLSQL OO implementation. Happy learning!!!!!
2 comments
Read more

Editor's Column - Traditional DBMS

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Post a Comment
Read more

PLSQL programming ( Learn by examples - Part 3 )

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...
Post a Comment
Read more