实验5 存储过程和触发器(20181210)oracle

Oracle存储过程总结1、创建存储过程create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as--声明变量(变量名变量类型)begin--存储过程的执行体end test;打印出输入的时间信息E.g:create or replace procedure test(workDate in Date) isbegindbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-d d));end test;2、变量赋值变量名 := 值;E.g：create or replace procedure test(workDate in Date) isx number(4,2);beginx := 1;end test;3、判断语句:if 比较式 then begin end; end if;E.gcreate or replace procedure test(x in number) isbeginif x >0 thenbeginx := 0 - x;end;end if;if x = 0 thenbeginx: = 1;end;end if;end test;4、For 循环For ... in ... LOOP--执行语句end LOOP;(1)循环遍历游标create or replace procedure test() asCursor cursor is select name from student;name varchar(20);beginfor name in cursor LOOPbegindbms_output.putline(name);end;end LOOP;end test;(2)循环遍历数组create or replace procedure test(varArray in myPackage.TestArray) as --(输入参数varArray 是自定义的数组类型，定义方式见标题6)i number;begini := 1; --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。

数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试142学号：**********姓名：***日期：2016-11-141 实验目的：1)掌握SQL进行存储过程创建和调用的方法；2)掌握SQL进行触发器定义的方法，理解触发器的工作原理；3)掌握触发器禁用和重新启用的方法。

2 实验平台：操作系统：Windows xp。

实验环境：SQL Server 2000以上版本。

3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。

1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。

存储过程定义代码：CREATE PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND o=o存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。

存储过程定义代码：CREATE PROCEDURE XM_PROC @sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=@sname)SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE o=o AND SC.sno=S.sno ANDS.sn=@snameELSEPRINT'无该姓名的同学。

'END运行截图：3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。

如果没有该专业，则显示“无此专业”。

存储过程定义代码：CREATE PROCEDURE XBNL_PROC@departName VARCHAR(30)='计算机',@begin INT,@end INTASDECLARE @numOfBoys INTDECLARE @numOfGirls INTDECLARE @d# VARCHAR(3)DECLARE @result VARCHAR(50)BEGINSELECT @d# = dno FROM D WHERE dn=@departNameIF @d# IS NOT NULLBEGINSELECT @numOfBoys =COUNT(sno)FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='男'SELECT@numOfGirls =COUNT(sno)FROM S WHERE age BETWEEN@begin AND@end AND dno=@d# AND sex='女'SET @result = @departName+'专业年龄在'+CAST(@begin AS VARCHAR(3))+'-'+CAST(@end AS VARCHAR(3))+'之间的男生有'+CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+CAST(@numOfGirls AS VARCHAR(3))+'人'ENDELSESET @result='无此专业。

南华大学计算机科学与技术学院实验报告（2012~2013 学年度第二学期）课程名称Oracle高级数据库开发设计实验名称存储过程与触发器基本操作姓名学号专业班级地点教师前提表脚本：create table S_RZ0122 (Sno varchar2(11) primary key,Sname varchar2(20) not null,Ssex varchar(2) not null ,Sage number(2) not null,Sdept varchar(20) not null)create table C_RZ0122(Cno varchar2(20) primary key,Cname varchar2(20) not null ,Ccredit number(2) not null)create table SC_RZ0122(Sno varchar2(11) not null,Cno varchar2(20) not null,Score number(3) ,primary key(Sno ,Cno),foreign key(Sno) references S_RZ0122(Sno),foreign key(Cno) references C_RZ0122(Cno))INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811101,'李勇','男',21,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811102,'刘晨','男',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811103,'王敏','女',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811104,'张小红','女',19,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821101,'张立','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821102,'吴宾','女',19,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821103,'张海','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831101,'钱小平','女',21,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831102,'王大力','男',20,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831103,'张珊珊','女',19,'通信工程系')---增加课程信息脚本：---insert allINSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C001','高等数学',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C002','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C003','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C004','计算机文化学',2) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C005','VB',2)INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C006','数据库基础',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C007','数据结构',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C008','计算机网络',4) select * from C_RZ0122;---增加关联学生和课程信息脚本：---INSERT ALLINSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C001',96)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C002',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C003',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C005',62)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C001',92)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C002',90)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C004',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C001',76)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C004',85)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C005',73)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C004',78)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C005',65)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C007',0)select Sno,Cno,Score from SC_RZ0122;一．实验题目存储过程与触发器基本操作二．实验要求掌握对存储过程和触发器的创建、修改等基本操作。

