Oracle中的PL/SQL 过程化sql语言

数据库的设计（DataBase Design）：
针对于用户特定的需求，然后我们创建出来一个最使用而且性能高的数据库！

数据库设计的步骤：
01.需求分析
02.概念结构设计
03.逻辑结构设计
04.物理结构设计
05.数据库的实施
06.数据库的运行和维护

 

数据库的3大范式：
1.确保每列的原子性！每一列都是一个不可再分的数据！
2.确保每列都和主键相关！
3.确保每列都和主键有直接的管理，而不是间接依赖（传递依赖）！

PL/SQL： (Procedural  Language)  过程化sql语言！

数据库在执行PL/SQL语句的时候，PL和SQL是分别执行的！

PL/SQL运算符

算术运算符

下表列出了所有PL/SQL支持的算术运算符。假设变量A=10和可变B=5，则：

BEGIN 
   dbms_output.put_line( 10 + 5);
   dbms_output.put_line( 10 - 5);
   dbms_output.put_line( 10 * 5);
   dbms_output.put_line( 10 / 5);
   dbms_output.put_line( 10 ** 5);
END;

当上述代码在SQL提示符执行时，它产生了以下结果：

关系运算符

关系运算符比较两个表达式或值，并返回一个布尔结果。下表列出了所有PL/SQL支持的关系运算符。假设变量A=10，变量B=20，则：

DECLARE  --声明
   a number (2) := 21;
   b number (2) := 10;
BEGIN    --开始
   IF (a = b) then
      dbms_output.put_line('Line 1 - a is equal to b');
   ELSE
      dbms_output.put_line('Line 1 - a is not equal to b');
   END IF;  --结束循环

   IF (a < b) then
      dbms_output.put_line('Line 2 - a is less than b');
   ELSE
      dbms_output.put_line('Line 2 - a is not less than b');
   END IF;  --结束循环
   
   IF ( a > b ) THEN
      dbms_output.put_line('Line 3 - a is greater than b');
   ELSE
      dbms_output.put_line('Line 3 - a is not greater than b');
   END IF;  --结束循环

   -- Lets change value of a and b
   a := 5;
   b := 20;
   IF ( a <= b ) THEN
      dbms_output.put_line('Line 4 - a is either equal or less than b');
   END IF;

   IF ( b >= a ) THEN
      dbms_output.put_line('Line 5 - b is either equal or greater than a');
   END IF;
   
   IF ( a <> b ) THEN
      dbms_output.put_line('Line 6 - a is not equal to b');
   ELSE
      dbms_output.put_line('Line 6 - a is equal to b');
   END IF;

END; --结束

当上述代码在SQL提示符执行时，它产生了以下结果：

比较运算符

比较运算符用于一个表达比较到另一个。结果总是 TRUE，FALSE或NULL。

--LIKE 运算符
--这个程序测试LIKE运算符，将学习如何在PL/ SQL程序使用，但这里用一个程序来显示LIKE运算符的功能：
DECLARE
PROCEDURE compare (value  varchar2,  pattern varchar2 ) is
BEGIN
   IF value LIKE pattern THEN
      dbms_output.put_line ('True');
   ELSE
      dbms_output.put_line ('False');
   END IF;
END;

BEGIN
   compare('Zara Ali', 'Z%A_i');
   compare('Nuha Ali', 'Z%A_i');
END;

当上述代码在SQL提示符执行时，它产生了以下结果：

--BETWEEN运算符
--下面的程序显示了运算符BETWEEN的用法
DECLARE
   x number(2) := 10;
BEGIN
   IF (x between 5 and 20) THEN
      dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
   
   IF (x BETWEEN 5 AND 10) THEN
      dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
   
   IF (x BETWEEN 11 AND 20) THEN
      dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
END;

当上述代码在SQL提示符执行时，它产生了以下结果：

--IN和IS NULL运算符
--下面的程序显示IN和IS NULL运算符的用法
DECLARE
   letter varchar2(1) := 'm';
BEGIN
   IF (letter in ('a', 'b', 'c')) THEN
      dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
 
   IF (letter in ('m', 'n', 'o')) THEN
       dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
   
   IF (letter is null) THEN
    dbms_output.put_line('True');
   ELSE
      dbms_output.put_line('False');
   END IF;
END;

当上述代码在SQL提示符执行时，它产生了以下结果：

 

逻辑运算符

下表显示了PL/SQL支持的逻辑运算符。所有这些操作符布尔运算，并产生布尔结果。假设变量A=true，变量B=false

DECLARE
   a boolean := true;
   b boolean := false;
BEGIN
   IF (a AND b) THEN
      dbms_output.put_line('Line 1 - Condition is true');
   END IF;
   IF (a OR b) THEN
      dbms_output.put_line('Line 2 - Condition is true');
   END IF;
   IF (NOT a) THEN
      dbms_output.put_line('Line 3 - a is not true');
   ELSE
      dbms_output.put_line('Line 3 - a is true');
   END IF;
   IF (NOT b) THEN
      dbms_output.put_line('Line 4 - b is not true');
   ELSE
      dbms_output.put_line('Line 4 - b is true');
   END IF;
