更新时间:2021-08-28 15:57:19
我想你不能在SQL中做这样的转换。但是在PL / SQL中你可以:
I think you cannot do such casting in SQL. But in PL/SQL you can:
CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)
/
DECLARE
tab STRARRAY;
cnt NUMBER:= 0;
BEGIN
SELECT COUNT(*)
INTO cnt
FROM TABLE(CAST(tab AS strarray));
dbms_output.put_line(cnt);
END;
/
我认为我错在上面的假设。我没有删除它,因为它仍然是有效的例子。下面的示例使用COLLECT作为table_type的类型来转换现有表列(emp表):
I think I was wrong in my assumptions above. I did not delete that as it is still valid example. Below example casting existing table column (emp table) with COLLECT as type of table_type:
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
/
-- This is dumb but works:
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
UNION ALL
SELECT deptno
, CAST(COLLECT(ename) AS varchar2_ntt) AS emps
FROM scott.emp
GROUP BY deptno
/