序列: ORACLE使用來(lái)模擬ID自動(dòng)增長(zhǎng)的

create sequence seq_test4;
create table test2(
 tid number primary key,
 tname varchar2(10)    
);

insert into test2 values(seq_test4.nextval,'張三');
select * from test2;

游標(biāo)(光標(biāo)): 是用來(lái)操作查詢結(jié)果集,相當(dāng)于是JDBC中ResultSet

   語(yǔ)法: cursor 游標(biāo)名[(參數(shù)名 參數(shù)類型)] is 查詢結(jié)果集
   
   開發(fā)步驟:
       1. 聲明游標(biāo)
       2. 打開游標(biāo)       open 游標(biāo)名
       3. 從游標(biāo)中取數(shù)據(jù)  fetch 游標(biāo)名 into 變量
                     游標(biāo)名%found :找到數(shù)據(jù)
                     游標(biāo)名%notfound : 沒有找到數(shù)據(jù) 
       4. 關(guān)閉游標(biāo)       close 游標(biāo)名
       
  系統(tǒng)引用游標(biāo)
       1. 聲明游標(biāo) : 游標(biāo)名 sys_refcursor
       2. 打開游標(biāo): open 游標(biāo)名 for 結(jié)果集
       3. 從游標(biāo)中取數(shù)據(jù)
       4. 關(guān)閉游標(biāo)
            
 for循環(huán)遍歷游標(biāo):
       不需要聲明額外變量
       不需要打開游標(biāo)
       不需要關(guān)閉游標(biāo)      

--輸出員工表中所有的員工姓名和工資(不帶參數(shù)游標(biāo))
游標(biāo):所有員工
聲明一個(gè)變量,用來(lái)記錄一行數(shù)據(jù) %rowtype

declare
 --游標(biāo)
 cursor vrows is select * from emp;
 --s聲明變量,記錄一行數(shù)據(jù)
 vrow emp%rowtype;
begin
 --1.打開游標(biāo)  
 open vrows;
 --2.從游標(biāo)提取數(shù)據(jù)
 --循環(huán)取數(shù)據(jù)
 loop
   fetch vrows into vrow; 
   exit when vrows%notfound;  
   dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
 end loop;
 --3.關(guān)閉游標(biāo)
 close vrows;
end;

--輸出指定部門下的員工姓名和工資
/*
游標(biāo): 指定部門的所有員工
聲明一個(gè)變量記錄一行數(shù)據(jù)
*/

declare
 --聲明游標(biāo)
 cursor vrows(dno number) is select * from emp where deptno = dno;
 --聲明變量
 vrow emp%rowtype;
begin
  --1.打開游標(biāo) , 指定10號(hào)部門
  open vrows(10);
  --2. 循環(huán)遍歷,取數(shù)據(jù)
  loop
 fetch vrows into vrow;
 exit when vrows%notfound;    
  dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
  end loop;
  close vrows;
end;

--系統(tǒng)引用游標(biāo)
--輸出員工表中所有的員工姓名和工資

  declare
  --聲明系統(tǒng)引用游標(biāo)
vrows sys_refcursor;
  --聲明一個(gè)變量
vrow emp%rowtype;
begin
  --1.打開游標(biāo)
  open vrows for select * from emp;
  --2.取數(shù)據(jù)
loop
  fetch vrows into vrow;
  exit when vrows%notfound;
   dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal);
  end loop;
  close vrows;
end;

--擴(kuò)展內(nèi)容----使用for循環(huán)遍歷游標(biāo)

declare
  --聲明一個(gè)游標(biāo)
  cursor vrows is select * from emp;
