SQL在query中写法

1、列表方式取sql多条数据循环输出

ClassMethod GetOprDispClassExecute(ByRef qHandle As %Binary, stdate As %String, endate As %String, loc As %String, ClassName As %String) As %Status
{
	 n (qHandle, stdate,endate,loc,ClassName)
	 Set repid=$I(^CacheTemp)
 	 Set qHandle=$lb(0,repid,0)
  	 Set ind=1
  	 q:stdate="" $$$OK
	 q:endate="" $$$OK
  	  s sqlStr=" select c.CSSDP_Desc as PackageName, sum(a.Cssdpd_qty) as qty,a.CSSDPD_Package_DR->CSSDP_UnitPrice as Price"
            	 	_" From cssd_packagedisp a,cssd_packageclass b,cssd_package c where"
            	 	_" b.ID=c.CSSDP_PackClassdr and c.CSSDP_Rowid=a.CSSDPD_Package_DR and"
             	 	_" a.CSSDPD_Date>='"_stdate_"' and a.CSSDPD_Date<='"_endate_"'"
   	 ;s sqlStr=sqlStr1_" and a.CSSDPD_ToLoc_DR='"_loc_"' and b.CSSDPC_PackClassName='"_ClassName_"' group by a.CSSDPD_Package_DR"
  	 i loc'="" s sqlStr=sqlStr_" and a.CSSDPD_ToLoc_DR='"_loc_"'"
    	 i ClassName'="" s sqlStr=sqlStr_" and b.CSSDPC_PackClassName='"_ClassName_"'"
	 s sqlStr=sqlStr_" group by a.CSSDPD_Package_DR"
   	 s result=##class(%Library.ResultSet).%New()
	 d result.Prepare(sqlStr)
	 w sqlStr,!
	 d result.Execute()
	 While(result.Next()){
     		 ;s ID=result.Data("ID")
      		s PackageName=result.Data("PackageName")
      		s qty=result.Data("qty")
      		s Price=result.Data("Price")
      		s total=qty*Price
 		d OutputRow20
}
  		 q $$$OK
OutputRow20
    		s Data=$lb(PackageName,qty,Price,total)
 		s ^CacheTemp(repid,ind)=Data
  		s ind=ind+1
		q
}

ClassMethod GetOprDispClassFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = GetOprDispClassExecute ]
{
		Set AtEnd=$LIST(qHandle,1)
  		Set repid=$LIST(qHandle,2)
 		 Set ind=$LIST(qHandle,3)
  		Set ind=$o(^CacheTemp(repid,ind))
 		 If ind="" {
  			Set AtEnd=1
  			Set Row=""
  		}
  		Else      {
  			Set Row=^CacheTemp(repid,ind)
 		 }
  		s qHandle=$lb(AtEnd,repid,ind)
		Quit $$$OK
}

ClassMethod GetOprDispClassClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = GetOprDispClassExecute ]
{
		Set repid=$LIST(qHandle,2)
  		Kill ^CacheTemp(repid)
  		Quit $$$OK
}

2、用游标方式取sql数据循环

ClassMethod FindTarByArcExecute(ByRef qHandle As %Binary, ArcRowid, HospID) As %Status
{
	;d ##class(%ResultSet).RunQuery("DHCBILLConfig.DHCBILLFIND","FindTarByArc","11009||1", 2)
	Set repid=$I(^CacheTemp)
	Set ind=1
	Kill PLIST
	Set num=0
	Set today=+$H
	&sql(declare taricur cursor for
		select olt_tariff_dr,olt_tariff_dr->tari_code,olt_tariff_dr->tari_desc,
		olt_qty,olt_startdate,olt_enddate,olt_inst_dr,olt_inst_dr->phcin_desc1,
		olt_priority_dr,olt_priority_dr->oecpr_desc,olt_rowid
		from  dhc_orderlinktar  
		where olt_arcim_dr=:ArcRowid)
	&sql(open taricur)
	For  &sql(fetch taricur into:tarirowid,:taricode,:taridesc,:qty,:stdate,:enddate,:yfrowid,:yfdesc,:priorrowid,:priordesc,:oltrowid)  q:SQLCODE  d
	.Set num=num+1
	.Set enddate1=enddate
	.If +stdate'="0" d  s stdate=$zd(stdate,3)
	.If +enddate'="0" d  s enddate=$zd(enddate,3)
	.Set itemprice=+$$GetItmPrice^DHCPRICE(tarirowid,today,"","","",HospID)
	.Set PLIST(num)=taricode_"^"_taridesc_"^"_qty_"^"_stdate_"^"_enddate_"^"_yfdesc_"^"_priordesc_"^"_oltrowid_"^"_tarirowid_"^"_yfrowid_"^"_priorrowid_"^"_itemprice
	&sql(close taricur)
	Set PLIST=num
	If PLIST<=0 Set qHandle=$lb(0,repid,0) Quit $$$OK
	Set Num=PLIST
	For i=1:1:Num Do
	.Set TarInfo=PLIST(i)
	.Set TarCode=$p(TarInfo,"^",1)
	.Set TarDesc=$p(TarInfo,"^",2)
	.Set TarQty=$p(TarInfo,"^",3)
	.Set TarStDate=$p(TarInfo,"^",4)
	.Set TarEndDate=$p(TarInfo,"^",5)
	.Set TarYF=$p(TarInfo,"^",6)
	.Set TarPrority=$p(TarInfo,"^",7)
	.Set Oltrowid=$p(TarInfo,"^",8)
	.Set TarRowid=$p(TarInfo,"^",9)
	.Set YFRowid=$p(TarInfo,"^",10)
	.Set PriorityRowid=$p(TarInfo,"^",11)
	.Set TarPrice=$p(TarInfo,"^",12)
	.If $p(TarPrice,".",1)="" Set TarPrice="0"_TarPrice
	.Set TarCode=..EvalJSON(TarCode)
	.Set TarDesc=..EvalJSON(TarDesc)
	.Set TarYF=..EvalJSON(TarYF)
	.Set TarPrority=..EvalJSON(TarPrority)
	.Do OutputRowArcTar
	Set qHandle=$lb(0,repid,0)
	Quit $$$OK
OutputRowArcTar
	Set Data=$lb(TarCode,TarDesc,TarQty,TarStDate,TarEndDate,TarYF,TarPrority,Oltrowid,TarRowid,YFRowid,PriorityRowid,TarPrice)
	Set ^CacheTemp(repid,ind)=Data
	Set ind=ind+1
	Quit
}

ClassMethod FindTarByArcClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = FindTarByArcExecute ]
{
	Set repid=$LIST(qHandle,2)
 	Kill ^CacheTemp(repid)
	Quit $$$OK
}