Cursor in SQL

Posted on 2011-04-21
Last Modified: 2012-05-11
Hi. I want to use a cursor on a table of managers. If any manager has a salary less that 10k raise and error. If manager has salary > than 50k output a message and re-raise the exception.

This is what i have so far............

DECLARE CURSOR man_cur IS SELECT * FROM manager WHERE salary < 10000.........

I'm confused after this. Thanks
Question by:graziazi
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I don't understand the question.  Please provide more of the requirements.

    For example: are you wanting a pl/sql procedure with a loop through the cursor?  If you hit a vlue and raise an exception, it stops.  How do you want to 'output' a value before the exception?

    Sample data and expected results would help a lot.

    Author Comment

    Yes I want a pl/sql procedure with a loop through the cursor. Ok let's simplify by taking out the 90k clause
    LVL 76

    Accepted Solution

    create or replace procedure myProc
         for i in (SELECT * FROM manager) loop

             if i.salary < 10000 then
                       raise_application_error(-20001, || ' has a salary < 10000');
             elsif i.salary > 90000 then
                       dbms_output.put_line('Have a winner!');
                       raise_application_error(-20001, || ' has a salary > 90000');
             end if;
         end loop

    Author Closing Comment

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to recover a database from a user managed backup

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now