如何以最简单的方式在 SQL Developer 中定义和使用 Oracle SQL 脚本中的变量

Question

我只想在SQL Developer(版本18.1.0.095(中定义并使用Oracle SQL中的变量(背景：我们有一些通过jBPM定期运行的遗留生产SQL脚本(。

在SQL开发人员中，我想避免"输入绑定"提示窗口。

令人惊讶的是，我发现这不是那么简单。 我错过了一些明显的东西吗？

CREATE TABLE Test_Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255)
);
INSERT INTO Test_Persons
(PersonID,LastName,FirstName)
values(1,'LN_1','FN_1');
INSERT INTO Test_Persons
(PersonID,LastName,FirstName)
values(2,'LN_2','FN_2');
commit;
// --- method 1: not working and shows the prompt window (which I try to avoid)--------
var last_name_input varchar2(20);
select 'LN_2' into :last_name_input from dual;
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;
// --- method 2: not working and shows the prompt window (which I try to avoid) --------
declare
var last_name_input varchar2(20);
var first_name_output varchar2(20);
begin
select 'LN_2' into :last_name_input from dual;
select tp.FIRSTNAME into first_name_output from Test_Persons tp where tp.LASTNAME = :last_name_input;
--    dbms_output.Put_line('here: ' || :first_name_output);
end;
// --- method 3: working and shows the prompt window (which I try to avoid) --------
var last_name_input varchar2(20);
exec :last_name_input := 'LN_2';
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

Answer 1

您可以使用这两种方法中的任何一种来避免 SQL 开发人员中的提示。

从exec设置变量的值并作为脚本运行 (F5(

var   last_name_input varchar2(20);
EXEC  :last_name_input := 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

另一种方法是使用替换变量(默认&，也可以使用SET DEFINE更改它(

define last_name_input = 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = '&last_name_input';

Answer 2

您的方法2在Oracle 12c上删除"："后有效 -

CREATE TABLE Test_Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255)
);
INSERT INTO Test_Persons
(PersonID,LastName,FirstName)
values(1,'LN_1','FN_1');
INSERT INTO Test_Persons
(PersonID,LastName,FirstName)
values(2,'LN_2','FN_2');
commit;

set serverout on;
declare
last_name_input varchar2(20);
first_name_output varchar2(20);
begin
select 'LN_2' into last_name_input from dual;
select tp.FIRSTNAME into first_name_output from Test_Persons tp where tp.LASTNAME = last_name_input;
dbms_output.Put_line('here: ' || first_name_output);
end;

输出-

Table TEST_PERSONS created.

1 row inserted.

1 row inserted.

Commit complete.
here: FN_2

PL/SQL procedure successfully completed.