Oracle存储过程语法

 

1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字 --名字可加双引号也可不加
(
    参数1 IN NUMBER,
    参数2 IN VARCHAR2
) IS

变量1 INTEGER :=0;--内部变量声明及赋值
变量2 DATE;
BEGIN

END 存储过程名字

--或者

END;

/

//oracle公司推荐使用VARCHAR2，不使用VARCHAR
//存储过程传入参数的in可省略，默认就是in
//没有bool类型，用int代替

2.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子：
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM tbl where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

 

DECLARE sname VARCHAR2(20) :='jerry';
SELECT ename INTO sname FROM tbl WHERE empno=1;

 

3.IF 判断
if (p2=1) then
  dbms_output.put_line('111');
 elsif p2=2 then
  dbms_output.put_line('222');
 else
  dbms_output.put_line('else');
end if;

--如果输出的行太长
v_pos:=1;
WHILE v_pos<= LENGTH(SqlStr) LOOP
            DBMS_OUTPUT.PUT_LINE(SUBSTR(SqlStr, v_pos, 200));
            v_pos:= v_pos+ 200;
END LOOP;

 

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

5.变量赋值
  V_TEST := 123;

6.用for in 使用cursor
declare
 cursor cc is select * from tbl order by id;       
    ccrec cc%rowtype;
    begin        
      for ccrec in cc loop
          begin
            dbms_output.put_line(ccrec.ID||'-'||ccrec.Name);
          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单步调试
本篇文章来源于 PHP资讯 原文链接：http://www.phpq.net/oracle/oracle-stored-procedure-syntax.html

 

9.CASE 语句

CASE selector
   WHEN value1 THEN action1;
   WHEN value2 THEN action2;
   WHEN value3 THEN action3;
   …..
   ELSE actionN;
END CASE;

CASE表达式

DECLARE
   temp VARCHAR2(10);
   v_num number;
BEGIN
   v_num := &i;
   temp := CASE v_num
     WHEN 0 THEN 'Zero'
      WHEN 1 THEN 'One'
     WHEN 2 THEN 'Two'
   ELSE
       NULL
   END;
   dbms_output.put_line('v_num = '||temp);
END;
/

CASE搜索语句

CASE
   WHEN (boolean_condition1) THEN action1;
   WHEN (boolean_condition2) THEN action2;
   WHEN (boolean_condition3) THEN action3;
   ……
   ELSE    actionN;
END CASE;

CASE搜索表达式 

DECLARE
   a number := 20;
   b number := -40;
   tmp varchar2(50);
BEGIN
   tmp := CASE
              WHEN (a>b) THEN 'A is greater than B'
              WHEN (a<b) THEN 'A is less than B'
              ELSE
              'A is equal to B'
              END;
   dbms_output.put_line(tmp);
END;
/

 

 或者用输出的方式调试

set serveroutput on;

declare 
   a varchar2(20);
begin
  select 用户名||密码 into a from login where rownum<2;
  dbms_output.put_line(a);
end;
/