存储过程语法结构

1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(

<nobr onmousemove="kwM(3);" onmouseout="kwL(event, this);" onmouseover="kwE(event,3, this);" oncontextmenu="return false;" target="_blank" onclick="return kwC();" style="border-bottom: 1px dotted rgb(102, 0, 255); text-decoration: underline; color: rgb(102, 0, 255); background-color: transparent;" id="key2">参数</nobr>1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END <nobr onmousemove="kwM(2);" onmouseout="kwL(event, this);" onmouseover="kwE(event,2, this);" oncontextmenu="return false;" target="_blank" onclick="return kwC();" style="border-bottom: 1px dotted rgb(102, 0, 255); text-decoration: underline; color: rgb(102, 0, 255); background-color: transparent;" id="key1">存储</nobr>过程名字

<script type="text/javascript"><!-- google_ad_client = "pub-1572879403720716"; google_ad_width = 300; google_ad_height = 250; google_ad_format = "300x250_as"; google_ad_type = "text_image"; google_ad_channel ="0168087865"; google_alternate_ad_url ="http://www.qqread.com/0000js/google300.htm"; google_color_border = "F9FCFE"; google_color_bg = "F9FCFE"; google_color_link = "1F3A87"; google_color_url = "FF6666"; google_color_text = "000000" google_language = 'zh-CN'; //--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>2.SELECT INTO STATEMENT

将select查询的<nobr onmousemove="kwM(0);" onmouseout="kwL(event, this);" onmouseover="kwE(event,0, this);" oncontextmenu="return false;" target="_blank" onclick="return kwC();" style="border-bottom: 1px dotted rgb(102, 0, 255); text-decoration: underline; color: rgb(102, 0, 255); background-color: transparent;" id="key0">结果</nobr>存入到变量中，可以同时将多个列存储多个变量中，必须有一条
<nobr onmousemove="kwM(4);" onmouseout="kwL(event, this);" onmouseover="kwE(event,4, this);" oncontextmenu="return false;" target="_blank" onclick="return kwC();" style="border-bottom: 0px dotted; text-decoration: underline; color: rgb(102, 0, 255); background-color: transparent;" id="key3">记录</nobr>，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...

3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;

4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

5.变量赋值
V_TEST := 123;

6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;

7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;