当前位置: 澳门新濠3559 > 数据库 > 正文

存储过程和函数可以避免开发人员重复的编写相

时间:2019-11-09 00:17来源:数据库
什么是代码执行结构: 这里所说的代码执行结构就是多条sql语句的执行顺序。 代码执行结构主要用于触发器、存储过程和函数等存储多条sql语句中。     3.1 创建存储过程 基本语法

什么是代码执行结构:

  • 这里所说的代码执行结构就是多条sql语句的执行顺序。
  • 代码执行结构主要用于触发器、存储过程和函数等存储多条sql语句中。

 

 


3.1 创建存储过程

  • 基本语法
DELIMITER $$
CREATE PROCEDURE 过程名字([参数列表])
BEGIN
 -- 过程体

END
$$ 
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE proe()
BEGIN
 -- 过程体
  SELECT * FROM my_account;    
END
$$ 
DELIMITER ;

澳门新濠3559 1

 

 

//带OUT返回的 
CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * FROM t_user WHERE USER_NAME LIKE p_name; 
END IF; 
SELECT FOUND_ROWS() INTO p_int; 
END 

本文内容:

  • 什么是代码执行结构
  • 顺序结构
  • 分支结构
  • 循环结构

 

 首发日期:2018-04-18


2.6 函数的作用域

  • MySQL中的作用域与js中的作用域完全一样。
    • 全局变量可以在任何地方使用;局部变量只能在函数内部使用。  

 

  • 全局变量:使用set关键字定义,使用@符号定义。
  • 局部变量:使用declare关键字声明,没有@符号,所有的局部变量的声明,必须在函数体开始之前。
DELIMITER $$
CREATE FUNCTION res (num INT) RETURNS INT
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE SUM INT DEFAULT 0;
    WHILE i <= num DO
    SET SUM = SUM +i;
    SET i = i+1;
    END WHILE;

    RETURN SUM;

END;
$$
DELIMITER ;

澳门新濠3559 2

澳门新濠3559 3

 

一、程序结构

这是表1

分支结构:

分支结构的执行是依据一定的条件选择执行路径,它会依据我们给定的条件来选择执行那些sql语句

澳门新濠3559 4

mysql中分支结构只有if-else:

  • 语法:

    if 条件 then
        sql语句
    [elseif 条件 then
        sql语句]
    [else
     sql语句]
    end if;
    
  • 示例:

    create table pass(id int primary key auto_increment,name varchar(15),score int );
    create table unpass(id int primary key auto_increment,name varchar(15),score int);
    
    -- 使用存储过程来
    create procedure myif(in name varchar(15),in score int)
    begin
        if score >=60 then
            insert into pass(name,score) values(name,score);
        else
            insert into unpass(name,score) values(name,score);
        end if;
    end;
    -- 调用,并查看结果
    call myif("lilei",61);
    call myif("hanmeimei",95);
    select * from pass;
    select * from unpass;
    call myif("tuhao",59);
    select * from unpass;
    
  • if中的条件基本可以参照select语句的while子句的条件。什么innot in = != 等都可以用。

    create procedure myif3(in a char(1))
    begin
        if a in('a','b') then
            select 1;
        else 
            select 2;
        end if;
    end;
    call myif3('a');
    call myif3('b');
    call myif3('c');
    

 

2.5 函数参数

  • 函数分为两种:定义的参数叫形参,调用时的参数叫实参。

 

  • 形参:必须指定数据类型
DELIMITER $$
CREATE FUNCTION 函数名 (形参名字 数据类型) RETURNS 数据类型
BEGIN
   -- 函数体
   -- 返回值

   RETURN 返回值;
END;
$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION res (num INT) RETURNS INT
BEGIN
    SET @i = 1;
    SET @sum = 0;
    WHILE @i <= num DO
    SET @sum = @sum + @i;
    SET @i = @i+1;
    END WHILE;

    RETURN @sum;

END;
$$
DELIMITER ;

SELECT res(100);

澳门新濠3559 5

 

  • 在函数内部定义的@变量在函数外部也可以使用。

澳门新濠3559 6

 

1、顺序结构

结果

补充:

  • 理论上,如果做出判断不符合,然而又不想继续执行下去的时候,应该执行返回(比如C语言的return来中断函数运行),但mysql中并没有对应的中断机制,所以需要我们主动中断(中断的方法有很多种,比如执行一条符合语法但无法运行的语句)【这种场景比如有:判断一名学生是否存在,不存在就不执行任何操作,所以应该执行一条无法成功运行的语句来报错返回。】
  • 事实上,还存在一种分支结构:case when 【好像好多书都没怎么讲到,所以这里不讲述。有兴趣的可以自行百度。】

 

 

 


2 函数

  • 函数:将一段代码块封装到一个结构中,在需要执行代码块的时候,调用结构执行即可(代码复用)。

 

  • 函数分为两类:系统函数和自定义函数。

 

