新出炉的SQL面试题

Question

表T 如下：
phone_head  cust
075588132    41
07558        42
075588131    37
075588       40编写一个存储过程，输入 phone_head 返回一个 cust.
要求：
1. 输入的phone_head 匹配表T中长度最大的 phone_head.
   如输入 0755881327089 则输入应该是 41
2. 在表T上建一个索引，此过程要效率很高！
3. 此过程唯一的要求就是高效。反正面试官反复强调的就是要高效，看看各位的想法，

Answer 1

-- 1. 创建索引
create index idx_t_phone_head on t(phone_head);-- 创建存储过程
create or replace procedure find_phone_head(ph varchar2, pc out number) is
  v_ph varchar2 t.phone_head%type;
  v_pc number;
begin
  v_ph := ph;
  while length(v_ph) > 0 loop
    begin
      select cust into v_pc from t where phone_head = v_ph;
      pc := v_pc;
      return;
    exception
      when no_data_found then
        null;
      when others then
        return;
    end;
    v_ph := substr(v_ph, 1, length(v_ph) - 1);
  end loop;
end;

Answer 2

drop table tt;
create table tt (phone_head varchar2(20), cust number);
create index idx_t_phone_head on tt(phone_head);
insert into tt values ('075588132', 41);
insert into tt values ('07558', 42);
insert into tt values ('075588131', 37);
insert into tt values ('075588', 40);
commit;create or replace procedure find_phone_head(ph varchar2, pc out number) is
  v_ph tt.phone_head%type;
  v_pc number;
begin
  v_ph := ph;
  while length(v_ph) > 0 loop
    begin
      select cust into v_pc from tt where phone_head = v_ph;
      pc := v_pc;
      return;
    exception
      when no_data_found then
        null;
      when others then
        return;
    end;
    v_ph := substr(v_ph, 1, length(v_ph) - 1);
  end loop;
end;declare
  cc number;
begin
  find_phone_head('0755881327089',cc);
  dbms_output.put_line(cc);
end;-- 结果
41

Answer 3

如果两个长度一样， 取哪个啊？
比如输入phone_head为07558813，那么cust 是41 还是37 啊？
这个有没有什么规则呢？

Answer 4

--我觉得创建函数索引要好点，
--2楼方法固然好，但是循环次数多了drop table tt;
create table tt (phone_head varchar2(20), cust number);
--创建一个函数index
create index idx_t_phone_head on tt(Length(phone_head));insert into tt values ('075588132', 41);
insert into tt values ('07558', 42);
insert into tt values ('075588131', 37);
insert into tt values ('075588', 40);
commit;--创建过程，
--效率高原因：循环次数少 最多Max(Length(phone_head))
--可能有一个地方还待改善：就是求最大长度
--select Max(Length(phone_head)) into max_len from tt  ;
--这样会全表扫描，如果表数据很大，开销当然也比较高了
--暂时只想到这样做
create or replace procedure find_phone_head(ph varchar2, pc out number) 
is
  v_ph tt.phone_head%type;
  v_pc number;
  max_len number;  --加一个参数，表tt中phone_head的最大长度
begin
  v_ph := ph;
  --求tt表中phone_head的最大长度
  select Max(Length(phone_head)) into max_len from tt  ;  if max_len<= length(v_ph) THEN
     select cust into pc from tt where Length(phone_head) = max_len and rownum=1;
  else
      for i in 1..length(v_ph) LOOP
          --1、长度正好
          begin
              select cust into pc from tt where Length(phone_head) = length(v_ph);
              exit;
          exception
              when no_data_found THEN
                  --2、向上增加
                  begin
                      select cust into pc from tt where Length(phone_head) = length(v_ph)+i and rownum=1;
                      exit ;
                  exception when no_data_found then
                      --3、再向下递减
                      select cust into pc from tt where Length(phone_head) = length(v_ph)-i and rownum=1;
                      exit ;
                  end;
              when others then
                  exit;
          end;
      end loop;
  end if;
end;declare
  cc number;
begin
  find_phone_head('0755881327089',cc);
  dbms_output.put_line(cc);
end;    PL/SQL block, executed in 0.015 sec.
    41                                  
    Total execution time 0.015 sec.     

