一、存储过程的概念
存储过程(Stored Procedure )是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。
通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库
应用程序都应该用到存储过程。
二、存储过程的优点
(1)性能好
(2)可重用PL/SQL
(3)安全性好
(4)解耦应用程序与物理表结构。
(5)模块化,维护性好。
三、存储过程分类
1)系统存储过程
就是由oracle预先提供的一组完成特定功能的存储过程,安装完oracle就有了。
2)自定义存储过程
就是存在oracle数据库里由一组plsql语句组成的自定义过程(procedure)。
它可以供其它oracle自定义存储过程、自定义函数和job调用或者由客户端程序调用。
四、存储过程的语法
OUT 表示输出参数,可以理解为按引用传递方式。可以作为存储过程的输出结果,供外部调用者使用。
IN OUT 即可作输入参数,也可作输出参数。
参数的数据类型只需要指明类型名即可,不需要指定宽度。
案例1: 删除指定员工记录
存储过程(Stored Procedure )是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。
通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库
应用程序都应该用到存储过程。
二、存储过程的优点
(1)性能好
(2)可重用PL/SQL
(3)安全性好
(4)解耦应用程序与物理表结构。
(5)模块化,维护性好。
三、存储过程分类
1)系统存储过程
就是由oracle预先提供的一组完成特定功能的存储过程,安装完oracle就有了。
2)自定义存储过程
就是存在oracle数据库里由一组plsql语句组成的自定义过程(procedure)。
它可以供其它oracle自定义存储过程、自定义函数和job调用或者由客户端程序调用。
四、存储过程的语法
<pre name="code" class="plain">CREATE [<span style="color:#ff99ff;">OR REPLACE</span>] PROCEDURE procedure_name
([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
[arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
......
[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen])
[ AUTHID DEFINER |<span style="color:#6666cc;"> CURRENT_USER </span>]
{ IS | AS }
<声明部分>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END procedure_name;
说明:
存储过程名定义:包括存储过程名和参数列表。参数名和参数类型。参数名不能重复, 参数传递方式:IN, OUT, IN OUT
IN 表示输入参数,按值传递方式。OUT 表示输出参数,可以理解为按引用传递方式。可以作为存储过程的输出结果,供外部调用者使用。
IN OUT 即可作输入参数,也可作输出参数。
参数的数据类型只需要指明类型名即可,不需要指定宽度。
五、存储过程的使用
模拟java写两数求和的方法(有4种写法)
<strong>--(1)无参无返回</strong>
public void add1(){
int a=10;
int b=20;
int sum =a+b;
System.out.println(sum);
}
--调用
add1();
<pre name="code" class="plain">create or replace procedure add1
is
--声明变量
a number default 110;
b number default 20;
sums number;
begin
sums:=a+b;
dbms_output.put_line(sums);
end;
--调用存储过程
<span style="color:#ff0000;">--(1)在SQL>提示符下调用过程,execute过程名(参数……)</span>
set serverout on;
execute add1();
<span style="color:#ff0000;">--(2)在PL/SQL块中调用过程,过程名(参数……)</span>
begin
add1();
end;
<strong>--(2)有参无返回</strong>
public void add2(int a,int b){
int sum =a+b;
<pre name="code" class="plain"> System.out.println<span style="font-family: Arial, Helvetica, sans-serif;">(sum);</span>
}--调用add2();
<strong>--in 输入参数,默认值</strong>
create or replace procedure add2(a number,b number)
is
sums number;
begin
sums:=a+b;
dbms_output.put_line(sums);
end;
<pre name="code" class="plain"><span style="color: rgb(255, 0, 0);">--在PL/SQL块中调用过程,过程名(参数……)</span>
begin add2(160,40);end;
<strong>--(3)无参有返回</strong>
public int add3(){
int a =10;
int b=20;
int sum =a+b;
return sum;
}
--调用
int sums=add3();
<strong>--out输出参数</strong>
create or replace procedure add3(sums out number)
is
--声明变量
a number default 190;
b number default 20;
begin
sums:=a+b;
end;
--调用
declare
sums number; --声明变量
begin
add3(sums);
dbms_output.put_line(sums);
end;
<strong>--(4)有参有返回</strong>
public int add4(int a,int b){
return a+b;
}
--调用
int sums=add4(10,20);
<strong>--in,out</strong>
create or replace procedure add4(a in number,b in number,sums out number)
is
begin
sums:=a+b;
end;
<span style="color:#3366ff;"><strong>--1)按位置传递参数</strong></span>
--调用
declare
sums number; --声明变量
begin
add4(10,50,sums);
dbms_output.put_line(sums);
end;
<pre name="code" class="plain"><span style="color:#6633ff;"><strong>--2)按名称传递参数 (必须是存储过程的变量名)</strong></span>
declare
sums1 number; --声明变量
begin
add4(a =>150,b =>50,sums =>sums1);
dbms_output.put_line(sums1);
end;
<span style="color:#6666cc;"><strong>--3)混合方式传递参数</strong></span>
declare
sums1 number; --声明变量
begin
add4(10,350,sums =>sums1);
dbms_output.put_line(sums1);
end;
--<strong>IN OUT <span style="color: rgb(51, 51, 51); font-family: 宋体; font-size: 14.399999618530273px; line-height: 20.799999237060547px;">即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。</span></strong>
如: result=2*2*2
--创建存储过程
create or replace procedure pro(a in out number)
is
b number default 3;
begin
a:=power(a,b);
end;
--调用
declare
a number:=&no;
begin
pro(a);
dbms_output.put_line(a);
end;
案例1: 删除指定员工记录
IN输入参数(默认值可以省略不写)
create or replace procedure delEmps(v_empno emps.empno%type)
is
--1)声明变量
ex exception;
begin
--执行语句
delete from emps where empno=v_empno;
--判断
if sql%notfound then
raise ex; --2)抛出异常
end if;
--可以删除
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!');
--3)处理异常
exception
when ex then
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
when others then
DBMS_OUTPUT.PUT_LINE(sqlcode||'---'||sqlerrm);
end;
--调用
begin
delemps(1201);
end;
案例2:计算指定部门的工资总和,并统计其中的职工数量。
IN输入参数(默认值可以省略不写),out输出参数
CREATE OR REPLACE PROCEDURE proc_demo
(
dept_no NUMBER,
sal_sum OUT NUMBER,
emp_count OUT NUMBER
)
IS
BEGIN
SELECT SUM(sal), COUNT(1) INTO sal_sum, emp_count
FROM emps WHERE deptno = dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
--PL/SQL执行调用
DECLARE
v_no number:=&no;
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和:'||v_sum||',人数:'||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num,dept_no => v_no);
DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和:'||v_sum||',人数:'||v_num);
END;
用命令窗口/CMD下如何调用?
var sums number; --<span style="font-family: Arial, Helvetica, sans-serif;">工资总和</span>
var counts number; --人数
<span style="font-size:14px;"> execute scott.proc_demo(10,:sums,:counts); --(必须写<span style="color:#ff0000;"><strong>:变量名)</strong></span></span>
--输出结果
print sums
print counts<strong>
</strong>
案例3:查询所有的数据
--静态游标---》不能达到客户端;(应用程序交互)
create or replace procedure allEmps
is
--声明游标
cursor c1 is select * from emps;
begin
for v_row in c1 loop
dbms_output.put_line(v_row.ename||'---->'||v_row.job||'--->'||v_row.sal);
end loop;
end;
--调用
begin
allemps();
end;
注意:另一个用户WY如何调用SCOTT用户下的存储过程,必须授予权限方可。
语法:GRANT EXECUTE ON 存储过程名 TO 用户名
grant execute on ( 过程, 函数, 程序包或程序包体) to public;
在命令窗口调用:
SQL>conn wy/wyaaa;
SQL> set serverout on;
SQL>execute scott.add2(10,80);
六、删除存储过程
语法: DROP
PROCEDURE
[
user
.]Procudure_name;
drop procedure scott.add1;