SQL & PL/SQL

db - 10g

I am asking this question out of curiosity.

I posted a question and recieved a successful answer at the following address on this forum;

2280697


The following insert statement uses a sequence to create an arbitrary number for each null record. The problem is, the sequence is firing regardless of the condition being true or false (but it is working in that it only inserts values when the condition is true). The result is the sequence appears to increment each time it loops through the cursor and so the first arbitrary number to be inserted is not 1001 but some other higher number.

INSERT INTO psp_trees
( plot_id
, tree_name
, species_code)
VALUES
( get_plot_id
, c1.treenum           
, c1.species
) RETURNING tree_id INTO get_tree_id;   

I have gotten around the issue by removing the sequence from the insert statement and placing it outside the cursor loop as an update statement.

      UPDATE psp_trees
         SET tree_name = to_char(tree_arbitrary_name_seq.nextval)
       WHERE tree_name IS NULL;

Is there anyway to get the sequence to only fire in the insert statement when the condition is true.

This is the complete anonymous block for your reference;

DECLARE p_access_num NUMBER;
col_access_var VARCHAR2(30);
type number_ptb IS table OF VARCHAR2(4000);
p_access_nam number_ptb;
get_plot_measurement_id NUMBER;
update_plot_measurements VARCHAR2(4000);
get_tree_id NUMBER;
get_plot_id NUMBER; 

BEGIN 

    p_access_num := 6;
    p_access_nam := number_ptb();
    p_access_nam.extend(7);
    
    p_access_nam(1) := 535;
    p_access_nam(2) := 548;
    p_access_nam(3) := 898;
    p_access_nam(4) := 544;
    p_access_nam(5) := 551;
    p_access_nam(6) := 724;

    FOR loop_int IN 1 .. p_access_num

    LOOP 

      col_access_var := p_access_nam(loop_int); 

      -- 1. Initiate insert process by getting PK from psp_plots.
      SELECT plot_id INTO get_plot_id FROM psp_plots WHERE plot_name = col_access_var;     

      -- 2. Insert records into psp_plot_measurements and keep relationship with psp_plots with variable get_plot_id.
      --    and get primary key value of psp_plot_measurements to insert into psp_tree_measurements during loop process.
      INSERT INTO psp_plot_measurements (plot_id) VALUES (get_plot_id) RETURNING plot_measurement_id INTO get_plot_measurement_id;    

      -- 3. Update record created at point 2.
      UPDATE psp_plot_measurements
         SET measurement_date    = (SELECT DISTINCT date_ FROM pspdata)
           , codominant_height   = (SELECT DISTINCT height_codom FROM pspdata)
           , assessor            = (SELECT DISTINCT assessor FROM pspdata)
       WHERE plot_measurement_id = col_access_var
       ;  

      -- 4. Open cursor to insert rows one at a time and maintain relationships between related tables.
      FOR c1 IN (SELECT treenum
                      , dbhob
                      , treecomments
                      , species
                   FROM pspdata
                  WHERE plotnumber = col_access_var
                    AND date_ IS NOT NULL)      

          LOOP    

            -- 5. Insert record into psp_trees
            --    and get primary key value of psp_trees to insert into psp_tree_measurements.
            INSERT INTO psp_trees
            ( plot_id
            , tree_name
            , species_code)
            VALUES
            ( get_plot_id
            , c1.treenum
            --, NVL(c1.treenum , to_char(tree_arbitrary_name_seq.nextval))
            --, (CASE WHEN c1.treenum IS NULL THEN to_char(tree_arbitrary_name_seq.nextval) ELSE c1.treenum END)
            , c1.species
            ) RETURNING tree_id INTO get_tree_id;    

            -- 6. Insert records into psp_tree_measurements and keep relationship with psp_trees with variable get_tree_id.
            INSERT INTO psp_tree_measurements 
            ( plot_measurement_id
            , tree_id
            , dbhob           
            , comments
            ) 
            VALUES 
            ( get_plot_measurement_id
            , get_tree_id
            , c1.dbhob
            , c1.comment
            );    

          END LOOP;          

  END LOOP;  
  
      -- 7. Update null columns with arbitrary number.
      UPDATE psp_trees
         SET tree_name = to_char(tree_arbitrary_name_seq.nextval)
       WHERE tree_name IS NULL;

END;
/

Edited by: benton on Sep 6, 2011 1:53 PM