用户可通过指定存储过程的名字并给定参数(需要

作者: www.9159.com  发布:2019-11-09

一、存储过程
1、定义:一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行

style="font-size: 18px; font-family: "Microsoft YaHei""> style="font-size: 14pt; color: #008000">阅读目录: style="color: #ff6600">MySQL存储过程_创建-调用-参数

  • style="font-size: 18px; font-family: "Microsoft YaHei"">存储过程:SQL中的“脚本”

    style="font-size: 18px; font-family: "Microsoft YaHei"">    创建存储过程

    style="font-size: 18px; font-family: "Microsoft YaHei"">    调用存储过程

    style="font-size: 18px; font-family: "Microsoft YaHei"">    存储过程体

    style="font-size: 18px; font-family: "Microsoft YaHei"">    语句块标签

  • style="font-size: 18px; font-family: "Microsoft YaHei"">存储过程的参数

    style="font-size: 18px; font-family: "Microsoft YaHei"">    in:向过程里传参

    style="font-size: 18px; font-family: "Microsoft YaHei"">    out:过程向外传参值

    style="font-size: 18px; font-family: "Microsoft YaHei"">    inout:in and out

Oracle数据库之PL/SQL过程与函数

PL/SQL块分为匿名块与命名块,命名块又包含子程序、包和触发器。

过程和函数统称为PL/SQL子程序,我们可以将商业逻辑、企业规则写成过程或函数保存到数据库中,以便共享。

过程和函数均存储在数据库中,并通过参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据,而过程不返回数据。

1. 存储过程概念

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储在数据库中。经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

2. 创建过程

语法:

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
   [ ( parameter_declaration [, parameter_declaration ]... ) ]
   [ invoker_rights_clause ]
   { IS | AS }
   { [ declare_section ] body | call_spec | EXTERNAL} ;

说明:

procedure_name:过程名称。

parameter_declaration:参数声明,格式如下:

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
          | { OUT | IN OUT } [ NOCOPY ] datatype 

IN:输入参数。

OUT:输出参数。

IN OUT:输入输出参数。

invoker_rights_clause:这个过程使用谁的权限运行,格式:

AUTHID { CURRENT_USER | DEFINER }

declare_section:声明部分。

body:过程块主体,执行部分。

一般只有在确认procedure_name过程是新过程或是要更新的过程时,才使用OR REPALCE关键字,否则容易删除有用的过程。

示例1:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
      tot_emps := tot_emps - 1;
   END;

示例2:

CREATE OR REPLACE PROCEDURE insert_emp(
   v_empno     in employees.employee_id%TYPE,
   v_firstname in employees.first_name%TYPE,
   v_lastname  in employees.last_name%TYPE,
   v_deptno    in employees.department_id%TYPE
   )
AS
   empno_remaining EXCEPTION;
   PRAGMA EXCEPTION_INIT(empno_remaining, -1);
BEGIN
   INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID)
   VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno);
   DBMS_OUTPUT.PUT_LINE('插入成功!');
EXCEPTION
   WHEN empno_remaining THEN
      DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

3. 使用过程参数

当建立过程时,既可以指定过程参数,也可以不提供任何参数。

过程参数包括输入参数、输出参数和输入输出参数,其中输入参数(IN)用于接收调用环境的输入数据,输出参数(OUT)用于将输出数据传递到调用环境,而输入输出参数(IN OUT)不仅要接收输入数据,而且还要输出数据到调用环境。

3.1 带有输入参数的过程

通过使用输入参数,可以将应用程序数据传递到过程。当定义过程参数时,默认参数模式是输入参数,另外可以使用IN关键字显式定义输入参数。

示例:

CREATE OR REPLACE PROCEDURE insert_emp(
    empno employee.empno%TYPE,
    ename employee.ename%TYPE,
    job employee.job%TYPE,
    sal employee.sal%TYPE,
    comm IN employee.comm%TYPE,
    deptno IN employee.deptno%TYPE
    )
