Premium Essay

Cursors in Pl/Sql

In: Computers and Technology

Submitted By tduck0612
Words 2868
Pages 12
SQL Cursor
A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers.
Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.
To write the explicit cursor, please refer to the following example. Note that a cursor definition can array a number of arguments.
For example, DECLARE
CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';
BEGIN
FOR rec_ac IN csr_ac ('LE')
LOOP
DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);
END LOOP ;
CLOSE csr_ac;
END;
/
Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

SQL> DECLARE

2 CURSOR csr_ac (p_name VARCHAR2) IS

3 SELECT empno, ename, sal

4 FROM emp

5

6 WHERE ename LIKE '%SMITH%';

7

8 v_a emp.empno%TYPE;

9 v_b emp.ename%TYPE;

10 v_c emp.sal%TYPE;

11

12 BEGIN

13 OPEN csr_ac('');

14 LOOP

15 FETCH csr_ac INTO v_a, v_b, v_c;

16 EXIT WHEN csr_ac%NOTFOUND;

17

18 DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

19

20 END LOOP;

21 CLOSE csr_ac;

22 END;

23

24

Similar Documents

Premium Essay

Biodata

...Flat files (delimited & fixed width) and Oracle. * Used the SQL Loader for extracting the data from Flat Files into Oracle. * Experience in PL/SQL programming, Developing Packages, stored procedures, Functions and Triggers. * Experience in developing business applications using Oracle 11g/10g/9i, Oracle Forms10g/9i, Reports10g/9i, SQL, PL/SQL, SQL*Loader and Open Interface. * Expertise in design, development of end user screens and reports using Oracle Developer/2000 (Forms, Reports) and other front-end tools. * Expert in writing Cursors and Handling Exceptions while developing the applications. * Experienced in Project planning and scheduling, System design, Functional Specification, Design specification, Coding and System test plan. * Data cleansing experience using PL/SQL. SQL coding and UNIX. * Using Informatica Designer designed and developed Source Entities for Oracle, COBOL files and Target warehouse Entity for Oracle. * Involved in preparing Test Plans, Unit Testing, System Integration Testing, Implementation and Maintenance. * Extracted the data from different sources like COBOL Files, Flat files (delimited & fixed width) and Oracle. * Used the SQL Loader for extracting the data from Flat Files into Oracle. * Experience in PL/SQL programming, Developing Packages, stored procedures, Functions and...

Words: 622 - Pages: 3

Premium Essay

Introduction

