Oracle数据库之Collection干货

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/