IS
BEGIN
    INSERT INTO employee VALUES(empno, ename, job, sal, comm, depno);
END;

3.2 带有输出参数的过程

通过在过程中使用输出参数,可以将数据或消息传递到调用环境和应用程序。当定义输出参数时,需要指定参数模式OUT。

示例:

CREATE OR REPLACE PROCEDURE update_sal(
    eno NUMBER,
    salary NUMBER,
    name out VARCHAR2) 
IS
BEGIN
    UPDATE employee SET sal=salary WHERE empno=eno
    RETURNING ename INTO name;
END;

3.3 带有输入输出参数的过程

通过在过程中使用输入输出参数,可以在调用过程时输入数据到过程,在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时,需要指定参数模式为IN OUT。

示例:

CREATE OR REPLACE PROCEDURE divide(
    num1 IN OUT NUMBER,
    num2 IN OUT NUMBER) 
IS
    v1 NUMBER;
    v2 NUMBER;
BEGIN
    v1 := trunc(num1 / num2);
    v2 := mod(num1,num2);
    num1 := v1;
    num2 := v2;
END; 

4. 调用过程

当在SQL*PLUS中调用过程时,需要使用CALL或者EXECUTE命令,而在PL/SQL块中过程可以直接引用。

ORACLE使用EXECUTE语句来调用存储过程语法:

EXEC[UTE] procedure_name(parameter1, parameter2, …);

示例1:

-- 调用删除员工的过程
EXEC remove_emp(1);

-- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);

示例2:

DECLARE
    v_name employee.ename%type;
BEGIN
    update_sal(&eno,&salary,v_name);
    dbms_output.put_line('姓名:'||v_name);
END;

5. 函数介绍

函数是一段独立的PL/SQL程序代码,它执行某个特定的、明确的任务。通常,函数将处理从程序的调用部分传递给它的信息,然后返回单个值。信息通过称为参数的特殊标识符传递给函数,然后通过RETURN语句返回。

6. 创建函数

语法:

CREATE [ OR REPLACE ] FUNCTION [ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) 
  ]
  RETURN datatype
  [ { invoker_rights_clause
    | DETERMINISTIC
    | parallel_enable_clause
    | RESULT_CACHE  [ relies_on_clause ]
    }...
  ]
  { { AGGREGATE | PIPELINED }  USING [ schema. ] implementation_type
  | [ PIPELINED ] { IS | AS } { [ declare_section ] body 
                              | call_spec
                              | EXTERNAL
                              }
  } ;

示例1:

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS
     acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total INTO acc_bal FROM orders 
      WHERE customer_id = acc_no; 
      RETURN(acc_bal); 
    END;

函数参数也有输入、输出、输入输出三种模式:IN、OUT、IN OUT是形参的模式。若省略,则为IN模式。

和过程一样,IN模式的形参只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变。

OUT模式的形参会忽略调用时的实参值(或说该形参的初始值总是NULL),但在函数内部可以被读或写,函数返回时形参的值会赋予给实参。

IN OUT具有前两种模式的特性,即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。

调用时,对于IN模式的实参可以是常量或变量,但对于OUT和IN OUT模式的实参必须是变量。

示例2:

CREATE OR REPLACE FUNCTION get_salary(
  dept_no IN NUMBER DEFAULT 1,
  emp_count OUT NUMBER)
  RETURN NUMBER
IS
  V_sum NUMBER;
BEGIN
  SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES
  WHERE DEPARTMENT_ID=dept_no;
  RETURN v_sum;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('数据不存在');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('其它异常:');
      DBMS_OUTPUT.PUT_LINE('错误号:' || SQLCODE||',错误消息:'||SQLERRM);
END get_salary;

7. 函数调用

语法:

function_name([[parameter_name1 =>] value1[, [parameter_name2 =>] value2, ...]]);