Answer 5

呵呵,做个极端点的.
SQL> select phone_head, cust
  2    from (select phone_head, cust, row_number() over(order by rn desc) rnum
  3            from tablet a,
  4                 (select rownum rn
  5                    from dual
  6                  connect by rownum < length('0755881327089') - 1) b
  7           where a.phone_head = substr('0755881327089', 1, rn))
  8   where rnum = 1;
 
PHONE_HEAD                                                                              CUST
-------------------------------------------------------------------------------- -----------
075588132                                                                                 41
                                                                       41
SQL> explain plan for select phone_head, cust
  2  from (select phone_head, cust, row_number() over(order by rn desc) rnum
  3            from tablet a,
  4  
  4                 (select rownum rn
  5                    from dual
  6                  connect by rownum < length('0755881327089') - 1) b
  7           where a.phone_head = substr('0755881327089', 1, rn))
  8   where rnum = 1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3092622286
--------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |    78 |     4  (2
|*  1 |  VIEW                             |          |     1 |    78 |     4  (2
|*  2 |   WINDOW SORT PUSHED RANK         |          |     1 |    36 |     4  (2
|   3 |    NESTED LOOPS                   |          |     1 |    36 |     3   (
|   4 |     VIEW                          |          |     1 |    13 |     2   (
|   5 |      COUNT                        |          |       |       |
|   6 |       CONNECT BY WITHOUT FILTERING|          |       |       |
|   7 |        FAST DUAL                  |          |     1 |       |     2   (
|   8 |     TABLE ACCESS BY INDEX ROWID   | TABLET   |     1 |    23 |     1   (
|*  9 |      INDEX UNIQUE SCAN            | TABLET_1 |     1 |       |     0   (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("RNUM"=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "RN" DESC )<=1)
   9 - access("A"."PHONE_HEAD"=SUBSTR('0755881327089',1,"RN"))
 
23 rows selected
 
SQL> select count(*) from tablet;
 
  COUNT(*)
----------
     10005
 
SQL> 

Answer 6

以前一直遵循能用sql尽量不要用过程的做法.现在看来在过程里做精确控制也能够提高不少效率.深受启发.

Answer 7

借用2楼的数据，改进了循环次数11:26:30 SQL> edi
已写入 file afiedt.buf  1  create or replace procedure return_cust(pno tt.phone_head%type,num out number)
  2  as
  3  v_pho tt.phone_head%type;
  4  begin
  5  v_pho:=pno;
  6  loop
  7  begin
  8  select cust into num from tt where phone_head=v_pho;
  9  exit when num is not null;
 10  exception
 11  when others then
 12  null;
 13  end;
 14  v_pho:=substr(v_pho,1,length(v_pho)-1);
 15  end loop;
 16* end;
11:28:16 SQL> /过程已创建。11:28:17 SQL> declare
11:28:23   2  v_pho tt.phone_head%type;
11:28:23   3  v_mum number;
11:28:23   4  begin
11:28:23   5  v_pho:='0755881327089';
11:28:23   6  return_cust(v_pho,v_mum);
11:28:23   7  dbms_output.put_line(v_mum);
11:28:23   8  end;
11:28:24   9  /
41PL/SQL 过程已成功完成。

Answer 8

SQL> create or replace procedure return_cust(p_hone in aa.phone_head%type,c_num out aa.num%type)
  2  as
  3  s_phone aa.phone_head%type:=p_hone;
  4  l number :=length(s_phone);
  5  begin
  6   for i in reverse 1 .. l loop
  7    begin
  8     select num into c_num from aa where aa.phone_head=s_phone;
  9     exit when c_num is not null;
 10     exception
 11      when others then
 12       null;
 13     end;
 14     s_phone := substr(s_phone,1,i-1);
 15   end loop;
 16  end;
 17  /Procedure created.SQL> declare    
  2     p_hone aa.phone_head%type:='0755881327089';
  3     c_num aa.num%type;
  4   begin
  5    return_cust(p_hone,c_num);
  6    dbms_output.put_line(c_num);
  7   end;
  8  /
41PL/SQL procedure successfully completed.