?? schema.sql
字號:
-- schema.sql - PL/SQL procedures/functions for OCCI sampleconnect 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 rowsinsert 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;/
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -