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[]="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[]="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[]="openlab/open123";
// char rdbdesc[] = "DB10";
char rdbdesc[] = "(DESCRIPTION ="
"(ADDRESS = (PROTOCOL = TCP)(HOST = 172.60.5.80)(PORT = 1521))"
"(CONNECT_DATA ="
"(SERVER = DEDICATED)"
"(SERVICE_NAME = orcl)"
")"
")";
char name[];
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[]="openlab/open123";
char userpasswd81[]="openlab/open123";
char rdbdesc[]="DB10";
char name80[];
char name81[];
char label80[]="80";
char label81[]="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[]="openlab/open123";
char name[];
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[];
.....
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[];
double sal;
};
int main(void)
{
exec sql begin declare section;
char userpasswd[]="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[];
double sal;
};
int main(void)
{
exec sql begin declare section;
char userpasswd[]="openlab/open123";
struct emp emps[];
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[];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[];
double sal;
};
int main(void)
{
exec sql begin declare section;
char userpasswd[]="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[];
double sal;
};
int main(void)
{
exec sql begin declare section;
char userpasswd[]="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[]="create table....";
exec sql execue immediate :sqlstr;
案例：dsqla.pc
2) 非select语句，有占位符
char sqlstr[]="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[]="openlab/open123";
char sqlstr[];
int id=1001;
char name[]="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[]="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[];
double sal;
};
int main(void)
{
exec sql begin declare section;
char userpasswd[]="openlab/open123";
char sqlstr[];
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
---------------------------------------------------------------------