一.存储过程
1.首先在pl/sql打开程序窗口中的procedure(程序文件),就是那个⚪中带有P
2.开始编译存储
create [or replace] procedure procedure_name//[ ]有时候会写,有时候不会写。根据题目情况
[(argument1 [in |out |in out] 数据类型,)] //in是输入型参数 ,out输出型参数,in out输入输出型参数,数据类型一般都定义到属性列%type。
is
[声明部分;]//定义存储过程用到的变量,不能加decare关键字
begin
select 表中原有属性列名 into 设的变量名 from 表明 where sno =v_sno;
//注意where这里不要再用到&sno,因为v_sno就是存储过程的输入参数,到时候测试时直接输入
exception
when no_data_found then
dbms_output.put_line('没找到这个学生');
end;
3.右击对象,点测试,然后看到下面参数,找到输入参数名,输入参数,就成功了
题目:创建一个带输入和输出参数的存储过程,根据给定的学生学号返回该学生的姓名、选课数量和平均成绩
CREATE PROCEDURE select_procedure(
v_sno IN INT, -- 输入参数:学生学号
v_sname OUT VARCHAR(), -- 输出参数:学生姓名
v_course_count OUT INT, -- 输出参数:选课数量
v_average_grade OUT FLOAT -- 输出参数:平均成绩
)
IS
-- 声明变量
total_grade FLOAT;
BEGIN
-- 获取学生姓名
SELECT sname INTO v_sname
FROM student
WHERE sno = v_sno;
-- 获取选课数量
SELECT COUNT(*) INTO v_course_count
FROM enrollment
WHERE sno = v_sno;
-- 计算总成绩
SELECT SUM(grade) INTO total_grade
FROM enrollment
WHERE sno= v_sno;
-- 计算平均成绩
IF v_course_count > 0 THEN
SET v_average_grade = total_grade / v_course_count;
ELSE
SET v_average_grade = 0;
END IF;
END;
二.存储函数
1.打开新建下的程序窗口,点function,就是⭕中是F
2.开始编译存储函数
为了确保在创建函数之前,首先删除同名的任何现有函数,以避免冲突或错误。
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION func_deptno';//此语句动态执行SQL语句以删除名为 func_deptno
的函数。EXECUTE IMMEDIATE
语句用于动态执行SQL
EXCEPTION
WHEN OTHERS THEN
NULL; -- 忽略对象不存在的错误 //这是一个异常处理块。如果在执行动态SQL语句期间发生任何异常,它会捕获异常。WHEN OTHERS
子句是一个用于捕获任何异常的通用处理块。在这里,使用 NULL
表示在发生异常时不采取任何操作。
END;
create [or replace] function 存储函数名
[(argument1 [in |out |in out] 数据类型,......)]
return 数据类型 //用于指定函数返回值的数据类型
is
指定函数名 数据类型;//例如 v_empno emp.deptno%type;
begin
select 属性列名 into 设好的变量名 from 表名 where 表中属性列值 =argument1;//argument1就是自己设置的参数名
return 指定的要返回的函数名 ;
end 存储函数名;
3.像上面一样测试,输入值
题目:创建一个有参数的存储函数,根据给定的员工编号返回该员工所在的部门编号。
create or replace function func_deptno
(v_empno in emp.empno%type)
return emp.deptno%type
is
v_deptno emp.deptno%type;
begin
select deptno into v_deptno from emp where empno =v_empno;
return v_deptno;
end func_deptno;
三.触发器
触发器就是对数据库的各种操作触发运行的
1.触发器的分类
(1).根据触发时间:
before,事前触发;
after,在触发语句执行之后被触发;
instead of,替代触发器,触发器操作代替触发语句
(2).根据触发事件
DML(INSERT FELETE UPDATE)
--语句级触发器。不论多少行数据,只能被执行一次
--行级触发器。DML语句执行多少行,行级触发器就调用多少次
DDL(CREATE ALTER DROP)
--数据库级别。数据库中的任何用户都可以用触发器
--用户级别。只有在创建触发器时指定方案的用户才能用。(???这个怎么体现)
2.在测试窗口找到Trigger,开始编译
create or replace trigger trigger_name
before/after
delete /insert /update /update of 属性列名
on table_name
for each row
when(触发条件)//当工资<3000时,给员工加工资之类的。只有当触发条件和触发语句同时满足,触发器才能被触发
begin
:old.salary:=5000;//引用表中的字段,:new.column_name|:old.column_name。一个用的old表,就是原来的表的数据,一个是重新改值,赋予新的值
end trigger_name;
3.测试
试试能不能用。在sql脚本进行DML,DDL操作,然后运行
一般出现的问题
(1)删除0行,没有输出结果
出现的原因:是因为之前操作表中已经没有sno=1的数据了,所以是删除0行
(这里的删除就能不能不要再原表中操作,不要改变我的表数据啊)
(我有一个问题,就是建立了两个delete触发器,但是触发器的操作部分不一样,那进行删除操作的时候,用户是怎么选择连哪个触发器的。)
题目:创建一个行级DELETE触发器,当删除学生表中某个学生信息时,激发触发器,同时删除选课表中该学生所有的选课信息。
create or replace noneditionable trigger delect_stu
before delete on student
for each row
begin
dbms_output.put_line('fuck,您完成删除');
end delect_stu;