Oracle（PLSQL）入門學習八（完結篇）

學習視頻：https://www.bilibili.com/video/BV1tJ411r7EC?p=75

游標cursor：用於存放多條數據的容器。需要開始open和關閉close。游標下移使用“fetch...into...”。

declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  for i in 1 .. 3 loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || yb.ename);
  end loop;
  close myCursor;
end;

使用%found和%notfound屬性判斷游標是否有值，使用這兩個屬性的前提是游標必須經過下移。

declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || '-' || yb.ename);
    exit when myCursor%notfound;
  end loop;
  close myCursor;
end;

declare
  cursor myCursor is
    select * from emp;
  yb myCursor%rowtype;
begin
  open myCursor;
  fetch myCursor
    into yb; --游標必須先經過下移，才能使用%found和%notfound屬性
  while myCursor%found loop
    fetch myCursor
      into yb;
    dbms_output.put_line(yb.empno || '-' || yb.ename);
  end loop;
  close myCursor;
end;

智能游標：變數定義、自動開關、自動下移。

--智能游標
--變數自動定義
--游標自動開
--游標自動下移
--自動關閉

declare
  cursor myCursor is
    select * from emp;
begin
  for employee in myCursor loop
    dbms_output.put_line(employee.empno || '-' || employee.ename);
  end loop;
end;

異常：無法預測的程式錯誤。

declare
  employee emp%rowtype;
begin
  select * into employee from emp where empno = 70369;
exception
  when no_data_found then
    dbms_output.put_line('找不到數據');
  when others then
    dbms_output.put_line('預設異常，，，');
end;

存儲過程：遠程發送存儲過程名，不需要發送具體的sql，避免發送過程中被截取、篡改sql。優點：提高效率，且更加安全。

create or replace procedure getmax(x number, y number) is
begin
  if x > y then
    dbms_output.put_line(x);
  else
    dbms_output.put_line(y);
  end if;
end;

call getmax(10,12);
exec getmax(11,12);
execute getmax(45,56);

調用存儲過程三種方法call、exec、execute。

存儲過程參數模式：in表示只讀，out表示只寫，in out表示可讀可寫。

--參數變數 參數模式 參數類型。 預設模式為in。
create or replace procedure getmax(x in number, y in number,z out number) is
begin
  if x > y then
    z:=x;
  else
    z:=y;
  end if;
end;

declare
  max_result number;
begin
  getmax(89, 85, max_result);
  dbms_output.put_line(max_result);
end;

函數：和存儲過程不同的是可以返回值。

create or replace function fgetmax(x number, y number) return number is
begin
  if y > x then
    return y;
  else
    return x;
  end if;
end;
/

select fgetmax(45,44) from dual;

 定時任務：使用dbms_job包創建定時任務。sumbit提交定時任務，run運行定時任務，任務編號是自動生成的，這個任務編號很重要，最好記錄下來，刪除定時任務需要任務編號。如果不知道編號是多少，只能手動找到dbms_job下的對應定時任務，然後移除。移除使用remove方法。

create table xperson(
id number primary key,
name varchar2(30)
)

create sequence seq_id;

create or replace procedure addxperson is
begin
  insert into xperson values (seq_id.nextval, 'bibi');
end;

declare
  taskid binary_integer;
begin
  dbms_job.submit(taskid,
                  'addxperson();',
                  sysdate,
                  'sysdate+10/(24*60*60)');
  dbms_output.put_line(taskid);
  dbms_job.run(taskid);
end;

觸發器：某個事件引發些什麼操作。DML操作，DDL操作，資料庫的啟動和關閉可以觸發。加for each row表示行級觸發器，每增刪改一條數據，就觸發一次，多條就多次。不加 for each row，則表示表級觸發器

insert into xperson values(99,'bibi');

--不需要is 行級觸發器，xperson表每增加一條數據，觸發一次
create or replace trigger triggerone after insert on xperson
for each row
begin
  dbms_output.put_line('數據添加了');
end;


--不需要is 行級觸發器，xperson表每刪除一條數據，觸發一次
create or replace trigger triggerone after delete on xperson
for each row
begin
  dbms_output.put_line('數據添加了');
end;

觸發器的新舊數據獲取： “:old.列名”，“:new.列名”。行級觸發器才有新舊數據，表級觸發器沒有。insert操作只有新數據，沒有舊數據。update操作有舊有新。

delete只有舊數據。

--行級觸發器才有新舊數據
create or replace trigger triggerone after update on xperson
for each row
begin
  dbms_output.put_line('就名字' || :old.name || ' 新數據' || :new.name);
end;

update xperson set name = 'bibibiib';