26.5. Using IN, OUT, and IN OUT Parameter Modes

EnterpriseDB provides support for IN, OUT, and IN OUT parameter modes in both stored procedures and functions.

The following examples demonstrate the usage of the various parameter modes.

Please note that the following examples are written in C and require a valid C/C++ compiler.

The following examples demonstrate how a function and stored procedure with various parameter modes are called by a C program.

26.5.1. Example for Executing a Function

The sample SPL code for a function is shown below.

CREATE OR REPLACE FUNCTION hire_clerk(
   p_ename         VARCHAR2,
   p_deptno        NUMBER
) 
RETURN NUMBER
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_mgr           NUMBER(4);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_deptno        NUMBER(2);
BEGIN
    v_empno := new_empno;
    INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
        TRUNC(SYSDATE), 950.00, NULL, p_deptno);
    SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
        v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
        FROM emp WHERE empno = v_empno;
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Manager    : ' || v_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
    RETURN v_empno;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        RETURN -1;
END;

The corresponding C code for executing this function is given below:

BOOL ExecuteFunction()
{
	
	RETCODE rCode;
	HENV *hEnv = (HENV*)malloc(sizeof(HENV));
	HDBC *hDBC = (HDBC*)malloc(sizeof(HDBC));
	HSTMT *hStmt = (HSTMT*)malloc(sizeof(HSTMT));
	SQLSMALLINT iTotCols = 0;
	Connect("enterprisedb","enterprisedb","edb",&hEnv,&hDBC);
	rCode = SQLAllocStmt(*hDBC,hStmt); 
	rCode = SQLAllocHandle(SQL_HANDLE_STMT,*hDBC,hStmt);
	
	
	static SQLNUMERIC Num2=20;
	static SQLNUMERIC Num3;
	
	RETCODE rc=0;
	
	SQLCHAR strName1[30]  = "SMITHE";
	SQLCHAR strName2[30];
	
	
	static SQL_TIMESTAMP_STRUCT dt5; 
	
	SQLINTEGER Num1IndOrLen;
	SQLINTEGER Num2IndOrLen;
	
	
	rc = SQLPrepare((*hStmt),(SQLCHAR*)"{ ? = call hire_clerk(?,?)}",SQL_NTS);
	
	rc = SQLBindParameter((*hStmt),1, SQL_PARAM_OUTPUT, SQL_C_ULONG,SQL_NUMERIC,0, 0,
					&Num3, 0, &Num1IndOrLen);
	
	rc = SQLBindParameter((*hStmt),2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,30, 0,
 				strName1,30, &Num2IndOrLen);
	
	rc = SQLBindParameter((*hStmt),3, SQL_PARAM_INPUT, SQL_C_ULONG,SQL_NUMERIC,0, 0,
					&Num2, 0, &Num1IndOrLen);
						
 										
 	Num1IndOrLen=0;
	Num2IndOrLen=0;

        rc = SQLExecute((*hStmt));
	if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
	 printf("Success..");
	}
	
	rc=SQLFetch((*hStmt));
      	if(SQL_NO_DATA == rc)
      	{
		printf("No Data...\r\n");
		return 0;
       	}
	
	free(hEnv);
	free(hDBC);
	free(hStmt);
	return 0;
}

26.5.2. Explanation

First, the environment, database and statement handles are created and initialized. After that a database connection is established and the function is executed via a Callable Statement after which each parameter is bound separately to the command to be executed.

Finally, the statement is executed. The output is displayed on the screen below. Once the program finishes executing the function and displaying the results all memory consumed by various resources is deallocated.

26.5.3. Output After Executing the Function

26.5.4. Example for Executing a Stored Procedure

The sample SPL code for a stored procedure is shown below:

CREATE OR REPLACE PROCEDURE emp_query (
    p_deptno        IN     NUMBER,
    p_empno         IN OUT NUMBER,
    p_ename         IN OUT VARCHAR2,
    p_job           OUT    VARCHAR2,
    p_hiredate      OUT    DATE,
    p_sal           OUT    NUMBER
)
IS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
          OR  ename = UPPER(p_ename));
