Cache学习(五)索引查询的编写
上一章小结:
从头创建类,必须有Row_ID 这一列,不用写在方法中,选择时重命名即可。
没有创建storage时,数据存储至默认storage中。
-
索引查询遍历
-
常见变量
-
常见命令
$d: 11代表有子节点且该节点有值 10代表有子节点且该节点无值。1代表该节点有值,0代表该节点无值。
练习1.创建科目表
cache5 05.35
- 默认存储的课程表
/// function:课程表
/// createdate:2023年8月9日
/// creator:houyuxuan
/// 字段:Crs_Code,Crs_Desc,Crs_ActiveFlag (Y/ N),Crs_RowID
Class User.DHCCourseSFC Extends %Persistent [ SqlRowIdName = Crs_RowID, SqlTableName = DHC_CourseSFC ]
{
/// 课程代码
Property CrsCode As %String [ SqlColumnNumber = 2, SqlFieldName = Crs_Code ];
/// 课程名称
Property CrsDesc As %String [ SqlColumnNumber = 3, SqlFieldName = Crs_Desc ];
/// 有效
Property CrsActive As %String(DISPLAYLIST = ",Yes,No", VALUELIST = ",Y,N") [ SqlColumnNumber = 4, SqlFieldName = St_Active ];
Storage Default
{
<Data name="DHCCourseSFCDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>CrsCode</Value>
</Value>
<Value name="3">
<Value>CrsDesc</Value>
</Value>
<Value name="4">
<Value>CrsActive</Value>
</Value>
</Data>
<DataLocation>^User.DHCCourseSFCD</DataLocation>
<DefaultData>DHCCourseSFCDefaultData</DefaultData>
<IdLocation>^User.DHCCourseSFCD</IdLocation>
<IndexLocation>^User.DHCCourseSFCI</IndexLocation>
<StreamLocation>^User.DHCCourseSFCS</StreamLocation>
<Type>%Library.CacheStorage</Type>
}
}
当删除默认存储,global发生变化,原先默认存储在^User.DHCCourseSFCD中
使用$i(^DHCCOURSESFC(0))获取count值
- sql存储的有索引课程表
d ##class(User.DHCCourseSFC).%BuildIndices() 有数据新建一下索引
/// function:课程表
/// createdate:2023年8月9日
/// creator:houyuxuan
/// 字段:Crs_Code,Crs_Desc,Crs_ActiveFlag (Y/ N),Crs_RowID
Class User.DHCCourseSFC Extends %Persistent [ SqlRowIdName = Crs_RowID, SqlTableName = DHC_CourseSFC, StorageStrategy = NewStorage1 ]
{
/// 课程代码
Property CrsCode As %String [ SqlColumnNumber = 2, SqlFieldName = Crs_Code ];
/// 课程名称
Property CrsDesc As %String [ SqlColumnNumber = 3, SqlFieldName = Crs_Desc ];
/// 有效
Property CrsActive As %String(DISPLAYLIST = ",Yes,No", VALUELIST = ",Y,N") [ SqlColumnNumber = 4, SqlFieldName = St_Active ];
Storage NewStorage1
{
<SqlIdExpression>$i(^DHCCOURSESFC(0))</SqlIdExpression>
<SQLMap name="DataMaster">
<ConditionalWithHostVars></ConditionalWithHostVars>
<Data name="Crs_Code">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="Crs_Desc">
<Delimiter>"^"</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="St_Active">
<Delimiter>"^"</Delimiter>
<Piece>3</Piece>
</Data>
<Global>^DHCCOURSESFC</Global>
<RowIdSpec name="1">
<Expression>{L1}</Expression>
<Field>Crs_RowID</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>{Crs_RowID}</Expression>
<StartValue>1</StartValue>
</Subscript>
<Type>data</Type>
</SQLMap>
<SQLMap name="IndexCode">
<ConditionalWithHostVars></ConditionalWithHostVars>
<Global>^DHCCOURSESFC</Global>
<PopulationType>nonnull</PopulationType>
<RowIdSpec name="1">
<Expression>{L4}</Expression>
<Field>Crs_RowID</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>0</Expression>
</Subscript>
<Subscript name="2">
<Expression>"code"</Expression>
</Subscript>
<Subscript name="3">
<Expression>{Crs_Code}</Expression>
</Subscript>
<Subscript name="4">
<Expression>{Crs_RowID}</Expression>
</Subscript>
<Type>index</Type>
</SQLMap>
<SQLMap name="IndexDesc">
<ConditionalWithHostVars></ConditionalWithHostVars>
<Global>^DHCCOURSESFC</Global>
<PopulationType>nonnull</PopulationType>
<RowIdSpec name="1">
<Expression>{L4}</Expression>
<Field>Crs_RowID</Field>
</RowIdSpec>
<Subscript name="1">
<Expression>0</Expression>
</Subscript>
<Subscript name="2">
<Expression>"Desc"</Expression>
</Subscript>
<Subscript name="3">
<Expression>{Crs_Desc}</Expression>
</Subscript>
<Subscript name="4">
<Expression>{Crs_RowID}</Expression>
</Subscript>
<Type>index</Type>
</SQLMap>
<SqlRowIdName>Crs_RowID</SqlRowIdName>
<StreamLocation>^User.DHCCourseSFCS</StreamLocation>
<Type>%CacheSQLStorage</Type>
}
}
建索引时要注意结构,假如未从0节点开始,则索引会出错,影响表数据。错误的索引使用k删除。
练习二:无索引查询的编写
$o:返回下一个rowid的值,最后一个后面的值为空。
^DHCSTUDENTSFCA(值)代表当前global节点的值
$p截取字段值
由于StSexDR指向了CT(“SEX”)的内容,根据下图^CT的结构
可以得出:
$get防止取到的值在指向表中不存在,若不存在值返回空值,防止报错undifined
s StSexDR=$Piece(DataStr,"^",3) ///指向了ctsex,需要取指向表的描述
s StSexDRDesc=$Piece($get(^CT("SEX",StSexDR)),"^",2) ///取得对应的性别描述 ///^CT("SEX",1)="1^男^^61089^^^"
$Zdate转换日期,(日期,变量值)
$data(d)
判断变量是否存在
0:Global不存在
1:存在且有值但是没有子节点
10:有子节点,但是没有值
11:有子节点且有值
$o:返回下一个rowid的值,最后一个后面的值为空。
w !:输出换行
/// function:无索引遍历数据,当入参为空输出所有学生。
/// createdate:2023年8月9日
/// creator:houyuxuan
/// input:StRowId Code
/// output:
/// table:DHC_StudentSFCA
/// other:d ##class(User.DHCStudentSFCA).GetDataList("1","")
ClassMethod GetDataList(StRowId As %String, Code As %String) As %String
{
if (StRowId'=""){
if ($Data(^DHCSTUDENTSFCA(StRowId))=0) { ///$Data=0该节点不存在
w "不存在此学生"
}
else{
s DataStr=$Get(^DHCSTUDENTSFCA(StRowId)) ///获取当前节点的数据串
s StCode=$Piece(DataStr,"^",1) ///获取字段第一个rowid,具体查看表结构data位置
s StName=$piece(DataStr,"^",2) ///姓名
s StSexDR=$Piece(DataStr,"^",3) ///指向了ctsex,需要取指向表的描述
s StSexDRDesc=""
s:StSexDR'="" StSexDRDesc=$Piece($get(^CT("SEX",StSexDR)),"^",2) ///取得对应的性别描述^CT("SEX",1)="1^男^^61089^^^"
s StDob=""
s StDob=$Piece(DataStr,"^",4)
s:StDob'="" StDob=$Zdate(StDob,3) ///日期转换
w "学号:"_StCode_"姓名:"_StName_"性别:"_StSexDR_"日期:"_StDob
}
}
else{
s RowId=0
for{
s RowId=$O(^DHCSTUDENTSFCA(RowId))
q:RowId=""
s DataStr=$Get(^DHCSTUDENTSFCA(RowId)) ///获取当前节点的数据串
s StCode=$Piece(DataStr,"^",1) ///获取字段第一个rowid,具体查看表结构data位置
s StName=$piece(DataStr,"^",2) ///姓名
s StSexDR=$Piece(DataStr,"^",3) ///指向了ctsex,需要取指向表的描述
s StSexDRDesc=""
s:StSexDR'="" StSexDRDesc=$Piece($get(^CT("SEX",StSexDR)),"^",2) ///取得对应的性别描述^CT("SEX",1)="1^男^^61089^^^"
s StDob=""
s StDob=$Piece(DataStr,"^",4)
s:StDob'="" StDob=$Zdate(StDob,3) ///日期转换
if (StCode=Code)||(Code=""){
w RowId_"学号:"_StCode_"姓名:"_StName_"性别:"_StSexDR_"日期:"_StDob_""
w !
}
}
}
}
}
练习三:有索引查询的编写
有索引直接通过$o找数据下标,返回索rowid
/// function:有索引遍历数据,当入参为空输出所有学生。
/// createdate:2023年8月10日
/// creator:houyuxuan
/// input: Code
/// output:
/// table:DHC_StudentSFCA
/// global: ^DHCSTUDENTSFCA
/// other:d ##class(User.DHCStudentSFCA).GetDataListByCode("789")
ClassMethod GetDataListByCode(Code As %String) As %String
{
quit:($Get(Code)="") "code为空" ///传入为空跳出
quit:($data(^DHCSTUDENTSFCA(0,"code",Code))=0) "数据不存在" ///无索引无数据退出
s RowId=0
s RowId=$o(^DHCSTUDENTSFCA(0,"code",Code,RowId)) ///使用$o从节点取得rowid
quit:RowId=""
s DataStr=$Get(^DHCSTUDENTSFCA(RowId)) ///获取获取到的RowId的数据串
s StCode=$Piece(DataStr,"^",1) ///获取字段第一个rowid,具体查看表结构data位置
s StName=$piece(DataStr,"^",2) ///姓名
s StSexDR=$Piece(DataStr,"^",3) ///指向了ctsex,需要取指向表的描述
s StSexDRDesc=""
s:StSexDR'="" StSexDRDesc=$Piece($get(^CT("SEX",StSexDR)),"^",2) ///取得对应的性别描述^CT("SEX",1)="1^男^^61089^^^"
s StDob=""
s StDob=$Piece(DataStr,"^",4)
s:StDob'="" StDob=$Zdate(StDob,3) ///日期转换
if (StCode=Code)||(Code=""){
w RowId_"学号:"_StCode_"姓名:"_StName_"性别:"_StSexDR_"日期:"_StDob_""
w !
}
}
}
练习四:获取rowid为3的就诊(PA_Adm)的病人(PA_PatMas)的性别描述(CT_Sex)
cache5 1.24.29
/// 题目:获取rowid为3的就诊(PA_Adm)的病人(PA_PatMas)的性别描述(CT_Sex)
/// global:^PAADM({PAADM_RowID})
/// table:PA_Adm
/// method:d ##CLASS(User.DHCStudentSFCA).GetPAADmSexDesc("3")
ClassMethod GetPAADmSexDesc(AdmRowId As %String) As %String
{
q:AdmRowId="" "RowId not be NUll"
q:$d(^PAADM(AdmRowId))=0
s PAADMPAPMIDR=$p($g(^PAADM(AdmRowId)),"^",1)
s:PAADMPAPMIDR'="" PAPMISexDR=$p($g(^PAPER(PAADMPAPMIDR,"ALL")),"^",7)
s:PAPMISexDR'="" CTSEXDesc=$p($g(^CT("SEX",PAPMISexDR)),"^",2)
w "rowid为3的"_AdmRowId_"就诊(PA_Adm)的病人(PA_PatMas)的性别描述为"_CTSEXDesc
}