Skip to content
≡

Administrator

Developer

Tools

External Tools

11. Using Stored Procedures in C/C++#

Using Stored Procedures#

Embedded SQL statements can be used to create and execute stored procedures and stored functions within applications.

CREATE#

This statement is used to create a stored procedure or stored function.

Syntax#

Stored procedure#
EXEC SQL CREATE [ OR REPLACE ] PROCEDURE
<procedure_name> ( [ <parameter_declaration_list> ] ) ] 
AS | IS 
[ <declaration_section> ] 
BEGIN <statement> 
[ EXCEPTION <exception_handler> ] 
END 
[ <procedure_name> ] ; 
END-EXEC;
Stored function#
EXEC SQL CREATE [ OR REPLACE ] FUNCTION 
<function_name> [ ( [ <parameter_declaration_list> ] ) ]
RETURN <data_type> 
AS | IS 
[ <declaration_section> ] 
BEGIN <statement> 
[ EXCEPTION <exception_handler> ] 
END 
[ <function_name> ] ; 
END-EXEC;

Arguments#

  • <procedure_name>: This is the name of the stored procedure.
  • <function_name>: This is the name of the stored function.
  • <parameter_declaration_list>: Please refer to the Stored Procedures Manual.
  • <declaration_section>: Please refer to the Stored Procedures Manual.
  • <statement>: Please refer to the Stored Procedures Manual.
  • <exception_handler>: Please refer to the Stored Procedures Manual.
  • <data_type>: Please refer to the Stored Procedures Manual.

Examples#

Various examples in which stored procedures and stored functions are created are shown below.

[Example 1] The following example illustrates the creation of a stored procedure.

In this example, the stored procedure ORDER_PROC is created. This procedure searches for one record in which the value in the ONO column is the same as the value of the s_ono parameter of the stored procedure. If no records are found, then a new record having default values is inserted into the ORDERS table. If one record is found, then the values in the PROCESSING column in the record that is found are written to 'P'.

< Sample Program: psm1.sc >

EXEC SQL CREATE OR REPLACE PROCEDURE ORDER_PROC
(s_ono in bigint)
        AS
            p_order_date date;
            p_eno        integer;
            p_cno        bigint;
            p_gno        char(10);
            p_qty        integer;
        BEGIN
            SELECT ORDER_DATE, ENO, CNO, GNO, QTY 
            INTO p_order_date, p_eno, p_cno, p_gno, p_qty
            FROM ORDERS 
            WHERE ONO = s_ono;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                p_order_date := SYSDATE;
                p_eno        := 13;
                p_cno        := BIGINT'7610011000001';
                p_gno        := 'E111100013';
                p_qty        := 4580;
                INSERT INTO ORDERS 
                        (ONO, ORDER_DATE, ENO, CNO, GNO, QTY) 
                VALUES 
                        (s_ono, p_order_date, p_eno, p_cno, p_gno, p_qty);
            WHEN OTHERS THEN
                UPDATE ORDERS 
                SET PROCESSING = 'P'
                WHERE ONO = s_ono;
        END;
    END-EXEC;

[Example 2] The following example illustrates the creation of a stored function.

This stored function inserts a new record into the ORDERS table, and then counts and returns the number of records in the ORDERS table.

< Sample Program: psm2.sc >

EXEC SQL CREATE OR REPLACE FUNCTION ORDER_FUNC(
            s_ono in bigint, s_order_date in date, 
            s_eno in integer, s_cno in char(13), 
            s_gno in char(10), s_qty in integer)
        RETURN INTEGER
        AS
            p_cnt integer;
        BEGIN
            INSERT INTO ORDERS 
(ONO, ORDER_DATE, ENO, CNO, GNO, QTY) 
VALUES 
(s_ono, s_order_date, s_eno, s_cno, s_gno, s_qty);
            SELECT COUNT(*) INTO p_cnt FROM ORDERS;
            RETURN p_cnt;
        END;
    END-EXEC;

ALTER#

This statement is used to precompile a stored procedure or stored function.

Syntax#

Stored procedure#
EXEC SQL ALTER PROCEDURE <procedure_name> COMPILE;
Stored functionment#
EXEC SQL ALTER FUNCTION <function_name> COMPILE;

Arguments#

  • <procedure_name>: This is the name of the stored procedure.

  • <function_name>: This is the name of the stored function.

