文档章节

Oracle存储过程实例

深圳大道
 深圳大道
发布于 2016/12/29 15:39
字数 1754
阅读 7
收藏 0
--准备环境
--表1
CREATE TABLE itemfile
(
  itemcode varchar2(4),
  itemdesc varchar2(20),
  p_category varchar2(20),
  qty_hand number(5),
  re_level number(5),
  max_level number(5),
  itemrate number(7,2)
);
INSERT INTO itemfile VALUES('i201','nuts','spares',100,50,250,20);
INSERT INTO itemfile VALUES('i202','bolts','spares',95,125,300,16.5);
INSERT INTO itemfile VALUES('i204','holders','spares',18,30,75,112);
INSERT INTO itemfile VALUES('i205','covers','accessories',30,15,50,400);
INSERT INTO itemfile VALUES('i203','panels','accessories',75,30,150,4000);
INSERT INTO itemfile VALUES('i206','brackets','spares',150,73,200,132);
COMMIT;
--表2
CREATE TABLE order_master
(
  orderno VARCHAR2(5),
  odate DATE,
  vencode VARCHAR2(5),
  ostatus CHAR(1),
  del_date DATE
);
alter session set nls_date_language = 'AMERICAN';
INSERT INTO order_master VALUES('o001','12-MAY-05', 'V002','c', '15-MAY-05');
INSERT INTO order_master VALUES('o002','14-MAY-05', 'V001','p', '15-MAY-05');
INSERT INTO order_master VALUES('o003','14-MAY-05', 'V001','p', '15-FEB-05');
INSERT INTO order_master VALUES('o004','14-MAY-05', 'V003','p', '15-FEB-05');
INSERT INTO order_master VALUES('o005','14-MAY-05', 'V001','p', '15-FEB-05');
INSERT INTO order_master VALUES('o006','14-APR-03', 'V004','p', '18-MAY-05');
INSERT INTO order_master VALUES('o007','14-MAY-04', 'V003','p', '10-FEB-05');
INSERT INTO order_master VALUES('o008','11-MAY-05', 'V001','p', '12-JUN-05');
INSERT INTO order_master VALUES('o009','14-JAN-05', 'V002','c', '16-FEB-05');
INSERT INTO order_master VALUES('o011','14-JAN-05', 'V001','p', '10-FEB-05');
INSERT INTO order_master VALUES('o012','14-FEB-05', 'V003','p', '15-MAY-05');
INSERT INTO order_master VALUES('o013','14-MAR-05', 'V001','p', '15-MAY-05');
INSERT INTO order_master VALUES('o014','14-FEB-05', 'V002','c', '12-MAY-05');
INSERT INTO order_master VALUES('o015','14-APR-03', 'V004','p', '17-APR-05');
COMMIT;
alter session set nls_date_language = 'SIMPLIFIED CHINESE';
--表3
CREATE TABLE ORDER_DETAIL
(
  ORDERNO     VARCHAR2(5) PRIMARY KEY,
  ODATE       DATE,
  VENCODE     VARCHAR2(5),
  itemcode    VARCHAR2(10),
  qty_ord     NUMBER,
  qty_deld    NUMBER,
  OSTATUS     CHAR(1),
  DEL_DATE    DATE,
  ORDER_COST  NUMBER
);

--表4
create table student
(
stuid int,
stuname varchar2(10),
sex  varchar2(2)
);
insert into student values (101, '刘德华', '男');
insert into student values (102, '张学友', '男');
insert into student values (103, '周润发', '男');
insert into student values (104, 'wind', '女');
insert into student values (105, '林青霞', '女');
--表5
create table subject
(
subid varchar2(10) primary key,
subname varchar2(30) not null
);
insert into subject values ('s001', 'oracle');
insert into subject values ('s002', 'java');

----------------------------------------------------------------
****************************************************************
第一部分:开发存储过程
****************************************************************
----------------------------------------------------------------
----------------------------------------------------------------
***************************************************************
1.建立过程:不带任何参数
---------------------------------------------------------------
--案例01:建立一个输出当前系统日期和时间的过程
create or replace procedure system_out_time
is
begin
dbms_output.put_line(systimestamp);
end;

调用过程方法01:
SQL> exec system_out_time;

调用过程方法02:
SQL> call system_out_time();
 
--案例01:打印乘法小九九
create or replace procedure xjj
as
i integer;
j integer;
begin
    dbms_output.put_line(' 打印小九九   ');
     for i in 1..9 loop 
     for j in 1..9 loop
  if i>=j then
    dbms_output.put_line(to_char(j) ||'*'||to_char(i) || '='||to_char(i*j)||'  ');
    end if;
    end loop;
dbms_output.put_line('    ');
end loop;
end;

----------------------------------------------------------------
***************************************************************
2.建立过程:带输入IN参数
---------------------------------------------------------------

