Oracle-4 - ：超级适合初学者的入门级笔记：plsql，基本语法，记录类型，循环，游标，异常处理，存储过程，存储函数，触发器
初学者可以从查询到现在的pl/sql的内容都可以在我这里的笔记中找到，希望能帮到大家，视频资源在 资源，
我自己的全套笔记在 笔记
在pl/sql中可以继续使用的sql关键字有：update delete insert select--into commit rollback savepoint ，在这里需要注意的是查询跟以前有些不一样了
plsql由三个块组成：声明部分，执行部分，异常处理部分
declare：在此声明pl/sql用到的变量，类型及游标，以及局部的存储过程的和函数
begin：执行部分：过程及sql语句，即程序的主要部分
exception：执行异常部分，错误处理
end
其中执行部分是必要的
pl/sql之helloworld
set SERVEROUTPUT ON --首先必须执行此语句，否则没有输出
begin
dbms_output.put_line('hello world');
end; --下图前三个均是没有执行set serveroutput on 语句的执行结果，在执行完此语句才能有输出
其中只有begin部分，和结束end，因为此语句不需要变量声明就省去了declare部分，和exception错误部分
变量常量等的命名规则，下列标红的就是建议使用的命名的开头
实例：用pl/sql查询出tno为t001的老师的名字并输出
下面的select 语句是把查询结果放到了变量v_name中然后输出
declare
v_name TEACHER.TNAME%TYPE; --这里是动态的获取teacher表中tname字段的类型
v_tno varchar2(10);
begin
--普通查询语句 ：select * from teacher where tno ='t001';
select tno,tname into v_tno,v_name from teacher where tno ='t001';
SYS.DBMS_OUTPUT.PUT_LINE(v_name||','||v_tno);
end;
记录类型：是把逻辑相关的数据作为一个单元存储起来，其作用是存放互不相同但逻辑相关的信息，类似java中一个类的概念一样
注意不能将select语句中的列赋值给布尔变量
declare
--在为一个变量赋值的时候的格式 ： v_tno number(10) :=10; 自这里 “：=”是赋值，判断为=
--type 自定义名1 is record
type teacher_mas is record (
v_name TEACHER.TNAME%TYPE, --逗号
v_tno varchar2(10) --无标点符号
);
-- 定义一个记录类型的成员变量
--自定义2 自定义名1 在这就相当于创建了一个对象
v_teacher_mas teacher_mas;
--如果字段特别多的话 我们可以使用：v_teacher_mas teacher%rowtype; 表示与teacher表中的所有的类型都一直 ，下面就直接可以查询 * into v_teacher_mas 了
begin
--普通查询语句 ：select * from teacher where tno ='t001';
select tno,tname into v_teacher_mas from teacher where tno ='t001';
SYS.DBMS_OUTPUT.PUT_LINE(v_teacher_mas.v_name||','||v_teacher_mas.v_tno);
end;
流程控制语句：
if 语句结构： if 《条件表达式》 then ---- end if； 相当于 java中 if() {}
if 《条件表达式》 then -- else-- end if； 相当于java中的 if(){} else {}
if 《条件表达式》 then -- els if《条件表达式》-- endif；这里是elsif 不是elseif 相当于java中的多重判断了就 ：if(){} elseif (){} eles{}
实例：--查询sno为s001的学生的c001课程的成绩，如果大于60输出及格 小于60输出不及格 其他输出一般
每次的if或者elsif必须跟一个分隔符 用end if作为结束标志，当然同一个if后可以加and
declare
v_score SC.SCORE%type;
begin
select score into v_score from sc where sc.sno='s001' and CNO='c001';
if v_score<60 then SYS.DBMS_OUTPUT.PUT_LINE('不及格');
elsif v_score >=60 then SYS.DBMS_OUTPUT.PUT_LINE('及格');
else SYS.DBMS_OUTPUT.PUT_LINE('一般');
end if;
end;
case语句结构：case 值
when 表达式 then
when 表达式 then
else
end；
由于case比较恶心，sno为s001的学生的c001课程的成绩为78.9，看下查询sql，在这里case跟Java、中的switch一样
case 一个值，when 后 只能跟常量，并且 then后不能赋值，输出等，只可以返回结果
declare
v_score SC.SCORE%type;
v_mas varchar2(30);
begin
select score into v_score from sc where sc.sno='s001' and CNO='c001';
v_mas :=
case v_score when 78.9 then '及格'
when 60 then '不及格'
else '一般'
end;
SYS.DBMS_OUTPUT.PUT_LINE(v_mas);
end;
循环结构： 实例 输出1--100 用循环结构
1. loop...exit...where ....end loop
declare
v_min number(3):=1;
begin
loop
SYS.DBMS_OUTPUT.PUT_LINE(v_min);
exit when v_min >= 100;
v_min := v_min +1;
end loop;
end;
2. while<布尔表达式> loop 要执行的语句 end loop；
declare
v_i number(3):=1;
begin
while v_i <=100 loop
SYS.DBMS_OUTPUT.PUT_LINE(v_i);
v_i := v_i +1;
end loop;
end;
3. for 循环计数器 in【reverse】 上限 .. 下限 loop 要执行的语句 end loop；不要忘记上限于下限中间有两个点
每循环一次变量自动+1，使用关键字reverse自动-1 ，跟在in reverse 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或者表达式，可以使用exit 退出循环
begin
for c in 1..100 loop
SYS.DBMS_OUTPUT.PUT_LINE(c);
end loop;
end;
标号与goto：无条件的跳到指定的标号去的意思
实例 ：打印1到100 ，当打印到50的时候，打印结束循环，然后结束整个循环
declare
v_i number(3):=1;
begin
while v_i<=100 loop
if v_i=50
then goto label;
end if;
SYS.DBMS_OUTPUT.PUT_LINE(v_i);
v_i := v_i+1;
end loop;
<<label>>
SYS.DBMS_OUTPUT.PUT_LINE('结束循环');
end;
游标的使用：类似java中的迭代器Iterator，游标是一个指向上下文的句柄或指针，通过游标，可以处理多行记录
1. 显示游标处理
显示游标处理四步骤
1. 定义游标：cursor --is -- 在指定数据类型时，不能使用长度约束
2. 打开游标：open -- ： 程序不能用open语句重复打开一个游标
3. 提取游标：fetch--into--
4. 关闭游标： close--
游标实例：打印出80号部门的所有员工的信息
declare
--记录类型
type emp_mas is record(
v_empid employees.employee_id%type,
v_name employees.last_name%type,
v_sal employees.salary%type
);
--记录类型对象
emp_mas_record emp_mas;
--定义游标
cursor emp_ens_mas is select employee_id,last_name,salary from employees where department_id=80;
begin
--打开游标
open emp_ens_mas;
--提取游标
fetch emp_ens_mas into emp_mas_record;
--emp_ens_mas%found 相当于java中的hashNext
while emp_ens_mas%found loop
SYS.DBMS_OUTPUT.PUT_LINE(emp_mas_record.v_empid||','||emp_mas_record.v_name||','||emp_mas_record.v_sal);
fetch emp_ens_mas into emp_mas_record;
end loop;
--关闭游标
close emp_ens_mas;
end;
2. 游标的for循环：pl/sql提供了游标for循环，自动执行游标的open，fetch，close语句和循环语句的功能，当进入循环时，游标for循环语句自动打开游标，并提取
第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标for循环语句会东子提取下一行数据供程序处理，当提取完结果
集中的所有数据行后结束循环，并自动游标
格式：for 变量 in 游标 loop -----end loop；
与上题一样，打印出80号部门的所有员工的信息
declare
--定义游标
cursor emp_ens_mas is select employee_id,last_name,salary from employees where department_id=80;
begin
for c in emp_ens_mas loop
SYS.DBMS_OUTPUT.PUT_LINE(c.employee_id||','||c.last_name||','||c.salary);
end loop;
end;
3. 异常的捕获与处理
1. 预定义异常，就是已经系统定义好的一些异常，这些异常由系统自动抛出，如下
declare
v_i number(30);
begin
select salary into v_i from employees where employee_id >=100;
SYS.DBMS_OUTPUT.PUT_LINE(v_i);
end;
由于employee_id >= 100 的员工的工资返回的结果不止一个 ，所以这里就会出现 返回值太多的一场
此时就可以在exception中捕获此异常并进行处理，如果不处理的话，系统报错并且程序整体终止
declare
v_i number(30);
begin
select salary into v_i from employees where employee_id >=100;
SYS.DBMS_OUTPUT.PUT_LINE(v_i);
exception
when Too_many_rows then SYS.DBMS_OUTPUT.PUT_LINE('返回值太多了！！');
when others then SYS.DBMS_OUTPUT.PUT_LINE('其他错误！！');
end;
上面捕获的是系统预定义异常Too_many_rows ，如果产生其他不知道的异常可以使用others 进行捕获并处理
2. 非预定义异常的处理
对于非预定异常的处理，首先必须对非定义的oracle错误进行定义，步骤
1. 在pl/sql块的定义部分定义异常情况：<异常情况>exception;
2. 将其定义好的异常情况，与标准的oracle错误连接起来，使用 pragma exception_init 语句：pragma exception_init(<异常情况>,<异常代码>);
3. 在pl/sql块的异常情况处理部分对异常情况做出相应的处理
我们来删除employee_id = 100的用户
declare
begin
delete from employees where employee_id =100;
end;
这时候由于100号员工有子记录，employee_id 等于 本表的manager_id，所以删除不了
上面爆出来的错误代码 2292 没有在oracle中的预定义异常，我们这时候就只能自己定义错误名与此错误号相关联
declare
my_exception exception;
pragma exception_init(my_exception,-2292);
begin
delete from employees where employee_id =100;
exception
when my_exception then SYS.DBMS_OUTPUT.PUT_LINE('违反约束 非预定义异常！！');
end;
3. 用户自定义的异常处理
用户自定义异常是通过显示使用 raise 语句来触发的，当引发一个异常错误的时候，控制就转向到exception块异常错误部分
对于这类异常情况的处理步骤如下
1. 在pl/sql块的定义部分定义异常情况 <异常情况>exception;
2. raise <异常情况>
3. 在pl/sql块的异常情况处理部分对异常情况做出相应的处理。
实例：查询employee_id 为100 号员工的工资，如果工资>1w则抛出异常“工资高”
declare
my_exception exception;
v_i number(5) ;
begin
select salary into v_i from employees where employee_id =100;
if v_i>10000 then
raise my_exception; --出发自定义异常
end if;
exception
when my_exception then SYS.DBMS_OUTPUT.PUT_LINE('工资高！！');
end;
存储函数与存储过程
Oracle 提供可以把pl/sql程序存储在数据库中，并可以在任何地方来运行他，这样就叫存储过程或函数
过程和函数的唯一区别是函数总向调用者返回数据，而过程不返回
创建一个函数： 创建函数时如果重名直接覆盖创建
1. 建立内嵌函数
语法： create or replace function 函数名 （id number ，name varchar2）
return number
is --需要使用的变量游标等可以在这里定义
begin --函数体
exception --异常接受处理
end;
实例，写一个可以返回helloworld 的函数（无参函数）
create or replace function get_helloWorld
return varchar2
is
begin
return 'Hello World';
end;
函数创建完成调用此函数：1. select GET_HELLOWORLD from dual;
2. begin
SYS.DBMS_OUTPUT.PUT_LINE(GET_HELLOWORLD);
end;
（有参函数）：create or replace function get_helloWorld(name varchar2) --不需要指定长度
return varchar2
is
begin
return 'Hello World ' || name;
end;
调用：select GET_HELLOWORLD('纯菜鸟') from dual;
2. 关于out函数：pl/sql程序可以通过out型的参数实现有多个返回值
in参数标记表示传递给函数的值在该函数执行中不改变；out标记表示一个值在函数中进行计算并通过该参数传递给调用语句，in out 标记标识传递给函数的值可以变化
并传递给调用语句。若省去标记，则参数隐含为in 。return 包含返回结果的数据类型
实例：定义一个函数，获取给定部门的工资总和 和 该部门的员工总数（定义为out类型的参数）
要求：部门号定义为参数，工资总额定义为返回值
创建函数： create or replace function get_salary(empid number,empNum out number)
return number
is
v_sal number(6) :=0;
cursor my_emp_cur is select salary from employees where department_id = empid;
begin
empNum :=0; --参数只能在函数体中赋值，如果不对请指正
for c in my_emp_cur loop
v_sal := c.salary + v_sal; --工资
empNum := empNum+1;
end loop;
return v_sal;
end;
调用函数：declare
v_count_people_number number(3);--存储人数的变量
begin
SYS.DBMS_OUTPUT.PUT_LINE( get_salary(80,v_count_people_number));
SYS.DBMS_OUTPUT.PUT_LINE(v_count_people_number);
end;
--从调用函数这就可以看到，在上面out函数中并没有显示返回人数，但是在调用的时候，Oracle会带回参数并存到自己定义的变量中，此时输出只会输出函数返回的结果，而输出待会的参数
存储过程创建：获取给定部门的工资总和（out） ，要求：部门号和工资总额定义为参数
create or replace procedure get_sal(empid number,sum_sal out number)
is
cursor my_emp_cur is select salary from employees where department_id = empid;
begin
sum_sal :=0;
for c in my_emp_cur loop
sum_sal := sum_sal+c.salary;
end loop;
end;
我们发现存储过程的语法格式与存储函数的语法格式只是相差 过程是 procedure 无return，，而函数是function 有return
调用：declare
v_count_people_number number(7);
begin
get_sal(80,v_count_people_number);
sys.dbms_output.put_line(v_count_people_number);
end;
触发器：类似过程和函数，都有声明，执行，和异常处理过程的pl/sql块，区别与存储过程，存储过程是由程序调用，而触发器是由事件触发调用，触发器不能接受参数，Oracle事件指的是对表或视图的增删改
可以在增删改操作前或者操作后进行触发，可以对每个行或语句操作上进行触发。
触发器的组成：
1. 触发事件：增删改
2. 触发时间：before after
3. 触发器本身 ：
4. 触发频率：语句级（statement）触发器 和 行级（row）触发器：例如更改一个表的工资，如果更改一个人的触发一次就是行级，如果整个表更改前或后触发就是语句级
创建触发器的语法
create [or replace ] trigger 名字
before | after
insert | update | delete [of column]
on table
[for each row] --行级还是语句级的，写上的话就是行级的，不写就是语句级的
where ---
在teacher 表上的 tname 上添加触发器：当更新update tname的时候 输出：tname被更改
create or replace trigger tea_tname_up
after --事件之前被触发
update of tname on teacher --作用在teacher 表上的tname列中，也可以直接作用在表上，去掉行就行 直接on table
--不写就是语句级的，写for each row 就是行级的
begin --被触发后做的事情
SYS.DBMS_OUTPUT.PUT_LINE('tname被更改');
end;
当更新：update teacher set tname ='纯菜鸟' where tid=1; 时
：new 和 ：old修饰符：比如更改表中的数据，用这两个就可以看到更新前和更新后的数据
修改上面的触发器，使其tname更改后，输出更改前的和更改后的
create or replace trigger tea_tname_up
after
update of tname on teacher
for each row --作用与每行，使用new 和old 必须加上这个
begin
SYS.DBMS_OUTPUT.PUT_LINE('修改前的：'||:old.tname||' 修改后的：'||:new.tname);
end;
更改tname：update teacher set tname ='懒蛋' where tid=1;
实例：当删除teacher 表中的数据的时候，吧删除的数据备份到 teacher_bak;
teacher中的数据
teacher_bak中的数据
触发器创建：create or replace trigger teacher_two_bak
after
delete on teacher
for each row
begin
insert into teacher_bak values (:old.tid,:old.tname);
end;
测试：delete from teacher where tid = 1;
执行完后，teacher 与 teacher_bak 中的数据分别是
......
