ORACLE数据库的嵌入SQL语言proc编程.docx

1、ORACLE数据库的嵌入SQL语言proc编程ORACLE数据库的嵌入SQL语言1 基本的SQL语句1.1宿主变量和指示符1）、声明方法同其他数据库管理器一样，ORACLE使用宿主变量传递数据库中的数据和状态信息到应用程序，应用程序也通过宿主变量传递数据到ORACLE数据库。根据上面两种功能，宿主变量分为输出宿主变量和输入宿主变量。在SELECT INTO和FETCH语句之后的宿主变量称作“输出宿主变量”，这是因为从数据库传递列数据到应用程序。除了SELECT INTO和FETCH语句外的其他SQL语句中的宿主变量，称为“输入宿主变量”。这是因为从应用程序向数据库输入值。如：INSERT、UP

2、DATE等语句。请看下面这个例子：int emp_number; char temp20;VARCHAR emp_name20; /* get values for input host variables */ printf(Employee number? ); gets(temp);emp_number = atoi(temp);printf(Employee name? ); gets(emp_name.arr); emp_name.len = strlen(emp_name.arr); EXEC SQL INSERT INTO EMP (EMPNO, ENAME) VALUES (:

3、emp_number, :emp_name); 在上面这个例子中，其中的emp_number和emp_name就是宿主变量。值得注意的是，它同其他数据库的区别是，定义宿主变量可以不需要BEGIN DECLARE SECTION和END DECLARE SECTION。2）、指示符变量大多数程序设计语言（如C）都不支持NULL。所以对NULL的处理，一定要在SQL中完成。我们可以使用主机指示符变量来解决这个问题。在嵌入式SQL语句中，主变量和指示符变量共同规定一个单独的SQL类型值。指示符变量是一个2字节的整数。针对输入宿主变量和输出宿主变量，指示变量共有下面几种情况：同输入宿主变量一起使用时：

4、-1 Oracle将null赋值给列，即宿主变量应该假设为NULL。 =0 Oracle将宿主变量的实际值赋值给列。同输出宿主变量一起使用时：-1 表示该列的输出值为NULL。 0 Oracle已经将列的值赋给了宿主变量。列值未做截断。 0 Oracle将列的值截断，并赋给了宿主变量。指示变量中存放了这个列的实际长度。 -2 Oracle将列的值截断，并赋给了宿主变量。但是这个列的实际长度不能确定。 从数据库中查询数据时，可以使用指示符变量来测试NULL：EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE :com

5、mission INDICATOR :ind_comm IS NULL . 注意，不能使用关系操作符来比较NULL，这是因为NULL和任何操作都为false。如：EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE comm = :commission如果comm列的某些行存在NULL，则该SELECT语句不能返回正确的结果。应该使用下面这个语句完成：EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE (comm = :commissio

6、n) OR (comm IS NULL) AND (:commission INDICATOR :ind_comm IS NULL);1.2 查询如果是单行查询，则应该使用SELECT INTO语句。如果是多行查询，应该使用游标或宿主变量数组。如：单行查询的一个例子：EXEC SQL SELECT ename, job, sal + 2000 INTO :emp_name, :job_title, :salary FROM emp WHERE empno = :emp_number; 在嵌入SQL语句中，也可以使用子查询。如：EXEC SQL INSERT INTO emp2 (empno,

7、ename, sal, deptno) SELECT empno, ename, sal, deptno FROM emp WHERE job = :job_title; 1.3 修改数据1）、插入数据使用INSERT语句插入数据。其语法同ANSI SQL语法类似。如：EXEC SQL INSERT INTO emp (empno, ename, sal, deptno) VALUES (:emp_number, :emp_name, :salary, :dept_number); 2）、更新数据使用UPDATE语句更新数据。其语法同ANSI SQL语法类似。如：EXEC SQL UPDATE

