遊標和動態SQL

遊標類別：靜態遊標（指在編譯的時候，遊標就與一個select語句進行了靜態綁定的遊標，這種遊標只能作用於一個查詢語句）和動態遊標（就是希望我們的查詢語句在運行的時候才跟遊標綁定，為了使用動態遊標，必須宣告遊標變數）。

動態遊標分兩種，分別是強型別和弱型別。強型別的動態遊標只能支援查詢結果與他型別相符的這種查詢語句，弱型別的動態遊標可以支援任何的查詢語句。

靜態遊標分為兩種，隱式遊標和顯示遊標。顯示遊標是有使用者聲明和操作的一種遊標。隱式遊標是Oracle為所有的資料操作語句自動宣告的一種遊標。

在每個使用者的會話中，我們可以同時開啟多個遊標，這個數量有資料庫初始化參數檔案中的OPEN CURSORS這個參數來定義。

顯示遊標的用法步驟：

1、聲音明顯式遊標，語法：CURSORISSELECT;

在聲明遊標的時候通常還要宣告一些變數用來存放查詢語句產生的查詢結果。聲明遊標和變數都在declare的。通常先宣告變量，在宣告遊標。

2、打開遊標，從打開遊標開始，後面的步驟都是在begin和end中執行的。語法：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,游標是否指向有效行；%NOTFOUND，遊標是否沒有指向有效行；%ROWCOUNT，遊標抽取過的行數。

語法：遊標名%屬性名。

例如：公司上市，決定給員工提高薪資，入職時間超過1年漲100，1000元封頂。

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 <類型> IN <遊標名>LOOP –操作各行資料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、不用隱式遊標

隱式遊標與顯示遊標的區別：1、不用聲明遊標。 2、不用開啟和關閉遊標。 3.必須使用INTO子句，結果只能是一條。

隱式遊標與顯示遊標的相同的：有相同的屬性，隱式遊標使用屬性的方法是在屬性名稱前面加上SQL%，即SQL%FOUND，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||’在’||department_name);
END;

因為隱式遊標查詢結果只有一行，所以如果用來計數沒有多大的意義，所以%ROECOUNT這個屬性常用來判斷插入、刪除、更新是否成功，但是要在COMMIT語句之前。如果在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類型的變量，類似於指標。

定義ref動態遊標類型：type<類型名稱> is ref cursor return <返回類型>;

聲明ref動態遊標：<遊標名稱> <類型名稱>;

開啟動態遊標：OPEN<遊標名> FOR <查詢語句>;

範例：

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語句的REF動態遊標。

弱型別ref動態遊標：不帶有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語句’;[INTO<變數序列>] [USING <參數序列>];只能執行傳回一行或0行的語句。

如果後面的語句是個select語句，則可以使用into子句用來接收select語句所選擇的記錄值。可以是變數序列，或是一個記錄型變數也就是record型的變數。如果SQL語句中有參數需要動態決定，那麼我們使用USING子句，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;

EXECUTEIMMEDIATEATE ，如果寫一個傳回多行的SQL語句，可以使用ref動態遊標，他的語法：OPEN cursor_name FOR [USING <參數序列>];

範例：動態輸出薪資大於某個金額的員工資訊

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;

🎜🎜