1.存储过程概念优点
1.概念
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。
简单的说就是专门干一件事一段sql语句。
可以由数据库自己去调用,也可以由java程序去调用。
2.优点
使用存储过程或函数的优点:
1、可重复调用;
2、由于其预编译性,所以执行效率高;
3、由于其封装性,安全性高
4、可对数据库进行复杂操作,包括调用其他存储过程或函数等。
2. 存储过程语法格式
创建存储过程的语法如下所示:
CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name [ ( [{IN | OUT | IN OUT}] datatype [default|:=value], ...) ] {IS | AS} [description part 说明部分] BEGIN SQL STATEMENT 语句序列 [EXCEPTION 例外处理] END [procedure _Name 过程名];
过程参数说明:
- OR REPLACE 是一个可选的关键字,建议用户使用此关键字。如果过程已经存在,该关键字将重新创建过程,这样就不必删除和重新创建过程。
- 关键字IS 和 AS均可,它们本身没有区别。IS/AS后面是一个完整的PL/SQL块,可以定义局部变量,但不能以DECLARE开始。局部变量在过程内部存放值。
- 形式参数可以有三种模式:IN、OUT、IN OUT。如果没有为形式参数指定模式,那么默认的模式是IN。
3.创建/调用无参存储过程
例1:向产品表中插入一条新信息
CREATE PROCEDURE Insertpro IS BEGIN INSERT INTO products(productid,productname) VALUES( 20,‘aa'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line(‘产品编号已存在'); WHEN OTHERS THEN dbms_output.put_line('发生其他错误'); END;
注意:
- 不论编译是否成功,创建过程/函数命令CREATE PROCEDURE或CREATE FUNCTION都将自动把其源代码存入数据库中,而编译代码只有在编译成功后才能存入数据库中。
- 只有编译代码被存入到数据库的存储过程和函数才能被调用。
4.创建/调用带参存储过程
IN、OUT、IN OUT三种模式的描述
模 式 |
描 述 |
IN参数(默认模式) (输入参数) |
用来从调用环境中向存储过程传递值,不能给IN参数赋值,给此参数传递的值可以是常量、有值的变量、表达式等。 |
OUT参数(输出参数) |
用来从过程中返回值给调用者,不能将此参数的值赋给另一个变量,不能是常量或 表达式。在过程体内,必须给OUT参数赋值。 |
IN OUT参数 (输入输出参数) |
既可以从调用者向过程中传递值,执行过程后还可返回可能改变了的值给调用者。 |
例2:创建带输入参数的存储过程,向产品表插入一个新的产品信息,使用异常设置错误信息(供应商编号或类别编号 不存在).
CREATE OR REPLACE PROCEDURE Para_InsertProd (prodid number,prodname nvarchar2,suppid number,cateid number default 1) IS e_noSuppCate EXCEPTION; Pragma EXCEPTION_INIT(e_noSuppCate,-2291); BEGIN INSERT INTO products(productid,productname, supplierid, categoryid) VALUES(prodid, prodname, suppid, cateid); commit; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line ('产品编号已存在'); WHEN e_noSuppCate THEN dbms_output.put_line(‘供应商编号或类别编号不存在'); END;
带输入参数存储过程的调用格式:
1) 位置表示法
EXEC[UTE] procedure_name(参数值1,参数值2 …);
2) 名称表示法
EXEC[UTE] procedure_name(参数名1=>参数值1,参数名2=>参数值2 …);
3) 混合表示法
当用户使用名称表示法后,后续的参数必须使用名称表示法。
例3:创建带输出参数的存储过程,用于查询orderdetails表中某产品的总销量和总销售金额,并将其值返回,如记录不存在产生异常即设置错误信息(该产品不存在).
CREATE OR REPLACE PROCEDURE Paraout_orderDetail (prodid in number, t_quant out orderdetails.quantity%type, t_uprice out orderdetails.unitprice%type) IS BEGIN SELECT sum(quantity),sum(quantity*unitprice) INTO t_quant, t_uprice FROM orderdetails WHERE productid=prodid Group by productid; EXCEPTION WHEN no_data_found THEN Dbms_output.put_line(‘该产品信息不存在’); END;
带输出参数存储过程的调用格式:
SQL PLUS中绑定输出参数值
variable 输出参数变量1 数据类型,输出参数变量2 数据类型 …;
调用存储过程
EXEC[UTE] procedure_name(参数值1,…参数名n,:绑定变量1, :绑定变量2….);
1) SQL PLUS命令行中执行带输出参数的存储过程
SQL>variable qt number;
SQL>variable tprice number;
SQL>EXEC Paraout_orderDetail(1,:qt,:tprice);
SQL>print qt;
SQL>print tprice;
2)SQL PLUS中在程序块中调用存储过程
DECLARE qt number; tprice number; BEGIN Paraout_orderDetail(1,qt,tprice); dbms_output.put_line(qt); dbms_output.put_line(tprice); END;
例4:创建带输入/输出参数的存储过程, 用于对两个数进行交换.
CREATE OR REPLACE PROCEDURE swap(p_num1 IN OUT number, p_num2 IN OUT number) IS temp number; BEGIN temp:=p_num1 ; p_num1:=p_num2; p_num2:=temp; END; --过程内的局部变量只能在过程内使用 --建立PL/SQL程序块调用存储过程: DECLARE V_max number:=20; V_min number:=29; BEGIN if v_max< V_min then swap(v_max, V_min); End if; dbms_output.put_line(‘v_max:’||v_max); dbms_output.put_line(‘v_min:’||v_min); END;
注意:
- 如果形式参数是IN模式的参数,实际参数可以是一个具体的值或一个有值的变量;
- 如果形式参数是OUT模式的参数,实际参数必须是一个变量,当调用过程后,此变量就被赋值了。可以输出此变量的值来测试过程执行的结果。
- 如果形式参数是IN OUT模式的参数,则实际参数必须是一个预先已经赋值的变量。执行完过程后,该变量被重新赋值,可以输出此变量的值来测试过程执行结果