欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-07 08:00:43
...
基本语法:

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异常错误
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;