Description#

This statement is used to recompile a currently invalid stored procedure or stored function to restore it to a valid state.

Example#

Two examples in which stored procedures and stored functions are recompiled are shown below.

[Example 1] In the following example, the stored procedure ORDER_PROD is recompiled.

EXEC SQL ALTER PROCEDURE ORDER_PROC COMPILE;

[Example 2] In the following example, the stored function ORDER_FUNC is recompiled.

EXEC SQL ALTER FUNCTION ORDER_FUNC COMPILE;

DROP#

This statement is used to delete the stored procedure or stored function.

Syntax#

Stored procedure#
EXEC SQL DROP PROCEDURE <procedure_name>;
Stored procedure#
EXEC SQL DROP FUNCTION <function_name>;

Arguments#

  • <procedure_name>: This is the name of the stored procedure.

  • <function_name>: This is the name of the stored function.

Example#

Two examples in which stored procedures and stored functions are dropped are shown below.

[Example 1] In the following example, the stored procedure ORDER_PROC is dropped.

< Sample Program : psm1.sc >

EXEC SQL DROP PROCEDURE ORDER_PROC;

[Example 2] In the following example, the stored function ORDER_FUNC is dropped.

< Sample Program : psm2.sc >

EXEC SQL DROP FUNCTION ORDER_FUNC;

EXECUTE#

This statement is used to execute a stored procedure or stored function.

Syntax#

Stored procedure#
EXEC SQL EXECUTE BEGIN 
<procedure_name>
[ ( [ <:host_var> [ IN | OUT | IN OUT ] [ , … ] ] ) ];
END;
END-EXEC;
Stored function#
EXEC SQL EXECUTE BEGIN 
<:host_var> := <function_name> 
[ ( [ <:host_var> [ IN | OUT | IN OUT ] [ , … ] ] ) ];
END;
END-EXEC;

Argument#

  • <procedure_name> : This is the name of the stored procedure.

  • <function_name> : This is the name of the stored function.

  • <:host_var>
    This is used to specify any IN, OUT or IN/OUT parameters (i.e. input or output host variables) that are necessary for the execution of the stored procedure or function, as well as any output host variables for storing the result of the stored function.

Examples#

Examples of the execution of stored procedures and stored functions are shown below.

[Example 1] In the following example, the stored procedure ORDER_PROC is executed.

< Sample Program : psm1.sc >

EXEC SQL BEGIN DECLARE SECTION;
long long s_ono;
EXEC SQL END DECLARE SECTION;

s_ono = 111111;

EXEC SQL EXECUTE 
BEGIN
    ORDER_PROC(:s_ono in);
END;
END-EXEC;

[Example 2] In the following example, the stored function ORDER_FUNC is executed.

< Sample Program : psm2.sc >

EXEC SQL BEGIN DECLARE SECTION;
long long s_ono;
char      s_order_date[19+1];
int        s_eno;
char      s_cno[13+1];
char      s_gno[10+1];
int        s_qty;
int        s_cnt;
EXEC SQL END DECLARE SECTION;

s_ono = 200000001;
s_eno = 20;
s_qty = 2300;
strcpy(s_order_date, "19-May-03");
strcpy(s_cno, "7111111431202");
strcpy(s_gno, "C111100001");

EXEC SQL EXECUTE 
BEGIN
    :s_cnt := ORDER_FUNC(:s_ono in, :s_order_date in, 
                             :s_eno in, :s_cno in, :s_gno in, :s_qty in);
END;
END-EXEC;

ANONYMOUS BLOCK#

This is a SQL statement that can be executed immediately without creating a stored procedure.

EXEC SQL EXECUTE 
<<LABEL_NAME_OPTION>>
[DECLARE]
[ <declaration_section> ] 
BEGIN 
<statement> 
[ EXCEPTION <exception_handler> ] 
END 
[ <label_name> ] ; 
END-EXEC;

Example#

This is an exmaple of using an anonymous block.

< Sample Program : anon.sc >

/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char usr[64];
char pwd[64];
char conn_opt[1024];

long long s_ono;
EXEC SQL END DECLARE SECTION;

