Mysql 存储过程研发规范

Mysql 存储过程研发规范
Mysql 存储过程研发规范

Mysql存储过程研发规范

前言

mysql数据库的错误机制比较弱,复杂的sql语句容易报错,且执行效率低下,业务逻辑不清晰,在用存储过程编写业务逻辑的时候,往往很难定位问题,以及错误sql,给研发人员带来开发上障碍。往往排除问题,需要花费比较长的时间,后期存储过程的维护交接上会存在较大的障碍。因此在做存储过程编写的时候,增加一种异常捕捉的机制,断点续跑能力,快速的错误定位,下面章节简要介绍。

存储过程编写规范

1.过程的逻辑代码全部使用动态SQL。

2.使用创建临时表的方式,方便后续数据问题定位。

3.尽量不用复杂逻辑SQL关联关系,复杂的sql可以拆分成多步骤的逻辑sql。

4.过程最后一步删除临时表。

存储过程监控函数

MAKE_ETL_LOG(

IN IN_DAY integer, #当日的日志

IN IN_TCH_MOD integer, #存储过程模板实例ID

IN IN_PROC_NAME VARCHAR(64), #存储过程名称

IN IN_REMARK VARCHAR(128), #存储过程功能描述

IN IN_STAFFS VARCHAR(20), #开发员工号ID

IN IN_EXEC_TYPE CHAR, #执行类型

IN IN_STATE_DATE datetime) #存储过程的执行开始时间

函数功能说明:应用与开始执行存储过程的执行日志记录。

UPDATE_ETL_LOG(

IN IN_DAY integer, #当日的日志

IN IN_TCH_MOD integer, #存储过程模板实例ID

IN IN_RESULT integer, #存储过程执行结果1 :成功; -N 失败(N 存储过程的步骤)

IN IN_ERR_SQL VARCHAR(4000), #错误的sql语句

IN IN_STATE_DATE datetime) #记录时间

函数功能说明:记录存储过程执行完成是的状态时间。

动态sql执行函数

P_util_execute(

in v_sqlvarchar(4000), # 动态sql语句。

out out_sqlvarchar(4000), # 返回错误sql语句

out _err1 int ) # 异常状态,报错后异常赋值到父过程。

函数说明:执行动态SQL的函数,配合存储过程的模板使用,。

存储过程业务逻辑编写模板

P_MODEL_TEST (

IN V_TCH_MOD integer # 存储过程的模板实例ID

)

函数说明:存储过程的模板,已经集成了日志信息。建立新的存储过程,将里面的内容拷贝到新的存储过程里。

模板结构说明:

BEGIN

#Routine body goes here...

DECLARE _var,_err int default 0;

DECLARE V_STEP integer ;

DECLARE V_contint default 0;

DECLARE V_RESULT integer;

DECLARE V_beforestep_tablenamevarchar(50) ;

DECLARE V_tablenamevarchar(50) ;

DECLARE v_sql VARCHAR (4000) ;

-- DECLARE v_out_sql VARCHAR (4000) ;

DECLARE V_report_typevarchar(10) ;

-- V_TCH_MOD integer := 2132; -- 环节模板号

DECLARE V_PROC_NAME varchar(50) default '222222222';

DECLARE V_DAY varchar(8) default date_format(CURDATE(), '%Y%m%d');

DECLARE v_b_dayvarchar(8) default date_format(CURDATE()-1, '%Y%m%d');

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND set _err=1;

-- 写日志

SELECT count(RESULT),result INTO V_cont, V_STEP

FROM td_s_exe_log

WHERE TCH_MOD = V_TCH_MOD

AND DAY = V_DAY;

IF V_cont=0 then

set V_STEP=0;

END if;

IF V_STEP = 1 THEN

set V_STEP=100000; # 不能return 只能赋值方式,让程序不执行。

ELSE

set V_STEP = -1 * V_STEP; -- 从出错的第N步再执行

END IF;

IF _err=1 then

set V_STEP = 0; -- 从来没有执行过

END if;

CALL MAKE_ETL_LOG((cast(V_DAY as BINARY)), -- 日期

V_TCH_MOD, -- 环节模板号

V_PROC_NAME, -- 存储过程名,

'good', -- 备注

NULL, -- 环节管理员

'1', -- 执行类型(1:数据加载环节2:审核校验环节)

now() -- 状态时间

);