3.3、if…else if…else语句 --多分支结构

如果需要在if..else里判断多个条件时,就需要if..else if … else语句了,其格式如下:

if (条件判断1){

语句主体1 ;

}else if (条件判断2){

语句主体 2 ;

}

… // 多个else if()语句

else{

语句主体3 ;

}

package pb.test;

public class test6 {

    public static void main(String[] args) {
        int x=3;
        if(x==1){
            System.out.println("x的值是1!");
        }else if(x==2){
            System.out.println("x的值是2!");
        }else if(x==3){
            System.out.println("x的值是3!");
        }else{
            System.out.println("x的值不是1,2,3 中的一个!");
        }

    }

}

 

四、Switch结构

 

要在许多的选择条件中找到并执行其中一个符合判断条件的语句时,除了可以使用if..else不断地判断之外,也可以使用另一种更方便的方式即多重选择——switch语句,语法格式:

switch (表达式){

case 选择值1 : 语句主体 1 ;        break ;

case 选择值2 : 语句主体 2 ;                 break ;

…….

case 选择值n : 语句主体 n ;                break ; default:  语句主体 ;

}

 

4.1、正常运行的Switch

package com;

import java.util.Scanner;

/**
* 根据用户指定的朋份,打印该月份所属的季节
*                 12,1,2冬
                 * 2,3,4春
                 * 5,6,7夏季
                 * 9,10,11秋季
*/
public class SwitchTest {

        public static void main(String[] args) {
                //定义扫描器变量
                Scanner scanner=new Scanner(System.in);
                //定义变量接收
                int num=scanner.nextInt();
                /**
                 * 数值类型的结果,并不是很多时用switch
                 */
                 switch(num){  //byte short int char只接收4种类型  JDK7以上switch可以判断字符串
                         case 3:
                         case        4:
                         case 5:
                                 System.out.println(num+"春季");
                                 break;
                         case 6:
                         case        7:
                         case 8:
                                 System.out.println(num+"夏季");
                                 break;
                         case 9:
                         case        10:
                         case 11:
                                 System.out.println(num+"秋季");
                                 break;
                         case 12:
                         case        1:
                         case 2:
                                 System.out.println(num+"冬季");
                                 break;
                         default:
                                 System.out.println("输入 错误,请输入1--12之间的数字");
                 }

        }

}

 

4.2、没有Break的Switch

package com;

/**
 * 根据操作符做相应的操作
 * 
 * @author Denny
 *
 */
public class SwitchTest2 {

        public static void main(String[] args) {
                int a = 10;
                int b = 3;
                char c = '+';

                switch (c) {
                default:
                        System.out.println("未知操作符");
                case '+':
                        System.out.println("a+b=" + (a + b));
                case '-':
                        System.out.println("a-b=" + (a - b));
                case '*':
                        System.out.println("a*b=" + (a * b));
                case '/':
                        System.out.println("a/b=" + (a / b));

                }

        }

}

 

五、循环while和do..while

 

5.1、While循环

 

package com.day03;
/**
 * 使用While进行100以内累加操作
 * @author Administrator
 *
 */
public class WhileTest {

    public static void main(String[] args) {

        int i=1;  //变量
        int sum=0; //和

        while(i<=100){//先判断条件
            sum+=i;
            i++;
        }
        System.out.println("100以内整数之和:"+sum);

    }

}

 

5.2 do...while

 

package com.day03;

/**
 * 使用do...While进行100以内累加操作
 * 
 * @author Administrator
 *
 */
public class DoWhileTest {

    public static void main(String[] args) {
        int i = 1; // 变量
        int sum = 0; // 和

        do {
            sum += i;
            i++;

        } while (i <= 100);

        System.out.println("100以内整数之和:" + sum);
    }

}

 

5.3、while和do..while区别

 

package com.db1;

public class WhileDemo {

        public static void main(String[] args) {
                //while 判断条件可能一次也不执行
                int x=1;

                while(x<3);
                {
                        System.out.println("x="+x);
                        x++;
                }

                //do while 最少执行一次
                int y=1;

                do{
                        System.out.println("do:y="+y);
                        y++;
                }while(y<3);

        }

}

 

六、for循环

 

6.1、for循环基本结构

 

for (赋值初值;判断条件;赋值增减量){

语句1 ;

….

语句n ;

}

 

6.2、使用for进行100以内累加操作

package com.day03;
/**
 * 使用for进行100以内累加操作
 * @author Administrator
 *
 */
public class ForTest {

    public static void main(String[] args) {
        int sum=0;
        for (int i = 0; i <=100; i++) {
            sum+=i;
        }
        System.out.println("100以内整数之和:" + sum);
    }

}

 

6.3、打印99乖法表

双循环打印
package com.db1;

/**
 * 打印99乖法表
 * 
 * @author denny
 *
 */
