MySQL存儲過程和自定義函數(shù)、Navicat、創(chuàng)建存儲過程和函數(shù)、調(diào)用存儲過程和函數(shù)、三種方式、In和Out類型參數(shù)、

wwq圖書世界 2020-03-12

展開全文

與你相遇好幸運
可我已失去為你淚流滿面的權(quán)利
但愿在我看不到的天際
你張開了雙翼

1 MySQL存儲過程和函數(shù)

過程和函數(shù)，它們被編譯后保存在數(shù)據(jù)庫中，稱為持久性存儲模塊（Persistent Stored Module，PSM），可以反復(fù)調(diào)用，運行速度快。

1.1 存儲過程

存儲過程是由過程化 SQL 語句書寫的過程，這個過程經(jīng)編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中，因此稱它為存儲過程，使用時只要調(diào)用即可。

1.2 函數(shù)

這里指自定義函數(shù)，因為是用戶自己使用過程化 SQL 設(shè)計定義的。函數(shù)和存儲過程類似，都是持久性存儲模塊。函數(shù)的定義和存儲過程也類似，不同之處是函數(shù)必須指定返回類型。

MySQL 命令的執(zhí)行過程：
這里寫圖片描述

存儲過程和函數(shù)可以簡化語法分析和編譯的過程，提高運行速度。

2 我的 MySQL 數(shù)據(jù)表

數(shù)據(jù)庫名：peng
表名： imooc_goddess

這里寫圖片描述

我的創(chuàng)建數(shù)據(jù)庫、建表和插入數(shù)據(jù) SQL 語句：

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50712
Source Host           : localhost:3306
Source Database       : peng

Target Server Type    : MYSQL
Target Server Version : 50712
File Encoding         : 65001

Date: 2016-10-04 20:53:44
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for imooc_goddess
-- ----------------------------
DROP TABLE IF EXISTS `imooc_goddess`;
CREATE TABLE `imooc_goddess` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(30) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `create_user` varchar(30) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `update_user` varchar(30) DEFAULT NULL,
  `update_date` date DEFAULT NULL,
  `isdel` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of imooc_goddess
