`
hideto
  • 浏览: 2674954 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

在MySQL存储程序里使用SQL

阅读更多
1,使用非SELECT语句
UPDATE/INSERT/SET、DDL、DML等非查询语句可以随意在存储程序里执行
CREATE PROCEDURE simple_sqls()
BEGIN
    DECLARE i INT DEFAULT 1;

    /* Example of a utility statement */
    DROP TABLE IF EXISTS test_table
    CREATE TABLE test_table
        (id INT PRIMARY KEY, some_data VARCHAR(30)) ENGINE=innodb;

    /* Example of an INSERT using a procedure variable */
    WHILE (i<=10) DO
        INSERT INTO TEST_TABLE VALUES(i, CONCAT("record ", i));
        SET i=i+1;
    END WHILE

    /* Example of an UPDATE using procedure variabless */
    SET i =5;
    UPDATE test_table
        SET some_data=CONCAT("I updated row ", i)
    WHERE id=i;

    /* DELETE with a procedure variable */
    DELETE FROM test_table
        WHERE id>i;

END;


2,使用INTO
如果SELECT语句只返回一行记录,则可以使用INTO语句来把结果存入一个变量
如果SELECT语句返回多行记录,则使用INTO会出现运行时错误
CREATE PROCEDURE get_customer_details(in_customer_id INT)
BEGIN
    DECLARE l_customer_name     VARCHAR(30);
    DECLARE l_contact_surname   VARCHAR(30);
    DECLARE l_contact_firstname VARCHAR(30);

    SELECT customer_name, contact_surname, contact_firstname
        INTO l_customer_name, l_contact_surname, contact_firstname
        FROM customers
        WHERE customer_id=in_customer_id;

    /* Do something with the customer record */
END;


3,创建和使用Cursor
可以使用Cursor来处理SELECT语句返回多好记录时的场景
Cursor提供对查询结果集的访问,并且可以循环结果集的每一行,然后每行单独处理
DECLARE l_dept_id BIGINT;
DECLARE c_dept CURSOR FOR
    SELECT department_id FROM departments;

OPEN c_dept;
dept_cursor: LOOP
    FETCH c_dept INTO l_dept_id;
END LOOP dept_cursor;
CLOSE c_dept;

存在的第一个问题:变量声明必须在CURSOR声明之前
存在的第二个问题:上面的程序在FETCH结果集遇到最后一条之后会报错"no data to fetch" error (MySQL error 1329; SQLSTATE 02000)
为了避免第二个问题,我们需要声明一个HANDLER
DECLARE l_dept_id BIGINT;
DECLARE l_last_row_fetched INT;
DECLARE c_dept CURSOR FOR
    SELECT department_id FROM departments;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

SET l_last_row_fetched=0;
OPEN c_dept;
dept_cursor: LOOP
    FETCH c_dept INTO l_dept_id;
    IF l_last_row_fetched=1 THEN
        LEAVE dept_cursor;
    END IF;
END LOOP dept_cursor;
CLOSE c_dept;
SET l_last_row_fetched=0;


4,使用UNBOUNED SELECT语句返回数据给调用者
sp:
CREATE PROCEDCURE sp_get_all_users()
BEGIN
    SELECT user_name, age
        FROM users;
END;

Java客户端:
private void getAllUsers(Connection c) throws SQLException {
    CallableStatement s = c.prepareCall("{CALL sp_get_all_users()}");
    s.execute();
    ResultSet rs = s.getResultSet();
    while(rs.next()) {
        System.out.println(rs.getString("user_name"));
    }
    rs.close();
    s.close();
}


5,使用Prepared Statements处理动态SQL
MySQL支持server-side prepared statements,PREPARE创建,EXECUTE执行,DEALLOCATE销毁:
mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> SET @code='QB';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Query Browser';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> SET @code='AD';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Administrator';
Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> DEALLOCATE PREPARE prod_insert_stmt;
Query OK, 0 rows affected (0.00 sec)

这样一来就可以在sp里做一些手脚,让sp更flexible:
CREATE PROCEDURE set_col_value
    (in_table     VARCHAR(128),
     in_column    VARCHAR(128),
     in_new_value VARCHAR(1000),
     in_where     VARCHAR(4000))

BEGIN
    DECLARE l_sql VARCHAR(4000);
    SET l_slq=CONCAT_ws(' ',
                 'UPDATE', in_table,
                 'SET', in_column, '=', in_new_value,
                 'WHERE', in_where);
    SET @sql=l_sql;
    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;
END;


6,声明HANDLER的语法
DECLARE {CONTINUE | EXIT} HANDLER FOR
    {SQLSTATE sqlstate_code | MySQL error code | condition_name}
    stored_program_statement
分享到:
评论

相关推荐

    SQLDeveloper工具/MySQL/SQLServer驱动程序

    在这个特定的压缩包中,我们关注的是它与MySQL和SQL Server驱动程序的整合,这使得用户能够方便地在这些不同的数据库系统之间进行数据迁移和管理。 首先,让我们深入了解SQLDeveloper。它是一个免费的集成开发环境...

    Oracle Sql语句转换成Mysql Sql语句

    在数据库管理领域,Oracle SQL和MySQL SQL是两种广泛使用的SQL方言,它们在语法和功能上存在一定的差异。当需要将一个基于Oracle SQL的应用程序迁移到MySQL环境时,就需要进行SQL语句的转换工作。本项目提供了一个...

    通过SQL Server操作MySQL的步骤和方法

    在 SQL Server 中操作 MySQL 之前,需要安装 MySQL 的驱动程序。MySQL 的驱动安装包可以在 MySQL 官方网站上下载到。在安装完成后,可以在 ODBC 数据源管理器中看到安装成功的 MySQL ODBC 驱动程序。 1.1 安装 ...

    连接mysql sqlserver的两个数据库

    1. 使用JDBC驱动程序(如mysql-connector-java和sqljdbc)。 2. 在Java代码中创建并管理两个数据库的连接。 3. 编写SQL语句进行数据操作。 4. 使用数据集成工具或编写脚本来实现数据迁移或同步。 了解这些知识点后...

    PB 从SQL语句获取数据存储(MySQL)

    标题 "PB 从SQL语句获取数据存储(MySQL)" 指的是使用PowerBuilder (PB) 开发工具,通过SQL语句从MySQL数据库中检索和处理数据存储的过程。在这个Demo中,PB11.5 版本被用作开发环境,而MySQL作为后台数据库系统。...

    mysql数据库转换成SQLserver数据库

    例如,MySQL的ENUM类型在SQL Server中可能需要转化为CHAR或VARCHAR,MySQL的TINYINT可能对应SQL Server的BIT。此外,还要考虑主键、外键和约束的转换。 3. **工具使用**:"DB2DB"可能是一个用于数据库转换的工具,...

    从mysql数据库迁移至sqlserver数据库

    在迁移过程中,描述中提到的`text`类型在MySQL中存储大量文本数据,但在迁移到SQL Server时,如果没有预处理,可能会遇到中文乱码的问题。这是因为`text`在MySQL中默认编码可能与SQL Server的`ntext`类型不同。因此...

    sql和mysql jdbc包

    在IT行业中,SQL(Structured Query Language)是一种用于管理和操作关系型数据库的标准编程语言,而MySQL则是一款广泛应用的开源关系数据库管理系统。JDBC(Java Database Connectivity)是Java平台中用于访问...

    Mysql数据导入到SQLSERVER里面

    4. **转换数据库对象**:SSMA会将MySQL的表、视图、存储过程等数据库对象转换为SQL Server兼容的格式。 5. **数据迁移**:转换完成后,你可以选择迁移数据。这可能包括直接迁移数据或生成SQL脚本来手动执行。 6. **...

    sqlserver与mysql驱动程序

    使用这些驱动程序时,开发者需要在Java代码中引入相应的jar文件,并使用JDBC API来创建数据库连接、执行SQL语句以及处理结果集。例如,使用MySQL Connector/J,以下是一个简单的示例: ```java import java.sql.*; ...

    SQL Server转换为MySQL工具

    1. 数据库结构迁移:在转换过程中,工具会分析SQL Server的表结构,包括字段名、字段类型、主键、外键、索引等,并在MySQL中创建相应的表结构。理解这些元数据的重要性在于确保目标数据库能正确地存储源数据库的数据...

    Oracle数据库sql转换mysql数据库工具

    Oracle数据库和MySQL数据库是两种广泛使用的数据库管理系统,它们在数据存储、查询语法、数据库结构以及管理方式上存在显著差异。"Oracle数据库sql转换mysql数据库工具" 提供了解决这一问题的解决方案,允许用户将...

    JDBC数据库驱动程序包,Sql+mySql+oracle

    标题中的“JDBC数据库驱动程序包,Sql+mySql+oracle”揭示了这是一个包含不同数据库管理系统(DBMS)的Java数据库连接(JDBC)驱动的集合。JDBC是Java编程语言中用于与各种类型的数据库进行交互的一组接口和类。这个...

    mysql和sqlserver的驱动包

    MySQL和SQL Server是两种广泛应用的关系型数据库管理系统(RDBMS),它们在许多方面都有不同的特点和功能。在开发与数据库交互的应用程序时,通常需要相应的驱动程序来建立连接,执行查询和其他操作。以下是对这两个...

    mysql存储过程教程

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元,从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...

    MySQL存储过程的异常处理方法

    本实例展示了如何在MySQL存储过程中实现异常处理,以捕获并处理可能出现的错误。 首先,我们注意到在创建存储过程`myProc`时,使用了`delimiter $$`来改变MySQL客户端的语句分隔符,这是为了在存储过程中使用多个...

    MYSQL__省市区SQL.

    MySQL数据库在许多应用程序中用于存储和管理数据,特别是在地理信息系统和电子商务等领域,省市区的数据管理是常见的需求之一。本压缩包“MYSQL__省市区SQL”提供了这样的数据结构,便于开发者快速构建涉及地理位置...

    Java实现调用MySQL存储过程详解

    总的来说,Java通过JDBC调用MySQL存储过程涉及到连接数据库、创建`CallableStatement`、执行存储过程和处理结果。这使得开发者能够在Java应用中灵活地利用数据库提供的强大功能,实现更高效的数据处理。

Global site tag (gtag.js) - Google Analytics