Larkin - Oracle Package

標籤：結果   into   initial   tor   ott   use   incr   prim   table   

一切知識說明不如demo,so

demo所需要的 test 表

  CREATE TABLE "SCOTT"."TEST"    (    "ID" NUMBER(6,0) NOT NULL ENABLE,     "USERNAME" VARCHAR2(20 BYTE),     "USERPWD" VARCHAR2(20 BYTE),     "NEW_EMAIL" VARCHAR2(30 BYTE),     "REGDATE" DATE DEFAULT NULL,     "USERAGE" NUMBER(4,0),     "SCORE" NUMBER(20,0),      CONSTRAINT "TEST_PK" PRIMARY KEY ("ID")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"  ENABLE   ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS" ;

 

首先要定義包頭，聲明結構

 1 create or replace PACKAGE TESTPACKAGE AS  2  3   /* TODO enter package declarations (types, exceptions, methods etc) here */  4     --定義結構體 5     type te_stu is record( 6         pid test.id%type, 7         pname test.username%type, 8         page  test.userage%type, 9         pscore test.score%type10     );11     -- 定義遊標12     type test_cursor is ref cursor;13     procedure gtest(userid in number,tlist out test_cursor);14 END TESTPACKAGE;

然後我們建立主體

create or replace PACKAGE BODY TESTPACKAGE AS  procedure gtest(userid in number,tlist out test_cursor) AS    r_stu test%rowtype;  BEGIN    -- TODO: procedure TESTPACKAGE.gtest所需的實施    open tlist for select id,username,userage,score from test ;    -- where id=userid;  END gtest;END TESTPACKAGE;

此時一個基本的包寫完了，我們來進行調用，建立一個package.sql

declare    x testpackage.test_cursor;    t testpackage.te_stu;begin    testpackage.gtest(1,x);    loop         fetch x into t;          exit when x%notfound;        dbms_output.put_line(‘姓名=‘||t.pname);    end loop;end;

引用遊標一般返回結果集使用。從測試可看出oracle package相當於我們非常熟悉的Class。

 

Larkin - Oracle Package