...Cursors A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position. The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1. 1) DECLARE /* Output variables to hold the result of the query: */ 2) a T1.e%TYPE; 3) b T1.f%TYPE; /* Cursor declaration: */ 4) CURSOR T1Cursor IS 5) SELECT e, f 6) FROM T1 7) WHERE e < f 8) FOR UPDATE; 9) BEGIN 10) OPEN T1Cursor; 11) LOOP /* Retrieve each row of the result of the above query into PL/SQL variables: */ 12) FETCH T1Cursor INTO a, b; /* If there are no more rows to fetch, exit the loop: */ 13) EXIT WHEN T1Cursor%NOTFOUND; /* Delete the current tuple: */ 14) DELETE FROM T1 WHERE CURRENT OF T1Cursor; /* Insert the reverse tuple: */ 15) INSERT INTO T1 VALUES(b, a); 16) END LOOP; /* Free cursor used by the...

Words: 583 - Pages: 3

Free Essay

Computers

...Oracle9i: Program with PL/SQL Instructor Guide • Volume 2 40054GC11 Production 1.1 October 2001 D34008 Authors Nagavalli Pataballa Priya Nathan Copyright © Oracle Corporation, 1999, 2000, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this...

Words: 41259 - Pages: 166

Premium Essay

Cscc 640

...executes a dynamic SQL statement or an anonymous PL/SQL block. ____ 2. The %TYPE feature allows you to anchor variable declarations to a column in a table. ____ 3. COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. ____ 4. The only required sections of a PL/SQL block are BEGIN and END. ____ 5. The EXCEPTION handling section is an optional section in a PL/SQL block. ____ 6. The keyword DEFAULT can be used in place of the := symbol to assign initial values to the variables within the declaration statement. ____ 7. Declaring a table of records variable in a package specification allows it to persist for a user session. ____ 8. Implicit cursors are declared automatically for all INSERT, UPDATE, and DELETE statements issued within a PL/SQL block. ____ 9. You can process multiple rows of data from a database by creating explicit cursors. ____ 10. With an implicit cursor, Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE. ____ 11. The basic loop uses the LOOP and END LOOP markers to begin and end the loop code. ____ 12. Dynamic SQL lets you write schema-management procedures that can be centralized in one schema, and can be called from other schemas and operate on the objects in those schemas. ____ 13. In PL/SQL, if a SELECT ... INTO statement returns no rows, Oracle does not return an error. ____ 14. In PL/SQL, a SELECT ......

Words: 1344 - Pages: 6

Premium Essay

Oracle Sql Tutorial

...Oracle/SQL Tutorial1 Michael Gertz Database and Information Systems Group Department of Computer Science University of California, Davis gertz@cs.ucdavis.edu http://www.db.cs.ucdavis.edu This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System. Further information about Oracle and SQL can be found on the web site www.db.cs.ucdavis.edu/dbs. Comments, corrections, or additions to these notes are welcome. Many thanks to Christina Chung for comments on the previous version. Recommended Literature George Koch and Kevin Loney: Oracle8 The Complete Reference (The Single Most Comprehensive Sourcebook for Oracle Server, Includes CD with electronic version of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey: Oracle8 : A Beginner’s Guide [A Thorough Introduction for First-time Users], 767 pages, McGraw-Hill/Osborne, 1997. Steven Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition), O’Reilly & Associates, 1028 pages, 1997. C.J. Date and Hugh Darwen: A Guide to the SQL Standard (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R. Simon: Understanding the New SQL: A Complete Guide (2nd Edition, Dec 2000), The Morgan Kaufmann Series in Data Management Systems, 2000. 1 revised Version 1.01, January 2000, Michael Gertz, Copyright 2000. Contents 1. SQL – Structured Query Language 1.1. Tables 1.2. Queries (Part I) 1.3. Data Definition...

Words: 21631 - Pages: 87

Premium Essay

Deep'Z Studio.

...INTRODUCTION SQL is divided into the following  Data Definition Language (DDL)  Data Manipulation Language (DML)  Data Retrieval Language (DRL)  Transaction Control Language (TCL)  Data Control Language (DCL) DDL -- create, alter, drop, truncate, rename DML -- insert, update, delete DRL -- select TCL -- commit, rollback, savepoint DCL -- grant, revoke CREATE TABLE SYNTAX Create table (col1 datatype1, col2 datatype2 …coln datatypen); Ex: SQL> create table student (no number (2), name varchar (10), marks number (3)); INSERT This will be used to insert the records into table. We have two methods to insert.   a) By value method By address method USING VALUE METHOD Syntax: insert into (table_name) values (value1, value2, value3 …. Valuen); © Copy rights are reserved. 2 Ex: SQL> insert into student values (1, ’sudha’, 100); SQL> insert into student values (2, ’saketh’, 200); To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult. This will be avoided by using address method. b) USING ADDRESS METHOD Syntax: insert into (table_name) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash. Ex: SQL> insert into student values (&no, '&name', &marks); Enter value for no: 1 Enter value for name: Jagan Enter value for marks: 300 old new SQL> 1:...

Words: 42387 - Pages: 170

Premium Essay

Questions to Ask an Oracle Developer

...role, do you work in all these phases yourself or are you usually involved with one particular phase? General Oracle Database and PL/SQL Questions: • Do you have any experience with Autonomous Transactions in Oracle database? The purpose is to complete (commit/rollback) a transaction in a called procedure irrespective of the transaction state in the calling procedure. • Have you ever encountered a situation with Mutating Tables and what did you do to work around it? When a table is in state of transition it is said to be mutating. eg: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select. • What’s your experience with Oracle Forms and Reports. Where would you implement bulk of business rules so as to make your coding more modular in Oracle Forms? PLL’s (PL/SQL Libraries). • What is referential integrity? Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table. • What is a Transaction in Oracle? A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. • What is a Cursor in Oracle? A cursor is a handle (name or a pointer) for the memory associated with a specific...

Words: 1951 - Pages: 8

Premium Essay

Hostel Management

...1.INTRODUCTION 1.1 PURPOSE: The purpose of this document is to describe the requirements for an automation system for a college grievance cell to register the complaints online from valid users and maintain all related information. It also describes the interface, platform and other constraints. 1.2 SCOPE: This document is the only one that describes the requirements of the system. It is meant for the use by the developers and will be the basis for validating the final delivered system. Any change made to the requirements in the future will have to go through a formal approval process. The developer is responsible for asking clarification, where necessary, and will not make any alteration without the permission of the client. 1.3 DEFINITIONS AND ABBREVIATIONS: 1. OGC- Online Grievance Cell 2. Grievance-A grievance is the subject of a complaint filed by a student, which is to be resolved by procedures provided in the college. It is any issue causing problem to the students, which need to be relevant. Ex. Issues related to ragging, food, faculty etc. Grievance is sometimes referred to as complaint. 3. Student- Any student of SDMCET belonging to any of the semesters from 1st to 8th of any branch. 4. Authority- The Dean academic for academic and Dean Student welfare for non academic grievances, who are responsible for resolving the grievances of students. 5. Database Administrator- One who manages and maintains the database, i.e adding/ deleting of users...

Words: 5166 - Pages: 21

Premium Essay

Db Iv - Oracle 11g V2 - Final Examination -Chapters 10 and 11 Student Version-Completed

...| A BEFORE statement level trigger will fire before a BEFORE row level trigger. | c. | If you have two statement level triggers, the order in which they are fired is dependent on the order in which they were written. | d. | Only one trigger can be constructed per table. | __D__ 4. Row level options are only applicable for ____ events. a. | CREATE | c. | DECLARE | b. | INSERT | d. | UPDATE and DELETE | __A__ 5. The default timing of a trigger is ____. a. | statement level | c. | row level | b. | system level | d. | header level | __A__ 6. Which of the following events will cause the trigger to fire? AFTER UPDATE OF orderplaced ON bb_basket a. | INSERT | c. | DELETE | b. | UPDATE | d. | AFTER | __D__ 7. CURSOR basketitem_curIS SELECT idproduct, quantity, option1 FROM bb_basketitem WHERE idbasket = :NEW.idbasket; In the code fragment above, which of the following represents the correlation identifier? a. | idproduct | c. | quantity | b. | bb_basketitem | d. | :NEW.idbasket | __D__ 8. A(n) ____ table is one that is involved in a join and the keys of the original table are included in the keys of the resultant join. a. | mutating | c. | database | b. | system | d. | key-preserved |...

Words: 1769 - Pages: 8

Premium Essay

Teach Yourself Sql

...Teach Yourself SQL in 21 Days, Second Edition Table of Contents: Introduction Week 1 at a Glance Day 1 Introduction to SQL Day 2 Introduction to the Query: The SELECT Statement Day 3 Expressions, Conditions, and Operators Day 4 Functions: Molding the Data You Retrieve Day 5 Clauses in SQL Day 6 Joining Tables Day 7 Subqueries: The Embedded SELECT Statement Week 1 in Review Week 2 at a Glance Day 8 Manipulating Data Day 9 Creating and Maintaining Tables Day 10 Creating Views and Indexes Day 11 Controlling Transactions Day 12 Database Security Day 13 Advanced SQL Topics Day 14 Dynamic Uses of SQL Week 2 in Review Week 3 at a Glance Day 15 Streamlining SQL Statements for Improved Performance Day 16 Using Views to Retrieve Useful Information from the Data Dictionary Day 17 Using SQL to Generate SQL Statements Day 18 PL/SQL: An Introduction Day 19 Transact-SQL: An Introduction Day 20 SQL*Plus Day 21 Common SQL Mistakes/Errors and Resolutions Week 3 in Review Appendixes A Glossary of Common SQL Statements B Source Code Listings for the C++ Program Used on Day 14 C Source Code Listings for the Delphi Program Used on Day 14 D Resources E ASCII Table F Answers to Quizzes and Excercises © Copyright, Macmillan Computer Publishing. All rights reserved. Teach Yourself SQL in 21 Days, Second Edition Acknowledgments A special thanks to the following individuals: foremost to my loving wife, Tina, for her tolerance and endless support, to Dan Wilson for his...

Words: 128515 - Pages: 515

Premium Essay

Imformation Systems Teach Yourself Sql

...Teach Yourself SQL in 21 Days, Second Edition Table of Contents: Introduction Week 1 at a Glance Day 1 Introduction to SQL Day 2 Introduction to the Query: The SELECT Statement Day 3 Expressions, Conditions, and Operators Day 4 Functions: Molding the Data You Retrieve Day 5 Clauses in SQL Day 6 Joining Tables Day 7 Subqueries: The Embedded SELECT Statement Week 1 in Review Week 2 at a Glance Day 8 Manipulating Data Day 9 Creating and Maintaining Tables Day 10 Creating Views and Indexes Day 11 Controlling Transactions Day 12 Database Security Day 13 Advanced SQL Topics Day 14 Dynamic Uses of SQL Week 2 in Review Week 3 at a Glance Day 15 Streamlining SQL Statements for Improved Performance Day 16 Using Views to Retrieve Useful Information from the Data Dictionary Day 17 Using SQL to Generate SQL Statements Day 18 PL/SQL: An Introduction Day 19 Transact-SQL: An Introduction Day 20 SQL*Plus Day 21 Common SQL Mistakes/Errors and Resolutions Week 3 in Review Appendixes A Glossary of Common SQL Statements B Source Code Listings for the C++ Program Used on Day 14 C Source Code Listings for the Delphi Program Used on Day 14 D Resources E ASCII Table F Answers to Quizzes and Excercises / Copyright, Macmillan Computer Publishing. All rights reserved. Join the National Guard Car Computer Serve in your own backyard Get Free Info Here. No Obligation www.military.com Complete Diagnostic Software Tool DTC Codes, Sensor Readings...

Words: 129252 - Pages: 518

Free Essay

Resume

...Professional Summary: * Extensive Experience in MS SQL Server 2005/2008 and 2000 Database Administration and PL/SQL developer in Business Analyst including Planning, Deployment / Implementation and configuration. * Extensive experience in writing Complex Stored procedures as well analyzing and debugging existing complex stored procedures. * Successfully led, executed and maintained projects with multiple databases. * Implementing all kinds of SQL Server Constraints (Primary, Foreign, Unique, Check etc).  * Generating complex Transact SQL (T-SQL) queries, Sub queries, Co-related sub queries, Dynamic SQL queries * Programming in SQL Server - Using the stored procedures, Triggers, User-defined functions and Views, Common table expressions (CTEs) * Proficient in creating T-SQL (DML, DDL, DCL and TCL), Indexes, Views, Temporally Tables, Table Variables, Complex Stored Procedures, System and User Defined Functions . * Strong experience in creating complex Replication, Index, Functions, DTS packages, triggers, cursors, tables, views and other SQL joins and statements. * Expertise in Client-Server Application Development using Oracle […] PL/SQL, SQL *PLUS, TOAD and SQL*LOADER. * Experience in understanding complicated performance issues and worked with DBA's to suggest valuable ways to fix the problem  * Hands on experience working with SSIS, for ETL process ensuring proper implementation of Event Handlers, Loggings, Checkpoints, Transactions...

Words: 1001 - Pages: 5

Premium Essay

Csc 352 / 452: Database Programming Assignment #3

...accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept. • If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them. • Do not try to use complicated queries/cursors (e.g., joins) to get the results. You can use multiple PL/SQL statements to get the results easily. • You cannot use hard-coded values (e.g., IF department_name = 'MARKETING' THEN ……) in your programs. • You are not allowed to create temporary tables, views, functions, or procedures. • The EXCEPTION section is NOT allowed in your programs. • Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file. 1) (CSC 352 – 25 Points | CSC 452 – 15 Points) The table popular_names consists of some popular given names for male and female babies born during the years 1915-2014. Rank 1 is the most popular, rank 2 is the next most popular, and so forth. 2) (CSC 352 – 35 Points | CSC 452 – 20 Points) Based on the tables created in Assignment #1, write a PL/SQL anonymous block to perform the following tasks: • Display the number of employees in the company. • Display the maximum/minimum/average salary for the company. • Display 50% of the maximum salary for the company. • For each department whose average salary is greater than 50% of the maximum salary...

Words: 334 - Pages: 2

Premium Essay

Test

...create objects in the database * ALTER - alters the structure of the database * DROP - delete objects from the database * TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed * COMMENT - add comments to the data dictionary * RENAME - rename an object DML Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: * SELECT - retrieve data from the a database * INSERT - insert data into a table * UPDATE - updates existing data within a table * DELETE - deletes all records from a table, the space for the records remain * MERGE - UPSERT operation (insert or update) * CALL - call a PL/SQL or Java subprogram * EXPLAIN PLAN - explain access path to data * LOCK TABLE - control concurrency DCL DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. * GRANT - gives user's access privileges to database * REVOKE - withdraw access privileges given with the GRANT command TCL Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. * COMMIT - save work done * SAVEPOINT - identify a point in a transaction to which you can later roll back * ROLLBACK - restore database...

Words: 2240 - Pages: 9

Premium Essay

Csc 352 / 452: Database Programming

...accepted. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept. • If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them. • Do not try to use complicated queries/cursors (e.g., joins) to get the results. You can use multiple PL/SQL statements to get the results easily. • You cannot use hard-coded values (e.g., IF department_name = 'MARKETING' THEN ……) in your programs. • You are not allowed to create temporary tables, views, functions, or procedures. • The EXCEPTION section is NOT allowed in your programs. • Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file. 1) (CSC 352 – 25 Points | CSC 452 – 15 Points) The table popular_names consists of some popular given names for male and female babies born during the years 1915-2014. Rank 1 is the most popular, rank 2 is the next most popular, and so forth. 2) (CSC 352 – 35 Points | CSC 452 – 20 Points) Based on the tables created in Assignment #1, write a PL/SQL anonymous block to perform the following tasks: • Display the number of employees in the company. • Display the maximum/minimum/average salary for the company. • Display 50% of the maximum salary for the company. • For each department whose average salary is greater than 50% of the maximum salary...

Words: 334 - Pages: 2