第八十五章 SQL命令 UPDATE（四）
示例
本节中的示例更新SQLUser.MyStudents表。
下面的示例创建SQLUser.MyStudents表，并用数据填充它。
因为这个示例的重复执行会积累具有重复数据的记录，所以它使用TRUNCATE TABLE在调用之前删除旧数据。
在调用示例之前执行这个示例:
ClassMethod Update1()
{
CreateStudentTable
s stuDDL = 5
s stuDDL(1) = "CREATE TABLE SQLUser.MyStudents ("
s stuDDL(2) = "StudentName VARCHAR(32),StudentDOB DATE,"
s stuDDL(3) = "StudentAge INTEGER COMPUTECODE {SET {StudentAge}="
s stuDDL(4) = "$PIECE(($PIECE($H,"","",1)-{StudentDOB})/365,""."",1)} CALCULATED,"
s stuDDL(5) = "Q1Grade CHAR,Q2Grade CHAR,Q3Grade CHAR,FinalGrade VARCHAR(2))"
s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
s qStatus = tStatement.%Prepare(.stuDDL)
if qStatus'=1 {
w "DDL %Prepare failed:"
d $System.Status.DisplayError(qStatus)
q
}
s rtn = tStatement.%Execute()
if rtn.%SQLCODE = 0 {
w !,"表创建成功"
} elseif rtn.%SQLCODE = -201 {
w "表已经存在, SQLCODE=",rtn.%SQLCODE,!
} else {
w !,"表创建失败, SQLCODE=",rtn.%SQLCODE,!
w rtn.%Message,!
}
RemoveOldData
s clearit = "TRUNCATE TABLE SQLUser.MyStudents"
s qStatus = tStatement.%Prepare(clearit)
if qStatus'=1 {
w "Truncate %Prepare failed:"
d $System.Status.DisplayError(qStatus)
q
}
s truncrtn = tStatement.%Execute()
if truncrtn.%SQLCODE = 0 {
w !,"表旧数据已经被删除",!
} elseif truncrtn.%SQLCODE = 100 {
w !,"没有数据可以删除",!
} else {
w !,"truncate failed, SQLCODE=",truncrtn.%SQLCODE," ",truncrtn.%Message,!
}
PopulateStudentTable
s studentpop=2
s studentpop(1)="INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "
s studentpop(2)="SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
s qStatus = tStatement.%Prepare(.studentpop)
if qStatus '= 1 {
w "填充 %Prepare 失败:"
d $System.Status.DisplayError(qStatus)
q
}
s poprtn = tStatement.%Execute()
if poprtn.%SQLCODE = 0 {
w !,"表填充成功",!
w poprtn.%ROWCOUNT," rows inserted"
} else {
w !,"表填充失败, SQLCODE=",poprtn.%SQLCODE,!
w poprtn.%Message
}
}
.png)