网上的看的感觉和SQL差不多,我看不出来改怎么改 我想做一个带参数的存储过程 然后再做一个临时表 实现select * from 临时表 where 年=... and 月=... and 日=... 因为在我的六条select语句中只有where后面的时间条件需要在外面获取
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式 呃 不是很懂 能给个例子吗? declare -- Local variables here l_fund VARCHAR2(10); begin SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式 呃 不是很懂 能给个例子吗? declare -- Local variables here l_fund VARCHAR2(10); begin SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
你只需要查询结果 那你要存储过程干嘛,你一个sql语句不就能知道查询结果了,难道程序里的sql语句不能带参数么 亲,你如果是想把查询结果存到另一张表,你可以使用游标,循环插入表(自己去百度),或者直接类似这样:insert into table_a select * from table_b;commit;
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式 呃 不是很懂 能给个例子吗? declare -- Local variables here l_fund VARCHAR2(10); begin SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
你还是没看懂的意思 select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title into from ( --年-完成 select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --月-完成 select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --日-完成 select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --作业中 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='申请' union all --年-作废 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='作废' union all --月-作废 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='作废') as AAAAAAA 这个你知道吧 整合成一张表了 你在外面怎么对AAAAAAAl里面的每个Select条件做更改? 我最早使用视图 视图不能带参数 只有存储过程可以带参数 我想要select * from AAAAAAA where 年=月=日=的时候表AAAAAAA里面的where条件跟着变 不要跟我说select语句分开写 能分开写我就不会在这问了 你说这怎么办? 我的想法是写个存储过程 Select* from 存储过程 where 年=月=日= 应为存储过程可以带参数
SQL的 create proc proc_1 as(变量) begin select * from ,,, end exec proc_1 (变量)
你还是没看懂的意思 select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title into from ( --年-完成 select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --月-完成 select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --日-完成 select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' union all --作业中 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='申请' union all --年-作废 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='作废' union all --月-作废 select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf from REPORTVIEWER a inner join (select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='作废') as AAAAAAA 这个你知道吧 整合成一张表了 你在外面怎么对AAAAAAAl里面的每个Select条件做更改? 我最早使用视图 视图不能带参数 只有存储过程可以带参数 我想要select * from AAAAAAA where 年=月=日=的时候表AAAAAAA里面的where条件跟着变 不要跟我说select语句分开写 能分开写我就不会在这问了 你说这怎么办? 我的想法是写个存储过程 Select* from 存储过程 where 年=月=日= 应为存储过程可以带参数还能select * from 存储过程? 那你这就不是存储过程了,你写个function,返回值是一个table
关键就是怎么把外面select* from XXX where....的where判断条件带入到AAAAAAA里面去 六条select 不同就只有where
这是目标: 这是select语句:select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d where to_char(d.create_date,'yyyy')=××× group by rdlc_id,user_id) b on b.rdlc_id=a.sid inner join s_hse_ticket e on e.reghumsid=b.user_id where e.erifystate='完成' 一共六条 我不一一列出 上面有 另外 想做个按年月日的查询
各位看看改着呢麽办? where to_char(d.create_date,'yyyy')=×××,where e.erifystate='完成' 这有这里不同 这六条Select必须整合(这是绕不过去的),也就是说外面必须是select * from XXX where 年,月,日(问题来了),怎么用外面的where 年,月,日当作XXXX里面各条Select中的 where to_char(d.create_date,'yyyy')=××× 的判断条件?
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式
呃 不是很懂 能给个例子吗?
网上的看的感觉和SQL差不多,我看不出来改怎么改 我想做一个带参数的存储过程 然后再做一个临时表
实现select * from 临时表 where 年=... and 月=... and 日=...
因为在我的六条select语句中只有where后面的时间条件需要在外面获取
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式
呃 不是很懂 能给个例子吗?
declare
-- Local variables here
l_fund VARCHAR2(10);
begin
SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
end;
所以这种语法是错误的,你必须将查询的结果赋给变量,然后通过变量再用。
LZ需要学习的东西还很多。
不是很懂 在SQL中这样应该就可以了 为什么要必须将查询的结果赋给变量,然后通过变量再用?SQL中定义变量只是为了赋值给存储过程中的变量,确定条件而已,存储过程会生成一个虚拟的表。 Oracle中我就不知道了,那这个按你说的应该怎么改?
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式
呃 不是很懂 能给个例子吗?
declare
-- Local variables here
l_fund VARCHAR2(10);
begin
SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
end;
问一下 declare 是干什么用的?l_fund VARCHAR2(10);是变量吗?'sss'哪来的?dbms_output.put_line(l_fund);这句看不懂
存储过程里不能只写一个纯粹的查询语句,这样没有意义。可以写出SELECT INTO FROM 的形式
呃 不是很懂 能给个例子吗?
declare
-- Local variables here
l_fund VARCHAR2(10);
begin
SELECT 'sss' INTO l_fund FROM dual ;dbms_output.put_line(l_fund);
end;
问一下 declare 是干什么用的?l_fund VARCHAR2(10);是变量吗?'sss'哪来的?dbms_output.put_line(l_fund);这句看不懂看出来了,你连最基本的语法都不太清楚,所以自己去找例子研究研究吧 骚年
既然你有sql功底, 只是书写方式不同,找个例子不是一看就懂了么
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title into from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废') as AAAAAAA
这个你知道吧 整合成一张表了 你在外面怎么对AAAAAAAl里面的每个Select条件做更改?
我最早使用视图 视图不能带参数 只有存储过程可以带参数
我想要select * from AAAAAAA where 年=月=日=的时候表AAAAAAA里面的where条件跟着变
不要跟我说select语句分开写 能分开写我就不会在这问了
你说这怎么办? 我的想法是写个存储过程
Select* from 存储过程 where 年=月=日= 应为存储过程可以带参数
create proc proc_1
as(变量)
begin
select * from ,,,
end
exec proc_1 (变量)
select y_num_e,m_num_e,d_num_e,m_num_s,y_num_zf,m_num_zf,r_title into from
(
--年-完成
select distinct a.r_title,b.rdlc_num y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--月-完成
select distinct a.r_title,null y_num_e,b.rdlc_num m_num_e,null d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--日-完成
select distinct a.r_title,null y_num_e,null m_num_e,b.rdlc_num d_num_e,null m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'dd')=@daty group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
union all
--作业中
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,b.rdlc_num m_num_s,null y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='申请'
union all
--年-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,b.rdlc_num y_num_zf,null m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=@year group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废'
union all
--月-作废
select distinct a.r_title,null y_num_e,null m_num_e,null d_num_e,null m_num_s,null y_num_zf,b.rdlc_num m_num_zf
from REPORTVIEWER a
inner join
(select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'mm')=@month group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='作废') as AAAAAAA
这个你知道吧 整合成一张表了 你在外面怎么对AAAAAAAl里面的每个Select条件做更改?
我最早使用视图 视图不能带参数 只有存储过程可以带参数
我想要select * from AAAAAAA where 年=月=日=的时候表AAAAAAA里面的where条件跟着变
不要跟我说select语句分开写 能分开写我就不会在这问了
你说这怎么办? 我的想法是写个存储过程
Select* from 存储过程 where 年=月=日= 应为存储过程可以带参数还能select * from 存储过程? 那你这就不是存储过程了,你写个function,返回值是一个table
但是 我在网上看到过有人提问 select * from 存储过程 where。
说的是自已的不行 别人的为什么可以
六条select 不同就只有where
这是select语句:select sum(rdlc_num) as rdlc_num,rdlc_id,user_id from REPORTVIEWER_count d
where to_char(d.create_date,'yyyy')=××× group by rdlc_id,user_id) b
on b.rdlc_id=a.sid
inner join s_hse_ticket e on e.reghumsid=b.user_id
where e.erifystate='完成'
一共六条 我不一一列出 上面有 另外 想做个按年月日的查询