英文:
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;
结果:
英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论