public class Demo7 {

        public static void main(String[] args) {
                //外层循环控制行数
                for(int i=1;i<=9;i++){
                        //内层循环控制列
                        for (int j = 1; j <=i; j++) {
                                System.out.print(j+"*"+i+"="+(j*j)+"  ");
                        }
                        //换行
                        System.out.println();
                }

        }

}

 

一个循环打印

package com.db1;

/**
 * 打印99乖法表
 * 
 * @author denny 一个循环打印
 */
public class Demo7_1 {

        public static void main(String[] args) {
                //一个循环
                for (int i = 1, j=1;i <=9; i++) {
                        //输出
            System.out.print(i+"*"+j+"="+i*j+"  ");
            if(i==j){
                    i=0;
                    j++;
                    System.out.println();
            }
          //当j>9是终止
                        if(j>9){
                                break;
                        }

    }

        }

}

 

另类写法

package com.day03;

public class ForTest2 {

    public static void main(String[] args) {

        int x=1;
        for(System.out.println("a");x<3;System.out.println("c")){
        System.out.println("d");
        x++;
        }
        //结果adcdc
    }

}

 

打印菱形

package com.day03;
/**
 * 打印菱形
 * @author Denny
 * 思路:使用for和循环
 * 1.先打印正三角,
 * 2.再打印倒三角
 *
 */
public class ForTest7 {

    public static void main(String[] args) {
        /*
         * 打印三角形 
         */
        for(int x=0;x<5;x++){

            //打印空格
            for(int y=x+1;y<5;y++){
                System.out.print(" ");
            }
            //打印三角形
            for(int z=0;z<=x;z++){
                System.out.print("* ");
            }
            //换行 
            System.out.println();

        }
        //倒三角
        for(int x=4;x>0;x--){

            //打印空格
            for(int y=x;y<=4;y++){
                System.out.print(" ");
            }
            //打印三角形
            for(int z=0;z<x;z++){
                System.out.print("* ");
            }
            //换行 
            System.out.println();

        }

    }

}

存储过程和函数可以避免开发人员重复的编写相同的SQL语句,一、程序结构。 

七、break、continue、return

 

7.1、break

结果最近的和循环体,执行和循环体后的代码

package com.day03;
/**
 * 使用for和循环验证break
 * @author Administrator
 *
 */
public class BreakDemo {

    public static void main(String[] args) {

        for(int i=0;i<10;i++){
            if(i==5){
                break;//结束最近的和循环体 ,执行这个和循环体后面的代码
            }
            System.out.print(i+" ");

        }

        System.out.println("程序结束");
    }

}

 

结果:

0 1 2 3 4 程序结束

 

7.2、continue

跳过本次和循环,进行下次和循环

package com.day03;
/**
 * 使用for和循环验证continue
 * @author Administrator
 *
 */
public class ContinueDemo {

    public static void main(String[] args) {

        for(int i=0;i<10;i++){
            if(i==5){
                continue;  //跳过本次和循环执行下次和循环
            }
            System.out.print(i+" ");

        }

        System.out.println("程序结束");

    }

}

 

结果:

0 1 2 3 4 6 7 8 9 程序结束

7.3、return

结果程序

/**
 * 使用for和循环验证return
 * @author Administrator
 *
 */
public class ReturnDemo {

    public static void main(String[] args) {
        for(int i=0;i<10;i++){
            if(i==5){
                return;    //程序结束
            }
            System.out.print(i+" ");

        }

        System.out.println("程序结束");  //这里没有执行

    }

}

 

 

结果:

0 1 2 3 4 

八、函数(方法)

 

8.1 函数定义

函数就是定义在类中的具有特定功能的一段独立小程序,函数也称为方法。

    函数的格式:
    修饰符 返回值类型 函数名(参数类型 形式参数1,参数类型 形式参数2,...)
    {
          执行语句;
          return 返回值;
    }

    返回值类型:函数运行后的结果的数据类型。
    参数类型:是形式参数的数据类型。
    形式参数:是一个变量,用于存储调用函数时传递给函数的实际参数。
    实际参数:传递给形式参数的具体数值。
    return:用于结束函数。
    返回值:该函数运算后的结果,该结果会返回给调用者。

8.2、函数特点

1、定义函数可以将功能代码进行封装。
    2、便于对该功能进行复用。
    3、函数只有被调用才会被执行。
    4、函数的出现提高了代码的复用性。

    P.S.
    1、对于函数没有具体返回值的情况,返回值类型用关键字void表示,那么该函数中的return语句如果在最后一行可以省略不写,或者写上return;。

8.3、函数的应用

 

package com.day03;
/**
 * 函数
 * @author denny
 *
 */
public class FunctionDemo {

    public static void main(String[] args) {

        String str="Hello";
        getString(str);
    }
    public static void getString(String str){
        System.out.println(str);
    }

}

 

8.4、函数的重载

