Home » SQL & PL/SQL » SQL & PL/SQL » QUERY regarding procedure
icon8.gif  QUERY regarding procedure [message #258985] Tue, 14 August 2007 01:04 Go to next message
pooni17
Messages: 4
Registered: August 2007
Junior Member
HI,
i am writing a procedure to update a column in my table.
i am attaching the details of table.But the problem is it is taking to much of time.
plz help me to write an efficient procedure for this.
i am using oracle 10g.
  • Attachment: query.txt
    (Size: 3.28KB, Downloaded 130 times)

[Updated on: Tue, 14 August 2007 01:04]

Report message to a moderator

Re: QUERY regarding procedure [message #258986 is a reply to message #258985] Tue, 14 August 2007 01:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Edit: the procedure for those who can't download the document:
CREATE OR REPLACE PROCEDURE rdf_na_q206.update_node (
   c_node_id   IN       temp_rdf_node.node_id%TYPE
 , c_status    OUT      CHAR
)
IS
   CURSOR c_node
   IS
      SELECT rl.nav_state_id
      FROM   rdf_link_node rln
           , rdf_link rl
      WHERE  rln.node_id = c_node_id AND rln.link_id = rl.link_id;
-- c_navigable temp_rdf_node.N_NAVIGABLE%type;

-- select temp_rdf_node.N_NAVIGABLE into c_navigable from 
-- temp_rdf_node where temp_rdf_node.node_id=c_node_id;
BEGIN
   FOR x IN c_node
   LOOP
      IF (x.nav_state_id IS NOT NULL)
      THEN
         UPDATE temp_rdf_node
         SET n_navigable = 'Y'
         WHERE  temp_rdf_node.node_id = c_node_id;

         c_status    := 'Y';
      ELSIF (x.nav_state_id IS NULL)
      THEN
         UPDATE temp_rdf_node
         SET n_navigable = 'N'
         WHERE  temp_rdf_node.node_id = c_node_id;

         c_status    := 'N';
      END IF;
   END LOOP;

   COMMIT;
END update_node;
/

DECLARE
          cursor c1 is select * from temp_rdf_node ;
          v_status char:='Y';
      BEGIN
              for r in c1
              loop
                  update_node(r.node_id,v_status);
              end loop;
             if(v_status='N') then
                  dbms_output.put_line('Link is Non-Navigable and Table is Updated.');
             elsif(v_status='Y') then
                  dbms_output.put_line('Link is Navigable and Table is Updated.');
             end if;
             
      EXCEPTION
      
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('ERROR! No row found');
        WHEN INVALID_CURSOR THEN
             dbms_output.put_line('ERROR! Invalid cursor');
        WHEN OTHERS THEN
             dbms_output.put_line('*****Error Occured********');
             dbms_output.put_line('SQLCODE: '||to_char(SQLCODE));
             dbms_output.put_line('SQLERRM: '||SQLERRM);
  END ;




Structure of Temp_rdf_node::


CREATE TABLE TEMP_RDF_NODE
(
  NODE_ID      NUMBER(10)                       NOT NULL,
  LAT          NUMBER(10)                       NOT NULL,
  LON          NUMBER(10)                       NOT NULL,
  Z_COORD      NUMBER(10),
  ZLEVEL       NUMBER(5)                        NOT NULL,
  IS_ALIGNED   CHAR(1 CHAR)                     NOT NULL,
  NODE_TYPE    NUMBER(2),
  N_NAVIGABLE  CHAR(1 CHAR)
)



CREATE TABLE RDF_LINK_NODE
(
  LINK_ID   NUMBER(10)                          NOT NULL,
  NODE_ID   NUMBER(10)                          NOT NULL,
  REF_TYPE  CHAR(1 CHAR)                        NOT NULL
)




CREATE TABLE RDF_LINK
(
  LINK_ID        NUMBER(10)                     NOT NULL,
  DETAILED_CITY  CHAR(1 CHAR)                   NOT NULL,
  BRIDGE         CHAR(1 CHAR)                   NOT NULL,
  TUNNEL         CHAR(1 CHAR)                   NOT NULL,
  URBAN          CHAR(1 CHAR)                   NOT NULL,
  IS_ALIGNED     CHAR(1 CHAR)                   NOT NULL,
  FULL_GEOMETRY  CHAR(1 CHAR),
  NAV_STATE_ID   NUMBER(10),
  LONG_HAUL      CHAR(1 CHAR)                   NOT NULL,
  STUB_LINK      CHAR(1 CHAR)                   NOT NULL
)


Comments:
1. do a RAISE in your exception handler.
2. It seems a single update can do the trick.

MHE

[Updated on: Tue, 14 August 2007 01:17]

Report message to a moderator

Re: QUERY regarding procedure [message #258988 is a reply to message #258985] Tue, 14 August 2007 01:14 Go to previous messageGo to next message
pooni17
Messages: 4
Registered: August 2007
Junior Member
ALTER TABLE RDF_NA_Q206.RDF_LINK_NODE ADD (
CONSTRAINT PK_RDFLINKNODE
PRIMARY KEY
(LINK_ID, REF_TYPE)
USING INDEX
Re: QUERY regarding procedure [message #258991 is a reply to message #258986] Tue, 14 August 2007 01:22 Go to previous messageGo to next message
pooni17
Messages: 4
Registered: August 2007
Junior Member
Can u help in writing a procedure for me which is quite efficient?

[Updated on: Tue, 14 August 2007 01:28]

Report message to a moderator

Re: QUERY regarding procedure [message #259000 is a reply to message #258991] Tue, 14 August 2007 01:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member


You wrote a procedure that updates one record. But you call the procedure for every record in the table. You also select from the table (the cursor in the calling anonymous block) and in the procedure you commit changes on the same table. This is called 'fetch across commit' and is generally considered to be not such a good idea. Since you update all the records in the table, why not write a single update statement?

How are the relations between your tables? Basically you want to update TEMP_RDF_NODE depending with 'Y' if RDF_LINK.NAV_STATE_ID contains a value and with 'N' if it doesn't contain a value. If you can identify a single row in rdf_link for each temp_rdf_node you can probably write a more efficient update. If you find more than one record then you have problems already and you need to rethink your logic.

MHE
Re: QUERY regarding procedure [message #259022 is a reply to message #259000] Tue, 14 August 2007 03:25 Go to previous messageGo to next message
pooni17
Messages: 4
Registered: August 2007
Junior Member
Relationshjp in 3 tables is like shown in attached file.
for each record of Temp_rdf_node, i have to check for all the records associated with Temp_rdf_node.Node_id from the relation shown,if all rows doesnot contain any value for rdf_link.Nav_state_id then put 'N' else if any one is having value in rdf_link.Nav_state_id then put 'Y' in Temp_rdf_node.N_navigable.
  • Attachment: relation.doc
    (Size: 38.50KB, Downloaded 401 times)

[Updated on: Tue, 14 August 2007 03:31]

Report message to a moderator

Re: QUERY regarding procedure [message #259040 is a reply to message #259022] Tue, 14 August 2007 04:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah, so you can use a group function.

You could do something like this:
UPDATE temp_rdf_node t
SET n_navigable =
                 (SELECT   DECODE( MAX (rl.nav_state_id)
                                 , NULL, 'N'
                                 , 'Y'
                                 )
                  FROM     rdf_link_node rln
                         , rdf_link rl
                  WHERE    rln.node_id = t.node_id
                           AND rln.link_id = rl.link_id
                  GROUP BY rl.node_id);
WARNING! Untested code.

I'm sorry but I can't download just any file.

MHE
Previous Topic: to_date format question
Next Topic: Problem with sub query and MAX
Goto Forum:
  


Current Time: Thu Dec 08 14:49:12 CST 2016

Total time taken to generate the page: 0.10982 seconds