Oracle数据库的函数，存储过程，程序包，游标，触发器Oracle⾃定义函数函数的主要特性是它必须返回⼀个值。

创建函数时通过 RETURN ⼦句指定函数返回值的数据类型。

函数的⼀些限制：●函数只能带有 IN 参数，不能带有 IN OUT 或 OUT 参数。

●形式参数必须只使⽤数据库类型，不能使⽤ PL/SQL 类型。

●函数的返回类型必须是数据库类型Create function 函数名称 return 返回值类型 asBegin····End 函数名称;--创建不带参数函数，返回t_book中书的数量create function getBookCount return number asbegindeclare book_count number;beginselect count(*) into book_count from t_book;return book_count;end;end getBookCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表t_book中有'||getBookCount()||'本书');end;--创建带参数函数，查找某个表的记录数create function getTableCount(table_name varchar2) return number asbegindeclare recore_count number;query_sql varchar2(300);--定义sql语句beginquery_sql:='select count(*) from '||table_name;--execute immediate：⽴即执⾏该SQL语句execute immediate query_sql into recore_count;return recore_count;end;end getTableCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表中有'||getTableCount('t_book_log')||'条数据');end;CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)/* 参数、指定返回类型 */RETURN varchar2AS/* 定义局部变量 */min_price NUMBER;max_price NUMBER;BEGINSELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_priceFROM itemfile;IF price >= min_price AND price <= max_price THENRETURN '输⼊的单价介于最低价与最⾼价之间';ELSERETURN '超出范围';END IF;END;匿名块执⾏函数p NUMBER := 300;MSG varchar2(200);BEGINMSG := item_price_range(p);DBMS_OUTPUT.PUT_LINE(MSG);END;SELECT查询调⽤（因为函数必须有返回值）SELECT myfunction FROM dual;Oracle存储过程存储过程(Stored Procedure)，就是⼀组⽤于完成特定功能的SQL语句集，该SQL语句集经过编译后存储在数据库中。

Oracle基本语法存储过程触发器介绍PL/SQL 语言介绍PL/SQL是Oracle数据库的编程语言。

我们可以将PL/SQL 看成是对标准SQL的扩展，使用它编写的代码通常是放在Oracle数据库中执行。

概述PL/SQL是Oracle数据库专用的语言，具有第三代编程语言和第四代编程语言的特性，对大小写不敏感。

基于程序块的开发PL/SQL代码使用了程序块（block），利用模块化的方式进行构建，每一个程序块都是由一组逻辑上的变量、可执行代码以及异常处理代码构成。

其中，只有可执行代码部分是必须的。

set serveroutput ondeclarel_text varchar2(100); --请注意这里的分号beginl_text:='Hello,world!'; --请注意这里的冒号dbms_output.put_line(l_text); --请注意这里的点号exceptionwhen others thendbms_output.put_line('出现问题啦...');raise;end;/请注意观察上述代码，每一个执行语句都是使用分号作为结束标记。

由declare部分引出的是程序块的声明部分，通常情况下声明部分是定义所有变量和常量的地方，该部分是可选的。

由begin、end引出的是程序块的执行部分，又称为执行体，这里通常是处理执行逻辑的地方，该部分是必须的。

（注意：可以直接写null，代表什么操作都不做）由exception部分引出的是程序块的异常处理部分，这里是我们检查和控制可能会在程序块中遇到的错误的地方，Oracle 会在错误发生时，自动的跳转到这里。

