Oracle创建INSERT存储过程并调用

初学数据库，对存储过程一头雾水，上午学习创建简单的数据表，由于运维需要用到数据库存储，之前生产数据库定时存储过程一直失效，于是想自己写一个存储过程，测试下定时存储任务，后续打算用crontab来定时调用存储过程。下午从网上找了一些存储过程的例子，理解改写后往自己的数据库里面insert数据，一直在疯狂报错，简直到了丧心病狂的地步，瞬间觉得自己好水，哈哈。后来经过不懈努力(浪费时间)后，总算可以用，下面吧存储过程的创建放在下面，算是记录一下。

student2表结构信息见前一篇博客：表内容如下，表示学生的一些信息

表的创建请看上一篇博客

创建带参数的存储过程

这里我是通过sqlplus创建存储过程的，依然是用的shell脚本创建存储过程，也可以直接登录到数据库，直接创建存储过程。

createInsetStuProc.sh

#!/bin/bash
#DB="class"   #数据库为class
TB="student2" #需要建立的表是student
PRO="insertIntoStu"
echo "start to create procedure for  table name is $TB"
sqlplus UCR_PARAM/'YNNZABC1!'@bosscend <<EOF
DROP PROCEDURE $PRO 

create or replace procedure $PRO
(
        iv_id           in number,
        iv_name         in varchar2,
        iv_age          in number,
        iv_sex          in char,
        iv_address in varchar2,
        iv_birth        in char
)is
v_exp exception;
vbt_date char(16);
vs_id number;
vs_name varchar2(25);
vs_sex varchar2(10);
vs_age number;
vs_address varchar2(25);
vs_birth date;

begin
        DBMS_OUTPUT.PUT_LINE('check param...');
        vs_id := iv_id;
        vs_name := iv_name;
        vs_age := iv_age;
        vs_sex := iv_sex;
        vs_address := iv_address;
        vbt_date := iv_birth;
        vs_birth := to_date(vbt_date,'yyyy-mm-dd');
        
        insert into $TB(sid,sname,sage,ssex,saddress,sbirth) values(vs_id,vs_name, vs_age, vs_sex,vs_address, vs_birth);
        DBMS_OUTPUT.PUT_LINE('end join, please check...');
        commit;
end insertIntoStu;
/

quit;

EOF
echo "procedure created end"

创建存储过程之前先创建表对象。

重复创建存储过程会覆盖？

执行创建脚本

注意点：入参需要在内部用局部变量承载其值，并且进行相应的类型转换，比如由char类型转换为date类型。

查看存储过程内容语句：

select text from all_source where name = upper('insertIntoStu');

编译存储过程：编译可以确认存储过程没有错误

--编译存储过程
alter procedure insertIntoStu compile;

调用带参数的存储过程：

execInsertproc.sh

#!/bin/bash

TB="student2" #需要建立的表是student
PRO="insertIntoStu" #存储过程名字
echo "start to calling insert procedure for  table name is $TB"

sqlplus UCR_PARAM/'YNNZABC1!'@bosscend <<EOF
execute $PRO(1, 'lily', 24, 'M', 'Shanxi', '1993-01-14')
execute $PRO(2, 'kity', 17, 'WM', 'Shanxi', '2001-01-14')
execute $PRO(3, 'bobo', 27, 'WM', 'Shanxi', '1991-01-14')
execute $PRO(4, 'anna', 29, 'WM', 'Shanxi', '1989-01-14')
/
quit;

EOF
echo "calling procedure  end"

执行结果

查询检查结果：

带出参和自定义抛出异常的存储过程：

create or replace procedure insertIntoStu
(
	iv_id		in number,
	iv_name 	in varchar2,
	iv_age		in number,
	iv_sex		in char,
	iv_address in varchar2,
	iv_birth	in char,
	
	ov_resultcode	out number,
    ov_resulterrinfo out varchar2
)is
v_exp exception;
v_expid exception;
vbt_date char(16);
vs_id number;
vs_name varchar2(25);
vs_sex varchar2(10);
vs_age number;
vs_address varchar2(25);
vs_birth date;

begin
	ov_resultcode := 0;
	ov_resulterrinfo := 'execute success.';
	
	DBMS_OUTPUT.PUT_LINE('check param...');
	vs_id := iv_id;
	vs_name := iv_name;
	vs_age := iv_age;
	vs_sex := iv_sex;
	vs_address := iv_address;
	vbt_date := iv_birth;
	vs_birth := to_date(vbt_date,'yyyy-mm-dd');
	
	if (vs_age < 0) then
		DBMS_OUTPUT.PUT_LINE('age is an negative number!');
		ov_resultcode := ov_resultcode-1;
		ov_resulterrinfo := 'execute failed, please check param.';
		raise v_exp;
	end if;
	
	if (vs_id is null) then
		DBMS_OUTPUT.PUT_LINE('id is null!');
		raise v_expid;
	end if;
	
	--当年龄为负数的时候，这里不再执行，抛出异常后转向后面的控制语句
	insert into student2(sid,sname,sage,ssex,saddress,sbirth) values(vs_id,vs_name, vs_age, vs_sex,vs_address, vs_birth);
	DBMS_OUTPUT.PUT_LINE('end join, please check...');

		exception
	when v_exp then
		DBMS_OUTPUT.PUT_LINE('age is can not be negative!');
	when v_expid then
		DBMS_OUTPUT.PUT_LINE('id is can not be null!');

	commit;
end insertIntoStu;
/

测试年龄为负数插入

结果没有插入

注意，抛出异常语句(raise exception)后，会停止执行后续语句，转向 exception -- when   then 语句，因此，被保护语句应该在raise exception 之后 ，在exception之前，否则如果放在最后执行插入操作，依然能够正常插入

测试插入ID为null