# 一个复杂问题的求解过程（五）

SQL> CREATE OR REPLACE PROCEDURE P_RESULT AS
2   TYPE T_NUM_TAB IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3   V_NUM_TAB T_NUM_TAB;
4
5   PROCEDURE P(P_LEVEL NUMBER, P_NUM NUMBER, P_TOTAL NUMBER, P_RESULT_TAB IN OUT T_NUM_TAB) AS
6    V_RESULT NUMBER;
7    V_OUTPUT VARCHAR2(30);
8   BEGIN
9    FOR I IN
10    (
11     SELECT ROWNUM - 1 RN, (ROWNUM - 1) * VALUE VALUE
12     FROM (SELECT VALUE, POWER FROM T WHERE ID = P_LEVEL)
13     CONNECT BY ROWNUM <= POWER
14    ) LOOP
15     V_RESULT := P_TOTAL + I.VALUE;
16     IF P_TOTAL > 16 THEN
17      EXIT;
18     END IF;
19     P_RESULT_TAB(P_RESULT_TAB.COUNT + 1) := P_RESULT_TAB(P_NUM) || ',' || TO_CHAR(I.RN);
20     IF P_LEVEL > 1 THEN
21      P(P_LEVEL - 1, P_RESULT_TAB.COUNT, V_RESULT, P_RESULT_TAB);
22     ELSE
23      IF V_RESULT = 16 THEN
24       V_OUTPUT := LTRIM(P_RESULT_TAB(P_RESULT_TAB.COUNT), ',');
25       SELECT REVERSE(V_OUTPUT)
26       INTO V_OUTPUT
27       FROM DUAL;
28       DBMS_OUTPUT.PUT_LINE(V_OUTPUT);
29      END IF;
30     END IF;
31    END LOOP;
32   END;
33
34  BEGIN
35   V_NUM_TAB(1) := '';
36   FOR I IN (SELECT MAX(ID) ID FROM T) LOOP
37    P(I.ID, 1, 0, V_NUM_TAB);
38   END LOOP;
39
40  END;
41  /

SQL> SET SERVEROUT ON
SQL> EXEC P_RESULT
2,0,3,0,0
1,1,2,1,0
2,0,2,0,1
0,0,3,0,1
1,1,1,1,1
2,0,1,0,2
0,0,2,0,2
1,1,0,1,2

PL/SQL 过程已成功完成。

• 博文量
1955
• 访问量
10486843