1. proc中如何使用plsql 1.1 使用plsql的语法 exec sql execute begin /* 相当于plsql的匿名块 */ end; end-exec; 在预编译时,需要加如下两个选项: sqlcheck = semantics userid = 用户名/密码:在预编译时时,检查调用的过程、函数等子程序是否存在及合法 1.2 在proc中调用存储过程 1) 创建一个存储过程,传入两个参数,把两数之和存入第二个参数 create or replace procedure getsum_zsm_00( var_a number,var_b in out number) is begin var_b:=var_a + var_b; end; 2) 在proc中调用存储过程 案例:callproc.pc
#include <stdio.h> exec sql include sqlca; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; int x = 1; int y = 9526; exec sql end declare section; exec sql connect:userpasswd; exec sql execute begin getsum_zsm_00(:x,:y); end; end-exec; exec sql commit work release; printf("%d\n",y); return 0; }
1.3 在proc中使用函数 1) 创建一个函数,传入两个数字,返回最大值 create or replace function getmax_zsm_00( var_a number,var_b number) return number is begin if var_a > var_b then return var_a; end if; return var_b; end; 2) 编写一个proc程序,调用该函数 案例:callfunc.pc
#include <stdio.h> exec sql include sqlca; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; int x = 1; int y = 9526; int max=0; exec sql end declare section; exec sql connect:userpasswd; exec sql execute begin :max:=getmax_zsm_00(:x,:y); end; end-exec; exec sql commit work release; printf("%d\n",max); return 0; }
2. 连接数据库 2.1 本地数据库连接 exec sql connect:用户名/密码; exec sql connect:用户名 identified by :密码; 2.2 远程连接数据库 需要提供一个远程连接的描述字符串,包括:ip地址、端口号、oracle数据库的服务ID(服务器名) 这个字符串有固定格式,程序员可以根据格式拼接字符串 a文件中,一般有远程连接的$ORACLE_HOME/network/admin/tnsnames.or描述字符串的配置 $ORACLE_HOME:环境变量,oracle的安装路径 ip地址是要访问的数据库所在的服务器的ip地址 端口号默认1521 有了远程数据库的描述字符串,使用using引入描述: exec sql connect:用户名/密码 using :描述字符串; 案例:rdbconn.pc
#include <stdio.h> exec sql include sqlca; int main(void){ exec sql begin declare section; char userpasswd[30]="openlab/open123"; // char rdbdesc[20] = "DB10"; char rdbdesc[200] = "(DESCRIPTION =" "(ADDRESS = (PROTOCOL = TCP)(HOST = 172.60.5.80)(PORT = 1521))" "(CONNECT_DATA =" "(SERVER = DEDICATED)" "(SERVICE_NAME = orcl)" ")" ")"; char name[25]; exec sql end declare section; exec sql connect:userpasswd using :rdbdesc; if(sqlca.sqlcode) printf("%s\n",sqlca.sqlerrm.sqlerrmc); exec sql select first_name into :name from s_emp where id=1; exec sql commit work release; printf("%s\n",name); return 0; }
如果面对的是多个数据库,可以他通过定义标签,然后使用标签区分访问的是哪个数据库。
案例:mdbconn.pc
#include <stdio.h> exec sql include sqlca; int main(void) { exec sql begin declare section; char userpasswd80[30]="openlab/open123"; char userpasswd81[30]="openlab/open123"; char rdbdesc[20]="DB10"; char name80[25]; char name81[25]; char label80[10]="80"; char label81[10]="81"; exec sql end declare section; exec sql connect:userpasswd81 at :label81; exec sql connect:userpasswd80 at :label80 using :rdbdesc; exec sql at :label80 select first_name into :name80 from s_emp where id=1; exec sql at :label81 select first_name into :name81 from s_emp where id=1; exec sql at :label80 commit work release; exec sql at :label81 commit work release; printf("172.60.5.80:%s\n",name80); printf("172.60.5.81:%s\n",name81); return 0; }
3. proc中的错误处理 3.1 局部的错误处理方式 sqlca.sqlcode //错误码 sqlca.sqlerrm.sqlerrmc //错误信息 3.2 全局的错误处理方式 错误处理方案只写一次,出现匹配的类型的错误,自动查找错误处理方案并执行 exec sql whenever 条件 动作; 条件就是错误的方式,有三种: sqlerror: sql语句错误 notfound: 没有找到数据 sqlwarning: sql语句出现警告(不常用) 动作就是错误处理方案,包括: do 错误处理函数(): 条件发生后,调用错误处理函数 do break: 退出循环 continue: 继续 stop: 停止执行代码(不常用) goto 标签: 跳转到标签处(不常用) 标签: < <标签名> > 3.3 举例:删除一张不存在的表 案例:sqlerror.pc
#include <stdio.h> exec sql include sqlca; void sqlerror() { printf("sqlerror:%s\n",sqlca.sqlerrm.sqlerrmc); } void notfound() { printf("notfound:%s\n",sqlca.sqlerrm.sqlerrmc); } int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; char name[25]; int id=100; exec sql end declare section; exec sql whenever sqlerror do sqlerror(); exec sql whenever notfound do notfound(); exec sql connect:userpasswd; exec sql drop table tablename; exec sql select first_name into :name from s_emp where id=:id; exec sql commit work release; return 0; }
proc中默认对错误是忽略的(不提示、不中断) sql语句出现错误,会向上查找对应的全局处理语句,如果有,执行处理方案。如果没有,继续执行后面的代码。 4. proc中的数据处理 1) 单行单列的结果集 使用一个宿主变量解决 .... char name[25]; ..... exec sql select first_name into :name from s_emp where id=1; ..... 2) 单行多列的结果集 使用多个宿主变量 或 结构体变量 解决 exec sql select id,first_name,salary into :id,:name,:sal from s_emp where id=1; 案例:selecta.pc
#include <stdio.h> exec sql include sqlca; struct emp{ int id; char name[25]; double sal; }; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; struct emp e; exec sql end declare section; exec sql connect:userpasswd; exec sql select id,first_name,salary into :e from s_emp where id=1; exec sql commit work release; printf("%d %s %g\n",e.id,e.name,e.sal); return 0; }
3) 多行单列的结果集 使用一个数组解决 exec sql select first_name into :names from s_emp; 4) 多行多列的结果集 使用多个数组、结构体数组、游标解决 exec sql select id,name,region_id into :ids,:names,:rids from s_dept; 练习:使用多个数组接收s_dept表中的全部信息,并循环输出。 添加必要的错误处理 案例:selectb.pc
#include <stdio.h> exec sql include sqlca; struct emp{ int id; char name[25]; double sal; }; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; struct emp emps[50]; exec sql end declare section; exec sql connect:userpasswd; exec sql select id,first_name,salary into :emps from s_emp; exec sql commit work release; int i=0; for(;i<sqlca.sqlerrd[2];i++) { printf("%d %s %g\n",emps[i].id,emps[i].name, emps[i].sal); } return 0; }
在proc中使用游标的步骤: a.声明游标 exec sql declare 游标名 cursor for select语句; b. 打开游标 exec sql open 游标名; c. 提取数据 exec sql fetch 游标名 into :宿主变量; d.关闭游标 exec sql close 游标名; 使用游标时,结束循环采用: exec sql whenever notfound do break; 案例:selectc.pc
#include <stdio.h> exec sql include sqlca; struct emp{ int id; char name[25]; double sal; }; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; struct emp e; exec sql end declare section; exec sql connect:userpasswd; exec sql declare empcursor cursor for select id,first_name,salary from s_emp; exec sql open empcursor; exec sql whenever notfound do break; while(1) { exec sql fetch empcursor into :e; printf("%d %s %g\n",e.id,e.name,e.sal); } exec sql close empcursor; exec sql commit work release; return 0; }
有一种特殊的游标,叫做滚动游标。滚动游标可以直接调到任何一行。在用法上和普通游标(顺序游标)的区别: a. 声明时,滚动游标关键字是scroll cursor exec sql declare 游标名 scroll cursor for select语句; b. 提取数据时,滚动游标有多种方式: fetch first 提取第一行 fetch last 提取最后一行 fetch next 提取下一行 fetch prior 提取上一行 fetch absolute n: 提取第n行 fetch relative n 提取从当前位置开始的第n行 fetch current 提取当前行 exec sql fetch first 游标名 into :宿主变量 案例:cp selectc.pc scrollcursor.pc
#include <stdio.h> exec sql include sqlca; struct emp{ int id; char name[25]; double sal; }; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; struct emp e; exec sql end declare section; exec sql connect:userpasswd; exec sql declare empcursor scroll cursor for select id,first_name,salary from s_emp; exec sql open empcursor; exec sql fetch absolute 10 empcursor into :e; printf("%d %s %g\n",e.id,e.name,e.sal); exec sql fetch next empcursor into :e; printf("%d %s %g\n",e.id,e.name,e.sal); exec sql fetch relative -5 empcursor into :e; printf("%d %s %g\n",e.id,e.name,e.sal); exec sql fetch last empcursor into :e; printf("%d %s %g\n",e.id,e.name,e.sal); exec sql close empcursor; exec sql commit work release; return 0; }
5.动态sql sql在执行时,才由参数传入 1) 非select语句,没有占位符 char sqlstr[100]="create table...."; exec sql execue immediate :sqlstr; 案例:dsqla.pc 2) 非select语句,有占位符 char sqlstr[100]="insert into.....values(:b0,:b1...)"; exec sql prepare stmt from :sqlstr; exec sql execute stmt using :宿主变量....; 案例:dsqlb.pc
#include <stdio.h> #include <string.h> exec sql include sqlca; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; char sqlstr[100]; int id=1001; char name[20]="Tarena"; exec sql end declare section; exec sql connect:userpasswd; strcpy(sqlstr, "insert into testdsql_zsm_00 values(:b0,:b1)"); exec sql prepare s from :sqlstr; exec sql execute s using :id,:name; exec sql commit work release; return 0; }
3) select语句 char sqlstr[100]="select .... from ... where id>:b0"; exec sql prepare stmt from :sqlstr; exec sql delcare 游标名 cursor for stmt; .... 案例:dsqlc.pc
#include <stdio.h> #include <string.h> exec sql include sqlca; struct emp{ int id; char name[25]; double sal; }; int main(void) { exec sql begin declare section; char userpasswd[30]="openlab/open123"; char sqlstr[100]; struct emp e; int id=10; exec sql end declare section; exec sql connect:userpasswd; strcpy(sqlstr, "select id,first_name,salary from s_emp where id>:b0"); exec sql prepare s from :sqlstr; 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 :e; printf("%d %s %g\n",e.id,e.name,e.sal); } exec sql close empcursor; exec sql commit work release; return 0; }
---------------------------------------------------------------------- 总结: 1. sql select select..from 表达式 别名 null值: nvl where子句 比较运算符 between and in like _ % escape '\' is null 逻辑运算符:and or not order by 子句 order by 排序标准 排序方式,排序标准 排序方式 group by子句 select后的字段必须是分组标准,或者是组函数 having子句 分组后的筛选 单行函数 to_date to_char 组函数 表连接 select 字段列表 from 表1,表2 where 关联条件 [and 筛选条件]; (+):oracle中外连接 select 字段列表 from 表1 {{left|right|full} [outer] | [inner]} join 表2 on 关联条件 [where 筛选条件]; 子查询 where select having from ddl: table sequence view index dml insert delete update tcl ACID: 约束 分页 2. plsql 变量 数据类型: record、table、 ref cursor 表名.字段%type 表名%rowtype 流程控制 动态sql 游标 异常 有名块 过程 函数 包 触发器 3.proc 宿主变量 指示变量 数组变量 通信区 嵌入sql、plsql语句 连接数据库 错误处理 select结果集的处理 动态sql ---------------------------------------------------------------------