ORACLE数据库的嵌入SQL语言proc编程Word格式.docx
《ORACLE数据库的嵌入SQL语言proc编程Word格式.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库的嵌入SQL语言proc编程Word格式.docx（35页珍藏版）》请在冰点文库上搜索。
我们可以使用主机指示符变量来解决这个问题。
在嵌入式SQL语句中，主变量和指示符变量共同规定一个单独的SQL类型值。
指示符变量是一个2字节的整数。
针对输入宿主变量和输出宿主变量，指示变量共有下面几种情况：
同输入宿主变量一起使用时：
-1Oracle将null赋值给列，即宿主变量应该假设为NULL。
>
=0Oracle将宿主变量的实际值赋值给列。
同输出宿主变量一起使用时：
-1表示该列的输出值为NULL。
0Oracle已经将列的值赋给了宿主变量。
列值未做截断。
0Oracle将列的值截断，并赋给了宿主变量。
指示变量中存放了这个列的实际长度。
-2Oracle将列的值截断，并赋给了宿主变量。
但是这个列的实际长度不能确定。
从数据库中查询数据时，可以使用指示符变量来测试NULL：
EXECSQLSELECTename,sal
INTO:
emp_name,:
salary
FROMemp
WHERE:
commissionINDICATOR:
ind_commISNULL...
注意，不能使用关系操作符来比较NULL，这是因为NULL和任何操作都为false。
WHEREcomm=:
commission
如果comm列的某些行存在NULL，则该SELECT语句不能返回正确的结果。
应该使用下面这个语句完成：
WHERE（comm=:
commission）OR（（commISNULL）AND
（:
ind_commISNULL））;
1.2查询
如果是单行查询，则应该使用SELECTINTO语句。
如果是多行查询，应该使用游标或宿主变量数组。
单行查询的一个例子：
EXECSQLSELECTename,job,sal+2000
job_title,:
WHEREempno=:
emp_number;
在嵌入SQL语句中，也可以使用子查询。
EXECSQLINSERTINTOemp2（empno,ename,sal,deptno）
SELECTempno,ename,sal,deptnoFROMemp
WHEREjob=:
job_title;
1.3修改数据
1）、插入数据
使用INSERT语句插入数据。
其语法同ANSISQL语法类似。
EXECSQLINSERTINTOemp（empno,ename,sal,deptno）
salary,:
dept_number）;
2）、更新数据
使用UPDATE语句更新数据。
EXECSQLUPDATEemp
SETsal=:
salary,comm=:
commission
3）、删除数据
使用DELETE语句删除数据。
EXECSQLDELETEFROMemp
WHEREdeptno=:
dept_number;
1.4游标
用嵌入式SQL语句查询数据分成两类情况。
一类是单行结果，一类是多行结果。
对于单行结果，可以使用SELECTINTO语句；
对于多行结果，你必须使用游标来完成。
游标是一个与SELECT语句相关联的符号名，它使用户可逐行访问由ORACLE返回的结果集。
使用游标，应该包含以下四个步骤。
1）、定义游标
使用DECLARE语句完成。
EXECSQLDECLAREemp_cursorCURSORFOR
SELECTenameFROMempWHEREdeptno=:
值得注意的是，不能在同一个文件中定义两个相同名字的游标。
游标的作用范围是全局的。
2）、打开游标
使用OPEN语句完成。
EXECSQLOPENemp_cursor;
3）、取一行值
使用FETCH语句完成。
EXECSQLFETCHemp_cursorINTO:
emp_name;
4）、关闭游标
使用CLOSE语句完成。
它完成的功能是：
释放资源，如占用内存，锁等。
EXECSQLCLOSEemp_cursor;
5）、使用游标修改数据
我们可以使用CURRENTOF子句来完成修改数据。
SELECTename,salFROMempWHEREjob='
CLERK'
FORUPDATEOFsal;
...
EXECSQLWHENEVERNOTFOUNDGOTO...
for（;
;
）{
salary;
EXECSQLUPDATEempSETsal=:
new_salary
WHERECURRENTOFemp_cursor;
}
值得注意的是，在使用CURRENTOF子句来完成修改数据时，在OPEN时会对数据加上排它锁。
这个锁直到有COMMIT或ROLLBACK语句时才释放。
以下是使用游标修改数据的一个完整例子：
...
/*定义游标*/
SELECTename,job
emp_number
FORUPDATEOFjob;
/*打开游标*/
/*breakifthelastrowwasalreadyfetched*/
EXECSQLWHENEVERNOTFOUNDDObreak;
/*循环取值*/
）
{
/*更新当前游标所在的行的数据*/
SETjob=:
new_job_title
/*关闭游标*/
EXECSQLCOMMITWORKRELEASE;
下面这个例子完整演示了静态游标的使用方法。
这个例子的作用是，获得部门编号，通过游标来显示这个部门中的所有雇员信息。
#include<
stdio.h>
/*声明宿主变量*/
charuserid[12]="
SCOTT/TIGER"
charemp_name[10];
intdept_number;
chartemp[32];
voidsql_error（）;
/*包含SQLCA*/
sqlca.h>
main（）
{emp_number=7499;
/*处理错误*/
EXECSQLWHENEVERSQLERRORdosql_error（"
Oracleerror"
/*连接到Oracle数据库*/
EXECSQLCONNECT:
userid;
Connected.\n"
/*声明游标*/
Departmentnumber?
dept_number=atoi（temp）;
/*打开游标*/
EmployeeName\n"
-------------\n"
/*循环处理每一行数据，如果无数据，则退出*/
while
（1）
%s\n"
emp_name）;
exit（0）;
}
/错误处理程序*/
voidsql_error（msg）
char*msg;
{
charbuf[500];
intbuflen,msglen;
EXECSQLWHENEVERSQLERRORCONTINUE;
EXECSQLROLLBACKWORKRELEASE;
buflen=sizeof（buf）;
sqlglm（buf,&
buflen,&
msglen）;
msg）;
%*.s\n"
msglen,buf）;
exit
（1）;
2嵌入PL/SQL
嵌入PL/SQL和嵌入SQL不同。
嵌入PL/SQL提供了很多嵌入SQL不具有的优点，如：
更好的性能、更灵活的表达方式。
能够自己定义过程和函数。
PROCEDUREcreate_dept
（new_dnameINCHAR（14）,
new_locINCHAR（13）,
new_deptnoOUTNUMBER
（2））IS
BEGIN
SELECTdeptno_seq.NEXTVALINTOnew_deptnoFROMdual;
INSERTINTOdeptVALUES（new_deptno,new_dname,new_loc）;
ENDcreate_dept;
其中的IN/OUT，表示参数模式。
IN是传递参数值到过程，而OUT是从过程传递参数值到调用者。
但是，如果使用这些扩展的功能，也会造成同其他数据库厂商的嵌入SQL的不兼容。
3动态SQL语句
3.1ORACLE动态SQL语句的一些特点
ORACLEDBMS进入市场的时间早于DB2，其动态SQL支持是以IBM的system/R原型为基础的。
因此，ORACLE支持的动态SQL与IBM的DB2标准有不同。
虽然ORACLE和DB2在很大程度上是兼容的，但是在使用参数标志、SQLDA格式及支持数据类型转换等方面都有差异。
DB2中不允许在PREPARE的动态语句中引用宿主变量，而是用问号来标志语句中的参数，然后用EXECUTE或OPEN语句来规定参数值。
ORACLE允许用户用宿主变量规定动态语句中的参数。
而且，ORACLE支持的DESCRIBE语句同DB2有一些区别。
从已经PREPARE后的动态查询语句中获得对查询结果列的信息的语句为：
EXECSQLDESCRIBESELECTLISTFORqrystmtINTOqry_sqlda;
等价于DB2的：
EXECSQLDESCRIBEqrystmtINTOqry_sqlda;
从已经PREPARE后的动态查询语句中获得对查询参数的说明的语句为：
EXECSQLDESCRIBEBINDLISTFORqrystmtINTOqry_sqlda;
该ORACLE语句没有对应的DB2语句。
用户只能按照当前需要的参数和SQLDA的结构对SQLDA赋值。
然后再在OPEN语句或EXECUTE语句中使用SQLDA结构。
3.2使用动态SQL的四种方法
使用动态SQL，共分成四种方法：
方法支持的SQL语句
1该语句不包含宿主变量，该语句不是查询语句
2该语句包含输入宿主变量，该语句不是查询语句
3包含已知数目的输入宿主变量或列的查询
4包含未知数目的输入宿主变量或列的查询
l方法1：
使用EXECUTEIMMEDIATE命令实现，具体语法为：
EXECSQLEXECUTEIMMEDIATE{:
host_string|string_literal};
其中，host_variable和string是存放完整T-SQL语句。
请看下面这个例子。
这个例子的作用是执行用户随意输入的合法的SQL语句。
chardyn_stmt[132];
EnterSQLstatement:
gets（dyn_stmt）;
if（*dyn_stmt=='
\0'
break;
/*dyn_stmtnowcontainsthetextofaSQLstatement*/
EXECSQLEXECUTEIMMEDIATE:
dyn_stmt;
EXECUTEIMMEDIATE命令的作用是：
分析该语句的语法，然后执行该语句。
方法1适合于仅仅执行一次的语句。
l方法2：
方法支持的语句可以包含输入宿主变量。
这个语句首先做PREPARE操作，然后通过EXECUTE执行。
PREPARE语句的语法为：
EXECSQLPREPAREstatement_nameFROM{:
该语句接收含有SQL语句串的宿主变量，并把该语句送到ORACLE。
ORACLE编译语句并生成执行计划。
在语句串中包含一个“？
”表明参数，当执行语句时，ORACLE需要参数来替代这些“？
”。
PREPRARE执行的结果是，DBMS用语句名标志准备后的语句。
在执行SQL语句时，EXECUTE语句后面是这个语句名。
EXECUTE语句的语法为：
EXECUTE语句名USING宿主变量|DESCRIPTOR描述符名
它的作用是，请求ORACLE执行PREPARE语句准备好的语句。
当要执行的动态语句中包含一个或多个参数标志时，在EXECUTE语句必须为每一个参数提供值。
这样的话，EXECUTE语句用宿主变量值逐一代替准备语句中的参数标志（“？
”或其他占位符），从而，为动态执行语句提供了输入值。
使用主变量提供值，USING子句中的主变量数必须同动态语句中的参数标志数一致，而且每一个主变量的数据类型必须同相应参数所需的数据类型相一致。
各主变量也可以有一个伴随主变量的指示符变量。
当处理EXECUTE语句时，如果指示符变量包含一个负值，就把NULL值赋予相应的参数标志。
除了使用主变量为参数提供值，也可以通过SQLDA提供值。
这个例子的作用是删除用户指定的雇员信息。
intemp_numberINTEGER;
chardelete_stmt[120],search_cond[40];
strcpy（delete_stmt,"
DELETEFROMEMPWHEREEMPNO=:
nAND"
Completethefollowingstatement'
ssearchcondition--\n"
delete_stmt）;
gets（search_cond）;
strcat（delete_stmt,search_cond）;
EXECSQLPREPAREsql_stmtFROM:
delete_stmt;
Enteremployeenumber:
if（emp_number==0）
EXECSQLEXECUTEsql_stmtUSING:
l方法三：
是指查询的列数或输入宿主变量数在预编译时已经确定，但是数据库中的对象，如表、列名等信息未确定。
这些对象名不能是宿主变量。
这时，必须通过以下语句来完成：
PREPAREstatement_nameFROM{:
DECLAREcursor_nameCURSORFORstatement_name;
OPENcursor_name[USINGhost_variable_list];
FETCHcursor_nameINTOhost_variable_list;
CLOSEcursor_name;
下面这个例子演示用方法3完成动态查询：
charselect_stmt[132]=
"
SELECTMGR,JOBFROMEMPWHERESAL<
:
salary"
select_stmt;
EXECSQLDECLAREemp_cursorCURSORFORsql_stmt;
EXECSQLOPENemp_cursorUSING:
mgr_number,:
l方法四：
在预编译时，查询的列数或者宿主变量的个数不能确定，因为不知道具体的返回个数，所以不能使用输出宿主变量。
这是因为你不知道应该定义多少个宿主变量。
这时，就需要SQLDA结构和DESCRIBE命令。
SQLDA包含了动态查询的列描述信息。
对于输入宿主变量，也可以使用SQLDA来完成不确定的参数说明。
要完成方法四，必须通过以下语句来完成：
EXECSQLDECLAREcursor_nameCURSORFORstatement_name;
EXECSQLDESCRIBEBINDVARIABLESFORstatement_name
INTObind_descriptor_name;
EXECSQLOPENcursor_name
[USINGDESCRIPTORbind_descriptor_name];
EXECSQLDESCRIBE[SELECTLISTFOR]statement_name
INTOselect_descriptor_name;
EXECSQLFETCHcursor_nameUSINGDESCRIPTORselect_descriptor_name;
EXECSQLCLOSEcursor_name;
在上述语句中，DESCRIBESELECTLIST的作用是将PREPARE后的动态查询语句的列名、数据类型、长度等信息保存在SQLDA中。
DESCRIBEBINDVARIABLES的作用是，检查PREPARE后的动态查询语句的每个占位符的名字、数据类型、长度等信息。
并将它存放在SQLDA中，然后，使用SQLDA提示用户数据参数值。
值得注意的是，方法之间可以混合使用。
在一个查询中，列的个数确定，但是查询中的占位符不确定，这时，你可以结合方法3和方法4，即使用方法3的FETCH语句和方法4的OPEN语句，如：
EXECSQLFETCHemp_cursorINTOhost_variable_list;
反之，如果查询中占位符的个数确定，而列数不确定，则你可以使用方法3的OPEN语句，如：
EXECSQLOPENcursor_name[USINGhost_variable_list];
这里，我们讲解的是嵌入SQL，对于嵌入PL/SQL，有一些区别。
简单来说，主要有两点：
l预编译器将PL/SQL块中的所有宿主变量都作为输入宿主变量。
l不能对PL/SQL块使用FETCH命令。
l占位符不用声明，可以是任何名字。
INSERTINTOemp（empno,deptno）VALUES（:
e,:
d）
DELETEFROMdeptWHEREdeptno=:
numORloc=:
loc
其中的e、d、num和loc就是占位符。
升级会员 