在其中执行不用返回结果集合的过程非常简单,但返回结果集的方法有些不同。
最近发现一个可以使用执行TOADOracle存储过程返回游标结果集的方法:
创建以下包及过程:
Package
1CREATE OR REPLACE PACKAGE pkg_side_slope
2AS
3 TYPE t_cursor IS REF CURSOR;
4
5 PROCEDURE select_all_side_slope (cur_out OUT t_cursor);
6
7 PROCEDURE insert_side_slope (
8 p_side_slope_id IN NUMBER,
9 p_side_slope_code IN VARCHAR2,
10 p_city_id IN NUMBER,
11 p_reg_id IN NUMBER,
12 p_so_id IN NUMBER,
13 p_data_src_id IN NUMBER,
14 p_disk_grade_id IN NUMBER,
15 p_side_slope_name IN VARCHAR2,
16 p_side_slope_type IN NUMBER,
17 p_create_time IN DATE,
18 p_side_slope_adress IN VARCHAR2,
19 p_dt_id IN NUMBER,
20 p_dc_id IN NUMBER,
21 p_description IN VARCHAR2,
22 p_prevent_state IN VARCHAR2,
23 p_lon IN VARCHAR2,
24 p_lat IN VARCHAR2,
25 p_xcoord IN VARCHAR2,
26 p_ycoord IN VARCHAR2,
27 p_height IN VARCHAR2,
28 p_lslength IN VARCHAR2,
29 p_angle IN VARCHAR2,
30 p_orientation IN VARCHAR2,
31 p_ss_type IN NUMBER,
32 p_work_flow_id IN NUMBER,
33 p_land_bnd IN NVARCHAR2,
34 p_danger_bnd IN VARCHAR2
35 );
36
37 PROCEDURE update_side_slope (
38 p_side_slope_id IN NUMBER,
39 p_side_slope_code IN VARCHAR2,
40 p_city_id IN NUMBER,
41 p_reg_id IN NUMBER,
42 p_so_id IN NUMBER,
43 p_data_src_id IN NUMBER,
44 p_disk_grade_id IN NUMBER,
45 p_side_slope_name IN VARCHAR2,
46 p_side_slope_type IN NUMBER,
47 p_create_time IN DATE,
48 p_side_slope_adress IN VARCHAR2,
49 p_dt_id IN NUMBER,
50 p_dc_id IN NUMBER,
51 p_description IN VARCHAR2,
52 p_prevent_state IN VARCHAR2,
53 p_lon IN VARCHAR2,
54 p_lat IN VARCHAR2,
55 p_xcoord IN VARCHAR2,
56 p_ycoord IN VARCHAR2,
57 p_height IN VARCHAR2,
58 p_lslength IN VARCHAR2,
59 p_angle IN VARCHAR2,
60 p_orientation IN VARCHAR2,
61 p_ss_type IN NUMBER,
62 p_work_flow_id IN NUMBER,
63 p_land_bnd IN NVARCHAR2,
64 p_danger_bnd IN VARCHAR2
65 );
66END pkg_side_slope;
67/
68
1CREATE OR REPLACE PACKAGE pkg_side_slope
2AS
3 TYPE t_cursor IS REF CURSOR;
4
5 PROCEDURE select_all_side_slope (cur_out OUT t_cursor);
6
7 PROCEDURE insert_side_slope (
8 p_side_slope_id IN NUMBER,
9 p_side_slope_code IN VARCHAR2,
10 p_city_id IN NUMBER,
11 p_reg_id IN NUMBER,
12 p_so_id IN NUMBER,
13 p_data_src_id IN NUMBER,
14 p_disk_grade_id IN NUMBER,
15 p_side_slope_name IN VARCHAR2,
16 p_side_slope_type IN NUMBER,
17 p_create_time IN DATE,
18 p_side_slope_adress IN VARCHAR2,
19 p_dt_id IN NUMBER,
20 p_dc_id IN NUMBER,
21 p_description IN VARCHAR2,
22 p_prevent_state IN VARCHAR2,
23 p_lon IN VARCHAR2,
24 p_lat IN VARCHAR2,
25 p_xcoord IN VARCHAR2,
26 p_ycoord IN VARCHAR2,
27 p_height IN VARCHAR2,
28 p_lslength IN VARCHAR2,
29 p_angle IN VARCHAR2,
30 p_orientation IN VARCHAR2,
31 p_ss_type IN NUMBER,
32 p_work_flow_id IN NUMBER,
33 p_land_bnd IN NVARCHAR2,
34 p_danger_bnd IN VARCHAR2
35 );
36
37 PROCEDURE update_side_slope (
38 p_side_slope_id IN NUMBER,
39 p_side_slope_code IN VARCHAR2,
40 p_city_id IN NUMBER,
41 p_reg_id IN NUMBER,
42 p_so_id IN NUMBER,
43 p_data_src_id IN NUMBER,
44 p_disk_grade_id IN NUMBER,
45 p_side_slope_name IN VARCHAR2,
46 p_side_slope_type IN NUMBER,
47 p_create_time IN DATE,
48 p_side_slope_adress IN VARCHAR2,
49 p_dt_id IN NUMBER,
50 p_dc_id IN NUMBER,
51 p_description IN VARCHAR2,
52 p_prevent_state IN VARCHAR2,
53 p_lon IN VARCHAR2,
54 p_lat IN VARCHAR2,
55 p_xcoord IN VARCHAR2,
56 p_ycoord IN VARCHAR2,
57 p_height IN VARCHAR2,
58 p_lslength IN VARCHAR2,
59 p_angle IN VARCHAR2,
60 p_orientation IN VARCHAR2,
61 p_ss_type IN NUMBER,
62 p_work_flow_id IN NUMBER,
63 p_land_bnd IN NVARCHAR2,
64 p_danger_bnd IN VARCHAR2
65 );
66END pkg_side_slope;
67/
68
Package Body
1CREATE OR REPLACE PACKAGE BODY pkg_side_slope
2AS
3 PROCEDURE select_all_side_slope (cur_out OUT t_cursor)
4 IS
5 BEGIN
6 OPEN cur_out FOR
7 SELECT *
8 FROM side_slope;
9 END;
10
11 PROCEDURE insert_side_slope (
12 p_side_slope_id IN NUMBER,
13 p_side_slope_code IN VARCHAR2,
14 p_city_id IN NUMBER,
15 p_reg_id IN NUMBER,
16 p_so_id IN NUMBER,
17 p_data_src_id IN NUMBER,
18 p_disk_grade_id IN NUMBER,
19 p_side_slope_name IN VARCHAR2,
20 p_side_slope_type IN NUMBER,
21 p_create_time IN DATE,
22 p_side_slope_adress IN VARCHAR2,
23 p_dt_id IN NUMBER,
24 p_dc_id IN NUMBER,
25 p_description IN VARCHAR2,
26 p_prevent_state IN VARCHAR2,
27 p_lon IN VARCHAR2,
28 p_lat IN VARCHAR2,
29 p_xcoord IN VARCHAR2,
30 p_ycoord IN VARCHAR2,
31 p_height IN VARCHAR2,
32 p_lslength IN VARCHAR2,
33 p_angle IN VARCHAR2,
34 p_orientation IN VARCHAR2,
35 p_ss_type IN NUMBER,
36 p_work_flow_id IN NUMBER,
37 p_land_bnd IN NVARCHAR2,
38 p_danger_bnd IN VARCHAR2
39 )
40 IS
41 BEGIN
42 INSERT INTO side_slope
43 (side_slope_id, side_slope_code, city_id, reg_id,
44 so_id, data_src_id, disk_grade_id,
45 side_slope_name, side_slope_type, create_time,
46 side_slope_adress, dt_id, dc_id, description,
47 prevent_state, lon, lat, xcoord, ycoord,
48 height, lslength, angle, orientation, ss_type,
49 work_flow_id, land_bnd, danger_bnd
50 )
51 VALUES (p_side_slope_id, p_side_slope_code, p_city_id, p_reg_id,
52 p_so_id, p_data_src_id, p_disk_grade_id,
53 p_side_slope_name, p_side_slope_type, p_create_time,
54 p_side_slope_adress, p_dt_id, p_dc_id, p_description,
55 p_prevent_state, p_lon, p_lat, p_xcoord, p_ycoord,
56 p_height, p_lslength, p_angle, p_orientation, p_ss_type,
57 p_work_flow_id, p_land_bnd, p_danger_bnd
58 );
59 END insert_side_slope;
60
61 PROCEDURE update_side_slope (
62 p_side_slope_id IN NUMBER,
63 p_side_slope_code IN VARCHAR2,
64 p_city_id IN NUMBER,
65 p_reg_id IN NUMBER,
66 p_so_id IN NUMBER,
67 p_data_src_id IN NUMBER,
68 p_disk_grade_id IN NUMBER,
69 p_side_slope_name IN VARCHAR2,
70 p_side_slope_type IN NUMBER,
71 p_create_time IN DATE,
72 p_side_slope_adress IN VARCHAR2,
73 p_dt_id IN NUMBER,
74 p_dc_id IN NUMBER,
75 p_description IN VARCHAR2,
76 p_prevent_state IN VARCHAR2,
77 p_lon IN VARCHAR2,
78 p_lat IN VARCHAR2,
79 p_xcoord IN VARCHAR2,
80 p_ycoord IN VARCHAR2,
81 p_height IN VARCHAR2,
82 p_lslength IN VARCHAR2,
83 p_angle IN VARCHAR2,
84 p_orientation IN VARCHAR2,
85 p_ss_type IN NUMBER,
86 p_work_flow_id IN NUMBER,
87 p_land_bnd IN NVARCHAR2,
88 p_danger_bnd IN VARCHAR2
89 )
90 AS
91 BEGIN
92 UPDATE side_slope
93 SET side_slope_id = p_side_slope_id,
94 side_slope_code = p_side_slope_code,
95 side_slope_name = p_side_slope_name,
96 side_slope_type = p_side_slope_type,
97 create_time = p_create_time,
98 side_slope_adress = p_side_slope_adress,
99 dt_id = p_dt_id,
100 description = p_description,
101 prevent_state = p_prevent_state,
102 lon = p_lon,
103 lat = p_lat,
104 xcoord = p_xcoord,
105 ycoord = p_ycoord,
106 height = p_height,
107 lslength = p_lslength,
108 angle = p_angle,
109 orientation = p_orientation,
110 work_flow_id = p_work_flow_id,
111 land_bnd = p_land_bnd,
112 danger_bnd = p_danger_bnd
113 WHERE side_slope_id = p_side_slope_id;
114 END;
115END pkg_side_slope;
116/
117
1CREATE OR REPLACE PACKAGE BODY pkg_side_slope
2AS
3 PROCEDURE select_all_side_slope (cur_out OUT t_cursor)
4 IS
5 BEGIN
6 OPEN cur_out FOR
7 SELECT *
8 FROM side_slope;
9 END;
10
11 PROCEDURE insert_side_slope (
12 p_side_slope_id IN NUMBER,
13 p_side_slope_code IN VARCHAR2,
14 p_city_id IN NUMBER,
15 p_reg_id IN NUMBER,
16 p_so_id IN NUMBER,
17 p_data_src_id IN NUMBER,
18 p_disk_grade_id IN NUMBER,
19 p_side_slope_name IN VARCHAR2,
20 p_side_slope_type IN NUMBER,
21 p_create_time IN DATE,
22 p_side_slope_adress IN VARCHAR2,
23 p_dt_id IN NUMBER,
24 p_dc_id IN NUMBER,
25 p_description IN VARCHAR2,
26 p_prevent_state IN VARCHAR2,
27 p_lon IN VARCHAR2,
28 p_lat IN VARCHAR2,
29 p_xcoord IN VARCHAR2,
30 p_ycoord IN VARCHAR2,
31 p_height IN VARCHAR2,
32 p_lslength IN VARCHAR2,
33 p_angle IN VARCHAR2,
34 p_orientation IN VARCHAR2,
35 p_ss_type IN NUMBER,
36 p_work_flow_id IN NUMBER,
37 p_land_bnd IN NVARCHAR2,
38 p_danger_bnd IN VARCHAR2
39 )
40 IS
41 BEGIN
42 INSERT INTO side_slope
43 (side_slope_id, side_slope_code, city_id, reg_id,
44 so_id, data_src_id, disk_grade_id,
45 side_slope_name, side_slope_type, create_time,
46 side_slope_adress, dt_id, dc_id, description,
47 prevent_state, lon, lat, xcoord, ycoord,
48 height, lslength, angle, orientation, ss_type,
49 work_flow_id, land_bnd, danger_bnd
50 )
51 VALUES (p_side_slope_id, p_side_slope_code, p_city_id, p_reg_id,
52 p_so_id, p_data_src_id, p_disk_grade_id,
53 p_side_slope_name, p_side_slope_type, p_create_time,
54 p_side_slope_adress, p_dt_id, p_dc_id, p_description,
55 p_prevent_state, p_lon, p_lat, p_xcoord, p_ycoord,
56 p_height, p_lslength, p_angle, p_orientation, p_ss_type,
57 p_work_flow_id, p_land_bnd, p_danger_bnd
58 );
59 END insert_side_slope;
60
61 PROCEDURE update_side_slope (
62 p_side_slope_id IN NUMBER,
63 p_side_slope_code IN VARCHAR2,
64 p_city_id IN NUMBER,
65 p_reg_id IN NUMBER,
66 p_so_id IN NUMBER,
67 p_data_src_id IN NUMBER,
68 p_disk_grade_id IN NUMBER,
69 p_side_slope_name IN VARCHAR2,
70 p_side_slope_type IN NUMBER,
71 p_create_time IN DATE,
72 p_side_slope_adress IN VARCHAR2,
73 p_dt_id IN NUMBER,
74 p_dc_id IN NUMBER,
75 p_description IN VARCHAR2,
76 p_prevent_state IN VARCHAR2,
77 p_lon IN VARCHAR2,
78 p_lat IN VARCHAR2,
79 p_xcoord IN VARCHAR2,
80 p_ycoord IN VARCHAR2,
81 p_height IN VARCHAR2,
82 p_lslength IN VARCHAR2,
83 p_angle IN VARCHAR2,
84 p_orientation IN VARCHAR2,
85 p_ss_type IN NUMBER,
86 p_work_flow_id IN NUMBER,
87 p_land_bnd IN NVARCHAR2,
88 p_danger_bnd IN VARCHAR2
89 )
90 AS
91 BEGIN
92 UPDATE side_slope
93 SET side_slope_id = p_side_slope_id,
94 side_slope_code = p_side_slope_code,
95 side_slope_name = p_side_slope_name,
96 side_slope_type = p_side_slope_type,
97 create_time = p_create_time,
98 side_slope_adress = p_side_slope_adress,
99 dt_id = p_dt_id,
100 description = p_description,
101 prevent_state = p_prevent_state,
102 lon = p_lon,
103 lat = p_lat,
104 xcoord = p_xcoord,
105 ycoord = p_ycoord,
106 height = p_height,
107 lslength = p_lslength,
108 angle = p_angle,
109 orientation = p_orientation,
110 work_flow_id = p_work_flow_id,
111 land_bnd = p_land_bnd,
112 danger_bnd = p_danger_bnd
113 WHERE side_slope_id = p_side_slope_id;
114 END;
115END pkg_side_slope;
116/
117
在TOAD中执行过程select_all_side_slope,并返回游标结果集的方法为:
Execute
1BEGIN
2 pkg_side_slope.select_all_side_slope (cur_out=>:cur_out);
3END;
按F9后,将输出参数设置为cursor类型,即可在运行结果的列表中看到执行后返回的数据。1BEGIN
2 pkg_side_slope.select_all_side_slope (cur_out=>:cur_out);
3END;
本人在返回多个游标时,发现列表中还是只显示第一个游标的结果,不知哪位高手知道
如何查看?