END;

 当上述代码在SQL提示符执行时，它产生了以下结果：

PL/SQL运算符优先级

运算符优先级确定表达式分组。这会影响一个表达式是如何进行计算。某些运算符的优先级高于其他运算符; 例如，乘法运算符的优先级比加法运算高：

例如 x =7 + 3* 2; 这里，x被赋值13，而不是20，因为运算符*具有优先级高于+，所以它首先被乘以3 * 2，然后再加上7。

这里，具有最高优先级的操作出现在表的顶部，那些具有最低出现在底部。在表达式，更高的优先级运算符将首先计算。

DECLARE
   a number(2) := 20;
   b number(2) := 10;
   c number(2) := 15;
   d number(2) := 5;
   e number(2) ;
BEGIN
   e := (a + b) * c / d;      -- ( 30 * 15 ) / 5
   dbms_output.put_line('Value of (a + b) * c / d is : '|| e );

   e := ((a + b) * c) / d;   -- (30 * 15 ) / 5
   dbms_output.put_line('Value of ((a + b) * c) / d is  : ' ||  e );

   e := (a + b) * (c / d);   -- (30) * (15/5)
   dbms_output.put_line('Value of (a + b) * (c / d) is  : '||  e );

   e := a + (b * c) / d;     --  20 + (150/5)
   dbms_output.put_line('Value of a + (b * c) / d is  : ' ||  e );
END;

当上述代码在SQL提示符执行时，它产生了以下结果：

|| 拼接字符串
：= 赋值运算符
= 比较运算符 类似与java中的==
.. 范围运算符  1-10
！= <> ~= ^=  4种不等于
and 逻辑与
or 逻辑或
not 取反

 

PL/SQL语法：

1.declare 可选部分 ==》声明

2.begin 必须有 ==》书写sql 和 pl/sql

3.exception 可选部分 ==》异常

4.end 必须有 ==》pl/sql代码块结束

 事例1：loop循环语法

--loop循环语法：
declare    --声明部分
  i number;
begin      --开始
  i:=1;
  loop     --循环开始
    dbms_output.put_line(i);   --输出语句
    i:=i+1;
    exit when i=10;
  end loop; --循环结束
  
end;  --结束

事例2：while循环语法

declare    --声明部分
  i number;
begin      --代码开始
  
  i:=1;
 while i<8 loop     --循环开始
    dbms_output.put_line(i);   --输出语句
    i:=i+1;
  end loop; --循环结束
end;        --结束部分

事例3：for循环语法

declare    --声明部分
  i number;
  
begin      --代码开始
  
 for  i in 1..9 loop     --循环开始
    dbms_output.put_line(i);   --输出语句
  end loop; --循环结束
  
end;        --结束部分

事例4：根据老师的薪水输出不同的语句！

declare
  t_name  teacher.tname%type;  --说t_name的类型根据teacher表中tname的类型来决定
  t_sal   teacher.sal%type;
  t_result  varchar2(50);
begin    --开始
  select  tname,sal into t_name,t_sal   from teacher where tno=1002; --查询指定老师的薪水
  
    if t_sal>5000 and t_sal<10000 then   -- 多重if
      t_result:='一级';
    elsif t_sal>=10000 and t_sal<20000 then
      t_result:='二级';
    else
      t_result:='高级';
    end if;  
    --根据t_result的值来判断输入语句    switch
    case t_result
        when '一级' then
        dbms_output.put_line('哈哈....');
        when '二级' then
        dbms_output.put_line('一般般....');
        when '高级' then
        dbms_output.put_line('可以啊....');      
    end case;
end;     --结束

事例5：函数，将身份证中的出生日期隐藏起来

create or replace  function fn_teacher_tid(f_tid varchar2)
return varchar2     --创建一个函数  传递一个varchar2类型的值 返回一个varchar2类型的值
is
 f_result varchar2(50);    --声明变量
begin      --开始书写函数内容
  if length(f_tid)!=18 then
    dbms_output.put_line('身份证格式不正确');
    else
    dbms_output.put_line('身份证格式正确'); 
  end if;
    f_result:= substr(f_tid,1,6)||'********'||substr(f_tid,15);
    return f_result;
end fn_teacher_tid;   --函数结束

--调用函数  需要现将函数执行 放入到Functions中  没有出错时再调用方法
select fn_teacher_tid('41302619921215935X') from dual;

执行结果出生日期8位数字被隐藏

事例6：隐式游标

begin    --  隐式游标    自动创建
     update teacher set tname='我是隐式游标' where tno=1002;  --修改
    if sql%found  then
       dbms_output.put_line('教师的信息已经更改'||sql%rowcount);
       else
       dbms_output.put_line('更改失败');
    end if;      
end;

 

 事例7：显示游标

