Oracle PLSQL语句实例

/**   * plsql:某个项目对效率要求比较高的时候用，一般不用，大多数项目写的是夸数据库平台的，用不上。   * pssql大多数能做的东西，java都能替代它。在某些特殊的地方要求用plsql的时候你才会用。   *    * 变量声明的规则：   * 1、变量名不能使用保留字，如from、select等   * 2、第一个字符必须是字母   * 3、变量名最多包含30个字符   * 4、不要与数据库的表或者列同名   * 5、每一行只能声明一个变量   *    * 常用变量类型   * 1、 binary_integer:整数，主要用于计数而不是用来表示字段类型   * （Oracle本身非常注重效率，他的很多设计都是为了效率）   * 2、number：数字类型   * 3、char：定长字符串   * 4、varchar2:变长字符串   * 5、date：日期   * 6、long：长字符创，最长2GB   * 7、boolean：布尔类型，可以取为true、false和null值   *    */    set serveroutput on;--默认是关闭的  begin      dbms_output.put_line('HelloWorld');  end;--输出HelloWorld    declare      v_name varchar2(20);  begin      v_name:='mynaem';      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_tem 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; --sql里面也有常量      v_valid boolean:=false; --不能打印布尔类型的值      v_name varchar2(20) not null:='MyName'; --定义变量还能有限制  begin      dbms_output.put_line('v_temp value:' || v_temp);  end;    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表示我定义了一种类型,名称叫tyep_table_emp_empno       * is table说明这是一个数组，这个数组里面装着emp.empno%type类型的数据       * index by说明他的下表是由binary_ingeger来索引       */      type tyep_table_emp_empno is table of emp.empno%type index by binary_ingeger;      v_empnos type_table_emp_empno;  begin      v_empnos(0):=7369;      v_empnos(2):=7839;      v_empnos(-1):=9999;--在Oracle里面，这个下标值可以取负值      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;--更具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     declare      v_deptno emp2.deptno%type:=10;      v_count number;  begin      update emp2 set sal=sal/2 where deptno=v_deptno;--update、insert、delete可以直接用      select deptno into v_deptno from emp2 where empno=7369;--select语句必须和into一起使用且保证有且只有一条记录      select count(*) into v_count from emp2;      dbms_output.put_line(sql%rowcount||'条记录被影响');--rowcount代表刚刚执行完的这条sql语句到底影响了多少条记录                                                      --sql代表的是刚刚执行完的那条sql语句      commit;--用完之后要commit  end     /**   * plsql中的判断   */  begin--create table语句在plsql里面不能单独执行      execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';  end     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 --小心elsif中间没有e          dbms_output.put_line('middle');      else          dbms_output.put_line('high');      end if;  end     /**   * plsql 中的循环   */  declare      i binary_integer:=1;  begin      loop --plsql里面的循环以loop开头，以end 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 --相当于java里面增强的for循环          dbms_output.put_line(k);      end loop;            for k in reverse 1..10 loop          dbms_output.put_line(k);      end loop;  end     /**   * plsql里面的错误处理   * too_many_rows, no_data_found   * 这个东西了解下就够了   */  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     /**   * 创建记录错误的日志表   */  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;--SQLCODE代表出错的代码，Oracle里面出错代码都是负数              v_errmsg:=SQLERRM;--SQLERRM代表出错信息          insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);      commit;  end     /**   * 游标   */  declare--loop循环      cursor c is select * from emp;      v_emp c%rowtype; --将v_emp定义为游标c的类型  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     declare--while循环      cursor c is select * from emp;      v_emp c%rowtype; --将v_emp定义为游标c的类型  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     declare--for循环:cursor 不需要打开或者关闭，所以for循环是我们平时用得最多的循环      cursor c is select * from emp;  begin      for v_emp in c loop;      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;  begin      for v_temp in c loop          if(v_temp.sal<2000)then              update emp2 set sal=sal*2 where current of c;          elseif(v_temp.sal=5000)then              delete from emp2 where current of c;          end if;      end loop;      commit;  end    /**   * 创建存储过程(存储过程创建了不代表她已经执行了)   * Oracle的存储过程，即便我们有语法错误，依然会被创建。执行的时候会报错。   */  create or replace procedure p  is      cursor c is          select * from emp2 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;          elseif(v_temp.sal=5000)then              delete from emp2 where current of c;          end if;      end loop;      commit;  end    exec p;--执行存储过程    /**   * 带参数的存储过程   *  in是传入参数   *  out是传出参数（存储过程是没有返回值的）   *  如果没有写，默认是in   *  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     /**   * 创建函数   */  create or replace function sal_tax      (v_sal number)      return number  is  begin      if(v_sal < 2000) then          return 0.10;      elseif(v_sal<2750) then          return 0.15;      else          return 0.20;      end if  end     --调用函数  select lower(ename),sal_tax(sal)from emp;    /**   * 创建触发器   */  create tabel emp2_log  (      uname varchar2(20),      action varchar2(10),      atime date  );    create or replace trigger trig      /**       * 触发器不能直接执行，必须依附在某张表上       * 可以有after也可以有before insert       * 如果没有for each row则整个操作会被触发一次       * 如果用for each row，如果一个操作更新了六行则被触发六次       */      after insert or delete or update on emp2 for each row  begin      if inserting then          insert into emp2_log values(USER,'insert',sysdate);      elseif updateing then          insert into emp2_log values(USER,'update',sysdate);      elseif deleting then          insert into emp2_log values(USER,'delete',sysdate);      end if;  end     /**   * 触发器的一个副作用（不重要，了解即可。万不得已的情况下用）   * 从下面例子可以看出当我们update一个语句的时候   * 是先触发触发器   */  update dept set deptno=99 where deptno=10;--这样做会报错，违反了完整约束条件  --建立下面触发器以后就可以了（因为对参考dept表的emp表里面的值也做了修改）  create or replace trigger trig      after update on dept      for each row  begin      update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;  end     /**   * 树状结构的存储与展示   */  create table article  (      id number primary key,      cont varchar2(4000),      pid number,      isleaf number(1), --0代表非叶子节点，1代表叶子节点      alevel number(2)  );  --查询上表中某条记录的孩子节点  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