Mysql基本操作(二)

来源：互联网发布：计算机冗余控制软件编辑：程序博客网时间：2024/05/16 16:47

一、动态SQL

use school;
select * from class;

方法一：

use school;
delimiter
DROP PROCEDURE IF EXISTS proc_sql;
CREATE PROCEDURE proc_sql ()
BEGIN
    declare id int;
    set id = 2;
    set @id = id;

    PREPARE prod FROM 'select * from class where id > ?';
    EXECUTE prod USING @id;
    DEALLOCATE prepare prod;

END;
delimiter ;

call proc_sql();

方法二：

use school;
delimiter
DROP PROCEDURE IF EXISTS test_sql;
CREATE PROCEDURE test_sql (
    in _sql char(128),
    in _id int
)
BEGIN
    set @callsql = _sql;
    set @id = _id;

    PREPARE prod FROM @callsql;
    EXECUTE prod USING @id;
    DEALLOCATE prepare prod;

END;
delimiter ;

call test_sql('select * from class where id > ?',3);

二、自定义函数

use school;

DELIMITER
DROP FUNCTION IF EXISTS test_function;
CREATE FUNCTION test_function(format_date datetime) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x= date_format(format_date,'%Y年%m月%d日%h时%i分%s秒');
RETURN x;
END;
DELIMITER;

select test_function(SYSDATE());

SQL Commond命令下查看创建表语句和是否执行索引

0 0