声明通过上面的代码，我们可以看到在声明中可以使用变量和常量。

请注意，变量赋初值是可选的，而常量则是必须的。

变量和常量在使用之前必须在程序块的声明部分进行声明（或定义）。

declarel_number_variable number:=50;beginnull;end;/declarel_number_constant constant number:=20;beginnull;end;/现在我们尝试下述做法，请注意Oracle的出错提示declarel_number_constant constant number;beginnull;end;/Oracle提示“常数'L_NUMBER_CONSTANT' 的说明必须包含初始赋值”declarel_number_constant constant number :=20;beginl_number_constant:=50;end;/Oracle提示“表达式'L_NUMBER_CONSTANT' 不能用作赋值目标”前一种异常会在程序块的编译期间被捕获，并且不能恢复，程序块的异常处理部分也不能捕获这种错误。

ORACLE存储过程ORACLE存储过程（Oracle Stored Procedure）是一组可在oracle数据库中定义的、存储在数据库中、可以多次调用的SQL语句的集合。

存储过程类似于一段预编译过的、可重复使用的代码段，它们可以有效地减少网络通信的开销，并提高数据库应用程序的性能。

在ORACLE数据库中，存储过程是由PL/SQL语言编写的，PL/SQL （Procedural Language/Structured Query Language）是ORACLE数据库中主要的过程式语言，它结合了SQL语言的数据操作和控制结构，以及基于第三代语言的过程式编程。

一个存储过程可以包含多个SQL语句，这些SQL语句可以是查询语句、更新语句、插入语句等。

存储过程可以根据需要接受参数，这些参数可以是输入参数也可以是输出参数，使存储过程更加灵活和通用。

存储过程的优势主要体现在以下几个方面：1.提高性能：存储过程在数据库服务器上执行，可以减少网络通信的开销。

此外，存储过程可以预编译、优化和缓存，从而提高数据库应用程序的性能。

2.简化应用程序逻辑：存储过程可以封装复杂的业务逻辑，将它们集中管理，使应用程序的代码更加简洁和易于维护。

3.增强安全性：存储过程可以定义访问数据库的权限，并且只有授予存储过程执行权限的用户才能调用存储过程。

这样可以保护数据库中的数据安全。

4.提高代码重用性：存储过程可以在不同的应用程序中多次调用，从而提高代码的重用性。

这样可以减少开发工作量，提高开发效率。

5.支持事务处理：存储过程可以包含事务处理逻辑，可以保证数据库操作的原子性和一致性。

编写一个存储过程的基本步骤如下：1.创建存储过程：使用CREATEPROCEDURE语句创建存储过程。

```sqlCREATE PROCEDURE procedure_name [ (parameter_list) ]ISBEGIN-- SQL statementsEND;```2.编写存储过程的SQL语句：在BEGIN和END之间编写存储过程的SQL语句，可以包含SELECT、INSERT、UPDATE、DELETE等。

Oracle数据库基本操作五——存储过程与触发器4．存储过程与触发器：例7-1： (存储过程) 创建⼀个显⽰学⽣总⼈数的存储过程。

set serveroutput oncreate or replace procedure student_countasp1 number(3);beginselect count(*) into p1 from student;dbms_output.put_line('学⽣总⼈数是：'||p1);end;/execute student_count();例7-2： (存储过程) 创建显⽰学⽣信息的存储过程STUDENT_LIST，并引⽤STU_COUNT存储过程。

set serveroutput on;create or replace procedure student_listascursor select_hand is/*定义游标⽅便使⽤*/select sno,rtrim(sname) as sname,ssex,sage,sdept,sclass from student;beginfor i in select_hand loopdbms_output.put_line(i.sno||''||i.sname||''||i.ssex||''||i.sage||'' ||i.sdept||''||i.sclass);end loop;STUDENT_COUNT();end;/execute student_list();例7-3： (存储过程) 创建⼀个显⽰学⽣平均成绩的存储过程。

