Oracle PL/SQL基础

每种数据库都有自己特有的语言，Oracle下面就是PL_SQL了。它相对于SQL语言来说，提供了分支和循环，可以完成更多的工作。

PL-SQL：---带选择过程的SQL语句。
 各个数据库都有自己的类似语言。PL_SQL是Oracle数据库的。
 SQL> set serveroutput on;输出到服务器命令行端口，默认是off。
 SQL> declare        ---变量声明语句块
   v_name varchar2(20); ---每行只能声明一个变量。
      begin  ---开始执行语句块
  v_name := 'myname';
  dbms_output.put_line(v_name); ---输出语句。
      exception  ---异常处理语句
  when others  then
      dbms_output.put_line('error');
      end;  ---语句执行结束符

常用变量类型:
  binary_integer:整数，主要用来计数而不是用来表示字段类型。
  number：数字类型
  char:定长字符串
  varchar2：变长字符串
  date:日期
  long：长字符串，最长2GB
  boolean:布尔类型。取值（true，false，null）最好给初值。
   --boolean类型不能打印出来。

  字符串连接符：||
  注释一行：--
  注释多行：和Java一样

把程序变量类型和表的字段类型链接起来：
  --变量声明，使用%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;
       ---声明一个新的类型（table相当于数组类型）
   v_empnos type_table_emp_empno; ---用定义的类型声明一个变量。
  begin
       v_empnos(0) := 7369;
       v_empnos(1) := 7839;
   v_empnos(-1):= 9999;   ---数组下标可正可负。
  end;

--Record变量类型：相当于Java里面的类。
  declare
      type type_record_dept is record  ---定义新的变量类型
   (
       deptno dept.deptno%type,
       dname dept.dname%type,
       loc dept.loc%type
   );
   v_temp tpye_record_dept;
  begin
      v_temp.deptno:=50;
   v_temp.dname :='aaa';
   ***
   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 :='aaa';
   ***
   dbms.output.put_line(v_temp.deptno || ''||v_temp.dname);
  end;
PL_SQL的分支和循环：

 declare
  v_sal emp.sal%type;
 begin
  select sal into v_sal from emp
   where empno = 7369;
  if(sal < 1200) then
   dbms_output.put_line('low');
  elsif(v_sal<2000) then  --注意elsif拼写，少一个e.
   dbms_output.put_line('middle');
  else   ---注意else后面不跟then
   dbms_output.put_line('high');
  end if;  ---endif后面要有分号（；）
 end;

循环：
 ---相当于do-while循环
 declare
  i binary_integer := 1;
 begin
  loop   ---循环开始
   dbms_output.put_line(i);
   i := i + 1;
   exit when(i >=11); ---跳出循环的条件
  end loop;  --循环结束
 end;

 --相当于while循环
 declare
  j binary_integer := 1;
 begin
  while j < 11 loop
   dbms_output.put_line(j);
   j := j+1;
  end loop;
 end;
 
 ---相当于for循环
 begin
  for k in 1..10 loop
   dbms_output.put_line(k);
  end loop;

  for k in reverse 1..10 loop ---reverse逆序循环 从10到1.
   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 no_data_found then
   dbms_output.put_line('没数据');
  when others then
   dbms_output.put_line('error');
 end;
DBA通常建立error日志：
 1.建立日志表：
 create table errorlog
 (
 id number primary key,
 errcode number,
 errmsg varchar2(1024),
 errdate date
 );

 2.建立序列
 create sequence seq_errorlog_id start with 1 increment by 1;

 3.PL_SQL中运用
 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;
 --查看出错具体时间
 select to_char(errdate,'YYYY-MM-DD HH24:MI:SS') from errorlog;
游标---PL_SQL里面的重点****:
 declare   ---loop循环
  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循环
 declare   
  cursor c is
   select * from emp;
  v_emp emp%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循环，不容易出错，用得最多。
 declare
  cursor c is
   select * from emp;
 begin
  for v_emp in c loop --不需要定义v_emp,自动打开，关闭c.自动fetch数据。
   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;

 ---可更新的游标
 declare
  cursor c
  is
    select * from emp2 for update; --跟上for update表示可更新游标。
 begin
  for v_temp in c loop
   if(v_temp.sal < 2000) then
     update emp2 set sal = sal * 2 where current of c; ---更新游标所指的当前记录
   elsif (v_temp.sal = 5000) then
     delete from emp2 where current of c;  ---where current of c.
   end if;
  end loop ;
 end;
存储过程

存储过程--起了名字的PL_SQL程序：
 create or replace procedure p
 is
  cursor c is
   selcet * 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;
 
 执行存储过程：
 1.exec p;
 2.begin
     p;
   end;

 --带参数的存储过程
 --in 传入参数，out 传出参数，不写默认为传入参数。一个参数可既为传入参数，又可为传出参数。
 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;

 需注意的是produre创建的过程中如出现语法错误，不会报错，只是出警告。
 需要show error命令把错误show出来。

用存储过程实现蚂蚁大战大象的树状结构的展现：
 create or replace procedure p(v_pid article.pid%type,v_level binary_integer)
 is
   cursor c is select * from article where pid = v_pid;
   v_preStr varchar2(1024) := '';
 begin
   for i in 1..v_level loop
     v_preStr := v_preStr || '***';
   end loop;
  
   for v_article in c loop
     dbms_output.put_line(v_preStr || v_article.cont);
     if(v_article.isleaf = 0) then
  p(v_article.id,v_level + 1);
     end if;
   end loop;
 end;
函数和触发器：

function：函数--有参数和返回值。
 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;

 调用函数--类比组合函数：
 select lower(ename),sal_tax(sal) from emp;

触发器(trigger)：--某个表的某些操作触发触发器内的程式执行。
 1.create table emp2_log
   (
   uname varchar2(20);
   action varchar2(10);
   atime date
   )  ;
 2.建触发器 after ,before--触发的时机。for each row --针对每条记录的触发。
 create or replace trigger trig
   after insert or delete or update on emp2 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;

 drop trigger trig;
 触发器的副作用，一般不使用，了解。--万不得已的情况下使用。
  ---:NEW，：OLD是记录的新状态和旧状态。系统默认名。
 create or replace trigger trig
  after update on dept for each row
 begin
  update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
 end;
  ---用来更新有约束条件的记录，用到此数据的跟着更新。先触发触发器，再检查约束条件。