You can incorporate conditional control in PL/SQL with IF / ELSE / END IF.
1E6 and 1E7 mean 1 multiplied by 10^6 and 10^7 respectively:
1E6 and 1E7 mean 1 multiplied by 10^6 and 10^7 respectively:
SQL> SET ECHO ON SERVEROUTPUT ON TAB OFF TRIMSPOOL ON
SQL> DECLARE
2 CURSOR C1 IS
3 SELECT BYTES FROM DBA_SEGMENTS
4 WHERE BYTES IS NOT NULL;
5 SMALL NUMBER;
6 MEDIUM NUMBER;
7 LARGE NUMBER;
8 BEGIN
9 SMALL := 0;
10 MEDIUM := 0;
11 LARGE := 0;
12 FOR X IN C1
13 LOOP
14 IF X.BYTES < 1E6 THEN
15 SMALL := SMALL + 1;
16 ELSE
17 IF X.BYTES >= 1E7 THEN
18 LARGE := LARGE + 1;
19 ELSE
20 MEDIUM := MEDIUM + 1;
21 END IF;
22 END IF;
23 END LOOP;
24 DBMS_OUTPUT.PUT_LINE('Small Segments: '||SMALL);
25 DBMS_OUTPUT.PUT_LINE('Medium Segments: '||MEDIUM);
26 DBMS_OUTPUT.PUT_LINE('Large Segments: '||LARGE);
27 END;
28 /
Small Segments: 3113
Medium Segments: 1596
Large Segments: 608
PL/SQL procedure successfully completed.
SQL>
This example uses ELSIF instead of ELSE ... IF:
SQL> DECLARE
2 CURSOR C1 IS
3 SELECT BYTES FROM DBA_SEGMENTS
4 WHERE BYTES IS NOT NULL;
5 SMALL NUMBER;
6 MEDIUM NUMBER;
7 LARGE NUMBER;
8 BEGIN
9 SMALL := 0;
10 MEDIUM := 0;
11 LARGE := 0;
12 FOR X IN C1
13 LOOP
14 IF X.BYTES < 1E6 THEN
15 SMALL := SMALL + 1;
16 ELSIF X.BYTES >= 1E7 THEN
17 LARGE := LARGE + 1;
18 ELSE
19 MEDIUM := MEDIUM + 1;
20 END IF;
21 END LOOP;
22 DBMS_OUTPUT.PUT_LINE('Small Segments: '||SMALL);
23 DBMS_OUTPUT.PUT_LINE('Medium Segments: '||MEDIUM);
24 DBMS_OUTPUT.PUT_LINE('Large Segments: '||LARGE);
25 END;
26 /
Small Segments: 3113
Medium Segments: 1596
Large Segments: 608
PL/SQL procedure successfully completed.
SQL>
No comments:
Post a Comment