PLSql语句学习(二)

plsql中sql语句的运用
select 语句不用游标必须有into 还必须保证有且只有一条记录多了没有都不行
declare
         v_ename emp.ename%type;
         v_sal emp.sal%type;
begin
         select ename,sal into v_ename,v_sal from emp where empno=9999;
         dbms_output.put_line(v_ename||''||v_sal);
end;

declare
          v_deptno emp2.deptno%type :=50;
begin
          update emp2 set sal=sal/2 where deptno=v_deptno;
          dbms_output.put_line(sql%rowcount||'条记录被影响');
commit;
end;
--这里sql是关键字代表刚被执行的SQL语句%rowcount是SQL的属性表示影响了多少条记录

------------DDL语句如建表赋权限 immediate立即的..
begin
         EXECUTE IMMEDIATE 'create table t(nnn varchar(20) default ''aa'')';
end;

注意:DDL语句要加 execute immediate就OK了.

-------------------------------------------------------------------------------------
----------------plsql   循环.分支.......
--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---注意是elsif  不是else if
             dbms_output.put_line('middle');
       else dbms_output.put_line('high');---else后没有then
       end if;
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
          i binary_integer:=1;
begin
          while i<11 loop
          dbms_output.put_line(i);
          i:=i+1;
          end loop;
end;

---------for 循环
begin
        for k in 1..10 loop
             dbms_output.put_line(i);
        end loop;

----从10到1逆序
        for k in reverse 1..10 loop
            dbms_output.put_line(i);
        end loop;
end;

----------错误处理
declare
          v_temp number(4);
begin
           select cmpno into v_temp from emp where deptno=10;--deptno=10会返回多条记录
           exception
              when too_many_rows then----返回多条记录的异常名字是too_many_rows
                        dbms_output.put_line('太多记录了');
              when others then
                        dbms_output.put_line('error');
end;

declare
          v_temp number(4);
begin
          select cmpno into v_temp from emp where deptno=10;--deptno=10会返回多条记录
exception
               when no_data_found then---没有找到数据异常
               dbms_output.put_line('no data');
end;

----以下介绍DBA通常记录错误的做法 先建立一个错误日志表
create table errorlog
(
   id number primary key,
   errcode number,---出错代码
   errmeg varchar2(1024),--出错信息
   errdate date--出错时间
);
--主键自动递增
create sequence seq_errorlog_id start 1 increment by 1;

-----------
declare
          v_teptno 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是oracle关键字 表示出错信息
             insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errrmsg,sysdate);
             commit;
end;

 
-----------cursor 游标是一个指针 指向一个结果集就象迭代器 最开始指向结果集的top
declare
cursor c is
               select * from emp;---申明一个游标 这时候ORACLE并不执行SELECT 只是申明
               v_emp c%rowtype;
begin
        open c;--打开游标ORACLE才执行SELECT
        fetch c into v_emp;--从游标拿出一条数据游标自动往下移一格.. fetch取出的意思
        dbms_output.put_line(v_emp.ename);
        close c;
end;

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 循环
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);
          end loop;
          close c;
end;
------------for 循环 不需要 定义v_emp 也不需要打开关闭游标 都是系统自动帮你做所以FOR循环最简单
declare
          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_dempno emp.deptno%type,v_job emp.job%type)
          is
             select ename,sal from emp where deptno=v_dempno and job=v_job;
begin
          for v_emp in c(30,'clerk') loop
               dbms_output.put_line(v_emp.ename);
          end loop;
end;

-----可更新的游标 一般游标是在SELECT中遍历结果集,有一种不常用的游标
----- 可做修改删除等操作... 关键字 forupdate->为了更新 current of c->当前游标所在这行
declare
          cursor c is
                         select * from emp;
          v_emp c%rowtype;
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;
              end if;
          end loop;
          commit;
end;

----------------存储过程和一般的PLSQL块的区别就是------------------------
---create or replace procedure p is 代替了declare

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=10) then
                update emp2 set sal =sal+20 where current of c;
          else
                update emp2 set sal =sal+40 where current of c;
          end if;
       end loop;
             commit;
end;
--执行过程有2个办法
--1 exec p;
---2 begin
p;
end;
-------------带参数的存储过程 参数类型 in 传入 out 传出 in out 传入传出  不写的话默认是in
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_b;--把传入的值赋给传出值v_ret
         else
               v_ret:=v_b;
         end if;
               v_temp:=v_temp+1;--v_temp即是传入又是传出 所以可以自己给自己赋值
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;

---注意创建存储过程出错系统不会告诉你哪里错了只会警告:创建的过程带有编译错误
--如果想知道出错位置和原因 输入命令:show error

----------------function 必须有返回值--------
create or replace function sal_tax
(v_sal number)  return number
is
begin
        if(v_sal<2000) then
            return 0.1;
        elsif(v_sal<2750) then
            return 0.15;
        else
            return 0.20;
        end if;
end;

--调用函数
select sal_tax(sal) from emp;

-----------------触发器  触发器不能单独执行必须依附在某张表上
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--当在emp2这张表上插入更新删除时候会触发此触发器
--这里状态有after或berore 表示动作之后或之前
begin
        if inserting then--inserting关键字代表当前正在插入操作
               insert into emp2 values(USER,'insert',sysdate);--USER是关键字代表当前用户是谁
        elsif updating then--updating关键字代表当前正在更新操作
               insert into emp2 values(USER,'update',sysdate);
        elsif deleting then--deleting关键字代表当前正在删除操作
               insert into emp2 values(USER,'delete',sysdate);
        end if;
end;
--注意for each row 是每一行都触发触发器比如更新6行触发6次触发器
--如果不加for each row 更新6行只触发1次触发器
--使用触发器 此触发器是在emp2这张表上插入更新删除时候会触发
update emp2 set sal=sal*2 where deptno=30;

--------------触发器的副作用
如:update dept set deptno=99  where deptno=10;
这个SQL语句是不能执行的 但是触发器可以实现

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

----
由此得知先触发触发器后检查完整性约束

 

-----------树状结构的存储与展示
就是字典表 有父ID
create table article
(
id number primary key,
cont varchar2(4000),
pid number,---父ID
isleal number(1),--0代表叶节点 1代表子节点
vlevel number(2)---表示在哪一层
);

insert into article values(1,'蚂蚁大站大象',0,0,0);
insert into article values(2,'大象被大怕下',1,0,1);
insert into article values(3,'蚂蚁也不好过',2,1,2);
insert into article values(4,'乱说',2,0,2);
insert into article values(5,'没有乱说',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么不可能',6,1,2);
insert into article values(8,'可能性很大',6,1,2);
insert into article values(9,'大象死了',2,0,2);
insert into article values(10,'蚂蚁士大夫',9,1,3);
-----用存储过程调用自己来实现递归树

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.isleal=0) then
p(v_article.id,v_level+1);
end if;
end loop;
end;

执行:exec p(0,0);