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;