Oracle存储过程案例详解 Oracle存储过程案例详解

创建简单存储过程(Hello World) 为了方便读者简单易懂,我将下面使用到的表复制给大家。
具体表中的数据,请大家自己填写

-- Create tablecreate table EMP(empnoNUMBER(4) not null,enameVARCHAR2(10),jobVARCHAR2(9),mgrNUMBER(4),hiredate DATE,salNUMBER(7,2),commNUMBER(7,2),deptnoNUMBER(2))

文章图片

【Oracle存储过程案例详解】

文章图片

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 herefirstp(name2); end;

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

文章图片

文章图片

存储过程IF判断

create or replace procedure isifp(age in number) is/*存储过程if判断以then开始,以end if; 结束*/beginif (age > 30) thendbms_output.put_line('我已经超过30岁了'); elseif (age < 10) thendbms_output.put_line('我还是个儿童'); elsedbms_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 代表输出*/beginoutp:='my name is '|| name ||',my age is '||age; /*相当于JAVA中的return outp,但是请注意,存储过程中可以return多个值*/end inandout;

测试输出代码

-- Created on 2018/12/30 星期日 by ADMINISTRATOR declare -- Local variables herename varchar2(64):='数据库'; age number:=06; out_p varchar2(64); begin-- Test statements hereinandout(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为输出游标*/beginopen columnss forselect * 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 herefor e in ee loopdbms_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 hereopen ee; loopfetch 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 herefor e in ee loopdbms_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) ase_name varchar2(64); beginselect 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; elseprint2:='打印其他'; end if; end if; end intop;

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

-- Create tablecreate table CLASSES(idNUMBER not null,nameVARCHAR2(14),classesc VARCHAR2(10),seqNUMBER(5))tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited); -- Create/Recreate primary, unique and foreign key constraints alter table CLASSESadd constraint PK_CLASSES primary key (ID)using index tablespace USERSpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited);

下面我们创建一个序列

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

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

create or replace procedure proclasses(Namesin varchar2,classescs in varchar) as/*在我们创建存储过程的时候as其实是is*/idnumber; /*设置变量名称*/cnumber; seq number; beginselect 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); elseif (c = 0) thenseq := 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 herenames varchar2(32):='晓明'; classescs varchar2(32):='一班'; begin-- Test statements hereproclasses(names,classescs); end;

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