0.创建测试用表
CREATE TABLE TAB_GL_USER(ID INTEGER,NAME VARCHAR2(40),CLASS_ID INTEGER,DELETED INTEGER);
CREATE TABLE TAB_GL_CLASS(ID INTEGER,NAME VARCHAR2(40),DELETED INTEGER);
1.先创建自定义类型
CREATE OR REPLACE TYPE TYP_GL_USER FORCE AS OBJECT
(
ID INTEGER
,NAME VARCHAR2(40)
);
2.以第一步的类型作子类型创建集合类型
CREATE OR REPLACE TYPE TYP_GL_USERS AS TABLE OF TYP_GL_USER;
3.创建存储过程 集合转换成表使用
CREATE OR REPLACE PROCEDURE PROC_GL_ADD_USERS(V_USERS IN TYP_GL_USERS) IS
BEGIN
--作为表的用法
INSERT INTO TAB_GL_USER
(ID, NAME, DELETED)
--在存储过程中
--当类型TYP_GL_USER的所有字段都是关键字时(就像现在),
--这条语句只能取到行数,取到的所有字段都是空值
--Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SELECT ID, NAME, 0 DELETED FROM TABLE(V_USERS);
--作为集合的用法
FOR IDX IN 1 .. V_USERS.COUNT LOOP
INSERT INTO TAB_GL_USER
(ID, NAME, DELETED)
VALUES
(V_USERS(IDX).ID, V_USERS(IDX).NAME, 0);
END LOOP;
END PROC_GL_ADD_USERS;
4.PLSQL 块 (在存储过程中用法类似)中使用
DECLARE
V_USERS TYP_GL_USERS;
BEGIN
V_USERS := TYP_GL_USERS(TYP_GL_USER(1, 'Green Lee'),
TYP_GL_USER(2, 'San Zhang'));
PROC_GL_ADD_USERS(V_USERS);
END;
5.创建函数
CREATE OR REPLACE FUNCTION FUNC_GL_ADD_USERS(V_USERS TYP_GL_USERS)
RETURN NUMBER IS
FUNCTIONRESULT NUMBER;
BEGIN
FUNCTIONRESULT := 0;
PROC_GL_ADD_USERS(V_USERS);
FUNCTIONRESULT := 1;
RETURN(FUNCTIONRESULT);
END FUNC_GL_ADD_USERS;
6.使用函数 表中的行转换成集合
--由于该函数内使用了DML语句(INSERT),因此本语句执行会报ORA-14511错误,仅作语法参考
SELECT C.NAME CLASS_NAME,
FUNC_GL_ADD_USERS(CAST(COLLECT(TYP_GL_USER(U.ID, U.NAME)) AS
TYP_GL_USERS)) SUCCESSED
FROM TAB_GL_CLASS C, TAB_GL_USER U
WHERE C.ID = U.CLASS_ID
AND C.DELETED = 0
AND U.DELETED = 0
GROUP BY C.NAME;
7.集合和行互转
SELECT ID, NAME
FROM
TABLE (SELECT CAST(COLLECT(TYP_GL_USER(ID, NAME)) AS TYP_GL_USERS)
FROM TABLE(TYP_GL_USERS(TYP_GL_USER(1, 'Green Lee'),
TYP_GL_USER(2, 'San Zhang'))));
老乞丐:“哎,小弟,小弟,别走啊。”
小孩扭头过来。
老乞丐:“哇,不得了啊不得了,你有道灵光从天灵盖喷出来,你知道嘛,年纪轻轻的就有一身横练的筋骨,简直百年一见的练武奇才啊,如果有一天让你打通任督二脉,你还不飞龙上天,正所谓我不入地狱,谁入地狱……”
老乞丐:“警恶惩奸,维护世界和平这个任务就交给你了,好嘛?”
小孩点头说:“唔!”
老乞丐:“这本《如来神掌》秘笈是无价之宝,我看与你有缘,收你十块钱,传授给你吧!”
首发:Oracle数据库Collection干货 (toutiao.com)https://www.toutiao.com/i7007018857768501797/