馬士兵視訊PLSQL程式碼

set serveroutput on;//


--最簡單的語句塊
begin
dbms_output.put_line('HelloWorld!');
end;


--帶宣告的語句塊
declare 
  v_name varchar2(20);
begin
  v_name := 'myname';
  dbms_output.put_line(v_name);
end;




--完整的語句塊
declare
    v_num number := 0;
begin
    v_num := 2/v_num;
    dbms_output.put_line(v_num);
exception
    when others then
    dbms_output.put_line('error');
end;


--變數宣告
declare
   v_temp number(1);     --數字
   v_count binary_integer := 0; --整數
   v_sal number(7,2):= 4000.00;
   v_date date:=sysdate;
   v_pi constant number(3,2) :=3.14;
   v_valid boolean := false;
   v_name varchar2(20) not null := 'myname';
begin
  dbms_output.put_line('v_temp value:' || v_sal); 
end;




--變數宣告，使用%type屬性
declare
   v_empno number(4);
   v_empno2 emp.empno%type;
   v_empno3 v_empno2%type;
begin
   dbms_output.put_line('Test');
end;
 
--table變數
declare 
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
    v_empnos type_table_emp_empno;
begin
    v_empnos(0) := 7369;
    v_empnos(2) := 7839;
    v_empnos(-1):= 9999;
    dbms_output.put_line(v_empnos(-1));
end;


--record變數
declare
  type type_record_dept is record
   (
      deptno dept.deptno%type,
      dname  dept.dname%type,
      loc    dept.loc%type
   );
   v_temp type_record_dept;
begin
   v_temp.deptno:=50;
   v_temp.dname:='aaaa';
   v_temp.loc:='bj';
   dbms_output.put_line(v_temp.deptno || ' ' ||v_temp.dname);
end;


--使用%rowtype宣告record變數
declare
   v_temp dept%rowtype;
begin
   v_temp.deptno:=50;
   v_temp.dname:='aaaa';
   v_temp.loc:='bj';
   dbms_output.put_line(v_temp.deptno || ' ' ||v_temp.dname);
end;


--SQL語句的運用
declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
begin
   select ename,sal into v_ename,v_sal from emp where empno = 7369;
   dbms_output.put_line(v_ename || ' ' || v_sal);
end;




--PL SQL裡建表語句
begin
  execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;


--if 語句
--取出7369的薪水，如果<1200，low  <2000,middle, 否則high
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
                    where empno = 7369;
        if(v_sal < 1200) then
                    dbms_output.put_line('low');
        elsif(v_sal < 2000) then
                    dbms_output.put_line('middle');
        else
     dbms_output.put_line('high');
        end if;
end;


--迴圈
declare
    i binary_integer := 1;
begin
    loop
      dbms_output.put_line(i);
      i:=i+1;
      exit when (i >=11);
    end loop;
end; 




declare
    j  binary_integer := 1;
begin
    while j<11 loop
        dbms_output.put_line(j);
        j:=j+1;
    end loop;
end;






begin
     for k in 1..10 loop
        dbms_output.put_line(k);
     end loop;
     for k in reverse 1..10 loop
        dbms_output.put_line(k);
     end loop;
end;




--錯誤處理
declare
  v_temp number(4);
begin
  select empno into v_temp from emp where deptno = 10;
exception 
  when too_many_rows then
       dbms_output.put_line('太多記錄了');
  when others then
       dbms_output.put_line('error');
end;




declare
   v_temp number(4);
begin
   select empno into v_temp from emp where empno = 2222;
exception
    when no_data_found then
     dbms_output.put_line('沒資料');
end;


---使用一個表記錄錯誤資訊
create table  errorlog
(
id number primary key,
errcode number,
errmsg  varchar2(1024),
errdate date
);


create sequence seq_errorlog_id start with 1 increment by 1;


declare
     v_deptno dept.deptno%type := 10;
     v_errcode number;
     v_errmsg varchar2(1024);
begin
    delete from dept where deptno  = v_deptno;
    commit;
exception 
    when others then
    rollback;
       v_errcode := SQLCODE;
       v_errmsg  := SQLERRM;
    insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg, sysdate);
    commit;
end;






---遊標,重點
declare
   cursor c is
            select * from emp;
   v_emp c%rowtype;
begin
   open c;
     fetch c into v_emp;
     dbms_output.put_line(v_emp.ename);
   close c;
end;


--使用loop和cursor
declare
   cursor c is
            select * from emp;
   v_emp c%rowtype;
begin
   open c;
   loop
      fetch c into v_emp;
      exit when (c%notfound);
      dbms_output.put_line(v_emp.ename);
   end loop;
   close c;
end;




--使用while loop和cursor
declare
   cursor c is
            select * from emp;
   v_emp c%rowtype;
begin
   open c;
     fetch c into v_emp;
     while (c%found) loop
        dbms_output.put_line(v_emp.ename);
        fetch c into v_emp;
     end loop;
   close c;
end;


---使用for 和 cursor,最好使
declare 
    cursor c is
         select * from emp;
begin
    for v_emp in c loop   --v_emp自動宣告，自動next
     dbms_output.put_line(v_emp.ename);
    end loop;
end;


--帶引數的遊標
declare
   cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
   is
      select ename,sal from emp where deptno = v_deptno and job = v_job;
begin
    for v_temp in c(30,'CLERK') loop
      dbms_output.put_line(v_temp.ename);
    end loop;
end;




--儲存過程
create or replace procedure p
is 
    cursor c is 
          select * from emp2 for update;
begin
    for v_emp in c loop
         if (v_emp.deptno = 10) then
               update emp2 set sal = sal+10 where current of c;
         elsif (v_emp.deptno = 20) then
               update emp2 set sal = sal+20 where current of c;  
         else
               update emp2 set sal = sal+50 where current of c;  
  end if;
    end loop;
    commit;
end;


--帶引數的儲存過程
create or replace procedure p
    (v_a in number, v_b number, v_ret out number, v_temp in out number)
is


begin
    if  (v_a > v_b) then
v_ret := v_a;
    else
v_ret := v_b;
    end if;
    v_temp := v_temp +1 ;
end;


呼叫儲存過程
declare
    v_a number := 3;
    v_b number := 4;
    v_ret number;
    v_temp number := 5;
begin
    p(v_a,v_b,v_ret,v_temp);
    dbms_output.put_line(v_ret);
    dbms_output.put_line(v_temp);
end;


--函式
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else 
return 0.20;
end if;
end;




--觸發器
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);




create or replace trigger trig
   after insert or delete or update on emp2 for each row -- for each row 為每一行記錄一次
begin
   if inserting then
        insert into emp2_log values(user, 'insert', sysdate);
   elsif updating then
insert into emp2_log values(user, 'update', sysdate);
   elsif deleting then
insert into emp2_log values(user, 'delete', sysdate);
   end if;
end;


update emp2 set sal = sal*2 where deptno = 30;


update dept set deptno = 99 where deptno = 10; --因為deptno被人蔘考，不可被執行




create or replace trigger trig
    after update on dept for each row
begin
    update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;