Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: sql script using cursors

    I need to write a little script that would read in the contents of a table using a cursor and output to the screen a formatted version of its contents, row by row.

    Let's say my table is called pets and it has columns called name, age, breed, color.

    I want it to put out the contents to the screen in following format:
    For example,

    Name: Jack
    Age: 2
    Breed: Poodle
    Color: White
    Count: 1

    Name: Rex
    Age: 4
    Breed: German Retriever
    Color: Brown
    Count: 2

    Name: Sally
    Age: 5
    Breed: Mutt
    Color: Orange
    Count: 3

    How would I do that? I am good with basic CRUD operations in sql but have not used cursors before.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How would I do that?
    It is trivial in PERL, PHP, Python, etc.
    Pick a language & code it!
    P.S.
    The homework tutor is down the hall & the 2nd door on the left.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    look, i know how to do it in programming language like java, but I need to do this completely using sql (i.e. with cursors)

    I am not looking to be spoonfed the solution either, I just need to see an example of doing something like that.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I don't know that you really want to do this in SQL, but here is an example
    of formating and sending data to the screen or to a report ...


    set feedback off
    Set echo off
    set verify off
    set heading on
    set space 1
    set linesize 120
    set newpage 1
    set pagesize 80

    column v_customer_id heading 'Customer' format a10 trunc
    column v_timestamp heading 'Date' format date trunc
    column x_audit_time heading 'Timestamp' format a14 trunc
    column v_chg_column heading ' Column Chg' format a18 trunc
    column v_before heading 'Before Value' format a12 trunc
    column v_after heading 'After Value' format a12 trunc
    column v_user heading 'User' format a12 trunc
    column v_sequence heading 'Sequence' format 99999999 trunc
    column v_program heading 'Program' format a8 trunc
    column v_machine heading 'Machine' format a15 trunc
    column xtoday new_value TODAY noprint;
    column xbegin new_value zbegin noprint;
    column xend new_value zend noprint;

    select to_char(sysdate,'DD-MON-YY') xtoday from dual;

    define begin_date = '1-JAN-2000'
    define end_date = '31-DEC-2999'

    accept begin_date prompt 'Search From Date (ie: 01-JAN-2004): '
    accept end_date prompt 'Search To Date (ie: 31-DEC-2004): '

    Spool c:\Customer_Audit.lst

    SELECT
    DECODE('&begin_date',null,'1-JAN-2000 00:00:00',
    upper('&begin_date 00:00:00')) xbegin,
    DECODE('&end_date',null,'31-DEC-2999 23:59:59',
    upper('&end_date 23:59:59')) xend
    FROM DUAL;

    --- BNA1 ---

    define db_name = 'Database Name or whatever'

    ttitle left 'Database: &db_name' -
    center 'Report Heading' -
    right 'Date : ' &today -
    skip 1 left 'Customer Credit/Terms Changes' -
    center 'Customer Credit Limit/Terms Audit Report' -
    right 'Page : ' format 999 sql.pno ' ' skip 2;
    BREAK on v_sequence noduplicates skip 1

    SELECT
    sequence_no v_sequence,
    to_char(trans_timestamp,'MM/DD/YY HH24:MI') x_audit_time,
    substr(customer_id,1,10) v_customer_id,
    substr(COLUMN_CHANGED,1,18) v_chg_column,
    BEFORE_COLUMN_VALUE v_before,
    AFTER_COLUMN_VALUE v_after,
    substr(user_running,1,12) v_user,
    substr(machine_id,1,15) v_machine,
    substr(program_name,1,8) v_program
    FROM
    audit_customer]
    WHERE TO_CHAR(trans_timestamp,'DD-MON-YYYY HH24:MIS') between '&zbegin' and '&zend'
    ORDER BY sequence_no, trans_timestamp DESC
    /


    HTH
    Gregg

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    And it uses a cursor. Will that do?

    I suspect the assigment expects PL/SQL and either DBMS_OUTPUT or ref cursors though.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, you are novice regarding PL/SQL programming. I guess it is OK to ask for help, but I can't understand why don't you read the manual - there are quite a few nice examples of a cursor use. Here is the Managing Cursors in PL/SQL chapter; check implicit and explicit cursor FOR loops.

    I believe you'll need something like this (CHR(10) inserts a new line; that's the only thing you probably wouldn't find in the proposed manual):
    Code:
    set serveroutput on;
    
    begin
      for c1r in (select name, age, breed from dogs)
      loop
        dbms_output.put_line('Name: ' || c1r.name || chr(10) ||
                             'Age : ' || c1r.age
                            );
      end loop;
    end;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •