《函数存储过程触发器》由会员分享，可在线阅读，更多相关《函数存储过程触发器（20页珍藏版）》请在金锄头文库上搜索。
1、函数、存储过程、触发器,使用范例说明 函数必须有返回值,无参数函数的使用,create or replace function fun_zmx return varchar2 is spmc_name varchar2(50); beginselect spmc into spmc_name from cgmxb where rownum=1;return spmc_name; end; 说明：into spmc_name表示把获取的数据给定义的参数,有参数函数的使用,create or replace function fun_zmx(param number) return varchar
2、2 is name_sp varchar2(20); beginselect spmc into name_sp from cgmxb where mxid=param;return name_sp; end;,按当前字段类型返回函数,create or replace function fun_zmx(param user1.cgmxb.cj%type) return user1.cgmxb.mxid%type is name_sp user1.cgmxb.mxid%type; beginselect avg(mxid) into name_sp from cgmxb where cj=pa
3、ram;return name_sp; end; 说明：user1.cgmxb.cj%type表示当前用户的表中字段的类型,常用的系统函数,字符串连接函数concat select mxid,concat(spmc,cj) from cgmxb 等价于 select mxid,spmc|cj from cgmxb 字符串截取函数substr select mxid,substr(spmc,1,1) from cgmxb,常用的系统函数,大小写转换函数upper、lower 大写 select mxid,upper(spmc) from cgmxb 小写 select mxid,lower(up
4、per(spmc) from cgmxb,常用的系统函数,获取系统日期sysdate select sysdate from dual 日期格式转换 select to_date(20010202,yyyy-mm-dd) from dual to_date函数需要日期字符串 转换函数 select to_char(sysdate,yyyy-MM-dd) from dual,常用的系统函数,绝对值函数：abs select abs(-2.36) from dual 向上取值ceil select ceil(2.36) from dual 向下取值floor select floor(2.36)
5、from dual 取模mod select mod(8,3) from dual,常用的系统函数,次访换算power select power(2,3) from dual表示2的3次方 四舍五入换算round select round(3.1415926,3) from dual 3表示精度（保留小数的位数）,分页方法比较,SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM = 21 / SELECT * FROM ( SELECT A.*, ROWNUM RN FROM
6、(SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40第一种效率高于第二种,存储过程,存储过程具有如下特点： 1）存储过程是预编译过的，并且经优化后存储于SQL内存中，使用时无需再次编译，提高了工作效率； 2）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多； 3）使用存储过程可以减少SQL注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户
7、只能在其控制下间接地存取数据；,存储过程,）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制； 5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可； 6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。,存储过程,存储过程的定义： CREATE OR REPLACE PROCEDURE procedure_name (parameter1 model datatype1,pa
8、rameter2 model datatype2 .) IS AS BEGIN PL/SQL Block; END procedure_name; 其中： procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型， IS AS用于开始PL/SQL代码块。 注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度,存储过程,1）建立存储过程时，既可以指定存储过程的参数，也可以不提供任何参数。 2）存储过程的参数主要有三种类型：输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT)，其中IN用于接收
9、调用环境的输入参数，OUT用于将输出数据传递到调用环境，IN OUT不仅要接收数据，而且要输出数据到调用环境。 3）在建立存储过程时，输入参数的IN可以省略。,存储过程-无输入参数,create or replace procedure pro_zmx(param out user1.cgmxb.mxid%type) as beginselect avg(mxid) into param from cgmxb where cj=北京; end;,存储过程-输入输出参数,create or replace procedure pro_zmx(param1 in user1.cgmxb.cj%ty
10、pe,param out user1.cgmxb.mxid%type) as beginselect avg(mxid) into param from cgmxb where cj=param1; end;,存储过程 in out参数,create or replace procedure pro_zmx(param in out user1.cgmxb.mxid%type) as beginselect avg(mxid) into param from cgmxb where mxid=param; end;,触发器,当需要级联操作的时候可能用到触发器 语法：create or repl
11、ace trigger 触发器名称before|after insert or update or delete of 字段名称 on 表beginPL/SQL语句end,触发器,表触发器 行触发器 表 create or replace trigger tri_zmx after insert on cgmxb begininsert into cgb (cid,zdr) values (1,2); end;insert into cgmxb (mxid,spmc,cj) values (112,2,3),触发器,行 create or replace trigger tri_zmx before insert on cgmxb for each row beginselect seq_zmx.nextval into :new.mxid from dual; end;,
