Oracle midstream and dynamic bound variables

First, Cursors:

Described in database language: A cursor is a position entity mapped to a row of data in the result set, a cursor user can access any row of data in the result set, and the cursor is placed on a row to manipulate the row data, such as extracting data from the current row, and so on.

Before oracle9i, you can only fetch one row of data at a time using a FETCH statement, starting with oracle9i, by using fetch ... BULK COLLECT into statement, you can extract multiple rows of data at a time. The syntax is as follows:

(1) FETCH cursor_name into Variable1,variable2,... ；

This method must use a looping statement to process all the data for the result set.

(2) FETCH cursor_name BULK COLLECT into Collect1,collect2,... [LIMIT rows]

[Limit rows] can be used to limit the number of rows per cursor fetch each time.

Classification of cursors: explicit and implicit cursors

To display the use of cursors:

1. declaring cursors

CURSOR Mycur (vartype number) is

Select Emp_no,emp_zc from Cus_emp_basic

where com_no = VarType;

1. Open cursor

Open Mycur (000627) Note: 000627: Parameter

1. Reading data

Fetch mycur into Varno,varprice;

1. Close Cursors

Close mycur;

The properties of the cursor

Oracle cursors have 4 properties:%isopen,%found,%notfound,%rowcount

• %isopen determines if the cursor is open and if open%isopen equals True, otherwise equals false
• %found%notfound Determines whether the row where the cursor is valid, if valid,%foundd equals True, otherwise equals false
• %rowcount returns the number of record rows that the cursor reads until the current position.

Second, dynamically bound variables

Dynamic bound variables address a key aspect of Oracle application scalability, while Oracle's shared pool determines that the developer must use bound variables, and if you want Oracle to slow down or even terminate completely, you don't have to bind the variables.

The above questions are illustrated here;

In order to query an employee code is 123, you can query:

SELECT * from emp where empno= ' 123 ';

You can also check this:

SELECT * from EMP where empno=:empno;

As we always do, you may not use it again after you query employees ' 123 ', then you may query employees ' 456 ', then query ' 789 ' and so on; If the query uses a query like the first one, you are a new query every time (we call it hard-coded query method); Oracle must analyze, parse, check, optimize and so on every time.

The second query statement provides a binding variable: Empno, whose value is provided when the query is executed, the query scheme is stored in a shared pool once compiled, and can be used for retrieval and reuse; In terms of performance and scalability, the difference is huge or even astonishing; in layman's words, it is not a level;

The higher the frequency of the first query, the greater the system hardware resources consumed, thereby reducing the number of users used; it would also kick out the other query statements that were optimized from the shared pool; As a mouse broke a pot of soup, the overall performance of the system decreased; Instead of executing the binding variable, submit the same object to the user of the exact same query (this sentence, everyone sounds more difficult to understand, then I will give a detailed explanation), disposable use can be reused, its efficiency does not whisper; To make an image of the metaphor, the first query is like a disposable chopsticks, and the second query like Iron chopsticks, as long as the Wash, Dick and Harry can use, reasonable and efficient use of resources

Dynamic binding vs. static comparison:

DECLARE

V_sql VARCHAR2 (500);

BEGIN

--Do not use bound variables

/*for I in 1..50000 LOOP

V_sql: = ' INSERT into t_temp values (' | | i| | ') ';

EXECUTE IMMEDIATE V_sql;

END loop;*/

--adding data using bound variables

For I in 1..50000 LOOP

V_sql: = ' INSERT INTO t_temp values (: a) ';

EXECUTE IMMEDIATE v_sql USING i;

END LOOP;

END;

--truncate TABLE t_temp;

Dynamic Query SQL Example:

--Dynamic SQL for queries

DECLARE

V_sql VARCHAR2 (100);

V_cursor Sys_refcursor;

V_emp Emp%rowtype;

BEGIN

V_sql: = ' select * from emp & condition ';

OPEN v_cursor for V_sql;

LOOP

FETCH v_cursor into v_emp;

EXIT when V_cursor%notfound;

Dbms_output. Put_Line (v_emp. ENAME);

END LOOP;

CLOSE V_cursor;

END;