oracle存储函数存储过程语法及基本示例

基本语法：

declare
说明部分（声明变量，游标，例外说明）
begin
语句序列（逻辑代码DML语句）
exception
例外处理语句
end;
/

个人理解：if…end if;loop…end loop;等语法的使用相当于java中的花括号，即告诉计算机我开始执行到结束的位置。

个人案例：

CREATE OR REPLACE PROCEDURE MYTEST1 --声明存储过程名字

AS

--声明部分s

  CURSOR EMP_CURSOR IS

    SELECT * FROM EMP;--cursor必须在方法外面声明

  COLVALUE EMP%ROWTYPE;

--声明部分e

BEGIN

  --逻辑部分s

  OPEN EMP_CURSOR;--打开游标

  LOOP--循环开始

    FETCH EMP_CURSOR

      INTO COLVALUE;--取出每次循环的数据

    DBMS_OUTPUT.PUT_LINE(COLVALUE.ename);--打印员工名字

    EXIT WHEN EMP_CURSOR%NOTFOUND;

  END LOOP;--循环结束

  CLOSE EMP_CURSOR;--关闭游标

   --逻辑部分e

END;

IF语句：

1.IF 条件 THEN 语句1;
语句2;
END IF;

2.IF 条件 THEN 语句;
ELSE 语句;
END IF;

3.IF 条件 THEN 语句;
ELSIF 语句;
ELSIF 语句;
…;
ELSE 语句;
END IF;

declare  
   num integer;  
   inputno number(10);  
begin  
   inputno :='&请输入';  
   select sal into num from scott.emp where empno=inputno;  
   if(num<2000) then  
     dbms_output.put_line('薪水低于2000');  
   elsif (num>=2000 and num<=3000) then  
     dbms_output.put_line('薪水在3000-2000之间');  
   else  
       dbms_output.put_line('薪水高于3000');   
   end if;  
end;  
  
  
--case语句  
declare  
     v_deptno number:=10;  
     v_sal number;  
begin  
 case v_deptno  
      when 10 then  v_sal:=1;  
      when 20 then  v_sal:=2;  
      else   
           v_sal:=3;  
 end case;  
 update scott.emp set sal=sal+v_sal where deptno=v_deptno ;  
 commit;  
end;  
  
  
--目标  使用case 语句统计员工薪水等级  
select ename,sal,case  
    when sal<2000 then '低等'   
    when sal>=2000 and sal<3000 then '中等'  
    when sal>=3000 and sal<4000 then '上等'  
    else  '高等'  
    end  薪水等级  
from emp;  
  
  
--循环控制语句  loop...exit when...end loop循环控制   
declare  
    v_i int:=1;  
begin  
    loop  
        v_i:=v_i+1;  
        exit when v_i=20;  
        dbms_output.put_line(v_i);  
    end loop;   
end;  
  
--while...loop...end loop循环控制   
--九九乘法表  
declare  
  v_i number:=1;  
  v_j number;  
begin  
  while(v_i<10)  loop  
      v_j:=1;  
      loop  
          dbms_output.put(v_j||'*'||v_i||'='||v_j*v_i||'  ');  
          v_j:=v_j+1;  
          exit when v_j>v_i;  
      end loop;  
      dbms_output.put_line('');  
      v_i:=v_i+1;  
  end loop;  
end;  
  
--for循环  
--for 循环变量 in [reverse] 循环下界..循环上界 loop   
       --循环处理语句段;    
--end loop;  
declare   
  v_sum number:=1;  
begin  
   for i in  1..5 loop  
       v_sum:=v_sum*i;  
   end loop;  
   dbms_output.put_line('阶乘结果:'||v_sum);  
end;  

变量类型：number varchar2 date CHAR CLOB…

引用类型：变量名 引用变量%type 例： ename emp.name%type

记录型变量： 变量名 记录变量%rowtype
案例：

    declare emp_list emp%rowtype   --声明数据
	begin 
	select * into emp_list from emp where empno='7839';
	dbms_output.put_line(emp_list.ename||'的薪水是'||emp_list.sal);

解释：相当于把7839员工数据查出来放入emp_list中，再从中取出名字和薪水

循环语句：
loop循环：

