oracle生成一定长度字符串的所有可能组合 [英] Generate all possible combinations of strings of certain length in oracle

问题描述

我编写了一个程序来生成长度为 2 的字符串的所有可能组合.程序如下:

I have written a program to generate all possible combinations of strings of length two. The program is as follows:

CREATE OR REPLACE PROCEDURE string_combinations
AS
  vblString1   VARCHAR2(100);
  vblString2   VARCHAR2(100);
  vblChr1      NUMBER;
  vblChr2      NUMBER;
BEGIN
  vblChr1 := 65;
  LOOP
    SELECT Chr(vblChr1) INTO vblString1 FROM dual;
    vblChr2 := 65;
    LOOP
      vblString2 := vblString1||Chr(vblChr2);
      Dbms_Output.put_line(vblString2);
      vblChr2:=vblChr2+1;
      EXIT WHEN vblChr2=91;
    END LOOP;
  vblChr1:=vblChr1+1;
  EXIT WHEN vblChr1=91;
  END LOOP;
END;
/

我在另一个循环中使用了一个循环.所以，如果我必须生成长度为 3 的字符串，我可以简单地使用另一个循环.但是，如果我希望生成长度为 5、6、7 或更长的字符串，那将会很冗长.如何使用递归来实现它?我正在使用oracle.

I have used a loop inside another loop. So, if I have to generate strings of length three, I can simply use another loop. But that would be lengthy if I wish to generate strings of length 5,6,7 or more. How can I use recursion to achieve it? I am using oracle.

推荐答案

您不需要 PL/SQL 来生成按字母顺序排列的序列.您可以使用 Row Generator 方法在纯 SQL 中完成.

You don't need PL/SQL to generate an alphabetical sequence. You could do it in pure SQL using Row Generator method.

WITH combinations AS
  (SELECT chr( ascii('A')+level-1 ) c FROM dual CONNECT BY level <= 26
  )
SELECT * FROM combinations
UNION ALL
SELECT c1.c || c2.c FROM combinations c1, combinations c2
UNION ALL
SELECT c1.c
  || c2.c
  || c3.c
FROM combinations c1,
  combinations c2,
  combinations c3
/

以上将为您提供单个和两个字符的所有可能组合 c1、c2、c3.对于更多组合，您可以添加组合如 c4、c5 等

The above would give you all possible combinations c1, c2, c3 for single and two characters. For more combinations, you could just add combinations as c4, c5 etc.

这篇关于oracle生成一定长度字符串的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！