IF _err<>1 THEN -- 判断是否出现异常,无异常则执行

SET V_RESULT = 1;

IF V_RESULT >= V_STEP THEN

-- 代码业务逻辑编写地方。

setv_sql='SELECT 88888; ';

callP_util_execute(v_sql,@v_out_vql,_err);

END IF;

END IF;

IF _err<>1 THEN -- 判断是否出现异常,无异常则执行

SET V_RESULT = 2;

IF V_RESULT >= V_STEP THEN

-- 代码业务逻辑编写地方。

select sleep(3);

END IF;

END IF;

IF _err<>1 THEN

-- 修改程执行成功状态

CALL UPDATE_ETL_LOG((cast(V_DAY as BINARY)), V_TCH_MOD, 1, NULL, now());

COMMIT;

END IF;

IF _err=1 THEN

ROLLBACK;

call UPDATE_ETL_LOG((cast(V_DAY as BINARY)),

V_TCH_MOD,

-1 * V_RESULT,

@v_out_vql, # 报错的sql脚本。

now());

END IF;

END

使用说明:红色部分是业务SQL逻辑代码的编写地方。所有的逻辑需要包裹在里面。可增加逻辑步奏。增加方式拷贝以下的脚本,SET V_RESULT = 1;代办逻辑脚本在存储过程中的第一步。

IF _err<>1 THEN -- 判断是否出现异常,无异常则执行

SET V_RESULT = 1;

IF V_RESULT >= V_STEP THEN

-- 代码业务逻辑编写地方。

setv_sql='SELECT 88888; ';

callP_util_execute(v_sql,@v_out_vql,_err);

END IF;

END IF;

日志表

说明:当result=1 表示:上图标识当日的所有的存储过程都正常执行完毕。

当Result=-4 表示:在存储过程的第四步发生执行SQL异常。

当ERRMSG 表示:单result<>1 时候,记录错误的sql语句。研发人员拿出sql定位错误。

注意,过程重新跑的时候,只要当天的模板号不变,存储过程将会中报错的步奏开始执行,直到执行完成。如果当日的过程已经执行完了。重新执行的时候存储过程的业务逻辑代码不会被执行。

代码附件:

存储过程的典型例子

可能有不少朋友使用SQL SERVER做开发也已经有段日子,但还没有或者很少在项目中使用存储过程,或许有些朋友认为根本没有必要使用存储过程等等。其实当你一个项目做完到了维护阶段时,就会发现存储过程给我们带来了好处了,修改方便,不能去改我们的应用程序,只需要改存储过程的内容,而且还可以使我们的程序速度得到提高。 QUOTE: SQL SERVER 联机丛书中的定义: 存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。 可以创建一个过程供永久使用,或在一个会话中临时使用(局部临时过程),或在所有会话中临时使用(全局临时过程)。 也可以创建在 Microsoft SQL Server 启动时自动运行的存储过程。 要使用存储过程,首先我们必需熟悉一些基本的T-SQL语句,因为存储过程是由于一组T-SQL语句构成的,并且,我们需要了解一些关于函数、过程的概念,因为我们需要在应用程序中调用存储过程,就像我们调用应用程序的函数一样,不过调用的方法有些不同。 下面我们来看一下存储过程的建立和使用方法。 一、创建存储过程 和数据表一样,在使用之前我们需要创建存储过程,它的简明语法是: QUOTE: CREATE PROC 存储过程名称 [参数列表(多个以“,”分隔)] AS SQL 语句 例: QUOTE: CREATE PROC upGetUserName @intUserId INT, @ostrUserName NVARCHAR(20) OUTPUT -- 要输出的参数 AS BEGIN -- 将uName的值赋给 @ostrUserName 变量,即要输出的参数 SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId END 其中 CREATE PROC 语句(完整语句为CREATE PROCEDURE)的意思就是告诉SQL SERVER,

C#调用存储过程简单完整例子讲解

