本章内容:
1. EXECUTE IMMEDIATE语句
2. OPEN-FOR、FETCH和CLOSE语句
代码如下:
2. OPEN-FOR、FETCH和CLOSE语句
SQL> DECLARE
2 TYPE zip_cur_type IS REF CURSOR;
3 zip_cur zip_cur_type;
4 sql_stmt VARCHAR2(500);
5 v_zip VARCHAR2(5);
6 v_total NUMBER;
7 v_count NUMBER;
8 BEGIN
9 sql_stmt := 'SELECT zip, COUNT(*) total'||
10 ' FROM student ' ||
11 'GROUP BY zip';
12
13 v_count := 0;
14 OPEN zip_cur FOR sql_stmt;
15 LOOP
16 FETCH zip_cur INTO v_zip, v_total;
17 EXIT WHEN zip_cur%NOTFOUND;
18
19 -- Limit the number of lines printed on the
20 -- screen to 10
21 v_count := v_count + 1;
22 IF v_count <= 10 THEN
23 DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
24 ' Total: '||v_total);
25 END IF;
26 END LOOP;
27 CLOSE zip_cur;
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 IF zip_cur%ISOPEN THEN
32 CLOSE zip_cur;
33 END IF;
34
35 DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
36 END;
37 /
Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 5
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3
SQL> DECLARE
2 TYPE zip_cur_type IS REF CURSOR;
3 zip_cur zip_cur_type;
4
5
6 v_table_name VARCHAR2(20) := '&sv_table_name';
7 sql_stmt VARCHAR2(500);
8 v_zip VARCHAR2(5);
9 v_total NUMBER;
10
11 v_count NUMBER;
12 BEGIN
13 DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
14 ' table');
15
16 sql_stmt := 'SELECT zip, COUNT(*) total'||
17 ' FROM '||v_table_name||' '||
18 'GROUP BY zip';
19
20 v_count := 0;
21 OPEN zip_cur FOR sql_stmt;
22 LOOP
23 FETCH zip_cur INTO v_zip, v_total;
24 EXIT WHEN zip_cur%NOTFOUND;
25
26 -- Limit the number of lines printed on the
27 -- screen to 10
28 v_count := v_count + 1;
29 IF v_count <= 10 THEN
30 DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
31 ' Total: '||v_total);
32 END IF;
33 END LOOP;
34 CLOSE zip_cur;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 IF zip_cur%ISOPEN THEN
39 CLOSE zip_cur;
40 END IF;
41
42 DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
43 END;
44
45 /
Enter value for sv_table_name: student
old 6: v_table_name VARCHAR2(20) := '&sv_table_name';
new 6: v_table_name VARCHAR2(20) := 'student';
Totals from student table
Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 5
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3
SQL> DECLARE
2 TYPE zip_cur_type IS REF CURSOR;
3 zip_cur zip_cur_type;
4
5 TYPE zip_rec_type IS RECORD
6 (zip VARCHAR2(5),
7 total NUMBER);
8 zip_rec zip_rec_type;
9
10 v_table_name VARCHAR2(20) := '&sv_table_name';
11 sql_stmt VARCHAR2(500);
12 v_count NUMBER;
13 BEGIN
14 DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
15 ' table');
16 sql_stmt := 'SELECT zip, COUNT(*) total'||
17 ' FROM '||v_table_name||' '||
18 'GROUP BY zip';
19 v_count := 0;
20 OPEN zip_cur FOR sql_stmt;
21 LOOP
22 FETCH zip_cur INTO zip_rec;
23 EXIT WHEN zip_cur%NOTFOUND;
24
25 -- Limit the number of lines printed on the
26 -- screen to 10
27 v_count := v_count + 1;
28 IF v_count <= 10 THEN
29 DBMS_OUTPUT.PUT_LINE ('Zip code: '||zip_rec.zip||
30 ' Total: '||zip_rec.total);
31 END IF;
32 END LOOP;
33 CLOSE zip_cur;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37 IF zip_cur%ISOPEN THEN
38 CLOSE zip_cur;
39 END IF;
40
41 DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
42 END;
43 /
Enter value for sv_table_name: student
old 10: v_table_name VARCHAR2(20) := '&sv_table_name';
new 10: v_table_name VARCHAR2(20) := 'student';
Totals from student table
Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 5
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3