Oracle存储过程案例详解

创建简单存储过程(Hello World)

为了方便读者简单易懂,我将下面使用到的表复制给大家。
具体表中的数据,请大家自己填写

-- Create table create table EMP ( empno NUMBER ( 4 ) not null , ename VARCHAR2 ( 10 ), job VARCHAR2 ( 9 ), mgr NUMBER ( 4 ), hiredate DATE , sal NUMBER ( 7 , 2 ), comm NUMBER ( 7 , 2 ), deptno NUMBER ( 2 ) )

create or replace procedure firstP ( name in varchar2 ) is /*这里name为的参数,in为输入,varchar2为类型*/ begin /* dbms_output.put_line(); 相当输出到控制台上,这样我们一个简单的存储过程就完成啦 记住一句话的结束使用分号结束,存储过程写完一定要执行 将它保存到数据库中 (F8)快捷键,或者点击左上角执行*/ dbms_output . put_line ( '我的名字叫' || name ); /*dbms_output.put_line相当于JAVA中的System.out.println("我的名字叫"+name);*/ end firstP ;

下面我们要对刚刚写过的存储过程进行测试,我们开启Test Window这个窗口

-- Created on 2018 / 12 / 30 星期日 by ADMINISTRATOR declare -- Local variables here /*测试名称 名称类型 使用 := 给参数赋值,在多说一句,分号结束本句*/ name2 varchar2 ( 64 ):= '数据库' ; begin -- Test statements here firstp ( name2 ); end ;

我们打开DBMS Output就可以看到执行的存储过程啦。

存储过程IF判断

create or replace procedure isifp ( age in number ) is /*存储过程if判断以then开始,以end if; 结束*/ begin if ( age > 30 ) then dbms_output . put_line ( '我已经超过30岁了' ); else if ( age < 10 ) then dbms_output . put_line ( '我还是个儿童' ); else dbms_output . put_line ( '我正在奋斗时期' ); end if ; end if ;   end ;

存储过程输出

create or replace procedure inandout ( name in varchar2 , age in number , outp out varchar2 ) is /*in 代表输入,out 代表输出*/ begin outp := 'my name is ' || name || ',my age is ' || age ; /*相当于JAVA中的return outp,但是请注意,存储过程中可以return多个值*/ end inandout ;

测试输出代码

-- Created on 2018 / 12 / 30 星期日 by ADMINISTRATOR declare -- Local variables here name varchar2 ( 64 ):= '数据库' ; age number := 06 ; out_p varchar2 ( 64 ); begin -- Test statements here inandout ( name , age , outp =>: out_p ); /*这里的outp是存储过程中的输出参数,out_p是在测试中使用的别名*/ end ;

返回游标

create or replace procedure sysrefcursor ( id in number , columnss out sys_refcursor ) as /*columnss out sys_refcursor 为输出游标*/ begin open columnss for select * from emp where empno = id ; end ;

测试游标

第一种测试方法

-- Created on 2018 / 12 / 30 星期日 by ADMINISTRATOR declare -- Local variables here cursor ee is select * from emp where empno = 7934 ; begin -- Test statements here for e in ee loop dbms_output . put_line ( 'deptno:' || e . deptno ); end loop ; end ;

输出结果如下:

第二种测试方法

-- Created on 2018 / 12 / 30 星期日 by ADMINISTRATOR declare -- Local variables here cursor ee is select * from emp where empno = 7934 ; cur ee % rowtype ; begin -- Test statements here open ee ; loop fetch ee into cur ; exit when ee % notfound ; dbms_output . put_line ( 'name:' || cur . ename ); end loop ; close ee ; end ;

上面测试结果仅仅返回一条数据。下面我来演示返回多条数据的情况。
首先请看我表中的数据

有两个job中内容为CLERK的数据。

-- Created on 2018 / 12 / 30 星期日 by ADMINISTRATOR declare -- Local variables here cursor ee is select * from emp where job = 'CLERK' ; begin -- Test statements here for e in ee loop dbms_output . put_line ( 'deptno:' || e . deptno ); end loop ; end ;

游标返回多条数据。

由于对于初学者来说,游标可能不是很容易理解,下面我用JAVA语言来描述一下。
我们在java程序中写条件查询的时候,返回出来的数据是List<泛型>。这个操作相当于游标,说白了就是个查询而已(大家不要误认为就这么一句简单的SQL为什么要用游标,因为只是方便读者学习游标罢了,具体业务具体分析,请不要抬杠哦)
当我们要使用list中的数据时,我们使用循环调用某一条数据时,是不是就要用实体类对象点get字段。可以理解为for e in ee loop dbms_output.put_line('deptno:'||e.deptno); end loop;
这里面的e.deptno。

获取table中的column

create or replace procedure intop ( id in number , print2 out varchar2 ) as e_name varchar2 ( 64 ); begin select ename into e_name from emp where empno = id ; if e_name = 'ALLEN' then dbms_output . put_line ( e_name ); print2 := 'my name is ' || e_name ; else if e_name = 'SMITH' then print2 := '打印sql' || e_name ; else print2 := '打印其他' ; end if ; end if ; end intop ;

稍微复杂一点存储过程

由于朋友这里有个需求需要用存储过程,进而更新一下博客。
首先我们先创建一张表

-- Create table create table CLASSES ( id NUMBER not null , name VARCHAR2 ( 14 ), classesc VARCHAR2 ( 10 ), seq NUMBER ( 5 ) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create / Recreate primary , unique and foreign key constraints alter table CLASSES add constraint PK_CLASSES primary key ( ID ) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );

下面我们创建一个序列

-- Create sequence create sequence SEQ_CLASSES minvalue 1 maxvalue 9999999999999999999999999999 start with 2 increment by 1 cache 20 ;

下面创建存储过程,写的乱一些,希望不要介意

create or replace procedure proclasses ( Names in varchar2 , classescs in varchar ) as /*在我们创建存储过程的时候as其实是is*/ id number ; /*设置变量名称*/ c number ; seq number ; begin select SEQ_CLASSES . nextval into id from dual ; /*获取下一个序列,使用into赋值给id这个变量名称*/ dbms_output . put_line ( 'classescs=' || classescs ); /*打印而已*/ select count (*) into c from Classes where classesc = classescs ; /*条件判断,classesc=进来的变量*/ if ( c > 0 ) then /*当数量大于0时*/ select max ( seq ) + 1 into seq from Classes where classesc = classescs ; dbms_output . put_line ( '第一个seq' || seq ); else if ( c = 0 ) then seq := 0 ; /*如果查询出来的数量为0的时候,我们赋值seq变量为0*/ dbms_output . put_line ( 'c=0的时候seq' || seq ); end if ; end if ; insert into classes ( id , name , classesc , seq ) values ( id , names , classescs , seq ); /*insert插入这个不用多说了,大家都明白;注意的是我们insert之后一定要提交。 不然数据没有持久化到数据库，这个insert没有任何意义了*/ end proclasses ;

下面我们来调用这个存储过程

-- Created on 2019 / 1 / 7 星期一 by ADMINISTRATOR declare -- Local variables here names varchar2 ( 32 ):= '晓明' ; classescs varchar2 ( 32 ):= '一班' ; begin -- Test statements here proclasses ( names , classescs ); end ;

到此这篇关于Oracle存储过程案例详解的文章就介绍到这了,更多相关Oracle存储过程内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/weixin_41768626/article/details/85406750

查看更多关于Oracle存储过程案例详解的详细内容...