Cursor variables, PL-SQL Programming

Assignment Help:

Cursor Variables

Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. The Cursor variables are true PL/SQL variables, to which you can assign new values and can pass to subprograms stored in an Oracle database. This gives you a convenient way and more flexibility to centralize data retrieval.

Normally, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following process opens the cursor variable generic_cv for the chosen query:

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade; END IF;

Attributes

The PL/SQL variables and cursors have attributes that are properties that let you reference the datatype and structure of an item without repeating its definition. The Database columns and tables have same attributes that you can use to ease maintenance. A percent sign (%) serves as the attribute indicator.

%TYPE

 

The %TYPE attribute provides the datatype of a variable or database column. This is principally useful when declaring variables that will hold database values. For example, suppose there is a column named title in a table named books. To declare a variable named my_title  which has the same datatype as column title, use dot notation and the %TYPE   attribute, as shown:

my_title books.title%TYPE;

Declaring my_title with %TYPE has two benefits. First, you do not require knowing the exact datatype of the title. Second, if you change the database definition of title (make it a big character string for example), the datatype of my_title changes consequently at run time.

%ROWTYPE

The PL/SQL, records are used to group data. A record consists of a number of related fields in which the data values can be stored. The %ROWTYPE attribute gives a record type that shows a row in a table. The record can store a whole row of data selected from the table or fetched from a cursor or cursor variable.

The Columns in a row and corresponding fields in a record have similar names and datatypes. In the illustration below, you declare a record named dept_rec. Its fields have similar names and datatypes as the columns in the dept table.

DECLARE

dept_rec dept%ROWTYPE;            -- declare record variable

You use dot notation to reference the fields, as the example below shows:

my_deptno := dept_rec.deptno;

If you declare a cursor which retrieves the job title, last name, salary and hire date of an employee, you can use %ROWTYPE  to declare a record that stores similar information, as shown:

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE;                               

-- declare record variable that represents

-- a row fetched from the emp table

When you execute the statement

FETCH c1 INTO emp_rec;

The value in the ename  column of the emp table is assigned to the ename field of emp_rec, the value in the sal   column is assigned to the sal field, and so on. Figure represents how the result might appear.

787_cursor variables.png

Figure: %ROWTYPE Record emp_rec


Related Discussions:- Cursor variables

Closing a cursor - explicit cursor, Closing a Cursor The CLOSE stateme...

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Information hiding in pl/sql, Information Hiding   With the informatio...

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

Using raise_application_error - user-defined exceptions, Using raise_applic...

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

Data types, Datatypes Every constant and variable has a datatype that s...

Datatypes Every constant and variable has a datatype that specifies the storage format, constraints, and the valid range of values. The PL/SQL gives a variety of predefined dat

Effects of null for union - sql, Effects of NULL for union - SQL The ...

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

EXCEPTION handling, set serveroutput on declare a number(5); b numb...

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Effects of null operator, Effects of NULL Operator As a general rule-b...

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

Cursor for loops, Cursor FOR Loops In most cases that need an explicit ...

Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

I want to build website for funding, Project Description: I want to rebu...

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd