To Study About Cursor and to Execute Using PL/SQL Programs

EX:NO: CURSOR

DATE :

AIM:

To study about cursor and to execute using PL/SQL programs.

CURSOR-TECHNIQUE:

Oracle allocates an area of memory known as context area for the processing of SQL statements. The context area contains the information including the number of rows processed by the statements, pointer to the parsed representation of the statement. A cursor is a pointer to the context area.

The syntax is,

DECLARE

CURSOR<cursor name>IS<query>;

<local declaration>;

BEGIN

OPEN<cursor name>INTO<column name>;

LOOP

FETCH<cursor name>INTO<column name>;

EXIT WHEN CURSOR NAME<attributes>;

END LOOP;

CLOSE<cursor name>;

END;

The attributes are,

%notfound

This attribute indicates whether fetch statement returns row from the active set. If the last fetch fails to return a row, then it is evaluated to true else it evaluates to false.

%found

It evaluates to true if the last fetch statement succeeds in returning a row. It is set to false if the last fetch command failed because no more rows are available.

%rowcount

It returns the number of rows fetched.

%isopen

If cursor is already open, it is evaluated to true, else it evaluates to false.

QUERIES:

1.Create table student(id,name,m1,m2,m3) and insert values into the table.

2.Find the total and average for the list of students using SQL cursor program a and print their id,name,total and average.

3.Create table itemIid,name,rate) and insert values into the table.

4.Update the rate field by adding Rs.100% in item table where id is equal to 5 or 3 using SQL cursor program.

5.Write the cursor program to count the number of rows fetched during the selection of records from item table.

1.create table stud(id number,name varchar(10),m1 number,m2 number,m3 number);

Table created.

Insert into stud values(&id,’&name’,&m1,&m2,&m3);

Enter the value for id:35

Enter the value for name:malini

Enter the value for m1:100

Enter the value for m2:99

Enter the value for m3:99

1 row created.

2.SQL>declare

Cursor c is select * from stud;

T int;

A int;

X c% rowtype;

Begin

Open c;

Loop

Fetch c into x;

Exit when c%notfound;

T:=x.m1+x.m2+x.m3;

A:=t/3;

Dbms_output.put_line(‘id=’||x.id); Dbms_output.put_line(‘name=’||x.name);

