⑴ oracle 存儲過程 數組循環
declare
type typ_rec is record of (student.name%type, student.age%type); --集合變數
type typ_tab is table of typ_rec index by binary_integer; --以集合變數為單位的table數組
rec_sql typ_rec;
another_rec student%rowtype; --跟rec_sql一樣
begin
--for循環里的rec_tmp不用定義，可以自動生成的
for rec_tmp in (select t.name, t.age from student t) loop
dbms_output.putline(rec_tmp.name || ' ''s age + 1 = ' || to_char(rec_tmp.age + 1) );
end loop;
exception
when others then
return;
end;
⑵ oracle存儲過程中循環for in是如何使用的
1、首先編寫存儲過程的整體結構，如下圖所示定義變數。
⑶ 如何在oracle建一個存儲過程來遍歷數組，新手求解
SQL code
DECLARE
-- Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
-- Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY BINARY_INTEGER;
-- Declare and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');
-- Declare an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title
DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.
FOR i IN month.FIRST..month.LAST LOOP
-- Initialize a null associative array element.
calendar(i) := '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(i) := month(i);
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;
⑷ oracle存儲過程中循環查詢返回多個結果集怎麼集合在一起
你可以先把數據集保存到array裡面，完了之後再用一次性的導出來。
又或者你可以檢查你的循環查詢，是否能用一條sql來完成。