set serveroutput on;create or replace procedure student_avgs(no in student.sno%type)asavgs1 number(3,1);beginselect avg(score) into avgs1 from score group by sno having sno = no ;dbms_output.put_line('学号为：'||no||' 的平均成绩是：'|| avgs1);end;/execute student_avgs('96002');例7-4： (存储过程) 创建显⽰所有学⽣平均成绩的存储过程。

Oracle中触发器如何调⽤存储过程1--创建测试表B_TEST_TABLE，稍后会在本表上增加触发器，在插⼊本表的同时复制⼀份数据到B_TEST_TABLE2中2create table B_TEST_TABLE3(4 c1 VARCHAR2(200),5 d2 DATE6);78--创建B_TEST_TABLE2表，在插⼊B_TEST_TABLE数据时，复制⼀份数据到本表中9create table B_TEST_TABLE210(11 c1 VARCHAR2(200),12 d2 DATE13);1415--创建存储过程，往B_TEST_TABLE2中插⼊⼀条数据16create or replace procedure TestPro(C1 VARCHAR2) is17begin18INSERT INTO B_TEST_TABLE2 VALUES (C1, SYSDATE);19--因为此存储过程是触发器中调⽤的，所以不能增加提交命令20--COMMIT;21end TestPro;22--下⾯这个斜杠不能去掉，否则会导致触发器和存储过程或其他脚本编译在同⼀个对象中，每个单独的对象都应⽤此符号分开(建表等操作除外) 23/24--创建触发器，before insert on b_test_table为在插⼊b_test_table数据之前触发，可以改成after insert on b_test_table25--或者before update on b_test_table等选项26create or replace trigger TEST_TABLE_TRIGGER_INST_BF27 before insert on b_test_table28for each row29begin30--调⽤存储过程:new代表的是新的记录对象，可以直接取值或修改该记录的每⼀列，:new.c1是取新纪录的c1列值，:new.d2是取新纪录的d2列值31 TestPro(:new.c1);32--如果不想⽤存储过程也可以直接这样写33--INSERT INTO B_TEST_TABLE2 VALUES (:new.c1, :new.d2);34end TEST_TABLE_TRIGGER_INST_BF;35/36--插⼊测试数据37insert into B_TEST_TABLE values ('1', sysdate);38commit;3940--查询测试表数据41select*from B_TEST_TABLE;42select*from B_TEST_TABLE2;。

南华大学计算机科学与技术学院实验报告（2012~2013 学年度第二学期）课程名称Oracle高级数据库开发设计实验名称存储过程与触发器基本操作姓名学号专业班级地点教师前提表脚本：create table S_RZ0122 (Sno varchar2(11) primary key,Sname varchar2(20) not null,Ssex varchar(2) not null ,Sage number(2) not null,Sdept varchar(20) not null)create table C_RZ0122(Cno varchar2(20) primary key,Cname varchar2(20) not null ,Ccredit number(2) not null)create table SC_RZ0122(Sno varchar2(11) not null,Cno varchar2(20) not null,Score number(3) ,primary key(Sno ,Cno),foreign key(Sno) references S_RZ0122(Sno),foreign key(Cno) references C_RZ0122(Cno))INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811101,'李勇','男',21,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811102,'刘晨','男',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811103,'王敏','女',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811104,'张小红','女',19,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821101,'张立','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821102,'吴宾','女',19,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821103,'张海','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831101,'钱小平','女',21,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831102,'王大力','男',20,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831103,'张珊珊','女',19,'通信工程系')---增加课程信息脚本：---insert allINSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C001','高等数学',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C002','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C003','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C004','计算机文化学',2) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C005','VB',2)INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C006','数据库基础',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C007','数据结构',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C008','计算机网络',4) select * from C_RZ0122;---增加关联学生和课程信息脚本：---INSERT ALLINSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C001',96)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C002',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C003',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C005',62)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C001',92)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C002',90)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C004',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C001',76)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C004',85)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C005',73)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C004',78)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C005',65)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C007',0)select Sno,Cno,Score from SC_RZ0122;一．实验题目存储过程与触发器基本操作二．实验要求掌握对存储过程和触发器的创建、修改等基本操作。

