proc_day2

一。proc中如何使用sql语句
    1.select语句 只要在语句前加exec sql,并且结合into使用
      exec sql select name into :v_name from t1 where id=10;
    
    2.dml（insert/delete/update）语句、 ddl (create/drop/alter) 、 tcl(commit/rollback/savepoint)
      直接在语句前加exec sql 即可。 
      注意：ddl中不能使用宿主变量。
       exec sql insert into t1 values(:v_id,:v_name);
       exec sql drop table t1;
       exec sql commit;
       
       
       例子：
         [nws@upright91 proc]$ cat sql.pc                                                                     
         #include<stdio.h>                                                                                    
         exec sql include sqlca;                                                                              
         int main()                                                                                           
         {                                                                                                    
          /* declare section */                                                                               
          exec sql begin declare section;                                                                     
            char userpasswd[30]="dhr/dhr";                                                                    
            int v_id=10;                                                                                      
            char v_name[30]="aa";                                                                             
          exec sql end declare section;                                                                       
                                                                                                              
          /* connect db */                                                                                    
          exec sql connect:userpasswd;                                                                        
                                                                                                     
          /* ddl */                                                                                           
          exec sql create table testproc(id number constraint testprocsql_id_pk primary key,name varchar(20));
          if(sqlca.sqlcode==0)                                                                                
          {                                                                                                   
           printf("create table success!\n");                                                                 
          }else {                                                                                             
           printf("%s\n",sqlca.sqlerrm.sqlerrmc);                                                             
          }                                                                                                   
                                                                                                     
          /* dml */                                                                                           
          exec sql insert into testproc values(:v_id,:v_name);                                                
          if(sqlca.sqlcode==0)                                                                                
          { printf("insert table success!\n");                                                                
          }else {                                                                                             
           printf("%s\n",sqlca.sqlerrm.sqlerrmc);}                                                            
                                                                                                     
          /* tcl */                                                                                           
          exec sql commit;                                                                                    
                                                                                                     
          /* dml */                                                                                           
          exec sql update testproc set name='bb' where id=:v_id;                                              
           if(sqlca.sqlcode==0)                                                                               
          { printf("update table success!\n");                                                                
          }else {                                                                                             
           printf("%s\n",sqlca.sqlerrm.sqlerrmc);}                                                            
          exec sql commit;                                                                                    
                                                                                                     
          /* tcl */                                                                                           
          exec sql commit;                                                                                    
                                                                                                     
          /* select */                                                                                        
          exec sql select name into :v_name from testproc where id=:v_id;                                     
          printf("v_name=%s\n",v_name);                                                                       
          /* disconnect db */                                                                                 
          exec sql commit work release;                                                                       
         }  
         
         
         运行结果：
          [nws@upright91 proc]$ ./sql                                                                                                  
          create table success!      
          insert table success!      
          update table success!      
          v_name=bb
          
