Question

我想提出一个问题,获得结果,然后利用我在第2次发言中第一次发言(审计员)的价值观,用另一种选择发言来表示。

I have 40 users in my db. All the users have the same db schema structure. I want to get the username via :

SELECT  distinct username 
   from all_users

之后,使用用户名称来进行这样的询问:

Select lastname, firstname, email, email2 from username.member.

我的一套成果将回归多个阶层,因此我也需要一种增长模式。

I have tried many different pl/sql combinations:

DECLARE
   CURSOR client_cur IS
   SELECT  distinct username 
   from all_users 
   where length(username) = 3;
   -- client cursor 
   CURSOR emails_cur (cli all_users.username%TYPE) IS
   SELECT id, name 
     FROM cli.org;
BEGIN
   FOR client IN client_cur LOOP
      dbms_output.put_line( Client is  || client.username);
      FOR email_rec in client_cur(client.username) LOOP
         dbms_output.put_line( Org id is   ||email_rec.id ||   org nam   || email_rec.name);
      END LOOP;
  END LOOP;
END;
/

以及

DECLARE
  CURSOR c1 IS
    SELECT  distinct username from all_users where length(username) = 3;
    client c1%rowtype;
   cursor c2 is Select id, name, allow_digest_flg from c1.username.org;
 digest c2%rowtype;
-- declare record variable that represents a row fetched from the employees table
--   employee_rec c1%ROWTYPE; 
 BEGIN
-- open the explicit cursor 以及use it to fetch data into employee_rec
    OPEN c1;
  loop
     FETCH c1 INTO client; 
   open c2; 
   loop
    fetch c2 into digest;
      DBMS_OUTPUT.PUT_LINE( digest is :   || c2.id ||   以及name is   || c2.name ||   flg is   || c2.allow_digest_flg );
   end loop;
  end loop;
 END;
/

以及大饥荒。

Can someone help me. THANKS

Answer 1

你们需要利用富有活力的食堂来实现这一目标。

DECLARE
    TYPE cur_type IS REF CURSOR;

    CURSOR client_cur IS
        SELECT DISTING username
        FROM all_users
        WHERE length(username) = 3;

    emails_cur cur_type;
    l_cur_string VARCHAR2(128);
    l_email_id <type>;
    l_name <type>;
BEGIN
    FOR client IN client_cur LOOP
        dbms_output.put_line( Client is  || client.username);
        l_cur_string :=  SELECT id, name FROM  
            || client.username ||  .org ;
        OPEN emails_cur FOR l_cur_string;
        LOOP
            FETCH emails_cur INTO l_email_id, l_name;
            EXIT WHEN emails_cur%NOTFOUND;
            dbms_output.put_line( Org id is   || l_email_id
                ||   org name   || l_name);
        END LOOP;
        CLOSE emails_cur;
    END LOOP;
END;
/

Edited to correct two errors, and to add links to 10g documentation for OPEN-FOR and an example. Edited to make the inner cursor query a string variable.

Answer 2

你肯定能够做这样的事情。

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    for d in (select * from dept)
  3    loop
  4      for e in (select * from emp where deptno=d.deptno)
  5      loop
  6        dbms_output.put_line(  Employee   || e.ename ||
  7                                in department   || d.dname );
  8      end loop;
  9    end loop;
 10* end;
SQL> /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES

PL/SQL procedure successfully completed.

或者使用直截了当的 cur具。

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor dept_cur
  3        is select *
  4             from dept;
  5    d dept_cur%rowtype;
  6    cursor emp_cur( p_deptno IN dept.deptno%type )
  7        is select *
  8             from emp
  9            where deptno = p_deptno;
 10    e emp_cur%rowtype;
 11  begin
 12    open dept_cur;
 13    loop
 14      fetch dept_cur into d;
 15      exit when dept_cur%notfound;
 16      open emp_cur( d.deptno );
 17      loop
 18        fetch emp_cur into e;
 19        exit when emp_cur%notfound;
 20        dbms_output.put_line(  Employee   || e.ename ||
 21                                in department   || d.dname );
 22      end loop;
 23      close emp_cur;
 24    end loop;
 25    close dept_cur;
 26* end;
 27  /
Employee CLARK in department ACCOUNTING
Employee KING in department ACCOUNTING
Employee MILLER in department ACCOUNTING
Employee smith in department RESEARCH
Employee JONES in department RESEARCH
Employee SCOTT in department RESEARCH
Employee ADAMS in department RESEARCH
Employee FORD in department RESEARCH
Employee ALLEN in department SALES
Employee WARD in department SALES
Employee MARTIN in department SALES
Employee BLAKE in department SALES
Employee TURNER in department SALES
Employee JAMES in department SALES

PL/SQL procedure successfully completed.

然而,如果你发现自己利用nes虫药管,让数据库与你的两个结果合并几乎总是更有效率。毕竟,关系数据库在加入方面确实是好的。我在这里猜想一下您的表象,以及这些表格如何根据你所写的法典,而是根据行文措词。

FOR x IN (SELECT *
            FROM all_users,
                 org
           WHERE length(all_users.username) = 3
             AND all_users.username = org.username )
LOOP
  <<do something>>
END LOOP;

Answer 3

Use alter session set current_schema = <username>, in your case as an execute immediate.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm 欲了解详情,请参见Oracle s 文件。

在你的情况下,这或许会oil到(untested)。

DECLARE

   CURSOR client_cur IS
     SELECT  distinct username 
       from all_users 
      where length(username) = 3;

   -- client cursor 
   CURSOR emails_cur IS
   SELECT id, name 
     FROM org;

BEGIN

   FOR client IN client_cur LOOP

   -- ****
      execute immediate 
      alter session set current_schema =   || client.username;
   -- ****

      FOR email_rec in client_cur LOOP

         dbms_output.put_line(
              Org id is   || email_rec.id || 
               org nam    || email_rec.name);

      END LOOP;

  END LOOP;
END;
/

友情链接