oracle sql批量变量收集

gijlo24d  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(332)

我有下面的配置表:
id\程序文件\名称VSTR\内容P1AC1P1BC2P1CC3P1DC4P1EC5P1FC6P1GC7
我有下面的方法基于id\u过程来获取vstr\u内容值。

SET SERVEROUTPUT ON;

DECLARE
    config procedures_text_config%rowtype;
BEGIN
    SELECT
        *
    INTO config
    FROM
        procedures_text_config
    WHERE
        id_procedure = 'P1';

    dbms_output.put_line(config.vstr_content);
END;

但是,我希望能够以以下格式(或类似格式)提取指定id\u过程和vstr\u名称的vstr\u内容:

config.a.vstr_content (expected output:C1).
igsr9ssn

igsr9ssn1#

我不确定你能做你想做的事(至少,在甲骨文里不行)。
从table上拿东西的通常方法是用 select 语句或(如果需要)函数。将参数传递给它并得到结果。这就是它的工作原理。您不会传递“config.a.vstr\u content”(不管它代表什么)。
这里有几个例子,看看这些是否有帮助。
(哦,是的-您的代码甚至无法运行,它将失败并出现太多行错误)。
样品表:

SQL> select * From config;

ID_PROCEDURE    VSTR_NAME  VSTR_CONTENT
--------------- ---------- ------------
P1              A          C1
P1              B          C2
P1              C          C3

SQL>

一个简单的函数,返回标量变量;就像您的例子一样,如果多行满足where条件,那么它可能会失败。

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return config.vstr_content%type is
  4    retval config.vstr_content%type;
  5  begin
  6    select vstr_content
  7      into retval
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

测试:

SQL> select f_config('P1', 'A') from dual;

F_CONFIG('P1','A')
--------------------------------------------------------------------------------
C1

SQL> select f_config('P1', null) from dual;
select f_config('P1', null) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.F_CONFIG", line 6

SQL>

那么,返回一个refcursor怎么样?

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return sys_refcursor is
  4    retval sys_refcursor;
  5  begin
  6    open retval for
  7    select *
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

测试:

SQL> select f_config('P1', 'A') from dual;

F_CONFIG('P1','A')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID V VS
-- - --
P1 A C1

SQL> select f_config('P1', null) from dual;

F_CONFIG('P1',NULL)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID V VS
-- - --
P1 A C1
P1 B C2
P1 C C3

SQL>

或者,返回集合:

SQL> create or replace type t_row as object
  2    (id_procedure varchar2(10),
  3     name varchar2(10),
  4     content varchar2(10));
  5  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

SQL> create or replace function f_config
  2    (par_id_procedure in varchar2, par_name in varchar2)
  3  return t_tab is
  4    retval t_tab;
  5  begin
  6    select t_row(id_procedure, vstr_name, vstr_content)
  7      bulk collect into retval
  8      from config
  9      where (id_procedure = par_id_procedure or par_id_procedure is null)
 10        and (vstr_name = par_name or par_name is null);
 11    return retval;
 12  end;
 13  /

Function created.

测试:

SQL> select * From table(f_config('P1', 'A'));

ID_PROCEDU NAME       CONTENT
---------- ---------- ----------
P1         A          C1

SQL> select content From table(f_config('P1', 'A'));

CONTENT
----------
C1

SQL> select * from table(f_config('P1', null));

ID_PROCEDU NAME       CONTENT
---------- ---------- ----------
P1         A          C1
P1         B          C2
P1         C          C3

SQL>

如果这些都没什么帮助,请解释一下你写的最后一句话的确切意思(“不过,我想要一种方式……”)。

相关问题