实验五触发器、存储过程操作实验本实验需要2学时。

请大家先根据“触发器.doc”文档完成相关操作，再进行本次实验。

介绍完“存储过程”后，需上交本次实验报告。

一、实验目的（1）掌握SQL Server中的触发器的使用方法；（2）掌握存储过程的操作方法。

二、实验内容1. 创建、查看、修改和删除触发器。

2. 创建、查看、修改和删除存储过程。

三、实验方法1. 触发器的操作（1）建立触发器方法一：使用企业管理器首先，打开企业管理器，定位数据库并找到要创建触发器的表；然后，右击该表名，在弹出的快捷菜单中选择“设计表”，在打开的“设计表”窗口中单击按钮，打开如图1所示窗口。

图1 触发器属性窗口最后，在触发器属性窗口中输入触发器的内容，并单击“确定”按钮。

方法二：使用CREATE TRIGGER语句语法：CREATE TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]}AS<SQL 语句块>（2）查看触发器方法一：使用企业管理器方法二：使用T-SQL语句- sp_help <触发器名>- sp_helptext <触发器名>- sp_depends <触发器名>（3）修改触发器方法一：使用企业管理器方法二：使用ALTER TRIGGER语句语法：ALTER TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]} AS<SQL 语句块>（4）删除触发器方法一：使用企业管理器方法二：使用DROP TRIGGER语句语法为：DROP TRIGGER <触发器名>2. 存储过程的操作（1）建立存储过程方法一：使用建立存储过程向导方法二：使用企业管理器方法三：使用SQL语句（CREATE PROCEDURE）语法：CREATE PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句> | <语句块> }（2）查看存储过程方法一：使用企业管理器方法二：使用SQL语句（系统存储过程）- sp_help <存储过程名>- sp_helptext <存储过程名>- sp_depends <存储过程名>（3）修改存储过程方法一：使用企业管理器方法二：使用SQL语句（ALTER PROCEDURE）语法为：ALTER PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句>| <语句块> }（4）删除存储过程方法一：使用企业管理器方法二：使用DROP PROCEDURE语句语法为：DROP PROCEDURE <存储过程名>四、实验内容1、在学生表student上建立一个DELETE类型的触发器tr_delete，触发动作是显示信息“已删除学生表中的数据”。

数据库oracle实验5实验五存储过程与触发器一、实验目的（1）掌握Oracle数据库编程语言PL/SQL的基础知识。

（2）掌握游标、存储过程和触发器的创建，使用方法。

（3）了解使用高级语言连接数据库的技术、基本方法，了解ODBC、ADO和JDBC的技术。

二、实验内容在实验一、实验二创建的表中用PL/SQL语言完成以下内容：1．创建存储过程，根据调用时提供的学生姓名查询该学生所修课程的课程信息，将课程号和课程名输出到输出窗口，并给出函数调用语句块。

2．创建存储过程，统计指定学生修课的平均成绩和选课门数，将统计结果用输出参数传递给主程序，在PL/SQL主程序中调用存储过程，输出过程的返回结果。

3．创建存储过程，在学生表Student中插入一条完整的元组。

4．创建存储过程，将指定零件的重量增加指定的值。

5．创建存储过程，根据指定范围查询供应量在指定范围内的零件名称和供应商名，将结果输出到输出窗口，并给出函数调用语句块。

6．（1）删除SPJ关系中所有数据。

（2）在插入和修改SPJ表中QTY属性列的值时用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少于300，如果少于则自动改为300。

（3）在SPJ表中录入值进行验证。

7．（1）删除SC关系中的所有数据。

（2）在SC关系中增加新属性列Status，用来记录课程成绩的等级。

