sql:MySql create FUNCTION,VIEW,PROCEDURE，mysqlprocedure

sql:MySql create FUNCTION,VIEW,PROCEDURE，mysqlprocedure

use geovindu;

#函數

DELIMITER $$
drop function if exists f_GetDepartmentName $$
CREATE function f_GetDepartmentName
(
   did int
) 
returns nvarchar(400) 
begin
declare 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,BookPlaceList
where 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
)
BEGIN
IF 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 ;