二。proc中如何使用PLSQL
    1.使用PLSQL的语法
      exec sql execute
         begin
           /* 这里相当于PLSQL匿名块 */
         end;
      end-exec;
      
      在proc预编译时，需要使用两个选项
         sqlcheck=semantics
         userid=用户名/密码     在预编译时连接数据库，确认存储过程存在和合法
     
     2.proc中如何调用存储过程
       a.写一个存储过程，传入两个整数参数，然后把这两个整数参数的和存入第二个参数中。
       
           create or replace procedure getsum(v_id1 in  number,v_id2 in out number)
           as
           begin
           v_id2:=v_id1+v_id2;           
           end;
           /
          
         b. 写一个proc程序验证存储过程
             [nws@upright91 proc]$ cat callproc.pc 
             #include<stdio.h>
             exec sql include sqlca;
             int main()
             {
              exec sql begin declare section;
               char userpasswd[30]="dhr/dhr";
               int x=10;
               int y=20;
              exec sql end declare section;
             
              exec sql connect:userpasswd;
             
              exec sql execute 
                   begin
                     getsum(:x,:y); 
                   end;
              end-exec;
              printf("y=%d\n",y);
             
              exec sql commit work release;
             }
         
         [nws@upright91 proc]$ proc callproc.pc  SQLCHECK=SEMANTICS userid=dhr/dhr  
         [nws@upright91 proc]$ gcc -o callproc callproc.c -I${ORACLE_HOME}/rdbms/public -L${ORACLE_HOME}/lib -lclntsh
         [nws@upright91 proc]$ ./callproc                                                                                            
          y=30 
         
         
      3.proc中如何调用函数
        a.设计一个plsql的函数，传入两个整数参数，返回这两个整数参数的最大值     
         create or replace function getmax(x number,y number)
          return number
          as
          begin
           if(x>y) then
            return x;
           else
            return y;
           end if;
          end;
          /
        
        b.写一个proc程序验证
          #include<stdio.h>               
          exec sql include sqlca;         
          int main()                      
          {                                                                                                                                         
           exec sql begin declare section;
            char userpasswd[30]="dhr/dhr";
            int x=10;                     
            int y=20;                     
            int z=0;                      
           exec sql end declare section;  
           exec sql connect:userpasswd;   
           exec sql execute               
             begin                        
              :z := getmax(:x,:y);        
             end;                         
           end-exec;                      
            printf("z=%d\n",z);           
           exec sql commit work release;  
          }  
          
          [nws@upright91 proc]$ proc callfun.pc SQLCHECK=SEMANTICS userid=dhr/dhr


          Pro*C/C++: Release 11.2.0.1.0 - Production on Fri Aug 5 13:25:40 2016
          
          Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
          
          System default option values taken from: /home/nws/oracle/product/11.2.0/dbhome_1/precomp/admin/pcscfg.cfg
          
          [nws@upright91 proc]$ gcc -o callfun callfun.c -I${ORACLE_HOME}/rdbms/lib -L${ORACLE_HOME}/lib -lclntsh
          [nws@upright91 proc]$ ./callfun 
          z=20
          
三。数据库连接
    1.本地数据库连接
      exec sql connect:user/passwd;
      exec sql connect:username identified by :password; 
    2.远程数据库连接
      $ORACLE_HOME/network/admin/tnsnames.ora 
      exec sql connect:userpasswd using :rdbms_dns;
      exec sql connect:username identified by :password using :rdbms_dns;
    3.使用using :远程数据库描述  就可以区分不同的数据库
      如果有多个数据库连接时，需要使用 at:标签 来区分具体的连接
      只要在每个数据库操作前加上exec sql at:标签名 即可。   
      
      
      
      例子：                                                                   
       #include<stdio.h>                                                 
       exec sql include sqlca;                                                                      
       int main()                                                        
       {                                                                 
        exec sql begin declare section;                                  
          char userpasswd[30]="dhr/dhr";                                 
          char rdbdns[30]="ora92";                                       
          char labdb91[20]="db91";                                       
          char labdb92[20]="db92";                                       
          char v_name[30];                                               
        exec sql end declare section;                                    
        exec sql connect:userpasswd at:labdb92 using :rdbdns;            
        exec sql connect:userpasswd at:labdb91;                          
                                                                  
        exec sql at:labdb92 select name into :v_name from t1 where id=10;
        printf("db92 v_name=%s\n",v_name);                               
                                                                  
        exec sql at:labdb91 select name into :v_name from t1 where id=20;
        printf("db91 v_name=%s\n",v_name);                               
                                                                  
        exec sql at:labdb91 commit work release;                         
        exec sql at:labdb92 commit work release;                         
       } 
       
       
      4.使用database link 进行远程数据库连接
         create database link 链接名 connect to username identified by password using '远程数据库描述'; 
         
         a.在sqlplus中建立91到92的远程连接 
           create database link my91_92link connect to dhr ientified by 'dhr' using 'ora92';
           
         b.
           select ename into :v_name from t1@my91_92link;
         
         总结：必须先登录本地数据库，然后才能使用数据库中的链接。链接可以建立多个。
               远程数据的事务交给本地数据库来控制。
               
               
               
