例: SQL プロシージャー内における行データ・タイプの使用

SQL プロシージャーで行データ・タイプを使用すると、レコード・データを取り出して、それをパラメーターとして渡すことができます。

このトピックでは、複数の SQL プロシージャーの定義を含んだ CLP スクリプトの例を示します。これは、多様な行の使用方法の一部です。

ADD_EMP という名前のプロシージャーは、1 つの行データ・タイプを入力パラメーターとして使用して、それを表に挿入します。

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.