数I据库三泛式，表约束，PLSQL编程

1、属性不可再分：
对于我们来说新建一张表的时候，我们的每一个列都必须表示唯一的数据，
2、属性不可部分依赖主属性（主键，唯一标记）
简单来说我们的属性不能同时具备几个含义，而且只属于我们的主属性的关联关系
3、属性不能间接依赖主属性
简单来说，就是我们的表属性不能能介入其他对象的属性 ，该表是直接拥有这个对象。
满足三大范式的目标就是 多表少字段

约束：
主键约束 primary key(唯一非空) – 【自带索引】
唯一约束 unique
非空约束 not null
检查约束 check （sex=’男’ or sex=’女’）
外键约束 foreign key
组合约束 约束(列1，列2)

在数据库设计过程中，大部分时候都是逻辑上有约束，物理上没有约束
这种约束语法我们通常都是通过逻辑上进行控制，

SQL编程 == PLSQL

PLSQL：就是利用sql语言写出一些类似java一样可以控制逻辑的代码，

语法规则：
–变量都是在规定的地方先定义在使用，
declare
–定义变量的地方
– 名字 类型 := 值
v_msg varchar2(20) :=’Helloword11111’;

begin

–写逻辑的地方
dbms_output.put_line(v_msg);
dbms_output.put_line(1+1);

end;

吧我们表数据查询出来存入变量 select…into

 select ename into v_name from emp where empno = 7369;
 select ename into v_name2 from emp where empno = 7369;

数据类型，：
常用：int、number（t,n）, date , boolean , varchar2(n)
运算符号： 除了赋值（：=）和判断等于（=） 其他整体和java差不多

数据类型： %type ： 参照数据类型
表名。列名%tyep

declare 
 v_name varchar2(20);
 v_name2 emp.ename%type;
 v_name varchar2(20);
 v_deptno int;
 v_sal number(10,1);
 v_date date;



begin

 select ename into v_name from emp where empno = 7369;
 select ename into v_name2 from emp where empno = 7369;


end;

我们可以基于%type来定义属于自己的复杂数据类型：

type 类名 is record（）；

declare 
--自定义数据类型
  type v_dept is record (
       v_deptno dept.deptno%type,
       v_dname dept.dname%type,
       v_loc dept.loc%type
  );
  v_param v_dept;
begin
  select d.deptno, d.dname, d.loc into v_param from dept d where deptno = 10;
end;

oracle自定义了一个专业的能描述一张一行的数据类型，%rowtype

declare 
  v_dept dept%rowtype;


begin

  select * into v_dept from dept where deptno=10;
  dbms_output.put_line(v_dept.deptno);
  update dept set loc = 'xxx' where deptno = 10;
  commit;

end;

sql的if语句：语法

declare 

    v_flag boolean;


begin
       v_flag := 3>10;

       if(v_flag) then 
                  dbms_output.put_line('OK');
       else 
                  dbms_output.put_line('NO');

       end if;

end;

if - eslif 语法 多分支：

declare

  v_sal emp.sal%type;                   

begin

  select sal into v_sal from emp where empno = 7369;

  if (v_sal <1000) then
     update emp set sal = sal+1500 where empno = 7369;
  elsif (v_sal > 1000 and v_sal < 2000) then
     update emp set sal = sal+500 where empno = 7369;

  end if;
  commit;
end;

plsql循环：
loop循环

declare
  v_num number;

begin
  v_num := 1;
  loop
     dbms_output.put_line(v_num);
     v_num := v_num + 1;  

     if (v_num = 10) then
        exit; 
     end if; 
  end loop;
end;

while 循环：

declare
  v_num number := 1;
begin

 while(v_num < 10) loop
    dbms_output.put_line(v_num);
    v_num:=v_num+1;   


 end loop;
end;

for循环：

declare
  v_num number := 1;
begin
  -- v_num := 10  v_num <20
  for v_num in 10..20 loop
      dbms_output.put_line(v_num);

  end loop;

end;

plsql数组：
注意plsql没有提供数组的数据类型，需要我们自己来进行定义，

[create or replace] TYPE myarr
  is VARRAY(N) OF VARCAHR2(20)

创建一个myarr数据类型是VARRAY（N） of 装varchar2（20）这种数据

declare 
   type names is varray(3) of varchar2(20);

  empnames names;
  totals number;
  x number;

begin
  empnames := names('zhangsan','liis','wangwu');
  totals := empnames.count;
  dbms_output.put_line(totals);

  for x in 1..totals loop

      dbms_output.put_line(empnames(x));
  end loop;
end;

oracle函数： 简单来说就是类似java的方法，里面封装了特定的功能，我们可以通过函数名进行调用这个功能，

   Create or replace function f_name(par1,par2)
Return number is ret number;
Begin
       代码块
End;

注意，函数是一个独立的个体。需要独立的编写，不能写到plsql里面作为代码款

