sql:MySql create FUNCTION,VIEW,PROCEDURE

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

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 ;