- 浏览: 364460 次
- 性别:
- 来自: Alien
文章分类
最新评论
-
风一样的男人__:
[flash=200,200][url]引用[/url][/f ...
java线程内存模型,线程、工作内存、主内存 -
极乐君:
厉害了,,,请问可以转载到我们专栏吗?会注明来源和地址的~专栏 ...
java线程内存模型,线程、工作内存、主内存 -
zdd001:
Spring 线程池使用 -
zdd001:
Spring 线程池使用 -
u014663756:
看了三行就知道是我想要的!!
java线程内存模型,线程、工作内存、主内存
PL/SQL高级编程部分
无名块不存储在数据库中,并且不能从其他的PL/SQL块中调用
有名块存储在数据库数据字典中,可以在客户端与服务器端的任何工具和任何应用中运行
(1).存储过程
(2).存储函数
(3).包
(4).触发器
过程执行某一动作,函数计算一个值,包将相关的过程函数逻辑上捆绑在一起,触发器隐式执行某一动作
存储过程和函数是把一个PL/SQL块编译后存进数据库数据字典中,以后可以重复使用的模式对象
注意:在存储过程和函数中的形式参数及返回结果如果是字符型,不要指定指定长度
1.存储过程
(1).创建存储过程
- create [OR REPLACE] PROCEDURE 过程名[(参数名[IN|OUT|IN OUT]数据类型...)]
- {IS | AS}
- [说明部分]
- BEGIN
- 语句系列
- [EXCEPTION 出错处理]
- END[过程名];
参数说明:
IN 输入参数:用来从调用环境中向存储过程传递值,即IN模式参数不能出现在赋值语句式边
OUT输出参数:用来从存储过程中返回值给调用者,在过程体内,必须给OUT模式参数赋值,OUT模式参数
可以出现在赋值语句的左边.没有值的OUT模式参数不能出现赋值语句的右边.
IN OUT:输入参数,输出参数.即可从调用者向存储过程传递值,过程执行后又可返回改变后的值给调用者.
eg:
给某一指定的员工涨指定数量的工资,该存储过程有两个形式参数:emp_id和v_increase,没有指定参数
的模式,默认是IN 模式.
- create procedure raise_salary(emp_id integer, v_increase real) is
- BEGIN
- update emp set sal = sal + v_increase where empno = emp_id;
- commit;
- end raise_salary;
根据给定的员工号(通过IN模式参数代入),用OUT模式参数返回员工的姓名,工资和奖金信息.
- create or replace procedure query_emp(v_emp_no IN emp.empno%type,
- v_emp_name out emp.ename%type,
- v_emp_sal out emp.sal%type v_emp_comm out emp.comm%type) is
- BEGIN
- select ename, sal, comm
- into v_emp_name, v_emp_sal, v_emp_comm
- from emp
- where empno = v_emp_no;
- end query_emp;
2.存储函数
(1).创建存储函数的语法如下:
- create [or replace ]function 函数名[(参数名[in]数据类型...)] return 数据类型
- {is|as}
- [说明部分]
- BEGIN
- 语句序列
- return (表达式)
- [EXCEPTION 例外处理程序]
- END 函数名;
编写一个函数,计算几个人的平均工资,并在函数体的循环过程中输出结果
- create or replace function average_sal(v_n in number(3)) return number
- is
- cursor c_emp is select empno,sal from emp;
- v_total_sal emp.sal%type:=0;
- v_counter number;
- v_emp_no emp.empno%type
- BEGIN
- FOR r_emp in c_emp loop exit when c_emp% rowcount>v_n or c_emp%notfound;
- v_total_sal:=v_total_sal+r_emp.sal;
- v_counter:=c_emp%rowcount;
- v_emp_no:=r_emp.empno;
- dbms_output.put_line('loop='||v_counter||';empno='||v_emp_no);
- end loop;
- return (v_total_sal/v_counter);
- end average_sal;
注意存储过程与函数的区别
1...返回值不同:存储函数有零个或多个参数,函数可以有IN参数,但不能有OUT参数。函数只返回一个值,函数值的返回是靠return 子句返回的
2...存储过程有零个或多个参数,地程可以有IN参数,在调用过程时通过参数列表接受IN参数的输入。过程不返回值,其返回值是靠OUT参数带出来的
过程可以有零个或多个OUT参数返回结果
调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现
过程名(实际参数1,实际参数2....)
函数可以在任何表达式能够出现的地方被调用,调用函数的语句不能作为可执行语句单独出现在PL/SQL块中
变量名:函数名(实际参数1,实际参数2....);
存储过程的调用
(1).在PL/SQL块中的调用
eg:
- DECLARE
- V_empno emp.empno%type := 7777;
- v_ename emp.ename%type;
- v_sal emp.sal%type;
- v_comm emp.comm%type;
- BEGIN
- query_emp(v_empno, v_ename, v_sal, v_comm);
- dbms_output.put_line(v_ename || ' ' || v_sal || ' ' || v_comm);
- END;
函数的调用
- DELCARE
- v_empno number:=111;
- v_sal nubmer;
- BEGIN
- v_sal:=get_sal(v_empno);
- dbms_output.put_line(v_sal);
- end;
(2).在SQL*PLUS中的调用
过程的调用
eg:
- set serveroutput on/*激活DBMS_OUTPUT系统包*/
- ACCEPT p_emp_no prompt 'please enter the employee number:';/*接受员工号*/
- variable v_emp_name varchar2(14);/*定义存放OUT参数输出结果的变量 */
- variable v_emp_sal number;
- variable v_emp_comm number;
- execute query_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);
注意在SQL*PLUS中,用variable定义的变量在引用时,必须前面加昌号(:),用accept接收的变量在引用时,必须前面加& 符号
函数的调用
函数不能作为一条语句单独出现,只能出现在别的过程中作为别的过程的参数
SQL>EXECUTE dbms_output.put_line(get_sal(0000));
或者
SQL>SELECT get_sal(0000) from dual;
3.包
(1).创建包的说明:
eg:制作一个包的说明,生成一个管理雇员薪水的包sal_package,其中包括一个为雇员加薪的过程和降薪的过程
并且在包中还有两个记录所有雇员薪水增加和减少的全局变量
- create package sal_package is
- procedure raise_sal(v_empno emp.empno%type), v_sal_increment emp.sal%type);
- procedure reduce_sal(v_empno emp.empno%type, v_sal_reduce emp.sal%type);
- v_raise_sal emp.sal%type := 0;
- v_reduce_sal emp.sal%type := 0;
- end;
创建包的主体
制作sal_package包的包主体
- create or replace package body sal_package is
- procedure raise_sal(v_empno emp.empno%type, v_sal_increment emp.sal%type) is
- BEGIN
- UPDATE emp set sal = sal + v_sal_increment where empno = v_empno;
- commit work;
- v_raise_sal := v_raise_sal + v_sal_increment;
- end;
- procedure reduce_sal(v_empno emp.empno%type, v_sal_reduce emp.sal%type) is
- BEGIN
- update emp set sal = sal - v_sal_reduce where empno = v_empno;
- commit work;
- v_reduce_sal := v_reduce_sal + v_sal + reduce;
- end;
- end;
包的调用:包名.过程名
SQL>EXECUTE sal_package.raise_sal(111,23423);
4.触发器
eg:
制作一个数据库触发器,将那些超过其工种工资范围的员工信息记录到audit_message表中,在sal_guide记录
了每一工种的工资范围
- create or replace trigger check_sal
- before insert or update of sal, job on emp
- for each row
- when (new.job <> 'persident')
- DECLARE
- v_minsal sal_guide.minsal%type;
- v_maxsal sal_guide.maxsal%type;
- e_sal_out_of_range exception;
- BEGIN
- select minsal, maxsal
- into v_minsal, v_maxsal
- from sal_guide
- where job = :new.job;
- if :new.sal < v_minsal or :new :sal > v_maxsal then
- raise e_sal_out_of range;
- end if;
- exception
- when e_sal_out_of_range then
- insert into audit_message
- (line_nr, line)
- values
- (1,
- 'Salary' || TO_CHAR(:new.sal) || 'is out of range for employee' ||
- TO_CHAR(:new.empno));
- END;
(1).触发器的组成
触发时间:before after
触发事件:insert update delete
触发器类型:statement,row (语句级,行级)
触发器体(完整的PL/SQL块)
触发器可分语句级触发器,行级触发器
(2).语句级触发器
创建语法如下:
eg:
创建一个before 型语句级触发器,限制一周内向emp表插入数据的时间,如果是周六,周日
或晚上6点到第二天早上8点之间插入,则中断操作,并提示用户不允许在此时间向emp表插入
- create or replace trigger secure_emp
- before insert on emp
- BEGIN
- IF (TO_CHAR(sysdate, 'DY') IN ('SAT', 'SUN')) or
- (TO_CHAR(sysdate, 'HH24') NOT BETWEEN '8' and '18') then
- raise_application_error(-20500,
- 'you may only insert into emp during normal hours.');
- END IF;
- END;
(3).使用触发器谓词(inserting,updating,deleting)
通过谓词可以创建一个包含多个触发事件的触发器
对上例进行扩展不但限制插入数据的时间,还限制进行数据修改和删除的时间
- create or replace trigger secure_emp
- before delete or insert or update on emp
- BEGIN
- if (TO_CHAR(sysdate,'DY' IN('SAT','SUN')) OR (TO_NUMBER(sysdate,'HH24')NOT BETWEEN 8 AND 18 )THEN
- if deleting then
- raise_application_error(-20502,'You may only deletefrom emp during normal hours. ');
- elsif inserting then
- raise_application_error(-20500,'You may only insert into emp during mormal hours');
- else
- RAISE_APPLICATION_ERROR(-20504,'You may only update emp table during normal hours.');
- end if ;
- end if ;
- end;
(4).行级触发器
创建语法
- create [or replace]trigger trigger_name
- {before|after} event1[or event2....] on table_name
- for each row [when restricting_condition]
PL/SQL
eg:创建一个行级触发器,将每个用户对数据库emp表进行数据库操纵(插入,更新,删除)的次数记录到
audit_table表中
- create or replace trigger audit_emp
- after delete or insert or update on emp
- for each row
- BEGIN
- IF DELETING THEN
- update audit_table
- set del = del + 1
- where user_name = user
- and table_name = 'emp'
- and column_name is null;
- elsif inserting then
- update audit_table
- set ins = ins + 1
- where user_name = user
- and table_name = 'emp'
- and column_name is null;
- else
- update audit_table
- set upd = upd + 1
- where user_name = user
- and table_name = 'emp'
- and column_name is null;
- end if;
- end;
使用行级触发器的标识符(:OLD和:NEW)
在列名前加上:OLD标识符表示该列变化前的值,加上:NEW标识符表示变化后的值
eg:
在行级触发器加WHEN限制条件,根据销售员工资的改变自动计算销售员的奖金
- create or replace trigger derive_comm
- before update of sal on emp
- for each row
- when (new.job = 'SALESMAN')
- BEGIN
- :new.comm := :old.comm * (:new.sal / :old.sal);
- end;
发表评论
-
oracle 表字段命名规范
2013-04-09 16:35 3740命名一律为大写字母(或小写,不要大下写混合)英文单词单 ... -
PowerDesigner设计表的简单用法 转
2013-04-09 16:22 1354PowerDesigner设计表的简单用法 (h ... -
(转)Oracle Partition 分区详细总结
2013-03-18 00:41 943(转)Oracle Partition 分区详细总结 ... -
oracle sql语句优化
2013-03-01 00:27 877oracle sql语句优化 报 我转blogj ... -
oracle结构
2012-12-26 03:06 954Oracle10g结构 本 ... -
Oracle中的索引详解
2012-12-26 02:56 736Oracle中的索引详解 ... -
sql中的group by having
2012-12-14 18:32 913--sql中的group by 用法解析:-- Grou ... -
Oracle数据库Decode()函数的使用方法
2012-10-30 13:48 951Oracle数据库Decode()函数的使用方法 语法 ... -
oracle 存储过程
2012-03-21 12:43 1235select (case when nvl(max(catgr ... -
java 基础性能'
2012-02-04 00:11 91012.尽量合理的创建HashM ... -
刷新物化视图和查看子类四级分类
2011-12-07 16:45 1297exec dbms_refresh.refresh('mv_g ... -
删除重复记录(sql) case when
2011-11-15 16:43 2472select 姓名,工龄, (case when (工龄> ... -
SQL写一个临时表,去更新这个表里的字段
2011-10-17 16:31 5120create table temp_should_displa ... -
SQL总结
2011-10-11 20:57 1030cd "C:\Program Files (x86) ... -
使用BoneCP的必备条件
2011-09-21 19:36 1926使用BoneCP需要如下类库支持: 被连接的数据库的JDBC驱 ... -
oracle 树结构查询
2011-08-24 23:31 1091select * from table_name where ... -
sql语句优化
2011-08-22 15:56 898最近几周一直在进行数 ...
相关推荐
oracle pl/sql 存储过程和函数与触发器
12oracle的PL/SQL编程-函数.包.触发器 PPT 12oracle的PL/SQL编程-函数.包.触发器 PPT
通过使用过程、函数、触发器和包,建立模块化的PL/SQL应用。 内容推荐 《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、...
第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体应用 第26章 开发web应用 第27章 dbms_sq...
通过使用过程、函数、触发器和包,建立模块化的PL/SQL应用。 内容推荐 《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、...
讲解oracle中的pl/sql的使用,存储过程,游标,函数,程序包,触发器。等等。
C# 访问Oracle示例+PL/SQL+存储过程+触发器 完整示例 测试可用 --PL/SQL基础1 declare begin dbms_output.('不输出不换行'); dbms_output.put_line('输出并换行'); end; --PL/SQL基础2 declare dig number(20,...
PL/SQL,包括函数、包、触发器、异常、游标、子过程等,希望对学习Oracle、PL/SQL的朋友有帮助!
第19章PL/SQL函数 第20章PL/SQL包 第21章触发器 第22章使用对象类型 第四部分PL/SQL系统包 第23章使用大对象 第24章读写OS文件 第25章开发多媒体应用 第26章开发Web应用 第27章DBMS_SQ动态SQL ...
走进Oracle、认识PL/SQL、数据表的基本操作、表中数据的基本操作、数据的基本查询、查询中函数的使用、数据表的高级查询、索引及视图的使用、数据类型、流程控制、游标、存储过程和函数、触发器、异常处理、事务和锁...
PL/SQL 程序设计 本章主要重点: PL/SQL概述 PL/SQL块结构 PL/SQL流程 运算符和表达式 游标 异常处理 数据库存储过程和函数 包 触发器
第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体应用 第26章 开发web应用 第27章 dbms_sq...
PL/SQL还可以用来编写过程、函数、包及数据库触发器。过程和函数也称为子程序,在定义时要给出相应的过程名和函数名。它们可以存储在数据库中成为存储过程和存储函数,并可以由程序来调用,它们在结构上同程序模块...
除此之外,可以在ORACLE数据库的某些客户端工具中,使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点,以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍...
PL/SQL,Oracle对SQL标准的扩充,增加了面向过程的功能,所以可以用来编写存储过程、存储函数、触发器等等。 PL/SQL是结构化SQL,就是在标准SQL中加入了IF...ELSE... For....等
Oracle PL/SQL编程基础。包括,基本sql语句,函数,存储过程,触发器,包。PL/SQL工具的使,等等oracle相关的基础知识
第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体应用 第26章 开发web应用 第27章 dbms_sq...
oracle PL_SQL编程 创建存储过程、函数、触发器等