示例1:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(27, v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

示例2:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(dept_no => 27, emp_count => v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

示例3:

DECLARE
  v_num NUMBER;
  v_sum NUMBER;
BEGIN
  v_sum := get_salary(emp_count => v_num);
  DBMS_OUTPUT.PUT_LINE('部门27的工资总和:'||v_sum||',人数为:'||v_num);
END;

8. 删除过程或函数

删除过程语法:

DROP PROCEDURE [schema.]procudure_name;

删除函数语法:

DROP FUNCTION [schema.]function_name;

9. 过程与函数比较

过程 函数
作为PL/SQL语句执行 作为表达式的一部分执行
在规范中不包含RETURN子句 必须在规范中包含RETURN子句
不返回任何值 必须返回单个值
可以RETURN语句,但是与函数不同,它不能用于返回值 必须包含至少一条RETURN语句

过程与函数的相同功能有:

  1. 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
  2. 输入参数都可以接受默认值,都可以传值或传引导。
  3. 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
  4. 都有声明部分、执行部分和异常处理部分。
  5. 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

 

Mysql学习笔记(八)

2、优点:①将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用
           ②批量处理:SQL+循环,减少流量,也就是“跑批”
      ③统一接口,确保数据的安全

#SQL语句:先编译后执行

存储过程(Stored Procedure)

(1)是一组为了完成特定功能的 SQL语句集,是利用 SQL Server 所提供的 Transact-SQL 语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中的一个重要对象,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是由 流控制和 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量 。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。

(2) 存储过程的优点:

➢ 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一 般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

➢ 当对数据库进行复杂操作时(如对多个表进行 Update、Insert、Query、Delete 时), 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

➢ 存储过程可以重复使用,可减少数据库开发人员的工作量。

➢ 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

3、>存储过程就是具有名字的一段代码,用来完成一个特定的功能。
   >创建的存储过程保存在数据库的数据字典中。

存储过程(Stored Procedure):

(1)创建存储过程

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

说明:

✓ DEFINER:指明使用存储过程的访问权限。

✓ sp_name: 存储过程名称。

✓ proc_parameter: [ IN | OUT | INOUT ] param_name type

        ☆ in:表示向存储过程中传入参数;存储过程默认为传入参数,所以参数 in 可以省略;

        ☆ out:表示向外传出参数;

        ☆ inout:表示定义的参数可传入存储过程,并可以被存储过程修改后传出存储过程;

        ☆ param_name:参数名;

        ☆ type:参数的类型,可以为 mysql 任何合法得数据类型。

        ☆ 如果有多个参数,参数之间可以用逗号进行分割。

✓ Characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

        ☆ 这个 LANGUAGE SQL 子句是没有作用的。仅仅是为了说明下面过程的主体 使用 SQL 语言编写。这条是系统默认的。

        ☆ 如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定 的 ” ( DETERMINISTIC ), 否 则 就 是 “ 非 确 定 ” 的 。 默 认 的 就 是 NOT DETERMINISTIC。         ☆ CONTAINS SQL 表示子程序不包含读或写数据的语句。

        ☆ NO SQL 表示子程序不包含 SQL 语句。

        ☆ READS SQL DATA 表示子程序包含读数据的语句,但不包含写数据的语句。

        ☆ MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明 确给定,默认的是 CONTAINS SQL。

        ☆ SQL SECURITY 特征可以用来指定子程序该用创建子程序者的许可来执行, 还是使用调用者的许可来执行。默认值是 DEFINER。

        ☆ COMMENT 子句是一个 MySQL 的扩展,它可以被用来描述存储程序。这个 信息被 SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION 语句来 显示。存储子程序不能使用 LOAD DATA INFILE。

        ☆ 特 征 子 句 也 有 默 认 值 , 如 果 省 略 了 就 相 当 于 : LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''

✓ routine_body: 包含合法的 SQL 过程语句。可以使用复合语句语法, 复合语 句可以包含声明,循环和其它控制结构语句。

4、基本创建格式

  一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

(2)修改存储过程

ALTER {PROCEDURE} sp_name [characteristic ...]

说明:

✓ 这个语句可以被用来改变一个存储程序的特征。必须用 ALTER ROUTINE 权限

才可用此子程序。这个权限被自动授予子程序的创建者。

✓ 在 ALTER PROCEDURE 语句中,可以指定超过一个的改变。

1 delimiter//    -- 将SQL语句的结束标记设置为//
2 DROP PROCEDURE IF EXISTS testprint; -- 如果存在该名字的存储过程则删除
3 CREATE PROCEDURE testprint() -- 创建一个名字为testprint的存储过程,    -- 括号里面可以传参数:CREATE PROCEDURE mytest(in sid INT,in suid INT)
4 BEGIN    -- 代码部分开始
5 SELECT '你好,成都' as infos; -- 存储过程的代码部分
6 END;    -- 代码部分结束
7 //    -- 结束标记
8 delimiter; -- 编写编译结束后将SQL语句的结束标记还原

 

(3)删除存储过程

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

5、调用

优点(为什么要用存储过程?):

(4)显示存储过程

SHOW CREATE {PROCEDURE} sp_name

似于 SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。

 -- 调用无参存储过程
CALL testprint();

-- 调用有参数的存储过程
set @stuid = 1;
SET @subid = 1;
CALL mytest(@stuid,@subid);    

  ①将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用

(5)显示存储过程特征

SHOW {PROCEDURE} STATUS [LIKE 'pattern']

它返回子程序的特征,如数据库,名字,类型,创建者及创建和修改日期。

 

  ②批量处理:SQL+循环,减少流量,也就是“跑批”

(6)调用存储过程

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

调用一个先前用 CREATE PROCEDURE 创建的程序。

CALL 语句可以用声明为 OUT 或的 INOUT 参数的参数给它的调用者传回值。它也“返回”

受影响的行数,客户端程序可以在 SQL 级别通过调用 ROW_COUNT()函数获得这个数,从

C 中是调用 the mysql_affected_rows() C API 函数来获得。

6、存储过程的参数
存储过程可以有0个或多个参数,用于存储过程的定义。
3种参数类型:
   IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
   OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
   INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
建议:>输入值使用in参数;
>返回值使用out参数;
>inout参数就尽量的少用。
例子:
1)、传入学生和科目id ,得到该学生该科目的成绩

  ③统一接口,确保数据的安全

(7)存储过程的变量:

声明变量:DECLARE var_name[,...] type [DEFAULT value]

变量赋值,SET 语句: SET var_name = expr [, var_name = expr] ...

变量赋值,SELECT ... INTO 语句 SELECT col_name[,...] INTO var_name[,...] table_expr

 1 delimiter $$ 
 2 DROP PROCEDURE IF EXISTS mytest;
 3 CREATE PROCEDURE mytest(in sid INT,in suid INT)
 4 BEGIN
 5 UPDATE score SET score.scscore=scscore+5 
 6 WHERE score.sid = sid AND score.suid = suid;
 7 END;
 8 $$
 9 delimiter;
10 
11 set @stuid = 1;
12 SET @subid = 1;
13 CALL mytest(@stuid,@subid);

相对于oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。

(8)存储过程的语句

BEGIN...END 复合语句 [begin_label:] BEGIN [statement_list] END [end_label] 存储子程序可以使用 BEGIN ... END 复合语句来包含多个语句。statement_list 代表一个或多个语句的 列表。statement_list 之内每个语句都必须用分号(;)来结尾。 复合语句可以被标记。除非 begin_label 存在,否则 end_label 不能被给出,并且如果二者都存在,他们必须是同样的。

 

 

(9)存储过程的注释语法

mysql 存储过程可使用两种风格的注释

➢ 双模杠:--,该风格一般用于单行注释

➢ c 风格:/* 注释内容 */, 一般用于多行注释

2)、-- 传入学生序号,获取显示该学生的姓名

