Assign value to variable outside BEGIN-END? 在BEGIN-END之外分配值给变量?

huangapple go评论66阅读模式
英文:

Oracle SQL Assign value to variable outside BEGIN-END?

问题

I have the following structure:

ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
ACCEPT PVAR_FULLROUTE PROMPT 'Full Route: '

SPOOL "&PVAR_FULLROUTE.&PVAR_FLENME.";

... which works as expected.

Now I want to make the second variable to be dependent on a Procedure without having to create it (or a Function). I would normally use COLUMN & a SELECT, but the logic within the BEGIN-END is more complex:

ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
DEFINE PVAR_FULLROUTE; -- I'm not sure this is valid

BEGIN
    {Series of validations}
    {How do I assign a value to PVAR_FULLROUTE from within the procedure?}
END;

SPOOL "&PVAR_FULLROUTE.&PVAR_FLENME.";

What is the correct way to assign a value to a variable from within a procedure, so I can use it in other BEGIN-END sections? Is it possible?

英文:

I have the following structure:

ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
ACCEPT PVAR_FULLROUTE PROMPT 'Full Route: '

SPOOL "&PVAR_FULLROUTE.&PVAR_FLENME.";

... which works as expected.

Now I want to make the second variable to be dependent on an Procedure without having to create it (or a Function). I would normally use COLUMN & a SELECT, but the logic within the BEGIN-END is more complex:

ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
DEFINE PVAR_FULLROUTE; -- I'm not sure this is valid

BEGIN
    {Series of validations}
    {How do I assign a value to PVAR_FULLROUTE from within the procedure?}
END;

SPOOL "&PVAR_FULLROUTE.&PVAR_FLENME.";

What is the correct way to assign a value to a variable from within a procedure, so I can use it in other BEGIN-END sections? Is it possible?

答案1

得分: 1

这里有一个选项。

接受文件名:

SQL> accept par_filename prompt '输入文件名: '
输入文件名: test.txt

创建一个变量,其值然后在匿名PL/SQL块中设置:

SQL> var par_fullroute varchar2(20)
SQL> begin
  2    select 'this_is_route' into :par_fullroute from dual;
  3  end;
  4  /

PL/SQL过程成功完成

SQL> print par_Fullroute

PAR_FULLROUTE
--------------------------------------------------------------------------------
this_is_route

好了,它的值已经设置。现在,创建一个column并将绑定变量(par_fullroute)放入新的替代变量:

SQL> column par_bv_route new_value bvr
SQL> select :par_fullroute par_bv_route from dual;

PAR_BV_ROUTE
--------------------------------------------------------------------------------
this_is_route

一切都设置好了,剩下的是为spool创建文件名:

SQL> spool "&bvr.&par_filename."
SQL> select sysdate from dual;

SYSDATE
---------
08-FEB-23

SQL> spool off;

结果:

Assign value to variable outside BEGIN-END?
在BEGIN-END之外分配值给变量?

英文:

Here's one option.

Accept filename:

SQL> accept par_filename prompt 'Enter filename: '
Enter filename: test.txt

Create a variable whose value is then set in anonymous PL/SQL block:

SQL> var par_fullroute varchar2(20)
SQL> begin
  2    select 'this_is_route' into :par_fullroute from dual;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print par_Fullroute

PAR_FULLROUTE
--------------------------------------------------------------------------------
this_is_route

OK; its value is set. Now, create a column and put bind variable (par_fullroute) into new substitution variable:

SQL> column   par_bv_route    new_value    bvr
SQL> select :par_fullroute par_bv_route from dual;

PAR_BV_ROUTE
--------------------------------------------------------------------------------
this_is_route

Everything's set; what's left is to create filename for spool:

SQL> spool "&bvr.&par_filename."
SQL> select sysdate from dual;

SYSDATE
---------
08-FEB-23

SQL> spool off;

Result:

Assign value to variable outside BEGIN-END?
在BEGIN-END之外分配值给变量?

huangapple
  • 本文由 发表于 2023年2月9日 03:18:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75390716.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定