重载的概念:
    在同一个类中,允许存在一个以上的同名函数,只要它们的参数个数或者参数类型不同即可和返回值类型无关
    重载的好处:
    代码复用提高,提高阅读性

package com.day03;
/**
 * 函数
 * @author denny
 *
 */
public class FunctionDemo {

    public static void main(String[] args) {
        System.out.println(add(4,5));
        System.out.println(add(4,5,6));
        System.out.println(add(2.0,3.5));
    }
    //2个整数参数
    public static int  add(int x, int y){
        return x+y;
    }
    //3个整数参数
    public static int  add(int x, int y,int z){
        return x+y+z;
    }
    //2个double参数
    public static double  add(double x, double y){
        return x+y;
    }

}

 

澳门新濠3559 7

九、数组

9.1、什么数组

 

数组是一组相关数据的集合,一个数组实际上就是一连串的变量,

数组按照使用可以分为一维数组、二维数组、多维数组。同一数组中的变量数据类型相同

9.2、数组的优点

 

不使用数组定义100个整型变量:int i1;int i2;…int i100

使用数组定义:int i[100]

同一类型的多个变量可以使用数组来完成

9.3、数组的定义和格式

格式一:

元素类型[] 数组名=new 元素类型[元素个数或者数组长度];

示例:int [] arr=new int[5];

格式2:

元素类型[] 数组名=new 元素类型{元素1,元素2,...元素n};

示例:int [] arr=new int[5,3,6,8,9];

数组声明后实际上是在栈内存中保存了此数组的名称,接下来便是要在堆内存中配置数组所需的内存,其中“长度”是告诉编译器,所声明的数组要存放多少个元素,

而“new”则是命令编译器根据括号里的长度开辟空间。

9.4、内存分配和特点

int [] arr=new int[4];

澳门新濠3559 8 

9.5、常识

数组的下标是从0开始的,

最后的元素下标是[长度-1】

可以通过下标赋值:arr[0]=2,arr[3]=5..

 

Case 
When search_condition then statement_list 
End case 

循环结构:

 

  • 循环结构是指在程序中需要反复执行某个功能而设置的一种程序结构。mysql中循环结构用于循环多次运行同一sql语句。
  • mysql中的循环结构有loop结构、while结构、repeat结构,这里只讲述while结构,有兴趣了解其他的可以自行百度。
  • 澳门新濠3559 9
  • 语法:

    while 条件 do

    sql语句
    

    end while;

循环名:while 条件 do
    sql语句;
    leaveiterate 循环名;
end while;

 

  • 示例:
    -- 无意义的一个例子,仅作演示
    create table whilenum(id int);
    --  自带条件的
    create procedure mywhile()
    begin
        declare num int;
        set num=10;
        c1:while num>0 do
                insert into whilenum values(num);
                set num=num-1;
            end while;
    end;
    -- 以传入参数作为条件的
    create procedure mywhile2(in num int)
    begin
        c1:while num>0 do
                insert into whilenum values(num);
                set num=num-1;
            end while;
    end;
    -- 带中断的
    create procedure mywhile3(in num int)
    begin
        c1:while num>0 do
                if num%2=0 then
                    set num=num-1;
                    iterate c1;
                end if;
                insert into whilenum values(num);
                set num=num-1;
            end while;
    end;
    

 

 

 


2.1 系统函数

  • 系统定义好的函数,直接调用即可。
  • 任何函数都有返回值,因此函数的调用都是通过select调用。

 

  • substring(str,offset,len):从offset(MySQL中的自妇产的下标是从1)开始,截取长度是len。
SET @cn = '世界你好';
SET @en = "hello world";

-- 字符串截取
SELECT SUBSTRING(@cn,1,2),SUBSTRING(@en,1,2);

澳门新濠3559 10

  • char_length:字符长度
  • length:字节长度
SET @cn = '世界你好';
SET @en = "hello world";

-- 字符串长度
SELECT CHAR_LENGTH(@cn),CHAR_LENGTH(@en),LENGTH(@cn),LENGTH(@en);

澳门新濠3559 11

  • instr:判断字符串是否在某个字符串中存在,存在,返回位置;失败,返回0。
SET @cn = '世界你好';
SET @en = "hello world";

SELECT INSTR(@cn,'你');

澳门新濠3559 12

SELECT INSTR(@cn,'哈哈');

澳门新濠3559 13

  • Lpad:左填充,将字符串按照某个指定的填充方式,填充到指定长度。
SET @cn = '世界你好';
SET @en = "hello world";

SELECT LPAD(@cn,20,'呵呵');

澳门新濠3559 14

  • strcmp:compare,字符串比较,小,就返回-1;相等,就返回0;大,就返回1。
SET @a = 'hello';
SET @b = 'hei';
SET @c = 'HEI';

SELECT STRCMP(@a,@b),STRCMP(@b,@c),STRCMP(@c,@a);

澳门新濠3559 15

 

选择结构是根据条件的成立与否,再决定要执行哪些语句的一种结构

4 关闭光标

顺序结构:

  • 顺序结构就是从上到下依次执行sql语句
  • 一般默认情况下都是顺序结构

 

 


3.2  查看存储过程

  • 函数的查看方式完全使用与存储过程。

 

  • 查看所有的存储过程。
    • show procedure status [like 'pattern'];  
SHOW PROCEDURE STATUS;

澳门新濠3559 16

  • 查看创建过程语句
    • show create procedure 存储过程名字;    

澳门新濠3559 17

 

2、选择结构

4)存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
三、光标(游标)
 3.1 定义      查询语句可能查询出多条记录,在存储过程和函数中使用光标标来逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前,并且声明在变量和条件之后。
