SQl编程存储过程

• 调用

<code class="lang-sql">调用存储过程使用 <span class="hljs-keyword">CALL SP_NAME()
删除存储过程 <span class="hljs-keyword">DROP <span class="hljs-keyword">PROCEDURE SP_NAME
查看已定义存储过程     <span class="hljs-keyword">SHOW <span class="hljs-keyword">PROCEDURE <span class="hljs-keyword">STATUS/<span class="hljs-keyword">SHOW <span class="hljs-keyword">CREATE <span class="hljs-keyword">PROCEDURE SP_NAME(详细信息)
</span></span></span></span></span></span></span></span></span></code>

• 创建数据库

<code class="lang-sql"><span class="hljs-keyword">create <span class="hljs-keyword">database <span class="hljs-keyword">if <span class="hljs-keyword">not <span class="hljs-keyword">exists demo1 <span class="hljs-keyword">default  <span class="hljs-built_in">character <span class="hljs-keyword">set <span class="hljs-string">‘utf8‘;
</span></span></span></span></span></span></span></span></span></code>

• 创建数据表

<code class="lang-sql"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-string">`user` (
<span class="hljs-string">`id` <span class="hljs-built_in">int(<span class="hljs-number">11) <span class="hljs-keyword">NOT <span class="hljs-literal">NULL AUTO_INCREMENT,
<span class="hljs-string">`name` <span class="hljs-built_in">varchar(<span class="hljs-number">32) <span class="hljs-keyword">DEFAULT <span class="hljs-literal">NULL,
PRIMARY <span class="hljs-keyword">KEY (<span class="hljs-string">`id`)
) <span class="hljs-keyword">ENGINE=<span class="hljs-keyword">InnoDB <span class="hljs-keyword">DEFAULT <span class="hljs-keyword">CHARSET=utf8
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

• 定义结束符号

<code class="lang-sql"><span class="hljs-comment">-- 定义 结束符
DELIMETER $$
</span></code>

• 函数无参数

<code class="lang-sql">CREATE PROCEDURE loop_insert_post()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i<1000 DO
INSERT INTO user(`name`,`addtime`)values(concat(‘JM‘,i),now());
SET i = i+1;
END WHILE;
END $$
</code>

• 调用函数

<code class="lang-sql"><span class="hljs-comment">-- 调用函数
<span class="hljs-keyword">CALL loop_insert_post //
</span></span></code>

• 恢复mysql 默认结束符

<code class="lang-sql">DELIMETER ；</code>

SQl编程存储过程

标签：数据表   drop   database   begin   set   character   te pro   uil   sql