第二十八章 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 
    }
}