Dynamically Calling Stored Procedures

Syntax

Figure 1 shows the syntax diagram.

Figure 1 call_procedure::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 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 the variable whose value is passed to the stored procedure parameter. 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.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

--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;
/

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;