四。proc中的异常处理
   1.局部错误处理
    sqlca.sqlcode 来判断sql语句的执行状态。
    局部的错误处理
   
   2.proc中的全局错误处理方式
    
    exec sql whenever 条件  动作
    条件：sqlerror  、 notfound、 sqlwarning
    动作：do 错误处理函数名();
          do break; 终止循环
          continue;
          stop;
          goto 标签;
          
    3.举例
      把emp表删除 
      总结：proc中SQL出错之后，向上查找  exec sql whenever 语句，如果找到就执行whenever语句对应的动作。
            如果找不到就采用系统默认的忽略处理。
      [nws@upright91 proc]$ cat error.pc
       #include<stdio.h>
       exec sql include sqlca;
       void processError()
       {
        printf("%s\n",sqlca.sqlerrm.sqlerrmc);
       }
       int main()
       {
        exec sql begin declare section;
         char userpasswd[30]="dhr/dhr";
        exec sql end declare section;
        exec sql whenever sqlerror do processError();
        exec sql connect:userpasswd;
        exec sql drop table t1;
        printf("app continue!\n");
        exec sql commit work release;
       }
             
      
      [nws@upright91 proc]$ ./error 
       ORA-00942: table or view does not exist
                              
       app continue!
       
        ***********************************************
       
       [nws@upright91 proc]$ cat error.pc 
       #include<stdio.h>
       exec sql include sqlca;
       void processError()
       {
        printf("%s\n",sqlca.sqlerrm.sqlerrmc);
        exec sql drop table t1;
       }
       void pnotfound()
       {
        exec sql whenever notfound continue;
        printf("%s\n",sqlca.sqlerrm.sqlerrmc);
       }
       int main()
       {
        exec sql begin declare section;
         char userpasswd[30]="dhr/dhr";
         char v_name[30];
        exec sql end declare section;
        exec sql whenever sqlerror do processError();
        exec sql whenever notfound do pnotfound();
        exec sql connect:userpasswd;
        exec sql drop table t1;
        printf("app continue!\n");
        exec sql select name into :v_name from t2 where id=10;
        exec sql commit work release;
       }
       
       运行结果：
       [nws@upright91 proc]$ ./error 
       ORA-00942: table or view does not exist
                                     
       app continue!
       ORA-01403: no data found 
       
