schema.sql

occi简单的例子

-- schema.sql - PL/SQL procedures/functions for OCCI sample

connect scott/tiger;

drop table Books;
drop type KeywordsList;
drop package OCCIDemo1;

create type KeywordsList as varray(20) of varchar2(30)
/

create table Books
(
   Id         number(10),      
   Title      varchar2(100), 
   Author     varchar2(100), 
   Price      number(10,2),
   PubDate    date,
   Keywords   KeywordsList
);

-- insert some rows
insert into Books values (1, 'The C++ Programming Language', 
    'Bjarne Stroustrup', 35.75, TO_DATE('1-1-2000','dd-mm-yyyy'), 
     KeywordsList('C++','Programming','Object Oriented'));

insert into Books values (2, 'Oracle 8 DBA Handbook', 
    'Kevin Loney', 45.00, TO_DATE('1-1-1998','dd-mm-yyyy'),
     KeywordsList('Oracle','Databases'));

insert into Books values (3, 'C++ Object Databases',
    'Bjarne Stroustrup', 30.00, TO_DATE('1-1-1999','dd-mm-yyyy'), 
     KeywordsList('Databases','Object Oriented','C++'));

commit;

create or replace package OCCIDemo1 as
     type IdList is table of number(10) index by binary_integer;
     type BookList is table of varchar2(100) index by binary_integer;
     type BookCur is ref cursor;

     procedure GetBookDetails(pId in number, pTitle out varchar2,
               pPrice out number, pPubDate out date,
               pKeywords out KeywordsList);
     function  GetBookList(pIds in IdList, pBooks out BookList) return
               number;
     procedure SearchBooks(pKeyword in out varchar2, pBookCur out BookCur);
end OCCIDemo1;
/

create or replace package body OCCIDemo1 as

     procedure GetBookDetails(pId in number, pTitle out varchar2,
               pPrice out number, pPubDate out date,
               pKeywords out KeywordsList) is
     begin
        select title, price, pubdate, keywords
          into pTitle, pPrice, pPubDate, pKeywords
        from books
        where id = pId;
     end GetBookDetails;
     
     function  GetBookList(pIds in IdList, pBooks out BookList) return
               number is
     begin
       for i in pIds.first..pIds.last loop
        select title into pBooks(i) from books
        where id = pIds(i);
       end loop;

       return pBooks.last;
     end GetBookList;

     procedure SearchBooks(pKeyword in out varchar2, pBookCur out BookCur) is
     begin
        pKeyword := upper(pKeyword);
        --open and return a cursor, query uses collections unnesting
        open pBookCur for 
           select title from books a, table(a.keywords) p
           where upper(p.column_value) = pKeyword;
     end SearchBooks;

end OCCIDemo1;
/