C#调用存储过程简单完整例子https://www.360docs.net/doc/b519073111.html,/itblog/article/details/752869 创建存储过程 Create Proc dbo.存储过程名 存储过程参数 AS 执行语句 RETURN 执行存储过程 GO DECLARE @iRet INT, @PKDisp VARCHAR(20) SET @iRet = '1' Select @iRet = CASE WHEN @PKDisp = '一' THEN 1 WHEN @PKDisp = '二' THEN 2 WHEN @PKDisp = '三' THEN 3 WHEN @PKDisp = '四' THEN 4 WHEN @PKDisp = '五' THEN 5 ELSE 100 END DECLARE @i INT SET @i = 1 WHILE @i<10 BEGIN set @i=@i+1 PRINT @i END DECLARE @d INT set @d = 1 IF @d = 1 BEGIN -- 打印 PRINT '正确' END ELSE BEGIN PRINT '错误' END

CREATE PROC P_TEST @Name VARCHAR(20), @Rowcount INT OUTPUT AS BEGIN SELECT * FROM T_Customer WHERE NAME=@Name SET @Rowcount=@@ROWCOUNT END GO ---------------------------------------------------------------------------------------- --存储过程调用如下: ---------------------------------------------------------------------------------------- DECLARE @i INT EXEC P_TEST 'A',@i OUTPUT SELECT @i --结果 /* Name Address Tel ---------- ---------- -------------------- A Address Telphone (所影响的行数为 1 行) ----------- 1 (所影响的行数为 1 行) */ ---------------------------------------------------------------------------------------- --DotNet 部分(C#) --WebConfig 文件: ---------------------------------------------------------------------------------------- ......

SQL存储过程实例

题目1 1、学校图书馆借书信息管理系统建立三个表: 学生信息表:student 图书表:book 借书信息表:borrow 请编写SQL语句完成以下的功能: 1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、 学生名称、图书编号、图书名称、借出日期;参考查询结果如下图所示: 2)查询所有借过图书的学生编号、学生名称、专业;参考查询结果如下图所示:

3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期; 参考查询结果如下图所示: 4)查询目前借书但未归还图书的学生名称及未还图书数量;参考查询结果如下图所 示: 附加:建表语句:

标准答案:

题目2 程序员工资表:ProWage 创建一个存储过程,对程序员的工资进行分析,月薪1500到10000不等,如果有百分之五十的人薪水不到2000元,给所有人加薪,每次加100,再进行分析,直到有一半以上的人大于2000元为止,存储过程执行完后,最终加了多少钱? 例如:如果有百分之五十的人薪水不到2000,给所有人加薪,每次加100元,直到有一半以上的人工资大于2000元,调用存储过程后的结果如图:

请编写T-SQL来实现如下功能: 1)创建存储过程,查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000 元之上,如果不到分别每次给每个程序员加薪100元,至之一半程序员的工资达到2200,3000,3500,4000,5000或6000元。 2)创建存储过程,查询程序员平均工资在4500元,如果不到则每个程序员每次加200元, 至到所有程序员平均工资达到4500元。 建表语句

数据库系统原理与开发-数据库存储过程

6.3 数据库存储过程

【本节的主要内容】 ?了解存储过程的概念 ?掌握存储过程创建、删除的方法?掌握存储过程的执行方法 ?掌握PostgreSQL的PL/SQL基本语法?理解存储过程的优缺点

一、什么是存储过程 ?存储过程(Stored Procedure)是一种数据库的对象; ?由一组能完成特定功能的SQL 语句集构成; ?是把经常会被重复使用的SQL语句逻辑块封装起来,经编译后,存储在数据库服务器端; ?当被再次调用时,而不需要再次编译; ?当客户端连接到数据库时,用户通过指定存储过程的名字并给出参数,数据库就可以找到相应的存储过程予以调用。

二、创建存储过程 ?不同的数据库系统创建存储过程的语法存在差异; ?许多数据库为创建存储过程和函数提供不同命令; ?如ORACLE、MySQL、SQL SERVER等数据库,使用CREATE PRECEDURE命令创建存储过程,使用CREATE FUNCTION命令创建函数。 ?PostgreSQL使用CREATE FUNCTION命令创建存储过程。