1 声明光标


3.5 存储过程参数类型

  • 函数的参数需要数据类型指定,过程比函数更严格。

 

  • 过程还有自己的类型限定:三种类型
    • ① in:数据从外部传入给内部使用(值传递):可以是数值也可以是变量
    • ②out:只允许过程内部使用(不用外部数据),给外部使用的(引用传递,外部的数据会被先清空才进入内部),只能是变量。
    • ③inout:外部的可以在内部使用,内部修改也可以给外部使用,典型的引用传递,只能传递变量。  

 

  • 基本语法
DELIMITER $$
CREATE PROCEDURE 过程名 (IN 形参名称 数据类型,OUT 形参名称 数据类型,INOUT 形参名称 数据类型)
BEGIN
    -- 过程体
END;
$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE pro1 (IN int_1 INT,OUT int_2 INT,INOUT int_3 INT)
BEGIN
    -- 过程体
    SELECT INT_1,INT_2,INT_3;
END;
$$
DELIMITER ;
  • 调用:out和inout类型的参数必须传入变量,而不是是数值。
SET @int_2 = 1;
SET @int_3 = 1;
SET @int_1 = 1 ;
SELECT @int_1,@int_2,@int_3;
CALL pro1(@int_1,@int_2,@int_3);
SELECT @int_1,@int_2,@int_3;

澳门新濠3559 18

 

(九),mysql 1 代码执行结构 代码执行结构有三种:顺序结构、分支结构和循环结构。 1.1 分支结构 分支结构:实现准备多个代码块,...

3.2、if...else语句--双分支结构

if (判断条件){

语句主体1 ;

}else{

语句主体2;

}

 

package com.day03;

/**
 * 判断一个数是奇数还是偶数
 * 
 * @author Administrator
 *
 */
public class IFELSETest {

    public static void main(String[] args) {

        int x = 3;
        if (x % 2 == 0) {
            System.out.println(x + "是偶数!");
        } else {
            System.out.println(x + "是奇数!");
        }

    }

}

 

调用:

1 代码执行结构

  • 代码执行结构有三种:顺序结构、分支结构和循环结构。

 

 

 Proc_parameter:存储过程的参数列表
      格式[IN|OUT|INOUT]param_name type
      Param_name为参数名,type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数,分别用in/out/inout标识。参数的取名不要与数    据表的列名相同。
Characteristic:存储过程的某些特征设定,分别介绍
        1 COMMENT'string':用于对存储过程的描述,其中string为描述内容,comment为关键字。
        2 LANGUAGE SQL:指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。
        3 DETERMINISTIC:表示存储过程对同样的输入参数产生相同的结果;NOT DETERMINISTIC,则表示会产生不确定的结果(默认)。
        4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示存储过程包含读或写数据的语句(默认)
        No sql表示不包含sql语句
        Reads sql data表示存储过程只包含读数据的语句
        Modifies sql data 表示存储过程只包含写数据的语句
        5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行,还是使用调用者(invoker)的许可来执行。默认是definer
    Routine_body:存储过程的主体部分,包含了在过程调用的时候必须执行的sql语句。以begin开始,以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。
1.2、数据准备

3.3 调用过程

  • 因为存储过程是没有返回值的,所以不能使用select关键字。

 

  • 调用存储过程,使用call关键字。
call 存储过程的名字;

CALL proe();

澳门新濠3559 19

二、顺序结构

If search_condition then statement_list 
[elseif search_condition then statement_list]… 
[else statement_list] 
End if 

1.1 分支结构

  • 分支结构:实现准备多个代码块,按照条件选择性执行某段代码。

 

  • 在MySQL中只有if分支。

 

  • 澳门新濠3559,基本语法
if  条件判断  then
 -- 满足条件要执行的代码
else if
-- 不满足条件要执行的代码
else
-- 不满足条件要执行的代码
end if; 

 

  • 触发器结合if分支:判断商品库存是否足够,不够不能生成订单。
