Updated on 2025-09-22 GMT+08:00

Stored Procedures

Overview

A stored procedure is a program that is prepared and stored in a database. It consists of a group of SQL statements and is used to perform a series of database operations. A stored procedure can be called by multiple applications, improving code reusability and maintainability.

The stored procedure has the following features:

  • Simplified complex data operations: enables execution of multi-step service logic, such as batch processing and data conversion.
  • Optimized performance: improves the execution efficiency of database operations through precompilation and reduced network communication.
  • Enhanced security: encapsulates service logic in stored procedures to restrict direct access to database tables, hardening data security.
  • Simplified development and maintenance: integrates service logic in the database, simplifying application development and maintenance.

Examples

In the following example, the stored procedure prc_test is used to insert data from the tb_test1 table to the tb_test2 table. If the insertion fails, the stored procedure inserts the calling time, error information, and data into the tb_test_err table.
-- Environment preparation: Create tables and insert data into the tables.
gaussdb=# CREATE TABLE tb_test1(id int,info text);
CREATE TABLE
gaussdb=# CREATE TABLE tb_test2(id int PRIMARY KEY, info text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tb_test2_pkey" for table "tb_test2"
CREATE TABLE
gaussdb=# INSERT INTO tb_test1 VALUES (generate_series(1,5),'AAA');
INSERT 0 5
gaussdb=# INSERT INTO tb_test2 VALUES (generate_series(1,2),'aaa');
INSERT 0 2
gaussdb=# CREATE TABLE tb_test_err(
    err_time text,
    err_info text,
    id int,
    info text
);
CREATE TABLE

-- Create the stored procedure prc_test.
gaussdb=# CREATE OR REPLACE PROCEDURE prc_test(v_time IN text) AS 
    -- Define a cursor.
    CURSOR v_cur IS SELECT * FROM tb_test1;
    v_id int;
    v_info text;
BEGIN
    -- Open the cursor.
    OPEN v_cur;
    LOOP
        -- Use the cursor.
        FETCH v_cur INTO v_id,v_info; 
        -- Loop exit condition: The cursor cannot obtain data.
        EXIT WHEN v_cur %notfound;
        BEGIN
            -- Insert data from the tb_test1 table to the tb_test2 table.
            INSERT INTO tb_test2 VALUES (v_id,v_info);        
        -- Troubleshooting: If the execution fails, insert the error information to the tb_test_err table.
        EXCEPTION 
            WHEN OTHERS THEN
                INSERT INTO tb_test_err VALUES (v_time,sqlerrm,v_id,v_info);
        END;
    END LOOP; 
    -- Close the cursor.
    CLOSE v_cur;      
END;
/
CREATE PROCEDURE

-- Call the stored procedure to pass the character string 2019-01-31.
gaussdb=# CALL prc_test('2019-01-30');

-- View the result.
gaussdb=# SELECT * FROM tb_test2;
 id | info 
----+------
  1 | aaa
  2 | aaa
  3 | AAA
  4 | AAA
  5 | AAA
(5 rows)

-- View the data that fails to be inserted.
gaussdb=# SELECT * FROM tb_test_err;
  err_time  |                            err_info                             | id | info 
------------+-----------------------------------------------------------------+----+------
 2019-01-30 | Duplicate key value violates unique constraint "tb_test2_pkey". |  1 | AAA
 2019-01-30 | Duplicate key value violates unique constraint "tb_test2_pkey". |  2 | AAA
(2 rows)

-- Drop the tables and the stored procedure.
gaussdb=# DROP PROCEDURE prc_test;
DROP PROCEDURE
gaussdb=# DROP TABLE tb_test1,tb_test2,tb_test_err;
DROP TABLE