oracle存储过程及函数的使用

最新推荐文章于 2021-12-22 16:37:20 发布

jeff_qiu_prod

最新推荐文章于 2021-12-22 16:37:20 发布

阅读量338

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle 存储过程基础语句

本文链接：https://blog.youkuaiyun.com/qq_36760847/article/details/95940903

Oracle 专栏收录该内容

3 篇文章

订阅专栏

该博客围绕Oracle存储过程展开，介绍了开始前的准备工作，详细阐述了存储过程的定义、输入输出调用、基本使用案例、游标案例等内容，还涉及函数、定时任务、数组申明遍历、断点调试以及DBLink创建等方面，为学习Oracle存储过程提供了全面参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考资料

https://blog.youkuaiyun.com/uniqueweimeijun/article/details/82668835
https://blog.youkuaiyun.com/m0_38025207/article/details/80717119
https://blog.youkuaiyun.com/huxiangen/article/details/81079834

开始前的准备

create table QJF_STUDENT
(
  sid    NUMBER,
  sname  VARCHAR2(20),
  sage   NUMBER,
  sgender VARCHAR2(2)
)

存储过程

-- 基本的新增操作
-- 可以直接在SQL windows上执行
create or replace procedure qjf_addstudent(
              sid     in qjf_student.sid%type,
              sname   in qjf_student.sname%type,
              sage    in qjf_student.sage%type,
              sgender in qjf_student.sgender%type
              )
is                                         
begin
  insert into qjf_student
    (sid, sname, sage, sgender)
  values
    (sid, sname, sage, sgender);
end;

-- 可以直接在SQL windows上执行
-- 下面是调用存储过程的两种方式

-- 第一种
begin
  qjf_addstudent(2,'小李',23,'女');
end;

--  第二种
call qjf_addstudent(1,'汉汉',23,'男');
commit;

-- 基本的查询删除操作
create or replace procedure qjf_delstudent(
           s  in qjf_student.sid%type,
           sn out qjf_student.sname%type
)
 is
begin  --将查询到的sname值赋给输出参数sn 
    select sname into sn from qjf_student where sid = s;
    delete from qjf_student where sid = s;  
  --异常处理 
    exception  --未查询相关的sname数据时抛出异常
      when no_data_found then sn := '未找到数据';
end;

-- 注释: s为输入参数，sn为输出参数使用out关键字标识

补充说明：
在参数列表中，参数 in 表.字段%type(如 sid in qjf_student.sid%type)，表示参数sid的数据类型和qjf_student表的字段sid一致。

输入输出存储过程的调用

declare sname
  qjf_student.sname%type;--声明一个sname的变量，来保存存储过程输出的结果
begin
   --调用存储过程

   qjf_delstudent(1,sname);
   --打印存储过程输出的结果
   --此句添加至存储过程中也可，
   -- dbms_output.put_line(),可添加至任何begin...end;语句中间
   dbms_output.put_line(sname);
end;

基本使用案例

-- 第一个 基本的创建 
create or replace procedure qjf_add(
                         v1 in number,
                         v2 in number,
                         v3 out number)
is
begin
       v3:=v1+v2;
end;

-- 这里进行调用输出
declare
  v3 number(10);
begin 
  v3:=0;
  qjf_add(100,200,v3);
  DBMS_OUTPUT.PUT_LINE('输出结果：'||v3);
END; 

-- 第二个 基本的循环语句
declare
     vcc number;--定义数字变量
begin
  vcc:=1;--赋值
  loop--循环语句的开始
    exit when vcc>11;--退出条件
    dbms_output.put_line(vcc);
    vcc:=vcc+1;
  end loop;--循环语句的结束
end;

游标案例

declare 
cursor cur is select e.ename,e.empno from emp e;
 v_name emp.ename%type;
 v_num emp.empno%type;
begin
  -- Test statements here
  open cur;
  loop
    fetch cur into v_name,v_num;
    exit when cur %notfound;
    dbms_output.put_line('姓名:' || v_name || '年龄:' ||v_num);
  end loop;
  close cur;
end;

存储过程返回数据集创建及调用

-- 定义包头
create or replace package qjf_pkg_alen
as
type cursorRef is ref cursor; --定义游标引用类型
procedure query(u_id number,cursor_ref out cursorRef); --定义存储过程声明
end qjf_pkg_alen;

-- 定义包体
create or replace package body qjf_pkg_alen --注意这里的包名要和定义包头的包名一致
 as
  procedure query(u_id number, cursor_ref out cursorRef) --这里的存储过程要和包头中的一致参数列表中的参数名也要一致
   is
  begin
    if u_id = 0 then
      --如果传入的id为0则查询所有数据
      open cursor_ref for
        select sid, sname from qjf_student;
    else
      --如果传入的id为非0则查询指定id的数据
      open cursor_ref for
        select sid, sname from qjf_student where sid = u_id; --根据查询打开游标
    end if;
  end query;
end qjf_pkg_alen;

-- 调用
declare
   return_cursor qjf_pkg_alen.cursorRef; --定义接收返回数据集的变量
   v_id number(4);   --定义接收id的变量
    uname varchar2(50);  --定义接收name的变量
begin 
     qjf_pkg_alen.query(0,return_cursor);  --调用包中的存储过程返回游标引用
     loop fetch return_cursor into v_id,uname; --循环获取游标中每一行的数据
         exit when return_cursor%notfound;          --游标结束时退出
         dbms_output.put_line('id:'||v_id||' name:'||uname); --将获取到的值打印出来
     end loop;    --循环结束
     close return_cursor;  --关闭游标
end;

存储过程使用 DEMO


