ORACLE开发知识：Oracle存储过程案例详解

— 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;

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

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;

— 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;

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;