s_ono = 999999;
/* execute anonymous block */
EXEC SQL EXECUTE
    <<LABEL1>>
    DECLARE
        p_order_date date;
        p_eno        integer;
        p_cno        bigint;
        p_gno        char(10);
        p_qty        integer;
    BEGIN
        SELECT ORDER_DATE, ENO, CNO, GNO, QTY
        INTO p_order_date, p_eno, p_cno, p_gno, p_qty
        FROM ORDERS
        WHERE ONO = :s_ono;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_order_date := SYSDATE;
            p_eno        := 13;
            p_cno        := BIGINT'7610011000001';
            p_gno        := 'E111100013';
            p_qty        := 4580;
            INSERT INTO ORDERS (ONO, ORDER_DATE, ENO, CNO, GNO, QTY)
                   VALUES (:s_ono, p_order_date, p_eno, p_cno, p_gno, p_qty);
        WHEN OTHERS THEN
            UPDATE ORDERS
            SET ORDER_DATE = p_order_date,
                ENO        = p_eno,
                CNO        = p_cno,
                GNO        = p_gno,
                QTY        = p_qty
            WHERE ONO = :s_ono;
    END;
END-EXEC;

Using Array-Type Host Variables with the EXECUTE Statement#

Array-type host variables can be used with the EXECUTE statement. When array-type host variables are used with the EXECUTE statement, executing the EXECUTE statement a single time has the same effect as executing the EXECUTE statement a number of time equal to the size of the array, thereby realizing a performance improvement.

Array Types#

When used with the EXECUTE statement, array-type host variables can only be used as IN parameters.

The following shows the array types that can be used in the EXECUTE statement:

  • Simple arrays
  • Structures containing arrays as elements thereof

Limitations#

The use of array-type host variables with the EXECUTE statement is limited in the following ways. Please keep them in mind when writing applications.

  • Array-type host variables cannot be used as OUT or IN/OUT parameters.

    Example: 

    EXEC SQL BEGIN DECLARE SECTION;
int var1[10];
int var2[10];
int var3[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE BEGIN 
PROC1(:var1 in, :var2 out, :var3 in out);   (X)
END;
END-EXEC;

  • The value returned by a stored function cannot be assigned to an array-type host variable.

    Example: 

    EXEC SQL BEGIN DECLARE SECTION;
int var1[10];
int var2[10];
int var3[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE BEGIN 
:var1 = FUNC1(:var2 in, :var3 in);      (X)
END;
END-EXEC;

  • Array-type host variables cannot be used together with non-array type host variables as parameters for stored procedures or functions.

    Example: 

    EXEC SQL BEGIN DECLARE SECTION;
int var1;
int var2;
int var3[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE BEGIN 
    PROC1(:var1 in, :var2 in, :var3 in);  (X)
END;
END-EXEC;

  • Due to the last two limitations above, array-type host variables cannot be used in stored function execution statements

Example#

The following example shows the use of array-type host variables as IN parameters in a stored procedure execution statement.

< Sample Program : arrays2.sc >

EXEC SQL BEGIN DECLARE SECTION;
char a_gno[3][10+1];
char a_gname[3][20+1];
EXEC SQL END DECLARE SECTION;

strcpy(a_gno[0], "G111100001");
strcpy(a_gno[1], "G111100002");
strcpy(a_gno[2], "G111100003");
strcpy(a_gname[0], "AG-100");
strcpy(a_gname[1], "AG-200");
strcpy(a_gname[2], "AG-300");

EXEC SQL EXECUTE
BEGIN
    GOODS_PROC(:a_gno in, :a_gname in);
END;
END-EXEC;

Sample Programs#

psm1.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/psm1.sc

Result of Execution#
$ is -f schema/schema.sql
$ make psm1
$ ./psm1
<SQL/PSM 1>
------------------------------------------------------
[Create Procedure]                                                
------------------------------------------------------
Success create procedure

------------------------------------------------------
[Execute Procedure]                                               
------------------------------------------------------
Success execute procedure

------------------------------------------------------
[Drop Procedure]                                                  
------------------------------------------------------
Success drop procedure

psm2.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/psm2.sc

Result of Execution#
$ is -f schema/schema.sql
$ make psm2
$ ./psm2
<SQL/PSM 2>
------------------------------------------------------
[Create Function]                                                 
------------------------------------------------------
Success create function

------------------------------------------------------
[Execute Function]                                                
------------------------------------------------------
31 rows selected

------------------------------------------------------
[Drop Function]                                                   
------------------------------------------------------
Success drop function