8、 emp SET sal = :salary, comm = :commission WHERE empno = :emp_number; 3）、删除数据使用DELETE语句删除数据。其语法同ANSI SQL语法类似。如：EXEC SQL DELETE FROM emp WHERE deptno = :dept_number; 1.4 游标用嵌入式SQL语句查询数据分成两类情况。一类是单行结果，一类是多行结果。对于单行结果，可以使用SELECT INTO语句；对于多行结果，你必须使用游标来完成。游标是一个与SELECT语句相关联的符号名，它使用户可逐行访问由ORACLE返回的结果集。使用游标，

9、应该包含以下四个步骤。1）、定义游标使用DECLARE语句完成。如：EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename FROM emp WHERE deptno = :dept_number;值得注意的是，不能在同一个文件中定义两个相同名字的游标。游标的作用范围是全局的。2）、打开游标使用OPEN语句完成。如：EXEC SQL OPEN emp_cursor; 3）、取一行值使用FETCH语句完成。如：EXEC SQL FETCH emp_cursor INTO :emp_name;4）、关闭游标使用CLOSE语句完成。它完成的功能是：释放

10、资源，如占用内存，锁等。如：EXEC SQL CLOSE emp_cursor; 5）、使用游标修改数据我们可以使用CURRENT OF子句来完成修改数据。如：EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, sal FROM emp WHERE job = CLERK FOR UPDATE OF sal; . EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND GOTO . for (;) EXEC SQL FETCH emp_cursor INTO :emp_name, :sal

11、ary; . EXEC SQL UPDATE emp SET sal = :new_salary WHERE CURRENT OF emp_cursor; 值得注意的是，在使用CURRENT OF子句来完成修改数据时，在OPEN时会对数据加上排它锁。这个锁直到有COMMIT或ROLLBACK语句时才释放。以下是使用游标修改数据的一个完整例子：./* 定义游标 */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, job FROM emp WHERE empno = :emp_number FOR UPDATE OF job; /* 打开

12、游标 */ EXEC SQL OPEN emp_cursor; /* break if the last row was already fetched */ EXEC SQL WHENEVER NOT FOUND DO break; /* 循环取值*/ for (;) EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title; /* 更新当前游标所在的行的数据*/ EXEC SQL UPDATE emp SET job = :new_job_title WHERE CURRENT OF emp_cursor; . /* 关闭游标 */ EXEC

13、 SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; . 下面这个例子完整演示了静态游标的使用方法。这个例子的作用是，获得部门编号，通过游标来显示这个部门中的所有雇员信息。 #include /* 声明宿主变量 */ char userid12 = SCOTT/TIGER;char emp_name10;int emp_number;int dept_number; char temp32;void sql_error();/*包含SQLCA */ #include main() emp_number = 7499;/* 处理错误*/ EXE

14、C SQL WHENEVER SQLERROR do sql_error(Oracle error);/* 连接到Oracle数据库*/ EXEC SQL CONNECT :userid; printf(Connected.n); /* 声明游标 */ EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename FROM emp WHERE deptno = :dept_number; printf(Department number? ); gets(temp);dept_number = atoi(temp); /* 打开游标*/ EXEC SQ

15、L OPEN emp_cursor; printf(Employee Namen); printf(-n);/* 循环处理每一行数据，如果无数据，则退出*/EXEC SQL WHENEVER NOT FOUND DO break;while (1) EXEC SQL FETCH emp_cursor INTO :emp_name; printf(%sn, emp_name); EXEC SQL CLOSE emp_cursor; EXEC SQL COMMIT WORK RELEASE; exit(0); /错误处理程序*/void sql_error(msg)char *msg;char b

16、uf500;int buflen, msglen;EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; buflen = sizeof (buf);sqlglm(buf, &buflen, &msglen);printf(%sn, msg); printf(%*.sn, msglen, buf); exit(1); 2 嵌入PL/SQL嵌入PL/SQL和嵌入SQL不同。嵌入PL/SQL提供了很多嵌入SQL不具有的优点，如：更好的性能、更灵活的表达方式。能够自己定义过程和函数。如：PROCEDURE create

