在函数中如何调用存储

1.创建实体表及初始化数据

create table PRESIDENTS(
ID NUMBER(10) not null,
NAME VARCHAR(32) not null,
BIRTHDATE DATE not null,
PARTY char(1) not null,
primary key (ID)
)

insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (1, 'George W. Bush', to_date('07/06/1946','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (2, 'Bill Clinton', to_date('08/19/1946','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (3, 'George H. W. Bush', to_date('06/12/1924','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (4, 'Ronald W. Reagan', to_date('02/06/1911','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (5, 'Jimmy Carter', to_date('10/01/1924','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (6, 'Gerald R. Ford', to_date('07/14/1913','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (7, 'Richard M. Nixon', to_date('01/09/1913','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (8, 'Lyndon B. Johnson', to_date('08/27/1908','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (9, 'John F. Kennedy', to_date('05/29/1917','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (10, 'Dwight D. Eisenhower', to_date('10/14/1890','MM/DD/YYYY'),'R');
2.创建临时表以控制在函数与存储之间的数据

CREATE GLOBAL TEMPORARY TABLE TEMP_PRESIDENTS(
 ID NUMBER(10) not null,
 NAME VARCHAR(32) not null,
 BIRTHDATE DATE not null,
 PARTY char(1) not null
) ON COMMIT PRESERVE ROWS;

3.创建存储

CREATE PROCEDURE LOAD_TEMP_PRESIDENTS (
 partyParam CHAR
)
as
begin
 EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_PRESIDENTS';
 COMMIT;
 
 INSERT INTO TEMP_PRESIDENTS
 SELECT ID, NAME, BIRTHDATE, PARTY FROM PRESIDENTS WHERE PARTY = partyParam;
 COMMIT;
end;
/

4.创建一个需要从函数返回的数据类型

CREATE OR REPLACE TYPE PRESIDENT_TYPE AS OBJECT (
 ID NUMBER(10),
 NAME VARCHAR2(32),
 BIRTHDATE DATE,
 PARTY CHAR(1)
);

5.创建一个以PRESIDENT_TYPE为类型的表类型

CREATE OR REPLACE TYPE PRESIDENT_TYPE_TABLE AS TABLE OF PRESIDENT_TYPE;

6.创建函数

CREATE OR REPLACE FUNCTION PRESIDENTS_FUNC (
 partyParam CHAR
)
return PRESIDENT_TYPE_TABLE pipelined
is
  PRAGMA AUTONOMOUS_TRANSACTION;

  TYPE ref0 is REF CURSOR;
  myCursor ref0;
  out_rec PRESIDENT_TYPE := PRESIDENT_TYPE(0, null, null, null);
 
BEGIN
 LOAD_TEMP_PRESIDENTS(partyParam);

 open myCursor for
  select
  id,
  name,
  birthdate,
  party
  from TEMP_PRESIDENTS;
 
 LOOP FETCH myCursor into
  out_rec.ID,
  out_rec.NAME,
  out_rec.BIRTHDATE,
  out_rec.PARTY;
 
 EXIT WHEN myCursor%NOTFOUND;
 PIPE ROW(out_rec);
 END LOOP;
 CLOSE myCursor;
 
 RETURN;
END;