--oracle 創建包,使用游標進行轉列顯示結果
-- Create table
create table Piece
(
name VARCHAR2(30),
type VARCHAR2(30),
area VARCHAR2(50),
piece VARCHAR2(50)
);
--插入數據
insert into piece
( name, type, area, piece)
values
('蘋果', '優', '北京', '20');
commit;
insert into piece
( name, type, area, piece)
values
('香蕉', '優', '北京', '18');
commit;
insert into piece
( name, type, area, piece)
values
('蘋果', '優', '上海', '19');
commit;
insert into piece
( name, type, area, piece)
values
('香蕉', '優', '上海', '21');
commit;
--ORACLE package的创建
--包定义
create or replace package Package_Name is
--存儲過程定義
PROCEDURE procedure_name(I_DATA1 IN VARCHAR2,--品質
O_DATA OUT SYS_REFCURSOR);
end Package_Name;
--包主體
create or replace package body Package_Name is
--存儲過程內容
PROCEDURE procedure_name(I_DATA1 IN VARCHAR2,--品質
O_DATA OUT SYS_REFCURSOR) is
sql_1 varchar(1000);
sql_2 varchar(1000);
sql_3 VARCHAR2(10000);
cur_1 SYS_REFCURSOR;--游標
V_Piece VARCHAR2(200);
begin
--行轉列
open cur_1 for
select distinct area from piece
where type=I_DATA1 order by area;
loop
fetch cur_1 into V_Piece;
exit when cur_1%notfound;
sql_3 := sql_3 || ',max(decode(t2.area,'''|| V_Piece ||''',t2.piece)) as ' || V_Piece ;
END LOOP;
sql_1:=' select t2.name 水果, type 品質';
sql_2:=' from
(
select name, type, area, piece from piece
where type='''||I_DATA1||'''
)t2 group by t2.name, type
order by name, type
';
DBMS_OUTPUT.put_line( sql_1 || sql_3 || sql_2 );
open O_DATA for sql_1 || sql_3 || sql_2;
end;
end Package_Name;