如何在SQL中定义和使用全局变量[重复]

This question already has an answer here:

这个问题在这里已有答案:

How do I use variables in Oracle SQL Developer? 9 answers

如何在Oracle SQL Developer中使用变量? 9个答案

I'm a tester and I have to run lots of select queries to filter out the information I'm testing.

我是测试人员,我必须运行大量选择查询来过滤掉我正在测试的信息。

Is there a way to make global variable and use it instead of pasting same values?

有没有办法制作全局变量并使用它而不是粘贴相同的值?

Sample code:

Select l.prod_package,m.* from avtt7m0 m, avtt7l0 l 
where l.cust_id='52317162090004' 
and l.ar_id=m.ar_id and m.lc_st_code='ACT'; 

Select m.* from avtt7m0 m, avtt7l0 l 
where l.cust_id='52317162090004' 
and l.ar_id=m.ar_id and m.lc_st_code='ACT'; 

Select * From AKTTD90 
where cust_id in ('52317162090004'); 

Select * From Kndt7m0 
where cust_id in ('52317162090004');

3 个解决方案

#1

You can declare the variable and assign it the customer value.

您可以声明变量并为其分配客户值。

DECLARE @Customer BIGINT='52371762090004'

SELECT * FROM AKTTD90 WHERE Cust_id=@Customer

UPDATE: Try executing the above query.

更新:尝试执行上述查询。

#2

This aims at SQL Server - your question doesn't have a specific RDMS. You can Declare a variable (which must begin with @ then Set it then use it (in most cases):

这针对SQL Server - 您的问题没有特定的RDMS。你可以声明一个变量(必须以@开头然后设置它然后使用它(在大多数情况下):

SET @CustomerID = 12345
SELECT * FROM dbo.Customers WHERE CustomerID = @CustomerID

Note that this cannot be used for certain things, such as a variable for a table name. Also note that these variables are not actually Global in the sense that they will not survive a GO statement.

请注意,这不能用于某些事情,例如表名的变量。还要注意,这些变量实际上并不是Global,因为它们不会在GO语句中存活。

#3

One option to do this is with PL/SQL-Package Variables:

执行此操作的一个选项是PL / SQL-Package变量:

create or replace package steptest as
  procedure set(a number);
  function get return number;
end;
/

create or replace package body steptest as
  x number;
  procedure set(a number) is
  begin
    x:=a;
  end;
  function get return number is
  begin
    return x;
  end;
begin
  x:=0;
end;    
/

And then you can:

然后你可以:

exec steptest.set(1234);

select steptest.get from dual;

#1