Dynamically Calling Stored Procedures
This section describes how to dynamically call store procedures. You must use anonymous statement blocks to package stored procedures or statement blocks and append IN and OUT behind the EXECUTE IMMEDIATE...USING statement to input and output parameters.
Syntax
Figure 1 shows the syntax diagram.
Figure 2 shows the syntax diagram for using_clause.
The above syntax diagram is explained as follows:
- CALL procedure_name: calls the stored procedure.
- [:placeholder1,:placeholder2,...]: specifies the placeholder list of the stored procedure parameters. The numbers of the placeholders and parameters are the same.
- USING [IN|OUT|IN OUT]bind_argument: specifies where the variable passed to the stored procedure parameter value is stored. The modifiers in front of bind_argument and of the corresponding parameter are the same.
- Overloaded functions or stored procedures with placeholders cannot be called.
- When a stored procedure is called, => cannot be used to skip parameters.
-- Create the stored procedure proc_add. gaussdb=# CREATE OR REPLACE PROCEDURE proc_add ( param1 in INTEGER, param2 out INTEGER, param3 in INTEGER ) AS BEGIN param2:= param1 + param3; END; / CREATE PROCEDURE gaussdb=# DECLARE input1 INTEGER:=1; input2 INTEGER:=2; statement VARCHAR2(200); param2 INTEGER; BEGIN -- Declare the call statement. statement := 'call proc_add(:col_1, :col_2, :col_3)'; -- Execute the statement. EXECUTE IMMEDIATE statement USING IN input1, OUT param2, IN input2; dbe_output.print_line('result is: '||to_char(param2)); END; / result is: 3 ANONYMOUS BLOCK EXECUTE -- Delete the stored procedure. gaussdb=# DROP PROCEDURE proc_add; DROP PROCEDURE
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