4 三、创建存储过程的语法 CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS retype | RETURNS TABLE ( column_name column_type [, ...] ) ]AS $$ //$$用于声明存储过程的实际代码的开始DECLARE -- 声明段BEGIN --函数体语句END; $$ LANGUAGE lang_name; //$$ 表明代码的结束, LANGUAGE 后面指明所用的编程语言 (1)name :要创建的存储过程名;(3)argmode :存储过程参数的模式可以为IN 、OUT 或INOUT ,缺省值是IN 。(4)argname :形式参数的名字。 (5)RETURNS :返回值;RETURNS TABLE :返回二维表 (2)OR REPLACE :覆盖同名的存储过程;

MySQL存储过程实例教程

MySQL存储过程实例教程 MySQL 5.0以后的版本开始支持存储过程,存储过程具有一致性、高效性、安全性和体系结构等特点,本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。 实例261:存储过程的创建 这是一个创建存储过程的实例 录像位置:光盘mingrisoft9?lt;/p> 实例说明 为了保证数据的完整性、一致性,提高应用的性能,常采用存储过程技术。MySQL 5.0之前的版本并不支持存储过程,随着MySQL技术的日趋完善,存储过程将在以后的项目中得到广泛的应用。本实例将介绍在MySQL 5.0以后的版本中创建存储过程。 技术要点 一个存储过程包括名字、参数列表,以及可以包括很多SQL语句的SQL语句集。下面为一个存储过程的定义过程: create procedure proc_name (in parameter integer)begindeclare variable varchar(20);if parameter=1 thenset variable='MySQL';elseset variable='PHP';end if;insert into tb (name) values (variable);end; MySQL中存储过程的建立以关键字create procedure开始,后面紧跟存储过程的名称和参数。MySQL的存储过程名称不区分大小写,例如PROCE1()和proce1()代表同一个存储过程名。存储过程名不能与MySQL数据库中的内建函数重名。 存储过程的参数一般由3部分组成。第一部分可以是in、out或inout。in表示向存储过程中传入参数;out表示向外传出参数;inout表示定义的参数可传入存储过程,并可以被存储过程修改后传出存储过程,存储过程默认为传入参数,所以参数in可以省略。第二部分为参数名。第三部分为参数的类型,该类型为MySQL数据库中所有可用的字段类型,如果有多个参数,参数之间可以用逗号进行分割。 MySQL存储过程的语句块以begin开始,以end结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程内部语句要以分号结束,所以在定义存储过程前应将语句结束标志“;”更改为其他字符,并且该字符在存储过程中出现的几率也应该较低,可以用关键字delimiter更改。例如: mysql>delimiter // 存储过程创建之后,可用如下语句进行删除,参数proc_name指存储过程名。 drop procedure proc_name 实现过程

一个完整的数据库示例--说明

一、表的结构及完整性约束 新建一个数据库jxsk,包括S、C、SC、T、TC五个表,结构如下:C表: S表: SC表: T表:

TC表: 二、安全性控制及视图机制 1、三类角色:depart、teacher、student depart的权限: teacher的权限:

student的权限: 2、有2个院系用户:d_jsj,d_xx,同属于depart角色。

有1个教师用户:t ,属于teacher 角色。

有一个学生用户:s,属于student角色。 3、创建计算机系教师视图t_view_jsj、计算机系学生视图s_view_jsj,并授予d_jsj 用户在这两个视图上的select、delete、update、insert权限。 计算机系教师视图t_view_jsj: create view t_view_jsj as select tno,tn,sex,age,prof,sal,comm,dept from t where dept='计算机' with check option

授予d_jsj用户在计算机系教师视图t_view_jsj 上的select、delete、update、insert 权限: grant select,update,delete,insert on t_view_jsj to d_jsj 计算机系学生视图t_view_jsj: create view s_view_jsj as select sno,sn,sex,age,dept,resume,native from s where dept='计算机' with check option 授予d_jsj用户在计算机系学生视图s_view_jsj 上的select、delete、update、insert 权限: grant select,update,delete,insert on s_view_jsj to d_jsj …… 4、创建一个视图,显示学号,姓名,院系,课程名,成绩。 create view score_view(学号,姓名,院系,课程名,成绩) as select s.sno,sn,dept,cn,score from s,sc,c where s.sno=sc.sno and https://www.360docs.net/doc/b519073111.html,o=https://www.360docs.net/doc/b519073111.html,o 三、完整性控制--触发器、规则 1、要求当删除C表中某课程信息时,同时删除SC和TC中与此课程相关的记录。create trigger c_delete_trigger on c after delete as delete from sc where cno in (select cno from deleted) delete from tc where cno in (select cno from deleted) go

