《mysql必知必会》读书笔记--存储过程的使用

　　注意：如果使用mysql的命令行程序，";"是分隔符，存储过程中也有";"这样会造成使用存储过程中的SQL出现语法错误，解决办法是临时更改语句的分隔符：

DELIMITER <span style="color: #808080">//</span><span style="color: #000000">
....
DELIMITER ;</span>

　　其中,除了\ 之外，任何字符都可以用作语句分隔符

删除存储过程

<span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PROCEDURE</span> productpring;

　　只是给出存储过程的名字，不用跟()

存储过程参数的使用

<span style="color: #008080">--</span><span style="color: #008080">创建存储过程</span>
<span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> ordertotal(
</span><span style="color: #808080">IN</span> onumber <span style="color: #0000ff">INT</span><span style="color: #000000">,
OUT ototal </span><span style="color: #0000ff">DECIMAL</span>(<span style="color: #800000; font-weight: bold">8</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">)
)
</span><span style="color: #0000ff">BEGIN</span>
<span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">sum</span>(item_price<span style="color: #808080">*</span><span style="color: #000000">quantity)
</span><span style="color: #0000ff">FROM</span><span style="color: #000000"> orderitems
</span><span style="color: #0000ff">WHERE</span> order_num <span style="color: #808080">=</span><span style="color: #000000"> onumber
</span><span style="color: #0000ff">INTO</span><span style="color: #000000"> ototal;
</span><span style="color: #0000ff">END</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080">调用</span>
CALL ordertotal(<span style="color: #800000; font-weight: bold">234567</span>, <span style="color: #008000">@total</span><span style="color: #000000">)
</span><span style="color: #008080">--</span><span style="color: #008080">显示结果</span>
<span style="color: #0000ff">SELECT</span> <span style="color: #008000">@total</span>

智能存储过程 

　　前面的存储过程只是为了解及学习使用，实际应用中的存储过程不是像上面的一样简单。如下是一较为复杂的存储过程：

<span style="color: #008080">--</span><span style="color: #008080"> 创建一个名为ordertotal的存储过程</span><span style="color: #008080">
--</span><span style="color: #008080"> 参数：onumber-订单号 taxable-是否上税（0，不需要 1，需要） ototal-返回的合计</span>
<span style="color: #0000ff">CREATE</span><span style="color: #000000"> PROCECURE ordertotal(
</span><span style="color: #808080">IN</span> onumber <span style="color: #0000ff">INT</span><span style="color: #000000">,
</span><span style="color: #808080">IN</span><span style="color: #000000"> taxable BOOLEAN,
OUT ototal </span><span style="color: #0000ff">DECIMAL</span>(<span style="color: #800000; font-weight: bold">8</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">)
) COMMENT </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">Obtain order total, optionally adding tax</span><span style="color: #ff0000">‘</span>
<span style="color: #0000ff">BEGIN</span>
<span style="color: #008080">--</span><span style="color: #008080"> 定义临时变量total-存储查询到的合计</span>
<span style="color: #0000ff">DECLARE</span> total <span style="color: #0000ff">DECIMAL</span>(<span style="color: #800000; font-weight: bold">8</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">)
</span><span style="color: #008080">--</span><span style="color: #008080"> 临时变量taxrate-税点</span>
<span style="color: #0000ff">DECLARE</span> taxrate <span style="color: #0000ff">INT</span> <span style="color: #0000ff">DEFAULT</span> <span style="color: #800000; font-weight: bold">6</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 得到查询的合计</span>
<span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">Sum</span>(item_price <span style="color: #808080">*</span><span style="color: #000000"> quantity) 
</span><span style="color: #0000ff">FROM</span><span style="color: #000000"> orderitems
</span><span style="color: #0000ff">WHERE</span> order_num <span style="color: #808080">=</span><span style="color: #000000"> onumber
</span><span style="color: #0000ff">INTO</span><span style="color: #000000"> total;
</span><span style="color: #008080">--</span><span style="color: #008080"> 判断是否需要上税</span>
<span style="color: #0000ff">IF</span> taxable <span style="color: #0000ff">THEN</span>
<span style="color: #008080">--</span><span style="color: #008080"> 需要，将上税部分添加进合计</span>
<span style="color: #0000ff">SELECT</span> total<span style="color: #808080">+</span>(total<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #808080">*</span>taxrate) <span style="color: #0000ff">INTO</span><span style="color: #000000"> total;
</span><span style="color: #0000ff">END</span> <span style="color: #0000ff">IF</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 将最终的合计返回</span>
<span style="color: #0000ff">SELECT</span> total <span style="color: #0000ff">INTO</span><span style="color: #000000"> ototal;
</span><span style="color: #0000ff">END</span>;

《mysql必知必会》读书笔记--存储过程的使用

标签：number   ice   order   option   efault   解决   定义   命令   行存储