커서 및 동적 SQL

커서 범주: 정적 커서(컴파일 중에 select 문에 정적으로 바인딩된 커서를 나타냅니다. 이 커서는 하나의 쿼리 문에서만 작동할 수 있습니다.) 및 동적 커서(즉, 쿼리 문이 런타임 시 커서에 바인딩됩니다. 동적 커서를 사용하려면 커서 변수를 선언해야 합니다.

동적 커서에는 강한 유형과 약한 유형의 두 가지 유형이 있습니다. 강력한 형식의 동적 커서는 쿼리 결과가 해당 형식과 일치하는 쿼리 문만 지원할 수 있는 반면 약한 형식의 동적 커서는 모든 쿼리 문을 지원할 수 있습니다.

정적 커서는 암시적 커서와 명시적 커서의 두 가지 유형으로 구분됩니다. 표시 커서는 사용자가 선언하고 조작하는 커서입니다. 암시적 커서는 모든 데이터 조작 문에 대해 Oracle이 자동으로 선언한 커서입니다.

각 사용자 세션에서 동시에 여러 커서를 열 수 있습니다. 이 숫자는 데이터베이스 초기화 매개변수 파일의 OPEN CURSORS 매개변수에 의해 정의됩니다.

커서 표시 방법:

1. 명시적 커서 선언, 구문: CURSOR9fb3ebc560db139c4bd95ef3e44784b9ISSELECTfa7ef95fdb8b7324b4f19dcbb8e23063;

커서를 선언할 때 일반적으로 의 경우 쿼리 문에서 생성된 쿼리 결과를 저장하기 위해 일부 변수도 선언됩니다. 선언에서 커서와 변수를 선언합니다. 일반적으로 변수가 먼저 선언되고 커서가 선언됩니다.

2. 커서 열기 커서 열기부터 시작하여 다음 단계가 수행됩니다. 구문: open;; 커서가 열리면 쿼리문이 실행되기 시작합니다. 쿼리 결과는 Oracle 버퍼에 배치되고 커서는 쿼리 결과의 첫 번째 행을 가리킵니다.

3. 커서 추출 커서를 추출하면 커서가 쿼리 결과의 각 행을 차례로 가리킵니다. 구문: FETCHINTO;;

4. 커서를 닫습니다. 구문: CLOSE;;

예:

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;

커서 속성: %ISOPEN, 커서가 열려 있는지 여부, %FOUND, 커서가 유효한 행을 가리키는지 여부, %ROWCOUNT, 커서가 추출한 행 수.

구문: 커서 이름% 속성 이름.

예: 회사가 상장하고 직원의 급여를 인상하기로 결정했습니다. 1년 이상 근무 시 급여 인상액은 100위안이며, 급여 상한선은 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

읽기 위해 루프 커서 사용, 구문: FOR 7674b22ef33c73b930516fd6bc30b7a3 IN 9fb3ebc560db139c4bd95ef3e44784b9LOOP – 데이터의 각 행을 작동합니다. 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;

숨겨진 수식 커서

암시적 커서와 명시적 커서의 차이점: 1. 커서를 선언할 필요가 없습니다. 2. 커서를 열고 닫을 필요가 없습니다. 3. INTO 절을 사용해야 하며, 결과는 하나만 가능합니다.

암시적 커서는 명시적 커서와 동일합니다. 동일한 속성을 갖습니다. 암시적 커서에 대한 속성을 사용하는 방법은 속성 이름 앞에 SQL%, 즉 SQL%FOUND, SQL%를 추가하는 것입니다. 아이소펜 등

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;

암시적 커서 쿼리 결과는 행이 1개뿐이므로 계산에 사용하면 큰 의미가 없으므로 삽입, 삭제, 업데이트 성공 여부를 확인하기 위해 %ROECOUNT 속성을 자주 사용합니다. 이지만 COMMIT 문 Before에서 사용해야 합니다. COMMIT 후에 %ROECOUNT는 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 동적 커서

ref 동적 커서는 런타임에 다른 명령문과 연결될 수 있으며 동적입니다. ref 동적 커서는 다중 행 쿼리 결과 집합을 처리하는 데 사용됩니다. ref 동적 커서는 포인터와 유사한 ref 유형의 변수입니다.

참조 동적 커서 유형 정의: 유형4104eecf4e6ce5619cb13d4684424e03은 참조 커서 반환 ba201cadbb632e36d006c0ff71f12bff;

참조 동적 커서 선언: 4104eecf4e6ce5619cb13d4684424e03

참조 동적 커서 열기: OPENa7cd60d7ac1ccd5236d7a6ac25d79eae FOR 41a33baa1cabaab3fc01293369f91e36

예:

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;

강력한 형식의 참조 동적 커서: REF 동적 커서 RETURN 문.

약하게 형식화된 참조 동적 커서: RETURN 문이 없는 REF 동적 커서입니다.

예:

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;

사용자 입력(직원, 부서)을 기반으로 정보를 인쇄합니다.

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;

동적 SQL 문을 만듭니다.

컴파일 시 결정되는 정적 SQL입니다.

컴파일되지 않은 동적 SQL은 실행 중에 동적으로 결정되며, 사용자 입력 매개변수 등에 따라 SQL 문을 결정할 수 있어 PL/SQL에서 지원되지 않는 DDL 문 문제를 해결합니다.

동적 DML.DDL을 생성하기 위한 SQL 문 구문:

EXECUTEIMMEDIATE 'DML, DDL 문' [INTO8653678379457ed9ce1c2c9ce01536b5] [USING abad75629ba6d95c004be7c425eb05e9]; 한 행 또는 0행을 반환하는 문입니다.

다음 명령문이 Select 문인 경우 into 절을 사용하여 Select 문에서 선택한 레코드 값을 받을 수 있습니다. 일련의 변수일 수도 있고, 레코드 유형 변수인 레코드 유형 변수일 수도 있습니다. SQL 문에 동적으로 결정해야 하는 매개변수가 있는 경우 입력 매개변수 변수를 바인딩하는 데 사용되는 USING 절을 사용합니다. SQL 문에 매개변수가 있는 경우 ":매개변수 이름"을 사용하세요

예: 동적으로 테이블 생성

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

예: 직원의 전화번호를 동적으로 쿼리

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;

예: 동적으로 레코드 삽입

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 문은 하나의 행만 반환하거나 아무것도 반환하지 않을 수 있습니다. 여러 행을 반환하는 SQL 문을 작성하는 경우 해당 구문은 다음과 같습니다. OPEN 커서_이름 FOR 8e69e3fa84db8d9cac5b2b153634346d];

예: 급여가 일정 금액 이상인 사원 정보를 동적으로 출력

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;