PL/SQL我再改

--有什么用：业务逻辑写在存储过程里面。（效率高，隐藏业务逻辑，没法跨数据库平台）
--学习方法：代码读懂，运行一遍。
--重点：
--存储过程(带名字的plsql程序块)-带参数的存储过程
--函数(怎样自定义函数)
----扩展的内容：靠java程序实现一个自定义的函数（创建一个简单的Java函数.doc）
--触发器(触发器为什么能影响外键约束)
----update dept set deptno = 99 where deptno = 10;
----triger执行完,才会检查数据完整性
--树状结构的存储与展示-id-cont-pid-isleaf-alevel

select * from dept;

declare - 可选
 声明各种变量或游标的地方。
begin - 必要
 开始执行语句。
 --单行注释语句用两个连在一起的'-'表示。
 /*多行注释语句，
 可以换行*/
exception - 可选
 出错后的处理。
end; - 必要（请注意end后面的分号）
 结束。

-----------------------------------------------------------------------------
--最简单的语句块
begin
 dbms_output.put_line('HelloWorld!');
end;

set serveroutput on;

-------------------------------------------------------------------------------
--简单的PL/SQL语句块

declare
 v_name varchar2(20);
 
begin
 v_name := 'myname';
 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;

--在变量声明时需要遵守一些基本的规则：
1. 变量名不能够使用保留字，如from 、select等
2. 第一个字符必须是字母
3. 变量名最多包含30个字符
4. 不要与数据库的表或者列同名
5. 每一行只能声明一个变量
--PL/SQL中的变量类型主要有以下几种：
1. binary_integer：整数，主要用来计数而不是用来表示字段类型
2. number：数字类型
3. char：定长字符串
4. varchar2：变长字符串
5. date：日期
6. long：长字符串，最长2GB
7. boolean：布尔类型，可以取值为true、false和null值

--变量声明
declare
 v_temp 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; --相当于java里面的final
 v_valid boolean := false;
 v_name varchar2(20) not null := 'MyName';
begin
  --v_pi :=5.8;
  --v_name :=null;
 dbms_output.put_line('v_temp value:' || v_temp); --字符串与boolean拼接会出错
end;

--变量声明，使用%type属性
declare
    v_empno number(4);
 v_empno2 emp.empno%type; --好处,表的定义变了,这里跟着变
 v_empno3 v_empno2%type;
begin
    dbms_output.put_line('Test');
end;

--简单变量赋值
declare
 v_name varchar2(20);
 v_sal number(7,2);
 v_sal2 number(7,2);
 v_valid boolean := false;
 v_date date;
begin
 v_name := 'MyName';
 v_sal := 23.77;
 v_sal2 := 23.77;
 v_valid := (v_sal = v_sal2);
 v_date := to_date('1999-08-12 12:23:38', 'YYYY-MM-DD HH24:MI:SS');
  dbms_output.put_line(v_valid);
end;

--复合变量
--Table变量类型
--相当于java里面的数组
declare
       --声明了一个数组类型,约定俗成以type_开头
       --下标的类型是binary_integer类型
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
 v_empnos type_table_emp_empno;
begin
  v_empnos(0) := 7369;
 v_empnos(2) := 7839;
 v_empnos(-1) := 9999; --下标可以取负值
 dbms_output.put_line(v_empnos(1));
end;

--Record变量类型
--相当于java里面的类
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;
begin
    v_temp.deptno := 50;
 v_temp.dname := 'aaaa';
 v_temp.loc := 'bj';
 dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

--SQL语句的运用
--select必须返回一条记录并且只能返回一条记录
--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 = 7369;
 dbms_output.put_line(v_ename || ' ' || v_sal);
end;
--ORA-01403: 未找到数据
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_ename emp.ename%type;
 v_sal emp.sal%type;
begin
  select ename,sal into v_ename,v_sal from emp where deptno = 30;
 dbms_output.put_line(v_ename || ' ' || v_sal);
end;

--v_emp可以存贮一条记录
declare
 v_emp emp%rowtype;
begin
 select * into v_emp from emp where empno = 7369;
 dbms_output.put_line(v_emp.ename);
end;

 

--insert
create table dept2 as select * from dept;
declare
    v_deptno dept.deptno%type := 50;
 v_dname dept.dname%type := 'aaaa';
 v_loc dept.loc%type := 'bj';
begin
    insert into dept2 values (v_deptno, v_dname, v_loc);
 commit;
end;

select * from dept2;
select * from emp2;

update emp2 set deptno=(select deptno from emp where emp.empno=emp2.empno);
--sql%rowcount
declare
 v_deptno emp2.deptno%type := 30;
 v_count number;
