DB2/AS400数据库SQL插入与存储过程编写指导请求

Hey there! Since you're already comfortable with general SQL INSERT/UPDATE statements, you’ll find DB2 for IBM i (formerly AS/400) pretty familiar—most core syntax aligns with standard SQL, with just a few IBM-specific nuances to note. Let’s walk through exactly what you need to know for INSERT statements and stored procedures.

The basic INSERT syntax is nearly identical to what you’re used to, but we’ll cover some IBM i-specific best practices and options.

Basic Single-Row Insert #

This works exactly like standard SQL. Just remember to specify the library (schema) if your table isn’t in your current default library (you can use either . or / to separate library and table name):

-- Basic INSERT with explicit column list
INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, EMAIL)
VALUES (101, 'John Doe', 'john.doe@example.com');

Insert All Columns #

If you’re inserting values for every column in the table (in the exact order the table was defined), you can omit the column list:

-- Insert all columns (matches table's column order)
INSERT INTO MYLIB.CUSTOMERS
VALUES (102, 'Jane Smith', 'jane.smith@example.com', '555-1234');

Use Default Values #

If your table has columns with predefined default values (like a JOIN_DATE set to CURRENT_DATE), you can use the DEFAULT keyword instead of hardcoding a value:

-- Leverage default column values
INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, JOIN_DATE)
VALUES (103, 'Bob Brown', DEFAULT); -- JOIN_DATE uses its defined default

Bulk Insert Multiple Rows #

DB2 for IBM i supports bulk inserts with multiple VALUES sets in one statement—super handy for adding several records at once:

-- Bulk insert multiple rows
INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, EMAIL)
VALUES 
  (104, 'Alice Lee', 'alice.lee@example.com'),
  (105, 'Mike Chen', 'mike.chen@example.com');

Quick Note on UPDATE #

Since you mentioned familiarity with UPDATE statements, just confirming: the syntax is almost identical to standard SQL. Example:

UPDATE MYLIB.CUSTOMERS
SET EMAIL = 'john.doe.updated@example.com'
WHERE CUST_ID = 101;

For stored procedures, you can write pure SQL procedures (perfect for someone familiar with standard SQL) or use IBM i-specific languages like ILE RPG/CL. We’ll focus on SQL stored procedures since that’s your comfort zone.

Create a Simple INSERT Stored Procedure #

This procedure takes input parameters and inserts a new customer into the table. Use CREATE OR REPLACE to easily update the procedure if needed:

-- Create a basic INSERT stored procedure
CREATE OR REPLACE PROCEDURE MYLIB.ADD_CUSTOMER(
  IN p_cust_id INT,
  IN p_name VARCHAR(50),
  IN p_email VARCHAR(100)
)
LANGUAGE SQL
BEGIN
  -- Insert the new customer
  INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, EMAIL)
  VALUES (p_cust_id, p_name, p_email);
  
  -- Optional: Return a success message
  SIGNAL SQLSTATE '00000' SET MESSAGE_TEXT = 'Customer added successfully';
END;

Call the Stored Procedure #

To execute the procedure, use the CALL statement. If you’re using a tool like IBM i Access Client Solutions (ACS) or a SQL CLI, this is straightforward:

-- Execute the stored procedure
CALL MYLIB.ADD_CUSTOMER(106, 'Sarah Kim', 'sarah.kim@example.com');

Stored Procedure with Output Parameters #

If you need to return data (like the number of rows inserted), add an OUT parameter and use GET DIAGNOSTICS to capture the row count:

-- Procedure with an output parameter for row count
CREATE OR REPLACE PROCEDURE MYLIB.ADD_CUSTOMER_WITH_STATUS(
  IN p_cust_id INT,
  IN p_name VARCHAR(50),
  IN p_email VARCHAR(100),
  OUT p_rows_inserted INT
)
LANGUAGE SQL
BEGIN
  INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, EMAIL)
  VALUES (p_cust_id, p_name, p_email);
  
  -- Capture the number of rows affected
  GET DIAGNOSTICS p_rows_inserted = ROW_COUNT;
END;

Calling a Procedure with Output Parameters #

The exact syntax depends on your tool, but here’s an example for ACS or a SQL script:

-- Declare a variable to hold the output value
DECLARE @rows_inserted INT;

-- Call the procedure
CALL MYLIB.ADD_CUSTOMER_WITH_STATUS(107, 'Tom Wilson', 'tom.wilson@example.com', @rows_inserted);

-- View the result
SELECT @rows_inserted AS ROWS_ADDED;

Error Handling in Stored Procedures #

Add error handlers to catch common issues like duplicate keys. This makes your procedures more robust:

-- Procedure with error handling for unique constraint violations
CREATE OR REPLACE PROCEDURE MYLIB.ADD_CUSTOMER_SAFE(
  IN p_cust_id INT,
  IN p_name VARCHAR(50),
  IN p_email VARCHAR(100)
)
LANGUAGE SQL
BEGIN
  -- Handle unique constraint errors (SQLSTATE 23505 = duplicate key)
  DECLARE EXIT HANDLER FOR SQLSTATE '23505'
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Customer ID already exists';
  
  INSERT INTO MYLIB.CUSTOMERS (CUST_ID, NAME, EMAIL)
  VALUES (p_cust_id, p_name, p_email);
END;

内容的提问来源于stack exchange，提问作者Satish