CodePudding user response:
You explain what do you want to do, is to realize what function, looking at it is not hard toI give you two examples, and then write a structure, a case is added in the where method, one is a string concatenation
Your program, that + condition in the second if I don't know what's condition, is the user input? Then the main conditions? Is also a enter
Finally the else I don't understand, afraid of you joke I have not learned the language and but I can read a about
The first is about the case in the where the joining together of
With the Core as
(select Co. course_number,
(case
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100
Then substr (' & amp; Param1 ', 1, 9)
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 1
Then the 'S1'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 2
Then 'S2'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 3
Then 'Q1'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 4
Then the 'Q2'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 5
Then 'Q3'
The when termid=((substr (' & amp; Param1 ', 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 (' & amp; Param1 ', 1, 4)) - '1990') * 100 the from dual))
Group by Co. course_number, Co. course_name, cc. Section_number, cc. Teacherid, cc. Studentid,
(case
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100
Then substr (' & amp; Param1 ', 1, 9)
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 1
Then the 'S1'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 2
Then 'S2'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 3
Then 'Q1'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 4
Then the 'Q2'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 5
Then 'Q3'
The when termid=((substr (' & amp; Param1 ', 1, 4)) - '1990') * 100 + 6
Then 'Q4'
End)
The order by course_number section_number)
The 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.l astfirst as TeacherName, student_number, s.l astfirst as StudentName, Sheldon horowitz ome_room, MF. CUST_FATHERMOBILEPHONE as FATHERPHONE, MF. CUST_FATHEREMAIL as FATHEREMAIL, MF. CUST_MOTHERMOBILEPHONE as MOTHERPHONE, MF. CUST_MOTHEREMAIL as MOTHEREMAIL
The from students s
Join the core c
On c.s. tudentid s.i d=
And s.e nroll_status='0'
The join of head of t
On t.i d=c.t eacherid
The join U_STUDENTSUSERFIELDS MF
On MF. Studentsdcid=s.d. cid
)
WHERE
Course_NUMBER=UPPER (' & amp; Param2 ')
And
(
(case
The when
'& amp; Param3 'is null
And 1=1
Then one end)=1
Or
(case
When '& amp; Param3 'is not null
And
(
Section_number in (
Select substr (regexp_substr (UPPER (' & amp; Param3 '),
'[^,] +'
1,
Level),
1
, 40)
The from dual
Connect by level & lt;=regexp_count (UPPER (' & amp; Param3 '),
', ') + 1)
)
Then one end)=1
)
The order by course_number section_number, studentname, student_number
;
Again is how to string concatenation (with input and output string concatenation) :
If object_id (' finaltest) is not null
Drop the proc finaltest
Go
The create proc finaltest
@ student_number INT the 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
The exec sp_executesql @ SQL, N '@ p int the 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 the out side -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Declare @ student_number_out int,
@ lastfirst_out varchar (50)
The exec finaltest
@ student_number=@ student_number_out output,
@ lastfirst=@ lastfirst_out output,
@ input_grade=9
Select @ student_number_out, @ lastfirst_out
Go
I've demonstrated the input and output sex, stored procedure, you said you want to do, I suppose you need to roll back the operation (the rollback, undo modify)
Then you need to add the begin in your procedure transaction, this call to a stored procedure
Finally run out you to manually reviewing these changes, and then input the rollback or commit
The begin transaction
The Execute Tonytest - here is the program you want to call you do anything, under the above are the beginning and the end of the stored procedure
The rollback transaction
The begin transaction
The Execute Tonytest
Commit the transaction
CodePudding user response:
In addition you didn't say running environment, best can you put this question to do it again,SQL language and other languages, C language to play when I was at school also can completely different way of thinking, but the two language
C from the ranks of consideration, kept the fetch cycle
SQL is a collection of statements, the programmer thinks of this set of target data has the common properties, all the dependency logic, through their programming features,
So in SQL, whether the joining together of the string or pointer should be as little as possible the use of SQL this program if you have to use a cursor for efficiency very insecure,
null