1、数据库存储过程与存储函数
存储过程是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它，叫做存储过程、存储函数。
2、存储过程与存储函数的区别
- 存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
- 返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out,inout)。
- 调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。
- 参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，IN、out和INOUT：
a. in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
b. out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
c. inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。
3、存储过程与存储函数的优缺点
- 存储过程只在创建时进行编译，以后每次执行存储过程都不需要再重新编译；而一般的SQL语句每执行一次就需要编译一次，所以使用存储过程可以提高数据库执行速度。
- 当地数据库进行复杂操作(如对多个表进行update、insert、delete、query)时，可以将此复杂操作用存储过程封装起来与数据库提供的事务处理结合起来一起使用。
- 存储过程可以重复使用可以减少数据库开发人员的工作量。
- 安全性高，可以设定只有某指定用户才具有对指定的存储过程使用的权限。
MySQL
1、存储过程
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...]
BEGIN
过程体
END
delimiter //
create procedure param_pro(in id int , out num int, inout p_sal int)
begin
delete from emp where empno = id ;
select max(sal) from emp into num;
select count(*) into p_sal from emp where sal >P_sal ;
end //
delimiter ;调用
set @p_sal = 1250 ;
call param_pro(7369 , @num , @p_sal);
select @num , @p_sal ;2、存储函数
CREATE FUNCTION 函数名([ 参数名 数据类型 [, …]]) RETURNS返回类型
BEGIN
过程体
END
delimiter //
create function fun1(id int) returns int
begin
return (select sal from emp where empno=id);
end //
delimiter ;调用：
select fun1(7698);3、修改
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]其中，sp_name表示存储过程或函数的名称，characteristic参数指定存储过程或函数的特性，可能取值有：
CONTAINS SQL:子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL：子程序不包含SQL语句。
READS SQL DATA：子程序包含读数据的语句。
MODIFIES SQL DATA：子程序包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER}：指明谁有权限执行。
DEFINER：只有定义者自己才能1执行。
INVOKER：调用者可以执行。
COMMENT ‘string’ ：注释。
ALTER PROCEDURE param_pro
MODIFIES SQL DATA
SQL SECURITY INVOKER ;oracle
1、存储过程
CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_name in|out|in out parameter_type, [, ...])]
{IS | AS}
[columnName1 tableName.columnName1%type;
columnName2 tableName.columnName2%type;
...
]
BEGIN
< procedure_body >
END;
-- 语法说明：
-- ① 用[]包含的内容为可有可无，根据实际情况而定；
-- ② procedure_name：存储过程名称；
-- ③ parameter_name：参数名称；
-- ④ 参数模式：
-- in: 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变；即，可以传入参数；
-- out：该模式定义的参数只能在过程体内部赋值，标识该参数可以将某个值传递回调用它的过程；即，可以返回值；
-- in out：表示该参数可以向过程中传递值，也可以将某个值传出去；即，既可以传入参数，也可以返回值；
-- ⑤ parameter_type：参数数据类型；
-- ⑥ is、as：在存储过程中，两者没有任何区别；但是在视图中只能用as，在游标中只能用is；
-- ⑦ procedure_body：PL/SQL子程序体；即该存储过程要执行的操作内容；
-- ⑧ 创建存储过程时，可以在is或者as后面添加对类型或变量的说明；备注，创建存储过程时需要注意的一些细节问题：
① 定义无参存储过程时，过程名称后面不能加括号，否则将会创建失败；
② 无参存储过程调用时，可以加括号也可不加，可通过PL/SQL调用，也可以通过execute关键字调用；
③ 有参存储过程的参数类型一定不能指定精度，否则将会报错，或创建失败；
④ 利用“表名.字段名%TYPE” 可以获取到表中字段的具体类型，以便声明变量时设置其类型；
⑤ 当需要通过：DBMS_OUTPUT.PUT_LINE('... ...'); 向SQL Developer控制台输出脚本内容时，注意需要先开启serveroutput，开启命令为如下：
set serveroutput on;实例，创建员工信息表存储过程
create or replace procedure addEmployee(eNo in out number, uName in out varchar2, dNo in out number, sal in out number, com in out number)
as
empNo emp.empNo%type;
usernName emp.username%type;
deptNo emp.deptno%type;
salary emp.salary%type;
comm emp.comm%type;
begin
insert into emp(empNo, username, deptNo, salary, comm)values(eNo, uName, dNo, sal, com);
end;调用
declare
empNo emp.empno%type := 7777;
username emp.username%type := 'Hellen';
deptNo emp.deptno%type := 10;
salary emp.salary%type := 3800;
comm emp.comm%type := 700;
begin
addEmployee(empNo, username, deptno, salary, comm);
end;无参存储过程
create or replace procedure firstProcedure as
begin
执行语句
DBMS_OUTPUT.PUT_LINE('你好，我是第一个存储过程...');
end;调用
-- 方式一：
begin
firstprocedure();
end;
-- 方式二：
begin
firstprocedure;
end;
存储函数
CREATE [OR REPLACE] FUNCTION function_name[(parameter_name in parameter_type, [, ...])]
RETURN returnValType
{IS | AS}
[variable1 type;
variable2 type;
...
]
BEGIN
< procedure_body >
END;
-- 语法说明：
-- ① 用[]包含的内容为可有可无，根据实际情况而定；
-- ② function_name：存储函数名称；
-- ③ parameter_name：参数名称；
-- ④ in: 参数模式，存储函数只有in模式；
-- ⑤ parameter_type：参数数据类型；
-- ⑥ returnValType：函数返回值数据类型；
-- ⑦ is、as：在存储过程中，两者没有任何区别；但是在视图中只能用as，在游标中只能用is；
-- ⑧ variable、type：分别用于声明函数在执行过程中所需要的变量名称和数据类型；
-- ⑨ procedure_body：PL/SQL子程序体；即该存储函数要执行的操作内容；
-- ⑩ 创建存储过程时，可以在is或者as后面添加对类型或变量的说明；备注，使用存储函数需要注意的一些细节问题：
① 存储函数必须包含一个return子句，用于指定函数要返回的数据类型；
② 函数定义的时候，无参函数名后面一定不能加括号；否则将创建失败；
③ 函数调用时，无参函数方法名后面可以不加括号，但是有参函数必须加括号、并传入参数；
实例，创建员工加薪存储函数
create or replace FUNCTION addSalary(eNo in number, addVal in number)
RETURN number
AS
pNo number; -- 定义变量保存员工编号
pName varchar2(30); -- 定义变量保存员工用户名
pDeptNo number; -- 定义变量保存员工部门编号
pSal number; -- 定义变量保存员工的工资
pComm number; -- 定义变量保存员工的奖金
pTotal number; -- 定义变量保存员工的总收入
newSalary number; -- 定义变量保存加薪后的工资
newComm number; -- 定义变量保存加薪后的奖金
BEGIN
SELECT empNo, username, deptNo, salary, comm INTO pNo, pName, pDeptNo, pSal, pComm FROM EMP WHERE empNo=eNo;
pTotal := (pSal+pComm)*12;
DBMS_OUTPUT.PUT_LINE('员工'||pName||'：初始工资为='||pSal||'；初始奖金为='||pComm||'；初始年薪为='||pTotal);
newSalary := pSal+pSal*addVal;
newComm := newSalary*0.12;
UPDATE EMP SET username=pName, deptNo=pDeptNo, salary=newSalary, comm=newComm WHERE empNo=pNo;
RETURN newSalary;
END;调用
declare
a number; -- 定义变量，接受存储函数返回值
begin
a := addSalary(1111, 0.3); -- 调用存储函数
DBMS_OUTPUT.put_line(a); -- 打印存储函数返回值
end;常用案例
增加字段
-- 创建存储过程
DROP PROCEDURE IF EXISTS ALTER_TABLE_ADD_KEY;
DELIMITER $$
CREATE PROCEDURE ALTER_TABLE_ADD_KEY() BEGIN
IF NOT EXISTS (SELECT * FROM INFOMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename' AND TABLE_SCHEMA = 'dbname' AND COLUMN_NAME='KEY') THEN
ALTER TABLE dbname.tablename ADD KEY INT(16) NOT NULL DEFAULT 0;
END IF;
END $$
DELIMITER;
-- 调用存储过程
CALL ALTER_TABLE_ADD_KEY();
DROP PROCEDURE ALTER_TABLE_ADD_KEY;添加主键
-- 添加主键
DROP PROCEDURE IF EXISTS ALTER_TABLE_ADD_PRIMARY;
DELIMITER $$
CREATE PROCEDURE ALTER_TABLE_ADD_PRIMARY() BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'tablename' AND TABLE_SCHEMA = 'dbname' AND INDEX_NAME='PRIMARY') THEN
ALTER TABLE dbname.tablename ADD PRIMARY KEY(KEY1, KEY2, ...);
END IF;
END $$
DELIMITER;
CALL ALTER_TABLE_ADD_PRIMARY();
DROP PROCEDURE ALTER_TABLE_ADD_PRIMARY;
-- 新增主键
ALTER TABLE DBNAME.TABLENAME DROP PRIMARY KEY, ADD PRIMARY KEY(KEY1, KEY2, ...)
2073
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