17、_dept (new_dname IN CHAR(14), new_loc IN CHAR(13), new_deptno OUT NUMBER(2) IS BEGIN SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO dept VALUES (new_deptno, new_dname, new_loc); END create_dept; 其中的IN/OUT，表示参数模式。IN是传递参数值到过程，而OUT是从过程传递参数值到调用者。但是，如果使用这些扩展的功能，也会造成同其他数据库厂商的嵌入SQL的不兼容。3

18、动态SQL语句3.1 ORACLE动态SQL语句的一些特点ORACLE DBMS进入市场的时间早于DB2，其动态SQL支持是以IBM的system/R原型为基础的。因此，ORACLE支持的动态SQL与IBM的DB2标准有不同。虽然ORACLE和DB2在很大程度上是兼容的，但是在使用参数标志、SQLDA格式及支持数据类型转换等方面都有差异。DB2中不允许在PREPARE的动态语句中引用宿主变量，而是用问号来标志语句中的参数，然后用EXECUTE或OPEN语句来规定参数值。ORACLE允许用户用宿主变量规定动态语句中的参数。而且，ORACLE支持的DESCRIBE语句同DB2有一些区别。如：从已经

19、PREPARE后的动态查询语句中获得对查询结果列的信息的语句为：EXEC SQL DESCRIBE SELECT LIST FOR qrystmt INTO qry_sqlda;等价于DB2的：EXEC SQL DESCRIBE qrystmt INTO qry_sqlda;从已经PREPARE后的动态查询语句中获得对查询参数的说明的语句为：EXEC SQL DESCRIBE BIND LIST FOR qrystmt INTO qry_sqlda;该ORACLE语句没有对应的DB2语句。用户只能按照当前需要的参数和SQLDA的结构对SQLDA赋值。然后再在OPEN语句或EXECUTE语句中使

20、用SQLDA结构。3.2 使用动态SQL的四种方法使用动态SQL，共分成四种方法： 方法 支持的SQL语句 1 该语句不包含宿主变量，该语句不是查询语句2 该语句包含输入宿主变量 ，该语句不是查询语句3 包含已知数目的输入宿主变量或列的查询 4 包含未知数目的输入宿主变量或列的查询 l方法1：使用EXECUTE IMMEDIATE命令实现，具体语法为：EXEC SQL EXECUTE IMMEDIATE :host_string | string_literal ;其中，host_variable和string是存放完整T-SQL语句。请看下面这个例子。这个例子的作用是执行用户随意输入的合法的

21、SQL语句。char dyn_stmt132; . for (;) printf(Enter SQL statement: ); gets(dyn_stmt); if (*dyn_stmt = 0) break; /* dyn_stmt now contains the text of a SQL statement */ EXEC SQL EXECUTE IMMEDIATE :dyn_stmt; . EXECUTE IMMEDIATE命令的作用是：分析该语句的语法，然后执行该语句。方法1适合于仅仅执行一次的语句。l方法2：方法支持的语句可以包含输入宿主变量。这个语句首先做PREPARE操作，

22、然后通过EXECUTE执行。PREPARE语句的语法为：EXEC SQL PREPARE statement_name FROM :host_string | string_literal ;该语句接收含有SQL语句串的宿主变量，并把该语句送到ORACLE。ORACLE编译语句并生成执行计划。在语句串中包含一个“？”表明参数，当执行语句时，ORACLE需要参数来替代这些“？”。PREPRARE执行的结果是，DBMS用语句名标志准备后的语句。在执行SQL语句时，EXECUTE语句后面是这个语句名。EXECUTE语句的语法为：EXECUTE 语句名 USING 宿主变量 | DESCRIPTOR

23、描述符名它的作用是，请求ORACLE执行PREPARE语句准备好的语句。当要执行的动态语句中包含一个或多个参数标志时，在EXECUTE语句必须为每一个参数提供值。这样的话，EXECUTE语句用宿主变量值逐一代替准备语句中的参数标志（“？”或其他占位符），从而，为动态执行语句提供了输入值。使用主变量提供值，USING子句中的主变量数必须同动态语句中的参数标志数一致，而且每一个主变量的数据类型必须同相应参数所需的数据类型相一致。各主变量也可以有一个伴随主变量的指示符变量。当处理EXECUTE语句时，如果指示符变量包含一个负值，就把NULL值赋予相应的参数标志。除了使用主变量为参数提供值，也可以通过

24、SQLDA提供值。请看下面这个例子。这个例子的作用是删除用户指定的雇员信息。. int emp_number INTEGER; char delete_stmt120, search_cond40; . strcpy(delete_stmt, DELETE FROM EMP WHERE EMPNO = :n AND ); printf(Complete the following statements search condition-n); printf(%sn, delete_stmt); gets(search_cond); strcat(delete_stmt, search_cond

25、); EXEC SQL PREPARE sql_stmt FROM :delete_stmt; for (;) printf(Enter employee number: ); gets(temp);emp_number = atoi(temp); if (emp_number = 0) break; EXEC SQL EXECUTE sql_stmt USING :emp_number; l方法三：是指查询的列数或输入宿主变量数在预编译时已经确定，但是数据库中的对象，如表、列名等信息未确定。这些对象名不能是宿主变量。这时，必须通过以下语句来完成：PREPARE statement_name

26、FROM :host_string | string_literal ; DECLARE cursor_name CURSOR FOR statement_name; OPEN cursor_name USING host_variable_list; FETCH cursor_name INTO host_variable_list; CLOSE cursor_name; 如：下面这个例子演示用方法3完成动态查询：char select_stmt132 =SELECT MGR, JOB FROM EMP WHERE SAL :salary; EXEC SQL PREPARE sql_stmt

27、 FROM :select_stmt; EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; EXEC SQL OPEN emp_cursor USING :salary; EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title; EXEC SQL CLOSE emp_cursor; l方法四：在预编译时，查询的列数或者宿主变量的个数不能确定，因为不知道具体的返回个数，所以不能使用输出宿主变量。这是因为你不知道应该定义多少个宿主变量。这时，就需要SQLDA结构和DESCRIBE命令。SQLDA包含

28、了动态查询的列描述信息。对于输入宿主变量，也可以使用SQLDA来完成不确定的参数说明。要完成方法四，必须通过以下语句来完成：EXEC SQL PREPARE statement_name FROM :host_string | string_literal ; EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name INTO bind_descriptor_name; EXEC SQL OPEN cursor_name USING DE

29、SCRIPTOR bind_descriptor_name; EXEC SQL DESCRIBE SELECT LIST FOR statement_name INTO select_descriptor_name; EXEC SQL FETCH cursor_name USING DESCRIPTOR select_descriptor_name; EXEC SQL CLOSE cursor_name; 在上述语句中，DESCRIBE SELECT LIST的作用是将PREPARE后的动态查询语句的列名、数据类型、长度等信息保存在SQLDA中。DESCRIBE BIND VARIABLES的

30、作用是，检查PREPARE后的动态查询语句的每个占位符的名字、数据类型、长度等信息。并将它存放在SQLDA中，然后，使用SQLDA提示用户数据参数值。值得注意的是，方法之间可以混合使用。如：在一个查询中，列的个数确定，但是查询中的占位符不确定，这时，你可以结合方法3和方法4，即使用方法3的FETCH语句和方法4的OPEN语句，如：EXEC SQL FETCH emp_cursor INTO host_variable_list; 反之，如果查询中占位符的个数确定，而列数不确定，则你可以使用方法3的OPEN语句，如：EXEC SQL OPEN cursor_name USING host_variable_list; 这里，我们讲解的是嵌入SQL，对于嵌入PL/SQL，有一些区别。简单来说，主要有两点：l预编译器将PL/SQL块中的所有宿主变量都作为输入宿主变量。l不能对PL/SQL块使用FETCH命令。l占位符不用声明，可以是任何名字。如：INSERT INTO emp (empno, deptno) VALUES (:e, :d) DELETE FROM dept WHERE deptno = :num OR loc = :loc 其中的e、d、num和loc就是占位符。