PL/SQL入门

--声明变量

  --普通变量
    v_name varchar2(12):='aaa';
    v_count number(4);

  --通过%TYPE属性为变量声明类型
   v_ename emp.ename%type;
   v_sal emp.sal%type;
   
--*************************************************************************************************************
--利用匿名块从数据库中查据输出到控制台上  
declare
    v_deptno dept.deptno%type;
    v_dname  dept.dname%type;
    v_loc    dept.loc%type;
begin
    select t.deptno,t.dname,t.loc into v_deptno,v_dname,v_loc  from dept t where t.deptno=10;
    dbms_output.put_line('v_deptno='||v_deptno);
    dbms_output.put_line('v_dname='|| v_dname);
    dbms_output.put_line('v_loc='|| v_loc);
end;

----利用匿名块插入数据到数据库中 
declare
begin
   insert into dept(deptno,dname,loc)values(50,'java','shanghai');
   commit;
end;


--*************************************************************************************************************
--if  then end if 
--如果名字是monday，输出hello monday
declare
 v_name  varchar2(12):='monday';
begin
  if v_name='monday' then
     dbms_output.put_line('hello monday');
  end if;
end; 

--if-then-else 
--如果名字是monday，输出hello monday,否则输出hello
declare
  v_name varchar2(12):='java';
begin
  if v_name='monday' then
     dbms_output.put_line('hello monday');   
  else
     dbms_output.put_line('hello');     
  end if;
end;


--if-then-elsif--else 
--如果名字是monday，输出hello monday,如果名字是java，输出hello java,否则输出hello
declare
  v_name  varchar2(12):='sql';
begin
  if v_name='monday' then
     dbms_output.put_line('hello monday');
  elsif v_name='java' then
     dbms_output.put_line('hello java');   
  else
     dbms_output.put_line('hello');
  end if;
end;
--**********************************************************************************************************
--loop
--循环输出从0-5  步长是1
declare
   v_i integer:=0;
begin
   loop 
       dbms_output.put_line(v_i);
       v_i:=v_i+1;
       exit when (v_i>5);
   end loop;
end;

--***********************************************************************************************************
--for  
--输出从0-5 步长是1
--正向输出
begin
  for v_i in 0..5 loop
      dbms_output.put_line(v_i);
  end loop;
end;

--反向输出
begin
  for v_i in reverse 0..5 loop
      dbms_output.put_line(v_i);
  end loop;
end;

--***********************************************************************************************************
--while
--循环输出0-5 步长是1
--正向输出
declare
  v_i integer:=1;
begin
  while v_i>0 loop
       dbms_output.put_line(v_i);
       v_i:=v_i+1;
      exit when (v_i>5); 
  end loop;
end;

--反向输出
declare
  v_i integer:=5;
begin
  while v_i>0 loop
       dbms_output.put_line(v_i);
       v_i:=v_i-1;
  end loop;
end;


--***********************************************************************************************************
--存储过程

--插入数据的存储过程,参数有外部传入
create or replace procedure insertProc
(
      v_deptno in dept.deptno%type,
      v_dname  in dept.dname%type,
      v_loc    in dept.loc%type
)
is
begin
      insert into dept(deptno,dname,loc) values(v_deptno,v_dname,v_loc);
      commit;
end;

--调用存储过程
execute insertProc(60,'oracle','beijing'); --要使用命令行执行

--带返回值的更新的存储过程,更新数据到dept表中
create or replace procedure updateProc
(
       v_deptno in dept.deptno%type,
       v_dname  in dept.dname%type,
       v_loc    in dept.loc%type,
       v_result out varchar2
)
is
begin
    update dept set dname=v_dname,loc=v_loc where deptno=v_deptno;
    commit;
    v_result:='update success';
end;

select * from dept;
--***********************************************************************************************************
--存储过程的应用案例

--账户表
create table t_account
(
     id       varchar(20), --帐号
     balance  number(9,2)  --余额
);
--存款表
create table t_inaccount
(
     id         varchar(20), --帐号
     inbalance  number(9,2)  --存入金额
);
--插入一个帐号
insert into t_account(id,balance) values('9999',1000);
commit;

--创建存款的存储过程

create or replace procedure accountProc
(
    v_id              in        t_inaccount.id%type,
    v_inbalance       in        t_inaccount.inbalance%type
)
is
    v_balance    t_account.balance%type;  --保存账户表的金额
begin
    --往存款表插入存款
    insert into t_inaccount(id,inbalance)values(v_id,v_inbalance);
    --查询账户表原来的金额
    select balance into v_balance from t_account  where id=v_id;
    --现在账户表的金额=原来的金额+新存款的金额
    v_balance:=v_balance+v_inbalance;
    --更新账户表的金额为现在账户表的金额
    update t_account  set balance=v_balance where id=v_id;
    --提交
    commit;
end;
--***********************************************************************************************************

--函数
--根据值返回税额
create or replace function tax(v_value in number)
   return number
is
begin
     if (v_value<1000) then 
        return v_value*0.1;
     elsif (v_value>=1000 and v_value<=2000) then
        return v_value*0.2;  
     else
        return v_value*0.3;   
     end if;   
end;   
select tax(1300) from dual;      
  
--***********************************************************************************************************
--触发器
--语句级DML触发器 
create or replace trigger deptTrigger
   before
   insert or update or delete
   on dept
begin
   dbms_output.put_line('调用了语句触发器');
end;   

