Larkin - Oracle Package

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%type
10     );
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

标签：结果   into   initial   tor   ott   use   incr   prim   table