Dynamic Cursor in plsql (2)
获取cursor行数：
当游标被打开后，％ROWCOUNT归零。第一次提取之前，cursor_name％ROWCOUNT返回0。此后，它返回的fetch到的行数。完成一次fetch操作后，count+1。
获取cursor 列数：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
--方法一：CREATE OR REPLACE FUNCTION count_sql( p_sql IN CLOB )RETURN INTEGERAS lv_cursor_id INTEGER; lv_columns DBMS_SQL.DESC_TAB; lv_column_count INTEGER;BEGIN -- Open Cursor lv_cursor_id := DBMS_SQL.OPEN_CURSOR; -- Parse Cursor DBMS_SQL.PARSE ( c => lv_cursor_id , statement => p_sql , language_flag => DBMS_SQL.NATIVE ); -- Describe Columns DBMS_SQL.DESCRIBE_COLUMNS ( c => lv_cursor_id , col_cnt => lv_column_count , desc_t => lv_columns ); -- Close Cursor DBMS_SQL.CLOSE_CURSOR(lv_cursor_id); RETURN lv_column_count;END count_sql;/SELECT count_sql('SELECT dummy, dummy, dummy, ''Y'' FROM DUAL') FROM DUAL;COUNT_SQL('SELECTDUMMY,DUMMY,DUMMY,''Y''FROMDUAL')-------------------------------------------------- 4================================================================--方法二：SQL> select column_value cnt from xmltable('count(distinct-values(for $i in ROW/* return name($i)))' passing dbms_xmlgen.getxmltype('select ename, sal, hiredate, deptno from emp').extract('ROWSET/ROW'))CNT -----4 1 row selected.SQL> select column_value cnt from xmltable('count(distinct-values(for $i in ROW/* return name($i)))' passing dbms_xmlgen.getxmltype('select sal, job, deptno from emp').extract('ROWSET/ROW'))CNT -----3 1 row selected. |
动态执行sql语句的函数：
func_dynamic_fetch（l_sql);----l_sql 为sql语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
CREATE OR REPLACE TYPE AnydataList FORCE IS VARRAY (2147483647) OF ANYDATA ;/CREATE OR REPLACE TYPE Varchar2List FORCE IS VARRAY (2147483647) OF VARCHAR2 (32767) ;/create or replace function func_dynamic_fetch ( p_sql in varchar2, p_no_of_cols integer := 1, p_return_multiple_row boolean := true) return anydatalist is lc_rs sys_refcursor; strlist varchar2List := varchar2List(); n number := 3; code varchar2(32767); TYPE ref_cursor IS REF CURSOR; rc_ ref_cursor; c_ NUMBER; i_ NUMBER; col_count_ NUMBER; desc_tab_ DBMS_SQL.DESC_TAB2; col_num NUMBER; n2 number; l_any_list anydatalist; l_types varchar2list; l_add_stmts varchar2list; l_fetch_type varchar2(32767); c_date_type constant number := 12; c_timestamp_type constant number := 180; function repeatStmt(n in integer, template in varchar2, seperator in varchar2 := '', vals in varchar2list := null) return varchar2 is l_result varchar2(32767); l_stmt varchar2(32767); begin for i in 1..n loop l_stmt := template; if vals is not null then l_stmt := replace(template, '__val__', vals(i)); end if; l_result := l_result || replace(l_stmt, '__$__', i); if i < n then l_result := l_result || seperator; end if; end loop; return l_result; end repeatStmt;begin open rc_ for p_sql; n2 := p_no_of_cols; c_ := DBMS_SQL.to_cursor_number(rc_); DBMS_SQL.DESCRIBE_COLUMNS2(c_, col_count_, desc_tab_); col_num := desc_tab_.first; l_types := varchar2list(); l_add_stmts := varchar2list(); if col_num is not null then loop if desc_tab_(col_num).col_type = c_date_type or desc_tab_(col_num).col_type =c_timestamp_type then varchar2listAdd(l_types, 'date'); --to conform to jdbc date format varchar2listAdd(l_add_stmts, 'varchar2ListAdd(l_str_list, to_char(a__$__, ''yyyy-mm-dd hh:mi:ss''));'); else varchar2listAdd(l_types, 'varchar2(32767)'); varchar2listAdd(l_add_stmts, 'varchar2ListAdd(l_str_list, a__$__);'); end if; col_num:= desc_tab_.next(col_num); exit when (col_num is null); end loop; end if; n := col_count_; if n2 > n then n2 := n; end if; dbms_sql.close_cursor(c_); code := ' declare lc_rs sys_refcursor; l_sql varchar2(32767); l_str_list varchar2list; l_counter integer; l_any_list anydatalist; ' || repeatStmt(n, 'a__$__ __val__; ', seperator => chr(10), vals => l_types) || ' begin l_sql := :0; --dbms_output.put_line(l_sql); open lc_rs for l_sql; l_any_list := anydatalist(); l_counter := 0; loop fetch lc_rs into ' || repeatStmt(n, 'a__$__', ', ') || ';' || ' exit when lc_rs%notfound; l_counter := l_counter + 1; l_str_list := varchar2List();' || repeatStmt(n2, '__val__', chr(10), vals => l_add_stmts) || ' anydataListAdd(l_any_list, anydata.convertCollection(l_str_list)); ' || (case when p_return_multiple_row = false then 'exit when l_counter = 1;' end) || ' end loop; if lc_rs%isopen then close lc_rs; end if; :1 := l_any_list; exception when others then dbms_output.put_line('' func_dynamic_fetch exec immediate exception: '' || sqlerrm); raise; if lc_rs%isopen then close lc_rs; end if; end; ' ; execute immediate code using p_sql, out l_any_list; return l_any_list;exception when others then if rc_%isopen then close rc_; end if; dbms_output.put_line('func_dynamic_fetch: exception: ' || sqlerrm); return anydatalist();end; /show errors; |