五。proc中的数据处理
    1.使用单个变量处理 单行单列
       exec sql select col_name into :v_col from table where 条件;
    2.使用多个变量，一次操作单行多列
       exec sql select name,id into :v_name,:v_id from emp where deptno=10;
    3.使用结构体操作 单行多列
      strunct{
        int id;
        char name[30];
        double sal;
      }emp;  
      
      typedef strunct emp{
        int id;
        char name[30];
        double sal;
      }emp_type;
      
      
      例子：      
      [nws@upright91 proc]$ cat struct.pc
       #include<stdio.h>
       exec sql include sqlca;
       int main()
       {
        exec sql begin declare section;
          char userpasswd[30]="dhr/dhr";
          struct{
           int id;
           char name[30];
           double salary;
         }emp;
        exec sql end declare section;
        exec sql connect:userpasswd;
        exec sql select empno,ename,sal into :emp from emp where empno=7902;
        printf("ID=%d,name=%s,sal=%lf",emp.id,emp.name,emp.salary);
        exec sql commit work release;
       }
      
      [nws@upright91 proc]$ ./strunct 
      ID=7902,name=FORD                         ,sal=3000.000000
      
      
     4.结合结构体和数组，把EMP表中所有EMPNO,ENAME,SAL放入一个结构体数组中
     
        #include<stdio.h>                                                             
        exec sql include sqlca;                                                       
        int main()                                                                    
        {                                                                             
         exec sql begin declare section;                                              
          char userpasswd[30]="dhr/dhr";                                                 
          struct emp{                                                                    
           int id;                                                                              
           char name[30];                                                                       
           double salary;                                                                      
          };                                                                                                                                                
          struct emp emps[50];                                                        
         exec sql end declare section;                                                
         exec sql connect:userpasswd;                                                 
         exec sql create table temptest(id number,name varchar(30),salary number);    
         if(sqlca.sqlcode==0)                                                         
         {                                                                            
          printf("create table successful\n");                                        
         }else{                                                                       
          printf("%s\n",sqlca.sqlerrm.sqlerrmc);                                      
         }                                                                            
         exec sql select empno,ename,sal into :emps from emp ;                        
         int i=0;                                                                     
         int datacount=sqlca.sqlerrd[2];                                              
         for (i=0;i<datacount;i++)                                                    
          {                                                                           
           exec sql insert into temptest values(:emps[i]);                            
           //printf("id=%d,name=%s,sal=%lf\n",emps.id[i],emps.name[i],emps.salary[i]);
          }                                                                           
                                                                                      
         exec sql commit;                                                             
         exec sql commit work release;                                                
        }  
        
        
       5.使用游标操作，多行多列
       （1）游标的使用步骤
           a.声明游标
             exec sql declare 游标名 cursor for select 语句;
           b.打开游标
             exec sql open 游标名;             
           c.提取数据
             exec sql fetch 游标名 into 宿主变量;
           d.关闭游标
             exec sql close 游标名;
         （2）.例子：把EMP表中所有ID ,NAME ,SAL放入游标中。
             [nws@upright91 proc]$ cat cursorloop.pc
              #include<stdio.h>
              exec sql include sqlca;
              int main()
              {
               exec sql begin declare section;
                char userpasswd[30]="dhr/dhr";
                struct{
                   int id;
                   char name[30];
                   double sal;
                }emp;
               exec sql end declare section;
               exec sql connect:userpasswd;
               exec sql declare cur_emp cursor for select empno,ename,sal from emp;
               exec sql open cur_emp;
               exec sql whenever notfound do break; 
               for(;;){
               exec sql fetch cur_emp into :emp;
               printf("id=%d,name=%s,sal=%lf\n",emp.id,emp.name,emp.sal);
               }
               exec sql close cur_emp;
               exec sql commit work release;
              }
              
              
              [nws@upright91 proc]$ ./cursorloop 
              id=7369,name=SMITH                        ,sal=800.000000
              id=7499,name=ALLEN                        ,sal=1600.000000
              id=7521,name=WARD                         ,sal=1250.000000
              id=7566,name=JONES                        ,sal=2975.000000
              id=7654,name=MARTIN                       ,sal=1250.000000
              id=7698,name=BLAKE                        ,sal=2850.000000
              id=7782,name=CLARK                        ,sal=2450.000000
              id=7788,name=SCOTT                        ,sal=3000.000000
              id=7839,name=KING                         ,sal=5000.000000
              id=7844,name=TURNER                       ,sal=1500.000000
              id=7876,name=ADAMS                        ,sal=1100.000000
              id=7900,name=JAMES                        ,sal=950.000000
              id=7902,name=FORD                         ,sal=3000.000000
              id=7934,name=MILLER                       ,sal=1300.000000 
              
              
          （3）滚动游标
               可以不按照顺序，提取数据
               scroll
               fetch last 最后一条数据
               fetch first 第一条数据
               fetch prior 当前的前一行数据
               fetch next 当前的后一行
               fetch relative n 正数向后，负数向前
               fetch absolute n 绝对的第几条
               fetch current  当前数据
               
               
               [nws@upright91 proc]$ cat cursorloop.pc
                #include<stdio.h>
                exec sql include sqlca;
                int main()
                {
                 exec sql begin declare section;
                  char userpasswd[30]="dhr/dhr";
                  struct{
                     int id;
                     char name[30];
                     double sal;
                  }emp;
                 exec sql end declare section;
                 exec sql connect:userpasswd;
                 exec sql declare cur_emp  scroll cursor for select empno,ename,sal from emp;
                 exec sql open cur_emp;
                 exec sql fetch last cur_emp into :emp;
                 printf("id=%d,name=%s,sal=%lf\n",emp.id,emp.name,emp.sal);
                 exec sql fetch first cur_emp into :emp;
                 printf("id=%d,name=%s,sal=%lf\n",emp.id,emp.name,emp.sal);
                 exec sql close cur_emp;
                 exec sql commit work release;
                } 
                
                
                [nws@upright91 proc]$ ./cursorloop 
                 id=7934,name=MILLER                       ,sal=1300.000000
                 id=7369,name=SMITH                        ,sal=800.000000
                 
