一、ORACLE存储过程基本语法

1.语法基本结构

语法一：

CREATE [OR REPLACE PROCEDURE] 存储过程名字 (参数1 IN NUMBER,参数2 IN NUMBER) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

     body

END [存储过程名字]

语法二：

CREATE [OR REPLACE] PROCEDURE procedure_name (varible {IN|OUT|IN OUT} type) {IS|AS}

[varible { in | out | in out }] type;

[varible { in | out | in out }] type;

......

[CURSOR curor_name IS select subclause]

BEGIN

body

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出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;

8.用pl/sql developer debug

  连接数据库后建立一个Test WINDOW

  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

二、oracle 存储过程语法例子

   包的定义:

CREATE OR REPLACE PACKAGE myTest IS

type out_cur is ref cursor;

procedure writeCount(codeid in nvarchar2);

procedure testSandyInSert(codeid in nvarchar2,counts out number);

END myTest;

   存储过程的定义:

CREATE OR REPLACE PACKAGE myTest IS

procedure writeCount(codeid in nvarchar2) is

m_count number;

begin

m_count:=0;

select count(1) into m_count from code where code_id = codeid;

dbms_output.put_line('输入参数是'||codeid);

dbms_output.put_line('查询结果是'||m_count);

end writeCount;

procedure testSandyInSert(codeid in nvarchar2,counts out number) is

m_cur out_cur;

m_code_sn nvarchar2(50);

m_code_id nvarchar2(50);

m_code_name nvarchar2(50);

m_insertstr nvarchar2(50);

m_for number:=0;

begin

counts:=0;

open m_cur for select code_sn,code_id,code_name from code where code_id= codeid;

loop fetch m_cur into m_code_sn, m_code_id, m_code_name;

exit when m_cur %notfound;

case UPPER(m_code_sn)

when 'SP_CTRL_L' then m_code_sn:='0';

when 'dld' then m_code_sn:='1';

else m_code_sn:='3';

end case;

if UPPER(m_code_sn) ='SP_CTRL_L'

then dbms_output.put_line('条件成立执行,测试if语句的使用');

else dbms_output.put_line('条件不成立执行,测试if语句的使用');

end if;

m_insertstr:=''''||m_code_sn||''','''||m_code_id||''','''||m_code_name||'''';

begin

execute immediate 'insert into sandytest(col1,col2,col3) values('||m_insertstr||')';

dbms_output.put_line('插入表成功!');  

counts:=1;

exception

when others then dbms_output.put_line('插入表失败!');

end;

end loop;

for m_for in 1..10 loop

dbms_output.put_line('循环测试:'||m_for);

end loop;

close m_cur;

end testSandyInSert;

end myTest;

三、ORACLE存储过程语法

存储过程 包含三部分： 声明，执行部分，异常。    

分为：无参数存储过程 和 带参数存储过程。    

1、无参1，调用没有参数的存储过程

<%

set conn=server.CreateObject("adodb.connection")

set cmd=server.CreateObject("adodb.command")

strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn

set cmd.ActiveConnection=conn

cmd.CommandText="{call nono}"

set rs=cmc.exe 或者cmd.execute

set rs=cmd.Execute()

%>

2、一个输入的参数的存储过程

<%

set conn=server.CreateObject("adodb.connection")

set cmd=server.CreateObject("adodb.command")

strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn

set cmd.ActiveConnection=conn

cmd.CommandText="{call oneinput(?)}"

cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput )

cmd("@aaa")=100

cmd.Execute()

%>

3、一个输入参数和一个输出的参数

<%

set conn=server.CreateObject("adodb.connection")

set cmd=server.CreateObject("adodb.command")

strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn

set cmd.ActiveConnection=conn

cmd.CommandText = "{call oneinout(?,?)}"

cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)

cmd("@aaa")=10

cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")

%>

4、一个输入参数，一个输出参数，和一个返回值

<%

set conn=server.CreateObject("adodb.connection")

set cmd=server.CreateObject("adodb.command")

strconn="dsn=pubs;uid=sa;pwd"

conn.Open strconn

set cmd.ActiveConnection=conn

cmd.CommandText="{?=call onereturn(?,?)}"

cmd.Parameters.Append

cmd.CreateParameter("@return_value",adInteger,adParamReturnValue )

cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput )

cmd("@aaa")=10

cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)

cmd.Execute()

bbb=cmd("@bbb")

rrr=cmd("@return_value")

%>

5、带参存储过程实例  

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12330444/viewspace-263021/ ，如需转载，请注明出处，否则将追究法律责任。