第6章过程函数触发器和包
6.1.1创建存储过程
例1 创建一个存储过程，该存储过程实现向dept表中插入一条记录。
create procedure pro_insertDept is
begin
insert into dept values(77,'市场拓展部','JILIN');
commit;
dbms_output.put_line('插入新记录成功！');
endpro_insertDept;
/
例2 在当前模式下，如果数据库中存在同名的存储过程，则要求按新创建的存储过程覆盖掉已存在的存储过程；如果不存在同名的存储过程，则直接创建即可。
create or replace procedure pro_insertDept is
begin
insert into dept values(99,'市场拓展部','BEIJING'); --插入数据记录
commit; --提交数据
dbms_output.put_line('插入新记录成功！'); --提示插入记录成功
endpro_insertDept;
/
例3. 在SQL *Plus环境中，使用execute命令执pro_insertDept存储过程，具体代码如下。SQL>execute pro_insertDept;
例4.在PL/SQL块中调用存储过程pro_insertDept,然后执行这个PL/SQL块。
SQL>set serverout on
SQL>begin
pro_insertDept;
end;
/
3.1.2存储过程的参数
例5创建一个存储过程，并定义3个in模式的变量，然后将这3个变量的值插入到dept表中。create or replace procedure insert_dept(
num_deptno in number,
var_ename in varchar2,
var_loc in varchar2) is
begin
insert into dept
values(num_deptno,var_ename,var_loc);
commit;
endinsert_dept;
/
例6在PL/SQL块中调用存储过程insert_dept,然后使用“指定名称”的方式向其传入参数值，最后执行当前的PL/SQL块。
SQL>begin
insert_dept(var_ename=>’采购部’,var_loc=>’成都’,num_deptno=>15);
end;
/
例7在PL/SQL块中调用存储过程insert_dept,然后使用“按位置传递”的方式向其传入参数值，最后执行当前的PL/SQL块。
SQL>begin
insert_dept(28,’工程部’,’洛阳’);
end;
/
例8在PL/SQL块中调用存储过程insert_dept,然后使用“按位置传递”的方式向其传入第一个参数值，使用“指定名称传递”的方式传入剩余的两个值，最后执行当前的PL/SQL块。SQL>exec nsert_dept(38, var_loc=>’济南’,var_ename=>’测试部’);
例9.创建一个存储过程，要求定义两个out模式的字符类型参数，然后在dept表中检索别的一行部门信息存储到这两个参数中。
create or replace procedure select_dept(
num_deptno in number,--定义in模式变量，要求输入部门编号
var_dname out dept.dname%type,--定义out模式变量，可以存储部门名称并输出
var_loc out dept.loc%type) is
begin
selectdname,loc
intovar_dname,var_loc
fromdept
where deptno = num_deptno;--检索某个部门编号的部门信息
exception
when no_data_found then --若select语句无返回记录
dbms_output.put_line('该部门编号的不存在');--输出信息
endselect_dept;
/
例10.首先在PL/SQL块中声明若干变量，然后调用select_dept存储过程，并将定义的变量传入该存储过程，以便接收out参数的返回值。
setserverout on
declare
var_dnamedept.dname%type;
var_locdept.loc%type;
begin
select_dept(99,var_dname,var_loc);
dbms_output.put_line(var_dname||'位于：'||var_loc);
end;
/
例11.使用variable关键字声明两个变量，分别用来存储部门名称和位置信息，然后使用exec 命令执行存储过程，并传入声明的两个变量来接收out参数的返回值。
SQL>variable var_dnamevarchar2(50);
SQL>variable var_locvarchar2(50);
SQL>exec select_dept(15,:var_dname,:var_loc);
例12 使用print命令打印输出变量var_dname和var_loc的值。
SQL>print var_dnamevar_loc;
例13.使用select 语句检索并输出变量var_dname和var_loc的值。
SQL>select :var_dname,:var_loc from dual;
例14.创建一个存储过程，在其中定义一个“in out”参数，该存储过程用来计算这个参数的平方或者平方根。
create or replace procedure pro_square(
num in out number,--计算它的平方或平方根
flag in boolean) is --计算平方或平方根的标识
i int := 2; --计算平方的参数
begin
if flag then --若为true
num := power(num,i);--计算平方
else
num:=sqrt(num);--计算平方根
end if;
end;
/
注意:boolean只能为true或者false.
例15.调用存储过程pro_square,计算某个数的平方或平方根。
declare
var_number number;--存储要进行运算的值和运算后的结果
var_temp number;--存储要进行运算的值
boo_flagboolean;--平方或平方根的逻辑标记
begin
var_temp :=3;--变量赋值
var_number :=var_temp;
boo_flag := false;--false表示计算平方根；true表示计算平方
pro_square(var_number,boo_flag);--调用存储过程
ifboo_flag then
dbms_output.put_line(var_temp ||'的平方是：'||var_number);--输出计算结果
else
dbms_output.put_line(var_temp ||'平方根是：'||var_number);
end if;
end;
/
6.1.3 IN参数的默认值
例16. 创建一个存储过程，定义3个IN参数，并将其中的两个参数设置初始默认值，然后将这个3个IN参数的值插入到dept表中。
create or replace procedure insert_dept(
num_deptno in number,--定义存储部门编号的IN参数
var_dname in varchar2 default '综合部',--定义存储部门名称的IN参数，并初始默认值
var_loc in varchar2 default '北京') is
begin
insert into dept values(num_deptno,var_dname,var_loc);--插入一条记录
end;
/
6.2.1创建函数
例17.定义一个函数，用于计算emp表中指定某个部门的平均工资。
create or replace function get_avg_pay(num_deptno number) return number is
num_avg_pay number;--定义临时变量，保存某个部门的平均工资
begin
select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某个部门的平均工资
return(round(num_avg_pay,2));--返回平均工资
exception
when no_data_found then --若此部门编号不存在
dbms_output.put_line('该部门编号不存在');
return(0); --返回平均工资为0
end;
/
6.2.2调用函数
例18.调用函数get_avg_pay,计算部门编号为10的雇员平均工资并输出。
SQL>set serveroutput on
SQL>declare
avg_pay number;
begin
avg_pay:=get_avg_pay(10);
dbms_output.put_line(‘平均工资是：’||avg_pay);
end;
/
6.2.3删除函数
SQL>drop function get_avg_pay;
案例1：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%
create or replace procedure mypro(spName varchar2) is
v_salemp.sal%type
begin
select sal into v_sal from emp where ename = spName
if v_sal<2000 then
updateemp set sal = sal*1.1where ename = spName
end if;
end;
案例2 编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上加100，如果为0则改为200
create or replace procedure mypro(spName varchar2) is
v_https://www.doczj.com/doc/e2219331.html,m%type
begin
select comm into v_comm from emp where ename = spName;
if v_comm<>0 then
updateemp set comm = comm+100where ename = spName;
else
updateemp set comm = comm+200where ename = spName;
end if;
end;
案例3：编写一个过程，可以输入一个雇员编号人，如果该雇员的职位是president就给他的工资增加1000，如果该雇员的职位是manager就给他的工资增加500，其他职位的雇员工资增加200.
create or replace procedure mypro(spName number) is
v_jobemp.job%type
begin
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' then
updateemp set sal = sal +1000where empno = spNo;
elsifv_job = 'MANAGER' then
updateemp set sal = sal + 500where empno = spNo;
else
updateemp set sal = sal + 200where empno = spNo;
end if;
end;
6.3.2语句级触发器
例19.在SCOTT模式下创建dept_log数据表，并在其中定义两个字段，分别用来存储操作种类信息和操作日期。
create table dept_log
(
operate_tag varchar2(10), --定义字段，存储操作种类信息
operate_time date --定义字段，存储操作日期
);
例20.创建一个触发器tri_dept,该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是dept表，要求在触发器执行时输出对dept表所做的具体操作。
create or replace trigger tri_dept
before insert or update or delete
on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
declare
var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
begin
if inserting then --当触发事件是INSERT时
var_tag := '插入';--标识插入操作
elsif updating then --当触发事件是UPDATE时
var_tag := '修改';--标识修改操作
elsif deleting then--当触发事件是DELETE时
var_tag := '删除';--标识删除操作
end if;
insert into dept_log
values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
endtri_dept;
/
例21.在数据表dept中实现插入、修改、删除3种操作，以便引起触发器tri_dept的执行。SQL>insert into dept values(66,’业务咨询部’,’长春’);
SQL>update dept set loc=’沈阳’ where deptno=66;
SQL>delete from dept where deptno=66;
SQL>select * from dept where deptno=66;
6.3.3行级触发器
例22. 在SCOTT模式下，创建一个用于存储商品种类的数据表，其中包括商品序号列和商品名称列。
create table goods
(
idint primary key,
good_namevarchar2(50)
);
SQL>create sequence seq_id;
例23.创建一个行级触发器，该触发器在数据表goods插入数据时被触发，并且在该触发器的主体中实现设置goods表的id列的值。
create or replace trigger tri_insert_good
before insert
on goods --关于goods数据表的id，在插入id列之前，引起该触发器的运行
for each row --创建行级触发器
begin
selectseq_id.nextval
into :new.id
from dual;--从序列中生成一个新的数值，赋值给当前插入行的id列
end;
/
例24 向goods表中插入两条记录
SQL>insert into goods(good_name) values(‘苹果’);
SQL>insert into goods(id,good_name) values(9,‘苹果’);
SQL>select * from goods;
6.4程序包
例25.
create or replace package pack_emp is
function fun_avg_sal(num_deptno number) return number;--该函数用于获取指定部门的平均工资
procedure pro_regulate_sal(var_job varchar2,num_proportion number);--该存储过程实现按照指定比例上调指定职务的工资
endpack_emp;
/
例26.创建程序包pack_emp的主体
create or replace package body pack_emp is
function fun_avg_sal(num_deptno number) return number is --引入“规范”中的函数
num_avg_sal number;--定义内部变量
begin
selectavg(sal)
intonum_avg_sal
fromemp
where deptno = num_deptno;--计算某个部门的平均工资
return(num_avg_sal);--返回平均工资
exception
when no_data_found then--若未发现记录
dbms_output.put_line('该部门编号不存在雇员记录');
return 0;--返回0
endfun_avg_sal;
procedure pro_regulate_sal(var_job varchar2,num_proportion number) is--引入“规范”中的存储过程
begin
updateemp
setsal = sal*(1+num_proportion)
where job = var_job;--为指定的职务调整工资
endpro_regulate_sal;
endpack_emp;
/
例27.创建一个匿名PL/SQL块，然后通过程序包pack_emp调用其中的函数fun_avg_sal和存储过程pro_regulate_sal,并输出函数的返回结果。
setserveroutput on
declare
num_deptnoemp.deptno%type;--定义部门编号变量
var_jobemp.job%type;--定义职务变量
num_avg_salemp.sal%type;--定义工资变量
num_proportion number;--定义工资调整比例变量
begin
num_deptno:=10;--设置部门编号为10
num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);--计算部门编号为10的平均工资
dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);--输出平均工资
var_job:='SALESMAN';--设置职务名称
num_proportion:=0.1;--设置调整比例
pack_emp.pro_regulate_sal(var_job,num_proportion);--调整指定部门的工资
end;
/
第一章存储过程与触发器实验 实验目的 1．理解存储过程的工作原理和作用。 2．掌握存储过程设置和程序设计过程。 3．理解触发器的工作原理和作用。 4．掌握触发器编写方法。 实验环境 采用IBM DB2或Sybase数据库管理系统作为实验平台。其中，DB2可以采用DB2 Express-C或DB2 V8 Enterprise。 实验完成人：李肇臻，谢锦 实验内容 一、存储过程实验 1．针对下面2个完整性约束条件，建立存储过程，实现当数据导入或更新时，可以自动修改拥塞率、半速率话务量比例 (1)拥塞率＝拥塞数量／呼叫数量 (2)半速率话务量比例＝半速率话务量／全速率话务量 在DB2CMD中运行，win7用管理员权限。 create procedure M() language SQL begin update CALLDATA set "callcongs"="congsnum"/"callnum" where "congsnum"<>0 and "callnum"<>0; update CALLDATA set "rate"="thtraff"/"traff" where "thtraff"<>0 and "traff"<>0; end @ 2．将存储过程添加到数据库服务器上
3．在客户端编写调用存储过程的主程序 4．运行客户端程序，调用存储过程，观察存储过程执行过程和数据更新情况；调用就用db2 CALL M() 二、触发器实验 1. 针对下列约束条件，分别建立1个触发器： 1）每个小区/扇区最多占用14个TCH频点，合法频点范围在[1,60]之间。当向小区中新加入频点时，如果小区中现有频点数目已达到14个，则用新加入的频点替换现有频点中的最小频点；当修改或新加入频点时，如果发现频点不在合法范围内，则输出提示信息，并拒绝该操作。 create trigger first_1 after insert on FREQUENCY
数据库基础与实践实验报告实验五存储过程和触发器 班级：惠普测试142 学号：1408090213 姓名：闫伟明 日期：2016-11-14
1 实验目的： 1)掌握SQL进行存储过程创建和调用的方法； 2)掌握SQL进行触发器定义的方法，理解触发器的工作原理； 3)掌握触发器禁用和重新启用的方法。 2 实验平台： 操作系统：Windows xp。 实验环境：SQL Server 2000以上版本。 3 实验内容与步骤 利用实验一创建的sch_id数据库完成下列实验内容。 1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。 存储过程定义代码： CREATE PROCEDURE JSXX_PROC AS SELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND https://www.doczj.com/doc/e2219331.html,o=https://www.doczj.com/doc/e2219331.html,o 存储过程执行语句与执行结果截图： EXECUTE JSXX_PROC 2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、 所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。 存储过程定义代码：
CREATE PROCEDURE XM_PROC @sname VARCHAR(100) AS BEGIN IF EXISTS(SELECT NULL FROM S WHERE sn=@sname) SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE https://www.doczj.com/doc/e2219331.html,o=https://www.doczj.com/doc/e2219331.html,o AND SC.sno=S.sno AND S.sn=@sname ELSE PRINT'无该姓名的同学。' END 运行截图： 3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业）， 统计并显示该专业各年龄段男、女生人数。如果没有该专业，则显示“无此专业”。 存储过程定义代码： CREATE PROCEDURE XBNL_PROC @departName VARCHAR(30)='计算机', @begin INT, @end INT AS
实验6过程、函数和程序包 姓名：学号： 专业：班级： 同组人：无实验日期：2013/7/21 【实验目的与要求】 ?掌握过程的创建与调用 ?掌握PL/SQL函数的编写与调用 ?熟悉程序包的使用 【实验内容与步骤】 6.0．实验准备工作:PL/SQL程序文件的编辑与执行 1．使用文档编辑器编辑以下文件，并保存为aa.sql： 2．以scott身份登录，在SQ L Plus中执行＠aa命令运行程序： 注：测试时，文件名请用全名(即包含路径，如：@c:\aa) 给出运行结果：
6.1．存储过程 1．最简单的存储过程编写与执行 (1)创建测试表 drop table Exam_Table; create table Exam_Table( e_id number(5), e_name varchar2(20), e_salary number(8,2) ); (2)创建存储过程 create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary number) is begin insert into Exam_Table values (v_id,v_name,v_salary); commit; dbms_output.put_line('数据插入成功'); end; / (3) 执行(调用)存储过程 exec insert_salary(6,'g',2000); (4)查询执行结果
select * from Exam_Table; 给出执行的最后结果： 2．参数的使用：in/out/in out参数 阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。 (1) 用两个参数：in ,out 传入一个姓名，输出：某某人你好： create or replace procedure mp(v_in varchar2,v_out out varchar2) is begin v_out:=v_in||'你好'; end; declare v_name varchar2(10); begin mp('scott',v_name); dbms_output.put_line(v_name); end;--输出：scott你好 给出运行结果：
实验六存储过程与触发器 一、目的与要求 1.掌握编写数据库存储过程的方法。 2.掌握建立数据库触发器的方法,通过实验观察触发器的作用与触发条件设置 等相关操作。 二、实验准备 1.了解编写存储过程与调用的T-SQL语法; 2.了解触发器的作用; 3.了解编写触发器的T-SQL语法。 三、实验内容 (一)存储过程 在studentdb数据库中建立存储过程getPractice,查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号与课程名称,若院系不存在,返回提示信息。 提示:D_Info表中存储了院系代码D_ID,而St_Info表中学号字段St_ID的前两位与之对应,则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。 1.分别执行存储过程getPractice,查询“法学院”与“材料科学与工程学院” 的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号与课程名称。 create procedure getPractice @D_Name varchar(30) output as begin if not exists (select * from D_Info where D_Name= @D_Name ) print '对不起,该院系不存在' else select st_info、St_ID,C_Info、C_No,C_Name from s_c_info inner join st_info on st_info、St_ID=s_c_info、st_id inner join C_Info on s_c_info、c_no=C_Info、C_No where st_info、St_ID in ( select St_ID from st_info join D_Info on D_Info、D_ID =left(st_info、St_ID,2) where C_Info、C_Type='实践' and D_Info、D_Name= @D_Name ) end go
信息工程学院实验报告 课程名称：《数据库原理》 实验项目名称：存储过程与触发器 一、实验目的: （1）了解存储过程的概念 （2）掌握创建、执行存储过程的方法 （3）了解查看、修改和删除存储过程的方法 （4）了解触发器的概念 （5）掌握创建触发器的方法 （6）掌握查看、修改、删除触发器信息的方法 二、实验设备与器件 Win7 +Sql server 2008 三、实验内容与步骤 （一）存储过程 运行实验四附录中的SQL语句，准备实验数据。然后创建下列存储过程，并调试运行存储过程，查看运行结果。 1．在企业管理器中创建一个名为StuInfo的存储过程，完成的功能是在student表中查询系号为D2的学号、姓名、性别、年龄、系号的内容。 CREATE PROCEDURE StuInfo AS SELECT SNO AS学号, SNAME AS姓名, SSEX AS性别, SAGE AS年龄, DNO AS系号 FROM student WHERE DNO='D2' 结果： stuinfo 2．使用T_SQL语句创建存储过程，完成的功能是在表student,course和study中查询以下字段：学号、姓名、性别、课程名称、考试分数。
use mydb --查询是否已存在此存储过程，如果存在，就删除它 if exists(select name from sysobjects where name='StuScoreInfo'and type='P') drop procedure StuScoreInfo go --创建存储过程 CREATE PROCEDURE StuScoreInfo as select student.sno as学号, sname as姓名, ssex as性别, https://www.doczj.com/doc/e2219331.html,ame as课程名称, study.grade as考试分数 from student,course,study where student.sno=study.sno and https://www.doczj.com/doc/e2219331.html,o=https://www.doczj.com/doc/e2219331.html,o 结果： StuScoreInfo 3．使用T_SQL语句创建一个带有参数的存储过程stu_sno_info，该存储过程根据传入的学生编号，在student表中查询此学生的信息。 if exists(select name from sysobjects where name='stu_info'and type='P') drop procedure stu_info go --创建存储过程 create procedure stu_info @sno varchar(8) as select sno as学号, sname as姓名, ssex as性别, sage as年龄, dno as年级
第10章存储过程、函数和包 存储过程(PROCEDURE)、函数(FUNCTION)和包(PAKAGE)是以编译的形式存储在数据库中的数据库的对象，并成为数据库的一部分，可作为数据库的对象通过名字被调用和访问。 存储过程通常是实现一定功能的模块；函数通常用于计算，并返回计算结果；包分为包头和包体；用于捆绑存放相关的存储过程和函数，起到对模块归类打包的作用。 存储过程、函数和包是数据库应用程序开发的重要方法，三者既有区别，也有联系。 ?存储过程和存储函数。 ?过程的参数和调用。 ?包和包的应用。 10.1 存储过程和函数 存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。 10.1.1 认识存储过程和函数 和PL/SQL程序相比，存储过程有很多优点，具体归纳如下： ·存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。 ·存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。 ·存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL 程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。 ·像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。 存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。
实验五：触发器和存储过程 一．实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。 二．实验内容： 有一个小型的图书管理数据库，包含的表为： bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表 borrowcard（cardid，ownername）；--借书证表 borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表 写一个存储过程，实现借书操作，要求有事务处理。（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。（3）要求用触发器实现表的完整性控制。 三、操作与运行 1.创建图书数据库： create table bookstore (bookid int not null primary key, bookname char(20), bookauthor char(20),
purchasedate datetime, state char(4) ) create table borrowcard (cardid int not null primary key, ownername char(20) ) create table borrowlog (cardid int not null, bookid int not null, borrowdate datetime, returndate datetime, primary key(cardid,bookid), ---foreign key(cardid)references borrowcard(cardid), ---foreign key(bookid)references bookstore(bookid) ) 通过以上语句，可以看到数据库中的表建立成功。 2.创建存储过程： create proc book_borrow @mycardid_in int, @mybookid_in int, @str_out char(30) output
存储过程与触发器实验日期和时间： 2016 年 5 月13 日、星 期 五第节 实验室：DJ2-信息管理实验室 班级：学号：姓名： 实验环境： 1.硬件：笔记本电脑 2.软件：SQL Server 2012 实验原理： 存储过程概念：存储过程是事先编好的，存储在数据库中的一组被编译了的T-SQL命令集合，这些命令用来完成对数据库的指定操作。存储过程可以接受用户的输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言（DDL）和数据操作语言（DML）语句，然后返回输入参数。 触发器概念：触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 实验任务： 此作业成绩得分根据你完成的任务的难度和数量评分，完成后在实验室给老师演示验收，课后提交电子版报告。如额外完成自拟题目应当事先将所拟题目提交给老师或在报告中明确标注题意。 假定有学校的图书馆管理信息系统，可以用于日常管理书库和同学们的借还书工作。 以下列出参考的库表情况： 根据管理的业务需求来分析，该管理信息系统的数据库应至少包括如下数据表：（打★号的是必须有的表） 1.★图书现有库存表。作用：记录图书的现有库存情况。至少包括：书号、书名、 作者、简介、类别、价格、出版社、出版日期、现有库存数量、最小库存量、库 存总量、库存位置等。 2.★读者信息表。作用：记录读者信息。至少包括：读者编号、证件类型、证件号 码、姓名、性别、职业（可填写教师、学生、教工、其它……）、所属单位、地址、 联系电话等。 3.★借书记录表。作用：记录借书情况,以及是否归还。至少包括：借阅ID（主键， 可设置为自动编号）、书号、读者编号、借阅数量、借阅日期、是否归还、管理员 编号……等。 4.★还书记录表。作用：记录还书情况。至少包括：还书ID（主键，可设置为自动 编号）、书号、读者编号、归还数量、归还日期、是否超期（超过假设45天为超 期）、超期天数、管理员编号……等。（附：为简化操作，续借可视为归还后再借）。 5.管理员信息表。作用：记录负责管理书库和借书还书工作的管理员信息。至少包 括：管理员编号、职工编号（在职工档案表中的职工编号）、用户名、密码、管理
南昌航空大学实验报告 二00 年月日 课程名称：数据库概论实验名称：数据库存储过程与触发器 班级：122031 姓名：同组人： 指导教师评定：签名： 一、实验环境 1．Windows2000或以上版本； 2．SQLServer 2005。 二、实验目的 熟悉不同数据库的存储过程和触发器，重点实践SQL Server2005，掌握SQL Server2005中有存储过程与触发器的相关知识。 三、实验要求 完成实验指导书中p115-7和p132 -4。 四、实验步骤及参考源代码 1.创建与执行存储过程 create procedure C_P_Proc as select distinct https://www.doczj.com/doc/e2219331.html,o,cna,pna,num from paper,customer,cp where https://www.doczj.com/doc/e2219331.html,o=https://www.doczj.com/doc/e2219331.html,o and paper.pno=cp.pno and cna='李涛' or cna='钱金浩' go execute C_P_Proc 2.删除存储过程 drop procedure C_P_Proc 3.创建插入触发器 create trigger TR_PAPER_I ON PAKER12203125 FOR INSERT AS DECLARE @appr float DECLARE @apno int SELECT @appr=ppr,@apno=pno from inserted begin if @appr<0 or @appr is null begin
raiserror('报纸的单价为空或小于!',16,1) update paper set ppr=10 where paper.pno=@apno end end 4.创建删除触发器 create Trigger TR_PAPER_D on PAKER12203125 after delete as declare @ipno char(6) declare @icount int; select @icount= count(*) from deleted,cp where deleted.pno=cp.pno if @icount>=1 begin select @ipno=pno from deleted raiserror('级联删除cp表中的数据',16,1) delete from cp where cp.pno=@ipno end 5.创建修改触发器 create trigger TR_PAPER_U ON PAKER12203125 for update as declare @ippr float select @ippr=ppr from inserted if @ippr<0 or @ippr is null begin raiserror('输入单价不正确',16,1) rollback transaction end 6. 分别对PAKER12203125表进行插入、修改、删除操作 insert into PAKER12203125 (pno,pna,ppr)values('000006','江西日报','1') insert into PAKER12203125 (pno,pna,ppr)values('000007','江南都市报','15.5') delete from PAKER12203125 where pno='000001' update PAKER12203125 set ppr=12.5 where pno='000002' update PAKER12203125 set ppr=-2 where pno='000004' 五、实验结果
过程、函数和和序包 姓名：学号：专业：班级：同组人：实验日期： 【实验目的与要求】 ?掌握过程的创建与调用 ?掌握PL/SQL函数的编写与调用 ?熟悉程序包的使用 【实验内容与步骤】 9.0．实验准备工作:PL/SQL程序文件的编辑与执行 1．使用文档编辑器编辑以下文件，并保存为aa.sql： 2．以scott身份登录，在SQ L Plus中执行＠aa命令运行程序： 给出运行结果：
9.1．存储过程 1．最简单的存储过程编写与执行 (1)创建测试表 drop table Exam_Table; create table Exam_Table( e_id number(5), e_name varchar2(20), e_salary number(8,2) ); (2)创建存储过程 create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary number) is begin insert into Exam_Table values (v_id,v_name,v_salary); commit; dbms_output.put_line('数据插入成功'); end; / (3) 执行(调用)存储过程 exec insert_salary(6,'g',2000); (4)查询执行结果 select * from Exam_Table; 给出执行的最后结果：
2．参数的使用：in/out/in out参数 阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。 (1) 用两个参数：in ,out 传入一个姓名，输出：某某人你好： create or replace procedure mp1(v_in in varchar2,v_out out varchar2) is begin v_out:=v_in||'你好'; end; declare v_name varchar2(10); begin mp1('scott',v_name); dbms_output.put_line(v_name); end;--输出：scott你好 给出运行结果： (2)-- in out类型参数 create or replace procedure mp(name_in in varchar2, name_out out varchar2, name_in_out in out varchar2) is begin dbms_output.put_line(name_in); name_out:= '返回的参数name_out是' || name_in; name_in_out := 'name_in_out是' || name_in || name_in_out; end; variable name_out varchar2(100); variable name_in_out varchar2(20); exec mp('11',:name_out,:name_in_out);
有教师表(教师号，教师名，职称，基本工资)，其中基本工资的取值与教师职称有关。实现这个约束的可行方案是( )。 A 在教师表上定义一个视图 B 在教师表上定义一个存储过程 C 在教师表上定义插入和修改操作的触发器 D 在教师表上定义一个标量函数 参考答案 C 在SQL SERVER中，执行带参数的过程，正确的方法为（）。 A 过程名参数 B 过程名（参数） C 过程名=参数 D ABC均可 参考答案 A 在SQL SERVER服务器上，存储过程是一组预先定义并（）的Transact-SQL语句。 A 保存 B 解释 C 编译 D 编写 参考答案 C 在SQL Server中，触发器不具有（）类型。 A INSERT触发器 B UPDATE触发器 C DELETE触发器 D SELECT触发器 参考答案 D
（）允许用户定义一组操作，这些操作通过对指定的表进行删除、插入和更新命令来执行或触发。 A 存储过程 B 规则 C 触发器 D 索引 参考答案 C 为了使用输出参数，需要在CREATE PROCEDURE语句中指定关键字( )。 A OPTION B OUTPUT C CHECK D DEFAULT 参考答案 B 下列( )语句用于创建触发器。 A CREATE PROCEDURE B CREATE TRIGGER C ALTER TRIGGER D DROP TRIGGER 参考答案 B 下列( )语句用于删除触发器。 A CREATE PROCEDURE B CREATE TRIGGER C ALTER TRIGGER D DROP TRIGGER 参考答案 D
大型数据库技术实验报告实验课程：大型数据库技术（Oracle） 专业 ：班级： 姓名：学号：同组人：实验日期： 实验项目实验八过程、函数和和程序包 实验 类型 设计性实验 目的要求?掌握过程的创建与调用 ?掌握PL/SQL函数的编写与调用?熟悉程序包的使用 （实验内容及步骤） 【实验步骤】 8.0．实验准备工作:PL/SQL程序文件的编辑与执行 1．使用文档编辑器编辑以下文件，并保存为aa.sql： 2．以scott身份登录，在SQ L Plus中执行＠aa命令运行程序：成绩
给出运行结果： 8.1．存储过程 1．最简单的存储过程 (1)创建测试表 drop table empl; create table empl( e_id number(5), e_name varchar2(20), e_salary number(8,2) ); (2)创建存储过程 create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary number) is begin insert into empl values (v_id,v_name,v_salary); commit; dbms_output.put_line('数据插入成功'); end; / (3) 执行(调用)存储过程 exec insert_salary(6,'g',2000);
(4)查询执行结果 select * from empl; 给出执行的最后结果： 2．存储过程应用实例：列车购票与退票过程简单模拟 drop table ticket; create table ticket ( trainno varchar2(10), ticketnum varchar2(10), primary key (trainno,ticketnum) ); -- 存储过程声明 create or replace procedure init is begin for i in 1..100 loop insert into ticket values ('1111', to_char(i, '0000')); end loop; for i in 1..100 loop insert into ticket values ('2222', to_char(i, '0000')); end loop; for i in 1..100 loop insert into ticket values ('3333', to_char(i, '0000')); end loop;
实验八存储过程和触发器 一、目的与要求 1. 正确理解存储过程和触发器的概念、功能和类型； 2. 掌握使用SSMS和T-SQL语句创建和管理存储过程和触发器。 二、上机准备 利用教师提供的XSGL数据库，该库中有3个表：student，course，sc。 三、实验内容 1. 将教师提供的XSGL数据库附加到本地数据库中。 2. 分别使用SSMS和T-SQL语句创建和管理存储过程和触发器。 （1）创建一个存储过程proc_stud_sc_info，查询学号、姓名、性别、系、课程号和成绩等信息。 use xsgl go create procedure proc_stud_sc_info as select student.sno,sname,sex,dept,cno,grade from student left join sc on student.sno=sc.sno go （2）创建一个存储过程proc_stud_info，根据输入的学号，查询学生的基本信息。 use xsgl go create procedure proc_stud_info @sno char(5)='95001' as select * from student where sno=@sno go （3）创建一个存储过程proc_stud_birth_year，根据输入的学生姓名，计算该学生的出生年份。 use xsgl go create procedure proc_stud_birth_year @sname varchar(6)='张立' as select sname,year(getdate())-age as 出生年份 from student where sname=@sname go
实验十：存储过程、函数和程序包 实验目的： 1.能够正确编写存储过程 2.能够正确定义和使用存储过程参数 3.能够正确调用存储过程 实验要求： 1.按以下步骤完成如下操作，并对出现的现象进行解释 2.将命令和操作过程记录到实验过程记录中 实验步骤和方法： 1.启动本地Oracle服务器 2.以scott用户普通身份连接到PL/SQL developer 3.按要求编写PL/SQL存储过程，并用两种方式调用各个存储过程（在查询中尽量思 考，找到不一样的解题方案） 1)编写给每个员工发放额外补贴500的存储过程。(提示:COMM字段值增加500) 2)创建新增部门的存储过程INSERT_DEPT，部门号、部门名称和部门所在位置 作为参数。（关于参数存储过程的用法：in参数的使用）
1)创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。 2)创建比较两个数大小的函数，返回较大值。
5.按要求编写包： 1)创建管理部门信息的包pacdept，具有从dept表获得指定部门信息，修改部门 名称，添加部门信息的功能（注：各模块应包含异常处理模块）。(可以参考下 一页的案例1)
思考：1、在实验中是否懂得了存储过程以及带参数的存储过程的用法？
【案例1】创建管理雇员信息的包EMPLOYE，具有从EMP表获得雇员信息，修改雇员名称，修改雇员工资和写回EMP表的功能。 步骤1：登录SCOTT账户，输入以下代码，并编译： CREATE OR REPLACE PACKAGE EMPLOYE --包头部分 IS PROCEDURE SHOW_DETAIL; PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER); PROCEDURE SAVE_EMPLOYE; PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2); PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER); END EMPLOYE; / CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分 IS EMPLOYE EMP%ROWTYPE; -------- 显示雇员信息 -------- PROCEDURE SHOW_DETAIL AS BEGIN DBMS_OUTPUT.PUT_LINE('----- 雇员信息 -----'); DBMS_OUTPUT.PUT_LINE('雇员编号：'||EMPLOYE.EMPNO); DBMS_OUTPUT.PUT_LINE('雇员名称：'||EMPLOYE.ENAME); DBMS_OUTPUT.PUT_LINE('雇员职务：'||EMPLOYE.JOB); DBMS_OUTPUT.PUT_LINE('雇员工资：'||EMPLOYE.SAL); DBMS_OUTPUT.PUT_LINE('部门编号：'||EMPLOYE.DEPTNO); END SHOW_DETAIL; -------- 从EMP表取得一个雇员 --------- PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER) AS BEGIN SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO; DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误！'); END GET_EMPLOYE; --------- 保存雇员到EMP表 --------- PROCEDURE SAVE_EMPLOYE AS BEGIN UPDATE EMP SET ENAME=EMPLOYE.ENAME,SAL=EMPLOYE.SAL WHERE EMPNO=EMPLOYE.EMPNO; DBMS_OUTPUT.PUT_LINE('雇员信息保存完成！');
实验五：触发器和存储过程 一、实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。 二、预习要求：存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。 三、实验内容： 有一个小型的图书管理数据库，包含的表为： bookstore（bookid，bookname，bookauthor，purchasedate，state）； --图书库存表 borrowcard（cardid，ownername）；--借书证表 borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表 写一个存储过程，实现借书操作，要求有事务处理。（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。（3）要求用触发器实现表的完整性控制。 四、完成情况（附上设计的SQL语句）。 ------触发器和存储过程 ------触发器和存储过程 CREATE DATABASE BOOK1 go use BOOK1 CREATE TABLE BOOKSTORE(Bookid nvarchar(10),Bookname nvarchar(10),Bookauthor nvarchar(10),purchasedate datetime,state Nvarchar(10),primary key(Bookid)) Create table Borrowcard(Cardid int,ownername nvarchar(10),primary key(Cardid)) Create table Borrowlog(Cardid int,Bookid nvarchar(10),borrowdate datetime,returndate datetime ,primary key(Cardid,Bookid))
第七章包和子程序 1.1目标 1.过程和函数 2.过程和函数在plsql中调用 3.包 1.2知识点 1.过程和函数的作用，语法结构，参数的类型 2.在plsql语句块中调用函数和过程 3.包和包体的作用，语法结构 1.3难点 1.过程和函数的作用，语法结构，参数的类型 2.包和包体的作用，语法结构 1.4讲解内容 前面Plsql是匿名的存储过程，可以批量执行sql操作，并且可以加入条件控制，但是每次执行的plsql脚本不能在服务器端保存，而过程和函数可以将plsql语句块按功能划分，并保存起来，实现代码的复用，简单的讲，过程和函数是对plsql语句块的封装，是有名字的plsql 语句块。 1.4.1过程 过程语法结构： Create or replace procedure存储过程名称as←创建或重定义存储过程名称, Begin 执行过程的语句块… End; ←执行语句块用begin和end包围，相当于程 序中的{}，表示语句块的开始和结束
Create or replace procedure存储过程名称（参数1，参数2）as←创建或重定义存储过程名称, Begin 执行过程的语句块… End; 要点： 1.参数默认是输入参数，注明out为输出参数in out为输入输出参数 2.没有参数的过程和函数，在过程名称后面不用带()括号 3.有参数的过程和函数，形式参数的数据类型不需要指定长度 4.局部变量放在as 和begin之间，begin和end之间是plsql语句块 示例： create or replace procedure pro1 as begin dbms_output.put_line('dd'); end; create or replace procedure pro2(v_val1 in out varchar2,v_val2 in out varchar2) as begin dbms_output.put_line('v_val1:'||v_val1); dbms_output.put_line('v_val2:'||v_val2); end; 1.4.2函数 函数的语法结构 和过程类似，区别在于多了一个返回值 Create or replace function函数名称return类型as←创建或重定义函数，必须带return 和返回一个数据的类型 Begin 函数执行的语句块 return返回值←返回值，和创建函数声明中返回类型要一致 End; 示例： create or replace function fun1 return varchar2as begin return'ddd'; end;
数据库技术与应用 实验报告七 班级：机械因材学号： 16 姓名：高永吉 一：实验名称：存储过程及触发器 二.实验目的： ⑴使用系统常用的存储过程； ⑵掌握存储过程的创建及应用 (3) 理解触发器的概念； (4) 掌握触发器的创建及应用。 三．实验内容、过程和结果： 存储过程 1创建一个存储过程.查看学号为1（根据实际情况取）的学生的信息.包括该学生的学号.班级编号.姓名。（提示：查询涉及到表Student）2执行1中创建的存储过程。 3使用输入参数创建题1中的存储过程。题1中所创建的存储过程只能学号为1的学生信息进行查看.要想对其他学生进行查看.需要进行参数传递。 4执行3中创建的存储过程.（1）按位置传递参数；（2）通过参数名传递参数； 5触发器 1）在课程表Course上创建一个触发器.该触发器被操作DELETE所触发.且要求触发触发器的DELETE语句在执行被取消。
2）在表Student中建立插入触发器, 插入一条记录时.若年龄>100或者年龄<=0,拒绝插入记录并显示：“年龄不符合规定.无法插入此记录！”； 3）创建一个触发器.如果在Student表中添加或更改数据.向客户端显示一条消息“你正在插入或修改学生表的数据”.要求触发触发器的DELETE、UPDATE语句被执行。 4 ）为Course表创建一个名称为my_trig的触发器.当用户成功删除该表中的一条或多条记录时.触发器自动删除Student表中与之有关的记录。 5 ）使用系统存储过程查看创建的触发器。 图一：创建一个存储过程.查看Tno为1（根据实际情况取）的教师的信息.包括该教师的姓名.sal
创建过程、函数和包 目标： 创建和使用子程序 创建和使用程序包 过程：procedure 过程的参数类型有in、out、in out 类型的！！ 函数：function 函数的参数类型只有in类型，不能有out、in out 类型的！！ 包：package set serveroutput on Rem =================================================================== Rem 1、创建简单的存储过程，如何执行存储过程 Rem =================================================================== CREATE or replace procedure my_proc AS(用is也行) BEGIN DBMS_OUTPUT.PUT_LINE('这是一个简单的存储过程的例子！'); end my_proc;等价于end； / --下面是在PL/SQL中执行存储过程 begin my_proc;
end; / --下面是在SQLPLUS中执行存储过程 execute my_proc;等价于 exec my_proc; Rem =================================================================== Rem 2、创建带参数的存储过程 Rem =================================================================== create table t( n_var number ); CREATE OR REPLACE PROCEDURE insert_into_t(p_parm in number) AS BEGIN DBMS_OUTPUT.PUT_LINE('---------start----------------------'); insert into t values(p_parm); end insert_into_t; / --下面是在SQLPLUS中执行存储过程 execute insert_into_t(p_parm => 100);