Cursor and dynamic SQL, cursor dynamic SQL

Cursor and dynamic SQL, cursor dynamic SQL

Cursor type: Static cursor (the cursor is statically bound to a select statement during compilation. This cursor can only act on one query statement) and dynamic cursors (that is, we want our query statements to be bound to the cursor only when running. To use dynamic cursors, we must declare the cursor variable ).

Dynamic cursors are classified into two types: strong and weak. A strong dynamic cursor can only support the query statement that matches the query result and Other types. A weak dynamic cursor can support any query statement.

Static cursors include implicit cursors and display cursors. A display cursor is a type of cursor with user statements and operations. Implicit cursor is a cursor automatically declared by Oracle for all data operation statements.

In each user's session, we can OPEN multiple CURSORS at the same time, which is defined by the open cursors parameter in the database initialization parameter file.

Procedure for displaying a cursor:

1. Acoustic explicit cursor, Syntax: CURSOR <CURSOR Name> ISSELECT <Statement>;

When declaring a cursor, you usually need to declare some variables to store the query results generated by the query statement. Declare the cursor and variable in declare. Variables are usually declared first, and the cursor is declared.

2. Open the cursor,Starting from opening the cursor, the subsequent steps are executed in begin and end. Syntax: open <cursor Name>; when the cursor is opened, the query statement starts to be executed, and the query result is placed in the Oracle buffer, then the cursor points to the first row of records in the query result in this buffer zone.

3. Extract the cursor,By extracting the cursor, the cursor points to each row of the query result in sequence. Syntax: FETCH <cursor Name> INTO <Variable list>;

4. Close the cursor,Syntax: CLOSE <cursor Name>;

Example:

