Execute immediate dynamic string [into {define variable [, define variable]



Дата13.09.2016
Размер23.67 Kb.
#9278
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a

dynamic SQL statement


EXECUTE IMMEDIATE dynamic_string

[INTO {define_variable

[, define_variable] ... | record}]

[USING [IN|OUT|IN OUT] bind_argument

[, [IN|OUT|IN OUT] bind_argument] ... ];
where dynamic_string is a string expression that represents a SQL statement or PL/SQL block, define_variable is a variable that stores a selected column value, and record is a user-defined or %ROWTYPE record that stores a selected row.

Except for multi-row queries, the dynamic string can contain any SQL statement. The string can also contain placeholders for bind arguments.

Used only for single-row queries, the INTO clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

You can place all bind arguments in the USING clause. The default parameter mode is IN.


You can use the INTO clause for a single row query, but you must use OPEN-FOR, FETCH, and CLOSE for a multirow query. OPEN-FOR/FETCH/CLOSE are not covered in this course, as they use cursor variables, and they are not part of the course.


Example1:

CREATE PROCEDURE del_rows

(v_table_name IN VARCHAR2,

rows_deld OUT NUMBER)

IS

BEGIN


EXECUTE IMMEDIATE 'delete from '||v_table_name;

rows_deld := SQL%ROWCOUNT;


END;


Example2

DECLARE


sql_stmt VARCHAR2(200);

emp_id NUMBER(4) := 7566;

dept_id NUMBER(2) := 50;

dept_name VARCHAR2(14) := ’PERSONNEL’;

location VARCHAR2(13) := ’DALLAS’;

emp_rec emp%ROWTYPE;


BEGIN
EXECUTE IMMEDIATE ’CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ’INSERT INTO dept VALUES (:1, :2, :3)’;

EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;


sql_stmt := ’SELECT * FROM emp WHERE empno = :id’;

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;


EXECUTE IMMEDIATE ’DELETE FROM dept WHERE deptno = :num’

USING dept_id;


EXECUTE IMMEDIATE ’ALTER SESSION SET SQL_TRACE TRUE’;

END;


Example3


DECLARE

plsql_block VARCHAR2(500);

new_deptno NUMBER(2);

new_dname VARCHAR2(14) := ’ADVERTISING’;

new_loc VARCHAR2(13) := ’NEW YORK’;

BEGIN


plsql_block := ’BEGIN create_dept(:a, :b, :c); END;’;

EXECUTE IMMEDIATE plsql_block

USING IN OUT new_deptno, new_dname, new_loc;

IF new_deptno > 90 THEN ...

END;

Какво е bind променлива и ползата от тях: http://www.akadia.com/services/ora_bind_variables.html


Тук ще се опитам да систематизирам малко нещата за възможните клаузи на EXCUTE IMMEDIATE, макар изразяването да не е точно на места. Тъй като по-нагоре не се говори за RETURNING клаузи и BULK COLLECT, написаното по-надолу може да не е съвсем ясно, ако човек няма вече някаква бегла предварителна представа, че има такива неща 


  1. ако използваме SELECT в EXCUTE IMMEDIATE,

използваме INTO клауза след оператора, за да получим резултата от селекта в pl/sql кода ни.

  • Ако селекта връща един ред пишем INTO и променлива/провменливи, в която се връща резултата.

EXECUTE IMMEDIATE ’SELECT ename FROM emp WHERE empno = 9999’

INTO v_ename;




  • Ако селекта връща множество редове заменяме INTO с BULK COLLECT INTO и връщаме резултата в масив.

EXECUTE IMMEDIATE ’SELECT ename FROM emp’

BULK COLLECT INTO v_ename_array;


  • Допустимо е вътре в самия динамичен селект преди from клаузата да се сложи into клауза, но тя се игнорира т.е. безсмислено е да я слягаме.

EXECUTE IMMEDIATE ’SELECT ename INTO :x FROM emp WHERE empno = 9999’

INTO v_ename;
INTO :x е безпредметно.


  1. Ако използваме “returning into” клауза, задължително имаме такава клауза вътре в динамичния update или delete и съответна клауза извън него, за да получим резултата от селекта в pl/sql кода ни.

EXECUTE IMMEDIATE

DELETE FROM emp WHERE empno = 9999 RETURNING ename INTO :1'
RETURNING INTO v_ename;
Забележка: в returning into клаузата вътре в оператора слагаме след кл.дума returning една или повече колони, който искаме им върнем стойността, а в into клаузата съответен брой променливи. Извън оператора се пише RETURNING INTO и една (съответно няколко променливи) от pl/sql кода ни, в който връщаме резултата – позиционно отговарят на колоните, който връщаме.

Ако оператора променя или изтрива множество редове съответно трябва да използваме returning bulk collect into вместо returning into извън оператора и масив вместо скаларна променлива.


EXECUTE IMMEDIATE

DELETE FROM emp WHERE RETURNING ename INTO :1'


RETURNING BULK COLLECT INTO v_ename_array;


  1. Във всички останали случая за указване на съответствието субституционна променлива, използвана в динамичния оператор – pl/sql променлива от процедурата ни, използваме USING клауза.

Най-общо казано, тя не може да се използва, за да се подаде масив. За такива случаи ни трябва FORALL цикъл :


FORALL i IN v_empno_array.first .. v_empno_array.last
    EXECUTE IMMEDIATE
    'DELETE FROM empWHERE  empno = :1'
    USING v_empno_array(i)

Друг полезен документ:



http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf


Сподели с приятели:




©obuch.info 2024
отнасят до администрацията

    Начална страница