select *from emp
where
job=decode(3,2,'MANAGER','SALESMAN');
=======================================================
这条语句本来是当(3==2)为真时 :job=‘MANAGER’,为假时:job='SALESMAN'
现在想为假时 选出所有job 该怎么修改?不用动态语句
where
job=decode(3,2,'MANAGER','SALESMAN');
=======================================================
这条语句本来是当(3==2)为真时 :job=‘MANAGER’,为假时:job='SALESMAN'
现在想为假时 选出所有job 该怎么修改?不用动态语句
解决方案 »
- 怎么样求上一个月同星期几的平均值啊
- 求一正则表达式
- 急啊!!!! 大家看看这样的删除,应该怎么实现??
- oracle 数据库 用pro c 来批量插数据出错怎么才能打印出哪个数据有问题?
- 我有个sql,3个字符型的数值,求平均值。可是这3个数中如果有0,就得到空值?
- 紧急!如何用SQL语句取包内函数返回的值?
- 都说oracle9i不需要改名,怎么我在机器上装不上去?分都散完了,分不多请大家帮忙!
- 如何得到前一个月的同一天的时间?
- 如何在AIX433上复制ORACLE817数据库?马上送分
- 临时表和@@rowcount的问题?
- 求解ORACLE 查询快,插入慢
- 请各位大侠帮我看看这个SQL可以优化吗?现在performance很低,谢谢了。
其实你按你的要求,就是取所有语句,然后把3==2的替换掉,所以
刚才错了
--根据意思应是这样子
select * from emp
where (3=2 and job='MANAGER') or (3<>2 and job='SALESMAN');
--但是3=2不可能成立,因此也可以写为这样子
select * from emp where job='SALESMAN';
--所以你的3=2因该换成其他有意义的条件才合时
====================================================================
create or replace procedure var_select(
name in varchar2)
is
emp_rec emp%rowtype;
begin
select *from emp into empo_rec
where
job=decode(name,'test1','MANAGER','SALESMAN');
end;
=====================================================================
目的就是根据传入的name选择不同的数据,即选择条件不同
补充:job不是抽出字段,而是选择条件
name in varchar2)
is
emp_rec emp%rowtype;
begin
select * into emp_rec from emp
where
job=decode(name,'test1','MANAGER','SALESMAN');
end;
name in varchar2)
is
emp_rec emp%rowtype;
begin
select * into emp_rec from emp
where
job=decode(name,'test1','MANAGER','');
end; 只要job is not null 就可以这样
==================================================================================
代码:
create or replace procedure var_select(
name in varchar2)
is
type emp_rec_type is table of emp%rowtype;
emp_rec emp_rec_type;
cursor v_emp is
select * from emp
where
job=decode(name,'test1','MANAGER','');
begin
open v_emp;
fetch v_emp bulk collect into emp_rec;
for i in 1..emp_rec.count loop
dbms_output.put_line(emp_rec(i).deptno);
end loop;
close v_emp;
commit;
end;
===========================================================================
你自己跑跑看看
SQL> create or replace procedure var_select(
2 p_name in varchar2)
3 is
4 emp_rec emp%rowtype;
5 cursor cur is select * from emp
6 where job=decode(p_name,'test1','MANAGER','SALESMAN');
7 begin
8 open cur;
9 loop
10 fetch cur into emp_rec;
11 exit when cur%notfound;
12 dbms_output.put_line('empno:'||emp_rec.empno);
13 end loop;
14 close cur;
15 end;
16 /过程已创建。SQL> exec var_select('test1');
empno:7566
empno:7698
empno:7782 PL/SQL 过程已成功完成。SQL> exec var_select('test2');
empno:7499
empno:7521
empno:7654
empno:7844 PL/SQL 过程已成功完成。
我的问题我自己已经解决了,整理一下希望对有相同要求的童鞋有所帮助
================================================================================
码:
create or replace procedure var_select(
name in varchar2)
is
type emp_rec_type is table of emp%rowtype;
emp_rec emp_rec_type;
cursor v_emp is
select * from emp
where
job like decode(name,'test1','MANAGER','%');
begin
open v_emp;
fetch v_emp bulk collect into emp_rec;
for i in 1..emp_rec.count loop
dbms_output.put_line(emp_rec(i).deptno);
end loop;
close v_emp;
commit;
end;
=================================================================================
目的:
这条语句本来是当(name='test1')为真时 ,抽出条件job=‘MANAGER’;
为假时, 抽出条件:所有job
==================================================================================
运行结果:连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> set serveroutput on;
SQL> exec var_select('test1');
20
30
10PL/SQL 过程已成功完成。SQL> exec var_select('test2');
20
30
30
20
30
30
10
20
10
30
20
30
20
10PL/SQL 过程已成功完成。SQL>
===============================================================================
我希望大家回答问题时认真一点。知道你们都很忙,能看我的问题已经不错。不过答非所问让我很不舒服