--案例01:使用输入参数查询用户信息
create or replace procedure  find_emp(emp_no number)
as
empname emp.ename%type;
begin
select ename into empname from emp where empno=emp_no;
dbms_output.put_line('  雇员的姓名是:  '|| empname);
exception
when no_data_found then
dbms_output.put_line ( '  雇员编号未找到:     ');
end;
 

----案例02:根据已知的编号显示出对应职工的姓名
create or replace procedure queryempname
(sfindno emp.empno%type )
as
sname emp.ename%type;
sjob  emp.job%type;
begin
select ename, job into sname, sjob from emp where empno=sfindno;
dbms_output.put_line(' 编号为  '||sfindno|| '   的职工姓名为  '||sname || '  工作为   :' ||sjob);
exception
when no_data_found then
dbms_output.put_line('没有符合条件的记录!');
when too_many_rows then
dbms_output.put_line('返回的行数太多!');
when others then
dbms_output.put_line('发生以外错误!');
end;
 
--案例03:新建一个带有输入参数的存储过程
create or replace procedure add_empoyee
(
eno number,
name varchar2,
sal number,
job varchar2 default 'CLERK',
dno number
)
is
e_inte  exception;
pragma exception_init(e_inte, -2291);  --关联例外和错误号,相当于raise
begin
insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno);
exception
when dup_val_on_index then    --dup_val_on_index是oracle预定义错误
raise_application_error(-20000,'雇员信息不能重复!');   --raise_application_error显示触发例外
when e_inte then
raise_application_error(-20001, '部门号不存在!');
end;
 
---如该改写为,注意运行结果的异同:
create or replace procedure add_empoyee
(
eno number,
name varchar2,
sal number,
job varchar2 default 'CLERK',
dno number
)
is
e_inte  exception;
begin
insert into emp (empno, ename, sal, job,deptno) values (eno, name, sal, job, dno);
exception
when dup_val_on_index then    --dup_val_on_index是oracle预定义错误
dbms_output.put_line('雇员信息不能重复!');   
when e_inte then
dbms_output.put_line('部门号不存在!');
end;
 
--调用方法
exec add_empoyee(&no,'&name', &sal,'&job',&dno);
exec add_empoyee(1113,'CLERK',2000,'MANAGER',15);
如果使用job的默认值则:
exec add_empoyee(1113,'CLERK',2000,null,10);
 
----------------------------------------------------------------
***************************************************************
3.建立过程:带输入out参数
---------------------------------------------------------------
 
 
--案例01:带有输出out参数的存储过程
--新建存储过程
create or replace procedure test001
(epno in number,
v02 out varchar2)
as
salary number;
begin
 select sal into salary from emp
where empno=epno;
if salary <1000 then
  v02:='这样的工资太低!';
elsif salary  between 1000 and 2000 then
  v02:='这样的工资还可以接受!';
else
v02:='这样的待遇是我们不离开的原因!';
end if;
end;

--调用存储过程方法01 (这样的值是固定的):
declare 
 v2 varchar2(200);  --声明变量时需要和输入参数的类型一致
 begin
 test001 (&no, v2);
 dbms_output.put_line('v02的值为:'||v2);
end;
 
--调用存储过程方法02(用户交互):
declare 
 empno number:=&empno;
 v2 varchar(200);  --声明变量时需要和输入参数的类型一致
 begin
 test001 (empno, v2);
 dbms_output.put_line('v02的值为:'||v2);
end;
------也可以写成
declare 
 empno number;
 v2 varchar(200);  --声明变量时需要和输入参数的类型一致
 begin
 empno:=&empno;
 test001 (empno, v2);
 dbms_output.put_line('v02的值为:'||v2);
end;
 
--案例02:新建一个用于输出雇员名以及工资的过程
create or replace procedure query_emp
(
eno number,
name  out varchar2,
salary out number
)
is
begin
select ename, sal into name, salary from emp where empno=eno;
exception
when no_data_found then
raise_application_error(-20000, '这个员工不存在!');
end;
 
