oracle的高级特性,Oracle 高级特性

最新推荐文章于 2021-04-04 21:38:09 发布

樊康康

最新推荐文章于 2021-04-04 21:38:09 发布

阅读量251

点赞数

文章标签： oracle的高级特性

本地动态SQL

1.执行非查询语句和PL/SQL块

DECLARE

v_SQLString VARCHAR2(200);

v_PLSQLBlock VARCHAR2(200);

BEGIN

EXECUTE IMMEDIATE 'create table tmp_table (col1 varchar2(10))'; --DDL语句最后不能加分号

FOR v_Count IN 1 .. 10 LOOP

v_SQLString := 'insert into tmp_table (col1) values (''ROW ' || v_Count ||

''')'; --DML语句最后不能加分号

EXECUTE IMMEDIATE v_SQLString;

END LOOP;

v_PLSQLBlock := 'begin

for v_rec in (select * from tmp_table) loop

dbms_output.put_line(v_rec.col1);

end loop;

end;';--PL/SQL块最后需要加分号

EXECUTE IMMEDIATE v_PLSQLBlock;

EXECUTE IMMEDIATE 'drop table tmp_table';

END;

DDL,DML语句最后不能加分号, 而PL/SQL块最后需要加分号

可以使用using 子句执行带有绑定变量的语句：

DECLARE

v_SQLString VARCHAR2(200);

v_PLSQLBlock VARCHAR2(200);

v_Count2 NUMBER;

BEGIN

EXECUTE IMMEDIATE 'create table tmp_table (col1 varchar2(10),col2 varchar2(10))'; --DDL语句最后不能加分号

FOR v_Count IN 1 .. 10 LOOP

v_Count2 := v_Count + 1;

v_SQLString := 'insert into tmp_table (col1,col2) values (:var_test,:var_test2)'; --DML语句最后不能加分号

EXECUTE IMMEDIATE v_SQLString

USING v_Count, v_Count2;

END LOOP;

v_PLSQLBlock := 'begin for v_rec in (select * from tmp_table) loop dbms_output.put_line(v_rec.col1||' ||

''',''' || '||v_rec.col2); end loop; end;'; --PL/SQL块最后需要加分号

EXECUTE IMMEDIATE v_PLSQLBlock;

EXECUTE IMMEDIATE 'drop table tmp_table';

END;

2.执行查询

类似于游标变量，查询也是使用OPEN FOR语句执行的。它们的区别是包含查询的串可以是PL/SQL变量，而非文字。与其他任何变量一样，也可以从得到的游标变量中提取数据。对于绑定，就像EXECUTE IMMEDIATE一样，USING子句是可用的。

CREATE OR REPLACE PACKAGE NativeDynamic AS

TYPE t_RefCur IS REF CURSOR;

FUNCTION EmpQuery(p_WhereClause IN VARCHAR2) RETURN t_refCur;

FUNCTION EmpQuery2(p_Job IN VARCHAR2) RETURN t_refCur;

END NativeDynamic;

CREATE OR REPLACE PACKAGE BODY NativeDynamic AS

FUNCTION EmpQuery(p_WhereClause IN VARCHAR2) RETURN t_refCur IS

v_ReturnCursor t_RefCur;

v_SQLStatement VARCHAR2(500);

BEGIN

v_SQLStatement := 'select * from emp ' || p_WhereClause;

OPEN v_ReturnCursor FOR v_SQLStatement;

RETURN v_ReturnCursor;

END EmpQuery;

FUNCTION EmpQuery2(p_Job IN VARCHAR2) RETURN t_refCur IS

v_ReturnCursor t_RefCur;

v_SQLStatement VARCHAR2(500);

BEGIN

v_SQLStatement := 'select * from emp where job=:job';

OPEN v_ReturnCursor FOR v_SQLStatement

USING p_Job;

RETURN v_ReturnCursor;

END EmpQuery2;

END NativeDynamic;

SET SERVEROUTPUT ON;

DECLARE

v_Emp emp%ROWTYPE;

v_EmpCur NativeDynamic.t_RefCur;

BEGIN

v_EmpCur := NativeDynamic.EmpQuery('where deptno=20');

dbms_output.put_line('员工如下：');

LOOP

FETCH v_EmpCur

INTO v_Emp;

EXIT WHEN v_EmpCur%NOTFOUND;

dbms_output.put_line(v_Emp.empno || ' : ' || v_Emp.ename || ',' ||

v_Emp.job || ',' || v_Emp.deptno);

END LOOP;

CLOSE v_EmpCur;

v_EmpCur := NativeDynamic.EmpQuery2('CLERK');

dbms_output.put_line('员工如下：');

LOOP

FETCH v_EmpCur

INTO v_Emp;

EXIT WHEN v_EmpCur%NOTFOUND;

dbms_output.put_line(v_Emp.empno || ' : ' || v_Emp.ename || ',' ||

v_Emp.job || ',' || v_Emp.deptno);

END LOOP;

CLOSE v_EmpCur;

END;

EXECUTE IMMEDIATE也可以用于单行查询，可以带绑定变量也可以不带

DECLARE

v_Emp emp%ROWTYPE;

v_empno NUMBER;

v_SQLQuery VARCHAR2(500);

BEGIN

v_empno:=7369;

v_SQLQuery:='select * from emp where empno=:eno';

EXECUTE IMMEDIATE v_SQLQuery INTO v_Emp USING v_empno;

dbms_output.put_line(v_Emp.ename);

EXCEPTION WHEN no_data_found THEN

dbms_output.put_line('no_data_found...');

END;

3.成批绑定：

PL/SQL语句块中的SQL语句被发送到SQL引擎中进行执行。SQL引擎可以依次把数据发送回PL/SQL引擎(作为查询的结果)。在许多情况下，在数据库中将要插入或更新的数据首先放到一个PL/SQL集合中，然后该集合使用FOR循环进行迭代计算，并把信息发送到SQL引擎。对于该集合中的每一行，都将产生一个PL/SQL和SQL之间的上下文开关。

Oracle8i及更高版本允许你把一个集合里的所有行在一次操作中都传递到SQL引擎中，删除到只剩一个上下文开关，这叫做成批绑定，它使用FORALL语句来完成，下面是一个例子：

DECLARE

TYPE t_Numbers IS TABLE OF tmp_table.num_col%TYPE;

TYPE t_Strings IS TABLE OF tmp_table.char_col%TYPE;

v_Numbers t_Numbers := t_Numbers(1);

v_Strings t_Strings := t_Strings(1);

--Prints the total number of rows in tmp_table.

PROCEDURE PrintTotalRows(p_Message VARCHAR2) IS

v_Count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_Count FROM tmp_table;

dbms_output.put_line(p_Message || ' : Count is ' || v_Count);

END PrintTotalRows;

BEGIN

DELETE FROM tmp_table;

-- Fill up the PL/SQL nested tables with 1000 values.

v_Numbers.EXTEND(1000);

v_Strings.EXTEND(1000);

FOR v_Count IN 1 .. 1000 LOOP

v_Numbers(v_Count) := v_Count;

v_Strings(v_Count) := 'Element #' || v_Count;

END LOOP;

-- Insert all 1000 elements using a single FORALL statement.

FORALL v_Count IN 1 .. 1000

INSERT INTO tmp_table VALUES (v_Numbers(v_Count), v_Strings(v_Count));

PrintTotalRows('After first insert');

-- Insert elements 501 through 1000 again.

FORALL v_Count IN 501 .. 1000

INSERT INTO tmp_table VALUES (v_Numbers(v_Count), v_Strings(v_Count));

PrintTotalRows('After second insert');

-- Update rows

FORALL v_Count IN 1 .. 1000

UPDATE tmp_table

SET char_col = 'changed!'

WHERE num_col = v_Numbers(v_Count);

dbms_output.put_line('Update processed ' || SQL%ROWCOUNT || ' rows');

-- Likewise,this DELETE will remove 300 rows

FORALL v_Count IN 401 .. 600

DELETE FROM tmp_table WHERE num_col = v_Numbers(v_Count);

PrintTotalRows('After delete');

COMMIT;

END;

FORALL的事务性问题，如果在处理成批DML操作中的一行时有错误，则只有该行被回滚。该行之前的行仍然被处理。这与使用OCI或预编译器的成批操作具有相同的行为。

Oracle9i的SAVE EXCEPTION 子句可以用于FORALL语句。使用这个子句，在批处理期间发生的任何错误都将被保存，并且该处理将会继续。可以使用SQL%BULK_EXCEPTION属性来查看该异常，该属性起着SQL*Plus表的作用。如下：

DECLARE

TYPE t_Numbers IS TABLE OF tmp_table.num_col%TYPE INDEX BY BINARY_INTEGER;

TYPE t_Strings IS TABLE OF tmp_table.char_col%TYPE INDEX BY BINARY_INTEGER;

v_Numbers t_Numbers;

v_Strings t_Strings;

v_NumErrors NUMBER;

BEGIN

DELETE FROM tmp_table;

FOR v_Count IN 1 .. 10 LOOP

v_Numbers(v_Count) := v_Count;

v_Strings(v_Count) := '1234567890';

END LOOP;

FORALL v_Count IN 1 .. 10

INSERT INTO tmp_table VALUES (v_Numbers(v_Count), v_Strings(v_Count));

--Add an extra character to v_Strings(6).

v_Strings(6) := v_Strings(6) || 'a';

--This bulk update will fail on the sixth row,and continue processing.

FORALL v_Count IN 1 .. 10 SAVE EXCEPTIONS

UPDATE tmp_table

SET char_col = char_col || v_Strings(v_Count)

WHERE num_col = v_Numbers(v_Count);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Got exception: ' || SQLERRM);

--Print out any errors.

v_NumErrors := SQL%BULK_EXCEPTIONS.COUNT;

dbms_output.put_line('Number of errors during processing: ' ||

v_NumErrors);

FOR v_Count IN 1 .. v_NumErrors LOOP

dbms_output.put_line('Error ' || v_Count || ' , iteration ' ||

SQL%BULK_EXCEPTIONS(v_Count)

.ERROR_INDEX || ' is: ' ||

SQLERRM(0 - SQL%BULK_EXCEPTIONS(v_Count)

.ERROR_CODE));

END LOOP;

COMMIT;

END;

SQL>

Got exception: ORA-24381: 数组 DML 出错

Number of errors during processing: 1

Error 1 , iteration 6 is: ORA-12899: 列的值太大 (实际值: , 最大值: )

PL/SQL procedure successfully completed

BULK COLLECT子句可用作SELECT INTO,FETCH INTO,RETURNING INTO子句的一部分，并将从查询中把行检索到所指示的集合中。

注：FORALL可以用于集合类型以及INSERT,UPDATE,DELETE 语句中，而BULK COLLECT子句用于取数据

樊康康

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle的高级特性,Oracle 高级特性

本地动态SQL1.执行非查询语句和PL/SQL块DECLAREv_SQLString VARCHAR2(200);v_PLSQLBlock VARCHAR2(200);BEGINEXECUTE IMMEDIATE 'create table tmp_table (col1 varchar2(10))'; --DDL语句最后不能加分号FOR v_Count IN 1 .. 10 LOOPv_SQL...
复制链接

扫一扫