数据库PostgreSQL掌握存储过程的创建执行删除操作.docx
《数据库PostgreSQL掌握存储过程的创建执行删除操作.docx》由会员分享，可在线阅读，更多相关《数据库PostgreSQL掌握存储过程的创建执行删除操作.docx（23页珍藏版）》请在冰豆网上搜索。
数据库PostgreSQL掌握存储过程的创建执行删除操作
数据库系统原理实验报告
一、存储过程实验
1.实验目的
（1）掌握存储过程的创建操作。
（2）掌握存储过程的执行操作。
（3）掌握存储过程的删除操作。
2.实验内容
（1）创建带输入参数的存储过程。
（2）执行所创建的存储过程。
（3）删除所有新创建的存储过程。
3.示例
例1：
Createorreplacefunctionloop1（）returnsvoidas$$
DECLARE
iint:
=1;
BEGIN
LOOP
INSERTINTOemployee_1511630117
values（i）;
i:
=i+1;
exitwheni>10;
ENDLOOP;
END;
$$languageplpgsql;
selectloop1（）
例2：
Createfunctionsales_tax（subtotalreal）returnsrealas$$
begin
returnsubtotal*0.06;
end;
$$languageplpgsql;
selectsales_tax（0.8）;
例3：
Createorreplacefunctionselect_test（）returnstable（nochar（6））as$$
BEGIN
returnQUERY
selectdepartmentidfromemployee_1511630117;
END;
$$languageplpgsql;
select*fromselect_test（）;
selectselect_test（）;
dropfunctionselect_test（）
4.实验步骤
对应于employee数据库
（1）创建一个无参存储过程，查询雇员的编号，姓名，性别，出生日期；
createorreplacefunctionEmployeeInfo（）returns
table（employeeidchar（6）,namechar（10）,birthdaydate,sexbit
（1））
as$$
begin
returnquery
selectemployee_1511630117.employeeid,employee_1511630117.name,
employee_1511630117.birthday,employee_1511630117.sex
fromemployee_1511630117;
end;
$$
languageplpgsql;
select*fromEmployeeInfo（）;
（2）创建一个带参的存储过程，根据传入的employeeid查询employee表中的其他信息
createorreplacefunctionemp_info（idtchar（6））returns
table（idchar（6）,namechar（10）,birthdate,sexbit
（1））as$$
begin
returnquery
selectemployee_1511630117.employeeid,employee_1511630117.name,
employee_1511630117.birthday,employee_1511630117.sex
fromemployee_1511630117
whereidt=employeeid;
end;
$$languageplpgsql;
select*fromemp_info（'1002'）;
（3）创建一个带参数的存储过程，根据输入的employeeid，departmentid查询雇员的其他信息
createorreplacefunctionEmp_Info2（idtchar（6）,departmentidtchar（3））
returnstable（idchar（6）,namechar（10）,sexbit
（1）,departmentchar（3））as$$
begin
returnquery
selectemployee_1511630117.employeeid,employee_1511630117.name,employee_1511630117.sex,employee_1511630117.departmentid
fromemployee_1511630117
whereidt=employeeidand
departmentidt=employee_1511630117.departmentid;
end;
$$languageplpgsql;
查询：
select*fromemp_info2（'1001','1'）;
（4）删除创建的存储过程
dropfunctionemployeeInfo（）;
5.实验过程中遇到的问题、解决办法及心得体会。
遇到的问题：
解决办法：
需要明确所查询的对象将select对象改为
selectemployee_1511630117.employeeid,employee_1511630117.name,employee_1511630117.sex,employee_1511630117.departmentid。
运行结果如实验过程中的示图所示。
心得体会：
存储过程实验在参考并理解例子的前提下做出来并不难，但就如遇到的问题中所示，实验过程中有存在细节问题没有注意到，这是以后一定要注意的地方。
二触发器实验
1.实验目的
（1）掌握触发器的创建、修改和删除操作。
（2）掌握触发器的触发执行。
（3）掌握触发器与约束的不同。
2.实验要求
（1）创建触发器。
（2）触发器执行触发器。
（3）验证约束与触发器的不同作用期。
（4）删除新创建的触发器。
3.实验示例
（1）创建触发器
①启动PostgreSQL查询编辑器，选择要操作数据库。
②在查询命令窗口中输入以下CREATETRIGGER语句，创建触发器和相应的存储过程。
CREATEORREPLACEFUNCTIONDEBUG（）RETURNSTRIGGERAS$$
DECLARE
BEGIN
RAISENOTICE'%',TG_NAME;
RETURNNEW;
END;
$$
LANGUAGEPLPGSQL;
为sc表创建一个基于UPDATE操作和DELETE操作的复合型触发器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器被激活生效，显示相关的操作信息。
CREATETRIGGERSC_TRIGGER_AFTER
AFTERUPDATEOFGRADEORDELETE
ONSC_1511630117
FOREACHROW
EXECUTEPROCEDUREDEBUG（）;
为sc表创建一个基于UPDATE操作和DELETE操作的复合型触发器，当修改该表中的成绩信息之前或者删除成绩记录之前，触发器被激活生效，显示相关的操作信息。
CREATETRIGGERSC_TRIGGER_BEFORE
BEFOREUPDATEOFGRADEORDELETE
ONSC_1511630117
FOREACHROW
EXECUTEPROCEDUREDEBUG（）;
（2）触发触发器
①在查询命令窗口中输入以下UPDATEsc语句，修改成绩列，激发触发器。
UPDATESC_1511630117
SETGRADE=GRADE+5
WHERECNO='1'
②在查询命令窗口中输入以下UPDATEsc语句修改非成绩列，激发触发器。
UPDATESC_1511630117
SETCNO='113'
WHERECNO='103'
③在查询命令窗口中输入以下DELETEsc语句，删除成绩记录，激发触发器。
DELETEFROMSC_1511630117
WHERECNO='2'
（3）比较约束与触发器的不同作用期
①在查询命令窗口中输入并执行以下ALTERTABLE语句，为sc表添加一个约束，使得成绩只能大于等于0且小于等于100。
ALTERTABLESC_1511630117
ADDCONSTRAINTSK_SC
CHECK（GRADE>=0ANDGRADE<=100）
②在查询命令窗口中输入并执行以下UPDATEsc语句，查看执行结果。
UPDATESC_1511630117
SETGRADE=120
WHERECNO='1'
因为刚添加了一个约束使得成绩只能大于等于0且小于等于100，所以更新成绩为120显示失败
查看完整性约束检查与触发器的执行顺序。
③在查询命令窗口中输入执行以下UPDATEsc语句，查看执行结果。
UPDATESC_1511630117
SETGRADE=90
WHERECNO='1'
（4）删除新创建的触发器
在查询命令窗口中输入DROPTRIGGER语句，删除新创建的触发器。
droptriggerSC_TRIGGER_AFTERonsc_1511630117;
droptriggerSC_TRIGGER_beforeonsc_1511630117
4.练习
（1）在Student表中编写insert的触发器，假如每个班的学生不能超过6个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现。
createorreplacefunctioninsert（）
returnstrigger
languageplpgsql
as$function$
begin
if（（selectcount（*）fromstudent_1511630117）>6）then
raiseexception'操作失败';
else
raisenotice'操作成功';
endif;
returnnull;
end
$function$;
创建触发器：
createtriggerstudent_1511630117_trigger_insert
afterinsert
onstudent_1511630117
foreachrow
executeprocedureinsert（）;
插入第五个数据显示成功：
INSERT
INTOstudent_1511630117
（SNO,SNAME,SSEX,SAGE,SDEPT）
VALUES
（'200215126','第5个','男',20,'CS'）
插入第六个数据显示成功：
INSERT
INTOstudent_1511630117
（SNO,SNAME,SSEX,SAGE,SDEPT）
VALUES
（'200215127','第6个','女',21,'CS'）
插入第七个数据显示失败：
INSERT
INTOstudent_1511630117
（SNO,SNAME,SSEX,SAGE,SDEPT）
VALUES
（'200215128','第7个','男',23,'CS'）
（2）在SC表上编写update触发器，当修改SC表中的grade字段时将其修改前后的信息输出到一张表table_1511630117（oldfloat,newfloat）。
创建表：
CREATETABLEtable_1511630117（oldsmallint,newsmallint）;
创建存储过程：
createorreplacefunctionupdate（）
returnstrigger
languageplpgsql
as$function$
begin
insertintotable_1511630117selectold.grade,new.grade;
raisenotice'已记录';
returnnull;
end
$function$;
创建触发器：
createtriggersc_1511630117_trigger_update
afterupdate
onsc_1511630117
foreachrow
executeprocedureupdate（）;
更新成绩：
UPDATESC_1511630117
SETGRADE=GRADE+5
WHERECNO='1'
5、心得体会：
在实验中很多粗心造成的问题，比如代码写错字母格等。
通过课堂的理论知识学习和实验课的上机实验，让我更能理解操作系统的知识。
升级会员 