你说明一下你要干啥吧,就是实现的是什么功能,看着不难的 我给你两个例子,再写个结构,一个是在where里加case的方法,一个是字符串拼接 你的程序,第二个if里那个 + condition 我不知道condition是啥,是用户输入的东西?然后主条件里?也是个输入吗 最后else我看不懂,不怕你笑话这个语言我没学过,不过我能看懂个大概第一个是关于case在where里的拼接 with Core as (select co.course_number, (case when termid=((substr('¶m1',1,4))-'1990') *100 then substr('¶m1',1,9) when termid=((substr('¶m1',1,4))-'1990')*100+1 then 'S1' when termid=((substr('¶m1',1,4))-'1990')*100+2 then 'S2' when termid=((substr('¶m1',1,4))-'1990')*100+3 then 'Q1' when termid=((substr('¶m1',1,4))-'1990')*100+4 then 'Q2' when termid=((substr('¶m1',1,4))-'1990')*100+5 then 'Q3' when termid=((substr('¶m1',1,4))-'1990')*100+6 then 'Q4' end) as Term, co.course_name,cc.section_number,cc.teacherid,cc.studentid from cc join courses co on co.course_number=cc.course_number and termid>=UPPER( (select ((substr('¶m1',1,4))-'1990')*100 from dual)) group by co.course_number, co.course_name,cc.section_number,cc.teacherid,cc.studentid, (case when termid=((substr('¶m1',1,4))-'1990') *100 then substr('¶m1',1,9) when termid=((substr('¶m1',1,4))-'1990')*100+1 then 'S1' when termid=((substr('¶m1',1,4))-'1990')*100+2 then 'S2' when termid=((substr('¶m1',1,4))-'1990')*100+3 then 'Q1' when termid=((substr('¶m1',1,4))-'1990')*100+4 then 'Q2' when termid=((substr('¶m1',1,4))-'1990')*100+5 then 'Q3' when termid=((substr('¶m1',1,4))-'1990')*100+6 then 'Q4' end) order by course_number,section_number) SELECT COURSE_NUMBER,TERM,COURSE_NAME,SECTION_NUMBER,TEACHERNAME,STUDENT_NUMBER,StudentName,HOME_ROOM,FATHERPHONE,FATHEREMAIL,MOTHERPHONE,MOTHEREMAIL FROM ( select course_number,Term,course_name,section_number,t.lastfirst as TeacherName,student_number,s.lastfirst as StudentName,s.home_room,MF.CUST_FATHERMOBILEPHONE AS FATHERPHONE,MF.CUST_FATHEREMAIL AS FATHEREMAIL,MF.CUST_MOTHERMOBILEPHONE AS MOTHERPHONE,MF.CUST_MOTHEREMAIL AS MOTHEREMAIL from students s join core c on s.id=c.studentid and s.enroll_status='0' join teachers t on t.id=c.teacherid join U_STUDENTSUSERFIELDS MF on MF.studentsdcid=s.dcid ) WHERE course_NUMBER=UPPER('¶m2') and ( (case when '¶m3' is null and 1=1 then 1 end )=1 or (case when '¶m3' is not null and ( section_number in ( select substr(regexp_substr(UPPER('¶m3'), '[^,]+', 1, level), 1 ,40) from dual connect by level <= regexp_count(UPPER('¶m3'), ',') + 1) ) then 1 end)=1 ) order by course_number,section_number,studentname,student_number ;再来就是怎么拼接字符串(带输入输出的字符串拼接): if object_id('finaltest') is not null drop proc finaltest go create proc finaltest @student_number INT output, @lastfirst varchar(50) output, @input_grade int as declare @sql NVARCHAR(500) set @sql=N'select @p=student_number,@q=lastfirst from students where grade_level=@i' exec sp_executesql @sql,N'@p int output,@q varchar(50) output,@i int', @p=@student_number output , @q=@lastfirst output, @i=@input_grade select @student_number,@lastfirst,@input_grade,@sql go
---------------------------------------------below is out side--------------------------------------------------------
我给你两个例子,再写个结构,一个是在where里加case的方法,一个是字符串拼接
你的程序,第二个if里那个 + condition 我不知道condition是啥,是用户输入的东西?然后主条件里?也是个输入吗
最后else我看不懂,不怕你笑话这个语言我没学过,不过我能看懂个大概第一个是关于case在where里的拼接
with Core as
(select co.course_number,
(case
when termid=((substr('¶m1',1,4))-'1990') *100
then substr('¶m1',1,9)
when termid=((substr('¶m1',1,4))-'1990')*100+1
then 'S1'
when termid=((substr('¶m1',1,4))-'1990')*100+2
then 'S2'
when termid=((substr('¶m1',1,4))-'1990')*100+3
then 'Q1'
when termid=((substr('¶m1',1,4))-'1990')*100+4
then 'Q2'
when termid=((substr('¶m1',1,4))-'1990')*100+5
then 'Q3'
when termid=((substr('¶m1',1,4))-'1990')*100+6
then 'Q4'
end)
as Term,
co.course_name,cc.section_number,cc.teacherid,cc.studentid from
cc join courses co
on co.course_number=cc.course_number
and termid>=UPPER(
(select ((substr('¶m1',1,4))-'1990')*100 from dual))
group by co.course_number, co.course_name,cc.section_number,cc.teacherid,cc.studentid,
(case
when termid=((substr('¶m1',1,4))-'1990') *100
then substr('¶m1',1,9)
when termid=((substr('¶m1',1,4))-'1990')*100+1
then 'S1'
when termid=((substr('¶m1',1,4))-'1990')*100+2
then 'S2'
when termid=((substr('¶m1',1,4))-'1990')*100+3
then 'Q1'
when termid=((substr('¶m1',1,4))-'1990')*100+4
then 'Q2'
when termid=((substr('¶m1',1,4))-'1990')*100+5
then 'Q3'
when termid=((substr('¶m1',1,4))-'1990')*100+6
then 'Q4'
end)
order by course_number,section_number)
SELECT COURSE_NUMBER,TERM,COURSE_NAME,SECTION_NUMBER,TEACHERNAME,STUDENT_NUMBER,StudentName,HOME_ROOM,FATHERPHONE,FATHEREMAIL,MOTHERPHONE,MOTHEREMAIL FROM
(
select course_number,Term,course_name,section_number,t.lastfirst as TeacherName,student_number,s.lastfirst as StudentName,s.home_room,MF.CUST_FATHERMOBILEPHONE AS FATHERPHONE,MF.CUST_FATHEREMAIL AS FATHEREMAIL,MF.CUST_MOTHERMOBILEPHONE AS MOTHERPHONE,MF.CUST_MOTHEREMAIL AS MOTHEREMAIL
from students s
join core c
on s.id=c.studentid
and s.enroll_status='0'
join teachers t
on t.id=c.teacherid
join U_STUDENTSUSERFIELDS MF
on MF.studentsdcid=s.dcid
)
WHERE
course_NUMBER=UPPER('¶m2')
and
(
(case
when
'¶m3' is null
and 1=1
then 1 end )=1
or
(case
when '¶m3' is not null
and
(
section_number in (
select substr(regexp_substr(UPPER('¶m3'),
'[^,]+',
1,
level),
1
,40)
from dual
connect by level <= regexp_count(UPPER('¶m3'),
',') + 1)
)
then 1 end)=1
)
order by course_number,section_number,studentname,student_number
;再来就是怎么拼接字符串(带输入输出的字符串拼接):
if object_id('finaltest') is not null
drop proc finaltest
go
create proc finaltest
@student_number INT output,
@lastfirst varchar(50) output,
@input_grade int
as
declare @sql NVARCHAR(500)
set @sql=N'select @p=student_number,@q=lastfirst from students where grade_level=@i'
exec sp_executesql @sql,N'@p int output,@q varchar(50) output,@i int',
@p=@student_number output ,
@q=@lastfirst output,
@i=@input_grade
select @student_number,@lastfirst,@input_grade,@sql
go
---------------------------------------------below is out side--------------------------------------------------------
declare @student_number_out int,
@lastfirst_out varchar(50)
exec finaltest
@student_number=@student_number_out output,
@lastfirst=@lastfirst_out output,
@input_grade=9
select @student_number_out,@lastfirst_out
go输入输出性我已经展示了,你说你要做存储过程,我假设你需要进行回滚操作(rollback,撤销修改)
那你需要在你的procedure里面加上begin transaction,这个叫开始一个存储过程最后运行完你要手动回顾这些修改,然后输入rollback或者commit
begin transaction
Execute Tonytest ---这里是你自己做的程序 你想叫什么都行,上面下面分别是存储过程的开始和结束
rollback transactionbegin transaction
Execute Tonytest
commit transaction
sql是一种集合语句,编程者考虑的是这一组目标数据具有的共同属性,一切都从属性逻辑出发,通过他们的特性进行编程。所以在sql里面,不管是字符串的拼接还是指针 都应该尽可能少的使用,sql这种程序如果你非要用游标来做的话效率非常没有保障。所以你可以说你的需求,让我活着楼下的大神们用sql的逻辑给你编,不要在这里改C系的程序,完全不同的逻辑体系