Dbms_output.put_line(‘total=’||t Dbms_output.put_line(‘average=’||a);

End loop;

Close c;

End;

OUTPUT:

Id=35

Name=malini

Total=298

Average=99

PL/SQL procedure successfully completed.

3.create table item(id number,name varchar(10),rate number);

Table created.

Insert into item values(&id,’&name’,&rate);

Enter value for id:35

Enter value for name:aa

Enter value for rate:100

1 row created.

4.select * from item;

ID NAME RATE

------

35 aa 100

14 bb 209

SQL>declare

Cursor c is select * from item;

A c%rowtype;

Begin

Open c;

Fetch c into a;

Update item set rate=rate+100;

Where id=5 or id=35;

Dbms_output.put_line(‘table updated’);

Close c;

End;

Table updated

PL/SQL procedure successfully completed.

SQL>select * from item;

ID NAME RATE

------

35 aa 200

14 bb 209

5.SQL>declare

Cursor c is select * from item;

X c%rowtype;

Begin

Open c;

Loop

Fetch c into x;

Exit when c%notfound;

Endloop;

Dbms_output.put_line(‘no.of.rows=’||c%rowcount);

Close c;

End;

OUTPUT:

No.of.rows=2

PL/SQL procedure successfully completed.

RESULT:

Thus the cursor was studied and queries were executed using SQL cursor.

Ex.No:JOINS AND CONSTRAINTS

Date:

Aim:

To relate the data through join operators and guard data through constraints.

Theory:

Sometimes we have to select data from two or more tables to make tables our result complete,at that time we have to use joins.
Tables in a database can be related to each other with keys.A primary key is a column with unique value for each row.The purpose is to bind data together across tables without repeating all of the data in every table.
In the ‘employees’ table below the ‘emp_id’ column is a primary key,means that no two rows can have the same emp_id.It distinguishes two persons even if they have the same name.
‘prod_id’ column is the primary key of the orders table.
The emp_id column in the orders table is used to refer the persons in the employees table without using their name.

Categories of joins:

Natural or inner join
Outer join
Left outer join
Right outer join
Full outer join

Natural or inner join:

It is a binary operation that allows us to combine certain selection & a Cartesian product into one operation.It is denoted ‘join’symbol.ie.it will return all rows from both table where there is a match by removing duplicates.

Syntax:

SELECT field1,field2,field3

FROM first_table

INNER JOIN second_table

ON first_table.key field=second_table.foreign key field

Left outer join:

It takes all tuples in the left relation that did not match with any tuples in the right relation pads the value with null values for all other attributes from right relation and adds them to the result of natural join.

Syntax:

SELECT field1,field2,field3

FROM first_table

LEFT OUTER JOIN second_table

ON first_table.key field=second_table.foreign key field.

Right outer join:

It takes all tuples in the right relation that did not match with any tuples in the left relation pads the value with null values for all other attributes from left relation and adds them to the result of natural join.

Syntax:

SELECT field1,field2,field3

FROM first_table

RIGHT OUTER JOIN second_table

ON first_table.key field=second_table.foreign key field.

CONSTRAINTS

It ensures that the change made by the database by an authorized user does not result in the condition of data consistency.

It guards against the accident damage to the data.

Not null
Unique constraints
Check

Similar to the primary key,but it contains the null value.We can restrict the null value by giving not null and unique constraints specified at end of the location.

JOINS:

Questions:

1) who has ordered a product & what did they order?

2) list all employees & their order if any.

3) list all orders & who has ordered-if any.

4) who ordered a printer?

CONSTRAINTS:

Questions:

Create table staff containing the fields,staffname, staffid,staffdepartment, staffdegree with not null constraints for the staffname ,set the constraints for staffdegree(bachelor,master,doctorate),set staffid as primary key.

OUTPUT:

1)Normal query:

Sql>select employee.e_name,order1.pr_name from employee,order1 where employee.emp_id=order1.emp_id;

E_NAME PR_NAME

Ramu printer

Harry chair

Harry table

Using join:

Sql>select e_name from employee inner join order1 on employee.emp_id=order1.emp_id;

E_NAME

Ramu

Harry

2)Sql>select employee.e_name,order1.pr_name from employee left join order1 on employee.emp_id=order1.emp_id;

E_NAME PR_NAME

Ramu printer

Harry chair

Harry table

Lucy

Peter

3) Sql>select employee.e_name,order1.pr_name from employee right join order1 on employee.emp_id=order1.emp_id;

E_NAME PR_NAME

Ramu printer

Harry chair

Harry table

4) Normal query:

Sql>select employee.e_name,order1.pr_name from employee,order1 where employee.emp_id=order1.emp_id and order1.pr_name=’printer’;

E_NAME PR_NAME

Ramu printer

Using inner join:

Sql>select employee.e_name,order1.pr_name from employee,order1 where employee.emp_id=order1.emp_id and order1.pr_name=’printer’;

E_NAME PR_NAME

Ramu printer

Constraints:

1)create table staff(staff_id number(10) not null,staff_name varchar(10) not null,dept varchar(10) not null, degree varchar(10) not null,primary key (staff_id), unique(staff_name),check(degree in(‘bachelor’,’master’,’doctrate’)));

table created.

Sql>insert into staff values(&staff_id,’&staff_name’,’&dept’,’&degree’);

Enter value for staff_id:467

Enter value for staff_name:nicky

Enter value for dept:cse

Enter value for degree:BE

Old 1:insert into staff values(&staff_id,’&staff_name’,’&dept’,’&degree’);

New 1:insert into staff values(467,’nicky’,’cse’,’BE’);

Error at line 1:

