Oracle PL/SQL 自定义函数、存储过程

讲函数之前，先介绍一下程序结构

3.程序结构

　　新建一个测试窗口，举一个小例子

declare
  -- 声明变量，包括游标
begin
  -- 执行部分
  dbms_output.put_line('hello world!');
  
  --异常处理
end;

　　变量声明时，类型可以用字段类型，也可以直接引用表的字段类型　

　　举个小例子：

declare
  -- 声明变量，包括游标
  pagename   varchar2(10);
  lang1    langmap.lang1%type;
begin
  -- 执行部分

  select l.pagename, l.lang1
    into pagename, lang1
    from langmap l
   where l.pagename = 'repair'
     and l.msgid = '10';
  dbms_output.put_line('配置项目:' || pagename || ' 状态:' || lang1);

  --异常处理
end;

　　或者直接引用表的行类型

 　　举个小例子：

declare
  -- 声明变量，包括游标
  v_langmap langmap%rowtype;
begin
  -- 执行部分

  select *
    into v_langmap
    from langmap l
   where l.pagename = 'repair'
     and l.msgid = '10';
  dbms_output.put_line('配置项目:' || v_langmap.pagename || ' 状态:' ||
                       v_langmap.lang1);

  --异常处理
end;

 

 

4.判断

begin
  if  条件   then   内容
    
  elsif  条件  then   内容
    
  else    内容
    
  end if;
end;

5.循环

begin
  loop 
     exit when  条件
     
     --语法部分  
  
  end loop;
end;

6.游标

　　不带参

declare 
  cursor c_notice is select t.noticeno,t.title from tenantnotice t;
  v_no tenantnotice.noticeno%type;
  v_title tenantnotice.title%type;
begin
  --打开游标
  open c_notice;
  
  --遍历游标
  loop 
    fetch c_notice into v_no,v_title;
    exit when c_notice%notfound;
    dbms_output.put_line('编号：'|| v_no||',标题：'|| v_title);
  end loop;
  
  --关闭游标
  close c_notice;
end;

　　带参

declare 
  cursor c_notice(v_noticeno tenantnotice.noticeno%type)
       is select t.noticeno,t.title from tenantnotice t where t.noticeno=v_noticeno;
  v_no tenantnotice.noticeno%type;
  v_title tenantnotice.title%type;
begin
  --打开游标
  open c_notice('201811290014');
  
  --遍历游标
  loop 
    fetch c_notice into v_no,v_title;
    exit when c_notice%notfound;
    dbms_output.put_line('编号：'|| v_no||',标题：'|| v_title);
  end loop;
  
  --关闭游标
  close c_notice;
end;

　　select  into 是隐式游标，只能返回一行（存储过程输出值）

　　For循环读取游标,会自行打开和关闭

declare
    cursor cur_device(v_fid varchar) is select * from factory_device where fid=v_fid;
begin
    for v_device in cur_device('002')
    loop
        dbms_output.put_line(v_device.device_code);
    end loop;
end;

 

7.存储过程

　　

 　　存储过程没有declare，变量直接在is（或者as，相同的效果）和begin之间声明就行

　　1）无参无返回值

create or replace procedure kxy_Proc_hello is
--这里可以声明变量

begin
  dbms_output.put_line('hello world');
end kxy_Proc_hello;

　　可在测试脚本中直接调用

begin
  kxy_Proc_hello;
end;

　　也可以在命令行中调用

exec kxy_Proc_hello;

　　命令行有时候看不到结果，先执行

set serveroutput on

　　2）带参无返回值

create or replace procedure kxy_Proc_GetTitle(i_no in tenantnotice.noticeno%type)
is
       v_title tenantnotice.title%type;
begin
  select t.title into v_title from tenantnotice t where t.noticeno=i_no;
  dbms_output.put_line(v_title);
end;

　　3）无参有返回值

 

　　4）带参有返回值

create or replace procedure kxy_Proc_GetTitle(i_no in tenantnotice.noticeno%type,o_title out tenantnotice.title%type)
is
begin
  select t.title into o_title from tenantnotice t where t.noticeno=i_no;
end;

　　调用

declare 
  v_title tenantnotice.title%type;
begin
  kxy_proc_gettitle('201811290014', v_title);
 -- kxy_proc_gettitle('201811290014',o_title => v_title);--这样写也可以
  dbms_output.put_line(v_title);
end;

 8.函数

　　函数必须有返回值，可在sql语句中直接调用，可以不传参，也可以使用 in/out参数 输入输出

　　1）无参

create or replace function kxy_Fun_hello
       return varchar2
is
--这里可以声明变量
       msg varchar2(50):= 'hello world';
begin
  return msg;
end;

select kxy_Fun_hello() from dual;