PRO*C (转)

单行读

#include
#include

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "scott/tiger";
char db_ename[30];
int db_deptno;
EXEC SQL END DECLARE SECTION;

void main() {
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL CONNECT :connstr;

EXEC SQL WHENEVER NOTFOUND GOTO notfound;
EXEC SQL SELECT ENAME, DEPTNO
INTO db_ename, db_deptno
FROM EMP
WHERE EMPNO = 7369;

found:
printf("%s is in department %i ", db_ename, db_deptno);
return;

notfound:
printf("Employee record not found in database. ");
return;
}

void sqlerror() {
printf("Stop Error: %25i ", sqlca.sqlcode);
return;
}

多行读

#include
#include

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "scott/tiger";
char db_ename[30];
int db_deptno;
EXEC SQL END DECLARE SECTION;

void main() {
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL CONNECT :connstr;

EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ENAME, DEPTNO
FROM EMP;

EXEC SQL OPEN emp_cursor;

EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FETCH emp_cursor INTO :db_ename, :db_deptno;
printf(" %s %i ", db_ename, db_deptno);
}

EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
return;
}

void sqlerror() {
printf("Stop Error: %25i ", sqlca.sqlcode);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
return;
}

调用PL/SQL段

#include
#include

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *sqlstmt = "begin :x := upper(:x); end;";
char *connstr = "scott/tiger@ORCL";
char valstr[25];
EXEC SQL END DECLARE SECTION;

void main() {
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL CONNECT :connstr;

EXEC SQL PREPARE stmt1 FROM :sqlstmt;
strcpy(valstr, "Hi world");
EXEC SQL EXECUTE stmt1 USING :valstr;

printf("Return value: %s ", valstr);
return;
}

void sqlerror() {
printf("Stop Error: %25i ",sqlca.sqlcode);
return;
}

Example Pro*C program to describe a database procedure and print

#define MAX_PLSQL_PARAMETERS 30

#include
#include

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

typedef char strz[31];
EXEC SQL TYPE strz IS STRING(31) REFERENCE;

/* Declare functions */
int ora_logon();
int ora_logoff();
int ora_error();
int plsql_desc(char *procname);

/*--------------------------------------------------------------------*/
int main() {
ora_logon();
printf("About to describe procedure DBMS_OUTPUT.PUT_LINE... ");
plsql_desc("DBMS_OUTPUT.PUT_LINE");
ora_logoff();
return 0;
}

/*--------------------------------------------------------------------
* Login to the Oracle database
*--------------------------------------------------------------------*/
int ora_logon() {

EXEC SQL BEGIN DECLARE SECTION;
char *oracleid = "monitor/oramon";
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL CONNECT :oracleid;
if (sqlca.sqlcode != 0) {
printf("ERROR: Unable to login to Oracle %.*s",
sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
}
}

/*--------------------------------------------------------------------
* Logoff from the Oracle database
*--------------------------------------------------------------------*/
int ora_logoff() {
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ALTER SESSION SET SQL_TRACE FALSE;
EXEC SQL COMMIT WORK RELEASE;
}

/*--------------------------------------------------------------------
* Handle Oracle errors
*--------------------------------------------------------------------*/
int ora_error() {
char errmsg[2000];
unsigned int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof(errmsg);
sqlglm(errmsg, &buf_len, &msg_len);
printf("Oracle Error: %.*s ", msg_len, errmsg);
exit(8);
}

/*--------------------------------------------------------------------
* Describe PL/SQL parameters
*--------------------------------------------------------------------*/
int plsql_desc(char *procname) {

int overload [MAX_PLSQL_PARAMETERS];
int position [MAX_PLSQL_PARAMETERS];
int level [MAX_PLSQL_PARAMETERS];
static strz argument_name [MAX_PLSQL_PARAMETERS];
static short arg_name_ind [MAX_PLSQL_PARAMETERS];
static int datatype [MAX_PLSQL_PARAMETERS];
int default_value [MAX_PLSQL_PARAMETERS];
int in_out [MAX_PLSQL_PARAMETERS];
int length [MAX_PLSQL_PARAMETERS];
int precision [MAX_PLSQL_PARAMETERS];
int scale [MAX_PLSQL_PARAMETERS];
int radix [MAX_PLSQL_PARAMETERS];
int spare [MAX_PLSQL_PARAMETERS];
int i = 0;
int t = -1; /* Default return type is -1 */

for (i=0; i overload[i] = -1;
}

EXEC SQL WHENEVER SQLERROR DO ora_error();
EXEC SQL EXECUTE
BEGIN
dbms_describe.describe_procedure(:procname, NULL, NULL,
:overload, :position, :level, :argument_name:arg_name_ind,
:datatype, :default_value, :in_out, :length, :precision,
:scale, :radix, :spare);
END;
END-EXEC;

/* The datatypes and their numeric type codes are:
0 placeholder for procedures with no arguments
1 VARCHAR, VARCHAR, STRING
2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR (ANSI FIXED CHAR), CHARACTER
106 MLSLABEL
250 PL/SQL RECORD
251 PL/SQL TABLE
252 PL/SQL BOOLEAN
*/

/* Print parameters with types */
i = 0;
while ((overload[i] != -1) && (i < MAX_PLSQL_PARAMETERS)) {
printf ("pos=%d overl=%d ", position[i], overload[i]);
if (arg_name_ind[i] != -1) {
printf("nam=%s ", argument_name[i]);
}
printf("lev=%d typ=%d len=%d ", level[i],datatype[i],length[i]);
printf("prec=%d scale=%d radix=%d ",precision[i],scale[i],radix[i]);
i++;
}

}

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-923265/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-923265/