（3）用触发器实现自动记录成绩等级，当插入和修改grade列的值时，如果grade 在0－59分，status自动填写为“不合格”；grade在60－69分，status自动填写为“合格”；grade在70－89分，status自动填写为“良好”；grade在90以上status自动填写为“优秀”。

（4）在SC表中录入值进行验证。

8．创建触发器，实现在转专业记录表change_major中自动登记转专业信息，即当修改学生表中属性列major的值时，系统自动在change_major中插入一条转专业的信息（注：change_major中的id属性列可设置为自动增长，具体参照课堂讲解的例题）。

1、触发器的概述如果希望该子程序能够自动执行，就需要将该程序定义为触发器，触发器可以看做是一种特殊的存储过程，它可以在数据库相关事件（如DELETE、UPDATE，INSERT，CREATe等）发生时自动执行，常用于管理复杂的完整性约束或监控对表的修改操作。

触发器执行的机制与Java中的事件监听机制类似，当出现特定的事件时就会自动调用，Oracle数据库中的事件包括增加数据、修改数据、删除数据等。

与Java中的事件处理机制类似，在创建触发器时也需要指定触发器执行的事件2、触发器的语法（1）TRIGGER：用于创建触发器的关键字，就类似于创建存储过程的procedure和创建自定义函数的function一样。

（2）trigger_name：指定触发器的名称。

（3）BEFORE | AFTER | INSTEAD OF：指定触发器的时间，BEFORE表示在触发器在事件发生之前被执行，AFTER表示触发器在事件发生之后执行，INSTEAD OF指定该触发器为代替触发器。

（4）trigger_event：指定触发器的触发事件，常用的事件有INSERT、UPDATE、DELETE、CREATE、DROP等，多个事件之间需要使用or关键字进行连接。

（5）ON obj_name指定发生事件的数据库对象名称，如表名称、视图名称等。

如果执行的是DDL操作就必须写为ON DATABASE。

（6）FOR EACH ROW：表示该触发器为行级触发器，如果不指定该语句就默认为语句级触发器。

（7）WHEN tri_condition：指定触发器执行的条件，例如使用update语句修改的数据满足某个条件时才执行触发器的内容。

3、触发器的分类Oracle数据库的触发器事件相对于其他数据库而言相对复杂，根据触发器触发事件和触发器执行情况可以将Oracle中的触发器分为5种类型，具体如下所示。

（1）行级触发器：对表执行DML操作时，每影响一行数据，该类型的触发器就会执行一次。

实验名称：存储过程与触发器实验目的：掌握SQLSERVER存储过程与触发器的定义、调用操作数据库结构关系：语法规定：（1）存储过程定义语法：CREATE PROC 过程名@parameter 参数类型……@parameter 参数类型outputASBegin命令块End利用TSQL调用存储过程：Execute 过程名[参数值,……][Output]如果没有参数，直接调用过程名（2）创建事后触发器的语法：CREATE TRIGGER 触发器名ON 表名For Insert [,Update,Delete] AsBegin命令块End（3）创建替代触发器的语法：CREATE TRIGGER 触发器名ON 表名Instead of Insert [,Update,Delete] AsBegin命令块End实验内容：（一）存储过程（1）创建带输入参数的存储过程，输入查询的工资范围，输出查询到的职工信息Create proc Myproc1@mingzint,@maxgzintasselect * from 职工表where 工资between @mingz and @maxgz 调用该过程execute Myproc1 1000,4000（2）使用Transact-SQL语言创建带输入输出参数的存储过程。

输入仓库号，输出该仓库的职工信息、职工最高工资、最低工资Create proc Myproc2@cangkuhaovarchar(50),@maxgzint output,@avggzint outputasbeginselect * from 职工表where 仓库号=@cangkuhaoSelect @maxgz=max(工资) from 职工表where 仓库号=@cangkuhaoSelect @avggz=avg(工资) from 职工表where 仓库号=@cangkuhaoEnd调用存储过程。