-- 触发器结合if分支,判断商品库存是否足够,不够不能生成订单
DELIMITER $$
CREATE TRIGGER insert_before BEFORE INSERT ON `ORDER` FOR EACH ROW
BEGIN
    -- 判断商品库存是否足够

    -- 获取商品库存:商品库存在表中
    SELECT inv  FROM goods WHERE id = new.g_id INTO @inv;    

    -- 比较kuc

    IF @inv < new.g_number THEN -- 库存不够
        -- 触发器没有提供一个能够阻止事件发生的能力,只能暴力报错
        INSERT INTO xxx VALUES(xxx);

    END IF;


END
$$
DELIMITER ;

 

三、条件选择结构

您可能感兴趣的文章:

  • 在Mysql数据库里通过存储过程实现树形的遍历
  • mysql存储过程详解
  • mysql存储过程 游标 循环使用介绍
  • MySQL存储过程使用实例详解
  • MySQL存储过程例子(包含事务,输出参数,嵌套调用)
  • MySQL 实现树的遍历详解及简单实现示例
  • mysql存储过程基础之遍历多表记录后插入第三方表中详解

2.2 自定义函数

  • 函数要素:函数名,参数列表,返回值,函数体(作用域)。

 

  • 创建函数
-- 创建函数

CREATE FUNCTION 函数名([形参列表]) RETURNS 数据类型
BEGIN
    -- 函数体
    -- 返回值
    RETURN 返回值(指定数据类型);
END;

DELIMITER $$
CREATE  FUNCTION dis() RETURNS INT
BEGIN
    RETURN 100;
END
$$ 
DELIMITER ;

 

  • 自定义函数和系统函数调用方式是一样的。
SELECT dis();

澳门新濠3559 20

 

3.1 、IF语句--单分支结构

if (判断条件){

语句1 ;

 

package com.day03;
/**
 * 比较2个数的大小
 * @author Administrator
 *
 */
public class IFTest1 {

    public static void main(String[] args) {
        int x=3;
        int y=10;
        System.out.println("====开始比较=====");
        if(x>y){
            System.out.println("x比y大!");
        }
        if(x<y){
            System.out.println("x比y小!");
        }
        if(x==y){
            System.out.println("x等于y!");
        }
        System.out.println("====比较完成!=====");
    }

}

 

运行:

2.3 查看函数

  • 查看所有函数:
show function status [like 'pattern'];

澳门新濠3559 21

  • 查看创建函数语句

澳门新濠3559 22

 

 

说明数据已成功复制过去了。

2.4 修改函数&删除函数

  • 函数不可以修改,只能先删除,再新增函数。

 

  • 删除函数的基本语句
drop function 函数名;

澳门新濠3559 23

 

程序至上而下逐行执行,一条语句执行完之后继续执行下一条语句,一直到程序的末尾

CREATE TABLE `test_cur1` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
) 
INSERT INTO `test_cur1` VALUES (1, '145', 'd1'); 
INSERT INTO `test_cur1` VALUES (2, '134', '1d'); 
INSERT INTO `test_cur1` VALUES (3, '123', '1ad'); 
INSERT INTO `test_cur1` VALUES (4, '121', '1as'); 

CREATE TABLE `test_cur2` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
) 

3.4 修改过程&删除过程

  • 存储过程不可以修改,只能先删除,再新增存储过后才能。

 

  • 删除存储过程
drop 存储过程的名字;

澳门新濠3559 24

 

3、循环结构

说明:存储过程体中的select…into语句返回的结果集只能有一行数据。
4、定义处理程序 是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题,并提出解决方法。

1.2 循环结构

  • 循环结构:某段代码在指定条件下执行。

 

  • while循环
while  条件判断 do
  -- 满足条件要执行的代码
  -- 变更循环条件
end while;

循环控制:在循环内部进行循环判断和控制
MySQL中没有对应的continue和break;
iterate:类似于continue
leave:类似于break

iterate和leave的使用方式: iterate/leave 循环名字;

-- 定义循环名字
循环名字:while  条件  do
    -- 循环体
    -- 循环控制
end while;

 

 

Select sp_name([func_parameter…]) 
Select fn_search(2); 

3 存储过程

  • 存储过程:简称过程,procedure,是一种用来处理数据的方式。
  • 存储过程是一个没有返回值的函数。

 

只能修改存储过程的特征,如果要修改存储过程的内容,可以先删除该存储过程,然后再重新创建
1.7、 删除存储过程

MySQL (九),mysql

Case 语句
表达形式1

3)存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。

以上就是本文的全部内容,希望对大家学习MySql存储过程与函数有所帮助

Repeat语句首先执行statement_list中的语句,然后判断条件search_condition是否为真,倘若为真,则结束循环,若不为真,继续循环。
Repeat先执行后判断,while先判断后执行。
使用范例:

澳门新濠3559 25

Drop procedure [if exists] sp_name; 

