存储过程的例子我用的是PL/SQL Developer 7.0.0.1050版本功能列表:(每个例子都可以单独运行,按照先SQL窗口,再Command窗口的执行顺序)
例子1的用处是传入参数改变记录的值,这里还体现了default的用法
例子2的用处是传入记录的编号,将这条记录取出,然后修改这条记录再写入另外一个表中,这里还体现了EXCEPTION的用法
例子3的用处是传入参数改变记录的值,主要体现了if,if else,return的用法例子1的用处是传入参数改变记录的值,这里还体现了default的用法
例子1:修改deptno为10的记录中,comm的值,comm=sal*v_percent。
在SQL窗口执行(SQL window)create or replace procedure update_commission
(v_dept in number,v_percent in Number Default 10 )
Is
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
End update_commission;在Command窗口执行(Command window)drop table emp;create table emp
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp Where deptno=10;
Delete From emp Where deptno=11;
Insert Into emp (deptno,comm,sal) Values (10,1,2);
Insert Into emp (deptno,comm,sal) Values (11,2,3);
Commit;Execute update_commission(10,15);
commit;
select * from emp;
Execute update_commission(10);
select * from emp;
commit;例子2的用处是传入记录的编号,将这条记录取出,然后修改这条记录再写入另外一个表中,这里还体现了EXCEPTION的用法
例子2
在SQL窗口执行(SQL window)create or replace procedure update_commission1
(v_dept in number)
Is
m_deptno number:=0;
m_comm number:=0;
Begin
SELECT deptno,comm into m_deptno,m_comm FROM emp1 where deptno=v_dept;
m_comm:=m_comm*2;
insert into emp2 (deptno,comm) values(m_deptno,m_comm);
EXCEPTION
WHEN OTHERS THEN
NULL;
End update_commission1;在Command窗口执行(Command window)
drop table emp1;create table emp1
(
DEPTNO number,
COMM number,
SAL number
);drop table emp2;create table emp2
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp1 Where deptno=10;
Delete From emp1 Where deptno=11;
Insert Into emp1 (deptno,comm,sal) Values (10,1,2);
Insert Into emp1 (deptno,comm,sal) Values (11,2,3);Delete From emp2 Where deptno=10;Commit;
Execute update_commission1(10);
commit;
select * from emp2;例子3的用处是传入参数改变记录的值,主要体现了if,if else,return的用法
例子3:修改deptno为10的记录中,comm的值,comm=sal*v_percent。
在SQL窗口执行(SQL window)create or replace procedure update_commission
(v_dept in number ,v_percent in Number Default 10)
Is
Begin
--IF ... Then ... END IF; usage
IF v_dept =10 Then
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
Return;
End;
End IF; --IF ... Then ... ELSE ... END IF; usage
IF v_dept =11 Then
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
End;
ELSE
Begin
Update emp Set comm=100 where deptno=11;
End;
End IF;
End update_commission;在Command窗口执行(Command window)
drop table emp;
create table emp
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp Where deptno=10;
Delete From emp Where deptno=11;
Delete From emp Where deptno=12;
Insert Into emp (deptno,comm,sal) Values (10,1,2);
Insert Into emp (deptno,comm,sal) Values (11,2,3);
Insert Into emp (deptno,comm,sal) Values (12,3,4);
Commit;select * from emp;
Execute update_commission(10);
select * from emp;
commit;
Execute update_commission(11);
select * from emp;
commit;
Execute update_commission(12);
select * from emp;
commit;
例子1的用处是传入参数改变记录的值,这里还体现了default的用法
例子2的用处是传入记录的编号,将这条记录取出,然后修改这条记录再写入另外一个表中,这里还体现了EXCEPTION的用法
例子3的用处是传入参数改变记录的值,主要体现了if,if else,return的用法例子1的用处是传入参数改变记录的值,这里还体现了default的用法
例子1:修改deptno为10的记录中,comm的值,comm=sal*v_percent。
在SQL窗口执行(SQL window)create or replace procedure update_commission
(v_dept in number,v_percent in Number Default 10 )
Is
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
End update_commission;在Command窗口执行(Command window)drop table emp;create table emp
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp Where deptno=10;
Delete From emp Where deptno=11;
Insert Into emp (deptno,comm,sal) Values (10,1,2);
Insert Into emp (deptno,comm,sal) Values (11,2,3);
Commit;Execute update_commission(10,15);
commit;
select * from emp;
Execute update_commission(10);
select * from emp;
commit;例子2的用处是传入记录的编号,将这条记录取出,然后修改这条记录再写入另外一个表中,这里还体现了EXCEPTION的用法
例子2
在SQL窗口执行(SQL window)create or replace procedure update_commission1
(v_dept in number)
Is
m_deptno number:=0;
m_comm number:=0;
Begin
SELECT deptno,comm into m_deptno,m_comm FROM emp1 where deptno=v_dept;
m_comm:=m_comm*2;
insert into emp2 (deptno,comm) values(m_deptno,m_comm);
EXCEPTION
WHEN OTHERS THEN
NULL;
End update_commission1;在Command窗口执行(Command window)
drop table emp1;create table emp1
(
DEPTNO number,
COMM number,
SAL number
);drop table emp2;create table emp2
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp1 Where deptno=10;
Delete From emp1 Where deptno=11;
Insert Into emp1 (deptno,comm,sal) Values (10,1,2);
Insert Into emp1 (deptno,comm,sal) Values (11,2,3);Delete From emp2 Where deptno=10;Commit;
Execute update_commission1(10);
commit;
select * from emp2;例子3的用处是传入参数改变记录的值,主要体现了if,if else,return的用法
例子3:修改deptno为10的记录中,comm的值,comm=sal*v_percent。
在SQL窗口执行(SQL window)create or replace procedure update_commission
(v_dept in number ,v_percent in Number Default 10)
Is
Begin
--IF ... Then ... END IF; usage
IF v_dept =10 Then
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
Return;
End;
End IF; --IF ... Then ... ELSE ... END IF; usage
IF v_dept =11 Then
Begin
Update emp Set comm=sal*v_percent where deptno=v_dept;
End;
ELSE
Begin
Update emp Set comm=100 where deptno=11;
End;
End IF;
End update_commission;在Command窗口执行(Command window)
drop table emp;
create table emp
(
DEPTNO number,
COMM number,
SAL number
);Delete From emp Where deptno=10;
Delete From emp Where deptno=11;
Delete From emp Where deptno=12;
Insert Into emp (deptno,comm,sal) Values (10,1,2);
Insert Into emp (deptno,comm,sal) Values (11,2,3);
Insert Into emp (deptno,comm,sal) Values (12,3,4);
Commit;select * from emp;
Execute update_commission(10);
select * from emp;
commit;
Execute update_commission(11);
select * from emp;
commit;
Execute update_commission(12);
select * from emp;
commit;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货