pro*c测试代码和编译makefile

#include <stdio.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

int main()
{
	EXEC SQL BEGIN DECLARE SECTION;
		int count;
		char userpwd[100];
		char dbname[100];
		char records[100];
		char line1[100];
		char line2[100];
	EXEC SQL END DECLARE SECTION;

	memset(records, 0, sizeof(records));
	memset(line1, 0, sizeof(line1));
	memset(line2, 0, sizeof(line2));
	
	EXEC SQL WHENEVER SQLERROR GOTO err;

	strcpy(userpwd, "elink/elink");
	strcpy(dbname, "elink");

	EXEC SQL CONNECT :userpwd USING :dbname;
	
	EXEC SQL SELECT ACC INTO :records FROM inst WHERE inst_index=555;
	printf("result is [%s]\n\n", records);

	/* commit and release test */
	EXEC SQL UPDATE INST SET ACC='123456' WHERE INST_INDEX='5';
	EXEC SQL COMMIT;
	//EXEC SQL COMMIT WORK RELEASE;

	EXEC SQL SELECT ACC INTO :records FROM inst WHERE inst_index='5';
	printf("result is [%s]\n\n", records);

	/* cursor test */
	EXEC SQL DECLARE inst_cursor CURSOR FOR
		SELECT * FROM INST;

	EXEC SQL OPEN inst_cursor;

	EXEC SQL WHENEVER NOTFOUND DO BREAK;
	while(1) {
		EXEC SQL FETCH inst_cursor INTO :line1,:line2;
		printf("line1=[%s], line2=[%s]\n", line1, line2);
	}
	EXEC SQL WHENEVER NOTFOUND CONTINUE;
	putchar('\n');

	EXEC SQL CLOSE inst_cursor;

	EXEC SQL EXECUTE
	declare sRight varchar2(60);
		icount number(4);
	begin 
		for curTable in (select code as svalue from inst) 
		loop
			--select count(code) into icount from inst;
			--if icount>2 then
				dbms_output.put_line(curTable.svalue);
			--end if;
		end loop;
	end;
	END-EXEC;

	return 0;

err:
	printf("error : [%d][%s]!\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
	return sqlca.sqlcode;
}

makefile:

SRCPC=pro_c_test.pc
SRC=pro_c_test.c
test:$(SRCPC)
	proc SQLCHECK=SEMANTICS USERID=elink/elink oname=$(SRC) $(SRCPC)
	gcc -g -o test $(SRC) -L$(ORACLE_HOME)/lib -lclntsh

.PHONY:clean
clean:
	-rm -rf *.lis *.c test

如果INTO后面跟的变量空间不够存放查询出来的结果，PRO*C会自动截断查询的结果，并自动在末尾填充空字符，从而不会导致内存溢出（如果INTO后面是结构体也一样）。

可以在PL/SQL的sql窗口中直接运行的PL/SQL块：

begin
  for curTable in (select pr_prodct_cd as dprodct_cd, pr_mcc as dpr_mcc, pr_eci_ind as dpr_eci_ind from cp_prdfrq)
  loop
    insert into cp_prdfrq_ext(pr_prodct_cd,pr_mcc,pr_eci_ind) values(curTable.dprodct_cd,curTable.dpr_mcc,curTable.dpr_eci_ind);
  end loop;
end;

commit work;