oracle数据库编程存储过程、函数、循环语法

以下总结了数据库常用的存储过程、函数、循环等语法以及调用

引用类型变量的声明与使用:
declare
    pname person.xingmin%type;	/*** 引用型变量：引用某张表的某列的类型
                                                                  声明pname变量和person表中的xingmin类型一样。
                                                            %type：指定该变量的类型与表的某列的数据类型相同；**/
 
begin
   select distinct xingmin  into pname from person where nianling=17 ;  //赋值:给引用型变量赋值
   dbms_output.put_line(pname);          //输出pname
end;

*******************************************************************************************************************************

行类型变量的声明与使用：
 declare
      row_data student%rowtype;                     //%ROWTYPE类型的变量,它可以一次存储从数据库检索的一行数据。

    begin
       select * into row_data from student where id=1;
     dbms_output.put_line(row_data.id||':'||row_data.name);
    end;

注意：如果定义了％rowtype类型的变量,该变量的结构与定义表的结构完全相同,查询时必须使用*或者列举全部的字段信息。

*******************************************************************************************************************************

if操作：
declare
     --定义变量
     i number(10) := &val;      //定义变量i，并需要从键盘输入i的值
begin
     if i=1 then
       dbms_output.put_line('我是1');

     elsif i > 1 and i<10 then
      dbms_output.put_line('我大于1小于10');

     else 
      dbms_output.put_line('我是'||i);
     end if;
end;

*******************************************************************************************************************************

循环操作：
declare
    x number := 1;      //声明变量x并将1赋值给x
begin
    while x <= 10 loop
        dbms_output.put_line('X的当前值为:' || x);
        x := x + 1;
    end loop;
end;

*******************************************************************************************************************************

loop循环：
 --loop循环（条件成立时退出）
   declare
    pnum number:=1;
    begin
        loop
        --退出条件：循环变量大于10
        exit when pnum>10;
        --打印该变量的值
        DBMS_OUTPUT.PUT_LINE(pnum);
         --循环变量+1
         pnum:=pnum+1;
     end loop;
     end;

*******************************************************************************************************************************

for循环：
declare
    x number;                                  //声明变量
  begin
  x := 1;                                         //给初值
   for x in reverse 1 .. 10 loop        //reverse由大到小。需由小到大去掉reverse
   
     dbms_output.put_line('内:x=' || x);
  end loop;
   dbms_output.put_line('end loop:x=' || x); 
 end;

*******************************************************************************************************************************

case分支：
简单Case函数：
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘0’ THEN ‘女’
ELSE ‘其他’ END

Case搜索函数：
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘0’ THEN ‘女’
ELSE ‘其他’ END

*******************************************************************************************************************************

游标：
游标的状态：
%Found ：用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询一条记录是，返回true,Fetch语句（获取记录）执行情况True or False。

%NotFound : 最后一条记录是否提取出True or False。 到了游标尾部，没有记录了，就返回true 

%ISOpen ： 游标是否打开True or False。

%RowCount ：游标当前提取的行数 ,即获得影响的行数 1
  例一：  
    declare
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
    cursor c_cursor is                               //声明一个叫c_cursor的游标
        select ename, sal from emp where rownum < 11;
begin
    open c_cursor;                                //打开游标
    fetch c_cursor                                 //游标将记录传给两个变量
        into v_ename, v_sal;
    while c_cursor %found loop          //%found若记录还未读完返回true，循环继续
        dbms_output.put_line(v_ename || '---' || to_char(v_sal));
        fetch c_cursor
            into v_ename, v_sal;
    end loop;
    close c_cursor;                             //关闭游标
end;

  例二：带入参的游标

declare
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
    cursor c_cursor(p_sal emp.sal%type) is        //游标c_cursor参数为p_sal，参数类型与emp.sal类型相同
        select ename, sal from emp where sal >= p_sal;
begin
    open c_cursor(1000);                                 //传入参数
    fetch c_cursor
        into v_ename, v_sal;
    while c_cursor %found loop
        dbms_output.put_line(v_ename || '---' || to_char(v_sal));
        fetch c_cursor
            into v_ename, v_sal;
    end loop;
    close c_cursor;
end;

例三：带参for循环
declare
    cursor c_cursor(dept_no number default 10) is
        select dname, loc from dept where deptno <= dept_no;
begin
    dbms_output.put_line('dept_no参数值为30：');
    for c1_rec in c_cursor(30) loop
        dbms_output.put_line(c1_rec.dname || '---' || c1_rec.loc);
    end loop;
    dbms_output.put_line(chr(10) || '使用默认的dept_no参数值10：');
    for c1_rec in c_cursor loop
        dbms_output.put_line(c1_rec.dname || '---' || c1_rec.loc);
    end loop;
end;


*******************************************************************************************************************************

自定义异常;
declare
    v_empno emp.empno%type := &empno;
    no_result exception;                                            //定义异常名为no_result
begin
    update emp set sal = sal + 100 where empno = v_empno;
    if sql%notfound then
        raise no_result;                                            //通过raise抛出异常
    end if;
exception
    when no_result then                                       //捕获异常
        dbms_output.put_line('你的数据更新语句失败了!'); //处理异常
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end;


*******************************************************************************************************************************
函数的创建：
create or replace function get_salary(
　　dept_no number, emp_count out number)              //in为传入，out传出，in out可传入也可传出
  　　return number 
 　　is
  　　v_sum number;
  begin
  　　select sum(sal), count(*) into v_sum, emp_count
  　　from emp where deptno=dept_no;
  　　return v_sum;                                                         //函数返回值v_sum
 　　exception
 　　　　when no_data_found then 
　　　　dbms_output.put_line('你需要的数据不存在!');
 　　　　when others then 
 　　　　dbms_output.put_line(sqlcode||'---'||sqlerrm);
 end get_salary;

函数的调用：
declare
　　v_num number;
　　v_sum number;
begin
　　v_sum :=get_salary(30, v_num);
　　dbms_output.put_line('30号部门工资总和：'||v_sum||'，人数：'||v_num);
end;

*******************************************************************************************************************************

存储过程的创建与使用：
create or replace procedure proc_demo(dept_no   number default 10,                               //创建过程
                                      sal_sum   out number,
                                      emp_count out number) is
    --计算指定部门的工资总和，并统计其中的职工数量。                                         
begin
    select sum(sal), count(*) into sal_sum, emp_count from emp where deptno = dept_no;
exception
    when no_data_found then
        dbms_output.put_line('你需要的数据不存在!');
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end proc_demo;


--调用过程:
declare
    v_num number;
    v_sum number(8, 2);
begin
    proc_demo(30, v_sum, v_num);                                                                          // 调用过程,传入30，返回v_sum, v_num的值
    dbms_output.put_line('30号部门工资总和：' || v_sum || '，人数：' || v_num);      
    proc_demo(sal_sum => v_sum, emp_count => v_num);
    dbms_output.put_line('10号部门工资总和：' || v_sum || '，人数：' || v_num);
end;