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
-- 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot