plsql

plsql 高级的笔记

程序结构

declare

begin
--程序开始 可以写sql+选择结构 +循环结构
--'你好 world' system.out.print('你好 world')
--程序包       类似于     java 类
--存储过程               方法
dbms_output.put_line('你好 world');--输出语句
end;

声明变量

declare
	sname varchar2(10);
begin
--第一种方法
--java 赋值 用==   oracle 用 := 
   sname :='tom';

--第二种方法 select 列名 into 变量名 from 表名 
select s.sname into sname from student where sno=108;
dbms_output.put_line(sname);
end;

引用变量

--%type 变量的类型和制定的列的类型是一致的
sname student.sname%type;
--%rowtype
stu stuednt%rowstudent;

declare
   sname student.sname%type ;
   stu student%rowtype; 
begin
  select * into stu from student s where sno=108;
  dbms_output.put_line(stu.sno||'==》'|| stu.sname); 
end;

选择结构 if

一般写完if 一定要先写end if ; 后面很容易忘记不写

declare
	snum number:=3;
begin
	if snum>2 then 
		dbms_output.put_line(snum||'大于2');
	end if;	
end;

if else

declare
	snum number:=3;
begin
	if snum>2 then 
		dbms_output.put_line(snum||'大于2');
	else
    	dbms_output.put_line(snum||'小于2');
    end if;	
end;

if else ifelse 结构

/*
if 条件 then 代码块1;
	elsif 条件 then 代码块2;
    	elsif 条件 then 代码块3；
end if;
*/
declare 
snum number :=1; 
begin
  if snum>2then dbms_output.put_line(snum||'大于2');
    elsif snum>0 then dbms_output.put_line(snum||'大于0');
    else
         dbms_output.put_line(snum||'小于等于0');
  end if;
end;

循环

declare
	snum number :=1;
begin 
	loop
	--exit when 退出循环条件
	exit when snum>5;
	dbms_output.put_line(snum);
	snum:=snum+1;
	end loop;
end;

作业 ：从1开始累加 当综合大于20 输出最后一个数

declare
	ssum number :=0;
	i number :=0;
begin
	loop
		exit when ssum>20;
		ssum:=ssum+i;--当结果在这里大于20的时候 接下来的一步会给i再加1  所以输出结果时  i就要减1
		i:=i+1
	end loop;
	dbms_output.put_line(i-1)
end

游标

1.游标：类似于集合，可以存储一组记录

2. 语法 ： cursor 游标 is 查询语句

declare
	cursor c_emp is select  tno,tname from emp;  --创建游标
	tno emp.tno%type;
	tname emp.tname%type;
begin
	--1.打开游标
	open c_emp;
	--2.循环游标取值
	loop
	fetch c_emp into tno,tname ;  ---fetch   into 取值
	exit when c_emp%notfound; ---当为true时 退出循环
	dbms_output.put_line(tno||'==>'||tname)
	end loop;
	--3.关闭游标
	close c_emp;

end;

带参数的游标

declare
     --               参数        参数类型
     cursor c_teacher(dno teacher.deptno%type) is select tno,tname,deptno from teacher where deptno=dno;
     tno teacher.tno%type;
     tname teacher.tname%type;
     deptno teacher.deptno%type;
begin
  --打开游标   传入参数 从这个部门里选择
  open c_teacher(20);
       loop
         fetch c_teacher into tno,tname,deptno;
         exit when c_teacher%notfound;
         dbms_output.put_line(tno||'==>'||tname||'==>'||deptno);
       end loop; 
   --关闭游标    
  close c_teacher;
  
end;

练习：统计scott用户下emp表每年入职的人数[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ta4Q4B0W-1593432183367)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20200628204638674.png)]

declare
  cursor c_emp is select to_char(hiredate,'yyyy') from emp;
  ccount1980 number :=0;
  ccount1981 number :=0;
  ccount1987 number :=0;
  ccount1982 number :=0;
  acount varchar2(4);
  
begin
  open c_emp;
  loop
    fetch c_emp into acount;
    exit when c_emp%notfound;
    if acount='1980' then  ccount1980:=ccount1980+1  ;
    elsif acount='1981' then ccount1981:=ccount1981+1 ;
    elsif acount='/1982' then ccount1982:=ccount1982+1;
    elsif acount='1987' then ccount1987:=ccount1987+1 ;
    end if;
  end loop;
  close c_emp;
   dbms_output.put_line('1980出生的人数' ||'==>'||ccount1980);
   dbms_output.put_line('1981出生的人数' ||'==>'||ccount1981);
    dbms_output.put_line('1982出生的人数'||'==>'||ccount1982);
    dbms_output.put_line('1987出生的人数'||'==>'||ccount1987);
end;

select *from teacher

游标属性

属性名称	说明
%found	用于检验游标是否成功，通常在FETCH语句前使用，当游标按照条件查询出一条记录时，返回true
%isopen	判断游标是否处于打开状态，视图打开一个已经打开或者已经关闭的游标，将会出现错误
%notfound	与%found的作用相反，当按照条件无法查询到记录时，返回true
%rowcount	循环执行游标读取数据时，返回检索出的记录数据的行数

异常

--预定异常
declare
  i number ;
  when exception zero_divide then dbms_output.put_line('除数不能为0 ');
  --others  类似于最大的异常类型
  when others then dbms_output.put_line('未知异常 ');
