存储过程

存储过程

存储过程和函数也是一种pl/sql块，是存入数据库的pl/sql块，但存储过程和函数不同于已经介绍过的pl/sql程序,我们通常把pl/sql程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中的。和pl/sql程序相比,存储过程有很多有点，归纳如下：

1. 存储过程和函数以命名的数据库对象形式存储于数据库中，优点是代码不保存本地，用户可以在任何客户机上登录到数据库，并调用或修改代码

2. 存储过程和函数可以由数据库提供安全保证，要想使用存储过程和函数，需要所有者授权后，才能执行存储过程或调用函数。

3. 存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公有模块，用户编写pl/sql程序或其他存储过程都可以调用它(但存储过程和函数不能调用pl/sql程序)。一个重复使用的功能，可以设计成为存储过程；一个经常调用的计算，可以设计为存储函数。

4. 像其他高级语言的过程和函数一样，可以传递参数给存储过程和函数，参数的传递也有很多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值

   存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能表演

创建存储过程

创建存储过程，需要CREATE PROCEDURE 或CREATE ANY PROCEDURE的系统权限。该权限由系统管理员授予，创建基本语法如下

create [or replace] procedure 存储过程名(参数 [in|out| in out] 数据类型... )
as|is
{说明部分}
begin
可执行部分
[exception 错误处理部分]
end [过程名];

--eg1编写一个求雇员总人数的存储
create or replace producre cs_empsum
is
    empsum number default 0;
​
begin
    select count(1) into empsum from xujb_templemp;
    dbms_output.put_line('雇员人数为：'||empsum);
exception
    when others then
    dbms_output.put_line('数据异常'||sqlcode||','||sqlerrm);
end;

 

调用存储过程使用

1. 在无名pl/sql块中直接使用

2. 使用 execute 存储过程名(注意要在命令窗口执行，sql窗口执行报错。)

3. 在其他存储过程中使用(存储过程中的相互调用)。

--在无名块中调用存储
--eg
declare
begin
    cs_empsum;
end;
/*用execute 在命令窗口执行*/
--eg2
SQL>SET SERVEROUTPUT ON
SQL>EXECUTE CS_EMPSUM
/*在其他存储中调用显示全员雇员信息和人数*/
--eg3
create or replace procedure ce_empdetil
is
    cursor emp_det1 is
    select ids,names,sal,dptno from xujb_temp1emp;
begin
    dbms_output.put_line('部门统计：');
    cs_empsum;--注意不能写成dbms_output.put_line('部门统计：'||cs_empsum); 没法连接应该是该存储没输出参数
    for i in emp_det1 loop
    dbms_output.put('雇员id:'||i.ids'   ');
    dbms_output.put('雇员名称:'||i.names'   ');
    dbms_output.put('雇员薪水:'||i.sal'   ');
    dbms_output.put('雇员部门编号:'||i.dptno'   ');
    dbms_output.put_line(' ');---注意此处的换行不能直接dbms_output.put_line();会报错
    end loop;
exception 
    when others then
    dbms_output.put_line('错误信息'||sqlcode||','||sqlerrm);
end;

参数传递

参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以打打增加存储过程的灵魂性和通用性。

参数的类型有三种，

参数类型	说明
IN	定义一个输入参数变量，用于传递参数给存储过程
OUT	定义一个输出参数变量，用户从存储过程获取数据
INOUT	定义一个输入，输出参数变量，兼有以上两种功能

in 类型只可以把值赋给其他变量，不能作为被赋值变量，而out类型可以被其他变量赋值。

参数的定义形式如下：

 参数名  IN   数据类型  DEFAULT 值;   ---输入参数，参数的数据类型不能有长度,例如varchar2(20),只能是varchar2
 参数名  OUT   数据类型;              --输出参数
 参数名 in out  数据类型 default 值； --定义一个输入输出参数

定义一个输入参数变量，用于传递参数给存储过程,在调用存储过程时，主程序的实际参数可以是常量，有值变量或表达式等，default关键字可选，用来设定参数的默认值，如果在调用存储时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

定义一个输出参数变量，用于从存储过程中获取数据，即变量从存储过程中返回值给主程序。

在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式，在存储过程中，参数变量只能被赋值，而不能将其用于赋值，在存储过程中必须给输出变量只是赋值一次

定义一个输入，输出参数变量，兼有以上两者功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式，default关键字为可选项，用来设定参数的默认值，在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值，在存储过程中必须给变量赋值一次。

如果省略in,out,in out ,则默认是in

/*编写一个根据雇员id来加薪的存储，增加金额和雇员编号由参数传递*/
create or replace procedure ce_empaddsal(iddd in varchar2 default '1',sal in number default 100,v_deptno out varchar2)
    empid varchar2(20):=iddd;
    empsal number:=sal;
    empname varchar2(20);
is
begin 
    select b.names into empname from xujb_temp1emp b where b.ids=empid;
    update xujb_temp1emp b set b.sal=b.sal+empsal1   where b.ids=empid;
    dbms_output.put_line('雇员名称：'||empname||'涨薪：'||empsal);
    commit;
exception
    when others then
    dbms_output.put_line('数据异常'||sqlcode||','||sqlerrm);
    rollback;
end;
​
---调用方式可在命令窗口用execute调用，1.
--execute ce_empaddsal(5,100);
---调用2，
--execute ce_empaddsal(sal=>100,iddd=>5);
​
--eg2:加上out参数，注意out型参数变量也可以是一个游标类型。
/*编写一个根据雇员id来加薪的存储，增加金额和雇员编号由参数传递*/
create or replace procedure ce_empaddsal(p_iddd in varchar2 default '1',p_sal in number default 100,v_deptno out varchar2)   
    empname varchar2(20);
is
begin 
    select b.names，b.dept_no into empname,v_deptno from xujb_temp1emp b where b.ids=empid;
    update xujb_temp1emp b set b.sal=b.sal+p_sal   where b.ids=p_iddd;
    dbms_output.put_line('雇员名称：'||empname||'涨薪：'||empsal);
    commit;
exception
    when others then
    dbms_output.put_line('数据异常'||sqlcode||','||sqlerrm);
    rollback;
end;
​
/*调用执行，在无名块中*/
​
declare
deptno varchar2(20);
begin
ce_empaddsal(p_iddd=>'5',p_iddd=>2000,v_deptno=>deptno);/*注意只要有一个参数用来指定符号'=>'，则所有参数都需要加上'=>'包括out参数*/
end
​
​
--eg3 in out参数的使用
/**编写一个修改电话的存储**/
​
create or replace procedure cs_phonchage(phone in out varchar2)
as
begin
    dbms_output.put_line('传入号码：'||phone);
    phone:='86'||phone;
end;
----调用函数，
declare
 tel varchar2(20):='1300000111'
begin
    cs_phonchage(tel);---注意这不能直接传入常量'1300000111',只能是变量，因为还是out参数
    dbms_out.put_line('变更为:'tel)
end;

删除存储过程

drop  procedure 存储过程名;