Fetch cur_name intovar_name[,var_name…]; 
Cur_name表示光标的名称 
Var_name表示将光标中的select语句查询出来的信息存入该参数。Var_name必须在声明光标前就定义好。 
Fetch cur_employee intoemp_name,emp_age; 

澳门新濠3559 26

澳门新濠3559 27

1.5、 调用存储过程

Declare cursor_name cursor forselect_statement; 
Cursor_name:光标名称 
Select_statement:select语句的内容 
Declare cur_employee cursor forselect name,age from employee; 

(3)、带INOUT的存储过程

调用输出:统计带林开头的人数

2 打开光标

存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。
一、存储过程
**
1.1、基本语法**

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
 
  [characteristic ...] routine_body  

3、select … into 语句
把选定列的值直接存储到局部变量中,语法格式

//创建储存过程.cmd 中运行 
CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * FROM t_user WHERE USER_NAME LIKE p_name; 
END IF; 
END 

澳门新濠3559 28

如:

DECLARE var_name[,…] type [DEFAULT value] 
Var_name:指定局部变量的名称 
Type:用于声明局部变量的数据类型 
default子句:用于为局部变量指定一个默认值。若没有指定,默认为null. 
Select col_name[,…] into var_name[,…] table_expr 
Col_name:用于指定列名 
Var_name:用于指定要赋值的变量名 
Table_expr:表示select语句中的from字句及后面的语法部分 

 1.6、 修改存储过程

表达形式2

澳门新濠3559 29

澳门新濠3559 30

然后写一个存储过程:返回各个分数等级的人

澳门新濠3559 31

2.3、存储过程和函数区别
1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。

[begin_label:] 
repeat 
Statement_list 
Until search_condition 
End repeat 
[end_label]   

然后写光标了:

 判断条件search_condition是否为真,若为真,则执行statement_list中的语句,然后再进行判断,如若仍然为真则继续循环,直至条件判断不为真时循环结束。
使用范例

//带多重IF的存储过程 
CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1)) 
BEGIN 
IF p_level ='A' THEN 
SELECT * FROM t_grade WHERE STU_SCORE >=90; 
ELSEIF p_level ='B' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <90 AND STU_SCORE>=80; 
ELSEIF p_level ='C' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <80 AND STU_SCORE>=70; 
ELSEIF p_level ='D' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <60; 
ELSE 
SELECT * FROM t_grade; 
END IF; 
END 

澳门新濠3559 32

使用说明:
局部变量只能在存储过程体的begin…end语句块中声明。
局部变量必须在存储过程体的开头处声明。
局部变量的作用范围仅限于声明它的begin..end语句块,其他语句块中的语句不可以使用它。
局部变量不同于用户变量,两者区别:局部变量声明时,在其前面没有使用@符号,并且它只能在begin..end语句块中使用;而用户变量在声明时,会在其名称前面使用@符号,同时已声明的用户变量存在于整个会话之中。
2、set语句
使用set语句为局部变量赋值

(2)、带OUT的存储过程

Search_condition参数:条件判断语句
Statement_list参数:不同条件的执行语句
多重IF的存储过程实例
数据准备

//调用并输出结果 
CALL SP_SCHOLARSHIP_LEVEL('A'); 

调用:

输出结果:计算5!

CREATE TABLE 
 t_student 
 ( 
  STU_ID INT NOT NULL, 
  STU_NAME CHAR(10) NOT NULL, 
  STU_CLASS INT NOT NULL, 
  STU_SEX CHAR(2) NOT NULL, 
  STU_AGE INT NOT NULL, 
  PRIMARY KEY (STU_ID) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8; 
Close cursor_name; 
Close cur_employee; 
Call sp_name([parameter[,…]]); 
Sp_name被调用存储过程的名称 
Parameter:指定调用存储过程所要使用的参数。
Case case_value 
When when_value then statement_list 
[When when_value then statement_list]… 
[else statement_list] 
End case

调用:

Sp_name:存储过程的名称,默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称

[begin_label:] 
while search_condition do 
Statement_list 
End while 
[end_label]  
//带INOUT的存储过程 
CREATE PROCEDURE sp_inout(INOUT p_num INT) 
BEGIN 
SET p_num=p_num*10; 
END 
//调用并输出结果 
SET @p_num=2; 
call sp_inout(@p_num); 
SELECT @p_num; 

复制代码 代码如下:

二、函数
**
2.1、 定义**

成绩表(STU_ID是学生表是外键关系):

使用范例

调用过程:

(2)循环语句 While语句、repeat语句和loop语句。
While语句

Declare cid int(10); 

澳门新濠3559 33

来看看两张表的数据:这是表2

MySQL中,创建存储函数的基本形式如下:
CREATE FUNCTION sp_name([func_parameter[,...]]) 
RETURNS type 
[characteristic ...] routine_body 
Return 

复制代码 代码如下:

Alter procedure proc_name[characteristic…] 

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement 
handler_type:CONTINUE | EXIT | UNDO 
Condition_value:Sqlwarning | not found | sqlexception 
//调用并输出结果 
CALL SP_SCHOLARSHIP_LEVEL3('d'); 

学生表:

//带while的存储过程 
CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT) 
BEGIN 
 SET p_result=1; 
 WHILE p_num > 1 DO 
 SET p_result = p_num * p_result; 
 SET p_num = p_num-1; 
 END WHILE; 
