`
sillycat
  • 浏览: 2542913 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

Mysql Database Event and Procedure(2)Store Procedure

 
阅读更多
Mysql Database Event and Procedure(2)Store Procedure

F.Y.I mysqldump command
>mysqldump -h localhost -u root -P 7778 -ppassword databasename table name --where "click_time >= '2016-12-01'" > affiliate_clicks_2016_12_01.sql

Import Command
mysql -h localhost -u username -p'password' databasename < clicks2_2016_12.sql

MySQL supports event scheduler after 5.1, supports stored procedure after 5.0.

First Example
DELIMITER //
CREATE PROCEDURE proc1(OUT s int)
BEGIN
     SELECT COUNT(1) INTO s FROM user;
END
//
DELIMITER ;

IN -  input parameter, procedure can not return and change that parameter
OUT - procedure can change that variable and return
INOUT -

IN Example
DELIMITER //
CREATE PROCEDURE demo_in_parameter (IN p_in int)
BEGIN
     SELECT p_in;
     SET p_in = 2;
     SELECT p_in;
END;
//
DELIMITER ;

Call the procedure
>SET @p_in = 1;
>CALL demo_in_parameter(@p_in);
p_in will be 2 in the procedure, but it is still 1 outside.

OUT Example
DELIMITER //
CREATE PROCEDURE demo_out_parameter(OUT p_out int)
BEGIN
     SELECT p_out;
     SET p_out=2;
     SELECT p_out;
END;
//
DELIMITER ;

INOUT Example
DELIMITER //
CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
BEGIN
     SELECT p_inout;
     SET p_inout=2;
     SELECT p_inout;
END;
//
DELIMITER ;

Call the procedure
SET @p_inout = 1;
CALL demo_inout_parameter(@p_inout);

Variable
DECLARE variable_name datatype [DEFAULT value];
DECLARE l_varchar archer(255) DEFAULT ’this is a sample default value’;

SET
SET variable_name = expression

USER Variable
SELECT ‘Hello World’ into @x;
SELECT @x;

SET @y=‘Goodbye Cruel World’;
SELECT @y;

Query All Tables
>show tables;

Query All Procedure
>show procedure status where db = 'jobs';

Query detail of Procedure, databaseName.procedureName
>show create procedure jobs.get_lineitem;

Condition Statement
DELIMITER //
CREATE PROCEDURE proc2(IN parameter int)
begin
     declare var int;
     set var = parameter+1;
     if var = 0 then
          insert into t values(17);
     end if;
     if parameter = 0 then
          update t set s1=s1+1;
     else
          update t set s1=s1+2;
     end if;
end;
//
DELIMITER ;

case Statement
DELIMITER //
CREATE PROCEDURE proc3 (in parameter int)
begin
     declare var int;
     set var=parameter+1;
     case var
          when 0 then
               insert into t values(17);
          when 1 then
               insert into t values(18);
          else
               insert into t values(19);
     end case;
end;
//
DELIMITER ;

Loop Statement - while … end while
DELIMITER //
CREATE PROCEDURE proc4()
begin
     declare var int;
     set var = 0;
     while var < 6 do
          insert into t values(var);
          set var=var+1;
     end while;
end;
//
DELIMITER ;

Loop Statement - repeat … end repeat
DELIMITER //
CREATE PROCEDURE proc5 ()
begin
     declare v int;
     set v=0;
     repeat
          insert into t values(v);
          set v=v+1;
          until v >=5
     end repeat;
end;
//
DELIMITER ;

Loop Statement - loop … end loop
DELIMITER //
CREATE PROCEDURE proc6()
begin
     declare v int;
     set v=0;
     LOOP_LABLE:loop
          insert into t values(v);
          set v=v+1;
          if v >=5 then
               leave LOOP_LABLE;
          end if:
     end loop:
end

Functions in Stored Procedure
ONCAT, LCASE, LEFT, LENGTH
ABS(number), round(number)
NOW(), DATE(datetime)

References:
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

http://www.jianshu.com/p/1cb06d5eda09
http://www.blogjava.net/nonels/archive/2008/10/09/233324.html
https://yq.aliyun.com/articles/20804
分享到:
评论

相关推荐

    解决mysql failed to open table mysql.event

    在MySQL数据库系统中,"failed to open table mysql.event" 是一个常见的错误,通常表示数据库无法正确访问或加载`mysql.event`表。`mysql.event`是MySQL服务器用来存储定时任务(也称为事件Scheduler)的地方,当这...

    mysql database 6本书

    Smarty.PHP.Template.Programming.and.Applications.Mar.2006.pdf MySQL and JSP Web Applications ...MySQL.Database.Design.and.Tuning.(2005).DDU.LotB.chm MySQL.Stored.Procedure.Programming.(2006).BBL.LotB.chm

    mysql存储过程编程 MySQL.Stored.Procedure.Programming

    mysql存储过程方面的圣经,以通俗的示例方法讲述mysql存储过程的深奥内容,In MySQL Stored Procedure Programming, they put that hard-won experience to good use. Packed with code examples and covering ...

    MySQL for Database Administrators Student Guide - Volume I

    2. 数据库架构设计: - 设计数据库结构,包括数据库、表、索引、视图、存储过程、触发器等。 - 确定数据类型选择、规范化和反规范化等数据库设计原则。 3. 数据库备份与恢复: - 使用命令行工具或者图形界面备份...

    MySQL Stored Procedure Programming

    If you are serious about building the web-based database applications of the future, you need to get up to speed quickly on how stored procedures work -- and how to build them the right ...

    mysql event脚本

    mysql event脚本

    Creating your MySQL Database.pdf

    Anyone working with applications that use a MySQL database backend will benefit greatly from the advice and techniques in this book. Although a working knowledge of both SQL and MySQL is assumed, the...

    MariaDB and MySQL common Table Expressions and Window Functions Revealed

    newly available in MariaDB 10.2 and MySQL 8.0, and helps you understand why and how every MariaDB and MySQL database programmer should learn and apply these features in their daily work. CTEs and ...

    MySQL database

    Introducton to MySQL

    Guide to Optimizing Performance of the MySQL Cluster Database

    This guide explores how to tune and optimize the MySQL Cluster database to handle diverse workload requirements. It discusses data access patterns and how to build distribution awareness into ...

    存储过程(Stored Procedure)

    ### 存储过程(Stored Procedure)详解 #### 一、存储过程的概念与作用 存储过程是一种预先编写并编译好的SQL语句集合,通常用于实现特定的数据库操作或逻辑处理。存储过程存储在数据库服务器中,用户可以通过指定...

    PHP.and.MySQL.Web.Development.5th.Edition

    The definitive guide to building database-driven Web applications with PHP and MySQL PHP and MySQL are popular open-source technologies that are ideal for quickly developing database-driven Web ...

    MySQL 系统管理员指南

    Starting with the basics, you will learn to store, manage and retrieve data in a MySQL database and to eventually manipulate the code behind MySQL 5.0 to customize your database. With the clear ...

    Devart dbForge Studio for MySQL Professional Edition v7.1.13

    by-step code execution, breakpoints,watches, a call stack, a variables evaluation mechanism to automate debugging of MySQL stored routines and triggers and keeps MySQL server's logic of procedure ...

    Introducing the MySQL 8 Document Store

    Learn the new Document Store feature of MySQL 8 and build applications around a mix of the best features from SQL and NoSQL database paradigms. Don’t allow yourself to be forced into one paradigm or ...

    db-mysql-event

    mysql event scheduler 使用详解,含全局设置、创建event、修改event、删除event及相应的example,测试环境 mysql ver 14.14 distrib 5.7.26

    MySQL 8 Cookbook epub 格式

    Store, retrieve, and manipulate your data using the latest MySQL 8 features Practical recipes on effective administration in MySQL, with a focus on security, performance tuning, troubleshooting, and ...

Global site tag (gtag.js) - Google Analytics