PLSQL – 使用ROWTYPE 回傳值
Author: Willie
Date: 2012/05/16
目的：Function Return Row, 可以方便使用Function來更新欄位
範例程式
// Package 的表頭
create or replace package WT_CST_TESET is //宣告要回傳的Row裡面有哪些欄位 cursor cr_po_line is
select
l.po_line_id,l.po_header_id,l.item_id, l.unit_price
from po_lines_all l;
procedure main;
function get_lines( po_line_id number)
return cr_po_line%rowtype;
end
WT_CST_TESET;
/
//Package 的表身
create or
replace package body WT_CST_TESET is
procedure main is
r_line cr_po_line%rowtype;
cursor c1 is
select
l.po_line_id,l.po_header_id,l.item_id, l.unit_price
from po_lines_all l
where PO_LINE_ID >=3205
AND PO_LINE_ID<=4000 ;
begin
for r
//取得Row的回傳
r_line:=get_lines(r1.po_line_id);
update po_lines_all pal
set pal.po_line_id =
r_line.po_line_id,
pal.item_id = r_line.item_id
where pal.po_line_id = r1.po_line_id;
end loop;
end main;
//回傳整筆Row的Function,利用 cr_po_line 的row type做回傳
function get_lines( po_line_id number)
return cr_po_line%rowtype
is
return_row cr_po_line%rowtype;
begin
select
l.po_line_id,l.po_header_id,l.item_id, l.unit_price
into return_row
from po_lines_all l
where l.po_line_id = po_line_id;
return return_row;
end get_lines;
end
WT_CST_TESET;
/
|