一、存储过程的创建和调用

(10)存储过程的条件和异常处理程序

DECLARE handler_type HANDLER FOR condition_value[,...]

sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value | condition_name |

SQLWARNING | NOT FOUND | SQLEXCEPTION

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,

指定的语句被执行。

对一个 CONTINUE 处理程序,当前子程序的执行在执行处理程序语句之后继续。对

于 EXIT 处理程序,当前 BEGIN...END 复合语句的执行被终止。UNDO 处理程序

类型语句还不被支持。

SQLWARNING 是对所有以 01 开头的 SQLSTATE 代码的速记。

NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记。

SQLEXCEPTION 是对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE

代码的速记。

 1 delimiter $$ 
 2 DROP PROCEDURE IF EXISTS getstuname;
 3 CREATE PROCEDURE getstuname(in sid INT,out stuname VARCHAR(100))
 4 BEGIN
 5 SELECT student.sname into stuname FROM student WHERE student.sid = sid;
 6 END;
 7 $$
 8 delimiter;
 9 
10 set @stuid = 2;
11 CALL getstuname(@stuid,@stuname);
12 SELECT @stuid as sid,@stuname AS stuname;

  >存储过程就是具有名字的一段代码,用来完成一个特定的功能。

 

  >创建的存储过程保存在数据库的数据字典中。

