Oracle中存储过程

╰+攻爆jí腚メ 2022-07-14 14:56 272阅读 0赞

### 过程(存储过程) ###

与过程相比，存储过程是在数据库中的一个对象，如果编译错误。  
可以用show errors or show errors procedure myproc;  
现在定义一个简单的过程，就是打印一个数字

CREATE OR REPLACE PROCEDURE myproc
    AS
    	i NUMBER ; 
    BEGIN
    	i := 100 ;
    	DBMS_OUTPUT.put_line('i = '||i) ; 
    END ;

执行过程： exec  过程名字

下面编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此  
过程就可以完成部门的增加操作。

CREATE OR REPLACE PROCEDURE	myproc(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 ;

过程的参数类型：  
  IN：值传递，默认的  
  IN OUT：带值进，带值出

OUT：不带值进，带值出

IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，  
当过程结束时，控制会返回控制环境，实际参数的值不会改变。  
过程：

create or replace procedure MyProcedure(param1 in INTEGER)    --param1形式参数
    AS
    BEGIN
        ……..
    END

调用：

num:=100;
    MyProcedure(num);        --num 实际参数

分析：  
实际参数num将其值100传递给过程的形式参数param1，param1的值变为100，过程执行完后返回到调用过程的语句，实际参数num的值不会发生任何变化。

OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，  
而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。  
过程:

create or replace procedure MyProcedure(param1 out INTEGER)    --param1形式参数
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(param1);    --param1为null
        param1:=1;                --修改param1的值为1
    END

调用：

num:=100;
    MyProcedure(num);        --num 实际参数

分析：  
实际参数并不能将其值传给过程的形式参数，相反过程的形式参数的初始值为null，等过程执行完后param1的值变为1，  
过程返回时将形式参数的值赋值给实际参数，所以num的值从100变为了1；

INOUT这种模式是IN和OUT的组合；在Oracle过程内部实际参数的值会传递给形式参数，  
形势参数的值可读也可写，过程结束后，形势参数的值将赋予实际参数。    
过程：

create or replace procedure MyProcedure(param1 in out INTEGER)    --param1形式参数，能接受实际参数传来的值
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(param1);    --param1为null
        param1:=1;
    END

调用：

num:=100;
    MyProcedure(num);        --num 实际参数

分析：  
实际参数num将其值100传递给过程的形式参数param1，param1的值初始化为100，过程执行中param1的值变为1，  
等过程执行完毕返回时又将形式参数param1的值赋值给了实际参数num，所以num的值从100变为了1；

### 练习： ###

下面编写一个存储过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调  
用此过程就可以完成部门的增加操作。

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;--执行过程

#### 3. 删除 mypro 存储过程 ####

drop procedure 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 条数据（只插入 ID 为奇数的数据） ####

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;