Oracle 存储过程

语法

• 输出

  begin
    -- Test statements here
    DBMS_OUTPUT.put_line('Hello World');
  end;
  

变量

普通数据类型

• char
• varchar2
• date
• number
• boolen
• long

特殊变量类型

• 引用型变量
• 记录型变量

变量声明方式

变量名 变量类型（变量长度） 如v_name varchar2(20);

变量赋值方式

直接赋值

• 直接赋值 如 v_name := '张三'

语句赋值

• 使用select... into ...赋值

示例

declare 
  -- 姓名
  v_name VARCHAR2(20) := '张三';
  -- 薪水
  v_sal number;
  v_addr VARCHAR2(200);
begin
  -- Test statements here
  v_sal := 5000;
  select '河北省保定市' into v_addr from dual;
  -- 打印
  Dbms_Output.put_line('姓名：'||v_name||',薪水：'||v_sal||',地址：'||v_addr);
  
end;

引用类型变量

• 类型长度取决于表中字段的类型和长度
  • 表明.列名%TYPE指定变量的类型和长度，例如：v_name emp.ename%TYPE;

declare 
  -- 姓名
  v_name emp.ename%TYPE;
  -- 薪水
  v_sal emp.sal%TYPE;
begin
  
  select ename,sal into v_name, v_sal from emp where empno = 7389;
  
  Dbms_Output.put_line('姓名：'||v_name||',薪水：'||v_sal);
  
end;

记录型变量

• 用来接收一整行记录，相当于java的一个对象
• 语法
  • 变量名 表明%ROWTYPE, 例如：v_emp emp%rowtype

流程控制

一、条件

语法

BEGIN
    IF 条件一 THEN 执行一
    elsif 条件而 THEN 执行二
    else 执行三
    END IF;
END;

示例

declare 
  -- 声明变量接收数据数量
  v_count number;
begin
  
  select count(*) into v_count from cust_info;
  
  if v_count>10 then
    dbms_output.put_line('表中记录数超过了10条：'||v_count);
    
    elsif v_count>=10 then
    dbms_output.put_line('表中记录数10--20：'||v_count);
    
    else 
      dbms_output.put_line('表中记录数不到10条：'||v_count);
    
  end if;
  
end;

二、循环

loop语法

BEGIN
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP
END;
    

示例

-- Created on 2020/10/1 星期四 by ADMINISTRATOR 
declare 
  -- 声明循环变量
  v_num number := 1;
begin
  
  loop
          
    exit when v_num>10;
    
    dbms_output.put_line(v_num);
    
    v_num := v_num + 1;
  
  end loop;
  -- Test statements here
  
end;

游标

• 用于临时存储查询返回的多行数据，可以通过游标遍历逐行访问处理该结果集的数据
• 使用方式： 声明->打开->读取->关闭

语法

• 游标声明

  • CURSOR 游标名[(参数列表)] IS查询语句；

• 游标打开

  • OPEN 游标名；

• 游标取值

  • FETCH 游标名 INTO 变量列表；

• 游标关闭

  • CLOSE 游标名；

• 游标属性

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

%NOTFOUND是在游标中找不到元素时候返回true，通常用来判断退出循环

示例

declare 
  -- 声明游标
  CURSOR V_CUST IS SELECT CUST_NAME,ID_NO FROM CUST_INFO;
  
  -- 声明变量接收游标中的元素
  v_name cust_info.cust_name%Type;
  v_no cust_info.id_no%type;
  
  
begin
  
  --打开游标
  open V_CUST;
       
       -- 遍历游标
       loop
         
       --获取游标中的数据
       FETCH V_CUST INTO v_name,v_no;
          
       -- 退出条件
       exit when V_CUST%notfound;
       
       -- 输出
       dbms_output.put_line('姓名：'||v_name||'----证件号：'||v_no);
       
       end loop;
  
  --关闭游标
  close V_CUST;
  
end;

带参游标

declare 
  -- 声明游标
  CURSOR V_CUST(V_CUST_TYPE CUST_INFO.CUST_TYPE%TYPE) IS SELECT CUST_NAME,ID_NO FROM CUST_INFO WHERE CUST_TYPE = V_CUST_TYPE;
  
  -- 声明变量接收游标中的元素
  v_name cust_info.cust_name%Type;
  v_no cust_info.id_no%type;
  
  
begin
  
  --打开游标
  open V_CUST(2);
       
       -- 遍历游标
       loop
         
       --获取游标中的数据
       FETCH V_CUST INTO v_name,v_no;
          
       -- 退出条件
       exit when V_CUST%notfound;
       
       -- 输出
       dbms_output.put_line('姓名：'||v_name||'----证件号：'||v_no);
       
       end loop;
  
  --关闭游标
  close V_CUST;
  
end;

存储过程

• 语法

  CREATE OR REPLACE PROOCEDURE 过程名称[（过程参数）] IS
  BEGIN
  END[过程名称]
  

• 示例

  create or replace procedure p_hello is
  --声明变量在这里
  
  begin
  
         dbms_output.put_line('hello!');  
  
  end p_hello;
  

• 调用

  begin
    --调用存储过程
    p_hello;
    
  end;
  

一、带输入参数存储过程

create or replace procedure p_query(in_id in cust_info.cust_id%type) is
--声明变量在这里
v_adde cust_info.adde%type;
v_name cust_info.cust_name%type;
begin
   
       --查询
       select cust_name,adde into v_name,v_adde from cust_info where cust_id = in_id;

       dbms_output.put_line(v_name || '--' || v_adde);  

end p_query;