-- ----------------------------
INSERT INTO `imooc_goddess` VALUES ('2', '小彭', null, '23', null, null, null, null, null, null, null, null);
INSERT INTO `imooc_goddess` VALUES ('6', '胖子', '0', '78', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('7', '小溪', '0', '34', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('9', '小霞', null, '23', '1990-09-09', 'xiaoxia@qq.com', '232445455', null, '2016-10-03', null, '2016-10-03', null);
INSERT INTO `imooc_goddess` VALUES ('10', 'hh', '1', '23', '1990-09-09', 'jkjfskf', '12323', 'Admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('11', '平', '1', '23', '2998-04-09', 'jjjj@ww.com', '1323', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('12', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('13', '胖紙', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('14', '校長', '1', '18', '1998-09-09', 'jkjijij@qq.com', '112323424', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');

3 存儲過程的創(chuàng)建模板和調(diào)用模板

3.1 創(chuàng)建存儲過程模板

這里寫圖片描述

[ ] 表示可以省略
DEFINER 創(chuàng)建者，省略為默認用戶
sp_name 為過程名
sp_name 的參數(shù)（proc_paramenter）可以無或多個
routine_body 過程體
proc_paramenter 類型：

IN 必須在調(diào)用存儲過程時指定
OUT 可以被存儲過程改變，并且可以返回
INOUT 調(diào)用時指定，并且可以被改變和返回

過程體如果是復(fù)合結(jié)構(gòu)，則使用 BEGIN …END 語句。

CREATE PROCEDURE sp_name(proc_paramenter)
BEGIN
routine_body
END

3.2 調(diào)用存儲過程

1. CAll sp_name([parameter[,.....]])

2. CALL sp_name[()]

4 創(chuàng)建的存儲過程

存儲過程的創(chuàng)建的方式有很多，但是存儲過程的創(chuàng)建所需的代碼都大同小異。

在電腦的 CMD 命令行創(chuàng)建
在 Navicat 表中函數(shù)模塊新建函數(shù)
在 Navicat 新建查詢（類似命令行方式）

其實 Navicat for MySQL 只是一個圖形化界面而已，其中的原理都是一樣的，CMD 方式還是最經(jīng)典的，最基礎(chǔ)的。

示例

當我們登錄到 MySQL 數(shù)據(jù)庫中時,可以通過SELECT VERSION();，查詢到當前 MySQL 版本信息,如下

這里寫圖片描述

下面是把 SELECT VERSION(); 作為一個過程體，寫入到新建的一個存儲過程中的三種方式。

方式一 CMD命令行方式

因為數(shù)據(jù)庫本來就有自己的函數(shù)，創(chuàng)建存儲過程名，盡量避免使用數(shù)據(jù)庫相關(guān)的名詞，可以使用比較獨特的名詞。

創(chuàng)建名為 v1 的存儲過程，存儲在數(shù)據(jù)庫中，創(chuàng)建其他存儲過程時，就不能使用 v1這個名字創(chuàng)建了，否則會發(fā)生重名錯誤。

這里寫圖片描述

方式二 Navicat 新建查詢

創(chuàng)建名為 v3 的存儲過程

這里寫圖片描述

方式三 Navicat 新建函數(shù)

創(chuàng)建名為 v2 的存儲過程

1.在函數(shù)上右擊新建函數(shù)。

這里寫圖片描述

2.選擇過程。
這里寫圖片描述

3.這個存儲過程沒有參數(shù)，不填寫，點完成。
這里寫圖片描述

4.在過程體中輸入SELECT VERSION();，點擊保存，輸入過程名 v2 ，確認。
這里寫圖片描述

還是GIF圖來的快
這里寫圖片描述

當然，調(diào)用這 3 種存儲過程，都可以在 Navicat 新建查詢或 CMD 中通過

CALL v1();
CALL v2();
CALL v3();

來調(diào)用

4.1 創(chuàng)建不帶參數(shù)的存儲過程

在 CMD 中執(zhí)行下面的語句。
這里寫圖片描述

4.2 創(chuàng)建帶有IN類型參數(shù)的存儲過程

DELIMITER 是MySQL的一個定界符，是告訴mysql解釋器,該段命令是否已經(jīng)結(jié)束了,mysql可以執(zhí)行了
但是，在復(fù)合結(jié)構(gòu)中，sql 語句用“；”結(jié)尾，但是我們并沒有結(jié)束輸入命令，所以通過
DELIMITER //
修改默認為 “；”的定界符為”//”, 以后輸入 “//”代表命令輸入結(jié)束，mysql 可以執(zhí)行了。
DELIMITER ; 改回默認的定界符。

這里寫圖片描述

參數(shù)名不能和數(shù)據(jù)表名相同，上面例子的 p_id不能寫成 id。
不能更改已經(jīng)保存的存儲過程的函數(shù)體，必須先刪除該存儲過程，再創(chuàng)建新的存儲過程。

4.3 創(chuàng)建帶有 IN和OUT類型參數(shù)的存儲過程

這是一個有輸入值和返回值的存儲過程。輸入要刪除的行號 p_id ,之后數(shù)據(jù)庫計算剩余行數(shù) 返回到 userNums 中。

1.要輸入的值為 p_id,返回的值為 userNums。
這里寫圖片描述

2.在過程體中輸入

DELETE FROM imooc_goddess WHERE id = p_id;
SELECT COUNT(id) FROM imooc_goddess INTO userNums;

這里寫圖片描述

3.點擊進入函數(shù)，點擊運行
這里寫圖片描述

結(jié)果：

時間: 00:00.08

Procedure executed successfully
受影響的行: 1

Parameters: IN `p_id` int,OUT `userNums` int
14,@nums
Return values: 14, 4

1.在 CMD 命令行的寫法

DELIMITER // 
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM imooc_goddess WHERE id = p_id;
SELECT COUNT(id) FROM imooc_goddess INTO userNums;
END
DELIMITER ;

注意如果數(shù)據(jù)中含有 removeUserAndReturnUserNums 存儲過程，更改名稱，再創(chuàng)建新的存儲過程。

在 CMD 命令行調(diào)用

這里寫圖片描述

4.4 創(chuàng)建帶有多個OUT類型的存儲過程

輸入年齡，返回刪除的行數(shù)，和剩余行數(shù)。

這里寫圖片描述

SQL 語句

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT 
deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM imooc_goddess WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM imooc_goddess INTO userCounts;
END

5 自定義函數(shù)

用戶自定義函數(shù)（user-defined function,UDP）是一種對MySQL 擴展的途徑，其用法與內(nèi)置函數(shù)相同。

5.1 創(chuàng)建自定義函數(shù)模板

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

關(guān)于函數(shù)體

函數(shù)體由合法的SQL語句構(gòu)成
函數(shù)體可以是簡單的SELECT或INSERT語句
函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用 BEGIN….END 語句
復(fù)合結(jié)構(gòu)可以包含聲明，循環(huán)，控制結(jié)構(gòu)

5.2 自定義函數(shù)的創(chuàng)建和調(diào)用

1 自定義不帶參數(shù)的函數(shù)

這里寫圖片描述

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日:%H時:%i分:%s秒');

SELECT f1();

在 java 項目上調(diào)用函數(shù)（函數(shù)有返回值）時

CallableStatement cs = conn.prepareCall("{?= call f1()}");

2 自定義兩個參數(shù)的函數(shù)

輸入兩個值，計算其平均值。

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

3 創(chuàng)建具有復(fù)合機構(gòu)函數(shù)體的函數(shù)

插入一條新數(shù)據(jù)，user_name，返回該行的id。

CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT  imooc_goddess(user_name) VALUES (username);
return LAST_INSERT_ID();
END

這里寫圖片描述

6 通過 Navicat 查看存儲過程和函數(shù)的創(chuàng)建語句

在 Navicat 中點開函數(shù)欄，選擇你要查看的函數(shù)，右擊選擇對象信息。
這里寫圖片描述

選擇DDL欄（數(shù)據(jù)定義語言）

這里寫圖片描述

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

語句中的

DEFINER=`root`@`localhost`

是數(shù)據(jù)庫自己默認添加的。

復(fù)制數(shù)據(jù)定義語言創(chuàng)建過程時-發(fā)生錯誤

當我想直接復(fù)制數(shù)據(jù)定義語言，更改一個名稱創(chuàng)建另一個存儲過程時，總是創(chuàng)建不成功。
1、更改名稱（Error）

CREATE DEFINER=`root`@`localhost` PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

2、修改DEFINER(下面的語句都是不能成功創(chuàng)建）

CREATE DEFINER={'root`@`localhost`} PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER={'root`@`%`} PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER={root|localhost} PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER={root} PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER=root PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER=`CURRENT_USER`() PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

CREATE DEFINER=CURRENT_USER PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

3、刪除DEFINER
刪除DEFINER，可以創(chuàng)建成功

CREATE  PROCEDURE `myselectAll`()
BEGIN
SELECT * FROM imooc_goddess;
END

然而創(chuàng)建函數(shù)時卻可以帶 DEFINER
下面的語句可以創(chuàng)建成功

CREATE DEFINER=`root`@`localhost` FUNCTION `f2`(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS float(10,2) unsigned
RETURN (num1+num2)/2

復(fù)制數(shù)據(jù)定義語言，創(chuàng)建存儲過程要刪除DEFINER。

7 存儲過程與自定義函數(shù)的區(qū)別

存儲過程實現(xiàn)的功能要復(fù)雜一些，而函數(shù)的的針對性更強
存儲的過程可以返回多個值，函數(shù)只能有一個返回值
存儲過程一般獨立的執(zhí)行，而函數(shù)可以作為其他 SQL語句的組成部分來實現(xiàn)。
過程通俗易懂的說法：它只是將其中的程序執(zhí)行一遍
函數(shù) 通俗易懂的說法：它不但將其中的程序執(zhí)行一遍，還返回一個值

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊一鍵舉報。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻花（0） +1

來自： wwq圖書世界 > 《待分類》

舉報/認領(lǐng)

0條評論

發(fā)表

請遵守用戶評論公約

類似文章 更多

wwq圖書世界

關(guān)注對話

TA的最新館藏

IntelliJ IDEA 設(shè)置編碼為utf-8編碼（各種中文亂碼問題解決）
springboot一個接口多個實現(xiàn)類的調(diào)用方式
springboot通過不同的策略動態(tài)調(diào)用不同的實現(xiàn)類
Window下如何獲取文件md5值的命令
詳解Windows下的md5sum命令
MySql中使用函數(shù)實現(xiàn)遞歸查詢

喜歡該文的人也喜歡更多

熱門閱讀換一換

乡下人产国偷v产偷v自拍,国产午夜片在线观看,婷婷成人亚洲综合国产麻豆,久久综合给合久久狠狠狠9

MySQL存儲過程和自定義函數(shù)、Navicat、創(chuàng)建存儲過程和函數(shù)、調(diào)用存儲過程和函數(shù)、三種方式、In和Out類型參數(shù)、

1 MySQL存儲過程和函數(shù)

1.1 存儲過程

1.2 函數(shù)

2 我的 MySQL 數(shù)據(jù)表

3 存儲過程的創(chuàng)建模板和調(diào)用模板

3.1 創(chuàng)建存儲過程模板

3.2 調(diào)用存儲過程

4 創(chuàng)建的存儲過程

示例

方式一 CMD命令行方式

方式二 Navicat 新建查詢

方式三 Navicat 新建函數(shù)

4.1 創(chuàng)建不帶參數(shù)的存儲過程

4.2 創(chuàng)建帶有IN類型參數(shù)的存儲過程

4.3 創(chuàng)建帶有 IN和OUT類型參數(shù)的存儲過程

4.4 創(chuàng)建帶有多個OUT類型的存儲過程

5 自定義函數(shù)

5.1 創(chuàng)建自定義函數(shù)模板

5.2 自定義函數(shù)的創(chuàng)建和調(diào)用

1 自定義不帶參數(shù)的函數(shù)

2 自定義兩個參數(shù)的函數(shù)

3 創(chuàng)建具有復(fù)合機構(gòu)函數(shù)體的函數(shù)

6 通過 Navicat 查看存儲過程和函數(shù)的創(chuàng)建語句

復(fù)制數(shù)據(jù)定義語言創(chuàng)建過程時-發(fā)生錯誤

7 存儲過程與自定義函數(shù)的區(qū)別

MySQL存儲過程和自定義函數(shù)、Navicat、創(chuàng)建存儲過程和函數(shù)、調(diào)用存儲過程和函數(shù)、三種方式、In和Out類型參數(shù)、