测试环境：
服务器：docker中的oracle服务器，服务名为xe，参考unbuntu下Docker安装oracle和mysql
客户端：instantclient客户端，参考打造ubuntu下精简版的oracle客户端及pro*c编译环境
在/opt/oracle/product/network/admin/tnsnames.ora配置服务名XE如下：
XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xe)
)
)
一、创建程序包和程序体
emp_actions.sql
CREATE OR REPLACE PACKAGE emp_actions AS
TYPE CharArrayTyp IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
TYPE NumArrayTyp IS TABLE OF FLOAT
INDEX BY BINARY_INTEGER;
PROCEDURE get_employees(
dept_number IN INTEGER,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT CharArrayTyp,
job_title OUT CharArrayTyp,
salary OUT NumArrayTyp);
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS
CURSOR get_emp (dept_number IN INTEGER) IS
SELECT ename, job, sal FROM emp
WHERE deptno = dept_number;
PROCEDURE get_employees(
dept_number IN INTEGER,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT CharArrayTyp,
job_title OUT CharArrayTyp,
salary OUT NumArrayTyp) IS
BEGIN
IF NOT get_emp%ISOPEN THEN
OPEN get_emp(dept_number);
END IF;
done_fetch := 0;
found := 0;
FOR i IN 1..batch_size LOOP
FETCH get_emp INTO emp_name(i),
job_title(i), salary(i);
IF get_emp%NOTFOUND THEN
CLOSE get_emp;
done_fetch := 1;
EXIT;
ELSE
found := found + 1;
END IF;
END LOOP;
END get_employees;
END emp_actions;
/
二、pro*c程序
sample9.pc
/*************************************************************
Sample Program 9: Calling a stored procedure
This program connects to ORACLE using the SCOTT/TIGER
account. The program declares several host arrays, then
calls a PL/SQL stored procedure (GET_EMPLOYEES in the
CALLDEMO package) that fills the table OUT parameters. The
PL/SQL procedure returns up to ASIZE values.
Sample9 keeps calling GET_EMPLOYEES, getting ASIZE arrays
each time, and printing the values, until all rows have been
retrieved. GET_EMPLOYEES sets the done_flag to indicate "no
more data."
*************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
EXEC SQL INCLUDE sqlca.h;
typedef char asciz[20];
typedef char vc2_arr[11];
EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;
/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;
asciz username;
asciz password;
asciz sid;
int dept_no; /* which department to query? */
vc2_arr emp_name[10]; /* array of returned names */
vc2_arr job[10];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
EXEC SQL END DECLARE SECTION;
long SQLCODE;
void print_rows(); /* produces program output */
void sql_error(); /* handles unrecoverable errors */
int main()
{
int i;
char temp_buf[32];
/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
strcpy(username, "scott");
strcpy(password, "tiger");
strcpy(sid, "XE");
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :sid;
printf("\nConnected to ORACLE as user: %s\n\n", username);
printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);/* Print column headers. */
printf("\n\n");
printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%s\n", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop.
* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back.
*/
for (;;)
{
EXEC SQL EXECUTE
BEGIN emp_actions.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;
print_rows(num_ret);
if (done_flag)
break;
}
/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void
print_rows(n)
int n;
{
int i;
if (n == 0)
{
printf("No rows retrieved.\n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2f\n",
emp_name[i], job[i], salary[i]);
}
/* Handle errors. Exit on any error. */
void
sql_error()
{
char msg[512];
int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);
printf("\nORACLE error detected:");
printf("\n%.*s \n", msg_len, msg);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}
三、makefile
TARGET = sample9
SRCS = sample9.c
OBJS = sample9.o
COMMLIB = -lpthread -lclntsh
LINKER = $(CC)
RM = /bin/rm -f
PROC = proc
PROCFLAGS = code=ANSI_C ireclen=2048 oreclen=2048 parse=none\
SQLCHECK=SEMANTICS userid=scott/tiger@XE
CFLAGS = -I$(ORACLE_HOME)/sdk/include\
-L$(ORACLE_HOME)/lib
.SUFFIXES: .o .c .pc
.c.o:
$(CC) -c $(CFLAGS) $<
.pc.c:
@echo "PROC ."
$(PROC) iname=$*.pc $(PROCFLAGS)
$(TARGET): $(SRCS) $(OBJS)
$(CC) -o $@ $(OBJS) $(CFLAGS) $(COMMLIB)
clean :
$(RM) *.lis $(SRCS) $(TARGET) $(OBJS) $(TARGET:%=%.c)四、运行
$ make
PROC .
proc iname=sample9.pc code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include include=/usr/include/linux include=/usr/local/include SQLCHECK=SEMANTICS userid=scott/tiger@XE
Pro*C/C++: Release 11.2.0.4.0 - Production on 星期六 5月 26 14:43:24 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
系统默认选项值取自于: /opt/ora11g/instantclient_11_2/precomp/admin/pcscfg.cfg
cc -c -I/opt/ora11g/instantclient_11_2/sdk/include -L/opt/ora11g/instantclient_11_2/lib sample9.c
...........................................
$ ls
emp_actions.sql makefile sample9 sample9.c sample9.lis sample9.o sample9.pc
$ ./sample9
Connected to ORACLE as user: scott
Enter department number: 20
Employee Job Salary
-------- --- ------
SMITH CLERK 800.00
JONES MANAGER 2975.00
SCOTT ANALYST 3000.00
ADAMS CLERK 1100.00
FORD ANALYST 3000.00
$ ./sample9
Connected to ORACLE as user: scott
Enter department number: 10
Employee Job Salary
-------- --- ------
CLARK MANAGER 2450.00
KING PRESIDENT 5000.00
MILLER CLERK 1300.00
五、删除程序包和程序包体
测试后不想留着emp_actions就用先面两个语句删除
SQL> drop PACKAGE BODY emp_actions;
程序包体已删除。
SQL> drop PACKAGE emp_actions;
程序包已删除。
wsl中访问主机oracle服务：
环境：
export ORACLE_HOME=/mnt/d/ora11g/instantclient_11_2
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG='simplified chinese_china'.UTF8
export PATH=$ORACLE_HOME/bin:$PATH
alias sqlplus='/usr/bin/rlwrap sqlplus'makefile
TARGET = procdemo
SRCS = procdemo.c
OBJS = procdemo.o
COMMLIB = -lpthread -lclntsh
LINKER = $(CC)
RM = /bin/rm -f
PROC = proc
PROCFLAGS = code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include\
include=/usr/include/linux\
include=/usr/local/include
CFLAGS = -I$(ORACLE_HOME)/sdk/include\
-L$(ORACLE_HOME)/lib
.SUFFIXES: .o .c .pc
.c.o:
$(CC) -c $(CFLAGS) $<
.pc.c:
@echo "PROC ."
$(PROC) iname=$*.pc $(PROCFLAGS)
$(TARGET): $(SRCS) $(OBJS)
$(CC) -o $@ $(OBJS) $(CFLAGS) $(COMMLIB)
clean :
$(RM) *.lis $(SRCS) $(TARGET) $(OBJS) $(TARGET:%=%.c)
procdemo.pc
/*
* procdemo.pc
*
* This program connects to ORACLE, declares and opens a cursor,
* fetches the names, salaries, and commissions of all
* salespeople, displays the results, then closes the cursor.
*/
#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
/*
* Use the precompiler typedef'ing capability to create
* null-terminated strings for the authentication host
* variables. (This isn't really necessary--plain char *'s
* would work as well. This is just for illustration.)
*/
EXEC SQL BEGIN DECLARE SECTION;
char username[20];
char password[11];
char sid[11];
struct emp_info
{
char emp_name[20];
float salary;
float commission;
};
struct emp_info *emp_rec_ptr;
EXEC SQL END DECLARE SECTION;
void sql_error(msg)
char *msg;
{
char err_msg[512];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
/* Allocate memory for emp_info struct. */
if ((emp_rec_ptr =
(struct emp_info *) malloc(sizeof(struct emp_info))) == 0)
{
fprintf(stderr, "Memory allocation error.\n");
exit(EXIT_FAILURE);
}
/* Connect to ORACLE. */
strcpy(username, "scott");
strcpy(password, "tiger");
strcpy(sid, "orcl");
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :sid;
printf("\nConnected to ORACLE as user: %s\n", username);
/* Declare the cursor. All static SQL explicit cursors
* contain SELECT commands. 'salespeople' is a SQL identifier,
* not a (C) host variable.
*/
EXEC SQL DECLARE salespeople CURSOR FOR
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB LIKE 'SALES%';
/* Open the cursor. */
EXEC SQL OPEN salespeople;
/* Get ready to print results. */
printf("\n\nThe company's salespeople are--\n\n");
printf("Salesperson Salary Commission\n");
printf("----------- ------ ----------\n");
/* Loop, fetching all salesperson's statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
EXEC SQL FETCH salespeople INTO :emp_rec_ptr;
printf("%s %9.2f %12.2f\n", emp_rec_ptr->emp_name,
emp_rec_ptr->salary, emp_rec_ptr->commission);
}
/* Close the cursor. */
EXEC SQL CLOSE salespeople;
printf("\nGOOD-BYE!!\n\n");
EXEC SQL COMMIT WORK RELEASE;
exit(EXIT_SUCCESS);
}
执行：
$ ./procdemo
Connected to ORACLE as user: scott
The company's salespeople are--
Salesperson Salary Commission
----------- ------ ----------
ALLEN 1600.00 300.00
WARD 1250.00 500.00
MARTIN 1250.00 1400.00
TURNER 1500.00 0.00
GOOD-BYE!!
occi
// g++-4.8 -I${ORACLE_HOME}/sdk/include -o OCCIConnect OCCIConnect.cpp -L${ORACLE_HOME}/lib -locci -lclntsh
#include <iostream>
#define LINUXOCCI //避免函数重定义错误
#include <occi.h>
#include <stdio.h>
using namespace std;
using namespace oracle::occi;
int main(){
Environment *env=Environment::createEnvironment();
cout<<"success"<<endl;
string name = "mymotif";
string pass = "wxwpxh";
string srvName = "orcl";
try {
Connection *conn = env->createConnection(name, pass, srvName);
cout<<"conn success"<<endl;
// 数据操作,创建Statement对象
Statement *pStmt = NULL; // Statement对象
pStmt = conn->createStatement();
if(NULL == pStmt) {
printf("createStatement error.\n");
return -1;
}
// 查询数据库时间
std::string strTemp;
ResultSet *pRs = pStmt->executeQuery( "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL");
while(pRs->next()) {
strTemp = pRs->getString(1);
printf("db time:%s.\n", strTemp.c_str());
// int类型取值用getInt()
break;
}
pStmt->closeResultSet(pRs);
//--------插入---------
// 指定DML为自动提交
pStmt->setAutoCommit(TRUE);
// 设置执行的SQL语句
//CREATE TABLE TABLE_TEST_WANG(NAME varchar(7) NOT NULL , NUM int, AGE int);
pStmt->setSQL("INSERT INTO TABLE_TEST_WANG (NAME, NUM, AGE) VALUES ('胡歌', 13, 37)");
// 执行SQL语句
unsigned int nRet = pStmt->executeUpdate();
if(nRet == 0) {
printf("executeUpdate insert error.\n");
}
// 终止Statement对象
conn->terminateStatement(pStmt);
env->terminateConnection(conn);
}
catch(SQLException e){
cout<<e.what()<<endl;
}
Environment::terminateEnvironment(env);
cout<<"end!"<<endl;
return 0;
}
573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
