Cursors and dynamic SQL

Cursor categories: static cursors (referring to cursors that are statically bound to a select statement during compilation. This cursor can only act on one query statement) and dynamic cursors (that is, we hope that our query statement is running It is only bound to the cursor when the cursor is used. In order to use a dynamic cursor, the cursor variable must be declared).

Dynamic cursors are divided into two types, namely strong type and weak type. A strongly typed dynamic cursor can only support query statements whose query results match its type, while a weakly typed dynamic cursor can support any query statement.

Static cursors are divided into two types, implicit cursors and explicit cursors. A display cursor is a cursor declared and operated by the user. An implicit cursor is a cursor automatically declared by Oracle for all data manipulation statements.

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

Steps to use to display a cursor:

1. Declare an explicit cursor, syntax: CURSORISSELECT;

When declaring a cursor, you usually also need to declare some variables to store the query statements. query results. Declare cursors and variables in declare. Usually variables are declared first, then cursors.

2. Open the cursor. Starting from opening the cursor, the following steps are performed in begin and end. Syntax: open; When the cursor is opened, the query statement starts to be executed, the query results are placed in the Oracle buffer, and then the cursor points to the first row of the query results in the buffer.

3. Extract the cursor. By extracting the cursor, the cursor points to each row of the query result in turn. Syntax: FETCHINTO;

4. Close the cursor, syntax: CLOSE;

Example:

declare
 name varchar2(50);                               --定义变量存储employees表中的内容。
 department_name varchar2(20);               --定义变量存储departments表中的内容；
 cursor emp_cur IS                               --定义游标emp_cur
select name,department_name               --选出所有员工的姓名和所做部门。
from employees e,departments d
  where e.department_id=d.department_id;
begin
 open emp_cur;                                      --打开游标
 LOOP
 FETCH emp_cur INTO name,depart_name;      --将第一行数据放入变量中，游标后移。
      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 does not point to a valid row; %ROWCOUNT, the number of rows extracted by the cursor.

Syntax: cursor name% attribute name.

For example: The company goes public and decides to increase the salary of employees. If the employee has been employed for more than 1 year, the salary will increase by RMB 100 and the salary will be capped at RMB 1,000.

declare
 hire_date date;              --存放员工入职日期
 e_id number;                   --存放员工id
 cursor emp_cur is            --定义游标
      select id,hire_date from employees;
begin
 open emp_cur;                 --打开游标
 loop
      fetch emp_cur into e_id,hire_date       --将数据逐条存入变量
      exit when emp_cur%NOTFOUND;
      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 loop cursor to read cursor, syntax: FOR IN LOOP – operate 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||’在’||employee_record.department_name);
      END LOOP;
END;

Implicit cursor

The difference between implicit cursor and explicit cursor : 1. No need to declare a cursor. 2. No need to open and close the cursor. 3. The INTO clause must be used, and the result can only be one.

Implicit cursors are the same as explicit cursors: they have the same attributes. The way to use attributes for implicit cursors is to add SQL% in front of the attribute name, that is, SQL%FOUND, SQL%ISOPEN, etc.

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||’在’||department_name);
END;

Because the implicit cursor query result only has one row, it does not make much sense if used for counting, so the %ROECOUNT attribute is often used to determine whether 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(‘表已更新！’);
      else
             dbms_output.put_line(‘编号未找到’);
      end if;
end;

REF dynamic cursor

ref dynamic cursor can be associated with different statements at runtime, it is dynamic. The ref dynamic cursor is used to process multi-row query result sets. The ref dynamic cursor is a variable of type ref, similar to a pointer.

Define ref dynamic cursor type: type is ref cursor return ;

Declare ref dynamic cursor: ;

Open ref dynamic Cursor: OPEN FOR ;

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.

Weakly typed 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

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(‘===员工信息===’);
      ELSEIF selection = ‘D’ THEN
             OPEN refcur FOR
                    SELECTdepartment_id,department_name FROM departments;
             dbms_output.put_line(‘===部门信息===’);
      ELSE
             dbms_output.put_line(‘请输入员工信息E或部门信息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 dynamic SQL statements based on user input (employee, department).

Static SQL, determined at compile time.

Dynamic SQL, not compiled, is dynamically determined during execution; the SQL statement can be determined based on user input parameters, etc.; it solves the problem that DDL statements are not supported in PL/SQL.

SQL statement syntax for creating dynamic DML.DDL:

EXECUTEIMMEDIATE 'DML, DDL statement'; [INTO] [USING ]; can only execute statements that return one row or 0 rows .

If the following statement is a select statement, you can use the into clause to receive the record value selected by the select statement. It can be a sequence of variables, or a record type variable that is a record type variable. If there are parameters in the SQL statement that need to be determined dynamically, then we use the USING clause, which is used to bind the input parameter variables. If there are parameters in the 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: Dynamically insert records

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;

EXECUTEIMMEDIATE statement can only return one row or nothing. , if you write a SQL statement that returns multiple rows, you can use a ref dynamic cursor. Its syntax: OPEN cursor_name FOR [USING ];

Example: Dynamically output salary greater than a certain amount Employee information

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(‘薪水大于’||p_salary||’的员工有：’);
      LOOP
             FETCH cur INTOe_id,e_name,e_salary;
             EXIT WHEN cur%NOTFOUND;
             dbms_output.put_line(‘编号：’||e_id||’姓名：’||e_name||’薪水：’||e_salary);
END LOOP
      CLOSE cur;
END;