六。动态SQL
   1.不能是select语句
      不能有占位符
       exec sql execute immediate  :sqlstr;
       
       例子：
           [nws@upright91 proc]$ cat dsqla.pc                                                                                           
           #include<stdio.h>                                                     
           exec sql include sqlca;                                               
           int main()                                                            
           {                                                                     
            exec sql begin declare section;                                      
             char userpasswd[30]="dhr/dhr";                                      
             char sqlstr[100];                                                   
            exec sql end declare section;                                        
            exec sql connect:userpasswd;                                         
            sprintf(sqlstr,"%s","create table test(\    //续行符
              id number,name varchar(10))");
            exec sql execute immediate :sqlstr;                                  
            if(sqlca.sqlcode==0){                                                
             printf("create table successfule!\n");                              
            }else{                                                               
             printf("%s\n",sqlca.sqlerrm.sqlerrmc);                              
            }                                                                    
            exec sql commit work release;                                        
           }
           
      2.可以有占位符  :b0 
        不能是select语句
        /*预处理*/
        exec sql prepare s from :sqlstr;
        exec sql execute s using :宿主变量;
        
        [nws@upright91 proc]$ cat dsqlb.pc
        #include<stdio.h>
        exec sql include sqlca;
        int main()
        {
         exec sql begin declare section;
          char userpasswd[30]="dhr/dhr";
          char sqlstr[100];
          int id=100; 
          char name[10]="test100";
         exec sql end declare section;
         exec sql connect:userpasswd;
         sprintf(sqlstr,"%s","insert into test values(:b0,:b1)");
         exec sql prepare s from :sqlstr;
         exec sql execute s using :id,:name;
         if(sqlca.sqlcode==0){
          printf("insert table success!\n");
         }else{
          printf("%s\n",sqlca.sqlerrm.sqlerrmc);
         }
         exec sql commit;
         exec sql commit work release;
        }
                
         
         验证数据       
         SQL> select * from test;
        
                ID NAME
        ---------- ------------------------------
               100 test100 
               
      3.只能是select语句
        可以有占位符
        "select id,name ,sal from emp where id>:b0"; 
        动态sql2+游标的形式
        核心思想 把预处理的串，关联到游标上
        
        
        [nws@upright91 proc]$ cat dsqlc.pc                                                                                                        
        #include<stdio.h>                                                       
        exec sql include sqlca;                                                 
        int main()                                                              
        {                                                                       
         exec sql begin declare section;                                        
          char userpasswd[30]="dhr/dhr";                                        
          char sqlstr[100];                                                     
          /* define a struct */                                                 
          struct {                                                              
           int id;                                                              
           char name[30];                                                       
           double sal;                                                          
          }emp;                                                                 
          int id=7900;                                                          
         exec sql end declare section;                                          
         exec sql connect:userpasswd;                                           
         sprintf(sqlstr,"%s","select empno,ename,sal from emp where empno>:b0");
         exec sql prepare s from :sqlstr;                                       
         /* 声明游标时关联到s */                                                
         exec sql declare empcursor cursor for s ;                              
         /* 打开游标时传参 */                                                   
         exec sql open empcursor using :id;                                     
         exec sql whenever notfound do break;                                   
         while(1)                                                               
         {                                                                      
           exec sql fetch empcursor into :emp;                                  
           printf("id=%d,name=%s,sal=%lf\n",emp.id,emp.name,emp.sal);           
         }                                                                      
         exec sql close empcursor;                                              
         exec sql commit work release;                                          
        } 
        
        
        运行结果
        [nws@upright91 proc]$ ./dsqlc                                                                                                   
        id=7902,name=FORD                         ,sal=3000.000000
        id=7934,name=MILLER                       ,sal=1300.000000