mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+
DELIMITER $$DROP PROCEDURE IF EXISTS `t_girl`.`sp_create_view`$$CREATE DEFINER=`root`@`%` PROCEDURE `sp_create_view`( IN f_view_name varchar(64), IN f_view_content varchar(8000)) BEGIN -- Created date 20080320. set @stmt = concat('create view ',f_view_name,' as ',f_view_content); prepare s1 from @stmt; execute s1; drop prepare s1; set @stmt = NULL; END$$DELIMITER ; 调用:call sp_create_view('show_user','select user,password,host from mysql.user'); select * from show_user;结果: query result(5 records) user password host root localhost .... ...
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
ps:楼上的大大,人家问的是存储过程建视图,不是直接建视图,hoho。
DELIMITER $$DROP PROCEDURE IF EXISTS `t_girl`.`sp_create_view`$$CREATE DEFINER=`root`@`%` PROCEDURE `sp_create_view`(
IN f_view_name varchar(64), IN f_view_content varchar(8000))
BEGIN
-- Created date 20080320.
set @stmt = concat('create view ',f_view_name,' as ',f_view_content);
prepare s1 from @stmt;
execute s1;
drop prepare s1;
set @stmt = NULL;
END$$DELIMITER ;
调用:call sp_create_view('show_user','select user,password,host from mysql.user');
select * from show_user;结果:
query result(5 records)
user password host
root localhost ....
...