请教ORACLE面试题！

Question

题目:                  產品A
  一級部件A1                          一級部件A2
二級部件A11   二級部件A12    二級部件A21   二級部件A22...            .....           .....        ....... 
上图为单阶BOM表.
要求:
1. 不能使用START WITH...CONNECT BY PRIOR语句
2. 产品的阶层不定,设定超过12层则出错
            
表结构及数据
ASSEMBLY_ITEM_NO COMPONENT_ITEM_NO  QUANTITY
A             A1              1
A             A2              1
A1             A11              1
A1             A12              1
A2             A21              1
A2             A22              1 产生结果
序號         料號
1                 A
11                 A1
111               A11
112               A12
12                 A2
121                A21
122                A22

Answer 1

不好意思发贴时不好布局，所以有点不好看。如果那位大哥愿意帮我解决这此问题，请留下MAIL，我直接给你传给你。先说声谢谢了。

Answer 2

来投机取巧  1  with t as (
  2  select 'A' assembly_item_no,'A1' component_item_no,1 quantity from dual
  3  union all
  4  select 'A','A2',1 from dual
  5  union all
  6  select 'A1','A11',1 from dual
  7  union all
  8  select 'A1','A12',1 from dual
  9  union all
 10  select 'A2','A21',1 from dual
 11  union all
 12  select 'A2','A22',1 from dual
 13  ),
 14  r as (
 15  select cast('1'||ltrim(component_item_no,'A') as varchar(12)) seq,component_item_no  from t
 16  union all
 17  select '1','A' from dual
 18  )
 19* select * from r order by seq
SQL> /SEQ          COM
------------ ---
1            A
11           A1
111          A11
112          A12
12           A2
121          A21
122          A22

Answer 3

高手，这么复杂的SQL也能写出来，不过他有要求:“
1. 不能使用START WITH...CONNECT BY PRIOR语句
2. 产品的阶层不定,设定超过12层则出错”
好像听他只能用游标才能做出来。
大家再帮我想想！

Answer 4

产品的阶层不定,设定超过12层则出错
-----------------------------------
只要他给定的数据符合 Annnn 命名规律，就可以使用这个语句。并且使用 cast('1'||ltrim(component_item_no,'A') as varchar(12)) 可以限定 12 层。当然有投机之嫌。当然用游标（+ 递归）也可以解决，如果他想这样。create table assembly (assembly_item_no varchar2(15),component_item_no varchar2(15),quantity number);
insert into assembly values('A','A1',1);
insert into assembly values('A','A2',2);
insert into assembly values('A1','A11',1);
insert into assembly values('A1','A12',1);
insert into assembly values('A2','A21',1);
insert into assembly values('A2','A22',1);
commit;create or replace procedure (get_result item_no varchar2, seq varchar2)
is
 cursor c is 
  select component_item_no from assembly
  where assembly_item_no=item_no order by component_item_no; component assembly.component_item_no%type;
begin
 if length(seq)>12 then
  raise_application_error(-20001,'Beyond limit.');
 end if; dbms_output.put_line(seq||lpad(chr(9),3,chr(9))||item_no); open c;
 loop
  fetch c into component;
  exit when c%notfound;
  get_result(component,seq||substr(component,-1,1));
 end loop;
 close c;
end;
/begin get_result('A','1'); end;
/
/*
1                       A
11                      A1
111                     A11
112                     A12
12                      A2
121                     A21
122                     A22
*/

Answer 5

-- 这个根据有一般性
create or replace procedure get_result (item_no varchar2, seq varchar2)
is
 cursor c is 
  select component_item_no from assembly
  where assembly_item_no=item_no order by component_item_no; component assembly.component_item_no%type; cnt number:=1;
begin
 if length(seq)>12 then
  raise_application_error(-20001,'Beyond limit.');
 end if; dbms_output.put_line(seq||lpad(chr(9),3,chr(9))||item_no); open c;
 loop
  fetch c into component;
  exit when c%notfound;
  get_result(component,seq||to_char(cnt,'fm99'));
  cnt:=cnt+1;
 end loop;
 close c;
end;
/

Answer 6

