ORACLE 存储过程 练习八 TABLE 类型测试

 1 创建数据类型

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);

 2 创建返回值为table的函数

CREATE OR REPLACE FUNCTION P_TEST_TABLE(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
/*拆分字符串函数*/
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END P_TEST_TABLE;

 3 函数的普通调用方法

SELECT column_value AS val

FROM   TABLE(str_split('Apple','Banana','Apricot'))

WHERE  column_value NOT LIKE 'A%';

 4 取得table里面特定值的调用方法

declare
       v_item varchar2(20);
begin
  SELECT column_value into v_item

     FROM   TABLE( select P_TEST_TABLE('Henry,Huang',',')  from dual)

WHERE  column_value NOT LIKE '%e%';
dbms_output.put_line(' v_item :'||v_item);
end;

 5 取得TABLE里面最后一个元素的调用方法

CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);
declare
       v_test_table str_split;
       v_item varchar2(20);
begin
 select P_TEST_TABLE('Henry,Huang',',') into v_test_table from dual;

-- 取得最后一个元素的下标
 dbms_output.put_line(  ' INDEX IS ' ||  v_test_table.last );
 --取得最后一个元素的值
 select  v_test_table(v_test_table.last) into v_item  from dual;
  dbms_output.put_line(  ' NAME IS ' ||  v_item);
end;

============参考资料==============

http://luoke920.iteye.com/blog/305883

http://blog.csdn.net/indexman/article/details/8097882