Oracle数据库创建存储过程的示例详解

2023-07-20 18:18:03 74

1.1，Oracle存储过程简介：

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，
减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

优点：

允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
允许更快执行，如果某操作需要执行大量SQL语句或重复执行，存储过程比SQL语句执行的要快。
减少网络流量，例如一个需要数百行的SQL代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。

1.2，创建存储过程的语法：

create[orreplace]procedure存储过程名（param1intype，param2outtype）
as
变量1类型（值范围）;
变量2类型（值范围）;
begin
selectcount(*)into变量1from表Awhere列名=param1；
if(判断条件)then
select列名into变量2from表Awhere列名=param1；
dbms_output.Put_line('打印信息');
elsif(判断条件)then
dbms_output.Put_line('打印信息');
else
raise异常名（NO_DATA_FOUND）;
endif;
exception
whenothersthen
rollback;
end;

参数的几种类型:

in是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变。
out模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
inout表示高参数可以向该过程中传递值，也可以将某个值传出去

1.3，示范一些存储过程

[下面一些存储过程的操作根据自己数据库中的内容进行内容显示，只要显示内容就正确，报错除外--，还有存储过程尽量不要粘贴代码，很容易报错]:

1.3.1，不带参数的存储过程：

CREATEORREPLACEPROCEDUREMYDEMO02
AS
nameVARCHAR(10);
ageNUMBER(10);
BEGIN
name:='xiaoming';--:=则是对属性进行赋值
age:=18;
dbms_output.put_line('name='||name||',age='||age);--这条是输出语句
END;
--存储过程调用(下面只是调用存储过程语法)
BEGIN
MYDEMO02();
END;

1.3.2，带参数的存储过程:

CREATEORREPLACEprocedureMYDEMO03(nameinvarchar,ageinint)
AS
BEGIN
dbms_output.put_line('name='||name||',age='||age);
END;

--存储过程调用
BEGIN
MYDEMO03('姜煜',18);
END;

1.3.3，出现异常的输出存储过程:

CREATEORREPLACEPROCEDUREMYDEMO04
AS
ageINT;
BEGIN
age:=10/0;
dbms_output.put_line(age);
EXCEPTIONwhenothersthen--处理异常
dbms_output.put_line('error');
END;
--调用存储过程
BEGIN
MYDEMO04;
END;

Oracle常见的三大异常分类[没有详细陈述，有兴趣的同学可以自行查下]
预定义异常：由PL/SQL定义的异常。由于它们已在standard包中预定义了，因此，这些预定义异常可以直接在程序中使用，而不必再定义部分声明。
非预定义异常：用于处理预定义异常所不能处理的Oracle错误。
自定义异常：用户自定义的异常，需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是Oracle错误，例如它可能是一个数据错误。

1.3.4，获取当前时间和总人数:

CREATEORREPLACEPROCEDURETEST_COUNT01
IS
v_totalint;
v_datevarchar(20);
BEGIN
selectcount(*)intov_totalfromEMP_TESTWHEREENAME='燕小六';--into是赋值的关键字
selectto_char(sysdate,'yyyy-mm-dd')intov_dateFROMEMP_TESTWHEREENAME='郭芙蓉';
DBMS_OUTPUT.put_line('总人数:'||v_total);
DBMS_OUTPUT.put_line('当前日期'||v_date);
END;

--调用存储过程
BEGIN
TEST_COUNT01();
END;

1.3.5，带输入参数和输出参数的存储过程：

CREATEORREPLACEPROCEDURETEST_COUNT04(v_idinint,v_nameoutvarchar2)
IS
BEGIN
SELECTENAMEintov_nameFROMEMP_TESTWHEREEMPNO=v_id;
dbms_output.put_line(v_name);
EXCEPTION
whenno_data_foundthendbms_output.put_line('no_data_found');
END;

--调用存储过程
DECLARE
v_namevarchar(200);
BEGIN
TEST_COUNT04('1002',v_name);
END;

