Mysql基本操作(二)

一、动态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命令下查看创建表语句和是否执行索引