--  存储过程使用 DEMO   
create or replace procedure DEMO_PROCEDURE (
 			  PARAM_IN_A   IN   VARCHAR2,    --  入参  A
              RETURN_MSG OUT VARCHAR2        --  返回信息
)
Authid CURRENT_USER   -- 授权给当前用户
Is
   PARAM_A     VARCHAR2(32);                --  参数  A
   PARAM_B     VARCHAR2(32);                 --   参数   B 
   PARAM_C     VARCHAR2(32);                 --   参数   C 
   ID_STR     VARCHAR2(32);                 --   主键
   TABLE_EXIST     VARCHAR2(32);              --   判断是否存在对应表的依据
   SQL_EXEC_STR   VARCHAR2(4000);            --  待执行的sql语句
   SQL_CREATE_STR   VARCHAR2(4000);            --  待执行的sql语句

Begin
   -------------参数转换准备start--------------
   PARAM_A := PARAM_IN_A;--   可能会进行转换操作
   PARAM_B :='2020-07-17 17:01:01';
   -------------参数转换准备end--------------

   --统计条件分析
    if PARAM_A is null OR Trim(PARAM_A) is null then
        RETURN_MSG:='PARAM_A不能为空！';
        return;
   end if;
     
   
   
   select to_char(to_date(PARAM_B ,'YYYY-MM-DD HH24:ss:mi'),'yyyy-mm-dd') into PARAM_C from dual;

   select  sys_guid() INTO ID_STR  from dual;
   
   --  是否存在  DEMO_PROCEDURE_TAB   不存在就创建
   select count(*) into TABLE_EXIST from tabs where table_name='DEMO_PROCEDURE_TAB';
   
   if ( TABLE_EXIST = 0 ) then
     SQL_CREATE_STR := '
	 create table DEMO_PROCEDURE_TAB(
	    ID varchar(32),
	    Name varchar(20)
		)'
	 ;
     EXECUTE IMMEDIATE SQL_CREATE_STR;
    end if;



   SQL_EXEC_STR := 'insert into DEMO_PROCEDURE_TAB(ID,Name) values ( '''||ID_STR||''',''tank'')';


   --  插入列表数据   execute immediate    当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号
   /**
    execute immediate
    简单来说 就是你一个存储过程当中 创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，
	但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，
	所以无法编译成功，而把insert into语句加如到 execute immediate之后 则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行
   */

   EXECUTE IMMEDIATE SQL_EXEC_STR; 
   
   commit;
	

Exception
   When Others Then
   RETURN_MSG := '错误原因：' || SUBSTR(SQLERRM, 1, 3000);
End DEMO_PROCEDURE;
/

函数

-- 根据字典类型和代码值获取对应的中文 作为demo
CREATE OR REPLACE FUNCTION fn_getDictName(p_type IN VARCHAR2, p_key IN VARCHAR2) RETURN varchar2
IS dic_name VARCHAR2(32);
BEGIN
  SELECT NAME INTO dic_name FROM DICTS t WHERE t.ITEMCODE=p_type AND CODE=p_key;
RETURN (dic_name);
END fn_getDictName;

在这里插入图片描述

CREATE OR REPLACE FUNCTION fn_test(v_param in varchar2 ) return varchar2
is
  return_result varchar2(100);
  temp_str  varchar2(100);
  -- 游标的声明
  cursor test_cur(p_test VARCHAR2) is
  select '1' id ,'小黄' name  from dual  union all
  select '2' id ,'小黑' name from dual;

BEGIN
    --常用处理
    if v_param is null  then
           dbms_output.put_line('函数入参为空');
    elsif  v_param='测试' then
        dbms_output.put_line('函数入参为：测试');
     else
       dbms_output.put_line('函数入参是其他的东西');
   end if;  
    SELECT '列字段' INTO temp_str FROM  dual;
   -- 隐性打开游标
   for item in test_cur('入参') loop
        dbms_output.put_line('item.id:'||item.id||' item.name:'||item.name);
   end loop;
    --返回值的处理
  return_result:='我是返回值';
RETURN (return_result);
END fn_test;

定时任务

-- DBMS_JOB
declare job_test number;
begin
sys.dbms_job.submit(
job_test ,-- job 名称
'XXXXXX;', -- 存储过程
sysdate,-- job启动后下次执行时间
'trunc(sysdate,''mi'') + 1/ (24*60)'); --每分钟执行一次
commit;
end;

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'testJob;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => sysdate,  /*初次执行时间-立即执行*/  
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
      );  
  commit;
end;


----------------
https://www.cnblogs.com/xueershewang/p/7355286.html?ivk_sa=1024320u

oracle存储过程中数组申明和遍历

// 创建存储过程
create or replace
procedure test as 
  
--申明数组类型
  type t_test is table of varchar2(9);
  test t_test:=t_test('A','B','C','D','E','F','G','H','J','K','L');
  begin
	  --遍历数组，将遍历得到的数据插入到AA表中
	  for i in 1 .. test.count loop
		  DBMS_OUTPUT.PUT_LINE('i='|| i || ', table_names= ' ||t_test(i));
	  end loop;
      WHILE test(i)='A' loop
	   insert into AA(hongyan) values('AAAA');
	  END LOOP;
	  insert into AA(hongyan) values(test(i));
	
  end;

commit;

调用存储过程

call test()
;

execute immediate

断点调试

https://blog.youkuaiyun.com/xfxfxfxfxf666/article/details/88553483

DBLink创建

create  database link dblinkName--dblink名称

      connect to username identified by  password----username ：远程数据库登录用户名   ,password 远程数据库登录密码
      using ' (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.77.97)(PORT = 1521))   --192.168.77.97 远程数据库的IP
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL)  --ORCL 远程数据库的服务名
   )
 )';

建立好DBlink之后 ，通过   select * from table@dblinkName 查询数据。