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