Oracle pro*c/c++ 动态sql(方法二示例)

方法二

也只能是非查询语句， 允许非查询动态 SQL 语句内包含输入宿主变量占位符, 而且只能带固定数量的宿主变量。使用内嵌PREPARE命令准备SQL语言：

• 1 PREPARE语法
  EXEC SQL PREPARE statement_name FROM {:host_string | string_literal};
  PREPARE是一个预编译器标识符,而不是宿主变量
• 2 USING子句
  EXEC SQL EXECUTE statement_name [USING :host_variable1[:indicator1] [, :host_variable2[:indicator2] … ]
• demo2

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"
#include "oraca.h"

typedef char mydname[20];
typedef char myloc[20];
//库函数
extern sqlgls(char * , size_t *, size_t * );
extern sqlglmt(void *,char *,size_t *,size_t *);
//定义宿主变量
EXEC SQL BEGIN DECLARE SECTION;
	EXEC SQL TYPE mydname is string(20);
	EXEC SQL TYPE myloc is string(20);
	char *serverid = "c##scott/root"; 
	
	int				deptno;
	dnameType		dname; 
	locType 		loc;
		
	
	char 			mySql[1024];
	char 			*pSql;
EXEC SQL END DECLARE SECTION;
int connet();
void doerr();
int main()
{
	int 	ret = 0;	
	char    inputchar;
	memset(mySql, 0, sizeof(mySql));
	pSql = NULL;
	
	EXEC SQL WHENEVER  sqlerror do doerr();
	connet();
	
	pSql = mySql;
	// 处理sql语言
	for(;;)
	{
		printf("\n请输入要更新部门编号 ");
		scanf("%d", &deptno);
		
		printf("\n请输入要新loc值 ");
		scanf("%s", loc);
		
	
		//准备动态sql (2个占位符)
		EXEC SQL PREPARE my_pre_sql FROM 'update dept set loc = :aa where deptno = :bb';
		//执行动态sql
		EXEC SQL EXECUTE my_pre_sql USING :loc, :deptno;  //
	
		EXEC SQL COMMIT;
		
		printf("\n any key to continue? ");
		getchar();
		printf("\n exit:n, other continue? ");
		scanf("%c", &inputchar);
		fflush(stdin);
		
		if (inputchar=='n' || inputchar=='N')
		{
			break;
		}
	}

	EXEC SQL COMMIT WORK RELEASE;
	return ret ;
}

int connet()
{
	int ret = 0;
	//连接数据库
	EXEC SQL connect :serverid;
	if (sqlca.sqlcode != 0)
	{
		ret = sqlca.sqlcode;
		return  ret;
	}
	else
	{
		printf("connect ok...\n");
	}
	return  ret;
}
void doerr()
{
	char	szerrbuf[120];
	size_t	outputlen, inputlen = 120;
	unsigned int ret = 0;

	//出错时,可以把错误SQL语言给打印出来
	EXEC SQL WHENEVER SQLERROR CONTINUE;

	ret = sqlgls(szerrbuf, &inputlen, &outputlen);
	printf("SQL:%.*s\n", inputlen, szerrbuf);
	printf("提示:%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
	EXEC SQL ROLLBACK WORK RELEASE;
	exit(1);
}