示例：SQL 过程中的行数据类型用法

可在 SQL 过程中使用行数据类型来检索记录数据并将其作为参数传递。

本主题包含 CLP 脚本的示例，其中包含多个 SQL 过程的定义，这些定义演示了行的多种用法的其中一些。

过程 ADD_EMP 将行数据类型用作输入参数，然后将其插入到表中。

过程 NEW_HIRE 使用 SET 语句将行指定给行变量，然后将行数据类型值作为调用另一过程的 CALL 语句中的参数传递。

过程 FIRE_EMP 会将一行表数据选择到行变量中，然后将行字段值插入到表中。

以下是 CLP 脚本，后跟以详细方式从 CLP 运行脚本的输出：

--#SET TERMINATOR @;
CREATE TABLE employee (id INT, 
                       name VARCHAR(10), 
                       salary DECIMAL(9,2))@

INSERT INTO employee VALUES (1, 'Mike', 35000), 
                            (2, 'Susan', 35000)@

CREATE TABLE former_employee (id INT, name VARCHAR(10))@

CREATE TYPE empRow AS ROW ANCHOR ROW OF employee@

CREATE PROCEDURE ADD_EMP (IN newEmp empRow)
BEGIN
  INSERT INTO employee VALUES newEmp;
END@

CREATE PROCEDURE NEW_HIRE (IN newName VARCHAR(10))
BEGIN
  DECLARE newEmp empRow;
  DECLARE maxID INT;

  -- Find the current maximum ID;
  SELECT MAX(id) INTO maxID FROM employee;

  SET (newEmp.id, newEmp.name, newEmp.salary) 
    = (maxID + 1, newName, 30000);

  -- Call a procedure to insert the new employee
  CALL ADD_EMP (newEmp);
END@

CREATE PROCEDURE FIRE_EMP (IN empID INT)
BEGIN
  DECLARE emp empRow;

  -- SELECT INTO a row variable
  SELECT * INTO emp FROM employee WHERE id = empID;

  DELETE FROM employee WHERE id = empID;
  
  INSERT INTO former_employee VALUES (emp.id, emp.name);
END@

CALL NEW_HIRE('Adam')@

CALL FIRE_EMP(1)@

SELECT * FROM employee@

SELECT * FROM former_employee@

以下是以详细方式从 CLP 运行脚本的输出：

CREATE TABLE employee (id INT, name VARCHAR(10), salary DECIMAL(9,2))
DB20000I  The SQL command completed successfully.

INSERT INTO employee VALUES (1, 'Mike', 35000), (2, 'Susan', 35000)
DB20000I  The SQL command completed successfully.

CREATE TABLE former_employee (id INT, name VARCHAR(10))
DB20000I  The SQL command completed successfully.

CREATE TYPE empRow AS ROW ANCHOR ROW OF employee
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE ADD_EMP (IN newEmp empRow)
BEGIN
  INSERT INTO employee VALUES newEmp;
END
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE NEW_HIRE (IN newName VARCHAR(10))
BEGIN
  DECLARE newEmp empRow;
  DECLARE maxID INT;

  -- Find the current maximum ID;
  SELECT MAX(id) INTO maxID FROM employee;

  SET (newEmp.id, newEmp.name, newEmp.salary) = (maxID + 1, newName, 30000);

  -- Call a procedure to insert the new employee
  CALL ADD_EMP (newEmp);
END
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE FIRE_EMPLOYEE (IN empID INT)
BEGIN
  DECLARE emp empRow;

  -- SELECT INTO a row variable
  SELECT * INTO emp FROM employee WHERE id = empID;

  DELETE FROM employee WHERE id = empID;
  
  INSERT INTO former_employee VALUES (emp.id, emp.name);
END
DB20000I  The SQL command completed successfully.

CALL NEW_HIRE('Adam')

  Return Status = 0

CALL FIRE_EMPLOYEE(1)

  Return Status = 0

SELECT * FROM employee

ID          NAME       SALARY     
----------- ---------- -----------
          2 Susan         35000.00
          3 Adam          30000.00

  2 record(s) selected.


SELECT * FROM former_employee

ID          NAME      
----------- ----------
          1 Mike      

  1 record(s) selected.