1.3.6，查询存储过程以及其他:

CREATEORREPLACEPROCEDUREjob_day04(deinvarchar,nameoutvarchar,App_Codeoutvarchar,error_Msgoutvarchar)
AS
BEGIN
SELECTENAMEintonameFROMEMP_TESTWHEREENAME=de;
EXCEPTIONWHENothersTHEN
error_Msg:='未找到数据';
END;
--调用存储过程
DECLARE
 devarchar(10);
 abvarchar(10);
  appcodevarchar(20);
  ermgvarchar(20);
BEGIN
  de:='张三丰';
  JOB_DAY04(de,ab,appcode,ermg);
  dbms_output.put_line(ermg);
END;

1.3.7，向数据库中添加数据的存储过程

CREATEORREPLACEPROCEDUREjob_day05(do1invarchar,dn1invarchar,eo1innumber,en1invarchar,App_Codeoutvarchar,error_Msgoutvarchar)
AS
BEGIN
INSERTINTOSTUDENT(NAME,CLASS)VALUES(do1,dn1);
INSERTINTOCOMPANY(EMPID,NAME,DEPARNAME)VALUES(eo1,en1,do1);
COMMIT;
EXCEPTIONWHENOTHERSTHEN
App_Code:=-1;
error_Msg:='插入失败';
END;
--调用存储过程
DECLARE
 do1varchar(10);
 dn1varchar(10);
  eo1number(20);
  App_Codevarchar(20);
error_Msgvarchar(20);
BEGIN
  do1:='张三丰';
dn1:='新桥';
eo1:=1001;
  JOB_DAY04(do1,dn1,App_Code,error_Msg);
  dbms_output.put_line(ermg);
END;

这个比较麻烦，做的时候假如报错就别找了--我找了好久也没找到，，，

2.0，游标的使用，看到的一段解释很好的概念，如下：

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放
在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，
系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多
行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
在我们进行insert、update、delete和select valueinto variable的操作中，使用的是隐式游标
隐式游标的属性返回值类型意义
SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数
SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型DML执行过程中为真，结束后为假

2.1，修改雇员薪资：

CREATEORREPLACEPROCEDUREjob_day06(epoinnumber)
AS
BEGIN
UPDATEEMPSSETSAL=(SAL+100)WHEREempno=epo;
IFSQL%FOUND--SQL%FOUND是隐式游标作用:判断SQL语句是否成功执行,当有作用行时则成功执行为true，否则为false。6THEN
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');
commit;
else
DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');
ENDIF;
END;
--调用存储过程
declare
e_numbernumber;
begin
e_number:=1001;
job_day06(e_number);
end;

2.2，查询编号为1001信息

CREATEORREPLACEPROCEDUREjob_day07
IS
BEGIN
DECLARE
cursoremp_sorisselectname,salFROMEMPSWHEREEMPNO='1001';--声明游标
cnameEMPS.NAME%type;--%type作用:声明的变量ename与EMPS表的NAME列类型一样
csalEMPS.SAL%type;
BEGIN
openemp_sor;--打开游标
loop
--取游标值给变量
FETCHemp_sorintocname,csal;
dbms_output.put_line('name:'||cname);
exitwhenemp_sor%notfound;
endloop;
closeemp_sor;--关闭游标
end;
end;
--调用存储过程
BEGIN
job_day07();
END;

总结:

存储过程通俗的理解就是就是一个执行过程,调用的时候给他所需要的需求就会对数据库进行操作,相当于我们自己手写Sql,只不过有了存储过程
只要调用一下传给他参数他就会帮我们写,比较方便,灵活的运用存储过程会让我们开发很方便

到此这篇关于Oracle数据库创建存储过程的示例详解的文章就介绍到这了,更多相关Oracle数据库创建存储过程内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

Oracle数据库创建存储过程的示例详解

热门推荐

随机推荐