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

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

Oracle SQL Assign value to variable outside BEGIN-END?

问题

I have the following structure:

  1. ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
  2. ACCEPT PVAR_FULLROUTE PROMPT 'Full Route: '
  4. 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:

  1. ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
  2. DEFINE PVAR_FULLROUTE; -- I'm not sure this is valid
  4. BEGIN
  5.     {Series of validations}
  6.     {How do I assign a value to PVAR_FULLROUTE from within the procedure?}
  7. END;
  9. 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:

  1. ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
  2. ACCEPT PVAR_FULLROUTE PROMPT 'Full Route: '
  4. 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:

  1. ACCEPT PVAR_FLENME PROMPT 'File Name (No Space)? '
  2. DEFINE PVAR_FULLROUTE; -- I'm not sure this is valid
  4. BEGIN
  5.     {Series of validations}
  6.     {How do I assign a value to PVAR_FULLROUTE from within the procedure?}
  7. END;
  9. 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

这里有一个选项。

接受文件名：

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

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

  1. SQL> var par_fullroute varchar2(20)
  2. SQL> begin
  3.   2    select 'this_is_route' into :par_fullroute from dual;
  4.   3  end;
  5.   4  /
  7. PL/SQL过程成功完成。
  9. SQL> print par_Fullroute
  11. PAR_FULLROUTE
  12. --------------------------------------------------------------------------------
  13. this_is_route

好了，它的值已经设置。现在，创建一个column并将绑定变量（par_fullroute）放入新的替代变量：

  1. SQL> column par_bv_route new_value bvr
  2. SQL> select :par_fullroute par_bv_route from dual;
  4. PAR_BV_ROUTE
  5. --------------------------------------------------------------------------------
  6. this_is_route

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

  1. SQL> spool "&bvr.&par_filename."
  2. SQL> select sysdate from dual;
  4. SYSDATE
  5. ---------
  6. 08-FEB-23
  8. SQL> spool off;

结果：

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

英文:

Here's one option.

Accept filename:

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

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

  1. SQL> var par_fullroute varchar2(20)
  2. SQL> begin
  3.   2    select 'this_is_route' into :par_fullroute from dual;
  4.   3  end;
  5.   4  /
  7. PL/SQL procedure successfully completed.
  9. SQL> print par_Fullroute
  11. PAR_FULLROUTE
  12. --------------------------------------------------------------------------------
  13. this_is_route

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

  1. SQL> column   par_bv_route    new_value    bvr
  2. SQL> select :par_fullroute par_bv_route from dual;
  4. PAR_BV_ROUTE
  5. --------------------------------------------------------------------------------
  6. this_is_route

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

  1. SQL> spool "&bvr.&par_filename."
  2. SQL> select sysdate from dual;
  4. SYSDATE
  5. ---------
  6. 08-FEB-23
  8. 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
  • oracle
  • plsql
  • sql
  • sqlplus
匿名

发表评论

匿名网友

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

确定