你的位置:首页 > 数据库

[数据库]sql:MySql create FUNCTION,VIEW,PROCEDURE


use geovindu;#函数DELIMITER $$drop function if exists f_GetDepartmentName $$CREATE function f_GetDepartmentName(  did int) returns nvarchar(400) begindeclare str nvarchar(100);select DepartmentName into str from DepartmentList where DepartmentID=did;return IFNULL(str,'');end; $$DELIMITER ;DELIMITER $$DROP FUNCTION IF EXISTS `geovindu`.`f_GetAuthorName` $$CREATE FUNCTION `geovindu`.`f_GetAuthorName` (did int) RETURNS varchar(400)BEGIN  declare str varchar(100);return(select ifnull(AuthorName,'') from AuthorList where AuthorID=did);END $$DELIMITER ;#测试SELECT f_GetDepartmentName(1);SELECT f_GetAuthorName(1);#视图/*视图*/select * from geovindu.views;desc View_BookInfoList;show create view View_BookInfoList;select * from View_BookInfoList;DELIMITER $$drop view IF EXISTS View_BookInfoList $$CREATE VIEW `geovindu`.`View_BookInfoList` AS select BookInfoID , BookInfoKind , BookInfoPlace ,BookInfoSeries , BookInfoAuthor , BookInfoPress , BookInfoLanguage , BookInfoStatus , BookInfoOperatorId , BookInfoISBN , BookInfoBarCode , BookInfoName ,  BookInfoRemarks ,BookInfoAddDate,BookInfoPublish ,BookInfoPrice,BookKindList.BookKindName,BookPlaceList.BookPlaceName,f_GetAuthorName(BookInfoAuthor)from BookInfoList,BookKindList,BookPlaceListwhere BookInfoList.BookInfoKind=BookKindList.BookKindID and BookInfoList.BookInfoPlace=BookPlaceList.BookPlaceID;$$DELIMITER ;#删除DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT)BEGIN     Delete From bookkindlist WHERE BookKindID = param1;END $$DELIMITER ;#查询所有DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll` $$CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll` ()BEGIN  SELECT * FROM bookkindlist;END $$DELIMITER ;select * from `geovindu`.`bookkindlist`;SELECT * FROM bookkindlist;#统计DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT)BEGIN    select COUNT(*) into param1ID From bookkindlist;END $$DELIMITER ;#更新 DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (  IN param1ID Int,  IN param1Name NVarChar(1000),  IN param1Parent Int)BEGINIF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录,不更新名称UPDATE BookKindList	SET		BookKindName=param1Name ,		BookKindParent=param1Parent	where		BookKindID=param1ID;ELSE  UPDATE BookKindList	SET BookKindParent=param1Parent	where		BookKindID=param1ID;END IF;END $$DELIMITER ;#查询一条DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT)BEGIN    SELECT * FROM BookKindList WHERE BookKindID = param1;END $$DELIMITER ;call proc_Select_BookKindList (1);#插入一条DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (  IN param1Name NVarChar(1000),  IN param1Parent Int)BEGIN    insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent);END $$DELIMITER ;#插入一条返回值DELIMITER $$DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT)BEGIN   IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then  #如果存在相同的记录,不添加    INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);    #set ID=Last_insert_id()    SELECT LAST_INSERT_ID() into ID;   end if;END $$DELIMITER ;