Cursor c_select_string Is Select column1,column2 From tab_source_name --Where …… --order by…… ; Begin v_proc := 'p_string_append'; v_errtext := v_proc||' process beginning'; select user into v_user from dual; v_errno := -1000; OPEN c_select_string; LOOP Fetch c_select_string Into v_compare,v_append_column; Exit When c_select_string%Notfound; Select column1 Into v_compare2 From Tab_String_Append_tmp Where column1=v_compare; If Sql%Found Then Update Tab_String_Append_tmp Set column2=column2||'_'||v_append_column Where column1=v_compare; End If; End Loop; …… select c1,c2 from Tab_String_Append_tmp …… EXCEPTION WHEN OTHERS THEN …… ROLLBACK; …… commit; RETURN; End;
select 字段1,max(sys_connect_by_path(字段2,',')) from (select t.字段1,t.字段2,rownum rn From t Where Rownum < 200 ) Group By 字段1 start with rn=1 connect by prior rn=rn-1sys_connect_by_path这个函数的确有用,9i开始有的,8i的不支持
create or replace procedure p_string_append(……)Is
--WEB交换机文件告警
v_user Varchar2(20);
v_proc Varchar2(20);
v_errno NUMBER;
v_errtext VARCHAR2(250);
v_compare Varchar2(128); --用来比较的临时变量
v_append_column Varchar2(128); --用来append的变量
v_compare_2 Varchar2(128); --variable2
Cursor c_select_string Is
Select column1,column2
From tab_source_name
--Where ……
--order by……
;
Begin
v_proc := 'p_string_append';
v_errtext := v_proc||' process beginning';
select user into v_user from dual;
v_errno := -1000; OPEN c_select_string;
LOOP
Fetch c_select_string Into v_compare,v_append_column;
Exit When
c_select_string%Notfound;
Select column1 Into v_compare2 From Tab_String_Append_tmp Where column1=v_compare;
If Sql%Found Then
Update Tab_String_Append_tmp Set column2=column2||'_'||v_append_column Where column1=v_compare;
End If;
End Loop;
……
select c1,c2 from Tab_String_Append_tmp ……
EXCEPTION
WHEN OTHERS THEN
……
ROLLBACK;
……
commit;
RETURN;
End;
from
(select t.字段1,t.字段2,rownum rn From t Where Rownum < 200 )
Group By 字段1
start with rn=1
connect by prior rn=rn-1sys_connect_by_path这个函数的确有用,9i开始有的,8i的不支持