begin
  for vrow in vrows loop
 dbms_output.put_line('姓名:'||vrow.ename ||' 工資: ' || vrow.sal || '工
作:'|| vrow.job);
  end loop;
end;

select * from emp;

--按照員工工作給所有員工漲工資,總裁漲1000,經(jīng)理漲800,其他人漲400
/*
游標(biāo) : 所有員工
聲明一個(gè)記錄一行數(shù)據(jù)
*/

declare
   --聲明游標(biāo)
   cursor vrows is select * from emp;
   --聲明一個(gè)變量
   vrow emp%rowtype; 
begin
  --1.打開游標(biāo)
  open vrows;
  --2.循環(huán)取數(shù)據(jù)
  loop
   --取數(shù)據(jù)
   fetch vrows into vrow;
   --退出條件
   exit when vrows%notfound;  
   --根據(jù)不同的職位,漲工資 總裁漲1000,經(jīng)理漲800,其他人漲400
   if vrow.job = 'PRESIDENT' then
      update emp set sal = sal + 1000 where empno = vrow.empno;
   elsif vrow.job = 'MANAGER' then
      update emp set sal = sal + 800 where empno = vrow.empno;
   else
      update emp set sal = sal + 400 where empno = vrow.empno; 
   end if;       
  end loop;
  --3.關(guān)閉游標(biāo)
  close vrows;
  --4.提交事務(wù)
  commit;
end;

例外:(意外)程序運(yùn)行的過程發(fā)生異常,相當(dāng)于是JAVA中的異常

 declare
   --聲明變量
  begin
   --業(yè)務(wù)邏輯
 exception
   --處理異常
   when 異常1 then
     ...
   when 異常2 then
     ...
   when others then
     ...處理其它異常
 end;

 zero_divide : 除零異常
 value_error : 類型轉(zhuǎn)換異常
 too_many_rows : 查詢出多行記錄,但是賦值給了rowtype記錄一行數(shù)據(jù)變量
 no_data_found : 沒有找到數(shù)據(jù)
   

 自定義異常:
    異常名  exception;
   raise 異常名          

例

declare
 vi number;
 vrow emp%rowtype;
begin
 --vi := 8/0;  
--vi := 'aaa';
 --select * into vrow from emp;
 select * into vrow from emp where empno=1234567;
exception
when zero_divide then
dbms_output.put_line('發(fā)生了除零異常');
 when value_error then
 dbms_output.put_line('發(fā)生了類型轉(zhuǎn)換異常');
when too_many_rows then
dbms_output.put_line(' 查詢出多行記錄,但是賦值給了rowtype記錄一行數(shù)據(jù)變量');
when no_data_found then
dbms_output.put_line('沒有找到數(shù)據(jù)異常');
when others then
 dbms_output.put_line('發(fā)生了其它異常' || sqlerrm);     
end;  

--查詢指定編號(hào)的員工,如果沒有找到,則拋出自定義的異常
游標(biāo)來(lái)判斷
%found %notfound
聲明一個(gè)游標(biāo)
聲明一個(gè)變量,記錄數(shù)據(jù)
從游標(biāo)中取記錄
如果有,則不管它
如果沒有就拋出自定義的異常

declare
  --聲明游標(biāo)
  cursor vrows is select * from emp where empno=8888;   
--聲明一個(gè)記錄型變量
  vrow emp%rowtype;
  --聲明一個(gè)自定義異常
  no_emp exception;  
begin
  --1.打開游標(biāo)
  open vrows;
  --2.取數(shù)據(jù)
  fetch vrows into vrow;
  --3.判斷游標(biāo)是否有數(shù)據(jù)
  if vrows%notfound then
raise no_emp;
end if;
close vrows;
exception
when no_emp then
dbms_output.put_line('發(fā)生了自定義的異常');
end;

存儲(chǔ)過程: 實(shí)際上是封裝在服務(wù)器上一段PLSQL代碼片斷,已經(jīng)編譯好了的代碼
1.客戶端取調(diào)用存儲(chǔ)過程,執(zhí)行效率就會(huì)非常高效

     語(yǔ)法:
          create [or replace] procedure 存儲(chǔ)過程的名稱(參數(shù)名 in|out 參
數(shù)類型,參數(shù)名 in|out 參數(shù)類型)
          is | as
           --聲明部分
          begin
           --業(yè)務(wù)邏輯 
          end; 

--給指定員工漲薪,并打印漲薪前和漲薪后的工資
參數(shù) : in 員工編號(hào)
參數(shù) : in 漲多少
聲明一個(gè)變量 : 存儲(chǔ)漲工資前的工資
查詢出當(dāng)前是多少
打印漲薪前的工資
更新工資
打印漲薪后的工資

create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
 --聲明變量.記錄當(dāng)前工資
 vsal number;    
begin
  --查詢當(dāng)前的工資
  select sal into vsal from emp where empno = vempno;
  --輸出漲薪前的工資
  dbms_output.put_line('漲薪前:'||vsal);
  --更新工資
  update emp set sal = vsal + vnum where empno = vempno;
  --輸出漲薪后的工資
  dbms_output.put_line('漲薪后:'||(vsal+vnum));
  --提交
  commit;
end;

調(diào)用：
--方式1
call proc_updatesal(7788,10);

--方式2 用的最多的方式
declare

begin
  proc_updatesal(7788,-100);
end;

存儲(chǔ)函數(shù): 實(shí)際上是一段封裝是Oracle服務(wù)器中的一段PLSQL代碼片斷,它是已經(jīng)編譯好了的代碼片段

    語(yǔ)法: 
         create [or replace] function 存儲(chǔ)函數(shù)的名稱(參數(shù)名 in|out 參數(shù)類
型,參數(shù)名 in|out 參數(shù)類型) return 參數(shù)類型
         is | as
         
         begin
           
         end;
    存儲(chǔ)過程和函數(shù)的區(qū)別:
         1.它們本質(zhì)上沒有區(qū)別
         2.函數(shù)存在的意義是給過程調(diào)用   存儲(chǔ)過程里面調(diào)用存儲(chǔ)函數(shù)
         3.函數(shù)可以在sql語(yǔ)句里面直接調(diào)用
         4.存儲(chǔ)過程能實(shí)現(xiàn)的,存儲(chǔ)函數(shù)也能實(shí)現(xiàn),存儲(chǔ)函數(shù)能實(shí)現(xiàn)的,過程也能實(shí)現(xiàn)
         
    默認(rèn)是 in       

--查詢指定員工的年薪
/*
參數(shù) : 員工的編號(hào)
返回 : 年薪
*/

create or replace function func_getsal(vempno number) return number
is
  --聲明變量.保存年薪
  vtotalsal number;     
begin
  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
  return vtotalsal;
end;

--調(diào)用存儲(chǔ)函數(shù)
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

--查詢員工的姓名,和他的年薪
select ename,func_getsal(empno) from emp;

--查詢指定員工的年薪--存儲(chǔ)過程來(lái)實(shí)現(xiàn)
--參數(shù): 員工編號(hào)
--輸出: 年薪

create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)
is
   