转油放水站污水沉降罐管路偏流影响分析与治理刘影会（黑龙江省大庆油田创业集团龙丰实业公司）摘要：通过研究杏北油田3座转油放水站污水沉降罐管路偏流现象，分析管路偏流形成原因，污水沉降罐工艺流程在进出口管路非对称情况下，容易出现管路偏流现象，且根据管路非对称形式不同，管路偏流形式及程度不同；管路偏流对管道使用年限、污水沉降效果、生产管理等方面产生了不同程度的影响。

在此基础上制定包括设计及施工管控、局部优化改造、跟踪管理等管路偏流的预防及治理措施，可为已建及未来建设的转油放水站污水沉降罐管路设计、施工及管理提供借鉴。

关键词：转油放水站；污水沉降罐；管路偏流；压力；流速大庆杏北油田建有的三元-A、三元-B、三元-C 3座转油放水站采用污水沉降罐工艺流程，站内工艺流程主要采用三相分离器+污水沉降罐工艺，即计量间来液进入三相分离器，分离污水进入污水沉降罐，沉降后污水一部分经加热炉加热由掺水热洗泵返输计量间，一部分经泵输流程放水至下一级污水处理站[1]。

在生产运行过程中，三座转油放水站污水沉降罐在不同程度上出现供液不平衡的问题，即存在管路偏流现象，对相关工作产生了不同程度的影响。

因此，研究管路偏流形成原因及其影响，对于延长管道使用年限、提高污水沉降效果、方便生产管理等工作具有重要意义[2]。

1 管路运行基本情况杏北油田3座转油放水站采用的是两座污水沉降罐并联工艺（图1、图2），与萨北Ⅱ转油放水站污水沉降罐工艺进行对比，分析转油放水站污水沉降罐的管路运行现状。

图1三元-A污水沉降罐工艺流程图2三元-B、三元-C污水沉降罐工艺流程图3 萨北Ⅱ污水沉降罐管路工艺流程通过工艺流程图对比，4座转油放水站的主要区别在于汇管与支管连接方式不同。

三元-A、三元-B、三元-C三座转油放水站的管路看似是对称布置，而实际上在汇管与支管连接位置存在局部的非对称布置。

萨北Ⅱ的管路布置在整体结构上以及汇管与支管连接位置的局部都是对称布置。

实验四：PL/SQL程序设计一、实验目的◆掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。

◆熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。

二、实验内容某餐饮系统数据库，请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件Source.sql中：（1）菜肴类别表MK（菜肴类别编号MKid，菜肴类别名称MkName）,菜肴类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。

（2）菜单信息表MList（菜肴编号Mid，菜肴名称Mname，菜肴类别MKid，菜肴单价Mprice，菜肴成本单价Mcost，更新日期Mdate)。

（3）餐台类别表DK（餐台类别编号DKid，餐台类别名称DkName），餐台类别：包间和散台等。

（4）餐台信息表Dinfo (餐台编号Did，餐台名称Dname，餐台类别DKid，座位数Dseats，更新日期Ddate)。

（5）消费单主表C (消费单号Cid，餐台编号Did，消费开始时间StartTime，结账时间EndTime，消费金额合计Smoney，盈利金额合计SPsum)，其中，消费金额合计=消费单明细表CList中该消费单号的所有消费记录的消费金额的合计，即SUM（消费金额）或SUM（菜肴单价×消费数量），盈利金额合计=消费单明细表CList中该消费单号的所有消费记录的盈利合计，即SUM（(菜肴单价 - 菜肴成本单价)×消费数量）。

（6）消费单明细表CList (消费单号Cid，序号Sid，菜肴编号Mid，菜肴名称Mname，消费数量Cqty，菜肴单价Mprice，菜肴成本单价Mcost，消费金额Cmoney) ，消费金额=消费数量×菜肴单价；消费数量为正数是正常点菜，消费数量为负数是退菜，三、实验步骤及相关程序截图3.1 创建表空间RESTAURANT,创建用户DINER3.1.1 创建表空间RESTAURANT，大小10M。