MySQL存储过程简单入门

MySQL存储过程语法 1、概念 存储过程就是能完成一定操作的一组SQL语句。 2、作用 大大提高效率(存储过程本身执行速度非常快,而且,调用存储过程大大减少数据库交互次数);提高重用性。 3、使用方法 1、创建: create procedure sp_name() begin …… end 注意:可能有参数。 2、调用: call sp_name() 注意:括号不能省略。 3、删除: drop procedure sp_name 注意:没有括号,不能在一个存储过程中删除另一个存储过程,只能调用。 4、语句: 条件语句, if 条件then statement else statement end if while循环语句, [label:] while expression do statement end while [label]; loop循环语句, [label:] loop statement end loop [label];

repeat until循环语句, [label] repeat statement until expression end repeat [label]; 5、常用命令: show procedure status:显示数据库所有存储过程基本信息。 show create procedure sp_name:显示一个存储过程详细信息。 关于运算符和基本函数与Java有些区别,用时注意就行。 4、实例 1、创建: create procedure proc_name (in parameter integer) begin declare variable varchar(20); if parameter=1 then set variable='MySQL'; else set variable='PHP'; end if; insert into tb (name) values (variable); end; 注意:代码不区分大小写;存储过程之间以及存储过程与内建函数不能同名; 存储过程参数(in传入,out传出,inout可传入,修改后传出,缺省是in); 由于存储过程内部要以分号结束,需要delimiter进行更改。 2、实现: ⑴、mysql –u用户名–p用户密码 ⑵、delimiter //(将结束符号“;”改成“//”,避免与存储过程冲突) ⑶、use 数据库名 ⑷、……(创建存储过程) ⑸、call proc_name(5)//(调用存储过程) -------------------------------------------- call proc_name(@para)//(对应存储过程定义中out的输出) select @para// ⑹、show procedure status//与show create procedure proc_name// 3、程序代码调用:(out型的部分代码) try{ //调取out型的存储过程P(计算记录总数) stmt = conn.prepareCall("{call p(?)}"); //读取所有OUT型的存储过程的返回参数数据 stmt.registerOutParameter(1, Types.INTEGER); stmt.execute(); int i= stmt.getInt(1);

实验六 MySql存储过程

实验六MySql存储过程 一、实验目的 1、熟悉MySql的存储过程 二、实验内容 1、建立一张学生表,属性有学号、姓名、年龄三个字段。 2、建立一个存储过程,实现学生的全查询 3、分别用IN 和OUT实现姓名的调用 4、声明一个变量,把变量加1,再把变量加入到学生表的学号字段中。 5、建立一个存储过程,外部调用这个存储过程,当外部传入的值是0时,则在学生表中插入一个学号是17的学生,如果是1时,则在学生表中插入一个学号是18的学生,如果都不是,则在学生表中插入一个学号是19的学生. 6、建立一个存储过程,做一个循环语句,循环插入5个学生。(至少用三种循环的存储过程方法) 三、试验结果截图 1.建立一张学生表,属性有学号、姓名、年龄三个字段。 2.建立一个存储过程,实现学生的全查询

3.分别用IN 和OUT实现姓名的调用 4.声明一个变量,把变量加1,再把变量加入到学生表的学号字段中。

5.建立一个存储过程,外部调用这个存储过程,当外部传入的值是0时,则在学生表中插入一个学号是17的学生,如果是1时,则在学生表中插入一个学号是18的学生,如果都不是,则在学生表中插入一个学号是19的学生.

6建立一个存储过程,做一个循环语句,循环插入5个学生。(至少用三种循环的存储过程方法) 所有代码: 1. create table stu( stuno int, stuna varchar(20), stuage int ); insert into stu values(001,'zhangsan',22);