--创建表
create table t_component
(ASSEMBLY_ITEM_NO  varchar2(10),
COMPONENT_ITEM_NO  varchar2(10),
QUANTITY number);
--创建类型
create or replace type type_seq_obj as object (seq varchar2(100),component varchar2(100));
create or replace type tbl_seq_obj as table of type_seq_obj;
--创建函数
create or replace function func_get_seq(i_component varchar2) return
tbl_seq_obj
as
v_result tbl_seq_obj;
begin
v_result :=tbl_seq_obj(type_seq_obj('1',i_component));
proc_get_seq_kernal(1,i_component,v_result);
return v_result;
end;
--创建函数中调用的递归过程
CREATE OR REPLACE PROCEDURE proc_get_seq_kernal(i_seq_parent       VARCHAR2,
                                                i_component_parent VARCHAR2,
                                                io_result          IN OUT NOCOPY tbl_seq_obj) AS
  v_assembly_item_no  VARCHAR2(100);
  v_component_item_no VARCHAR2(100);
  v_num number;
  v_num1 number;
  CURSOR my_level IS
    SELECT assembly_item_no, component_item_no
      FROM t_component t
     WHERE t.assembly_item_no = i_component_parent
     ORDER BY t.component_item_no ASC;
     
BEGIN
   v_num :=io_result.count;
   v_num1:=0;
  OPEN my_level;
  LOOP
    FETCH my_level INTO v_assembly_item_no, v_component_item_no;
    EXIT WHEN my_level%NOTFOUND;
    v_num1:=v_num1+1;
    v_num:=v_num+1;
    io_result.extend;
    io_result(v_num):=type_seq_obj(i_seq_parent||v_num1,v_component_item_no);
    if length(i_seq_parent||v_num1)>12 then
       raise_application_error(-20001,'Beyond limit.');
    end if;
    proc_get_seq_kernal(i_seq_parent||v_num1,v_component_item_no,io_result);  
  END LOOP;
END;SQL> select * from t_component;
 
ASSEMBLY_ITEM_NO COMPONENT_ITEM_NO   QUANTITY
---------------- ----------------- ----------
A                A1                         1
A                A2                         1
A1               A11                        1
A1               A12                        1
A2               A21                        1
A2               A22                        1
 
6 rows selected
 
SQL> select * from table(func_get_seq('A'));
 
SEQ                                                                              COMPONENT
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1                                                                                A
11                                                                               A1
12                                                                               A2
112                                                                              A12
121                                                                              A21
122                                                                              A22
 
7 rows selected
 
SQL>

Answer 7

奇怪,为什么多了一行为null的?

Answer 8

找到原因了.递归过程里取v_num的位置不对.
SQL> CREATE OR REPLACE PROCEDURE proc_get_seq_kernal(i_seq_parent       VARCHAR2,
  2                                                  i_component_parent VARCHAR2,
  3                                                  io_result          IN OUT NOCOPY tbl_seq_obj) AS
  4    v_assembly_item_no  VARCHAR2(100);
  5    v_component_item_no VARCHAR2(100);
  6    v_num number;
  7    v_num1 number;
  8    CURSOR my_level IS
  9      SELECT assembly_item_no, component_item_no
 10        FROM t_component t
 11       WHERE t.assembly_item_no = i_component_parent
 12       ORDER BY t.component_item_no ASC;
 13  
 14  BEGIN
 15     v_num1:=0;
 16    OPEN my_level;
 17    LOOP
 18      FETCH my_level INTO v_assembly_item_no, v_component_item_no;
 19      EXIT WHEN my_level%NOTFOUND;
 20      v_num :=io_result.count;
 21      v_num1:=v_num1+1;
 22      v_num:=v_num+1;
 23      io_result.extend;
 24      io_result(v_num):=type_seq_obj(i_seq_parent||v_num1,v_component_item_no);
 25      if length(i_seq_parent||v_num1)>12 then
 26         raise_application_error(-20001,'Beyond limit.');
 27      end if;
 28      proc_get_seq_kernal(i_seq_parent||v_num1,v_component_item_no,io_result);
 29    END LOOP;
 30    close my_level;
 31  END;
 32  /
 
Procedure created
 
SQL> 
SQL> select * from table(func_get_seq('A'))
  2  ;
 
SEQ                                                                              COMPONENT
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1                                                                                A
11                                                                               A1
111                                                                              A11
112                                                                              A12
12                                                                               A2
121                                                                              A21
122                                                                              A22
 
7 rows selected
 
SQL>