PL/SQL块分类(匿名块、命名块、子程序、触发器)
2016年07月28日 11:34:15 王涛WantTao 阅读数:2512更多
个人分类: ORACLE | PL/SQL
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wangtao510/article/details/52053599
-
-匿名块
-
DECLARE
-
V_NAME VARCHAR2(5);
-
BEGIN
-
SELECT NAME INTO V_NAME WHERE AGE=100;
-
DBMS_OUTPUT.PUT_LINE(V_NAME);
-
EXCEPTION
-
WHER NO_DATA_FOUND THEN
-
DBMS_OUTPUT.PUT_LINE('未查到到任何数据');
-
END;
-
-命名块
-
<<OUTER>>
-
DECLARE V_NAME VARCHAR2(5);
-
BEGIN
-
SELECT NAME INTO V_NAME WHERE AGE=100;
-
DBMS_OUTPUT.PUT_LINE(V_NAME);
-
EXCEPTION
-
WHER NO_DATA_FOUND THEN
-
DBMS_OUTPUT.PUT_LINE('未查到到任何数据');
-
END;--<<OUTER>>
-
-子程序之过程
-
CREATE OR REPLACE PROCEDURE UPDATE_SAL(NEWNAME VARCHAR2,_ID NUMBER)
-
IS
-
ERROR_EXP EXCEPTION;
-
BEGIN
-
UPDATE TABLENAME SET NAME=NEWNAME WHERE ID=_ID;
-
IF SQL%NOTFOUND THEN
-
RAISE ERROR_EXP;
-
END IF;
-
EXCEPTION
-
WHEN ERROR_EXP
-
RAISE_APPLICATION_ERROR('-20004','error');
-
END;-
-
-执行过程
-
exec UPDATE_SAL('WT',100);
-
--子程序之函数
-
CREATE FUNCTION ANNUAL_INCOME(NAME VARCHAR2)
-
RETURN NUMBER IS
-
ANNUAL_SALARY NUMBER(7,2);
-
BEGIN
-
SELECT SAL*12+NVL(COMM,0) INTO ANNUAL_SALARY
-
FROM TABLENAME WHERE LOWER(ENUMA)=LOWER(NAME);
-
END;
-
--执行函数
-
SQL> VAR INCOME NUMBER
-
SQL> CALL ANNUAL_INCOME('NAME') INTO:INCOME
-
SQL> PRINT INCOME
-
--触发器 指隐含执行的存储过程
-
CREATE TRIGGER UPDATE_CASCADE
-
AFTER UODATE OF deptno ON dept FOR EACH ROW
-
BEGIN
-
UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
-
END;
-
如上例所示,触发器UPDATE_CASCADE用于实现联级更新,当更新 deptno列时,emp的deptno列也会相应更新
https://blog.csdn.net/wangtao510/article/details/52053599
Oracle中命名块之存储过程的简单使用
2017年04月05日 10:29:32 菜鸟的奋斗之路 阅读数:1585
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/super_YC/article/details/69226920
一、匿名块和命名块
◆PL/SQL块分为良好总:命名块和匿名块。
◆匿名块:以declare或begin开始,每次执行匿名块都要通过客户端工具将其发送给Oracle,经过语法分析、编译然后执行。
◆命名块:具有名称的PL/SQL块,这些命名块被存储在Oracle中,编译一次,以后只可调用就可多次执行。如:存储过程、存储函数、包、触发器等、
存储过程:无返回值;
存储函数:有返回值;
包:可容纳多个过程或函数的一个容器(较好管理这些过程和函数,类似于java中的包);
触发器:在合适的实际被自动执行。(无需调用,在满足要求的情况下,自动执行;触发器也被称为隐式执行的命名块!)
◆匿名块和命名块的区别:
匿名块:能够动态地创建和执行过程代码的PL/SQL结构,每次执行匿名块时就会自动的将该块数据通过文本的形式发送给Oracle,然后执行,反馈给用户,每次执行都需发送和编译!
命名块:持久化的方式将代码作为数据库属性储存在系统目录中,在Oracle中编译一次,下次若使用可直接调用执行,无需再次编译,非常高效。
二、存储过程:
存储过程是具有名称、完整特定功能和无返回值的PL/SQL块,只需发送调用命令即可执行该过程,能够实现代码的重用,不能在SQL语句当中调用!
◆创建存储过程:
格式:create[or replace] procedure proc_name
[(para1[int|out|in out]type,...)]
{is|AS}
prdc_body
proc_name表示存储过程名;
para1表示参数名;type是参数para1的数据类型;
proc_body表示过程体.过程体是遵循PL/SQL块的结构,但不能使用declare关键字,end后面可带有过程名。
注意:参数列表是可选的
◆调用存储过程:(存储过程创建的时候会自动的保存在Oracle中,在执行储存过程的时候,PL/SQL块只需调用即可!)
(1)如果在PL/SQL块中调用,直接使用proc_name(...);
(2)如果使用sql plus环境中,需要使用 EXEC proc_name(...)的形式;
(3)如果存储过程无参数,这调用时可带有圆括号也可不带!
【示例】创建一个无参数的存储过程(显示所有员工的信息)
create or replace procedure pr_show_employee
is
--进入块的声明部分(无需编写declare关键字)
--定义游标
cursor v_emp_cursor is select * from employees;
--定义游标类型的记录型行变量
v_emp_record v_emp_cursor%rowtype;
begin
for v_emp_record in v_emp_cursor loop
dbms_output.put_line(v_emp_record.employee_id||','||v_emp_record.first_name||','||
v_emp_record.last_name||','||v_emp_record.salary);
end loop;
end pr_show_employee ;
--调用存储过程(在一个匿名块中执行)
begin
pr_show_employee();
end;
【示例】存储过程的参数接受产品类别编号,输出该类所有产品的信息
create or replace procedure pr_show_product(
product_type products.product_type_id%type)
as
--定义游标
cursor v_prd_cursor is select * from products p where product_type = p.product_type_id;
--定义游标的行类型变量
v_prd_record v_prd_cursor%rowtype;
begin
for v_prd_record in v_prd_cursor loop dbms_output.put_line(v_prd_record.product_id||','||v_prd_record||','||product_type_id||','||v_prd_record.description||','
||v_prd_record. name||','||v_prd_record.price);
end loop;
end pr_show_product;
--调用存储过程(执行)
declare
--定义编号变量的类型
v_prd_type_id products.product_type_id%type := 1;
begin
pr_show_product(v_prd_type_id);
end;
◆参数的传递模式:
参数传递有三种模式:in、out、in out
in:过程调用时,实参的值会传递给形参。形参被看作是常量,在过程内部不能被修改。
out:过程调用时,实参的值被忽略,形参为null值,在过程内部形参变量可以读写。过程结束时,形参的值赋值给实参!
in out:过程调用时,实参的值传递给形参,在过程内部形参变量可读可写,过程结束时,形参的值赋值给实参。
注意:如果在定义存储过程时,没有指明参数传递模式,则默认为 in 模式
【示例】创建一个有参数传递默认的存储过程
create or replace procedure pr_test(p_1 in out varchar2)
is
begin
dbms_output.put_line('p_1='||p_1);
p_1 := 'abc';
dbms_output.put_line('p_1='||p_1);
end pr_test;
--调用存储过程
declare
v_1 varchar2(5) := 'wwww';
begin
pr_test(v_1);
dbms_output.put_line('v_1='||v_1);
end;
注意:(1)在创建存储过程时,形参类型不能指定具体的长度,字符类型就为 varchar2,数字类型就是number;默认和实参类型的长度等同
(2)使用 in 模式,形参接受实参的值,默认形参为常量,在存储过程当中不能被修改,否则出错!
(3)使用 out 模式,实参的值被忽略,但必须是变量,用来接收存储过程执行完形参的值;而形参是可读可写的。
(4)使用 in out 模式,实参的值传递给形参,也必须是变量,形参可读可写,最后执行完存储过程形参的值传递给实参!
(5)参数传递,实参的值和长度或精度约束会传递给形参,因此,形参不能有长度或精度的约束。在过程中给形参赋值时,要注意它的长度或精度的限制!
https://blog.csdn.net/super_yc/article/details/69226920
【PL/SQL】匿名块、存储过程、函数、触发器
名词解释
子程序:PL/SQL的过程和函数统称为子程序。
匿名块:以DECLARE或BEGIN开始,每次提交都被编译。匿名块因为没有名称,所以不能在数据库中存储并且不能直接从其他PL/SQL块中调用。
命名块:除匿名块之外的其他块。包括过程、函数、包和触发器。可以在数据库中存储并在适当的使用运行。
子程序的优点:
1)具有扩展性
可以自定义PL/SQL语言以满足实际应用
2)高可用和可维护性
子程序的调用不受调用者数目的影响,只要有效就可被调用。
如果定义被更改以后,只有子程序受到影响,简化了维护、优化过程。
匿名块、过程、函数的执行过程基本相似,不同之处在于:匿名块在DECLARE后面声明变量,而过程和函数在IS后面声明。
函数也可以称为过程,特殊之处为:函数总是有一个返回值(RETURN)。
匿名块
[DECLARE]
BEGIN --statements
[EXCEPTION]
END; | 过程
PROCEDURE name IS
BEGIN --statements
[EXCEPTION]
END; | 函数
FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION]
END; |
1、 创建存储过程
1)语法
CREATE [OR REPLACE] PROCEDURE procedure_name
[argument[{IN|OUT|IN OUT}] TYPE]
{IS|AS}
<类型、变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END;
2)示例
①要求:记录登录数据的userid和logdate。
##创建表,用来存储userid和logdate
SQL> create table logtable (userid varchar2(10),logdate date);
Table created.
##创建存储过程
SQL> create or replace procedure logexecution is
2 begin
3 insert into logtable (userid, logdate) values (user, sysdate);
4 end;
5 /
Procedure created.
##执行存储过程
SQL> exec logexecution;
PL/SQL procedure successfully completed.
##验证执行结果
SQL> select * from logtable;
USERID LOGDATE
---------- -------------------
SYS 2017-03-12 01:18:28
② 要求:删除表中记录
##创建过程
SQL> create or replace procedure delemp(v_empno in emp.empno%type) is
2 no_result exception;
3 begin
4 delete from emp where empno = v_empno;
5 if sql%notfound then
6 raise no_result;
7 end if;
8 dbms_output.put_line('employee number' || v_empno || 'was deleted!');
9 exception
10 when no_result then
11 dbms_output.put_line('no row found!');
12 when others then
13 dbms_output.put_line('other error!');
14 end delemp;
15 /
Procedure created.
##执行过程
SQL> exec delemp(999);
no row found!
PL/SQL procedure successfully completed.
SQL> exec delemp(7499);
employee number7499was deleted!
PL/SQL procedure successfully completed.
2、 创建函数
1)语法
CREATE [OR REPLACE] FUNCTION function_name
[(argument[{IN|OUT|IN OUT}] TYPE,
...
argument[{IN|OUT|IN OUT}] TYPE)]
RETURN return_type {IS|AS}
function_body
2)示例
##创建函数
SQL> create or replace function get_salary(dept_no number, v_num out number)
2 return number is
3 v_sum number;
4 begin
5 select sum(sal), count(*)
6 into v_sum, v_num
7 from emp
8 where deptno = dept_no;
9 return v_sum;
10 exception
11 when no_data_found then
12 dbms_output.put_line('你需要的数据不存在!');
13 when too_many_rows then
14 dbms_output.put_line('程序运行错误!请使用游标');
15 when others then
16 dbms_output.put_line('发生其他错误!');
17 end get_salary;
18 /
Function created.
##调用函数(注:函数不能单独调用,只能在语句中调用)
SQL> declare
2 v_num number;
3 v_sum number;
4 begin
5 v_sum := get_salary(30,v_num);
6 dbms_output.put_line('30 department sum salary is '||v_sum||' employee: '||v_num);
7 end;
8 /
30 department sum salary is 9400 employee: 6
PL/SQL procedure successfully completed.
3、 触发器
触发器:在数据库中以独立的对象存储,它与存储过程不同的是,存储过程通过其他程序来启动运行或者直接启动运行,而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且触发器不能接收参数,不能commit。
触发器类型:
DML触发器:Oracle可以在DML语句进行触发,可以在DML操作前或操作后进行触发,并且可以对每个行或语句操作上进行触发。
替代触发器:由于在Oracle里,不能对复杂视图进行操作。所以给出了替代触发器。它就是Oracle专门为进行视图操作的一种处理方法。
系统触发器:它可以在Oracle数据库系统的事件中进行触发,如Oracle系统的启动与关闭等。
触发器组成:
触发事件:即在何种情况下触发TRIGGER。
触发事件:即该TRIGGER是在触发事件发生之前(BEFORE)还是之后(AFTER)触发。
触发器本身:即该TRIGGER被触发之后的目的和意图。
触发类型:说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器:指当某触发事件发生时,该触发器只执行一次。
行级(ROW)触发器:指当某触发器事件发生时,对收到该操作影响的每一行数据,触发器都单独执行一次。
1)示例
①创建DML触发器
要求:当职工表emp被删除一条记录时,把被删除记录写到职工表删除日志表中。
##建立删除日志表
SQL> create table emp_his as select * from emp where 1=2;
Table created.
##创建触发器
SQL> create or replace trigger del_emp
2 before update or delete on scott.emp
3 for each row
4 begin
5 insert into emp_his
6 (deptno, empno, ename, job, mgr, sal, comm, hiredate)
7 values
8 (:old.deptno,
9 :old.empno,
10 :old.ename,
11 :old.job,
12 :old.mgr,
13 :old.sal,
14 :old.comm,
15 :old.hiredate);
16 end del_emp;
17 /
Trigger created.
##验证触发器
SQL> delete from emp where empno=7844;
1 row deleted.
SQL> select count(*) from emp_his;
COUNT(*)
----------
1
②创建替代触发器
##创建复杂视图
SQL> create or replace view emp_view as
2 select deptno,count(*) total_employeer,sum(sal) total_salary
3 from emp group by deptno;
View created.
##对视图进行DML会报错,因为复杂视图不能直接进行DML操作
SQL> delete from emp_view where deptno=10;
delete from emp_view where deptno=10
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
##创建触发器
SQL> create or replace trigger emp_view_delete
2 instead of delete on emp_view
3 for each row
4 begin
5 delete from emp where deptno = :old.deptno;
6 end emp_view_delete;
7 /
Trigger created.
##再次删除,发现执行成功
SQL> delete from emp_view where deptno=10;
1 row deleted.
③ 创建系统事件触发器
注意:系统时间触发器要以sys用户运行
##登录sys用户
SQL> conn / as sysdba
Connected.
##创建表
SQL> create table logtable (name varchar2(20),log_time date);
Table created.
##创建触发器
SQL> create or replace trigger login_his
2 after logon on database
3 begin
4 insert into logtable values (user, sysdate);
5 end;
6 /
Trigger created.
##验证触发器
SQL> select * from logtable;
NAME LOG_TIME
-------------------- -------------------
SYS 2017-03-12 00:15:29
分类: Oracle
https://www.cnblogs.com/NextAction/p/7366636.html
原创
oracle存储过程、匿名块、函数、包
梦Scarlett0人评论1121人阅读2017-11-29 11:41:29
使用过程与函数的原则:
1、如果需要返回多个值和不返回值,就使用过程;如果只需要返回一个值,就使用函数。
2、过程一般用于执行一个指定的动作,函数一般用于计算和返回一个值。
3、可以SQL语句内部(如表达式)调用函数来完成复杂的计算问题,但不能调用过程。所以这是函数的特色。
2、存储过程,加自定义exception,并改进,由外部传参数
©著作权归作者所有:来自51CTO博客作者梦Scarlett的原创作品,如需转载,请与作者联系,否则将追究法律责任
存储过程sql
0
分享
收藏
上一篇:我的友情链接 下一篇:jdbc实现事物管理并搬表
http://blog.51cto.com/1385903/2045575