oracle pro*c中调用PL/SQL存储过程 - wangxuwei的个人空间

相关文章推荐
俊秀的瀑布 · 富文本编辑器 quill.js ...· 1 年前 ·
打篮球的拐杖 · 用div画横线_div ...· 1 年前 ·
刚分手的茶壶 · uiautomator 原理 ...· 1 年前 ·
力能扛鼎的烤面包 · 为容器和 Pods 分配 CPU 资源 | ...· 1 年前 ·
快乐的马铃薯 · Tomcat 源码分析 ...· 1 年前 ·
测试环境：
服务器：docker中的oracle服务器，服务名为xe，参考 unbuntu下Docker安装oracle和mysql
客户端：instantclient客户端，参考打造ubuntu下精简版的oracle客户端及pro*c编译环境
在/opt/oracle/product/network/admin/tnsnames.ora配置服务名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; 
            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-EXEC;
      print_rows(num_ret);
      if (done_flag)
         break;
/* Disconnect from the database. */
   EXEC SQL COMMIT WORK RELEASE;
   exit(0);
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. */
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
系统默认选项值取自于:  /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
........................................... 
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!!
       
        // g++-4.8 -I${ORACLE_HOME}/sdk/include -o OCCIConnect OCCIConnect.cpp -L${ORACLE_HOME}/lib -lclntsh -locci
#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;