这是一个来自SQL Server的查询,我想问的是,我想如何在Oracle中获得相同的结果,查询会是什么?
我需要这样的结果:"这是SQL Server上的查询">
declare @a int = 6
if @a = 1
begin
select *
from table_A
end
else
begin
select *
from table_B
end
我在oracle上尝试过这样的操作,但失败了
declare
type FF_rec is record(
st_Value number
);
var_ff FF_rec;
begin
var_ff.st_Value:=1;
if 1<=0 then
select * from Table_A;
else
select * from Table_B;
end if;
end;
如果必须在SQL级别,那么也许可以使用这样的代码:根据传递给PAR_WHAT
替换变量的值,您将从EMP
或DEPT
表中获得结果。
由于UNION
集合运算符,选择列列表的数量和数据类型必须匹配,即
- 您不能选择,例如,从第一个选择两列,从第二个选择5列
- 也不能在数据类型上不匹配,例如,第一次选择的第一列是
DATE
,而第二次选择的第1列是NUMBER
所以:
SQL> select empno, ename, job
2 from emp
3 where &&par_what = 1
4 union
5 select deptno, dname, loc
6 from dept
7 where &&par_what = 2;
Enter value for par_what: 1
EMPNO ENAME JOB
---------- -------------- -------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
<snip>
14 rows selected.
SQL> undefine par_what
SQL> /
Enter value for par_what: 2
EMPNO ENAME JOB
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
如果您对PL/SQL满意,那么一个选项是创建一个返回refcursor的函数。哪一个?取决于您传递给它的参数:
SQL> create or replace function f_test (par_what in number)
2 return sys_refcursor
3 is
4 l_rc sys_refcursor;
5 begin
6 if par_what = 1 then
7 open l_rc for select deptno, ename, job, sal, hiredate from emp;
8 elsif par_what = 2 then
9 open l_rc for select deptno, dname, loc from dept;
10 end if;
11
12 return l_rc;
13 end;
14 /
Function created.
测试:
SQL> select f_test(1) from dual;
F_TEST(1)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO ENAME JOB SAL HIREDATE
---------- ---------- --------- ---------- ----------
20 SMITH CLERK 800 17.12.1980
30 ALLEN SALESMAN 1600 20.02.1981
30 WARD SALESMAN 1250 22.02.1981
20 JONES MANAGER 2975 02.04.1981
30 MARTIN SALESMAN 1250 28.09.1981
30 BLAKE MANAGER 2850 01.05.1981
<snip>
14 rows selected.
SQL> select f_test(2) from dual;
F_TEST(2)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>