insert into stu values(002,'lisi',23); insert into stu values(003,'wangwu',23); insert into stu values(004,'maliu',24); insert into stu values(005,'zhaoqi',25); insert into stu values(006,'gaoba',23); insert into stu values(007,'ddddd',22); insert into stu values(008,'ttttt',21); 2. create procedure select_all() select * from stu; 3. delimiter // create procedure searchno( in no int, out na varchar(20), out age int ) begin select stuna from stu where stuno=no into na; select stuage from stu where stuno=no into age; end // delimiter ; call searchno(n,@na,@age); select @na,@age; 4. delimiter // create procedure noupdate( in n int) begin update stu set stuno=stuno+n; end // delimiter ; 5. delimiter // create procedure addstu( in sno int ) begin case sno when 0 then insert into stu values(17,'no17',20); when 1 then insert into stu values(18,'no18',20); else insert into stu values(19,'no19',20); end case; end //

MySQL存储过程实例教程2

MySQL存储过程详解 1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。 一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。 存储过程通常有以下优点: (1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。 (2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。 (3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。 (4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。 (5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。 2.关于MySQL的存储过程 存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。 3.MySQL存储过程的创建 (1). 格式

MySQL存储过程练习

存储过程实验 一、实验要求: 1.理解存储过程的概念 2.掌握存储过程的语法格式、使用方法 3.掌握存储过程的创建、执行 二、实验内容: (注意:做一下实验需要首先建立数据及其相应的表,并输入一些记录) 1.实验1:创建一个存储过程,实现查询表student中的记录信息,并执行存储过 程 (1)创建存储过程: begin select * from student end (2)执行存储过程:运行 sp_liststudent (3)执行结果如下:

2.实验2.创建一个存储过程,根据给定的学生学号返回该学生的姓名(1)创建存储过程:

CREATE PROCEDURE test5( IN id int) BEGIN SELECT * from student s WHERE s.id=id; END (2)执行存储过程:CALL test5(2) 执行结果如下: 3.实验3. 创建一个存储过程,根据班级的编号,统计该班人数,并将人数以输出 变量返回给用户。 (1)创建存储过程: CREATE PROCEDURE test00( IN roomidint) BEGIN SELECT COUNT(*) from student s WHERE s.roomid=9; END (2)执行存储过程:CALL test00(2) (3)执行结果如下:

4.实验4:创建一个存储过程查询学号为“020101”的学生的平均分是否超过了85 分,若超过则输出“ X X考出了高分”,否则输出“XX 考的一般”。 (1)创建存储过程: CREATE PROCEDURE test05() BEGIN IF (SELECT score from student WHERE id=1)>85 THEN SELECT '考得好'; ELSE SELECT '考得不好'; END IF; END

存储过程_将图片存入数据库

一、写一个存储过程,将图片存入数据库中 基本情况介绍: 数据库版本:oracle 11g 数据库用户:scott 数据库密码:tiger JDK:1.6 要导入的图片:D:\picture\1.jpg --创建存储图片的表 CREATE TABLE IMAGE_LOB (T_ID V ARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL); --创建存储图片的目录 CREATE OR REPLACE DIRECTORY IMAGES AS 'D:\picture'; 存储过程如下: CREATE OR REPLACE PROCEDURE IMG_INSERT (TID V ARCHAR2,FILENAME V ARCHAR2) AS F_LOB BFILE;--文件类型 B_LOB BLOB; BEGIN iNSERT INTO IMAGE_LOB (T_ID, T_IMAGE) V ALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB; --插入空的blob F_LOB:= BFILENAME ('IMAGES', FILENAME); --获取指定目录下的文件 DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONL Y); --以只读的方式打开文件 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,DBMS_LOB.GETLENGTH (F_LOB)); --传递对象 DBMS_LOB.FILECLOSE (F_LOB); --关闭原始文件 COMMIT; END;

存储过程的实例

存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure sp_name() begin ......... end 二.调用存储过程 1.基本语法:call sp_name() 注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递 三.删除存储过程 1.基本语法: drop procedure sp_name// 2.注意事项 (1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程 四.其他常用命令 1.show procedure status 显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等2.show create procedure sp_name 显示某一个mysql存储过程的详细信息 --------------------数据类型及运算符-------------------- 一、基本数据类型: 略 二、变量: 自定义变量:DECLARE a INT ; SET a=100; 可用以下语句代替:DECLARE a INT DEFAULT 100; 变量分为用户变量和系统变量,系统变量又分为会话和全局级变量 用户变量:用户变量名一般以@开头,滥用用户变量会导致程序难以理解及管理 1、在mysql客户端使用用户变量 mysql> SELECT 'Hello World' into @x; mysql> SELECT @x; mysql> SET @y='Goodbye Cruel World'; mysql> select @y; mysql> SET @z=1+2+3; mysql> select @z; 2、在存储过程中使用用户变量 mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql> SET @greeting='Hello'; mysql> CALL GreetWorld( );

mysql存储过程.详细说明,java代码调用过程

Mysql存储过程调用 说明: 由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词OUT或INOUT 在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。 当使用delimiter命令时,你应该避免使用反斜杠(‘"’)字符,因为那是MySQL的转义字符。 1、创建过程格式: Mysql> drop procedure if exists user_findById; Mysql> delimiter // Create procedure user_findById(in n int) Begin Select * from user where id= n; End // 调用过程: Mysql> set @n=1; --定义变量 Call user_findById(@n);--调用过程 // --显示结果 ======================================================

例2: Mysql> drop procedure if exists user_count; Mysql> delimiter // Create procedure user_count(out count int) Begin Select count(*) into count from user; End // --结束 注意: MySQL存储过程 “in”参数: 跟 C语言的函数参数的值传递类似, MySQL存储过程内部可能会修改此,参数,但对 in类型参数的修改,对调用者(caller)来说是不可见的(not visible)。 “out”参数: 从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值inout参数跟 out类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout参数传递值给存储过程。 总结:如果仅仅想把数据传给MySQL存储过程,那就使用“in”类型 参数;如果仅仅从MySQL存储过程返回值,那就使用“out”类型参数;如果需要把数据传给MySQL存储过程,还要经过一些计算后再传回给我们,

MySQL的存储过程导出和导入

MySQL的存储过程导出和导入 表结构等已经在目标数据库中存在,不想拷贝数据库过去,MySQL的存储过程导出和导入,Mysqldump工具可以实现,具体用法为: [root@localhost bin]# mysqldump -uroot -p -hlocalhost -P3306 -n -d -t -R DBName > procedure_name.sql 参数说明: -n: --no-create-db -d: --no-data -t: --no-create-info -R: --routines Dump stored routines (functions and procedures) Mysqldump是客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建活装载表的SQL语句: 主要参数介绍: 1.连接选项 -u,--user=name -p,--password=name -h,--host=name -P,--port=# 2.输出内容选项 --add-drop-database --add-drop-table -n;--no-create-db -d;--no-data -t;--no-create-info 3.输出格式选项 --compact -c --complete-insert -T(指定数据表中的数据备份为单纯的数据文件和建表SQL两个文件) 注意:xx.sql建表文件是以linux的root用户创建,而xx.txt文件则是一linux的mysql 用户创建,因此这两个文件的存放路径一定要保证mysql用户有读写创建文件的权限。 --fields-terminated-by=name(域分隔符) --fields-enclosed-by=name(域引用符) --fields-optionally-enclosed-by=name(域引用可选字符) --fields-escaped-by=name(转义字符) 4.字符集选项 --default--character-set=xx 5.其他选项

数据库存储过程练习附答案

存储过程、触发器练习 1、在学生选课数据库中,创建一存储过程deptmale,查询指定系的男生人数,其中系为输入参数,人数为输出参数。 create proc p_dept @dept char(20),@mannum int output as select @allcre=count(sno) from student where sdept=@dept and ssex='男' declare @num int exec p_dept '计算机系',@num output print @num 2、在s_c数据库中,创建一个存储过程totalcredit,根据输入的学生姓名,计算其总学分。(使用输出参数)。并执行该存储过程。 create proc p_cou @name char(10),@allcre int output as select @allcre=sum(ccredit)from student,course,sc where student.sno=sc.sno and https://www.360docs.net/doc/b519073111.html,o=https://www.360docs.net/doc/b519073111.html,o and sname=@name group by sc.sno declare @asum int exec p_cou '刘晨',@asum output print @asum 3、创建一更新触发器upd_grade,设置sc表的grade字段不能被更新,并显示信息“学生成绩不能被修改,请与教务处联系”。 CREATE TRIGGER mes_sc ON sc FOR UPDATE AS IF UPDATE(grade) BEGIN ROLLBACK TRAN PRINT '学生成绩不能被修改,请与教务处联系' END

sql存储过程及视图创建实例及语法

SQL Server 存储过程 Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 ?存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。 1、存储过程的优点 A、存储过程允许标准组件式编程 存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。 B、存储过程能够实现较快的执行速度 如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。 C、存储过程减轻网络流量 对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。 D、存储过程可被作为一种安全机制来充分利用 系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。 ?系统存储过程 系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。 常用系统存储过程有: exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列

C++调用SQL存储过程实例

c/c++调用mysql存储过程 mysql5.0以后就支持存储过程了,目前mysql的6.0Alpha版也已经推出。6.0不仅支持大型数据库如oracle等的绝大部分功能,如存储过程、视图、触发器、job等等,而且修正了这些功能所存在的bug,其中6.0.1还支持64位windows,以及表空间。 在c/c++中访问mysql,常见的只是一些简单的业务,使用c中嵌入简单的查询、插入、更新等操作即可。随着业务的复杂化,完成一个业务功能需要非常多的sql操作环节,把这些语句都嵌入c代码中会导致c代码越来越繁琐、不清晰,这时候,自然就想到了存储过程来封装所有的数据库逻辑,通过c简单调用mysql存储过程接口即可达到目的,极大地减轻了c程序员的工作量,也便于前端业务处理逻辑与数据库处理逻辑的分离。下面就介绍c 语言调用存储过程的简单的方法。 1、首先创建一张表 用于存放用户信息 Create table student( id int auto_increment, name varchar(20), age tinyint, remark varchar(50), primary key(id) ); 2、插入几条信息 Insert into student values(1,"zhouys",90, ""); commit; 3、查看用户信息 mysql> select * from student;

+------+-----------+------+----------+ | id | name | age | remark | +------+-----------+------+----------+ | 1 | zhouys | 90 | | +------+-----------+------+-----------+ 1 row in set (0.00 sec) mysql> 4、创建存储过程 如下: delimiter // create procedure querystudent( in in_id int , #0-字符id 1-数字id # out out_ret int, #返回结果 out out_name varchar(20), #名字 out out_age int #年龄) label_a:begin declare v_name varchar(20) ; declare v_age tinyint ; #参数判断 if (in_id<=0) then set out_ret=-1; #id error

MySQL存储过程基础教程

MySQL 5.0 存储过程 MySQL 5.0 新特性系列第一部分 MySQL 技术白皮书 Peter Gulutzan March, 2005 翻译:陈朋奕 西安电子科技大学 2005-5-6 (声明:属于个人翻译,不涉及任何商业目的,支持国内MySQL发展,请转载时注明出处,谢谢)Copyright 2005, MySQL AB 第1 页 如有错误请来信chenpengyi_007@https://www.360docs.net/doc/b519073111.html,,谢谢您的支持和阅读,同时感谢您对MySQL事业的关注请转载时保留这些信息

Table of Contents 目录(目录不做翻译了,因为基本都是专有名词) Introduction (3) A Definition and an Example (3) Why Stored Procedures (4) Why MySQL Statements are Legal in a Procedure Body (8) Characteristics Clauses (10) Parameters (13) The New SQL Statements (15) Scope (16) Loops (21) Error Handling (29) Cursors (35) Security (41) Functions (43) Metadata (44) Details (48) Style (52) Tips when writing long routines (63) Bugs (64) Feature Requests (65) Resources (65) Conclusion (66) About MySQL (66) Copyright 2005, MySQL AB 第2 页 如有错误请来信chenpengyi_007@https://www.360docs.net/doc/b519073111.html,,谢谢您的支持和阅读,同时感谢您对MySQL事业的关注请转载时保留这些信息

相关文档
最新文档