PL/SQL-CURSOR - 4 - Oracle Database

8,127 Expert 4TB

This thread contains some useful tips/samples regarding some advance concepts in cursors.

FEW MORE EXAMPLES
===================

Expand|Select|Wrap|Line Numbers

 
declare

er emp%rowtype;

cursor c1 is select * from emp;

begin

open c1;

loop

fetch c1 into er;

exit when c1%notfound;

if er.job='SALESMAN' then

dbms_output.put_line(er.empno||' '||er.ename||'  '||er.sal||'  '||er.sal*1.10);
 
elsif er.job='CLERK' then

dbms_output.put_line(er.empno||' '||er.ename||'  '||er.sal||'  '||er.sal*1.08);
 
else

dbms_output.put_line(er.empno||' '||er.ename||'  '||er.sal||'  '||'No ince req.');

end if;
 
end loop;

close c1;

end;

NOTE:--PLEASE TRY THE ABOVE CODE IN SCOTT SCHEMA

EXAMPLE #1
--------------------

Expand|Select|Wrap|Line Numbers

 
begin

--no need to open and close.

for dr in (select * from dept) loop

dbms_output.put_line(dr.deptno||'  '||dr.dname||'  '||dr.loc);

end loop;

end;

EXAMPLE #2
----------------------

Expand|Select|Wrap|Line Numbers

 
declare

--cursor is declared

cursor c1 is select * from dept;

-- a cur type variable is declared

dr c1%rowtype;

begin

-- open the cursor

open c1;

--fetch into the target variable

fetch c1 into dr;

-- check for existance of more dat in the cursor

while (c1%found=TRUE) loop

fetch c1 into dr;

dbms_output.put_line(dr.deptno||' '||dr.dname||'   '||dr.loc);

--end the loop

end loop;

--close the cursor.

close c1;

end;

SAMPLE PROGRAM TO SHOW USE OF RECORD TYPE IN CURSOR
-----------------------------------------------------------------------------------------------------------

Expand|Select|Wrap|Line Numbers

 
declare

TYPE MYTYPE IS RECORD

(

MENAME VARCHAR2(10),

MMGR NUMBER(4)

);

mm mytype;

CURSOR C1 IS

select ename,mgr from emp where deptno=20 and job='CLERK';

begin

open C1;

LOOP

FETCH C1 INTO mm.mename,mm.mmgr;

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(mm.mename || ' '|| mm.mmgr);

END LOOP;

CLOSE C1;

END;

Also Check PL/SQL-CURSOR - 5

Sep 17 '07 #1

Subscribe Post Reply

4646

by: cooldv | last post by:

i am running a website on Windows 2000 server with ASP 3 webpages and Access 2000 database. (with a hosting company) traffic is slow at this time but expect to grow. lately i have been reading...

ASP / Active Server Pages

SQL*Plus and Shell Programming

by: Peter | last post by:

I run most of my SQL scripts via kornshell on AIX. I use the "here-document" to run some of the smaller ones. Example: #!/bin/ksh # Analyze the table. sqlplus...

Oracle Database

SQL-2000 GUI

by: Dagwood | last post by:

Good morning: At least it's morning where I am. :) I have a rather newbie question I'm afraid. I have VisualStudio.NET, and have installed it along with SQL server. However I can't seem to...

Microsoft SQL Server

Installing SQL Server 2000 Personal Edition on Windows XP SP1

by: Ken Lindner | last post by:

I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...

Microsoft SQL Server

Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

by: Andreas Lauffer | last post by:

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...

Microsoft SQL Server

SQL Server 2000 Installation error

by: coosa | last post by:

Hi, I was installing SQL Server on my machine and during installation my PC freezed. It happens frequently on my machine. So i tried after restarting to install it again and since then i always...

Microsoft SQL Server

I cannot install SQL Server 2005 Express

by: Peter | last post by:

I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005...

Visual Basic .NET

Nested Looping SQL Querys

by: Fuzzydave | last post by:

I am back developing futher our Python/CGI based web application run by a Postgres DB and as per usual I am having some issues. It Involves a lot of Legacy code. All the actual SQL Querys are...

Python

Is SQL server not installed with VS2005

by: Developer | last post by:

Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can...

ASP.NET

Using SQL in Do Until Loop

by: dbrother | last post by:

Access 2003 Win XP Pro SP3 Using SQL /ADO Recordsets in a Do Loop Hello, I'm using a random number generator based on an integer input from a user from a form that will get X number of random...

Microsoft Access / VBA

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

PL/SQL-CURSOR - 4

Similar topics