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 ;
sql:MySql create FUNCTION,VIEW,PROCEDURE
最新推荐文章于 2024-02-22 17:41:57 发布