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!

help with pl

Discussion in 'SQL PL/SQL' started by mmm286, May 8, 2014.

  1. mmm286

    mmm286 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    España
    Hello,
    I am new programming in PL, and I have some problems. These is the idea:
    I want to make a query that returns a number of records from a single field and recording them in a variable . I can do these with a "select into" to record in a variable.
    After that, for each value of that query I want to make another select, and in the where put the variable of the other select and that the query returns some fields.
    I know that I could make these generating the results of the first query inside an auxiliary table the results of the first query and then use them in the second select, but I won't do it without auxiliary tables.
    How could I do these?
    Many Thanks and sorry for my English!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    post your code here, that would help people to give answer to you....
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way to do that:


    Code (SQL):

    SQL> DECLARE
      2
      3          cursor get_dept_no IS
      4          SELECT deptno FROM dept;
      5
      6          cursor get_emp_recs (v_dno NUMBER) IS
      7          SELECT * FROM emp WHERE deptno = v_dno;
      8  BEGIN
      9          FOR v_dept IN get_dept_no loop
     10                  FOR v_emprec IN get_emp_recs(v_dept.deptno) loop
     11                          dbms_output.put_line(v_emprec.empno||'  '||v_emprec.ename||'  '||v_emprec.deptno||'  '||v_emprec.hiredate||'  '||v_emprec.job)
    ;
     12                  END loop;
     13          END loop;
     14  END;
     15  /
    7782  CLARK  10  09-JUN-81  MANAGER
    7839  KING  10  17-NOV-81  PRESIDENT
    7934  MILLER  10  23-JAN-82  CLERK
    7939  DUKE  10  17-NOV-81  CEO
    7949  PRINCE  10  17-NOV-81  CFO
    7959  QUEEN  10  17-NOV-81  CIO
    7869  JACK  10  17-NOV-81  PRESIDENT
    7369  SMITH  20  17-DEC-80  CLERK
    7566  JONES  20  02-APR-81  MANAGER
    7788  SCOTT  20  09-DEC-82  ANALYST
    7876  ADAMS  20  12-JAN-83  CLERK
    7902  FORD  20  03-DEC-81  ANALYST
    7499  ALLEN  30  20-FEB-81  SALESMAN
    7521  WARD  30  22-FEB-81  SALESMAN
    7654  MARTIN  30  28-SEP-81  SALESMAN
    7698  BLAKE  30  01-MAY-81  MANAGER
    7844  TURNER  30  08-SEP-81  SALESMAN
    7900  JAMES  30  03-DEC-81  CLERK


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    Remember this is an example; your code will most likely differ.
     
    mmm286 likes this.
  5. mmm286

    mmm286 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    España
    Thanks to all.
    I cant put the code because it says me that is SPAM
     
  6. mmm286

    mmm286 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    España
    The logical of the pl is extract duplicated records by a field of some related tables and then, extract more fields of these duplicated rows to can send an email.
    Like I've said, I can do generating an auxiliary table with the duplicated field and then a query of the related tables to get more informaticion but I would like to do in a only pl/sql.
    Thanks and sorry for my english!
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    If you want to carry out comparing of structure of some tables, then it is possible to look at use package DBMS_COMPARISON.

    Otherwise you should use submissions of the dictionary of data, such as user_tables,user_tab_cols (or analog in DBA or ALL) and etc.
     
  8. mmm286

    mmm286 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    España
    Thanks.
    I think I cannot express me very well because my bad english
     
  9. mmm286

    mmm286 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    España
    I only extract some duplicated registers and then make other select with them to get other fields of these registers. For instance:

    David
    David
    John
    Marc

    Duplicated: David

    Then extract with David of some tables other fields in others tables:

    David 32423423Y Street xxxxx
    David 3422342O Street yyyyy
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Something is necessary similar?

    Code (SQL):

    SET serveroutput ON
    DROP TABLE t_test_user purge;
    DROP TABLE t_test_usr_data purge;
    CREATE TABLE t_test_user ( name varchar2(100 CHAR));
     CREATE TABLE t_test_usr_data
     (
      user_name varchar2 (100 CHAR),
      other varchar2(100 CHAR),  
      addr varchar2(255 CHAR)
     );
     INSERT INTO t_test_user VALUES('David');
     INSERT INTO t_test_user VALUES('David');
     INSERT INTO t_test_user VALUES('Jonh');
     INSERT INTO t_test_user VALUES('Maria');
     INSERT INTO t_test_usr_data VALUES ('David', '32423423Y', 'Street xxxxx');
     INSERT INTO t_test_usr_data VALUES ('David' ,'3422342O', 'Street yyyyy');
     commit;
     rem pl/SQL    
     BEGIN
         FOR i IN (SELECT name FROM t_test_user GROUP BY name HAVING COUNT(*) > 1)
         loop
             FOR j IN ( SELECT * FROM t_test_usr_data WHERE user_name = i.name )
             loop
                 dbms_output.put_line (j.user_name||' '||j.other||' '||j.addr);
             END loop;        
         END loop;    
     END;      
    /  

    SQL>


    SQL>
     
    TABLE dropped
     
    TABLE dropped
     
    TABLE created
     
    TABLE created
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    Commit complete
     
    David 32423423Y Street xxxxx
    David 3422342O Street yyyyy
     
    PL/SQL PROCEDURE successfully completed


     
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why do you need to do this with PL/SQL? A straight SQL query could return the duplicated records:


    Code (SQL):

    SQL> SELECT *
      2  FROM t_test_usr_data
      3  WHERE user_name IN (SELECT name FROM t_test_user GROUP BY name     HAVING COUNT(*) > 1);
    USER_NAME                                                                                            OTHER
                                              ADDR
    ---------------------------------------------------------------------------------------------------- ------------------------------------------------------
    ---------------------------------------------- ------------------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------
    David                                                                                                32423423Y
                                              Street xxxxx
    David                                                                                                3422342O
                                              Street yyyyy


    SQL>
     

    Unless this is a homework assignment...
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    I too think that this task is very similar to homework...
    Otherwise why to use pl/sql for the solution of this task...