Oracle数据库的存储过程、存储函数与触发器

时间:2023-02-13 05:08:19

Oracle数据库的存储过程、存储函数与触发器

在Oracle中使用的语言是plsql过程语言,它是一组sql语句组成的语句块+处理逻辑(条件判断+循环)。
下面我就简要介绍一下plsql语言的用法
语法:
      declate
            变量的声明
       begin
            程序体(一堆sql+if和循环)
            exception
                    when  异常类型  then
                                  处理
       end;
赋值语句:
            变量名  类型:=初始值
            select    字段名    into   变量名
变量的声明:
            数字   number
            字符串    varchar2
            日期     datetime
            引用类型变量 :  变量名    表名.列名%type(引用数据库的列的类型作为某变量的数据类型)
            记录类型变量:    变量名     表名%rowtype(当声明一个变量的值是数据库的一行时使用记录类型,引用数据库表中的一行作为数据类型)
举例:
--利用引用类型变量(7499员工的姓名,职位)
declare
v_name varchar2(10);
v_job emp.job%type;
begin
select ename,job into v_name,v_job from emp where empno=7499;
dbms_output.put_line('7459的姓名是:'|| v_name);
dbms_output.put_line('7459的职位是:'|| v_job);
end;

--记录类型变量查询
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7499;
dbms_output.put_line('7459的姓名是:'|| v_emp.ename);
dbms_output.put_line('7459的职位是:'|| v_emp.job);
end;
if语句
--判断工资的等级
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7499;
if v_sal>5000 then
dbms_output.put_line('7459的工资是:'|| v_sal||'挺高的');
else
if v_sal>300 then
dbms_output.put_line('7459的工资是:'|| v_sal||'一般');
else
dbms_output.put_line('7459的工资是:'|| v_sal||'凑合');
end if;
end if;
end;
循环语句
/*
循环语句
语法1:
loop
循环体(必须要有退出条件)
exit when 条件:如果条件满足退出
end loop;
语法2:
while 条件 loop
循环体(必须有改变条件语句)
end loop;
语法3:
for 循环变量 in 范围 loop---循环变量是不需要定义的
循环体
end loop;
*/
--输出1到10的数组(loop)
declare
v_n number:=1;
begin
loop
dbms_output.put_line(v_n);
v_n:=v_n+1;
exit when v_n>10;
end loop;
end;
--输出1到10的数组(while)
declare
v_n number:=1;
begin
while v_n<11 loop
dbms_output.put_line(v_n);
v_n:=v_n+1;
end loop;
end;
--输出1到10的数组(for)
declare
begin
for i in 1.. 10 loop
dbms_output.put_line(i);
end loop;
end;
游标
/*
游标:
接收查询结果集
语法:
声明游标
cursor 游标变量 is 查询sql语句------没有执行sql
打开游标
open 游标变量;------执行了sql语句
提取数据
fetch 游标变量 into 记录类型变量
关闭游标
close 游标变量
游标的属性:
游标变量%found ----如果返回真就代表现在有数据,如果假就没有数据。
游标变量%notfound---如果返回真就代表现在没有数据,如果假就代表有数据。
*/
--输出所有员工的员工编号和姓名
declare
cursor v_emp_cursor is select * from emp;--声明
v_emp emp%rowtype;
begin
open v_emp_cursor;----打开执行sql
fetch v_emp_cursor into v_emp;
while v_emp_cursor%found loop
dbms_output.put_line('员工编号:'|| v_emp.empno);
fetch v_emp_cursor into v_emp;
end loop;
close v_emp_cursor;
end;
--返回多行值(采用的是游标类型)
/*
系统引用游标sys_refcursor
声明游标时不指定结果集,打开游标时指定结果集装入数据
*/
--输出所有员工的员工编号
declare
v_emp_cursor sys_refcursor;
v_emp emp%rowtype;--记录类型变量
begin
open v_emp_cursor for select * from emp;
fetch v_emp_cursor into v_emp;
while v_emp_cursor%found loop
dbms_output.put_line('员工姓名'||v_emp.ename);
fetch v_emp_cursor into v_emp;
end loop;
close v_emp_cursor;
end;
--动态的输入部门的员工编号和姓名
declare
cursor v_emp_cursor(v_deptno number) is select * from emp where deptno=v_deptno;
v_emp emp%rowtype;
v_d number:=#
begin
open v_emp_cursor(v_d);--打开执行sql
fetch v_emp_cursor into v_emp;
while v_emp_cursor%found loop
dbms_output.put_line('员工编号:'|| v_emp.ename);
fetch v_emp_cursor into v_emp;
end loop;
close v_emp_cursor;
end;
异常
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp;
exception
when others then
dbms_output.put_line('其他异常');
end;
--自定义异常
--如果没有员工的部门就是一种异常
declare
cursor v_emp_cursor is select * from emp where deptno=50;
v_emp emp%rowtype;
v_noemp exception;
begin
open v_emp_cursor;
fetch v_emp_cursor into v_emp;
if v_emp_cursor%notfound then
raise v_noemp;
end if;
exception
when v_noemp then
dbms_output.put_line('我异常了');
end;
存储过程
/*
存储过程
是一段封装的plsql代码块,对重复利用的代码进行封装,一组
为了完成特定功能的语句集,经编译后存储在数据库中,用户通过指定
存储过程的名字并给出参数来执行它。
语法:
create or replace procedure 存储过程名称(参数名称 in|out 参数类型)
is|as
声明变量
begin
plsql程序体;
end;
*/
--没有返回值(增加工资输出前后工资值)
create or replace procedure add_sal(v_empno in number)
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line('增加前的工资:'|| v_sal);
update emp set sal=sal+100 where empno=v_empno;
commit;
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line('增加后的工资:'|| v_sal);
end;
--调用存储过程
begin
add_sal(7369);
end;
--返回单个值
--统计某个员工年薪
create or replace procedure count_year_sal(v_empno in number,v_year_sal out number)
is
begin
select sal*12+nvl(comm,0) into v_year_sal from emp where empno=v_empno;
end;
--调用存储过程
declare
v_ys number;
begin
count_year_sal(7369,v_ys);
dbms_output.put_line(v_ys);
end;