begin
    update emp2 set sal = sal/2 where deptno = v_deptno;
    delete from emp2 where deptno = 10;
 --select deptno into v_deptno from emp2 where empno = 7521 ;
-- select count(*) into v_count from emp2;
 
 dbms_output.put_line(sql%rowcount || '条记录被影响');--sql%rowcount是指离得最近的sql影响的行数
 --commit;
end;

rollback;

--PL/SQL使用DDL语句
--必须写execute immediate......
begin
    execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;

drop table T;
-------------------------------------------------------------------------------------------------------------------

--if语句
--取出7369的薪水，如果<1200，则输出'low'，如果<2000则输出'middle'，否则'high'
--注意elsif的写法
--注意else后面没有then
--注意end if后面有一个分号
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
  dbms_output.put_line('middle');
 else
  dbms_output.put_line('high');
 end if;
end;

--IF
--FI

--循环
--第一种
declare
   i binary_integer := 1;
begin
   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
      dbms_output.put_line(k);
   end loop;
  
   for k in reverse 1..10 loop
      dbms_output.put_line(k);
   end loop;
end;

--错误处理1
--这条select,返回多条记录,会产生异常
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; 
--错误处理2
declare
   v_temp number(4);
begin
   select empno into v_temp from emp where empno = 2222;
exception
   when no_data_found  then
       dbms_output.put_line('没数据');
end;
--------------------------------------------------------------------------------------------------------------------
--游标 (重点）

--open c 的时候,才执行select语句
declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 fetch c into v_emp;
 dbms_output.put_line(v_emp.ename);
  fetch c into v_emp;
 dbms_output.put_line(v_emp.ename);
 close c;
end;
--最常用的游标属性有以下四个：
--1. %isopen，boolean类型变量，用来代表游标是否打开。
--2. %notfound，boolean类型变量，如果最近的fetch语句没有返回一条记录，取true。
--3. %found，boolean类型变量，如果最近的fetch语句取到了记录，取true。
--4. %rowcount，number类型变量，用来代表目前fetch到的记录的总行数。

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);
   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
      dbms_output.put_line(v_emp.ename);
   end loop;
end;

--带参数的游标
--声明两个行参
--v_temp c%rowtype;--不需要声明
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;
   --v_temp c%rowtype;
begin
   --open c(30, 'CLERK');
   for v_temp in c(10, 'CLERK') loop
      dbms_output.put_line(v_temp.ename);
   end loop;
  
end;

--可更新的游标
--用的不多
--注意:for update
--注意游标的用法:where current of c,
--判断两个值是不是相等,用一个等号
declare
   cursor c
   is
      select * from emp2 for update;
   --v_temp 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;
update emp2 set sal=(select sal from emp where emp.empno=emp2.empno)
declare
   cursor c
   is
      select * from emp2 ;
   --v_temp c%rowtype;
begin
 
   for v_temp in c loop
      if(v_temp.sal < 2000) then
      update emp2 set sal = sal * 2 where empno=v_temp.empno;
   elsif (v_temp.sal = 5000) then
      delete from emp2 where  empno=v_temp.empno;
   end if;
   end loop;
   commit;
end;
----------------------------------------------------------------------------------------------------------------------
--存储过程
--带名字的plsql程序块
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 = 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;

--调用办法
exec p;

select * from emp2;

begin
  p;
end;

 

--带参数的存储过程 (stored procedure)
--in,传进来的参数
--out,传出的参数
--什么都不写,默认是in
--存储过程形参number varchar2 不允许指定长度
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;
--即使有错误也不报错
--shwo error

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 -- number sal_tax(number v_sal)
 (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;
create or replace function covert_date
(v_date date)
return varchar2
is
begin
     return to_char(v_date,'YYYY-MM-DD HH24:MI:SS');
end;
--调用方法如下
select empno,ename,covert_date(hiredate) from emp;
------------------------------------------------------------------------------------------------------------
--触发器
drop table emp2_log;
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);

--DML触发器
--USER   代表当前用户
--有after,有before

create or replace trigger trig
   after insert or delete or update on emp2 for each row --定义了trigger触发的时机
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;

update emp2 set sal = sal*2 where deptno = 30;
--for each row 每更新一行就会触发操作,如果不写,....只产生一行
select * from emp2_log;

--##########################################--
update dept set deptno = 99 where deptno = 10;

drop trigger trig;

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

select * from emp;
--triger执行完,才会检查数据完整性,所以可以执行update dept set deptno = 99 where deptno = 10;
rollback;
--##########################################--
----------------------------------------------------------------------------------------------------------------
--树状结构的存储与展示
drop table article;

create table article
(
id number primary key,
cont varchar2(4000),
pid number
);