1、创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
  Valid SQL routine statement

[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

 

#创建数据库,备份数据表用于示例操作

mysql> create database db1;
mysql> use db1;    
mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
mysql> create table MATCHES  as select * from TENNIS.MATCHES;

示例:创建一个存储过程,删除给定球员参加的所有比赛

mysql> delimiter $$  #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    ->   DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;  #将语句的结束符号恢复为分号

解析:

  默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀;

  在定义过程时,使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。

 

2、调用存储过程:call sp_name[(传参)];

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
5 rows in set (0.00 sec)

mysql> call delete_matches(57);
Query OK, 1 row affected (0.03 sec)

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
4 rows in set (0.00 sec)

解析:

  在存储过程中设置了需要传参的变量p_playerno,调用存储过程的时候,通过传参将57赋值给p_playerno,然后进行存储过程里的SQL操作。

 

3、存储过程体

  >存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等

  >过程体格式:以begin开始,以end结束(可嵌套)

BEGIN
  BEGIN
    BEGIN
      statements; 
    END
  END
END

注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。

 

4、为语句块贴标签

[begin_label:] BEGIN
  [statement_list]
END [end_label]

例如:

label1: BEGIN
  label2: BEGIN
    label3: BEGIN
      statements; 
    END label3 ;
  END label2;
END label1

标签有两个作用:

  ①增强代码的可读性

  ②在某些语句(例如:leave和iterate语句),需要用到标签

 

 

二、存储过程的参数

  存储过程可以有0个或多个参数,用于存储过程的定义。

3种参数类型:

  IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)

  OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

  INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

 

1、in输入参数

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    ->   select p_in;
    ->   set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

#以上可以看出,p_in在存储过程中被修改,但并不影响@p_id的值,因为前者为局部变量、后者为全局变量。

 

2、out输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
  #因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
  #调用了out_param存储过程,输出参数,改变了p_out变量的值

 

3、inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量

 

注意:

  ①如果过程没有参数,也必须在过程名后面写上小括号

    例:CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

  ②确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理

墙裂建议:

style="font-size: 18px; font-family: "Microsoft YaHei"">  >输入值使用in参数;

style="font-size: 18px; font-family: "Microsoft YaHei"">  >返回值使用out参数;

style="font-size: 18px; font-family: "Microsoft YaHei"">  >inout参数就尽量的少用。

本文由9159.com发布于www.9159.com,转载请注明出处:用户可通过指定存储过程的名字并给定参数(需要

关键词:

上一篇:没有了
下一篇:没有了