.
| __DECLARE_CURSOR__ MyCursor __DEALLOCATE_CURSOR__ MyCursor | declare MyCursor insensitive cursor for deallocate #ifdef SYB_STYLE cursor #endif MyCursor |
| __DEALLOCATE_CURSOR__ MyCursor | __DEALLOCATE_CURSOR__ MyCursor |
CREATE PROC
, , 2 , , .
.
| create proc Audit_Insert @Action DSTINYINT = NULL, @ObjectID DSIDENTIFIER = NULL, |
| create proc Audit_Insert @Action DSTINYINT = NULL, @ObjectID DSIDENTIFIER = NULL, |
EXEC
, , , 2 , , .
.
| exec MyProc @ID = @ID out, @ObjectID = @ObjID | exec MyProc @ID out, @ObjectID exec MyProc @ID out, @ObjectID |
80 , .
.
| exec MyProc @ID = @ID, @ObjectID = @ObjID | exec MyProc @ID, @ObjectID |
, .
.
| exec MyProc @ID = @ID, @ObjectID = @ObjID | exec MyProc @ID, @ObjectID |
CASE
:
1. :
Case
when { 1}
and { 2}
then {1}
when { 3}
and { 4}
then {2}
else {3}
End
2. , 80 :
Case
when { 1} then {1}
when { 2} then {2}
else {3}
End
case end , when - , then and, or, , . then , .. then , , .
.
| case when b.timeType = 1 then case when b.time1 <> '19000101' and b.time2 = '19000101' then dateadd(yy,1,b.time1) when b.time2 <> '19000101' and b.time1 = '19000101' then b.time2 when b.time1 <> '19000101' and b.time2 <> '19000101' then b.time2 else dateadd(yy,1,b.billDate) end when b.timeType = 2 and b.PresDate = '19000101' then dateadd(yy,1,b.billDate) else '19000101' end |
|
|
|
| case when b.timeType = 1 then case when b.time1 <> '19000101' and b.time2 = '19000101' then dateadd(yy,1,b.time1) when b.time2 <> '19000101' and b.time1 = '19000101' then b.time2 when b.time1 <> '19000101' and b.time2 <> '19000101' then b.time2 else dateadd(yy,1,b.billDate) end when b.timeType = and b.PresDate = '19000101' then dateadd(yy,1,b.billDate) when b.timeType = 2 and b.PresDate <>'19000101' then dateadd(dd,b.daysFrom,b.PresDate) when b.timeType = 3 then dateadd(dd,b.daysFrom,b.billDate) when b.timeType = 4 and b.time1 <> '19000101' then b.time1 else '19000101' end |
left join inner join
:
1 - outer:
select { }
from { }
left join {2}
on { 1 2}
and { 2 2}
inner join {3}
on { 1 3}
2 - c outer:
select { }
from { }
Left outer
join {2}
on { 1 2}
and { 2 2}
inner join {3}
on { 1 3}
outer.
4.3.10.2 left [outer] join / inner join, on and, or .
( ) (ANSI, T-SQL). ( ) from inner join left join, , where. .
4.3.10.4 ANSI- left [outer] join inner join, inner. outer join, right join . , , ( , from).
1.
| select a.Date, a.AccDeb, a.AccCre, a.QtyDeb, a.QtyCre, a.SwiftID from #Aviso a left join tSwift sw #M_NOLOCK_INDEX(XPKtSwift) on sw.SwiftID = a.SwiftID inner join tInstitution id #M_NOLOCK_INDEX(XPKtInstitution) on id.InstitutionID = a.InstOwnerDeb inner join tInstitution ic #M_NOLOCK_INDEX(XPKtInstitution) on ic.InstitutionID = a.InstOwnerCre left join tCountry cds #M_NOLOCK_INDEX(XPKtCountry) on cds.CountryID = id.CountryID left join tCountry cdg #M_NOLOCK_INDEX(XPKtCountry) on cdg.CountryID = id.InstGroupID left join tResource r #M_NOLOCK_INDEX(XPKtResource) on r.ResourceID = a.ResourcePsvID left join tPayInstruct p1 #M_NOLOCK_INDEX(XIE2tPayInstruct) on p1.DealTransactID = a.Reference andp1.Type = 2 and p1.Belong = 1 |
2. outer
|
|
|
| select a.Date, a.AccDeb, a.AccCre, a.QtyDeb, a.QtyCre, a.SwiftID from #Aviso a left outer join tSwift sw #M_NOLOCK_INDEX(XPKtSwift) on sw.SwiftID = a.SwiftID inner join tInstitution id #M_NOLOCK_INDEX(XPKtInstitution) on id.InstitutionID = a.InstOwnerDeb inner join tInstitution ic #M_NOLOCK_INDEX(XPKtInstitution) on ic.InstitutionID = a.InstOwnerCre left outer join tCountry cds #M_NOLOCK_INDEX(XPKtCountry) on cds.CountryID = id.CountryID left outer join tCountry cdg #M_NOLOCK_INDEX(XPKtCountry) on cdg.CountryID = id.InstGroupID left outer join tResource r #M_NOLOCK_INDEX(XPKtResource) on r.ResourceID = a.ResourcePsvID left outer join tPayInstruct p1 #M_NOLOCK_INDEX(XIE2tPayInstruct) on p1.DealTransactID = a.Reference andp1.Type = 2 and p1.Belong = 1 |
| select a.Date, a.AccDeb, a.AccCre, a.QtyDeb, a.QtyCre, a.SwiftID from #Aviso a left outer join tSwift sw #M_NOLOCK_INDEX(XPKtSwift) on sw.SwiftID = a.SwiftID inner join tInstitution id #M_NOLOCK_INDEX(XPKtInstitution) on id.InstitutionID = a.InstOwnerDeb inner join tInstitution ic #M_NOLOCK_INDEX(XPKtInstitution) on ic.InstitutionID = a.InstOwnerCre left outer join tCountry cds #M_NOLOCK_INDEX(XPKtCountry) on cds.CountryID = id.CountryID left outer join tCountry cdg #M_NOLOCK_INDEX(XPKtCountry) on cdg.CountryID = id.InstGroupID left outer join tResource r #M_NOLOCK_INDEX(XPKtResource) on r.ResourceID = a.ResourcePsvID left outer join tPayInstruct p1 #M_NOLOCK_INDEX(XIE2tPayInstruct) on p1.DealTransactID = a.Reference andp1.Type = 2and p1.Belong = 1 |
Transact SQL . : begin transaction, end transaction ... . , .