create or replace procedure pro_test_loop is  
i number;  
begin  
i:=0;  
loop  
  ii:=i+1;  
  dbms_output.put_line(i);  
  if i>5 then  
    exit;  
  end if;  
end loop;  
end pro_test_loop; 

while循环：

create or replace procedure pro_test_while is  
i number;  
begin  
i:=0;  
while i<5 loop  
  ii:=i+1;  
  dbms_output.put_line(i);  
end loop;  
end pro_test_while; 

for循环1：

create or replace procedure pro_test_for is  
i number;  
begin  
i:=0;  
for i in 1..5 loop  
  dbms_output.put_line(i);  
end loop;  
end pro_test_for; 

for循环2：

create or replace procedure pro_test_cursor is  
userRow t_user%rowtype;  
cursor userRows is  
select * from t_user;  
begin  
for userRow in userRows loop  
    dbms_output.put_line(userRow.Id||','||userRow.Name||','||userRows%rowcount);  
end loop;  
end pro_test_cursor;  

cursor:游标，相当于java中的list集合，可以存多组数据
fetch：获取当前指针指向的行数据

游标的属性返回值类型意义

%ROWCOUNT 整型获得FETCH语句返回的数据行数
%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型与%FOUND属性返回值相反
%ISOPEN 布尔型游标已经打开时值为真，否则为假

【填充，无意义】	【填充，无意义】	【填充，无意义】
CURSOR—>	第一行	fetch 字段1 into 变量1;
【填充，无意义】	第二行	上述语句将第一行数据取出放到变量1中。
【填充，无意义】	第三行
【填充，无意义】	…
【填充，无意义】	第N行

集合：

对于显式游标的运用分为四个步骤：
 定义游标—Cursor [Cursor Name] IS;
 打开游标—Open [Cursor Name];
 操作数据—Fetch [Cursor name]
 关闭游标—Close [Cursor Name],这个Step绝对不可以遗漏。

–定义一个带参数的光标
cursor 游标名(参数名 类型)is select ename from emp where deptno=实参;
open 游标名(10)–10相当于参数，10号部门。
既游标数据为：select ename from emp where deptno=10;

1）直接声明

declare

 cursor emp_cur  is select *  from emp;

 emp_record emp%rowtype;

begin 

 open emp_cur;

 loop 

  fetch emp_cur  into emp_record;

  exit when  emp_cur%notfound;

  dbms_output.put_line('name is:' || emp_record.ename ||' and sal is:' || emp_record.sal);

 end loop;
 
 close emp_cur;

end;
/

(2)ref cursor:分为强类型（有return子句的）和弱类型，强类型在使用时，其返回类型必须和return中的类型一致，否则报错，而弱类型可以随意打开任何类型。

例如：

强类型

declare

 type emp_cur_type  is ref cursor return emp%rowtype;

 emp_cur emp_cur_type;

 emp_record emp%rowtype;

begin

 open emp_cur  for select *  from  emp;

 loop

  fetch emp_cur  into emp_record;

  exit when emp_cur%notfound;

  dbms_output.put_line('name is:' ||  emp_record.ename || ' and sal is:' || emp_record.sal);

 end loop;

 close emp_cur;

 --open emp_cur for select * from dept; 错误的，类型不一致。

 --close emp_cur;

end;

/

弱类型：

declare

 type emp_cur_type is ref cursor;

 emp_cur emp_cur_type;

 emp_record emp%rowtype;

 dept_record dept%rowtype;

begin

 open emp_cur for select *  from emp;

 loop

  fetch emp_cur into emp_record;

  exit when emp_cur%notfound;

  dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' || emp_record.sal);

 end loop;

 close emp_cur;

 

 open emp_cur  for select *  from dept; --可再次打开，不同类型的

 loop

  fetch emp_cur  into dept_record;

  exit when emp_cur%notfound;

  dbms_output.put_line('dname is:' || dept_record.dname);

 end loop;

 close emp_cur;

end;

/

预定义说明的部分ORACLE异常错误

set serveroutput on;  
declare  
    var_name varchar(60);  
begin  
    select ename into var_name from emp  
    where deptno=&deptno;  
exception  
    when no_data_found then  
        dbms_output.put_line('没有匹配数据!');  
    when too_many_rows then  
        dbms_output.put_line('返回多行数据!');  
    when others then  
        dbms_output.put_line('提示错误不明!');  
end;