数据库PostgreSQL掌握存储过程的创建执行删除操作.docx

1、数据库PostgreSQL掌握存储过程的创建执行删除操作数据库系统原理实验报告一、存储过程实验 1. 实验目的 (1)掌握存储过程的创建操作。 (2)掌握存储过程的执行操作。 (3)掌握存储过程的删除操作。 2. 实验内容 (1)创建带输入参数的存储过程。 (2)执行所创建的存储过程。 (3)删除所有新创建的存储过程。 3. 示例 例1：Create or replace function loop1() returns void as $DECLARE i int :=1;BEGIN LOOP INSERT INTO employee_1511630117 values(i); i:=i+1

2、; exit when i10; END LOOP;END;$ language plpgsql;select loop1()例2：Create function sales_tax(subtotal real) returns real as $begin return subtotal *0.06;end;$language plpgsql;select sales_tax(0.8);例3：Create or replace function select_test() returns table (no char(6) as $BEGIN return QUERY select depa

3、rtmentid from employee_1511630117;END;$ language plpgsql;select * from select_test();select select_test();drop function select_test()4. 实验步骤 对应于employee数据库 （1） 创建一个无参存储过程，查询雇员的编号，姓名，性别，出生日期；create or replace function EmployeeInfo() returns table(employeeid char(6),name char(10),birthday date,sex bit

4、(1)as $beginreturn queryselect employee_1511630117.employeeid,employee_1511630117.name,employee_1511630117.birthday,employee_1511630117.sexfrom employee_1511630117;end ;$language plpgsql;select * from EmployeeInfo();(2) 创建一个带参的存储过程，根据传入的employeeid查询employee表中的其他信息create or replace function emp_info(

5、idt char(6) returns table(id char(6),name char(10),birth date,sex bit(1) as $begin return query select employee_1511630117.employeeid,employee_1511630117.name, employee_1511630117.birthday,employee_1511630117.sex from employee_1511630117 where idt = employeeid;end;$ language plpgsql;select * from em

6、p_info(1002);(3) 创建一个带参数的存储过程，根据输入的employeeid，departmentid 查询雇员的其他信息create or replace function Emp_Info2(idt char(6),departmentidt char(3)returns table(id char(6),name char(10),sex bit(1),department char(3) as $begin return query select employee_1511630117.employeeid,employee_1511630117.name,employe

7、e_1511630117.sex,employee_1511630117.departmentid from employee_1511630117 where idt = employeeid and departmentidt=employee_1511630117.departmentid;end;$ language plpgsql;查询：select * from emp_info2(1001,1);(4)删除创建的存储过程drop function employeeInfo();5. 实验过程中遇到的问题、解决办法及心得体会。 遇到的问题：解决办法：需要明确所查询的对象将selec

8、t对象改为select employee_1511630117.employeeid,employee_1511630117.name,employee_1511630117.sex,employee_1511630117.departmentid。运行结果如实验过程中的示图所示。心得体会：存储过程实验在参考并理解例子的前提下做出来并不难，但就如遇到的问题中所示，实验过程中有存在细节问题没有注意到，这是以后一定要注意的地方。二 触发器实验 1. 实验目的 (1)掌握触发器的创建、修改和删除操作。 (2)掌握触发器的触发执行。 (3)掌握触发器与约束的不同。 2. 实验要求 (1)创建触发器。

9、(2)触发器执行触发器。 (3)验证约束与触发器的不同作用期。 (4)删除新创建的触发器。 3. 实验示例 (1)创建触发器 启动 PostgreSQL 查询编辑器，选择要操作数据库。 在查询命令窗口中输入以下 CREATE TRIGGER 语句，创建触发器和相应的存储过程。CREATE OR REPLACE FUNCTION DEBUG() RETURNS TRIGGER AS $DECLAREBEGINRAISE NOTICE %,TG_NAME;RETURN NEW;END;$LANGUAGE PLPGSQL;为sc表创建一个基于 UPDATE 操作和 DELETE 操作的复合型触发器，

10、当修改了该表中的成绩信息或者删除了成绩记录时，触发器被激活生效，显示相关的操作信息。 CREATE TRIGGER SC_TRIGGER_AFTERAFTER UPDATE OF GRADE OR DELETEON SC_1511630117FOR EACH ROWEXECUTE PROCEDURE DEBUG();为sc表创建一个基于 UPDATE 操作和 DELETE 操作的复合型触发器，当修改该表中的成绩信息之前或者删除成绩记录之前，触发器被激活生效，显示相关的操作信息。 CREATE TRIGGER SC_TRIGGER_BEFOREBEFORE UPDATE OF GRADE OR

11、DELETEON SC_1511630117FOR EACH ROWEXECUTE PROCEDURE DEBUG();(2)触发触发器 在查询命令窗口中输入以下 UPDATE sc 语句，修改成绩列，激发触发器。 UPDATE SC_1511630117SET GRADE=GRADE+5WHERE CNO=1在查询命令窗口中输入以下 UPDATE sc 语句修改非成绩列，激发触发器。 UPDATE SC_1511630117SET CNO=113WHERE CNO=103 在查询命令窗口中输入以下 DELETE sc 语句，删除成绩记录，激发触发器。 DELETE FROM SC_15116

12、30117WHERE CNO=2(3) 比较约束与触发器的不同作用期 在查询命令窗口中输入并执行以下 ALTER TABLE 语句，为 sc 表添加一个约束，使得成绩只能大于等于 0 且小于等于 100。 ALTER TABLE SC_1511630117ADD CONSTRAINT SK_SCCHECK(GRADE=0 AND GRADE 6) then raise exception 操作失败; else raise notice 操作成功; end if; return null;end$function$;创建触发器：create trigger student_1511630117_

13、trigger_insertafter inserton student_1511630117for each rowexecute procedure insert();插入第五个数据显示成功：INSERTINTO student_1511630117(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(200215126,第5个,男,20,CS)插入第六个数据显示成功：INSERTINTO student_1511630117(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(200215127,第6个,女,21,CS)插入第七个数据显示失败：INSERTIN

14、TO student_1511630117(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(200215128,第7个,男,23,CS)(2)在 SC 表上编写 update 触发器，当修改 SC 表中的 grade 字段时将其修改前后的信息输出到一张表 table_1511630117(old float, new float)。 创建表：CREATE TABLE table_1511630117(old smallint,new smallint);创建存储过程：create or replace function update() returns trigger lan

15、guage plpgsql as $function$begin insert into table_1511630117 select old.grade,new.grade; raise notice 已记录; return null;end$function$;创建触发器：create trigger sc_1511630117_trigger_updateafter updateon sc_1511630117for each rowexecute procedure update();更新成绩：UPDATE SC_1511630117SET GRADE=GRADE+5WHERE CNO=15、心得体会：在实验中很多粗心造成的问题，比如代码写错字母格等。通过课堂的理论知识学习和实验课的上机实验，让我更能理解操作系统的知识。