begin
  select sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;


declare
  vtotal number;
 begin
  proc_gettotalsal(7788,vtotal);
  dbms_output.put_line('年薪:'||vtotal);
 end;

JAVA調(diào)用存儲(chǔ)過程
JDBC的開發(fā)步驟:
1.導(dǎo)入驅(qū)動(dòng)包
2.注冊(cè)驅(qū)動(dòng)
3.獲取連接
4.獲取執(zhí)行SQL的statement
5.封裝參數(shù)
6.執(zhí)行SQL
7.獲取結(jié)果
8.釋放資源

觸發(fā)器: 當(dāng)用戶執(zhí)行了 insert | update | delete 這些操作之后, 可以觸發(fā)一系列其它的動(dòng)作/業(yè)務(wù)邏輯
作用 :
在動(dòng)作執(zhí)行之前或者之后,觸發(fā)業(yè)務(wù)處理邏輯
插入數(shù)據(jù),做一些校驗(yàn)

   語(yǔ)法:
       create [or replace] trigger 觸發(fā)器的名稱
       before | after
       insert | update | delete 
       on 表名
       [for each row]
       declare
       
       begin
         
       end;
       
   觸發(fā)器的分類:
       語(yǔ)句級(jí)觸發(fā)器:   不管影響多少行, 都只會(huì)執(zhí)行一次
       
       行級(jí)觸發(fā)器:     影響多少行,就觸發(fā)多少次
              :old  代表舊的記錄, 更新前的記錄
              :new  代表的是新的記錄

--新員工入職之后,輸出一句話: 歡迎加入新公司

create or replace trigger tri_test1
after
insert
on emp
declare

 begin
  dbms_output.put_line('歡迎加入新公司');
end;

insert into emp(empno,ename) values(9527,'HUAAN');

--數(shù)據(jù)校驗(yàn), 星期六老板不在, 不能辦理新員工入職
--在插入數(shù)據(jù)之前
--判斷當(dāng)前日期是否是周六
--如果是周六,就不能插入

create or replace trigger tri_test2
before
insert 
on emp
declare
 --聲明變量
 vday varchar2(10);
begin
--查詢當(dāng)前
select trim(to_char(sysdate,'day')) into vday from dual;
  --判斷當(dāng)前日期:
  if vday = 'saturday' then
 dbms_output.put_line('老板不在,不能辦理入職');
 --拋出系統(tǒng)異常
 raise_application_error(-20001,'老板不在,不能辦理入職');
  end if;
end;

insert into emp(empno,ename) values(9528,'HUAAN2');

--更新所有的工資 輸出一句話

create or replace trigger tri_test3
after
update
on emp 
for each row
declare

begin
  dbms_output.put_line('更新了數(shù)據(jù)');
end;

update emp set sal = sal+10;

--判斷員工漲工資后的工資一定要大于漲工資前的工資
200 --> 100
觸發(fā)器 : before
舊的工資
新的工資
如果舊的工資大于新的工資 , 拋出異常,不讓它執(zhí)行成功

觸發(fā)器中不能提交事務(wù),也不能回滾事務(wù)

create or replace trigger tri_updatesal
before
update
on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20002,'舊的工資不能大于新的工資');
  end if;
end;

update emp set sal = sal + 10;
select * from emp;

update emp set sal = sal - 100;

模擬mysql中ID的自增屬性 auto_increment
insert into person(null,'張三');

觸發(fā)器:

pid=1 insert pid=1

序列 : create sequence seq_person_pid;

create table person(
    pid number primary key,
    pname varchar2(20)   
);

insert into person values(null,'張三'); 

create sequence seq_person_pid;

--觸發(fā)器
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare

begin
  dbms_output.put_line(:new.pname);
  --給新記錄 pid 賦值
  select seq_person_pid.nextval into :new.pid from dual;
end;

insert into person values(null,'張三'); 

select * from person;