Oracle中Function学习2
上一节说函数
这回聊一下 存储过程：有了上节知识点，对这个也许会一种小感觉.呵呵
过程（存储过程）：与过程相比， 存储过程是存在数据库中的一个对象
我主要通过例子来了解 不做多解释 相信你也能明白：
eg1: 现在定义一个简单的过程，就是打印一个数字
create or replace procedure myfun
is
i number;
begin
i:=100;
DBMS_OUTPUT.put_line('i = '||i) ;//打印一个数字
end;
/* 在想什么，会不会觉有点熟悉 ，怎么这么像function, 其实我也想 ，汗！！*/
执行格式： exec 过程名字
eg: exec myfun;
下面 还是主要写几个例子,边看例子，便解释
eg2:下面编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此
过程就可以完成部门的增加操作。
CREATE OR REPLACE PROCEDURE myprocmyproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
cou NUMBER ;
BEGIN
SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno -- 判断插入的部门编号是否存在，如果存在则不能插入
IF cou=0 THEN-- 可以增加新的部门
INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
DBMS_OUTPUT.put_line('部门插入成功！ ') ;
ELSE
DBMS_OUTPUT.put_line('部门已存在，无法插入！ ') ;
END IF ;
END ;
/
. 删除 mypro 存储过程
drop procedure myproc;
______________________________________________________________________________________--
过程的参数类型：
• IN：值传递，默认的
• IN OUT：带值进，带值出
• OUT：不带值进，带值出
eg: IN：值传递，默认的不说了
eg1 ：IN OUT 类型：
CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)
AS
cou NUMBER ;
BEGIN
-- 判断插入的部门编号是否存在，如果存在则不能插入
SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;
IF cou=0 THEN
-- 可以增加新的部门
INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;
DBMS_OUTPUT.put_line('部门插入成功！ ') ;
-- 修改 dno 的值
dno := 1 ;
ELSE
DBMS_OUTPUT.put_line('部门已存在，无法插入！ ') ;
dno := -1 ;
END IF ;
END ;
编写 PL/SQL 块验证过程：
DECLARE
deptno dept.deptno%TYPE ;
BEGIN
deptno := 12 ;
myproc(deptno,'开发','南京') ;
DBMS_OUTPUT.put_line(deptno) ;
END ;
/
eg2 :OUT 类型：
不带任何值进，只把值带出来。
CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)
AS
I number
BEGIN
I:= dno;
END ;
/
执行上面的存储过程
DECLARE
deptno dept.deptno%TYPE ;
BEGIN
deptno :=12
myproc(deptno) ;
DBMS_OUTPUT.put_line(deptno) ;
END ;
/
——————————————————————————————————————————————————————
下面编写一个存储过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调
用此过程就可以完成部门的增加操作。
create or replace procedure myproc(dno dept.deptno%type,
dn dept.dname%type,dl dept.loc%type)
as
cou number;
begin
select count(*) into cou from dept where deptno = dno;
if cou=0 then
insert into dept values (dno,dn,dl);
dbms_output.put_line('增加部门成功');
else
dbms_output.put_line('部门已存在');
end if;
end;
/
1. 创建三张表 dept10,dept20,dept30，表结构和 dept 一致（不拷贝数据）
create table dept10 as select * from dept where 1=2;
create table dept20 as select * from dept where 1=2;
create table dept30 as select * from dept where 1=2;
2. 编写一个存储过程 mypro，
i. 把 dept 表中 depto=10 的数据，存到 dept10,
ii. 把 dept 表中 depto=20 的数据，存到 dept20
iii. 把 dept 表中 depto=30 的数据，存到 dept30
iv. 执行该存储过程
create or replace procedure myproc
as
begin
insert into dept10 select * from dept where deptno=10;
insert into dept20 select * from dept where deptno=20;
insert into dept30 select * from dept where deptno=30;
end;
/
create or replace procedure mypro
as
cursor mycur is select * from dept;
empInfo emp%ROWTYPE ;
begin
for empInfo in mycur loop
if empInfo.deptno = 10 then
insert into dept10 values(empInfo.deptno, empInfo.dname, empInfo.loc);
elsif empInfo.deptno = 20 then
insert into dept20 values(empInfo.deptno, empInfo.dname, empInfo.loc);
elsif empInfo.deptno = 30 then
insert into dept30 values(empInfo.deptno, empInfo.dname, empInfo.loc);
end if;
end loop;
end;
exec myproc;
4. 写一个存储过程 (给一个用户名，判断该用户名是否存在)
create or replace procedure findName(name emp.ename%type,en out number)
as i number;
begin
select count(*) into i from emp where ename=name;
if i=1 then
en:=i;
dbms_output.put_line('用户存在');
else
en:=0;
dbms_output.put_line('用户不存在');
end if;
end;
/
5. 执行该存储过程
DECLARE
deptno dept.deptno%TYPE ;
BEGIN
findName(upper('aaa'),deptno) ;
DBMS_OUTPUT.put_line(deptno) ;
END ;
/
6.编写一个存储过程，批量插入 1000 条数据（只插入
create table test(i number(10));
create or replace procedure add1
as
i number(10);
begin
for i in 1..1000 loop
if mod(i,2) = 1 then
insert into test values(i);
end if;
end loop;
end;
