存储过程概述
存储过程是子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库。是一个有名字的PL/SQL代码块,支持接收或不接受参数,同时也支持参数输出。一个存储过程通常包含定义部分,执行部分,Exception部分,可以被其他子程序调用,也可以被重用。
过程定义
CREATE [OR REPLACE]PROCEDURE procedure_name
[(argument_name [IN | OUT | IN OUT] argument_type)]
AS | IS
BEGIN
procedure_body;
END [procedure_name];
存储过程中参数的类型
IN:表示是一个输入参数,可以指定缺省值。如省略参数类型,则缺省为in类型
OUT:表示是一个输出参数
IN OUT:既可以作为一个输入参数,也可以作为一个输出参数来输出结果
过程调用
EXECUTE |CALL procedure_name [(argument_list)]
例:定义一个过程,以JOB为参数,查询该JOB的最高工资、最低工资、平均工资。
create or replace procedure query_sal(v_job in emp.job%type)
as
v_min_sal emp.sal%type;
v_max_sal emp.sal%type;
v_avg_sal emp.sal%type;
begin
select min(sal) into v_min_sal from emp where job = v_job;
select max(sal) into v_max_sal from emp where job = v_job;
select avg(sal) into v_avg_sal from emp where job = v_job;
dbms_output.put_line('This job is minimum salary is ' || v_min_sal);
dbms_output.put_line('This job is maximum salary is ' || v_max_sal);
dbms_output.put_line('This job is average salary is ' || v_avg_sal);
exception
when no_data_found then
dbms_output.put_line('Not Record Found');
end;
SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
This job is minimum salary is 1250
This job is maximum salary is 1600
This job is average salary is 1400
PL/SQL procedure successfully completed.
参数及其传递方式
在 建立过程时,传递的参数为可选项,如果省略参数选项,则过程为无参过程(定义时不指定参数,调用时也不需要参数)。如果指定参数选项,则过程为有参过程 (定义时需要指定参数名字、模式、数据类型,调时时需要给出对应的参数值),定义时的参数,称为形参,调用时的参数称为实参。
无参过程
create or replace procedure display_systime
as
begin
dbms_output.put_line('Current Time is ' || sysdate);
end;
SQL> exec display_systime;
Current Time is 24-FEB-13
有参过程
定义时需要指定参数的名字、模式、数据类型
例:定义一个添加记录的过程(全部为输入参数)
create or replace procedure add_emp
(
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept in emp.deptno%type default 20 --缺省的部门号
)
as
begin
insert into emp (empno,ename,deptno) values(v_no,v_name,v_dept);
exception
when dup_val_on_index then
dbms_output.put_line('Record Exists');
end ;
SQL> exec add_emp(7369,'TEST',20); --调用
Record Exists
例:定义一个输入员工编号,修改记录,再返回修改后的结果(姓名和工资)。
create or replace procedure ed_emp
(
v_no in emp.empno%type, --定义了一个in类型,二个out类型的参数
v_name out emp.ename%type,
v_sal out emp.sal%type)
as
begin
update emp set sal = sal + 100 where empno = v_no;
select ename,sal into v_name,v_sal from emp where empno = v_no;
exception
when no_data_found then
dbms_output.put_line('Not Data Found');
end;
/
Procedure created.
SQL>VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL procedure successfully completed.
SQL> print t_name
T_NAME
--------------------------------------------------------------------------------
SMITH
SQL> print t_sal
T_SAL
----------
900
例:IN OUT类型参数的使用
create or replace procedure comp
(num1 in out number,num2 in out number)
as
v1 number;
v2 number;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL procedure successfully completed.
SQL> exec :v2 := 5
PL/SQL procedure successfully completed.
SQL> exec comp(:v1,:v2);
SQL> print v1 v2
V1
----------
8
V2
----------
15
SQL> exec comp(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print v1 v2
V1
----------
23
V2
----------
120
可以看到in out类型的参数既作为输入参数又作为输出参数。
存储过程参数的传递方式:
按位置传递:
实参按顺序将值传给形参
EXECUTE ED_EMP(7900,:t_name,:t_sal);
EXECUTE ED_EMP(8000,'TEST2',20);
按名字传递
EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003);
混合传递
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');
过程管理
查看系统过程信息
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
使用desc procedure_name 查看存储过程的参数信息
SQL>desc ed_emp;
PROCEDURE ed_emp
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
V_NO NUMBER(4) IN
V_NAME VARCHAR2(10) OUT
V_SAL NUMBER(7,2) OUT
从dba_objects获得存储过程的信息
SQL>select owner,object_name,object_type,status from dba_objects where object_name = 'ED_EMP';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------- --------------- -------
SCOTT ED_EMP PROCEDURE VALID
SQL>select object_name,procedure_name,interface,authid from user_procedures;
OBJECT_NAME PROCEDURE_NAME INT AUTHID
-------------------- ------------------------------ --- ------------
DISPLAY_SAL NO DEFINER
ED_EMP NO DEFINER
查看存储过程的源代码
SQL>select line, text from user_source where name='ED_EMP';
LINE TEXT
---------- --------------------------------------------------------------------------------
1 PROCEDURE ed_emp
2 (
3 v_no IN emp.empno%TYPE,
4 v_name OUT emp.ename%TYPE,
5 v_sal OUT emp.sal%TYPE
6 )
7 AS
8 BEGIN
9 UPDATE emp SET sal=sal+100 WHERE empno=v_no;
10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD!');
14 END ed_emp;
查看错误信息
SHOW ERRORS
函数概述
函数通常用于返回特定的数据。其实质是一个有名字的PL/SQL块,作为一个schema对象存储于数据库,可以被反复执行。函数通常被作为一个表达式来调用或存储过程的一个参数,具有返回值。
一、建立函数的语法
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
RETURN datatype
IS | AS
[local_variable_declarations;...]
BEGIN
--actions;
RETURN expression;
END [function_name];
建立函数的几点注意事项
1.指定参数数据类型时(argument),不能指定其长度
2.函数头部必须指定return子句,函数体内至少要包含一条return语句
3.可以指定in参数,也可以指定out参数,以及in out 参数
4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'
使用函数的优点:
1.增加了代码的灵活性,可以完成一些较为复杂的任务,以及仅仅通过SQL无法完成的任务
2.可以直接将函数使用到where子句中来过滤数据
3.可以作为存储过程的参数使用,是存储过程的一种补充
建立函数
建立不带参数的函数
create or replace function get_user
return varchar2
as
v_user varchar2(20);
begin
select username into v_user from user_users;
return v_user;
end;
使用全局变量接收函数的返回值
SQL> var v1 varchar2(20);
SQL> exec :v1 := get_user;
PL/SQL procedure successfully completed.
SQL> print v1
V1
--------------------------------------
SCOTT
使用本地变量接收函数的返回值
SQL> declare user_name varchar2(20);
2 begin
3 user_name := get_user();
4 dbms_output.put_line('Current User: ' || user_name);
5 end;
6 /
Current User: SCOTT
PL/SQL procedure successfully completed.
在SQL语句中直接调用函数
SQL> select get_user from dual;
GET_USER
------------------------------------
SCOTT
使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用)
SQL> set serveroutput on;
SQL> exec dbms_output.put_line('Current user: '||get_user);
Current user: SCOTT
建立带有in 参数的函数
create or replace function raise_sal(name in varchar2)
return number
as
new_sal emp.sal%type;
begin
select sal * 1.2 into new_sal from emp
where upper(ename) = upper(name);
return new_sal;
exception
when no_data_found then
raise_application_error(-20000,'Current Employee does not exists');
end;
SQL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT';
SAL RAISE_SAL('SCOTT')
---------- ------------------
3000 3600
SQL> select sal,raise_sal('SCOTTT') from emp where ename='SCOTT';
select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'
*
ERROR at line 1:
ORA-20000: Current Employee does not exists
ORA-06512: at "SCOTT.RAISE_SAL", line 11
建立带有out参数的函数
create or replace function get_info
(name varchar2,titile out varchar2)
return varchar2
as
deptname dept.dname%type;
begin
select e.job,d.dname into titile,deptname
from emp e,dept d
where e.deptno = d.deptno
and upper(e.ename) = upper(name);
return deptname;
exception
when no_data_found then
raise_application_error(-20000,'Current Employee does not exists');
end;
/
注意对于使用out参数的函数,不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。
调用如下
SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL procedure successfully completed.
SQL> print dname job
DNAME
-------------------------------------------------
RESEARCH
JOB
-------------------------------------------------
ANALYST
SQL> select get_info('scott') from dual
2 ;
select get_info('scott') from dual
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_INFO'
建立带有in out参数的函数
create or replace function comp
(num1 number,num2 in out number)
return number
as
v_result number(6);
v_remainder number;
begin
v_result := num1 * num2;
v_remainder := mod(num1,num2);
num2 := v_remainder;
return v_result;
exception
when zero_divide then
raise_application_error(-20000,'Divsion by zero');
end;
/
SQL> var n1 number
SQL> var n2 number
SQL> exec :n2 := 10;
PL/SQL procedure successfully completed.
SQL> exec :n1 := comp(16,:n2);
PL/SQL procedure successfully completed.
SQL> print n1 n2
N1
----------
160
N2
----------
6
函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注:函数在调用的时候需要按位置指定参数,没有存储过程参数传递灵活必须具有execute 函数的权限
2.函数在SQL中调用的主要场合
由于函数必须要返回数据,因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by , start with ,order by 以及group by 子句中
d. insert 命令的values子句中
f. update 命令的set 子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数,而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN,而不能带有输出参数OUT 以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型,不能使用PL/SQL特有的类型,如boolean,table,record等
d. SQL语句中调用的函数不能包含insert ,update 和delete 语句
创建一张表tb_emp
SQL> create table tb_emp as select * from emp;
Table created.
创建一个函数,用于删除tb_emp表中指定的empno号的雇员信息,并返回其薪资
SQL> create or replace function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
11 /
Function created.
使用SQL语句调用时,收到了错误信息,在内部查询内不能完成DML操作
SQL> select del_emp(7788) from dual;
select del_emp(7788) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.DEL_EMP", line 8
使用exec执行时函数被成功执行
SQL> var n1 number
SQL> exec :n1 := del_emp(7788);
PL/SQL procedure successfully completed.
SQL> print n1
N1
----------
3000
函数使用了与存储过程相关的视图,可以从系统视图中获得函数的相关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE
查看函数的源码
SQL>select line,text from user_source where name='DEL_EMP' order by line
LINE TEXT
---------- ------------------------------------------------------------
1 function del_emp
2 (no number)
3 return number
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
10 rows selected.
查看函数的参数信息
SQL> desc del_emp;
FUNCTION del_emp RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NO NUMBER IN
函数与存储过程的差异
存储过程 函数
---------------------------------- -------------------------------
不能被作为表达式调用 只能作为表达式被调用
声明头部关键字为procedure 声明头部关键字为function
声明头部不包含return关键字来描述返回类型 头部必须包含return关键字,且PL/SQL块中至少包含一个有效的return语句
可以通过out,in out返回零个或多个值 通过return语句返回一个与头部声明中类型一致的值,也可使用in,in out返回值
SQL语句中不可调用存储过程 SQL语句可以调用函数
多用于数据库中完成特定的操作,如删除,更新,插入等DML操作 多用于特定的数据如选择等
参考至:http://blog.csdn.net/robinson_0612/article/details/6063434
http://blog.csdn.net/robinson_0612/article/details/6069531
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
存储过程、函数和触发器是PL/SQL中的核心概念,它们在数据库管理中扮演着至关重要的角色。 一、存储过程 存储过程是一组预编译的SQL语句和PL/SQL代码,可以视为数据库中的可执行对象。它们允许开发者封装一系列的...
### PL/SQL存储过程编程详解 #### 一、Oracle应用编辑方法概览 在Oracle数据库的应用开发中,存在多种编辑方法和技术,它们各有特点和适用场景。以下是对这些方法的概述: 1. **Pro*C/C++**: 这是一种C语言与...
Oracle 10g PL/SQL 是Oracle数据库系统中用于创建和管理存储过程、函数、触发器等数据库对象的编程语言。本教程旨在为初学者提供一个全面的学习平台,同时也为经验丰富的开发者提供参考资料。PL/SQL是Oracle特有的...
还会讲解如何编写存储过程、函数和触发器。 2. **控制流程语句**:这包括条件判断(IF-THEN-ELSIF-ELSE)和循环(WHILE、FOR、LOOP)。这些语句允许根据特定条件执行代码块或重复执行某段代码。 3. **异常处理**:...
6. **子程序**:PL/SQL支持函数和过程的定义,这些子程序可以在多个地方重复使用,降低了代码的冗余。 7. **包(Packages)**:包是将相关的类型、变量、常量、过程和函数组合在一起的命名实体,便于管理和重用。 ...
例如,在`jbpm.sql`文件中,可能包含了与业务流程管理(BPM)相关的PL/SQL存储过程或函数,用于定义和执行工作流任务。 此外,PL/SQL还支持块的概念,如匿名块,这使得在不创建独立的存储过程或函数的情况下,也能...
PL/SQL 的函数与存储过程练习 PL/SQL 的函数与存储过程练习
Oracle PL/SQL是一种强大的编程语言,它结合了SQL的数据处理能力与PL的程序设计特性,是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战(待续)"的主题中,我们将深入...
7. **存储过程和函数**:存储过程是一组可重用的PL/SQL代码,可以在数据库中保存并按需调用,而函数则返回一个值。它们可以接受参数,提高代码复用性和性能。 8. **触发器**:触发器是在特定数据库事件(如INSERT、...
3. **项目管理**:通过项目管理功能,开发者可以组织和管理数据库对象,如表、视图、存储过程、函数、触发器等,便于团队协作和代码版本控制。 4. **数据操作**:PL/SQL Developer允许直接在界面中查看、插入、更新...
SQL and Pl/SQL培训文档 第一天 第一章 PL/SQL 简介 安装scott/tigger用户模式 Example1 标准的PL/SQL块结构 PL/SQL块类型 Example2:子程序,函数,程序包 PL/SQL代码的执行 第二章 PL/SQL数据类型 预定义数据类型 ...
创建存储过程需要使用`CREATE OR REPLACE PROCEDURE`语句,如上所示,其中`PROC_INSERT`是存储过程的名称,`IS`关键字后跟过程体,包括变量声明和执行语句。 #### 结论 通过上述对Oracle 9i PL/SQL程序设计笔记的...
PL/SQL软件包是一组相关的过程和函数的集合,它可以用来组织和管理程序。软件包由两部分组成,说明部分和包体部分,分别用于声明公共接口和实现逻辑。 异常处理在PL/SQL中用来处理程序运行时发生的错误。PL/SQL预定...
PL/SQL是Oracle数据库系统中的一个关键组件,全称为Procedural Language/Structured Query Language,是一种结合了SQL和过程编程语言的工具,主要用于编写数据库应用程序。64位的PL/SQL开发者工具对于那些处理大数据...
2. **存储过程和函数**:这些是PL/SQL的核心组成部分,它们封装了一系列操作,可以被多次调用,提高代码复用性。书中可能包含各种示例,如自定义计算函数、处理业务逻辑的存储过程等。 3. **触发器**:当数据库中的...
2. **数据库对象管理**:该工具允许用户直接在界面中创建、修改和删除数据库中的表、视图、索引、存储过程、函数、触发器等。这些操作都配有直观的向导,使得数据库管理工作变得简单易行。 3. **调试功能**:PL/SQL...
Oracle PL/SQL是一种...书中可能包含各种类型的PL/SQL程序设计技巧,从简单的存储过程和函数到复杂的事务处理和并发控制策略。通过实例学习,你将能够更好地理解和运用这些概念,提升你在Oracle数据库开发中的技能。
- **子程序**:包括存储过程和函数。这些子程序可以被其他PL/SQL代码调用,实现代码复用和模块化。 - **包**:包是一种封装一组相关对象(如子程序、游标、类型等)的方式,有助于提高代码的可维护性和重用性。 ###...
客户端工具如SQL*Plus,以及其他高级开发环境和第三方工具(如PowerBuilder),均可以调用服务器端的PL/SQL存储过程和函数。 #### 六、总结 PL/SQL为Oracle数据库的开发人员提供了一个强大而灵活的工具集,不仅...
2. **函数和过程**:掌握如何定义和调用自定义函数和过程。 3. **游标**:了解如何使用游标处理结果集。 4. **异常处理**:学习如何编写健壮的异常处理代码。 5. **存储过程和触发器**:学习如何创建和使用存储过程...