---创建类型
--员工号,姓名,工资
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT
(
ENO NUMBER,
ENAME VARCHAR2(20),
ESAL NUMBER
)
;
--员工号,姓名,工资
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT
(
ENO NUMBER,
ENAME VARCHAR2(20),
ESAL NUMBER
)
;
--使用table of 子句创建table类型
CREATE TYPE EMP_NT AS TABLE OF EMP_TYPE;
CREATE TYPE EMP_NT AS TABLE OF EMP_TYPE;
--使用emp_nt数据类型创建myemp表
CREATE TABLE myemp(deptno number,
edet emp_nt) nested table edet store as myemployee;
--初始化myemp的数据
INSERT INTO myemp values(10,emp_nt(emp_type(
1000, 'James',10000),emp_type(1001, 'Daniel',2000)));
INSERT INTO myemp values(10,emp_nt(emp_type(
1000, 'James',10000),emp_type(1001, 'Daniel',2000)));
此示例说明如何使用游标获取嵌套表中的数据。
DECLARE
SAL NUMBER;
ENA VARCHAR2(20);
CURSOR EMP_CURSOR IS
SELECT A.ENAME, A.ESAL
FROM THE (SELECT EDET FROM MYEMP WHERE DEPTNO = 10) A;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR
INTO ENA, SAL;
EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENA || ' ' || SAL);
END LOOP;
CLOSE EMP_CURSOR;
END;
/
SAL NUMBER;
ENA VARCHAR2(20);
CURSOR EMP_CURSOR IS
SELECT A.ENAME, A.ESAL
FROM THE (SELECT EDET FROM MYEMP WHERE DEPTNO = 10) A;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR
INTO ENA, SAL;
EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENA || ' ' || SAL);
END LOOP;
CLOSE EMP_CURSOR;
END;
/
执行结果如下:
DECLARE
SAL NUMBER;
ENA VARCHAR2(20);
CURSOR EMP_CURSOR IS
SELECT A.ENAME, A.ESAL
FROM table (SELECT EDET FROM MYEMP WHERE DEPTNO = 10) A;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR
INTO ENA, SAL;
EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENA || ' ' || SAL);
END LOOP;
CLOSE EMP_CURSOR;
END;
/
SAL NUMBER;
ENA VARCHAR2(20);
CURSOR EMP_CURSOR IS
SELECT A.ENAME, A.ESAL
FROM table (SELECT EDET FROM MYEMP WHERE DEPTNO = 10) A;
BEGIN
OPEN EMP_CURSOR;
LOOP
FETCH EMP_CURSOR
INTO ENA, SAL;
EXIT WHEN EMP_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENA || ' ' || SAL);
END LOOP;
CLOSE EMP_CURSOR;
END;
/
执行结果如下: