CREATE OR REPLACE PROCEDURE TESTPROC
(
I_IDLIST IN VARCHAR2,
I_ID IN NUMBER
)
IS
TYPE TEMPTABLE IS VARRAY OF NUMBER;
TMP_TABLE TEMPTABLE:= TEMPTABLE();
TEMP_I INT:=0;
TEMP_INDEX:=-1;
LEN INT:=1;
TEMP_STR VARCHAR2:='';
BEGIN
IF I_IDLIST IS NULL OR TRIM(I_IDLIST) = '' THEN
RAISE_APPLICATION_ERROR('不能为空');
END IF;
TEMP_INDEX := INSTR(I_IDLIST,',');
WHILE TEMP_INDEX != 0 LOOP
TEMP_STR := SUBSTR(I_IDLIST,LEN,TEMP_INDEX - LEN);
TMP_TABLE.EXTEND;
TMP_TABLE(TMP_TABLE.COUNT) := TO_NUMBER(TEMP_STR) ;
LEN := TEMP_INDEX + 1;
TEMP_INDEX := INSTR(I_IDLIST,',', LEN);
END LOOP;
UPDATE T_BLO SET MAXCOUNT = MAXCOUNT + 1 WHERE ID IN (SELECT * FROM TABLE (TMP_TABLE));
END TESTPROC;对才学习ORACLE对几个不怎么了解?谁能详细说明下??谢谢!!
(
I_IDLIST IN VARCHAR2,
I_ID IN NUMBER
)
IS
TYPE TEMPTABLE IS VARRAY OF NUMBER;
TMP_TABLE TEMPTABLE:= TEMPTABLE();
TEMP_I INT:=0;
TEMP_INDEX:=-1;
LEN INT:=1;
TEMP_STR VARCHAR2:='';
BEGIN
IF I_IDLIST IS NULL OR TRIM(I_IDLIST) = '' THEN
RAISE_APPLICATION_ERROR('不能为空');
END IF;
TEMP_INDEX := INSTR(I_IDLIST,',');
WHILE TEMP_INDEX != 0 LOOP
TEMP_STR := SUBSTR(I_IDLIST,LEN,TEMP_INDEX - LEN);
TMP_TABLE.EXTEND;
TMP_TABLE(TMP_TABLE.COUNT) := TO_NUMBER(TEMP_STR) ;
LEN := TEMP_INDEX + 1;
TEMP_INDEX := INSTR(I_IDLIST,',', LEN);
END LOOP;
UPDATE T_BLO SET MAXCOUNT = MAXCOUNT + 1 WHERE ID IN (SELECT * FROM TABLE (TMP_TABLE));
END TESTPROC;对才学习ORACLE对几个不怎么了解?谁能详细说明下??谢谢!!
我想拆分出来一次更新某个表里面的内容,所以用 集合来记录我取得的内容,
可是取得集合里面内容出错SELECT * FROM TABLE (TMP_TABLE))!谢谢回复!
/ create or replace function str2numList(
p_string in varchar2
) return numTableType
as
v_str long default p_string || ',';
v_n number;
v_data numTableType := numTableType();
begin
loop
v_n := to_number(instr( v_str, ',' ));
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data(v_data.count) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr(v_str, v_n+1);
end loop;
return v_data;
end;
/create or replace procedure proc_get_favorite(
in_session_key IN dat_ct_user_session.session_key%TYPE,
in_series_id_lst IN varchar2,
in_resolution IN dat_ct_series_status.resolution%TYPE,
out_cr_favorite OUT SYS_REFCURSOR,
out_flag OUT number
)
AS
begin
update dat_ct_user_session set last_access_time = SYSTIMESTAMP where session_key = in_session_key; if SQL%NOTFOUND then
out_flag := -1;
else
open out_cr_favorite for
select ss.series_id,s.series_name,ss.max_chapter_no,ss.end_status from dat_ct_series s,dat_ct_series_status ss
where s.series_id = ss.series_id
and s.status = 'A'
and ss.resolution = in_resolution
and ss.series_id in(
select * from
THE (select cast(str2numList(in_series_id_lst) as numtableType) from dual)
)
order by s.init_letter,s.series_name;
out_flag := 0;
end if; Exception when others then
raise;
end proc_get_favorite;
/
and ss.series_id in(
select * from
THE (select cast(str2numList(in_series_id_lst) as numtableType) from dual)
)我的传入的in_series_id_lst也是你那样的'111,222,333'的样式,也是要转换成数字的,然后再用in
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (100);
CREATE OR REPLACE FUNCTION fn_split(p_str IN VARCHAR2,
p_delimiter IN VARCHAR2)
RETURN ty_str_split IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2(4000);
str_split ty_str_split := ty_str_split(); --初始化数组
BEGIN
len := LENGTH(p_str);
len1 := LENGTH(p_delimiter); WHILE j < len LOOP
j := INSTR(p_str, p_delimiter, i);
IF j = 0 THEN
j := len;
str := SUBSTR(p_str, i);
str_split.EXTEND; --扩展数组
str_split(str_split.COUNT) := str;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := SUBSTR(p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split(str_split.COUNT) := str;
END IF;
END LOOP; RETURN str_split;
END fn_split;测试:
select * from table(fn_split('aaa,bb,cc',','));
p_string in varchar2
) return numTableType
as
v_str long default p_string || ',';
v_n number;
v_data numTableType := numTableType();
begin
loop
v_n := to_number(instr( v_str, ',' ));
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data(v_data.count) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr(v_str, v_n+1);
end loop;
return v_data;
end;
我能够用SELECT * FROM TABLE(v_data);那取得数据吗??