declare    --声明 显示游标
  c_tname  teacher.tname%type;
  c_sal    teacher.sal%type;
cursor  teacher_cursor
is
select tname,sal from teacher where tno<1005;  --游标数据来源
begin
  open teacher_cursor;  --打开游标
   fetch teacher_cursor into c_tname,c_sal;--使用游标
    while teacher_cursor%found  loop      
      dbms_output.put_line('教师的姓名是==》'||c_tname);
      dbms_output.put_line('教师的薪水是==》'||c_sal); 
      fetch teacher_cursor into c_tname,c_sal;--逐行读取  
    end loop;
  close teacher_cursor;   --关闭游标
end;

 

判断条件tno<1005  显示老师的姓名和工资

 

游标 ：
01.是oracle系统给我们用户开设的一个数据缓冲区！
02.存放的是sql语句执行的结果集！
03.每个游标区都有一个名称，用户通过游标逐行获取需要的数据！

分类：
01.隐式游标： 非查询语句
只要我们使用pl/sql，程序在执行sql语句的时候 自动创建！ 游标区===》sql
02.显示游标： 返回多行记录
03.REF游标（动态游标）： 处理运行时才能确定的动态sql查询结果

游标的常用属性：
01.sql%found 影响了一行或者多行数据 返回true
02.sql%notfound 没有影响行 返回true
03.sql%rowcount 返回true影响行数
04.sql%isopen 游标是否打开！始终是false

使用游标的步骤：
01.声明游标
02.打开游标
03.使用游标获取记录
04.关闭游标

事例8：触发器

触发器只针对于增删改！ 

update :old :new
insert :new
delete :old

:old 代表修改之前的值
:new 代表修改之后的值

-- 创建一个用于保存teacher操作记录的表
create  table  teacher_log
(
logid  number  not null,
old_value varchar2(150),
create_date date,
log_type  number,
t_no number
);
--创建主键
alter  table  teacher_log add  constraint pk_teacher_logid 
primary key(logid);

-- 创建序列
create  sequence sq_teacherLog_logid
minvalue 1
maxvalue 99999999999
start with 1
increment by 1;

--  创建触发器
create or replace trigger  tr_teacher
after insert or update or delete    --会在增删改之后 触发
on teacher for each row   --作用在teacher表中的每一行
declare   --声明变量
old_value    teacher_log.old_value%type;
log_type     teacher_log.log_type%type;
t_no         teacher_log.t_no%type;
begin
    if inserting  then
      log_type:=1; --新增
      t_no:=:new.tno;
      old_value:=:new.tname||'*****'||:new.sal;
    elsif deleting  then
       log_type:=2; --删除
      t_no:=:old.tno;
      old_value:=:old.tname||'*****'||:old.sal;
     else
       log_type:=3; --修改
       t_no:=:old.tno;
      old_value:=:old.tname||'*****'||:old.sal||'现在的薪水：'||:new.sal;
    end  if;  
  
--把用户修改的数据 放入 teacher_log
insert into  teacher_log
values(sq_teacherLog_logid.nextval,old_value,sysdate,log_type,t_no);    
end tr_teacher;  --结束
select * from teacher
select * from teacher for update
select * from teacher_log

 先执行添加语句  打开表中的锁、修改金额、点击对号，提交事务

select * from teacher for update

执行查询teacher_log表，可以看到被修改的数据

select * from teacher_log

删除表中第二条数据 TNO=1009  秦亚杰 打开表中的锁、删除字段、点击对号，提交事务

 

执行查询teacher_log表，可以看到被删掉的数据

select * from teacher_log

偷偷增加一条数据，自己人将工资多给点，打开表中的锁、添加行、新增数据、点击对号，提交事务

执行查询teacher_log表，可以看到增加信息

 

 在Oracle中增删改都需要手动去提交事务

事例9：存储过程

 

为了完成一个特定的功能而实现编写一组sql语句的集合！

 

新增数据时，如果身份证号码不足18位，报错！

create or replace  procedure  pro_addTeacher   --存储过程
(
p_no      teacher.tno%type,
p_name     teacher.tname%type,
p_tid      teacher.tid%type
)
is
ex_tidException  exception;  --异常类型

begin
   if length(p_tid)!=18 then
     raise ex_tidException;   --抛出异常
    end if; 
    
    --新增
    insert  into  teacher(tno,tname,tid)
    values(p_no,p_name,p_tid);
    commit;  --- 自动提交
    
exception    -- 异常处理部分
   when ex_tidException then
     dbms_output.put_line('身份证号不正确');
    when others then
     dbms_output.put_line('其他异常');
  
end pro_addTeacher;   --结束

--调用存储过程
call pro_addTeacher(1000,'新增','112222222222222222');
select * from teacher
select * from teacher for update

 

 

身份证号等于18位时，数据添加成功

 

name teacher.tname%type ：会根据表中字段的类型，自动改变！

teacherRow teacher%rowtype: 一整行的记录，包括很多字段！

teacherRow.name