8.CacheScript 与SQL

一、嵌入式SQL

1.新增与更新

/// Creator：      	邓体进
/// CreatDate：    	2022-03-01
/// Description： 	插入或更新数据
/// Table：        	DHCCLDemo.Student
/// Input:			id:ID,name:姓名,age:年龄
/// Return：       	ResultSet
/// w ##class(DHCCLDemo.BLL.Sample).SaveBySQL("", "王二", 23)
ClassMethod SaveBySQL(id, name, age)
{
	/// 错误示例:w ##class(DHCCLDemo.BLL.Sample).SaveBySQL("", "王二", "王二")
	if (##class(DHCCLDemo.Student).%ExistsId(id))
	{
		&sql(update DHCCLDemo.Student(Name, Age, Sex, epartment, Address, Birthplace)
		VALUES (:name, :age, NULL, NULL, NULL, NULL) 
		where RowId=:id)
	}
	else
	{
		&sql(INSERT INTO DHCCLDemo.Student (Name, Age, Sex, epartment, Address, Birthplace)
		VALUES (:name, :age, NULL, NULL, NULL, NULL))
	}
	
	if (SQLCODE=0)
	{
		return "S^"_%ROWID
	}
	else
	{
		return "E^"_%msg
	}
}

2.查询

/// Creator：      	邓体进
/// CreatDate：    	2022-03-01
/// Description： 	使用嵌入式SQL查询数据
///                 优点: 速度较快 ，缺点：同一文件游标不能重复
/// Table：        	DHCCLDemo.Student
/// Input:			opsId:手术ID,moduleCode:模块代码
/// Return：       	ResultSet
/// d ##class(%ResultSet).RunQuery("DHCCLDemo.BLL.Sample","FindByEmbeddedSQL","王二")
Query FindByEmbeddedSQL(name As %String) As websys.Query(ROWSPEC = "RowId,Name") [ SqlProc ]
{
}

ClassMethod FindByEmbeddedSQLExecute(ByRef qHandle As %Binary, name As %String) As %Status
{
	Set repid=$I(^CacheTemp)
	If $g(ind)="" Set ind=1
	Set rowId=""
	
    &sql(Declare studentCursor cursor for
        select RowId, Name
        into :id,:name From DHCCLDemo.Student
        where Name=:name)
   
    &sql(Open studentCursor)
    for { 
        &sql(Fetch studentCursor)
        quit:SQLCODE 
        do Output(id,name)
    }
   
    &sql(close studentCursor)
	Set qHandle=$lb(0,repid,0)
	quit $$$OK
Output(id,name)
	set Data=$lb(id,name)
 	Set ^CacheTemp(repid,ind)=Data
 	Set ind=ind+1
	quit
}

3.常用函数

SQL语句常需要对数据格式进行调调，如日期、时间等以ODBC输出:

%ODBCOUT

官方文档:

http://127.0.0.1:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_FUNCTIONS

以下列出常用的函数:

函数	说明
$FIND	查找
$LIST
$LISTBUILD
$PIECE
%ALPHAUP	转化为大写
%EXACT	截取
%MINUS	减法
%OBJECT	返回对象
%ODBCIN	以ODBC方式输入
%ODBCOUT	以ODBC方式输出
%SQLUPPER	转化为大写
ABS	取绝对值
ASCII	字符转为int
CAST
YEAR	获取年
WEEK	获取周
FLOOR	取整
JSON_ARRAY	生成为JSON数组
JSON_OBJECT	生成JSON对象
REPLACE	替换

二、动态SQL

1.新增与更新

不建议使用动态SQL

2.查询

/// Creator：      	邓体进
/// CreatDate：    	2022-03-01
/// Description： 	使用动态SQL查询数据
///                 优点: 灵活 ，缺点：速度较慢，常常拼接容易出错
/// Table：        	DHCCLDemo.Student
/// Input:			opsId:手术ID,moduleCode:模块代码
/// Return：       	ResultSet
/// d ##class(%ResultSet).RunQuery("DHCCLDemo.BLL.Sample","FindByDynamicalSQL","王二")
Query FindByDynamicalSQL(name As %String) As websys.Query(ROWSPEC = "RowId,Name") [ SqlProc ]
{
}

ClassMethod FindByDynamicalSQLExecute(ByRef qHandle As %Binary, name As %String) As %Status
{
	Set repid=$I(^CacheTemp)
	If $g(ind)="" Set ind=1
	set rowId=""
	Set myquery = "SELECT RowId, Name FROM DHCCLDemo.Student where Name='"_name_"'"
    Set tStatement = ##class(%SQL.Statement).%New()
    SET qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 
    { 
       Set qHandle=$lb(0,repid,0)
       quit 
    }
    SET rset = tStatement.%Execute()
    while rset.%Next() {
	    set id=rset.%Get("RowId")
	    set name=rset.%Get("Name")
    	do Output(id,name)
    }
	Set qHandle=$lb(0,repid,0)
	quit $$$OK
Output(id,name)
	set Data=$lb(id,name)
 	Set ^CacheTemp(repid,ind)=Data
 	Set ind=ind+1
	quit
}

3.JDBC

4.ODBC