update dept set loc='shanghai' where deptno=70;
     
--行级DML触发器
create or replace trigger deptTriggerRow
     before
     insert or update or delete
     on dept
     for each row
begin
     dbms_output.put_line('调用了行级触发器');
end;         
  
update dept set loc='shanghai' where deptno=70;
  

--***********************************************************************************************************
--触发器的应用1 
create table t_user
(
       id    integer ,
       username varchar2(20),
       password varchar2(20),
       primary key(id)
);

create table t_login
(
       id       integer ,
       username varchar2(20),
       password varchar2(20),
       primary key(id)
);

create or replace trigger insertLogin
  before
  insert
  on t_user
  for each row
begin
   insert into t_login(id,username,password)values(:new.id,:new.username,:new.password);
end;  
insert into t_user(id,username,password)values(1,'aaa','123');

--触发器的应用2
create or replace trigger deleteLogin
  before 
  delete
  on t_user
  for each row
begin
   delete from t_login where id=:old.id;
end;  

delete from t_user where id=1;


--***********************************************************************************************************
--游标
select * from dept; --游标就是结果集

--声明游标
v_deptno dept.deptno%type;
v_dname  dept.dname%type;
v_loc  dept.loc%type;

cursor deptCursor is select detpno,dname,loc from dept where deptno=10 ;      
             
--打开游标将执行查询和取出结果集
open deptCursor;

--检索当前记录的值到输出变量中。
fetch deptCursor into v_deptno,v_dname,v_loc;

--在对查询到的所有记录的处理完成后，关闭游标
close deptCursor;

--**********************************************************************************************************
--单行游标
declare
  v_deptno dept.deptno%type;
  v_dname dept.dname%type;
  v_loc dept.loc%type;
  cursor deptCursor is select * from dept where deptno=10; 
begin
   open  deptCursor;
         fetch deptCursor into v_deptno,v_dname,v_loc;
         dbms_output.put_line(v_deptno || ' ' || v_dname || ' ' || v_loc);
   close deptCursor;
end;

--多行游标
declare
  v_deptno dept.deptno%type;
  v_dname  dept.dname%type;
  v_loc    dept.loc%type;
  cursor  deptCursor is select * from dept;
begin
   open  deptCursor;
     --是否打开游标
     if deptCursor %isopen then
       dbms_output.put_line('-----------游标打开-----------');
     end if;
     loop
       fetch deptCursor into v_deptno,v_dname,v_loc;
        --是否有数据提取
       if deptCursor %found then
          dbms_output.put_line('有数据');
       elsif  deptCursor % notfound then  
          dbms_output.put_line('无数据');
       end if;
       exit when (deptCursor %notfound);
       dbms_output.put_line('第' || deptCursor%rowcount || '行：' || v_deptno || ' ' || v_dname || ' ' || v_loc);
     end loop;
   close deptCursor;
   dbms_output.put_line('-----------游标关闭-----------');
end;

--*********************************************************************************************
--异常
declare
   v_deptno dept.deptno%type;
   v_dname dept.dname%type;
   v_loc dept.loc%type;
begin
   select * into v_deptno,v_dname,v_loc from dept ;
   --select * into v_deptno,v_dname,v_loc from dept where deptno=99;
exception
 when no_data_found then
   rollback;
   dbms_output.put_line('没有数据');   
 when too_many_rows then
    rollback;
   dbms_output.put_line('数据过多');
 when others then
   rollback;
   dbms_output.put_line('其他异常');
end;

-----------------------------------------------------

declare
  v_id integer:=4;
  v_name varchar(20):='ddd';
begin
  insert into test(id,name)values(v_id,v_name);
  commit;
end;
---------------------------------------------------------------
declare
 v_id integer:=4;
begin
 delete from test where id=v_id;
 commit;
end;

declare
   v_count integer:=0;
begin
  if v_count > 3 then
     dbms_output.put_line('>3');
  elsif  v_count >=1 and v_count <=3 then
     dbms_output.put_line('>=1 and <=3');
  else   
     dbms_output.put_line('<1');
  end if;
end;
---------------------------------------------------------------
declare
   v_num integer:=100;
   v_result varchar2(10);
begin
   v_result:=
     case v_num
       when 1 then 'one'
       when 2 then 'two'
       when 3 then 'three'
       else 'unknow'
     end;
   dbms_output.put_line('v_num=' || v_num || ' v_result=' || v_result);  
end;
---------------------------------------------------------------
declare
   v_i integer:=1;
begin
  loop
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
    exit when (v_i>5);
  end loop;
end;
---------------------------------------------------------------
declare
  v_i integer:=1;
begin
   while v_i>0 loop
     dbms_output.put_line(v_i);
     v_i:=v_i+1;
     exit when (v_i>5);
   end loop;
end;
---------------------------------------------------------------
declare
  v_i integer:=1;
begin
  for v_i in /*reverse*/ 1..5 loop
    dbms_output.put_line(v_i);
  end loop;
end;
/* PL/SQL 中有goto */
---------------------------------------------------------------
declare
   v_id test.id%type;
   v_name test.name%type;
   cursor cursor_test is select id,name from test;
begin
   open  cursor_test;
     fetch cursor_test into v_id,v_name;
     while cursor_test %found loop
        dbms_output.put_line(v_id || '---' || v_name);
        fetch cursor_test into v_id,v_name;
     end loop;
   close cursor_test;
end;
---------------------------------------------------------------