Declare name varchar2 (50); -- defines the variable to store the content in the employees table. Department_name varchar2 (20); -- defines the variable to store the content in the orders table; cursor emp_cur IS -- defines the cursor emp_curselect name and department_name -- selects the names and departments of all employees. From employees e, orders ments d where e. department_id = d. department_id; begin open emp_cur; -- open the cursor loop fetch emp_cur INTO name, depart_name; -- put the first row of data INTO the variable and move the cursor back. Exit when emp_cur % NOTFOUND; dbms_output.put_line (name | '| department_name); end loop; CLOSE emp_cur; END;

Cursor attributes:% ISOPEN: whether the cursor is open; % FOUND: whether the cursor points to a valid row; % NOTFOUND: whether the cursor points to a valid row; % ROWCOUNT: the number of rows extracted by the cursor.

Syntax: cursor name % attribute name.

For example, when a company goes public, it decides to raise the employee's salary. The company's employment time has risen by in over one year, and the price of Yuan is capped.

Declare hire_date date; -- store employee entry date e_id number; -- store employee id cursor emp_cur is -- Define cursor select id, hire_date from employees; begin open emp_cur; -- open the cursor loop fetch emp_cur into e_id, hire_date -- save the data to the variable exit when emp_cur % NOTFOUND one by one; if 100 * (2014-to_char (hire_date, 'yyyy ')) <1000 then update salary setsalaryvalue = salaryvalue + 100 * (2010-to_char (hire_date, 'yyyy') where employee. id = e_id; else update salary setsalaryvalue = salaryvalue + 1000 where employee. id = e_id; end if; end loop; close emp_cur; end

Use the LOOP cursor to read data. Syntax: FOR <type> IN <cursor Name> LOOP-operation of each row of data end loop;

Declare cursor emp_cur is select name, department_name FROM employees e, departments d; WHERE e. department_id = d. department_id; begin for employ_record IN emp_cur LOOP dnms_output.put_line (employ_record.name | 'IN' | employee_record.department_name); end loop; END;

Implicit cursor

The difference between an implicit cursor and a display cursor: 1. You do not need to declare a cursor. 2. You do not need to open or close the cursor. 3. The INTO clause must be used and only one result is returned.

Implicit cursor has the same attributes as the display cursor. The implicit cursor uses attributes by adding SQL % before the attribute name, that is, SQL % FOUND and SQL % ISOPEN.

DECLARE name VARCHAR2 (50); department_name varchar (20); begin select name, department_name INTO name, deprtment_name FROM employees e, departments d; WHERE e. department_id = d. department_id and e. id = 1; dbms_output.put_line (name | 'in' | department_name); END;

Because the implicit cursor query result has only one row, if it is used for counting, it does not make much sense, so the % ROECOUNT attribute is often used to determine whether the insertion, deletion, and update are successful, but before the COMMIT statement. If after COMMIT, % ROECOUNT can only be 0;

Begin update employees set name = name | 'A' where id = 7; if SQL % rowcount = 1 then dbms_output.put_line ('table updated! '); Else dbms_output.put_line (' No. Not found '); end if; end;

REF Dynamic Cursor

Ref dynamic cursors can be associated with different statements during running. They are dynamic. Ref dynamic cursor is used to process multi-row query result sets. ref dynamic cursor is a variable of the ref type, similar to a pointer.

Define the ref Dynamic cursor type: type <type Name> is ref cursor return <return type>;

Declare ref Dynamic Cursor: <cursor Name> <type Name>;

OPEN ref Dynamic Cursor: OPEN <cursor Name> FOR <query statement>;

Example:

DECLARE      TYPE refcur_t IS REF CURSOR      RETURN employess%ROWTYPE;      refcur refcur_t;      v_emp employees%ROWTYPE;BEGIN      OPEN refcur FOR      SELECT * FROM employees;      LOOP             FETCH refcur INTO v_emp;             EXIT WHEN refcur%NOTFOUND;             dbms_output.put_line(refcur%ROWCOUNT||’‘||v_emp.name);      END LOOP;      CLOSE refcur;END;

Strong type ref Dynamic Cursor: REF dynamic cursor with RETURN statement.

Weak type ref Dynamic Cursor: REF Dynamic Cursor without RETURN statement.

For example:

DECLARE      TYPE refcur_t IS REF CURSOR        refcur refcur_t;      e_id number;      e_name varchar2(50);BEGIN      OPEN refcur FOR      SELECT id,name FROM employees;      FETCH refcur INTO e_id,e_name;      WHILE refcur%FOUND LOOP             dbms_output.put_line(‘#’||e_id||’:’||e_name);             FETCH refcur INTO e_id,e_name;      END LOOP;      CLOSE refcur;END;

Print Information based on user input (employees, Departments)

Declare type refcur_t is ref cursor; refcur refcur_t; p_id NUMBER; p_name VARCHAR2 (50); selection VARCHAR2 (1): = UPPER (SUBSTR ('& tab', 1, 1 )); begin if selection = 'E' then open refcur for select id, name FROMemployees; dbms_output.put_line ('= employee information = '); ELSEIF selection = 'D' then open refcur FOR SELECTdepartment_id, department_name FROM departments; dbms_output.put_line ('= Department Information = '); ELSE dbms_output.put_line ('enter employee information E or department information D'); RETURN; end if; FETCH refcur INTO p_id, p_name; WHILE refcur % found loop dbms_output.put_line ('#' | p_id | ':' | p_name); FETCH refcur INTO p_id, p_name; end loop; CLOSE refcur; END;

Create dynamicSQL Statement.

Static SQL, which is determined during compilation.

Dynamic SQL statements are dynamically determined during execution without compilation. SQL statements can be determined based on user input parameters. DDL statements are not supported in PL/SQL.

SQL statement syntax for creating dynamic DML. DDL:

EXECUTEIMMEDIATE 'dml, DDL statement'; [INTO <variable sequence>] [USING <parameter sequence>]; statements that return only one or zero rows can be executed.

If the following statement is a select statement, you can use the into clause to receive the record values selected by the select statement. It can be a variable sequence, or a record-type variable, that is, a record-type variable. If the SQL statement requires dynamic determination of parameters, we use the USING clause and the USING clause to bind the input parameter variables. If a parameter exists in an SQL statement, use ": parameter name"

Example: dynamically create a table

BEGIN      EXECUTE IMMEDIATE             ‘CREATE TABLE bonus(id NUMBER,amtNUMBER)’;END;

Example: dynamically query an employee's phone number

DECLARE      sql_stmt VARCHAR2(200);      emp_id NUMBER(10) :=’&emp_id’;      emp_rec employees%ROWTYPE;BEGIN      sql_stmt :=’select * from employees WHEREid =:id’;      EXECUTE IMMEDIATE sql_stmt INTO emp_recUSING emp_id;END;

Example: Dynamic insert record

DECLARE      Sql_stmt varchar2(200);      emp_id NUMBER(10) := ‘&emp_id’;      emp_rec employees%ROWTYPE;BEGIN      sql_stmt := ‘INSERT INTO employees(id)values(:id)’;      EXECUTE IMMEDIATE sql_stmt USING emp_id;      Dbms_output.put_line(emp.rec.phone);END;

The EXECUTEIMMEDIATE statement can only return one or no response. If you write an SQL statement that returns multiple rows, you can use the ref Dynamic Cursor. Its Syntax: OPEN cursor_name FOR <SQL statement> [USING <parameter sequence>];

Example: dynamically output employee information with a salary greater than a certain amount

DECLARE e_id NUMBER (10); e_name VARCHAR2 (50); s_salary NUMBER (8); TYPE c_type is ref cursor; cur c_type; p_salary NUMBER: = '& p_id '; begin open cur FOR 'selecte. id, e. name, e. salaryvalue from employees e, salary s where e. id = s. employeeid ands. salaryvalue>: sal order by id ASC '; USING p_salary; dbms_output.put_line ('salary higher than' | p_salary | 'employees:'); loop fetch cur ready e_id, e_name, e_salary; exit when cur % NOTFOUND; dbms_output.put_line ('No.:' | e_id | 'name: '| e_name | 'salary:' | e_salary ); end loop close cur; END;

Dynamic Cursor dynamic SQL is one thing

SQL Server supports four types of cursors: static cursors, dynamic cursors, forward-only cursors, and keyset-driven cursors. The dynamic cursor can update the result set. When the cursor is rolled, the dynamic cursor reflects all the changes made in the result set, the row data values, sequence, and members in the result set change each time they are extracted.
The static server cursor creates the entire cursor in tempdb, And the cursor driven by the key set creates the key set in tempdb.
Dynamic cursors are more suitable for large result sets. You can update the result set by specifying for update in the query statement, or you can update the result set without read_only. Normally, you do not have to worry about whether the result set is dynamic or not, which is implicitly converted by the server.
Dynamic example:
DECLARE abc CURSOR LOCAL
SELECT c1, c2
FROM dbo. Table1;
OPEN abc;
FETCH abc;
UPDATE dbo. Table1
SET c2 = c2 + d2
FROM dbo. Table2
Where current of abc; -- Update the cursor

Dynamic Cursor dynamic SQL is different