精华内容
下载资源
问答
  • SQL存储过程使用介绍

    万次阅读 多人点赞 2017-04-25 13:54:13
    先将存储过程的相关介绍和使用方法总结如下 ;1. 存储过程的概念存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次...

    在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;

    1. 存储过程的概念

    存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;


    2. 存储过程的优缺点

    优点 :

    1. 由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;
    2. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;
    3. 由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;
    4. 可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;
    5. 代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;
    6. 增强安全性 :
      • 通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;
      • 提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;
      • SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;

    缺点 :

    1. 如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;
    2. 可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

    3. 编写简单存储过程

    创建一个存储过程

    create procedure GetUsers()
    begin 
    	select * from user; 
    end;
    

    调用存储过程

    call GetUsers();
    

    删除存储过程

    drop procedure if exists GetUsers;
    

    4. 带参数的存储过程

    MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

    下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) , 关键字 OUT 指出相应的参数用来从存储过程传出

    create procedure GetScores(
    	out minScore decimal(8,2),
    	out avgScore decimal(8,2),
    	out maxScore decimal(8,2)
    )
    begin
    	select min(score) into minScore from user;
    	select avg(score) into avgScore from user;
    	select max(score) into maxScore from user;
    end;
    

    调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所示 :

    call GetScores(@minScore, @avgScore, @maxScore);
    

    该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore , 然后即可调用显示该变量的值 :

    select @minScore, @avgScore, @maxScore;
    

    使用 IN 参数 , 输入一个用户 id , 返回该用户的名字 :

    create procedure GetNameByID(
    	in userID int,
    	out userName varchar(200)
    )
    begin
    	select name from user
    	where id = userID
    	into userName;
    end;
    

    调用存储过程 :

    call GetNameByID(1, @userName);
    select @userName;
    

    5. 复杂一点示例

    根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

    create procedure GetPriceByID(
    	in prodID int,
    	in isDisc boolean,
    	out prodPrice decimal(8,2)
    ) 
    begin
    	declare tmpPrice decimal(8,2);
    	declare prodDiscRate decimal(8,2);
    	set prodDiscRate = 0.88;
    
    	select price from products
    	where id = prodID
    	into tmpPrice;
    
    	if isDisc then
    		select tmpPrice*prodDiscRate into tmpPrice;
    	end if;
    
    	select tmpPrice into prodPrice;
    end;
    

    该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :

    call GetPriceByID(1, true, @prodPrice);
    select @prodPrice;
    

    6. DELIMITER

    MySql 的命令行客户机的语句分隔符默认为分号 ; ,而实用程序也是用 ; 作为分隔符,这会使得存储过程的 SQL 出现语法错误,使用 DELIMITER $$ 告诉命令行实用程序将 $$ 作为新的语句结束分隔符,最后再使用 DELIMITER ; 改回来,如下所示:

    DELIMITER $$ 
    create procedure getUsers() begin select * from user; 
    end $$ 
    DELIMITER ;
    
    
    DELIMITER $$
    CREATE PROCEDURE getcount()
    BEGIN
    SELECT
    *
    FROM
    contract_user;
    END $$
    DELIMITER ;
    
    CALL getcount();
    

    个人网站: Github , 欢迎点击给星

    展开全文
  • Mongodb存储过程使用

    千次阅读 2015-10-21 16:35:21
    Mongodb支持存储过程使用存储过程可以接收和输出参数跟sql中的存储过程概念类似,返回执行存储过程的状态值,可以嵌套调用。Mongodb的存储过程使用Javascript编写,并存储在db.system.js表中。我们可以自定义...

    Mongodb支持存储过程的使用,存储过程可以接收和输出参数跟sql中的存储过程概念类似,返回执行存储过程的状态值,可以嵌套调用。Mongodb的存储过程是使用Javascript编写,并存储在db.system.js表中。我们可以自定义存储过程,然后存入该集合中,具体示例如下:
    Shell代码

    > db.system.js.save({_id:"addNumbers",value:function(x,y){return x+y;}});
    WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : "addNumbers" })

    如上面所示:db.system.js.save命令接收2个参数,_id代表存储过程的名字,value代表存储过程的定义。
    我们可以对存储过程进行查询、修改和删除,查询使用find()命令:
    Shell代码

    > db.system.js.find();
    { "_id" : "addNumbers", "value" : function (x,y){return x+y;} }

    在mongodb中,如果需要执行存储过程,则需要使用db.evel(‘procedureName(param1,param2,……)’); 命令,示例如下:
    Shell代码

    > db.eval("addNumbers(3,8)")
    11

    除了这种方式之外,我们还可以直接把存储过程的逻辑放在db.eval()参数中直接调用,无需事先声明存储过程的逻辑,如下所示:
    示例1:执行存储过程查询posts集合中的文档数目
    Shell代码

    > db.eval(function(){return db.posts.count();})
    0

    示例2:把存储过程本身当做参数传递给db.eval()命令

    > db.eval(function(x,y) { return addNumbers(x, y); }, 17, 25) 
    42
    展开全文
  • MySQL存储过程使用

    千次阅读 2014-09-30 01:44:40
    MySQL存储过程, Procedure

    1. 目标

    掌握如何创建存储过程


    2. 语法

    CREATE PROCEDURE sp_name([proc_parameter])

    [characteristics...] routine_body


    3. 说明

    • CREATE PROCEDURE为用来创建存储过程的关键字;
    • sp_name为存储过程的名称;
    • proc_parameter为指定存储过程的参数列表,参数列表的形式:[IN | OUT | INOUT] param_name type
    1. IN:表示输入参数,
    2. OUT:表示输出参数,
    3. INOUT:表示既可以输入也可以输出;
    4. param_name表示参数的名称;
    5. type表示参数的类型,该类型可以是MySQL数据库中的任意类型。
    • characteristics指定存储过程的特性,可以有以下几种取值方式:
    1. LANGUAGE SQL: 说明routine_body部分由SQL语句组成,当前系统支持的语言为SQL, SQL是LANGUAGE特性的唯一值;
    2. [NOT] DETERMINISTIC: 指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出;而NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输入。如果没有指定任意一个值,默认为NOT DETERMINISTIC。
    3. {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 指明子程序使用SQL语句限制。
    4. CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;
    5. NO SQL表明子程序不包含SQL语句;
    6. READS SQL DATA说明子程序包含读写数据的语句;
    7. MODIFIES SQL DATA表明子程序包含写数据的语句;
    8. 默认情况下,系统会指定为CONTAINS SQL;
    9. SQL SECURITY { DEFINER|INVOKER}: 指明谁有权限来执行。DEFINER表示只有定义存储过程者才能执行;INVOKER表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER。
    10. COMMENT 'string': 注释信息,可以用来描述存储过程或者函数。
    • routine_body是SQL代码内容,可以用BEGIN...END来表示SQL代码的开始与结束。


    4. 示例

    1) 创建示例数据库

    create database hr;
    use hr;

    2) 创建示例用到的表并插入样例数据

    create table employees
    (
    	employee_id int(11) primary key not null auto_increment,
    	employee_name varchar(50) not null,
    	employee_sex varchar(10) default '男',
    	hire_date datetime not null default current_timestamp,
    	employee_mgr int(11),
    	employee_salary float default 3000,
    	department_id int(11)
    );

    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('David Tian','男',10,7500,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Black Xie','男',10,6600,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Moses Wang','男',10,4300,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Rena Ruan','女',10,5300,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Sunshine Ma','女',10,6500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Scott Gao','男',10,9500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Warren Si','男',10,7800,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Kaishen Yang','男',10,9500,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Simon Song','男',10,5500,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Brown Guan','男',10,5000,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Eleven Chen','女',10,3500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Cherry Zhou','女',10,5500,4);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Klause He','男',10,4500,5);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Maven Ma','男',10,4500,6);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Stephani Wang','女',10,5500,7);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Jerry Guo','男',10,8500,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Gerardo Garza','男',10,25000,8);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Derek Wu','男',10,5500,5);

    3) 查看插入的样例数据

    select * from employees;


    4) 创建计算平均工资的存储过程

    DELIMITER //
    create procedure calculate_emp_sal_avg_p()
    begin
    	select AVG(employee_salary) as average_salary
    	from employees;
    end//
    DELIMITER ;

    说明

    • DELIMETER //:该语句作用是将MySQL的结果结束符设置为//,因为MySQL默认的语句结束符为分号";",为了避免与存储过程中SQL语句的结束符相冲突,需要使用DELIMETER改变存储过程的结束符,并以"END //" 结束存储过程。
    • 存储过程定义完毕以后再使用"DELIMETER ; "恢复默认结束符。
    • DELIMETER也可以指定其它符号为结束符。


    5. 调用存储过程

    存储过程是通过CALL语句进行调用的,语法如下:

    • CALL sp_name([parameter[,...]])

    CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程,其中sp_name为存储过程名称,parameter为存储过程参数。

    CALL calculate_emp_sal_avg_p();


    6. 查看存储过程

    1) SHOW STATUS 语句查看存储过程

    语法

    • SHOW PROCEDURE STATUS [LIKE 'pattern']

    这个语句是一个MySQL的扩展,它返回子程序的特征,如数据库、名字、类型、创建者及创建日期和修改日期。

    LIKE语句表示匹配存储过程的名称;



    2) SHOW CREATE 语句查看存储过程定义

    语法

    • SHOW CREATE PROCEDURE sp_name

    这个语句是一个MySQL的扩展,类似于SHOW CREATE TABLE,它返回一个可用来重新创建已命名存储过程的确切字符串。


    3) 从information_schema.Routines表中查看存储过程

    语法

    SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';

    • ROUTINE_NAME字段中存储的是存储过程或者函数的名称;
    • sp_name指存储过程或函数名称;


    如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方,请给予指正,非常感谢!

    联系方式:david.louis.tian@outlook.com

    版权@:转载请标明出处!


    展开全文
  • MySQL存储过程使用表名做参数

    万次阅读 2010-08-17 15:35:00
    MySQL存储过程使用表名做参数动态创建表的例子。

    MySQL存储过程使用表名做参数


    原创文章,chszs版权所有!
    如要转发,请联系chszs!
    盗贴行为将受起诉!

    MySQL存储过程使用表名做参数动态创建表的例子。

    一同学向我请教在MySQL的存储过程中,如何使用表名做参数动态创建表。

    这个问题在MySQL 5.0以前非常麻烦,但是在MySQL 5.0.13版之后,由于引入了PREPARE语句,一切变得简单了。

    此问题在网上搜索的帖子一般都是错误的!

    例子如下(已验证):

    展开全文
  • MySQL存储过程使用游标循环数据列表

    千次阅读 2019-02-26 10:04:43
    本篇文章主要讲解,我的一个案例,使用存储过程和游标循环数据列表,并且做一些操作,比如保存一些数据,修改一些数据; 1、需求? MySQL使用存储过程循环数据列表? 2、先描述下MySQL有哪些循环的语法 while...
  • Mysql存储过程使用多个游标的处理

    千次阅读 2016-04-14 11:22:21
    Mysql存储过程使用多个游标的处理定义数据库表DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `score` varchar(255) ...
  • Oracle 存储过程 使用示例

    万次阅读 2011-01-17 16:22:00
     在这里说一条使用存储过程很重要的理由:存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 Oracle 存储过程定义...
  • oracle 存储过程 关键字: oracle 存储过程 存储过程创建语法: create or replace procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); 变量2 类型(值范围); Begin Select...
  • Mysql存储过程使用随机数

    千次阅读 2017-04-06 11:35:10
    主要是使用RAND() ,例如 RAND()*100 表示(0~100)之间的随机 话不多说,直接上代码: DELIMITER ;; drop procedure if exists test_insert; create procedure test_insert() begin DECLARE i INT DEFAULT ...
  • DB2存储过程使用游标

    万次阅读 2011-12-21 12:57:37
    目前在接触DB2,在此将DB2中使用游标和if else ,while等收集起来进行汇总。   SET SCHEMA = "AUTEK"; CREATE PROCEDURE "AUTEK"."SP_ADDRESS" ( OUT "V_MESSAGE" VARCHAR(50) ) SPECIFIC "SQL...
  • 原因:是由于变量定义与查询表字段名一样,导致赋值不成功 如上图,查询结果为空, ...字段名,以此区别表字段与存储过程变量,如 select c.parkname into parkname from kt_audit_carport c where c.id=id;  ...
  • pgsql 常见的存储过程使用方法

    千次阅读 2019-01-18 11:25:39
    -- 这是使用pl/pgsql语言的风格,   CREATE OR REPLACE FUNCTION function2 () RETURNS setof table1 AS  $body$  DECLARE  result record;  BEGIN  for result in select * from table1 limit 10 loop  ...
  • mysql中存储过程 使用if else 判断

    千次阅读 2019-09-04 09:12:41
    BEGIN declare phonenum VARCHAR(32) ; SELECT phonenumber INTO phonenum FROM A WHERE phonenumber = `p_phonenumber` ; if phonenum is NULL then INSERT INTO A (phonenumber ,isselect) VALUES (`p....
  • 已知MySQL的存储过程使用事物后,执行commit操作时会返回0 row(s) affected, 但是增删改已经正确执行了。 那么实际应用中一定要这个影响行数确认执行结果怎么办? 此时需要手动实现影响行数计数,然后在commit...
  • with as在存储过程使用

    千次阅读 2015-04-17 09:40:50
    create or replace procedure strToken is  int var_count:=0;  int var_count1:=0; begin  with t as (select code from company where code='1111') ... SELECT (select count(1) from company_extend w
  • 在sqlplus中这两种方法都可以使用:  exec pro_name(参数1..);  call pro_name(参数1..)...2. 存储过程没有参数时,exec可以直接跟过程名(可以省略()),但call则必须带上().  Sql代码  SQL> --创建过...
  • 存储过程使用shell脚本执行sql文件

    千次阅读 2012-11-11 21:43:13
    今天接到的需求是把所有表的创建写...思路就是在存储过程里面使用shell脚本执行sql脚本文件。 通过MSDN得到执行shell的函数:xp_cmdshell。 下面是完整的脚本: CREATE PROCEDURE CreatTable ( @UserName var
  • 查询哪些存储过程使用了某个表

    千次阅读 2017-07-24 15:09:00
    查出的结果 p指的是存储过程 excelIntoSql P excelIntoSql_beifen P GetProductNextCD P GetSellOrderProductType P SP_GetBomDetail_ByProjectForProcess P sp_GetBomListfb P sp_GetBomListfbAL...
  • 由于项目需要,需在存储过程中连接远程数据库完成数据的抽取工作。做个记录^^   在此文件下 G:/oracle_test/product/10.2.0/db_1/NETWORK/ADMIN/tnsnames.ora 添加: link = ...
  • 一、格式类似于 ...CREATE PROCEDURE YourProcedure ... UPDATE A SET A.names = B.names FROM 表1...参考资料: SQL SERVER中 存储过程使用事务与捕获异常 http://www.studyofnet.com/news/1238.html
  • 存储过程使用游标变量返回结果集

    千次阅读 2013-01-26 23:22:37
    1)登录SQLPLUS,用户为SCOTT,利用该用户提供的表EMP ...⑤可以使用FETCH一次获取一条记录,也可以使用FETCH BULK COLLECT INTO 一次获取一条或者多条放到一个或者多个集合类型中。 ⑥不要忘记关闭游标。
  • 在MySQL存储过程中,可以使用SELECT …INTO语句对变量进行赋值,该语句在数据库中进行查询,并将得到的结果赋值给变量。SELECT …INTO语句的语法格式如下: SELECT col_name[,...] INTO var_name[,...] table_expr ...
  • 一切看图吧,懒得解释,解释了又不如图来的直观。 需求: 首先有四个表,如右图: ... 哎,自己研究了老半天,本身好久没有操作数据库了。其实很简单啦,就是个左外连接嘛,哈哈。...先说一下这个左外连接: left ...
  • CREATE OR REPLACE  PROCEDURE PRINTROW AS --声明行变量及游标 myrow EMP%rowtype; cursor mycur is select * from EMP; BEGIN  --打开游标  OPEN mycur; ... FETCH mycur INT
  • 本示例通过 while...end while 循环控制游标来实现插入表记录。DROP PROCEDURE IF exists pro_initCategoryForTradingEntity;create procedure pro_initCategoryForTradingEntity(tradingEntityId int)begin ...
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...
  • 使用sql语句定义一个存储过程挺简单的,参考这一篇博文https://blog.csdn.net/qq_44973159/article/details/91491985 create procedure ccgc as select * from t_student 而使用java并不 那么简单了,,,如下 ...
  • 存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字给出参数来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好 的数据库应用程序都应该用到存储过程。 ...
  • mybatis使用存储过程

    千次阅读 2018-11-04 21:31:36
    1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行 增删改查 时)...
  • 为此,需要先找出哪些存储过程使用到了这些表,然后修改。那么问题来了,mysql数据库如何查看哪些存储过程使用了这些数据表呢? 二、mysql数据库存储过程信息查看 1、show 命令 1) SHOW STATUS查看存储过程...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,413,560
精华内容 965,424
关键字:

存储过程怎么使用