遊標和動態SQL，遊標動態SQL

遊標和動態SQL，遊標動態SQL

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

動態資料指標分兩種，分別是強型別和弱類型。強型別的動態資料指標只能支援查詢結果與他類型匹配的這種查詢語句，弱類型的動態資料指標可以支援任何的查詢語句。

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

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

顯示遊標的用法步驟：

1、聲明顯式遊標，文法：CURSOR<遊標名>ISSELECT<語句>;

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

2、開啟遊標，從開啟遊標開始，後面的步驟都是在begin和end中執行的。文法：open<遊標名>;當開啟遊標後查詢語句就開始執行了，查詢結果放到Oracle的緩衝區中，然後遊標指向了這個緩衝區中查詢結果的第一行記錄之前。

3、提取遊標，通過提取遊標，遊標依次指向查詢結果的每一行。文法：FETCH<遊標名>INTO<變數列表>;

4、關閉遊標，文法：CLOSE<遊標名>;

樣本：

declare name varchar2(50);                               --定義變數儲存employees表中的內容。 department_name varchar2(20);               --定義變數儲存departments表中的內容； cursor emp_cur IS                               --定義遊標emp_curselect 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、不用聲明遊標。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動態資料指標：<遊標名> <類型名>;

開啟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;

EXECUTEIMMEDIATE語句只能返回一行或沒有返回，如果編寫返回多行的SQL語句，可以使用ref動態資料指標，他的文法：OPEN cursor_name FOR <SQL語句> [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;

動態資料指標 動態sql 是一回事

SQL Server 支援的四種遊標類型是：靜態資料指標、動態資料指標、順向資料指標、索引鍵集驅動資料指標。其中動態資料指標是對結果集可以進行更新，當滾動遊標時，動態資料指標反映結果集中所做的所有更改，結果集中的行資料值、順序和成員在每次提取時都會改變。
靜態伺服器資料指標在 tempdb 中建立整個遊標，索引鍵集驅動資料指標則在 tempdb 中建立鍵集
對於大結果集，使用動態資料指標更為適合。在查詢語句中指定for update就可以更新結果集,或沒有read_only就可以，常規情況下，不必關心是否動態，尚由伺服器隱含轉換的。
動態例子：
DECLARE abc CURSOR LOCAL FOR
SELECT c1, c2
FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1
SET c2 = c2 + d2
FROM dbo.Table2
WHERE CURRENT OF abc;--更新遊標
 
動態資料指標 動態sql 不同