create or replace function f1(x in number, y in number) return number 
is

       v_result number;

begin
       v_result := 10;

end;

创建函数的语法：
create or replace function f1(x in number, y in number) return number
is
定义变量的地方
begin
执行代码的地方

return 结果；

end；
注意：必须有参数和返回值 （参数默认使用输入参数）
执行完成之后，必须用return关键字返回对应的结果

 create or replace function f2(v_name in varchar2, v_sal in number) return varchar2
is
  v_str varchar2(30);
begin
  v_str := v_name || '(' || v_sal || '￥)';
  update emp set sal = v_sal where ename = v_name;
  return v_str;  
end;

同理里面可以编写CRUD代码

存储过程： 我们可以理解存储过程就是一个高级版本的函数。

create or replace procedure p1(v_num1 in number)
is

begin
   dbms_output.put_line(v_num1);

end; 

call p1(1221)

函数普遍用于执行特定的功能得到结果，是使用sql语句上
过程是用于处理大量的业务逻辑，而一般不需要结果。是独立使用，
但是过程有输出参数。

但是在我们进行jdbc操作的时候，时常需要调用存错过程有反馈结果。这时就可以利用out设计我们的输出参数来得到过程反馈的结果。

create or replace procedure p2(v_num1 in number, v_num2 in number, v_sum out number)
is

begin
       v_sum := v_num1 + v_num2;

end;


declare 
       v_sum1 number := 10;

begin

       dbms_output.put_line(v_sum1);
       p2(10,20,v_sum1);
        dbms_output.put_line(v_sum1);

end;

jdbc调用存错过程

–编写一个存储过程，根据emp编号，查询姓名和工资，并输出

create or replace procedure p4(v_empno in number, v_ename out varchar2, v_sal out number)
is

begin
       select ename, sal into v_ename, v_sal from emp where empno = v_empno;
end;

java代码：

package com.xingxue.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

import com.xingxue.utils.DBUtils;

public class TestProcedure {

    public static void main(String[] args) {

        //加载驱动
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        //创建链接
        Connection conn = null;
        try {
            conn = DriverManager.
            getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
            System.out.println(conn);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        //创建预处理sql 对象  == 创建存错过程调用对象
        try {

            CallableStatement call = conn.prepareCall("call p4(?,?,?)");
            //设置输入参数
            call.setInt(1, 7369);
            //设置输出参数   注册第几个参数返回数据类型是什么
            call.registerOutParameter(2, Types.VARCHAR);
            call.registerOutParameter(3, Types.INTEGER);

            //发送并执行sql
            call.execute();

            String name = call.getString(2);
            System.out.println(name);

            int sal = call.getInt(3);
            System.out.println(sal);


        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }



    }

}

由于存储过程可以编写大量的sql， 我们做级联操作，例如删除部门，先操作部门的员工信息，才能去删除部门这种情况，我们使用存储过程操作就可以利用存储过程封装业务逻辑，减少java访问数据库的次数， 避免事务的控制和处理，提升效率
存储过程的代码都会先预编译好，当调用的时候是直接执行，

但是存储过程的缺点就是无法进行数据库移植，比如我们从oracle换到mysql数据库，存储过程就会全部失效，那项目的功能就全部重新。

游标：游标是指向oracle为存储一些临时信息而创建的上下文区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。

简单来说，隐式游标就是当我们plsql执行DML语句的时候，oraccle会帮我们自动创建一些变量， 这些变量又特定的含义：

 declare
   v_num  number;

begin
   update emp set sal = 200 ;
   if sql%notfound then  --sql没有操作到数据
      dbms_output.put_line('no');
   elsif sql%found then   --sql操作到了数据
      v_num := sql%rowcount;   --sql操作了多少条数据
      dbms_output.put_line(v_num); 
   end if;

end;

显示游标指的就是用户自己创建的临时存储数据的区域： （存储大量数据-可以理解为java的list）

1、 定义游标
2、打开游标，【分配内存，存储数据】
3、获取游标数据，【 fetch 游标 into 变量】； 使用循环
4、使用完一定要关闭游标。

declare 
   cursor emplist is select ename,sal from emp; -- 申明游标
   v_ename varchar2(20);
   v_sal number;

begin
   open emplist; 
   loop
        fetch emplist into v_ename, v_sal;      
        dbms_output.put_line(v_ename || '====' || v_sal);

        if emplist%notfound then
           exit;

        end if;

   end loop;
   close emplist;
end;

declare 
   cursor emplist is select * from emp; -- 申明游标
   v_emp emp%rowtype;
   --v_emp(empno, ename, job, mgr, hiredate ,sal ,comm,deptno)
begin
   open emplist; 
   loop
        fetch emplist into v_emp;      
        dbms_output.put_line(v_emp.ename || '====' || v_emp.comm);

        if emplist%notfound then
           exit;

        end if;

   end loop;
   close emplist;
end;
select * from emp;