Cache学习（七）Query的概念以及操作方法（八）SQL实现增删改查（九）PLIST实现增删改查
-
Query的概念以及操作方法
query的作用可以在方法中被调用传出数据，替代w实现json数据的包装传值给前端
类似于sql
query的结构代码：
-
M语句的Sql常规增删改查以及PLIST增删改查
编写方法最好使用面向对象的方法
有返回值的方法执行时需要将d 改为w
plist入参
意义：从前端csp传递到后端调用方法与数据库交互
sql错误代码：
练习一：编写简单query
$g能够起到保护数据的作用，加入值不存在加上$g能够传回空值
query与普通method的区别：取出数据集，调用请求
query模板
入参、rowspec出参
/// function:编写使用query
/// input:RowId,Code,Name
/// GetList:入参、GetListExecute：执行，GetListFetch：从执行取数据出参，GetListClose：关闭临时global
/// others:d ##class(%ResultSet).RunQuery("User.DHCStudentSFCA","GetList")
Query GetList() As %query(ROWSPEC = "")
{
}
ClassMethod GetListExecute(ByRef qHandle As %Binary) As %Status
{
s repid=$i(^CacheTemp)
s ind=1
s qHandle=$lb(0,repid,0)
quit $$$OK
OutputRow2
s Data=$lb()
s ^Cachetmp(repid,ind)=Data
s ind=ind+1
quit
}
ClassMethod GetListFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
{
}
ClassMethod GetListClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = GetStudentDataExecute ]
{
Set repid=$LIST(qHandle,2)
kill ^CacheTemp(repid)
Quit $$$OK
}
}首先定义入参及其类型，定义出参
Cache7 0.15.39
编写查询学生数据query
/// function:编写使用query
/// input:RowId,Code,Name
/// others: d ##class(%ResultSet).RunQuery("User.DHCStudentSFCA","GetList","2")
Query GetList(Code As %String) As %Query(ROWSPEC = "StRowId:%String,StCode:%String,StName:%String,StSexDR:%String,StDob:%String")
{
}
ClassMethod GetListExecute(ByRef qHandle As %Binary, Code As %String) As %Status
{
set repid=$I(^CacheTemp)
set ind=1
///StRowId,StCode,StName,StSexDR,StDob
set StRowId=0
for{
set StRowId=$o(^DHCSTUDENTSFCA(StRowId)) q:StRowId=""
set StCode=$p($g(^DHCSTUDENTSFCA(StRowId)),"^",1)
set StName=$p($g(^DHCSTUDENTSFCA(StRowId)),"^",2)
set StSexDR=$p($g(^DHCSTUDENTSFCA(StRowId)),"^",3) ///取性别描述
set:StSexDR'="" StSexDR=$p($g(^CT("SEX",StSexDR)),"^",2)
set StDob=$p($g(^DHCSTUDENTSFCA(StRowId)),"^",4)
set:StDob'="" StDob=$zd(StDob,3)
///[包含
///输出至CacheTemp
if (StCode[Code){
set ^Cachetmp(repid,ind)=$lb(StRowId,StCode,StName,StSexDR,StDob)
set ind=ind+1
}
}
set qHandle=$lb(0,repid,0)
quit $$$OK
}
ClassMethod GetListFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
{
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)
}
set qHandle=$lb(AtEnd,repid,ind)
quit $$$OK
}
ClassMethod GetListClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = GetListExecute ]
{
Set repid=$LIST(qHandle,2)
kill ^CacheTemp(repid)
Quit $$$OK
}练习二：使用sql对学生表实现新增
导包：Import SqlUser
存数据库时需要将时间转为数字类型StDob=$zdh(StDob,3)
b:断点
有返回值的使用w执行方法
0.12.13 cache8
/// function：使用sql操作，数据新增
/// table:DHC_StudentSFCA
/// others：d ##class(User.DHCStudentSFCA).InsertData("007","ccc","1","2001-02-21")
ClassMethod InsertData(StCode As %String, StName As %String, StSexDR As %String, StDob As %String) As %String
{
///zhuanhuanriqi
if StDob'="" s StDob=$zdh(StDob,3)
&SQL(INSERT INTO DHC_StudentSFCA
VALUES(:StCode,:StName,:StSexDR,:StDob)
)
if SQLCODE=0{
s result="success"
}
else{
s result="error"_%msg
}
q result
}部分新增
/// function：使用SQL操作部分新增，数据新增
/// table:MRC_ICDDx
/// var:MRCID_Code,MRCID_Desc,MRCID_ICD9CM_Code,MRCID_DateActiveFrom,MRCID_Valid
/// others：d ##class(User.DHCStudentSFCA).AddMRCTCD("ZY0571ATEST","ccc1","ccc1","BES060")
ClassMethod AddMRCICD(MRCIDCode, MRCIDDesc, MRCIDICD9CMCode, MRCIDDateActiveFrom, MRCIDValid) As %String
{
///zhuanhuanriqi
if MRCIDDateActiveFrom'="" s MRCIDDateActiveFrom=$zdh(MRCIDDateActiveFrom,3)
&SQL(INSERT INTO MRC_ICDDx (MRCID_Code,MRCID_Desc,MRCID_ICD9CM_Code,MRCID_DateActiveFrom,MRCID_Valid)
VALUES(:MRCIDCode,:MRCIDDesc,:MRCIDICD9CMCode,:MRCIDDateActiveFrom,:MRCIDValid)
)
if SQLCODE=0{
s result="success"
}
else{
s result="error"_%msg
}
q result
}练习三：使用plist对学生表实现新增操作
根据列顺序确定，PLIST（）取值，列顺序可以在开发工具中找到，查看list即可
和sql的区别就是将数据放入数组中，在数据量大的情况下比较好管理
/// function：plist新增
/// table:MRC_ICDDx
/// others:
ClassMethod AddMRCICDByPLIST(MRCIDCode, MRCIDDesc, MRCIDICD9CMCode, MRCIDDateActiveFrom, MRCIDValid) As %String
{
if MRCIDDateActiveFrom'="" s MRCIDDateActiveFrom=$zdh(MRCIDDateActiveFrom,3)
k PList
s PList(2)=MRCIDCode
s PList(3)=MRCIDDesc
s PList(6)=MRCIDICD9CMCode
s PList(8)=MRCIDDateActiveFrom
s PList(29)=MRCIDValid
&SQL(
INSERT INTO MRC_ICDDx
VALUES :PLIST()
)
}练习四：使用两种方法对学生表实现更新
/// function:sql数据更新
/// input：St_Name,St_SexDR,St_Dob
/// table: DHC_StudentSFCA
/// other:d ##class(User.DHCStudentSFCA).UpdateData("3","liming","1","2001-02-21")
ClassMethod UpdateData(StRowId, StName, StSexDR, StDob) As %String
{
s:StDob'="" StDob=$zdh(StDob,3)
&sql(
UPDATE DHC_StudentSFCA
SET St_Name=:StName,St_Sex_DR=:StSexDR,St_Dob=:StDob
WHERE St_RowId=:StRowId
)
if (SQLCODE=0){
s result="sucsses"
}
else{
s result="error"
}
q result
}
/// function:PLIST数据更新
/// input：St_Name,St_SexDR,St_Dob
/// table: DHC_StudentSFCA
/// other:d ##class(User.DHCStudentSFCA).UpdateDataByPList("3","liming","1","2001-02-21")
ClassMethod UpdateDataByPList(StRowId, StName, StSexDR, StDob) As %String
{
q:StRowId="" "rowid is null"
k PLIST
s:StDob'="" StDob=$zdh(StDob,3)
s PLIST(3)=StName
s PLIST(4)=StSexDR
s PLIST(5)=StDob
&sql(
UPDATE DHC_StudentSFCA
VALUES :PLIST()
WHERE St_RowId=:StRowId
)
}练习五：使用两种方法对学生表实现删除操作
cache9 0.41.41
有返回值必须使用w执行命令
_%msg 执行sql错误值
/// function:数据删除
/// Table：DHC_StudentSFCA
/// Inputh:St_RowId 必须要加条件否则删除所有数据
/// output:0失败1成功
/// others：w ##class(User.DHCStudentSFCA).DeleteData("")
ClassMethod DeleteData(StRowId As %String) As %String
{
q:StRowId="" "rowid is null"
q:$d(^DHCSTUDENTSFCA(StRowId))=0 "data is null"
&SQL(
DELETE FROM DHC_StudentSFCA
WHERE St_RowId=:StRowId
)
if (SQLCODE=0) {
s Result=1
}
else {
s Result=0_%msg
}
q Result
}练习六：使用两种方法对学生表实现查询操作
查询一条
SQL INSERT INTO SELECT 语句：通过SQL，可以从一个表复制信息到另一个表。INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
SELECT vale1,value2 into Table2 from Table1; 类方法中的INTO不是Sql中的用法，是给变量赋值
/// function:数据查询（一条）
/// Table：DHC_StudentSFCA
/// Inputh:St_RowId 必须要加条件否则删除所有数据
/// output:0失败1成功
/// others：w ##class(User.DHCStudentSFCA).GetList("3")
ClassMethod GetList(StRowId As %String) As %String
{
Quit:StRowId="" "rowid is null"
Quit:$DATA(^DHCSTUDENTSFCA(StRowId))=0 "data is null"
&sql(
SELECT St_Code,St_Name,St_Sex_DR,St_Dob
INTO :StCode,:StName,:StSexDR,:StDob
FROM DHC_StudentSFCA WHERE St_RowId=:StRowId
)
Set:StDob'="" StDob=$ZDATE(StDob,3)
Set:StSexDR'="" StSexDR=$PIECE(^CT("SEX",StSexDR),"^",2)
If (SQLCODE=0){
Quit "学号:"_StCode_"姓名:"_StName_"性别"_StSexDR_"出生日期"_""
}
Else{
Quit "error"
}
}
取PLIST最大标号
s cnt=$o(PLIST(""),-1) 相当于for(i=1,i++,i<=cnt)
for i=1:1:cnt /// function:plist数据查询（一条）,但是字段很多，使用for循环输出plist
/// Table：DHC_StudentSFCA
/// Inputh:St_RowId 必须要加条件否则删除所有数据
/// output:0失败1成功
/// others：w ##class(User.DHCStudentSFCA).GetListByPLIST("3")
ClassMethod GetListByPLIST(StRowId as%String) as %String{
Quit:StRowId="" "StRowId is null"
Quit:$DATA(^DHCSTUDENTSFCA(StRowId))=0 "data is null"
&sql(
SELECT * INTO :PLIST()
FROM DHC_StudentSFCA
WHERE St_RowID=:StRowId
)
Set cnt=$ORDER(PLIST(""),-1) //取PLIST最大标号
For i=1:1:cnt{
Write " "
Write PLIST(i)
}
}
查询全部数据（不做掌握）面向对象的方法
///function:使用面向对象的方法查询全部数据
/// d ##class(User.DHCStudentSFCA).GetAllList()
ClassMethod GetAllList() as %String{
Set sqlstr="SELECT * FROM DHC_StudentSFCA"
Set result=##class(%ResultSet).%New()
If (result){
Do result.Prepare(sqlstr)
Do result.Execute()
While(result.Next()){
Set StRowId=result.Data("St_RowId")
Set StCode=result.Data("St_Code")
Set StName=result.Data("St_Name")
Set StSexDR=result.Data("St_Sex_DR")
Set StDob=result.Data("St_Dob")
Set:StDob'="" StDob=$ZDATE(StDob,3)
Set:StSexDR'="" StSexDR=$PIECE(^CT("SEX",StSexDR),"^",2)
w "学号"_StCode_"姓名"_StName_"性别"_StSexDR_"出生日期"_StDob
w !
}
}
}cache取数据就是取global，关系型数据库很少使用sql