ORA-02290:check constraint (cs06035.sys-c005597)violated.

Result:

Thus relating two tables using Join is performed successfully and data’s are guarded using constraints.

Ex.No:VIEWS

AIM:

To view virtual relations using views.

THEORY:

View is a virtual table based on the result set of a select statement. A view contains rows and columns just like a table. The fields in a view are fields from one or more real tables in a database. We can add SQL functions WHERE &JOIN statements to a view & present the data as if the data were coming from a single table.

NOTE:

The database design & structure will not be affected by the functions where or join statements in a view.

A viewcould be used inside a query, a stored procedure or from inside another view by adding functions, joins,etc…

SYNTAX:

CREATE VIEW VIEW_NAME AS

SELECT COLUMN_NAME(S)

FROM TABLE_NAME

WHERE CONDITION

NOTE:

The database does not store the view data. The data engine recreates the data using the view SELECT statement,every time a user queries a view.

QUESTIONS:

Create view named ALL_CUSTOMER which shows virtual relation about the customer who have account, loan, or both.
Find ALL_CUSTOMER of perryridge branch.
Create view named LOAN_BRANCH containing the details branch_name & loanno.
Write query to insert a new record(“perryridge”,”L-307”)
Display the content of LOAN table &LOAN_BRANCH.

OUTPUT:

sql>create view all_customer as select br_name,cust_name from depositer,account where depositer.acc_no=acc.account_no union select br_name, cust_name from borrower,loan where borrower.loan_no=loan.loan_no;

View created.

sql>select cust_name from all customers where br_name=’perryridge’;

Cust_name

Adams

Hayes

sql>create view loan_branch as select br_name,loan_no from loan;

View created.

sql>insert into loan_branch values(‘perryridge’,’L-35’);

1 row created.

sql>select * from loan;

LOANBR_NAMEAMT

L-35perryridge

L-11roundhill900

L-14downtown1500

L-15perryridge1500

L-16perryridge1300

L-17downtown1000

L-23redwood2000

L-93nassaw500

RESULT:

Thus the use of views commands in SQL were studied and executed.

EX.NO:

PL/SQL

DATE:

AIM:

To study about PL/SQL and to execute simple PL/SQL blocks.

TECHNIQUE:

1.PL/SQL is a structured language.

2.PL/SQL blocks are implemented using anonymous blocks, procedures and functions.

3.Anonymous blocks are standalone, they neither take input parameters nor return values and can be nested within another PL/SQL block.

SYNTAX:

DECLARE

BEGIN

executable statements>

EXCEPTION

M<exception handlers>

END;

CONDITIONAL CONTROL:

Three types of IF statements exist in PL/SQL.

SIMPLE IF:

IF <Boolean expression>THEN

END IF;

IF-ELSE:

IF <Boolean expression>THEN

ELSE

END IF;

ELSE IF Ladder:

IF <Boolean expression>THEN

ELSE IF <alternate Boolean expression>THEN

ELSE

END IF;

ITERATIVE CONTROL:

SIMPLE LOOPS:

LOOP

END LOOP;

FOR LOOPS:

FOR loop counter IN [REVERSE] lower_bound..upper_bound LOOP

END LOOP;

QUERIES:

Write a PL/SQL program to find if a given number is even or odd.

Write a PL/SQL program to find if the given number is a 1 digit ,2 digit or 3 digit number.

Write a PL/SQL program to find the biggest of 3 numbers.

Write PL/SQL program to print the given number in reverse order.

Write a PL/SQL program to find the sum of first 100 numbers

Write a PL/SQL program to find the sum of series using while.

Write a PL/SQL program to find the sum of series using for.

PL/SQL program to print a string in line order.

Write a PL/SQl program to select the salary for a particul;ar employee from the employee table and display it.

10. Write a PL/SQL program to do all the following:

i) To select the salary for a particular employee from the employee(E_name,E_no,Sal)table.

ii) Check if the salary is greater than 5000.It true insert the salary into salary(sal)table and display inserted message.