END 
//调用并输出结果 
CALL sp_cal(5,@result); 
SELECT @result; 
CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1)) 
BEGIN 
DECLARE p_num int DEFAULT 0; 
CASE p_level 
WHEN 'A' THEN 
SET p_num=90; 
WHEN 'B' THEN 
SET p_num=80; 
WHEN 'C' THEN 
SET p_num=70; 
WHEN 'D' THEN 
SET p_num=60; 
ELSE 
SET p_num=0; 
END CASE; 
SELECT * FROM t_grade g, t_student s WHERE g.STU_ID=s.STU_ID AND g.STU_SCORE >= p_num ; 
END 

因为;分会冲突,所以要加delimiter //。将//设置为结束运行符号

Set var_name=expr 
Set cid=910; 

澳门新濠3559 34

数据如下:

CREATE TABLE 
 t_user 
 ( 
  USER_ID INT NOT NULL AUTO_INCREMENT, 
  USER_NAME CHAR(30) NOT NULL, 
  USER_PASSWORD CHAR(10) NOT NULL, 
  USER_EMAIL CHAR(30) NOT NULL, 
  PRIMARY KEY (USER_ID), 
  INDEX IDX_NAME (USER_NAME) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8; 
//调用并输出结果 
CALL SP_SEARCH2('林%',@p_num); 
SELECT @p_num; 

1.3 IN、OUT、INOUT参数
(1)、带IN的存储过程

删除存储函数drop
修改存储函数alter 修改存储函数的某些相关特征。

输出结果:

澳门新濠3559 35

每个光标不再需要时都应该被关闭,使用close语句将会释放光标所使用的全部资源。在一个光标被关闭后,如果没有重新被打开,则不能被使用。对于声明过的光标,则不需要再次声明,可直接使用open语句打开。
3.2、使用范例 (将表test_cur1数据复制到test_cur2)

/**函数使用**/ 
CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT) 
RETURNS INT 
BEGIN 
IF p_num1 >= p_num2 THEN 
RETURN p_num1; 
ELSE 
RETURN p_num2; 
END IF; 
END 

2.2、函数使用例子
(比较大小 ,返回大的数)

Open cursor_name 
Open cur_employee; 
call get_cur() 

如下:

SET @p_num1=2; 
SET @p_num2=34; 
SELECT sp_cal_max(@p_num1,@p_num2); 
create procedure get_cur () 
BEGIN 
 DECLARE done INT DEFAULT 0; 
 DECLARE ID int(11); 
 DECLARE type char(11); 
 DECLARE order1 char(11); 
 DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//定义光标 
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
 //打开光标 
 OPEN mycur; 
 //开始循环 
 REPEAT 
 FETCH mycur INTO ID,type,order1;//取出光标的内容到临时变量 
 IF NOT done THEN 
  INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一张表 
 END IF; 
 UNTIL done END REPEAT;//当done=1时结束循环 
 //关闭光标 
 CLOSE mycur; 
END 

2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类~~存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。

Repeat语句语法格式

1.4、存储过程体    
       存储过程体中可以使用各种sql语句和过程式语句的组合,来封装数据库应用中复杂的业务逻辑和处理规则,以实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。
1、局部变量
在存储过程体中可以声明局部变量,用来存储存储过程体中临时结果。

然后这是插入的一些数据:

澳门新濠3559 36

CREATE TABLE 
 t_grade 
 ( 
  STU_ID INT NOT NULL, 
  STU_SCORE INT NOT NULL, 
  FOREIGN KEY (STU_ID) REFERENCES t_student (STU_ID), 
  INDEX STU_ID (STU_ID) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8; 

5、流程控制语句 (1)条件判断语句
If语句

//带repeat的存储过程 
CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT) 
BEGIN 
 SET p_result=1; 
 REPEAT 
  SET p_result = p_num * p_result; 
  SET p_num = p_num-1; 
  UNTIL p_num<=1 
 END REPEAT; 
END 
//调用并输出结果 
CALL sp_cal2(5,@result); 
SELECT @result; 

澳门新濠3559 37

//调用并输出结果 
CALL SP_SEARCH('林炳文') 

3 使用光标
Mysql中使用fetch关键字来使用光标,语法形式

 

子句用于声明存储函数返回值的数据类型。存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
调用存储函数

澳门新濠3559 38

编辑:数据库 本文来源:存储过程和函数可以避免开发人员重复的编写相

关键词: