創建基本表
-- Create table
create?table?USER_INFORMATION
(
P_ID????????????NUMBER,
USER_LOGIN_NAME?NVARCHAR2(30)
)
創建包:
create?or?replace?package?pack_test?is??
???????type?cur_test?is?ref?cursor;??
end?pack_test;?
/??
--這個不能少呀，加上這個就可以在sql/plus中運行了，這個是結束符號
創建存儲過程
create?or?replace?procedure?proc_cur(p_id?in?number,p_cur?out?pack_test.cur_test)???
is???
?????? v_sql?varchar2(400);
begin??
???????if?p_id =?0?then???
??????????open?p_cur?for?select?*?from?user_information;???
???????else???
????????? v_sql :=?'select * from user_information where id =: p_id';???
??????????open?p_cur?for?v_sql?using?p_id;???
???????end?if;???
end?proc_cur;
測試存儲過程
-- Test statements here??
set?serveroutput?on
declare???
?v_id?number?:=?0;??
?v_row USER_INFORMATION%rowtype;?? --注意這里是表名
?p_cur pack_test.cur_test;
begin???
?proc_cur(v_id, p_cur);??
?loop??
????fetch?p_cur?into?v_row;??
????exit?when?p_cur%notfound;??
??? DBMS_OUTPUT.PUT_LINE(v_row.USER_LOGIN_NAME||'='||v_row.P_ID);??
?end?loop;??
?close?p_cur;??
end;?
/??
?--語句塊結束符號
)
)