begin 
  i:=1/0;
  
end;

自定义异常

declare
  cursor c_teacher is select tno,tname,deptno from teacher where deptno=60; --创建游标
  ttno teacher.tno%type;--创建变量
  ttname teacher.tname%type;
  ddeptno teacher.deptno%type;
  --变量的类型时 exception 这个变量就是异常变量
  no_deptno_date exception;  --自定义异常
begin
  open c_teacher;
       fetch c_teacher into ttno,ttnae,ddeptno;
             --throw  ==raise  都是抛异常的意思
       if c_teacher%notfound then raise  no_deptno_date;
       end if;
  
  close c_teacher;
  exception when  no_deptno_date then dbms_output.put_line('该部门编号没有老师 ');
end;

存储过程

无参存储

create or replace procedure sayHelloWorld
--  is  as  相当于 declare
is 
begin
	dbms_output.put_line('HelloWorld ');
end;

--调用存储过程

begin
sayHelloWorld();   --无参存储过程 在创建的时候不用括号  调用的时候要带上括号
end；

带参存储

--带参数存储过程 根据tno输出教师的名字和薪水   参数名 参数类型 参数的数据类型
--in  输出参数 接收外界的值
--out 输出参数 将方法内的值返回到方法调用位置
create or replace procedure findTeacherBytno(
teano in teacher.tno%type
)
is
teaname teacher.tname%type;
teasal teacher.sal%type;
bgein
	select tname,sal into teaname,teasal from teacher where tno=teano;
	dbms_output.put_line('teaname||'=='||'teasal' ');
end;


--调用
declare 
bgein
	findTeacherBytno(1002);--类似Java  调用方法 传参数
end;

带两个参数

--带两个参数   
--根据tno找到教师  增加工资
create or replace procedure addsal(teatno in teacher.tno%type,money in number)
is
--teatno teacher.tno%type;
begin
  update teacher set sal=sal+money where tno=teatno;
  end;
  
--diaoyomg
declare
begin
  addsal(1002,200);
  commit;--谁调用谁提交事务
  end;

存储函数

存储函数 和存储过程语法基本是一样的

存储函数和存储过程最大的区别就是 存储函数有一个返回值

根据tno查询教师年薪

create or replace function getIncome(teatno in teacher.tno%type)
return number;
is
tsal teacher.sal%type;
tcomm teacher.comm%type;
begin
  select sal,comm,into tsal,tcomm from teacher where tno=teatno;
  return tsal*12+nvl(tcomm,0);
end;  

declare 
begin 
  getIncome(1002);
end;

触发器

​ 与一张表关联 对标执行 insert update delete

​ 1.语句级触发器 执行sql语句 触发器只执行一次 sql语句更新了多少条记录 触发器都执行一次

 create or replace trigger showInfo
 after insert  on teacher
 declare
 begin
   dbms_output.put_line('插入成功！');
 end;

​ 2.行级触发器 sql更新了多少条数据触发器就执行多少次

​ x相比语句触发器多加了 for each row

  create or replace trigger testType
 after update
 on teacher
 for each row
 declare 
 begin
   dbms_output.put_line('testType触发器执行');
 end;  
 
 update teacher set comm=1000;

案例

		**禁止在非工作时间插入新老师信息**

 create or replace trigger checkDay
 before insert --修改数据用的before
 on teacher --作用于 老师表
 declare    --去空格               得到今天是星期几
    --select rtrim(tochar(sysdate,'day')) into today from dual
    today varchar2(20);

 begin
   select rtrim(tochar(sysdate,'day')) into today from dual;--赋值给today
   if today='monday' then  raise_appliaction_error(-20005,'周一不能插入，放假'); --声明异常 
   end if;
 end;

三大范式

1. 第一范式：确保每列的原子性
2. 第二范式：确保表中每列都和主键相关
3. 第三范式：确保每列都和主键直接相关，而不是间接相关

事务

事务时绑定在一个任务单元的一些sql语句，目的是为了保证这些sql语句同时运行成功，则提交事务，如果有一个不成功，则事务回滚。

事务的四大特性（acid） ：

1. 原子性 不可分割sql语句组中 的sql执行时不能分割必须同时成功同时失败
2. 一致性 数据库必须是从一个一致性状态到另一个一致性状态
3. 持久性 事务一旦提交 对数据就是永久的
   nto today from dual;–赋值给today
   if today=‘monday’ then raise_appliaction_error(-20005,‘周一不能插入，放假’); --声明异常
   end if;
   end;

# 三大范式 

1. 第一范式：确保每列的原子性
2. 第二范式：确保表中每列都和主键相关
3. 第三范式：确保每列都和主键直接相关，而不是间接相关

# 事务

事务时绑定在一个任务单元的一些sql语句，目的是为了保证这些sql语句同时运行成功，则提交事务，如果有一个不成功，则事务回滚。

事务的四大特性（acid） ：

1. 原子性 不可分割sql语句组中 的sql执行时不能分割必须同时成功同时失败
2.  一致性 数据库必须是从一个一致性状态到另一个一致性状态
3.  持久性 事务一旦提交 对数据就是永久的
4. 隔离性 一个事务和其他事务之间不能互相干扰