locked
Stored procedure...... RRS feed

  • Question

  • User-2084712455 posted

    Please anybody write stored procedure(oracle) for mentioned details...

    I have to write a stored procedure in which I have to first select all application numbers from Table1.
    Now for every application we have to use this application no in where clause to select details of another table let say Table2 for every individual application no. and count the result of select statement.
    If Count is greater then zero then execute a select statement for same application no.
    If count is zero then execute a another select statement for same application no.

    Tuesday, May 21, 2013 3:11 AM

Answers

  • User197322208 posted

    and count the result of select statement

    insert the results into a temp table  and make select count(*) from table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 5:27 AM

All replies

  • User197322208 posted

    and count the result of select statement

    insert the results into a temp table  and make select count(*) from table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2013 5:27 AM
  • User364663285 posted

    Read:

    http://www.tutorialspoint.com/plsql/

    Tuesday, May 21, 2013 6:18 AM
  • User-1800438376 posted

    I think you need to search on google

    how to create stored procedure in sql server.

    Tuesday, May 21, 2013 7:38 AM
  • User79986525 posted

    Hi ,

    try cursor Like this

    DECLARE
      CURSOR c1 IS 
             SELECT table_name FROM all_tables;
      v_table_name all_tables.table_name%TYPE;
      v_count      INTEGER := 1;
    BEGIN
      OPEN c1;
      LOOP
         FETCH c1 INTO v_table_name;
         IF c1%notfound OR v_count > 2000 THEN
           EXIT;
         END IF;
         v_count := v_count + 1;
      END LOOP;
    
      -- Does cursor need to be closed
      IF c1%ISOPEN THEN  -- cursor is open
         CLOSE c1;
      END IF;
    
      dbms_output.put_line('Rows processed: '||v_count);
    END;
    /
    check link
    http://www.orafaq.com/wiki/Cursor

    or in sql server it will be like this

    declare @id int
    
    DECLARE db_cursor CURSOR FOR  
    SELECT ApplicationID
    FROM table1
    
    
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @ID   
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN  
     ifEXIST( select * from Table2 where AppId=@ID )
    BEGIN
    select * from table2
    END
    ELSE
    BEGIN
    select * from table3
    END
         
    
           FETCH NEXT FROM db_cursor INTO @ID
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor





    Tuesday, May 21, 2013 8:38 AM