insert into article values (1, '蚂蚁大战大象', 0);
insert into article values (2, '大象被打趴下了', 1);
insert into article values (3, '蚂蚁也不好过', 2);
insert into article values (4, '瞎说', 2);
insert into article values (5, '没有瞎说', 4);
insert into article values (6, '怎么可能', 1);
insert into article values (7, '怎么没有可能', 6);
insert into article values (8, '可能性是很大的', 6);
insert into article values (9, '大象进医院了', 2);
insert into article values (10, '护士是蚂蚁', 9);

select * from article;

蚂蚁大战大象
----大象被打趴下了
--------蚂蚁也不好过
--------瞎说
------------没有瞎说
--------大象进医院了
------------护士是蚂蚁
----怎么可能
--------怎么没有可能
--------可能性是很大的

create or replace procedure p (v_id article.id%type, v_grade binary_integer)
is
  cursor c is select * from article where pid = v_id;
  v_preStr varchar2(1024);
begin
 
  for v_i in 1..v_grade loop
    v_preStr := v_preStr || '----';
  end loop;
 
  for v_a in c loop
    dbms_output.put_line(v_preStr || v_a.cont);
    p (v_a.id, v_grade + 1);
  end loop;
end;

begin
  p(0, 0);
end;

---------------------------------------------------------------------------------------------------------
drop table article;

create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1), --0 代表非叶子节点，1代表叶子节点
alevel 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);
commit;

蚂蚁大战大象
    大象被打趴下了
     蚂蚁也不好过
     瞎说
  没有瞎说
     大象进医院了
  护士是蚂蚁
    怎么可能
     怎么没有可能
     可能性是很大的

 

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;

------
begin
  p(0, 0);
end;
----如果错误 show error

----第二个版本
create or replace procedure p (v_pid article.pid%type) is
  cursor c is select * from article where pid = v_pid;
  v_preStr varchar2(1024) := '';
begin

  for v_article in c loop
    for i in 1..v_article.alevel loop
      v_preStr := v_preStr || '____';
    end loop;

    dbms_output.put_line(v_preStr || v_article.cont);
    if(v_article.isleaf = 0) then
      p (v_article.id);
    end if;
  end loop;
end;
----
exec p(0)
----
begin
p(0);
end;
----------------------------------------------------------------------------------------------------------------
--展现emp表的树状结构
create or replace procedure p
    (v_empno emp.empno%type, v_grade binary_integer)
is
    cursor c is
     select * from emp where mgr = v_empno;
    v_preStr varchar2(4000) := '';
 v_i binary_integer := 0;
begin
    while v_i < v_grade loop
      v_preStr := v_preStr || '****';
   v_i := v_i + 1;
    end loop;
 
 for v_emp in c loop
     dbms_output.put_line(v_preStr || v_emp.ename);
  p(v_emp.empno, v_grade + 1); 
 end loop;
end;

declare
    v_emp emp%rowtype;
begin
    select * into v_emp from emp where mgr is null;
 dbms_output.put_line(v_emp.ename);
 p(v_emp.empno, 1);
end;
-----------------------------------------------------------------------------------------------------
--建立新的Article表，使用层次str的表现形式，最多4层，每层最多99个节点
--建立数据，写出树状结构展现的语句
drop table article;
create table article
(
id number primary key,
cont varchar2(4000),
str char(8), --number(8)
grade number(1)
);

蚂蚁大战大象
----大象被打趴下了
--------蚂蚁也不好过
--------瞎说
------------没有瞎说
--------大象进医院了
------------护士是蚂蚁
----怎么可能
--------怎么没有可能
--------可能性是很大的

insert into article values (1, '蚂蚁大战大象', '01000000', 1);
insert into article values (2, '大象被打趴下了', '01010000', 2);
insert into article values (3, '蚂蚁也不好过', '01010100', 3);
insert into article values (4, '瞎说', '01010200', 3);
insert into article values (5, '没有瞎说', '01010201', 4);
insert into article values (6, '怎么可能', '01020000', 2);
insert into article values (7, '怎么没有可能', '01020100', 3);
insert into article values (8, '可能性是很大的', '01020200', 3);
insert into article values (9, '大象进医院了', '01010300', 3);
insert into article values (10, '护士是蚂蚁', '01010301', 4);
commit;
--不要忘了commit;否则出不来

declare
  cursor c is select * from article order by str;
  v_preStr varchar2(1024) := '';
begin
  for v_temp in c loop
    v_preStr := '';
    for i in 1..v_temp.grade loop
      v_preStr := v_preStr || '****';
    end loop;
    dbms_output.put_line(v_preStr || v_temp.cont);
  end loop;
end;