DROP TABLE person;CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));INSERT INTO person VALUES(555662222,'Sam','Goodwin');INSERT INTO person VALUES(555882222,'Kent','Clark');INSERT INTO person VALUES(666223333,'Jane','Doe');COMMIT;
/
Create the following package on your Oracle server:CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER; PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:CREATE OR REPLACE PACKAGE BODY packperson
ASPROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));INSERT INTO person VALUES(555662222,'Sam','Goodwin');INSERT INTO person VALUES(555882222,'Kent','Clark');INSERT INTO person VALUES(666223333,'Jane','Doe');COMMIT;
/
Create the following package on your Oracle server:CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER; PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:CREATE OR REPLACE PACKAGE BODY packperson
ASPROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
解决方案 »
- 问个简单问题,在ORACLE这种写法怎么实现?
- 简单视图的小问题
- 超级菜鸟问题,答对马上给分哦!
- oracle如何定时启动进程?
- SQL SERVER 7的数据库中的表格怎么利用DTS向oracle 9i导入数据的时候显示表不存在呢?谁碰到过这个问题呀???
- 在线等一个非常小的问题,怎么在数据库中用sql语言增加字段,在线等,解决马上给分,只给唯一解决问题的人。
- 新手问题:怎么用.sql文件把触发器等东西导入数据库?
- oracle8i的个人版能不能安装在win98下,能不能在98下建立数据库!有可视界面吗?
- 如何只读取排序最前的10条记录
- 为什么SQL执行后看不到结果
- 如何select LONG类型的字段?
- 在Oracle数据库中,创建字段自动加1时了出错,不知什么原因?
http://expert.csdn.net/Expert/FAQ/FAQ_Index.asp?id=7012