--调用过程方法01:(使用固定的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(7788,nae,sala);
dbms_output.put_line('the employee name is : '||nae);
dbms_output.put_line('the employee sal is : '||sala);
end;
--调用过程方法02:(使用交互的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(&empno,nae,sala);
dbms_output.put_line('the employee name is : '||nae);
dbms_output.put_line('the employee sal is : '||sala);
end;
----------------------------------------------------------------
在存储过程中使用dbms_output.put_line
----------------------------------------------------------------
--案例02的另外一种写法:
create or replace procedure query_emp
(
eno number,
name  out varchar2,
salary out number
)
is
begin
select ename, sal into name, salary from emp where empno=eno;
dbms_output.put_line('the employee name is : '||name);
dbms_output.put_line('the employee sal is : '||salary);
exception
when no_data_found then
raise_application_error(-20000, '这个员工不存在!');
end;
 
--调用过程方法01:(使用固定的变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(7788,nae,sala);
end;

--调用过程方法02:(使用交互式变量)
declare
nae varchar2(10);  --这里的变量和过程中输入参数的类型需要对应
sala number;
begin
query_emp(&empno,nae,sala);
end;
 
 
 

----------------------------------------------------------------
***************************************************************
4.建立过程:带输入 IN out参数
---------------------------------------------------------------
--案例01:新建带 IN OUT参数的过程
--新建代码
create or replace procedure swap 
(
p1 in out number,
p2 in out number
)
as
v_temp number;
begin
v_temp:=p1;
p1:=p2;
p2:=v_temp;
end;
--调用带IN OUT参数的存储过程方法01:
/*由于参数即是输入又是输出参数,所以需要声明变量并输入值*/
declare 
n01 number:=100;
n02 number:=200;
begin
swap(n01, n02);
dbms_output.put_line ('n01=' ||n01);
dbms_output.put_line ('n02=' ||n02);
end;
--调用带IN OUT参数的存储过程方法02:
declare 
n01 number;
n02 number;
begin
n01:=100;
n02:=200;
swap(n01, n02);
dbms_output.put_line ('n01=' ||n01);
dbms_output.put_line ('n02=' ||n02);
end;

--案例02:新建一个带有in out参数的过程
create or replace procedure comp
(
nm01 in out number,
nm02 in out number
)
is 
v1 number;
v2 number;
begin
v1:=nm01/nm02;
v2:=mod(nm01,nm02);
nm01:=v1;
nm02:=v2;
end;

--调用
declare
n1 number;
n2 number;
begin
n1:=100;
n2:=30;
comp(n1,n2);
dbms_output.put_line(n1);
dbms_output.put_line(n2);
end;
 
----------------------------------------------------------------
***************************************************************
5.建立过程:为参数传递变量和数据
---------------------------------------------------------------
exec queryempname(sfindno=>7788);

exec queryempname(sfindno=>&no);

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/8434051

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
Jboss 中添加Oracle 的XA 数据源支持

这方面的资料太少了, 最终在一个IBM的网页上找到。 首先要先让Oracle 打开XA功能,可以通过以下几步实现 : 1) 在SQLPLUS(一定要用这个工具) 中,用Oracle 的系统管理员用户 SYSOPER 或 ...

小骏骏
2015/04/01
303
0
oracle数据结构体系学习笔记(1)

Oracle体系结构导图 oracle server 是一个数据库管理系统,为信息管理提供了一个开放,全面的综合方法 oracle server包含实例和数据库。 oracle instance 访问oracle数据库的一种手段 一个i...

做早起的鸟
2018/01/03
0
0
Oracle Train01

ORACLE系统概述 ORACLE公司自86年推出版本5开始,系统具有分布数据库处理功能.88年推出版本6,ORACLE RDBMS(V6.0)可带事务处理选项(TPO),提高了事务处理的速度.1992年推出了版本7,在ORACLE RD...

宏哥
2011/05/15
327
7
数据库相关概念

数据字典 数据字典(data dictionary)是对于数据模型中的数据对象或者项目的描述的集合。 数据字典在需求分析阶段被建立。 数据字典可能包含的信息,例如:数据库设计资料、储存的SQL程序、...

_编程菜鸟_
2016/04/23
19
0
使用sqlplus进行Oracle数据库批量自动发布

应用系统的更新发布在企业日常运维中是一件比较常见工作,有文件的备份替换,也会有数据库方面的更新操作。本文介绍如何通过一台装有sqlplus工具的中转机对不同应用的Oracle数据库进行自动化...

嘉为科技
09/10
23
0

没有更多内容

加载失败,请刷新页面

加载更多

Mybatis Plus删除

/** @author beth @data 2019-10-17 00:30 */ @RunWith(SpringRunner.class) @SpringBootTest public class DeleteTest { @Autowired private UserInfoMapper userInfoMapper; /** 根据id删除......

一个yuanbeth
今天
5
0
总结

一、设计模式 简单工厂:一个简单而且比较杂的工厂,可以创建任何对象给你 复杂工厂:先创建一种基础类型的工厂接口,然后各自集成实现这个接口,但是每个工厂都是这个基础类的扩展分类,spr...

BobwithB
今天
5
0
java内存模型

前言 Java作为一种面向对象的,跨平台语言,其对象、内存等一直是比较难的知识点。而且很多概念的名称看起来又那么相似,很多人会傻傻分不清楚。比如本文我们要讨论的JVM内存结构、Java内存模...

ls_cherish
今天
4
0
友元函数强制转换

友元函数强制转换 p522

天王盖地虎626
昨天
5
0
js中实现页面跳转(返回前一页、后一页)

本文转载于:专业的前端网站➸js中实现页面跳转(返回前一页、后一页) 一:JS 重载页面,本地刷新,返回上一页 复制代码代码如下: <a href="javascript:history.go(-1)">返回上一页</a> <a h...

前端老手
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部