Naming Convention
Improper stored procedure and variable naming may adversely affect system usage.
- The name of a stored procedure, variable, or type can contain a maximum of 63 characters. If this limit is exceeded, the name is automatically truncated to 63 characters.
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA -- When a stored procedure name containing 66 characters is created, a message is displayed, indicating that the name is truncated to 63 characters. gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011() AS BEGIN NULL; END; / NOTICE: identifier "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011" will be truncated to "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891" CREATE PROCEDURE -- When a variable name containing 66 characters is created, a message is displayed, indicating that the name is truncated to 63 characters. gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc1(abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011 int) as BEGIN NULL; END; / NOTICE: identifier "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011" will be truncated to "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891" CREATE PROCEDURE gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to function best_practices_for_procedure.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891() drop cascades to function best_practices_for_procedure.proc1(integer) DROP SCHEMA
- When creating a stored procedure, avoid using variables or types with the same name in different variable scopes. For details, see "Stored Procedures > Basic Statements > Variable Definition Statements > Scope of a Variable" in Developer Guide. Using variables and types with the same name in different variable scopes may reduce the readability of stored procedures and increase the maintenance difficulty.
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA -- Create a stored procedure, create the same variable name in different variable scopes, and assign values. gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc1() AS name varchar2(10) := 'outer'; age int := 2025; BEGIN DECLARE name varchar2(10) := 'inner'; -- This is only an example and is not recommended. age int := 2024; -- This is only an example and is not recommended. BEGIN dbe_output.print_line('inner name =' || name); dbe_output.print_line('inner age =' || age); END; dbe_output.print_line('outer name =' || name); dbe_output.print_line('outer age =' || age); END; / CREATE PROCEDURE -- Execute the stored procedure. The same variable name in different scopes actually refers to different variables. gaussdb=# CALL best_practices_for_procedure.proc1(); inner name =inner inner age =2024 outer name =outer outer age =2025 proc1 ------- (1 row) gaussdb=# DROP SCHEMA best_practices_for_procedure cascade; NOTICE: drop cascades to function best_practices_for_procedure.proc1() DROP SCHEMA
-
Do not use SQL keywords in stored procedure, internal variable, and data type names to ensure that the stored procedure can run properly in all scenarios.
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure."as"() AS -- This is only an example and is not recommended. BEGIN NULL; END; / CREATE PROCEDURE -- A direct call will result in an error. gaussdb=# CALL as(); ERROR: syntax error at or near "as" LINE 1: call as(); ^ gaussdb=# CALL best_practices_for_procedure."as"(); as ---- (1 row) gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE; NOTICE: drop cascades to function best_practices_for_procedure."as"() DROP SCHEMA
-
When creating a stored procedure, avoid using the same name as system functions to prevent confusion. If the same name must be used, specify the schema during a call.
gaussdb=# CREATE SCHEMA best_practices_for_procedure; CREATE SCHEMA -- Create an abs function with the same name as the abs system function in the schema. This is only an example and is not recommended. gaussdb=# CREATE OR REPLACE FUNCTION best_practices_for_procedure.abs(a int) RETURN int AS BEGIN dbe_output.print_line('my abs funciton.'); RETURN abs(a); END; / CREATE FUNCTION -- Call a stored procedure. If no schema is added, the abs system function is called. gaussdb=# CALL abs(-1); abs ----- 1 (1 row) -- You are advised to add a schema. gaussdb=# CALL best_practices_for_procedure.abs(-1); my abs funciton. abs ----- 1 (1 row) gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE; NOTICE: drop cascades to function best_practices_for_procedure.abs(integer) DROP SCHEMA
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