第二十八章 SQL命令 DELETE（二）

文章目录

• 第二十八章 SQL命令 DELETE（二）
• 示例

第二十八章 SQL命令 DELETE（二） 示例

以下两个示例都删除了TempEmployees表中的所有行。请注意，FROM关键字是可选的：

DELETE FROM TempEmployees

DELETE TempEmployees

以下示例从Employees表中删除员工编号234：

DELETE
     FROM Employees
     WHERE EmpId = 234

下面的示例从ActiveEmployees表中删除CurStatus列设置为“RETIRED”的所有行：

DELETE FROM ActiveEmployees
     WHERE CurStatus = 'Retired'

下面的示例使用子查询删除行：

DELETE FROM (SELECT Name,Age FROM Sample.Person WHERE Age > 65)

嵌入式SQL和动态SQL示例

在下面的一组程序示例中，第一个程序创建了一个名为SQLUser.WordPair的表，该表有三列。下一个程序插入六条记录。后续程序使用基于指针的嵌入式SQL删除所有英语记录，并使用动态SQL删除所有法语记录。最后一个程序显示剩余的记录，然后删除该表。

ClassMethod Delete1()
{
	&sql(
		CREATE TABLE SQLUser.WordPairs 
		(
			Lang        CHAR(2) NOT NULL,
			Firstword   CHAR(30),
			Lastword    CHAR(30) 
		)
	)
	if SQLCODE = 0 {
		w !,"表已创建" 
	} elseif SQLCODE = -201 {
		w !,"表已存在"  
		q
	} else {
		w !,"CREATE TABLE失败. SQLCODE=",SQLCODE 
	}
}

ClassMethod Delete2()
{
	#SQLCompile Path = Cinema,Sample
	&sql(
		INSERT INTO WordPairs 
		(
			Lang, Firstword, Lastword
		) 
		VALUES 
		(
			'En', 'hello', 'goodbye'
		)
	)
	if SQLCODE = 0 { 
		w !,"插入的第一条记录" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Fr','bonjour','au revoir'))
	if SQLCODE = 0 { 
		w !,"插入的第二条记录" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('It','pronto','ciao'))
	if SQLCODE = 0 { 
		w !,"插入的第三条记录" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Fr','oui','non'))
	if SQLCODE = 0 { 
		w !,"插入的第四条记录" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('En','howdy','see ya'))
	if SQLCODE = 0 { 
		w !,"插入的第五条记录" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
	&sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
	('Es','hola','adios'))
	IF SQLCODE = 0 { 
		w !,"插入的第六条记录",!!
		s myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
		s tStatement = ##class(%SQL.Statement).%New()
		s qStatus = tStatement.%Prepare(myquery)
		if qStatus'=1 {
			w "%Prepare failed:" 
			d $System.Status.DisplayError(qStatus) 
			q
		}
		s rset = tStatement.%Execute()
		d rset.%Display()
		w !,"End of data" 
	} else { 
		w !,"Insert 失败, SQLCODE=",SQLCODE
		q
	}
}

ClassMethod Delete3()
{

	#SQLCompile Path=Sample
	n %ROWCOUNT,%ROWID
	&sql(
		DECLARE WPCursor CURSOR FOR 
		SELECT Lang FROM WordPairs
		WHERE Lang='En'
	)
	&sql(
		OPEN WPCursor
	)
	q:(SQLCODE'=0)
	for { 
		&sql(
			FETCH WPCursor
		)
		q:SQLCODE 
		&sql(
			DELETE FROM WordPairs
			WHERE CURRENT OF WPCursor
		)
		if SQLCODE=0 {
			w !,"Delete 成功"
			w !,"Row count=",%ROWCOUNT," RowID=",%ROWID 
		} else {
			w !,"Delete 失败, SQLCODE=",SQLCODE 
		}
	}
	&sql(
		CLOSE WPCursor
	)
}

ClassMethod Delete4()
{
	s sqltext = "DELETE FROM WordPairs WHERE Lang=?"
	s tStatement = ##class(%SQL.Statement).%New(0,"Sample")
	s qStatus = tStatement.%Prepare(sqltext)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rtn = tStatement.%Execute("Fr")
	if rtn.%SQLCODE=0 {
		w !,"Delete succeeded"
		w !,"Row count=",rtn.%ROWCOUNT," RowID of last record=",rtn.%ROWID 
	} else {
		w !,"Delete failed, SQLCODE=",rtn.%SQLCODE }
}

ClassMethod Delete5()
{

	s myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
	s tStatement = ##class(%SQL.Statement).%New()
	s qStatus = tStatement.%Prepare(myquery)
	if qStatus'=1 {
		w "%Prepare 失败：" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
	&sql(
		DROP TABLE SQLUser.WordPairs
	)
	if SQLCODE=0 {
		w !!,"Table 删除"
		q 
	} else {
		w !,"Table 删除是啊白, SQLCODE=",SQLCODE 
	}
}