-
金仓数据库 Pro*C 迁移指南（ 5. 程序开发示例）
5. 程序开发示例
-
连接数据库
-
执行一条不返回结果的 SQL 语句
-
执行一条查询语句并获取结果
-
读写大字段数据
-
Oracle 动态方法 4
-
ANSI 动态方法 4
-
执行 PL/SQL 块
-
完整的编程示例
5.1. 连接数据库
Pro*C/C++ 中的连接有多种方式，用户可以根据所要编写的应用的实际场景选择合适的连接方式，并按需设定相应的连接参数。
在多连接方式下，能够指向性地选择所需要的连接是必要的功能。
void do_connect() { print_title("Test for \"CONNECT\" clause"); EXEC SQL BEGIN DECLARE SECTION; char auser[100] = "c##usr01"; char apwd[100] = "usr01"; char adbname[100] = "orcl"; char auser_pwd[100] = "c##usr01/usr01@orcl"; EXEC SQL END DECLARE SECTION; /* 默认连接 */ EXEC SQL CONNECT :auser IDENTIFIED BY :apwd USING :adbname; if ( SQLCODE != 0) { printf("CONNECT Datebase error.\n"); exit(0); } /* 连接 1 */ EXEC SQL DECLARE conn_1 DATABASE; EXEC SQL CONNECT :auser IDENTIFIED BY :apwd AT conn_1 USING :adbname; if ( SQLCODE != 0) { printf("CONNECT Datebase error.\n"); exit(0); } /* 连接 2 */ EXEC SQL DECLARE conn_2 DATABASE; EXEC SQL CONNECT :auser_pwd AT conn_2; if ( SQLCODE != 0) { printf("CONNECT Datebase error.\n"); exit(0); } printf("CONNECT Datebase successfully.\n"); }5.2. 执行一条不返回结果的 SQL 语句
以下是一个执行插入 SQL 语句的示例程序：
void do_insert_basedata() { print_title("Prepare base data"); EXEC SQL BEGIN DECLARE SECTION; int ids[7] = {0}; char sNames[10][10] = {0}; EXEC SQL END DECLARE SECTION; /* 准备基本数据数组 */ ids[0] = 1; ids[1] = 2; ids[2] = 3; ids[3] = 9; ids[4] = 95; ids[5] = 2022; ids[6] = 43001; strcpy(sNames[0], "ababab"); strcpy(sNames[1], "abcabc"); strcpy(sNames[2], "3d3d3d3d"); /* strcpy(sNames[3], ""); */ strcpy(sNames[4], "xkhxx"); strcpy(sNames[5], "dynamic"); strcpy(sNames[6], "dynamic3"); EXEC SQL INSERT INTO tab01 values(:ids, :sNames); EXEC SQL COMMIT; if ( SQLCODE == 0) { printf("INSERT %d row data with array.\n", sqlca.sqlerrd[2]); } }5.3. 执行一条查询语句并获取结果
以下是一个执行查询 SQL 语句的示例程序：
void do_normal_select() { print_title("Test for \"SELECT\" clause"); EXEC SQL BEGIN DECLARE SECTION; int iId = 0; char sName[50] = {0}; varchar vName[50] = {0}; int *pId = NULL; char *pName = NULL; /* 指示符变量 */ short ind_name; EXEC SQL END DECLARE SECTION; /* 选择Normal变量 */ EXEC SQL SELECT name INTO :sName from tab01 where id=1; printf("Normal => name:[%s]\n", sName); /* 选择varchar变量 */ EXEC SQL SELECT name INTO :vName from tab01 where id=1; printf("Normal => name:[%s]\n", vName.arr); /* 选择指针变量 */ iId = 0; memset(sName, 0x00, sizeof(sName)); pId = &iId; pName = "ababab"; EXEC SQL SELECT id, name INTO :pId,:sName from tab01 where name=:pName; printf("Normal => id:[%d] name:[%s]\n", *pId, sName); /* 选择指示器变量 */ iId = 0; memset(sName, 0x00, sizeof(sName)); EXEC SQL SELECT id, name INTO :iId, :sName:ind_name from tab01 where id=9; if( ind_name==-1 ) { printf("Normal indicator => id:[%d] name:[%s]\n", *pId, "(NULL)"); } else { printf("Normal indicator => id:[%d] name:[%s]\n", *pId, sName); } /*****************************************************************/ /* 使用varchar变量进行选择 */ iId = 0; memset(sName, 0x00, sizeof(sName)); memset((void*)&vName, 0x00, sizeof(vName)); strcpy(vName.arr, "ababab"); vName.len = strlen((char*)vName.arr); EXEC SQL SELECT id, name INTO :pId,:sName from tab01 where name=:vName; printf("Normal => id:[%d] name:[%s]\n", *pId, sName); }5.4. 读写大字段数据
以下为执行写入和读取 BLOB 数据的示例程序：
#include
#include #include #include #define BUFSIZE 128 OCIBlobLocator *blob; OCIClobLocator *clob1; OCIClobLocator *clob2; unsigned int amt, offset = 1, buflen, lobLen, chunkSize; int isOpen, isTemporary; unsigned char buff[BUFSIZE]; unsigned char outBuff[BUFSIZE]; EXEC SQL INCLUDE SQLCA; /* 说明一个 SQL 通信区 */ EXEC SQL WHENEVER SQLERROR CONTINUE; void do_lob_func() { print_title("Test for \"LOB\""); int i; EXEC SQL DROP TABLE t_proc_lob; /* 开始测试 */ EXEC SQL WHENEVER SQLERROR do print_sqlca(); EXEC SQL CREATE TABLE t_proc_lob(b blob, c1 clob, c2 nclob); EXEC SQL INSERT INTO t_proc_lob VALUES(empty_blob(), empty_clob(), empty_clob()); EXEC SQL ALLOCATE :blob ; EXEC SQL ALLOCATE :clob1 ; EXEC SQL ALLOCATE :clob2 ; EXEC SQL SELECT b, c1, c2 into :blob, :clob1, :clob2 FROM t_proc_lob FOR UPDATE; EXEC SQL VAR buff IS RAW(BUFSIZE); EXEC SQL VAR outBuff IS RAW(BUFSIZE); /* 执行lob数据写 */ memset(buff, 'A', BUFSIZE); amt = 80; buflen = 80; EXEC SQL LOB WRITE ONE :amt FROM :buff WITH LENGTH :buflen INTO :blob AT :offset; /* 执行lob数据读 */ memset(outBuff, 0, BUFSIZE); amt = BUFSIZE; EXEC SQL LOB READ :amt FROM :blob AT :offset INTO :outBuff; EXEC SQL LOB DESCRIBE :blob GET LENGTH, CHUNKSIZE INTO :lobLen, :chunkSize; EXEC SQL LOB DESCRIBE :blob GET LENGTH, ISOPEN, ISTEMPORARY, CHUNKSIZE INTO :lobLen, :isOpen, :isTemporary, :chunkSize; EXEC SQL LOB DESCRIBE :blob GET CHUNKSIZE, ISOPEN, ISTEMPORARY, LENGTH INTO :chunkSize, :isOpen, :isTemporary, :lobLen; for(i=0; i 5.5. Oracle 动态方法 4
动态 SQL 方法四是动态 SQL 的主要使用方式，因为查询数据项的个数、输入主变量的占位符个数以及输入主变量的数据类型可能直到语句执行前才是可知的。 也因为如此，动态 SQL 方法 4 主要是通过描述符实现的，详细使用方法如下示例程序：
EXEC SQL WHENEVER SQLERROR GOTO end_select_loop; #define ID_SIZE 10 #define NAME_SIZE 10 #define INAME_SIZE 10 #define ARRAY_SIZE 5 #define MAX_ITEMS 20 int array_size = ARRAY_SIZE; char * sqlda_select_stmt="select id,name from tab01 where id>=:id"; SQLDA *select_dp; int ids[ARRAY_SIZE]; int id1, id2; char names[ARRAY_SIZE][NAME_SIZE]; short ind_ids[ARRAY_SIZE] = {0,0,0,0,0}; short ind_names[ARRAY_SIZE] = {0,0,0,0,0}; void do_sqldyn_select() { print_title("Test for \"SQLDA Dynamic SQL\" select"); int i, null_ok, precision, scale; char bind_var[MAX_ITEMS][ID_SIZE] = {0}; bind_dp = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_ITEMS, NAME_SIZE, INAME_SIZE); bind_dp->N = MAX_ITEMS; select_dp = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_ITEMS, NAME_SIZE, INAME_SIZE); select_dp->N = MAX_ITEMS; /* 分配指向索引变量和实际数据的指针 */ for (i = 0; i < MAX_ITEMS; i++) { bind_dp->I[i] = (short*)malloc(sizeof(short)); select_dp->I[i] = (short*)malloc(sizeof(short)); bind_dp->V[i] = (char*)malloc(1); select_dp->V[i] = (char*)malloc(1); } EXEC SQL PREPARE stmt FROM :sqlda_select_stmt; EXEC SQL DECLARE C CURSOR FOR stmt; strcpy(bind_var[0], "1014"); EXEC SQL DESCRIBE BIND VARIABLES FOR stmt INTO bind_dp; if (bind_dp->F < 0) { printf ("***Too many bind param[%d], max is [%d]\n", -bind_dp->F, MAX_ITEMS); return; } bind_dp->N = bind_dp->F; for (i = 0; i < bind_dp->F; i++) { bind_dp->L[i] = strlen(bind_var[i]); bind_dp->V[i] = (char*)realloc(bind_dp->V[i], (bind_dp->L[i]+1)); memcpy(bind_dp->V[i], bind_var[i], bind_dp->L[i]+1); *bind_dp->I[i] = 0; bind_dp->T[i] = 1; } /* 开启游标 */ EXEC SQL OPEN C USING DESCRIPTOR bind_dp; select_dp->N = MAX_ITEMS; EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO select_dp; if (select_dp->F < 0) { printf ("***Too many select-items[%d], max is [%d]\n", -select_dp->F, MAX_ITEMS); return; } select_dp->N = select_dp->F; for (i = 0; i < select_dp->F; i++) { char title[NAME_SIZE]; SQLColumnNullCheck (SQL_SINGLE_RCTX, (unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok); switch (select_dp->T[i]) { case 1: /* CHAR datatype: no change in length needed, except possibly for TO_CHAR conversions (not handled here). */ break; case 2: /* NUMBER datatype: use SQLNumberPrecV6() to extract precision and scale. */ SQLNumberPrecV6(SQL_SINGLE_RCTX, (unsigned long*)&(select_dp->L[i]), &precision, &scale); /* 当Scale大于0时，将NUMBER数据类型转换成FLOAT，否则转换为INT类型 */ select_dp->L[i] = (scale > 0) ? sizeof(float) : sizeof(int); break; } if (select_dp->T[i] != 2) { select_dp->V[i] = (char*)realloc(select_dp->V[i], select_dp->L[i]+1); } else { select_dp->V[i] = (char*)realloc(select_dp->V[i], select_dp->L[i]); } / 当名字以null终止时，复制到临时缓冲区 */ memset(title, ' ', NAME_SIZE); strncpy(title, select_dp->S[i], select_dp->C[i]); if (select_dp->T[i] == 2) if (scale > 0) printf ("%.*s ", select_dp->L[i]+3, title); else printf ("%.*s ", select_dp->L[i], title); else printf("%-.*s ", select_dp->L[i], title); if (select_dp->T[i] == 2) if (scale > 0) select_dp->T[i] = 4; /* float */ else select_dp->T[i] = 3; /* int */ } printf ("\n"); EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop; for (;;) { EXEC SQL FETCH C USING DESCRIPTOR select_dp; /* 由于返回的每个变量都被强制转换为字符串、 int或float，这里几乎不需要进行任何处理。 这个例行程序指示在终端上打印值。 */ for (i = 0; i < select_dp->F; i++) { if (*select_dp->I[i] < 0) if (select_dp->T[i] == 4) printf ("%-*c ",(int)select_dp->L[i]+3, ' '); else printf ("%-*c ",(int)select_dp->L[i], ' '); else if (select_dp->T[i] == 3) /* int datatype */ printf ("%*d ", (int)select_dp->L[i], *(int *)select_dp->V[i]); else if (select_dp->T[i] == 4) /* float datatype */ printf ("%*.2f ", (int)select_dp->L[i], *(float *)select_dp->V[i]); else /* character string */ printf ("%-*.*s ", (int)select_dp->L[i], (int)select_dp->L[i], select_dp->V[i]); } printf ("\n"); } end_select_loop: if(SQLCODE!=0) { print_sqlca(); } /** 打印处理行数 **/ printf("******%d rows selected.\n\n", sqlca.sqlerrd[2]); /** 完成后，释放分配给绑定和选择描述符指针的内存 **/ for (i = 0; i < MAX_ITEMS; i++) { if (bind_dp->V[i] != (char *) 0) free(bind_dp->V[i]); free(bind_dp->I[i]); /* 分配MAX_ITEMS. */ if (select_dp->V[i] != (char *) 0) free(select_dp->V[i]); free(select_dp->I[i]); /* 分配MAX_ITEMS. */ } /* 释放描述符使用的空间. */ SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp); SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp); /*关闭游标 */ EXEC SQL CLOSE C; }5.6. ANSI 动态方法 4
EXEC SQL WHENEVER SQLERROR do print_sqlca(); EXEC SQL WHENEVER NOT FOUND DO BREAK; char * mthd4_stmt3="select id,name from tab01 where id>=:id"; void do_dynamic_method4_3() { print_title("Test for \"Dynamic SQL\" method4 3"); EXEC SQL BEGIN DECLARE SECTION; char sName[50]; int occurs = 0; int out_cnt = 0; int out_len = 0; int out_type = 0; int out_octet_len = 0; int out_ret_len = 0; int out_ret_octet_len = 0; int out_precision = 0; int out_scale = 0; char out_name[20] = {0}; char out_char_set_name[20] = {0}; short out_nullable; short out_ind = 0; short in_ind = 0; EXEC SQL END DECLARE SECTION; int i; int id_type=3, id_len=4, id_data=1; int id1_type=2, id1_data=0; /* SQLT_NUM 2 */ int name_type=1; /* SQLT_CHR 1 */ char name_data[50]; memset(name_data, 0x00, sizeof(name_data)); /* 执行一个查询 */ EXEC SQL ALLOCATE DESCRIPTOR 'input'; EXEC SQL ALLOCATE DESCRIPTOR 'output'; EXEC SQL PREPARE s FROM :mthd4_stmt3; EXEC SQL DESCRIBE INPUT s USING DESCRIPTOR 'input'; EXEC SQL DESCRIBE OUTPUT s USING DESCRIPTOR 'output'; /* 处理输入值 */ EXEC SQL SET DESCRIPTOR 'input' VALUE 1 TYPE = :id_type, LENGTH = :id_len, DATA = :id_data; EXEC SQL DECLARE c CURSOR FOR s; EXEC SQL OPEN c USING DESCRIPTOR 'input'; /* 处理输入值 */ EXEC SQL SET DESCRIPTOR 'input' VALUE 1 TYPE = :id_type, LENGTH = :id_len, DATA = :id_data; EXEC SQL DECLARE c CURSOR FOR s; EXEC SQL OPEN c USING DESCRIPTOR 'input'; /* 处理输出值 */ EXEC SQL GET DESCRIPTOR 'output' :out_cnt = COUNT; for ( i=0; i5.7. 执行 PL/SQL 块
#include
#include #include #include #include EXEC SQL BEGIN DECLARE SECTION; int acct; double debit; double new_bal; VARCHAR status[20]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; void do_plsql() { print_title("Embedded PL/SQL Debit Transaction Demo"); EXEC SQL WHENEVER SQLERROR GOTO errprint; EXEC SQL DROP TABLE IF EXISTS accounts; EXEC SQL DROP TABLE IF EXISTS journal; EXEC SQL CREATE TABLE accounts(account_id number(4) not null, bal number(11,2)); EXEC SQL CREATE TABLE journal(account_id number(4) not null, action varchar2(45) not null, amount number(11,2), date_tag date not null); EXEC SQL INSERT INTO accounts VALUES (1, 1000.00); acct = 1; debit = 100; /* ----- 开始PL/SQL块 ----- */ EXEC SQL EXECUTE DECLARE tmp_bal NUMBER(11,2); tmp_sta VARCHAR(65); BEGIN DECLARE insufficient_funds EXCEPTION; old_bal NUMBER; min_bal CONSTANT NUMBER := 500; BEGIN SELECT bal INTO old_bal FROM accounts WHERE account_id = :acct; -- If the account doesn't exist, the NO_DATA_FOUND -- exception will be automatically raised. tmp_bal := old_bal - :debit; IF tmp_bal >= min_bal THEN UPDATE accounts SET bal = tmp_bal WHERE account_id = :acct; INSERT INTO journal VALUES (:acct, 'Debit', :debit, SYSDATE); tmp_sta := 'Transaction completed.'; ELSE RAISE insufficient_funds; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN tmp_sta := 'Account not found.'; tmp_bal := -1; WHEN insufficient_funds THEN tmp_sta := 'Insufficient funds.'; tmp_bal := old_bal; WHEN OTHERS THEN ROLLBACK; tmp_sta := 'Error: ' || SQLERRM(SQLCODE); tmp_bal := -1; END; BEGIN :status := tmp_sta; :new_bal := tmp_bal; END; END; END-EXEC; /* ----- 结束 PL/SQL 块 ----- */ /* 以0终止字符串 */ status.arr[status.len] = '\0'; printf("===PL/SQL test ok.\n"); printf("===Status: %s\n", status.arr); if (new_bal >= 0) printf("===Balance is now: $%.2f\n", new_bal); return; errprint: printf(">>> PL/SQL test failed.\n"); printf(">>> Error during execution:\n"); printf(">>> %s\n",sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK RELEASE; } 5.8. 完整的编程示例
#include
#include #include EXEC SQL INCLUDE SQLCA; /* 说明一个 SQL 通信区 */ EXEC SQL INCLUDE ORACA; /* 说明一个 SQL 通信区 */ EXEC SQL WHENEVER SQLERROR do print_sqlca(); void print_sqlca(); int do_main_select(); char * main_stmt="SELECT id,name FROM tab01 WHERE id>:id "; char name_data[100]; long SQLCODE = 0; void print_sqlca() { printf("=== SQLDA ===\n"); printf("SQLCODE:[\e[31m%d\e[0m], SQLERR:[\e[31m%.*s\e[0m]\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); } int main() { EXEC SQL BEGIN DECLARE SECTION; char auser[20] = "system"; char apwd[20] = "123456"; char adbname[20] = "KingbaseES"; int ids[8] = {0}; char sNames[10][10] = {0}; int iId = 0; char nName[20] = {0}; EXEC SQL END DECLARE SECTION; EXEC ORACLE OPTION ( ORACA = YES ) ; /* 连接数据库 */ EXEC SQL CONNECT :auser IDENTIFIED BY :apwd USING :adbname; if ( SQLCODE != 0) { printf("CONNECT Datebase error.\n"); exit(0); } /* 创建表 */ EXEC SQL CREATE TABLE TAB01 (id number(38), name varchar2(15)); ids[0] = 1; ids[1] = 2; ids[2] = 3; ids[3] = 9; ids[4] = 98; ids[5] = 99; ids[6] = 2022; ids[7] = 43001; strcpy(sNames[0], "ababab"); strcpy(sNames[1], "abcabc"); strcpy(sNames[2], "3d3d3d3d"); strcpy(sNames[3], ""); strcpy(sNames[4], "xkhxx"); strcpy(sNames[5], "dynamic"); strcpy(sNames[6], "dynamic"); strcpy(sNames[7], "dynamic3"); /* 数据插入 */ EXEC SQL INSERT INTO tab01 values(:ids, :sNames); EXEC SQL COMMIT; if ( SQLCODE == 0) { printf("INSERT %d row data with array.\n", sqlca.sqlerrd[2]); } /* 数据查询 */ EXEC SQL SELECT name INTO :nName from tab01 where id=1; /* 数据更新 */ EXEC SQL UPDATE tab01 SET name='Update1' WHERE id=98; memset(nName, 0x00, sizeof(nName)); memcpy(nName, "Update2", sizeof("Update2")); EXEC SQL UPDATE tab01 SET name=:sName WHERE id=99; /* 数据删除 */ EXEC SQL DELETE FROM tab01 WHERE id=98; EXEC SQL COMMIT; iId = 99; EXEC SQL DELETE FROM tab01 WHERE id=:iId; EXEC SQL COMMIT; /* 清空表 */ EXEC SQL TRUNCATE TABLE TAB01; /* 删除表 */ EXEC SQL DROP TABLE TAB01; return 0; } -
C语言初阶-指针详解-庖丁解牛篇
大小端的理解以及宏定义实现的理解
Salesforce-Apex中的数据操控（DML&数据库方法）
基于多云构建监控告警系统
云服务器最佳实践-Linux云服务器SSH登录的安全加固
秋招面经第十二弹：东方航空一面-数据工程师
华为云,阿里云,腾讯云 安全组配置规则
基于Java+vue前后端分离高校疫情管理系统设计实现(源码+lw+部署文档+讲解等)
《Head First HTML5 javascript》第3章 探索浏览器 知识点总结