函数存储过程触发器

1、函数存储过程触发器第1页，共20页，2022年，5月20日，9点28分，星期一无参数函数的使用create or replace function fun_zmxreturn varchar2is spmc_name varchar2(50);begin select spmc into spmc_name from cgmxb where rownum=1; return spmc_name;end;说明：into spmc_name表示把获取的数据给定义的参数第2页，共20页，2022年，5月20日，9点28分，星期一有参数函数的使用create or replace function f

2、un_zmx(param number)return varchar2is name_sp varchar2(20);begin select spmc into name_sp from cgmxb where mxid=param; return name_sp;end;第3页，共20页，2022年，5月20日，9点28分，星期一按当前字段类型返回函数create or replace function fun_zmx(param user1.cgmxb.cj%type)return user1.cgmxb.mxid%typeis name_sp user1.cgmxb.mxid%type

3、;begin select avg(mxid) into name_sp from cgmxb where cj=param; return name_sp;end;说明：user1.cgmxb.cj%type表示当前用户的表中字段的类型第4页，共20页，2022年，5月20日，9点28分，星期一常用的系统函数字符串连接函数concatselect mxid,concat(spmc,cj) from cgmxb 等价于select mxid,spmc|cj from cgmxb 字符串截取函数substrselect mxid,substr(spmc,1,1) from cgmxb 第5页，共

4、20页，2022年，5月20日，9点28分，星期一常用的系统函数大小写转换函数upper、lower大写select mxid,upper(spmc) from cgmxb 小写select mxid,lower(upper(spmc) from cgmxb 第6页，共20页，2022年，5月20日，9点28分，星期一常用的系统函数获取系统日期sysdateselect sysdate from dual日期格式转换select to_date(20010202,yyyy-mm-dd) from dualto_date函数需要日期字符串转换函数select to_char(sysdate,yy

5、yy-MM-dd) from dual第7页，共20页，2022年，5月20日，9点28分，星期一常用的系统函数绝对值函数：absselect abs(-2.36) from dual向上取值ceilselect ceil(2.36) from dual向下取值floorselect floor(2.36) from dual取模modselect mod(8,3) from dual第8页，共20页，2022年，5月20日，9点28分，星期一常用的系统函数次访换算powerselect power(2,3) from dual表示2的3次方四舍五入换算roundselect round(3.

6、1415926,3) from dual3表示精度（保留小数的位数）第9页，共20页，2022年，5月20日，9点28分，星期一分页方法比较SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM = 21/SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A )WHERE RN BETWEEN 21 AND 40第一种效率高于第二种第10页，共20页，2022年，5月20日，9点28分，星期一存储

7、过程存储过程具有如下特点：1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率；2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据；第11页，共20页，2022年，5月20日，9点28分，星期一存储过程）在同时进行主、从表及多表间的数据维护及有效性验

8、证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。第12页，共20页，2022年，5月20日，9点28分，星期一存储过程存储过程的定义：CREATE OR REPLACE PROCEDURE procedure_name(parameter1 model datatype1,parameter2 model datat

9、ype2 .)IS ASBEGIN PL/SQL Block;END procedure_name;其中： procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型， IS AS用于开始PL/SQL代码块。注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度第13页，共20页，2022年，5月20日，9点28分，星期一存储过程1）建立存储过程时，既可以指定存储过程的参数，也可以不提供任何参数。2）存储过程的参数主要有三种类型：输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，其中IN用

10、于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境，IN OUT不仅要接收数据，而且要输出数据到调用环境。3）在建立存储过程时，输入参数的IN可以省略。第14页，共20页，2022年，5月20日，9点28分，星期一存储过程-无输入参数create or replace procedure pro_zmx(param out user1.cgmxb.mxid%type)as begin select avg(mxid) into param from cgmxb where cj=北京;end;第15页，共20页，2022年，5月20日，9点28分，星期一存储过程-输入输出参数crea

11、te or replace procedure pro_zmx(param1 in user1.cgmxb.cj%type,param out user1.cgmxb.mxid%type)as begin select avg(mxid) into param from cgmxb where cj=param1;end; 第16页，共20页，2022年，5月20日，9点28分，星期一存储过程 in out参数create or replace procedure pro_zmx(param in out user1.cgmxb.mxid%type)as begin select avg(mx

12、id) into param from cgmxb where mxid=param;end; 第17页，共20页，2022年，5月20日，9点28分，星期一触发器当需要级联操作的时候可能用到触发器语法：create or replace trigger 触发器名称 before|after insert or update or delete of 字段名称 on 表 begin PL/SQL语句 end第18页，共20页，2022年，5月20日，9点28分，星期一触发器表触发器行触发器表create or replace trigger tri_zmxafter insert on cgmxbbegin insert into cgb (cid,zdr) values (1,2);end;insert into cgmxb (mxid,spmc,cj) values (112,2