实验5 存储过程和触发器(20181210)oracle
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++等语言不同。
Oracle存储过程和触发器
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ] 各参数的含义如下
procedure_name 是要创 建的存储过程的名字 它后面跟一个可选项 number 它是一 个整数 用来区 别一组同名的存储过程 存储过程的命名必须符合命名规则 在一个数据库中或对其所有
结果集的格式由调用者确定 返回状态值给调用者 指明调用是成功或是失败 包括针对
数据库的操作语句 并且可以在一个存储过程中调用另一存储过程
SQL Server
我们通常更偏爱于使用第二种方法 即在
中使用存储过程而不是在客户
计算机上调用 Transaction-SQL 编写的一段程序 原因在于存储过程具有以下优点
on a.au_id=ta.au_id inner join titles t on t.title_id=ta.title_id inner join publishers p on t.pub_id=p.pub_id go
例 12-2 在该存储过程中使用了参数
use pubs if exists select name from sysobjects
where name=’author_infor and type=’p’ drop procedure author_infor go use pubs go create procedure author_infor @lastname varchar 40 , @firstname varchar 20 as select au_lname,au_fname,title, pub_name from authors a inner join titleauthor ta on a.au_id=ta.au_id inner join ttitles t on t.title_id=ta.title_id inner join publishers p on t.pub_id=p.pub_id where au_fname=@firstname and au_lname=@lastname go
存储过程和触发器(数据库实验5)
数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试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='无此专业。
Oracle存储过程和触发器基本操作解析
南华大学计算机科学与技术学院实验报告（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数据库的函数，存储过程，程序包，游标，触发器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基本语法 存储过程 触发器介绍
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存储过程（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数据库基本操作五——存储过程与触发器
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： (存储过程) 创建显⽰所有学⽣平均成绩的存储过程。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
学习创建和使用触发器。
实验内容
（1）创建存储过程GetGrade，查询指定员工的工资，使用CASE语句输出其工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高。并执行该存储过程。
创建存储过程代码如下：
create or replace procedure getgrade
callgetgrade(3);
（2）创建触发器MyTrigger，它的作用是当表departments中的记录被删除后，自动删除表employees中的对应的员工记录，从而保证数据的完整性。
程序代码如下：
create or replace trigger mytrigger
after update on departments
姓名
学号
实验日期
2018.12.10
院系
计算机与互联网学院
班级
实验出勤、操作得分
实验报告得分
实验总分
实验名称
使用存储过程和触发器
实验条件
实验器材
计算机
一、实验目的及要求
（1）了解存储过程的分类和使用方法。
（2）了解触发器的概念。
（3）学习创建和使用触发器。
实验原理(实验要求）
了解存储过程的分类和使用方法。
when yw_wage>=3000 and yw_wage<5000 then '中'
when yw_wage>=5000 then '高'
end;
dbms_output.put_line('该员工工资等级为'||yw_grade);
end;
执行存储过程代如下：
execute getgrade(3);
for each row
begin
update employees set dep_id = :new.dep_id
where dep_id = :old.dep_id;
end;
执行结果如下：
五：实验总结
通过这次上机实验：
了解了如何使用触发器和存储过程,知道他们的作用及原理.
(yw_depid number)
as
yw_wage employees.wage%type;
yw_grade varchar2(5);
begin
select wage into yw_wage
from employees
where emp_id=yw_depid;
yw_grade:=case
when yw_wage<=3000 then '低'