存储过程是一种命名的PL/SQL块,它既可以没有参数,也可以有若干个输入、输出参数,甚至可以有多个既可以作为输入又作输出的参数,一般没有返回值。存储过程被保存在数据库中,它不可以被SQL语句直接执行或调用,只能通过execut命令执行或在PL/SQL程序块内部被调用。
由于存储过程是一经编译好的代码,所以其在被调用或引用时,执行效率非常高。
一、创建存储过程
语法格式:
create [or replace] procedure pro_name [(parameter1[,parameter2]…)] is|as begin plsql_sentences; [exception] [dowith _ sentences;] end [pro_name];
pro_name:存储过程名称,如果数据库中一经存在了此名称,则可以指定"or replace"关键字,这样新的存储过程将覆盖掉原来的存储过程
parameter1:存储过程的参数,如是输入参数,则需要在其后指定"in"关键字,如是输出参数,则指定“out”关键字。在in或out关键字的后面是参数的数据类型,该类型不能指定长度。
plsql_sentences:PL/SQL语句,存储过程功能实现的主体。
dowith _ sentences:异常处理语句,也是PL/SQL语句,可选项。
注意
案例:创建一个存储过程,该过程实现向课程表中插入一条记录
-- 创建一个存储过程,用于向课程表添加一条课程记录 CREATE PROCEDURE pro_insert_course IS BEGIN INSERT INTO COURSE VALUES(10,'JAVA',8,'大神级讲师授课,需抢座'); COMMIT; DBMS_OUTPUT.put_line('添加课程成功'); END pro_insert_course;
案例:在当前模式下,如果数据库中存在同名的存储过程
-- 创建一个存储过程,用于向课程表添加一条课程记录 CREATE OR REPLACE PROCEDURE pro_insert_course IS BEGIN INSERT INTO COURSE VALUES(10,'JAVA',8,'大神级讲师授课,需抢座'); COMMIT; DBMS_OUTPUT.put_line('添加课程成功'); END pro_insert_course;
调用存储过程
1.在sql plus环境中,使用execute命令执行
exec pro_insert_course; --execute pro_insert_course;
2.在PL/SQL代码块中调用
-- 调用存储过程 BEGIN pro_insert_course; END;
存储过程的参数
前面创建的存储过程都没有涉及参数。oracle为了增强存储过程的灵活性(前面的存储过程每次都是添加相同的数据,这样会违反相关约束),可以实现向存储过程传入参数。参数是一种向程序单元输入和输出数据的机制,存储过程可以接收多个参数,参数模式包括IN、OUT和IN OUT3种。
IN模式
这是一种输入类型的参数,参数值由调用方传入,并且只能被存储过程读取。
CREATE OR REPLACE PROCEDURE pro_insert_course(p_cosid IN INT,p_cosname IN VARCHAR2,p_credit IN INT,p_intro IN VARCHAR2) AS BEGIN INSERT INTO COURSE VALUES(p_cosid,p_cosname,p_credit,p_intro); COMMIT; DBMS_OUTPUT.put_line('添加课程成功'); END; -- 1、指定名称传递(可以不按照参数定义的顺序传递) BEGIN pro_insert_course(p_cosname=>'ORACLE',p_credit=>6,p_cosid=>20,p_intro=>null); END;
-- 2、按位置传递
BEGIN pro_insert_course(20,'ORACLE',6,20,null); END;
-- 3、混合传递
BEGIN pro_insert_course(20,p_cosname=>'ORACLE',p_credit=>6,p_intro=>null);
END;
OUT模式
这是一种输出类型的参数,表示这个参数在存储过程中已经被赋值,并且这个参数值可以传递到当前存储过程以外的环境中,关键字out位于参数名称之后。
-- 创建一个存储过程,根据课程编号得到课程名称和课程学分信息 CREATE OR REPLACE PROCEDURE PRO_GET_COURSE(p_cosid IN INT, p_cosname OUT VARCHAR2,p_credit OUT NUMBER) IS BEGIN SELECT COS_NAME,CREDIT INTO p_cosname,p_credit FROM COURSE WHERE COS_ID=p_cosid; END; DECLARE v_cosid INT:=10; v_cosname VARCHAR2(20); v_credit NUMBER; BEGIN PRO_GET_COURSE(v_cosid,v_cosname,v_credit); DBMS_OUTPUT.put_line('课程名称:' || v_cosname || ',课程学分:' || v_credit); END;
在 sql plus中调用
IN OUT模式
该类型参数,可以在调用时传入值,调用后可以写入值传递到当前存储过程以外的环境中
-- 创建一个存储过程,计算一个数的平方或平方根 CREATE OR REPLACE PROCEDURE PRO_SUQARE(p_num IN OUT INT,p_flag IN BOOLEAN) IS v_i INT := 2; BEGIN IF p_flag THEN p_num := POWER(p_num,v_i); ELSE p_num := SQRT(p_num); END IF; END; -- 调用存储过程 DECLARE v_num number := 3; v_temp number; v_flag boolean; BEGIN v_temp := v_num; v_flag := FALSE; PRO_SUQARE(v_num,v_flag); IF v_flag THEN dbms_output.put_line(v_temp || '的平方=' || v_num); ELSE dbms_output.put_line(v_temp || '的平方根=' || v_num); END IF; END;
IN参数的默认值
前面的IN参数的值都是在调用存储过程时传入的,实际上,Oracle支持在声明IN参数的同时给其初始化默认值,这样在存储过程调用时,如果没有向IN参数传入值,则存储过程可以使用默认值进行操作。
CREATE OR REPLACE PROCEDURE pro_insert_course(p_cosid IN INT, p_cosname IN VARCHAR2, p_credit IN INT, p_intro IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'yyyy-MM-dd') || '开课') AS BEGIN INSERT INTO COURSE VALUES(p_cosid,p_cosname,p_credit,p_intro); COMMIT; DBMS_OUTPUT.put_line('添加课程成功'); END; select * from course;
删除存储过程
DROP PROCEDURE pro_insert_course;