11. Write a PL/SQL program to do all the following:

i)To select the salary for a particular employee from the employee(E_name,E_no,Sal)table.

ii)Check the salary range.It salary lesser than 2000 update employee table

salary by adding 400.

iii)If salary greater than or equal to 2000 and less than 5000 update employee table salary by adding 400.

iv)If salary greater than or equal to 5000 update employee table salary by adding 600 and display updated message.

To find if a given number is even or odd.

set serveroutput on

declare

a number;

begin

a:=&a;

if(a mod==0)then

dbms_output.put_line(‘a is even’||a);

else

dbms_output.put_line|(‘a is odd’||a);

end if;

end;

OUTPUT:

Enter the value of a:4

a:=&a a:=4

4 is even

2. To find if the given number is a 1 digit ,2 digit or 3 digit number.

set serveroutput on

declare

a number;

begin

a:=&a;

if(a<10)then

dbms_output.put_line(‘the digit is single’);

else if(a>10 and a<100)then

dbms_output.put_line(‘two digit number’);

else if(a>100 and a<1000)then

dbms_output.put_line(‘three digit number’);

end if;

end;

OUTPUT:

Enter the value of a:35

A:=35

Two digit number

3.To find the biggest of 3 numbers.

set serveroutput on

declare

a number;

b number;

c number;

begin

a:=&a;

b:=&b;

c:=&c;

if(a>b) and (a>c)then

dbms_output.put_line(‘a is greater’);

else if(b>a) and (b>c)then

dbms_output.put_line(‘b is greater’);

else

dbms_output.put_line(‘c is greater’);

end if;

end;

OUTPUT:

Enter the value for a:20

Enter the value for b:30

Enter the value for c:100

C is greater

4.To print the given number in reverse order.

set serveroutput on

declare

given number varchar(5):=’1234’;

str_length number(2);

inverted_number varchar(5);

begin

str_length:=length(given_number);

for cntr in reverse l..str_length

loop

inverted|_number:=inverted_number||substr(given_number,cntr,l);

end loop;

dbms_output,put_line(‘the given no is’||given_number);

dbms_output.put_line(‘the inverted number is’||inverted_number);

end;

OUTPUT:

The given number is: 1234

The inverted number is:4321

5.To find the sum of first 100 numbers.

set serveroutput on

declare

a number;

s1 number default 0;

begin

a:=1;

loop

s1:=s1+a;

exit when(a=100);

a:=a+1;

end loop;

dbms_output.put_line(‘sum between 1 to 100 is’||s1);

end;

OUTPUT:

Sum between 1 to 100 is 5050

6.To find the sum of series using while.

set serveroutput on

declare

n int;

i int:=0;

tot int:=0;

begin

n:=&n;

while(i<=n)

loop

tot:=i+tot;

i:=i+1;

end loop;

dbms_output.put_line(‘the sum is’||tot);

end;

OUTPUT:

Enter value for n:3

the sum is 6

7.Write a PL/SQL program to find the sum of series using for.

set serveroutput on

declare

n int;

i int:=0;

tot int:=0;

begin

n:=&n;

for I in 0..n

loop

tot:=i+tot

end loop

dbms_output.put_line(‘the sum is’||tot);

end;

OUTPUT:

Enter value for n:4

The sum is 10

8.PL/SQL program to print a string in line order.

set serveroutput on

declare

i int:=1;

name varchar(10);

begin

name:=’&name’;

for I in 1..10

loop

dbms_output.put_line(substr(name,I,1));

end loop;

end;

OUTPUT:

Enter value for name:abishek

9.Program to select salary of employee from table and display:

Create table employee (e_name varcher(5),e_no number(5),salary number);

Table created

Insert into employee values(‘malini’,1,3500);

1 row inserted.

E_name E_no Salary

Malini 1 3500

Rani 2 8500

Mani 3 9800

declare

no employee.eno%type;

sal employee.salary%type;

begin

