1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

pl/sql using substitution variable to set bind variable

Discussion in 'SQL PL/SQL' started by seeko, Nov 11, 2012.

  1. seeko

    seeko Guest

    I am still learning pl/sql so I am not good yet with code. II was working on my code and I got to the point I have to use substitution variable to set bind variable to decide minimum balance due(2000) for invoices that I can retrieve with SELECT. My result should get the balnace due of 2000 or greater.
    Here is my code that I am working with

    Code (Text):
    SET SERVEROUTPUT ON;
    Declare
      cursor invoices_cursor IS
      SELECT vendor_name, invoice_number,
              invoice_total - payment_total - credit_total AS balance_due
      FROM vendors v JOIN invoices i
          ON v.vendor_id = i.vendor_id
      WHERE invoice_total - payment_total - credit_total >= 5000
      ORDER BY balance_due DESC;
     
      invoice_row invoices%ROWTYPE;
     
    BEGIN
     DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$20,000 or more');
      FOR invoice_row IN invoices_cursor LOOP
      IF invoice_row.balance_due >= 20000 THEN
        DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END IF;
      END LOOP;
     
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$10,000 to $20,000 ');
      FOR invoice_row IN invoices_cursor LOOP
      IF invoice_row.balance_due >= 10000
      AND invoice_row.balance_due < 20000 THEN
      DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END IF;
      END LOOP;
     
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$5,000 to $10,000 ');
      FOR invoice_row IN invoices_cursor LOOP
      IF invoice_row.balance_due >= 5000
      AND invoice_row.balance_due < 10000 THEN
      DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END IF;
      END LOOP;
    END;
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are several conditions in this script that compare against totals -- which comparison is you want to change to a dynamic parameter supplied at run time?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    In a simple case your code can be added so:

    Code (Text):
    [code=sql]SET SERVEROUTPUT ON;
    Declare
      cursor invoices_cursor (p_sum_filter number ) IS
      SELECT vendor_name, invoice_number,
              invoice_total - payment_total - credit_total AS balance_due
      FROM vendors v JOIN invoices i
          ON v.vendor_id = i.vendor_id
      WHERE invoice_total - payment_total - credit_total >= p_sum_filter
      ORDER BY balance_due DESC;
     
      invoice_row invoices%ROWTYPE;
     
    BEGIN
     DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$20,000 or more');
      FOR invoice_row IN invoices_cursor(20000)  LOOP  
        DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END LOOP;
     
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$10,000 to $20,000 ');
      FOR invoice_row IN invoices_cursor(10000) LOOP
      IF invoice_row.balance_due >= 10000
      AND invoice_row.balance_due < 20000 THEN
      DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END IF;
      END LOOP;
     
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('$5,000 to $10,000 ');
      FOR invoice_row IN invoices_cursor(5000) LOOP
      IF invoice_row.balance_due >= 5000
      AND invoice_row.balance_due < 10000 THEN
      DBMS_OUTPUT.PUT_LINE(
          to_char(invoice_row.balance_due, '$99,999.99') || '   ' ||
          invoice_row.invoice_number || '   ' || invoice_row.vendor_name);
      END IF;
      END LOOP;
    END;
    [/CODE]


    But this code, it is possible to improve too still