--返回多行值(采用的是游标类型)
--查询指定部门下的所有员工信息(用系统引用游标实现)
create or replace procedure deptinfo(deno in number,v_emr_cursor out sys_refcursor)
is
begin
open v_emr_cursor for select * from emp where deptno=deno;
end;
--调用存储过程
declare
v_emp_cursor sys_refcursor;
v_emp emp%rowtype;
begin
deptinfo(20,v_emp_cursor);
fetch v_emp_cursor into v_emp;
while v_emp_cursor%found loop
dbms_output.put_line('员工编号'||v_emp.empno);
fetch v_emp_cursor into v_emp;
end loop;
close v_emp_cursor;
end;
存储函数
/*
存储函数
和存储过程几乎一样。
语法:
create or replace function 存储函数名称(参数 in|out 类型)
return 返回类型
is|as
begin
程序体(必须有return语句返回结果)
end;
*/
--统计某个员工年薪
create or replace function count_year_sal_fun(v_empno in number)
return number
is
v_sal number;
begin
select sal*12+nvl(comm,0) into v_sal from emp where empno=v_empno;
return v_sal;
end;
--调用存储函数
declare
v_ysl number;
begin
v_ysl:=count_year_sal_fun(7499);
dbms_output.put_line(v_ysl);
end;
存储过程与存储函数的区别:
1、创建关键字 不一样 procedure  function
2、创建函数必须使用return  指定函数返回值类型
3、函数必须在begin和end之间通过return返回一个变量
4、函数的调用必须有变量接收返回值。
5、函数可以用在select语句中。


触发器
/*
触发器
类似于监听器,用来监听表的操作
语法:
create or replace trigger 触发器名称
begore | after--触发时机
insert|update|delete--触发条件
on 表名---触发对象
begin
程序体
end;
*/
--插入员工表数据后触发器自动输出欢迎语句
--但是触发器一般不使用after,因为数据回滚,触发器还是会执行,不安全。
create or replace trigger welcome
after
insert
on bonus

begin
dbms_output.put_line('欢迎欢迎');
end;

insert into bonus(ename) values('张三');

--行级触发器for each row
create or replace trigger nolosal
before
update
on bonus
for each row
begin
if :new.sal< :old.sal then
raise_application_error(-20001,'不能少我工资');
end if;
end;

update bonus set sal=1000 where ename='张三';
--触发器实现id自增长
create sequence bonus_auto;
create or replace trigger bonus_id
before
insert
on bonus
for each row
begin
select bonus_auto.nextval into :new.ename from dual;
end;

insert into bonus(sal) values(1300);