no:=&no;

select salary into sal from employee where eno=no;

dbms_output.put_line(‘The salary is ’||sal);

end;

Output:

Enter the value for no:2

The salary is 8500

10.Program to insert salary into a table if it is greater than 2000:

Create table salary (sal salary(7));

Table created.

Declare

no employee.eno%type;

sal employee.salary%type;

begin

no:=&no;

select salary into sal from employee where eno=no;

if sal>2000 then insert into salary values(sal);

dbms_output.put_line(‘salary inserted’);

else

dbms_output.put_line(‘salary less than 2000 and so not inserted’);

end if;

end;

Output:

Salary table before execution

Sal

Enter the values for no:2

Sal;ary inserted

After execution

Sal

8500

11.program for salary updation:

declare

sal employee.salary.salary%type;

no employee.eno%type;

begin

no:=&no;

select salary into sal from employee where eno=no;

if(sal>2000)then

update employee set salary=sal+200 whereeno=no;

dbms_output.put_line(‘salary updated by 200’);

elseif(sql>=2000 and sal<5000)then

update employee set salary=sal+400 where eno=no;

dbms_output.put_line(‘updated by 400’);

else

update employee set salary=set+600 where eno=no;

end if;

end;

Output:

Enter the value:3

Salary updated by 200

Ename No Salary

Malini 1 3500

Rani 2 8500

Mani 3 10000

Result:

Thus the PL/SQL programs are executed and the output is verified.

EX.NO:

Relating Two tables using DML commands

DATE:

AIM:

To study and work the queries which are used to relate two tables.

QUERIES:

UNION:

This command is used to combine all the records from the specified tables.It removes the duplication of tables.

EXAMPLE:

SQL>select customer_name from depositer union select customer_name from borrower;

Output:

CUSTOMER_NAME

------

Rani

Kani

Kala

Raju

Sara

Anitha

UNION ALL:

This command is used to combine all the records from the tables without removing the duplicates from the table

EXAMPLE:

SQL>select customer_name from depositer union all select customer_name from borrower;

Output:

CUSTOMER_NAME

------

Rani

Kani

Kala

Raju

Sara

Anitha

Polly

Edmud

Sheela

RELATION OPERATION:

select all customer who have loan from the bank .find their name, loan _no,loan_amount.

SQL>select customer_name,borrower,loan_no,amount from borrower,loan where borrower,loan where borrower loan_no=loan,loan_no;

Output:

CUSTOMER_NAME LOAN NO AMOUNT

------

Kala 4567 29000

Anitha 1289 125000

Find average amount from the table loan where the branchname is tpr.

SQL>select avg(amount)from loan where branch_name=’tpr’;

AVG(amount)

------

4902500

GROUP BY:

SQL>select branch_name,avg(amount)from loan group by branch_name.

BRANCH_NAME AVG(AMOUNT)

------

tpr 392500

htr 270000

trt 89000

typ 320000

NESTED SUBQUERIES:

Find the customer who are borrower from the bank and who appears in

list of account holders obtained in sub query.

SQL>select distinct name from borrower where name in (select name

from depositer);

Output:

customer_name

------

hema

rani

Find the namename of customer who have loan at the bank and whose name are neither smith or johns.

SQL>select distinct name from borrower where name not in (select name from depositor);

customer_name

------

ramu

polly

edmund

INTERSECTION:

SQL>select customer_name from depositor intersect select customer_name from loan;

Output :

CUSTOMER_NAME

------

Hema

Rani

RESULT:

Thus the queries to relate two tables are executed successfully.

EX.NO:

USING SQL PLUS FUNCTION

DATE:

AIM:

SQL*plus provides specialized function to perform operations using data manipulation commands.A function takes one or more arguments and return a value.

The SQL*PLUS functions are broadly classified into 2 types

Single Row functions
Group functions

Single Row functions:

It is a scalar function and return only one value for every row queried in the table.

These function can appear in a select command and can be included in the ‘where’clause.