END;

The following is the C program for executing the above stored procedure:

      
BOOL ExecuteProcedure_1()
{
	
	RETCODE rCode;
	HENV *hEnv = (HENV*)malloc(sizeof(HENV));
	HDBC *hDBC = (HDBC*)malloc(sizeof(HDBC));
	HSTMT *hStmt = (HSTMT*)malloc(sizeof(HSTMT));
	SQLSMALLINT iTotCols = 0;
	Connect("enterprisedb","enterprisedb","edb",&hEnv,&hDBC);
	rCode = SQLAllocStmt(*hDBC,hStmt); 
	rCode = SQLAllocHandle(SQL_HANDLE_STMT,*hDBC,hStmt);
	
	static SQLREAL Num1=7369;
	static SQLNUMERIC Num2=20;
	static SQLREAL Num3;
	
	RETCODE rc=0;
	
	SQLCHAR strName1[30]  = "SMITH";
	SQLCHAR strName2[30];
	
	
	static SQL_TIMESTAMP_STRUCT dt5; 
	
	SQLINTEGER Num1IndOrLen;
	SQLINTEGER Num2IndOrLen;
	SQLINTEGER Num3IndOrLen;
	SQLINTEGER Num4IndOrLen;
	SQLINTEGER Num5IndOrLen;
	SQLINTEGER Num6IndOrLen;
	
	rc = SQLPrepare((*hStmt),(SQLCHAR*)"{call emp_query(?,?,?,?,?,?)}",SQL_NTS);
	
	rc = SQLBindParameter((*hStmt),1, SQL_PARAM_INPUT_OUTPUT, SQL_C_ULONG,SQL_NUMERIC,0, 0,
					&Num2, 0, &Num2IndOrLen);
	rc = SQLBindParameter((*hStmt),2, SQL_PARAM_INPUT, SQL_C_FLOAT,SQL_NUMERIC,0, 0,
					&Num1, 0, &Num1IndOrLen);					
 	rc = SQLBindParameter((*hStmt),3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,30, 0,
 				strName1,30, &Num3IndOrLen);
						
 	rc = SQLBindParameter((*hStmt),4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_CHAR,31, 0,
 				strName2,31, &Num4IndOrLen);
	
	rc = SQLBindParameter((*hStmt),5, SQL_PARAM_OUTPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP,0, 0,
					&dt5,0, &Num5IndOrLen);
	
	rc = SQLBindParameter((*hStmt),6, SQL_PARAM_OUTPUT, SQL_C_FLOAT,SQL_NUMERIC,0, 0,
					&Num3, 0, &Num6IndOrLen);
			
										
 	Num1IndOrLen=0;
	Num2IndOrLen=0;
	Num3IndOrLen=0;
	Num4IndOrLen=0;
	Num5IndOrLen=0;
	Num6IndOrLen=0;
	
        rc = SQLExecute((*hStmt));
	if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
	
		printf("Job = %s\r\n",strName2);
		
		printf("Hire Year = %d\r\n",dt5.year);
		printf("Hire Month = %d\r\n",dt5.month);
		printf("Hire Day= %d\r\n",dt5.day);
		
		printf("Sal = %.0f\r\n",Num3);
		
	}
	
	rc=SQLFetch((*hStmt));
      	if(SQL_NO_DATA == rc)
      	{
		printf("No Data...\r\n");
		return 0;
       	}
	
	free(hEnv);
	free(hDBC);
	free(hStmt);
	return 0;
}

26.5.5. Explanation

Here too, the environment, database and statement handles are first created and initialized. Once a database connection is established, the function is executed via a Callable Statement after which each parameter is bound separately to the command to be executed.

Once the statement has been executed, all memory consumed by various resources is deallocated.