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 BookKindListSETBookKindName=param1Name ,BookKindParent=param1ParentwhereBookKindID=param1ID;ELSE    UPDATE BookKindListSET BookKindParent=param1ParentwhereBookKindID=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 ;

　　

 

sql:MySql create FUNCTION,VIEW,PROCEDURE