oracle-sqldeveloper - 如何在声明块中使用变量？

Question

Declare str varchar2(100);
Begin
str := 'first,last,middle';
select regexp_substr(str, '[^,]+', 1, rownum) as SeparatedStr
from dual
connect by rownum <= length(str) - length(replace(str, ',', '')) + 1;
end;

我对 PL/SQL 很陌生，曾经是 T-SQL 人。在搜索了这么多文章后，我真的不确定如何使用变量。只需要帮助教我如何使用声明的变量。

标签： oracle-sqldeveloperplsqldeveloper

Answer 1

变量有助于存储选择查询的结果，但它取决于选择查询是给​​出单行还是多行作为输出。

复制您的查询如下：

1.

     DECLARE
        str VARCHAR2(100);
        result VARCHAR2(100);
        BEGIN
        str := 'first,last,middle';
        SELECT str INTO result FROM dual;
        dbms_output.put_line('str: '||result);
        END;
        /

2. As your SELECT query is producing more than one row it can not be assigned into a scalar variable.

DECLARE 
    str VARCHAR2(100);
    BEGIN
    FOR I IN(
    WITH TEST AS
    (SELECT 'first,last,middle' AS str FROM dual)
    SELECT regexp_substr(str, '[^,]+', 1, ROWNUM) AS separatedstr
    FROM TEST
    CONNECT BY ROWNUM <= LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1) LOOP
    dbms_output.put_line(I.separatedstr);
    END LOOP;
    END;
    /

希望这可以帮助。