存储过程
- 是命了名的PL/SQL块
- 可以有零个或多个参数
- 可以没有返回值，也可以有一个或多个返回值
- 以编译后的形式存放在数据库中
- 由开发语言调用或者PL/SQL块中调用。
- 是一种用来执行某些操作的子程序。
存储过程创建语法
CREATE [OR REPLACE] PROCEDURE
[schema.]procedure_name[(argument [in|out|inout] type…)]
IS | AS
[本地变量声明]
BEGIN
-执行语句部分
[EXCEPTION]
-错误处理部分
END[procedure_name];
-在Sql*Plus 中使用CREATE OR REPLACE子句创建存储程序单元。
-在头部定义所有参数。
-在IS之后，声明本地变量，不需要使用DECLARE开始声明。
-BEGIN开始程序的执行主体。
-oracle图形化界面“Enterprise Manager Console”来创建存储过程。
参数
参数可以为任何合法的PL/SQL类型
参数模式：IN，OUT，IN OUT。
-
- IN，就是从调用环境通过参数传入值，在过程中只能被读取，不能改变。
- OUT，由过程赋值并传递给调用环境。不能是具有默认值的变量，也不能是常量，过程中要给OUT参数传递返回值。
- IN OUT ，具有IN 参数和OUT 参数两者的特性，在过程中即可传入值，也可传出值。
实验41：编写存储过程
参数导入型的存储过程（in）
SQL> create or replace procedure p1
(v_id in emp.empno%type)
is
begin
update emp
set sal=sal+1
where empno=v_id;
commit;
end p1;
/
过程已创建。
验证原程序
SQL> select text from user_source where name='P1';
TEXT
--------------------------------------------------------------------------------
procedure p1
(v_id in emp.empno%type)
is
begin
update emp
set sal=sal+1
where empno=v_id;
commit;
end p1;
已选择9行。
在user_objects数据字典中查看存储过程。
SQL> COL OBJECT_NAME FOR A12
SQL> select object_name,object_type,status from user_objects where object_type='PROCEDURE';
OBJECT_NAME OBJECT_TYPE STATUS
--------------- -------------------- ---------
P1 PROCEDURE VALID
执行存储过程
SQL> EXECUTE P1(7369) --单独执行过程
PL/SQL 过程已成功完成。
SQL> SELECT EMPNO , SAL FROM EMP;
EMPNO SAL
---------- ----------
7369 801
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7839 5000
7844 1500
7900 950
7902 3000
EMPNO SAL
---------- ----------
7934 1300
已选择12行。
SQL> BEGIN --在模块中调用
2 P1(7499);
3 P1(7844);
4 P1(7902);
5 END;
6 /
PL/SQL 过程已成功完成。
SQL> SELECT EMPNO , SAL FROM EMP;
EMPNO SAL
---------- ----------
7369 801
7499 1601
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7839 5000
7844 1501
7900 950
7902 3001
7934 1300
已选择12行。
参数导入和导出型的存储过程（in/out）
SQL> create or replace procedure query_emp
(v_id in emp.empno%type,
v_name out emp.ename%type,
v_salary out emp.sal%type,
v_comm out emp.comm%type)
is
begin
select ename,sal,comm
into v_name,v_salary,v_comm
from emp
where empno=v_id;
end query_emp;
/
过程已创建。
SQL> select empno,ename,sal,comm from emp;
EMPNO ENAME SAL COMM
---------- ---------- ---------- ----------
7369 SMITH 801
7499 ALLEN 1601 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1501 0
7900 JAMES 950
7902 FORD 3001
7934 MILLER 1300
已选择12行。
传变量到调用的模块
SQL> declare
2 v1 emp.ename%type;
3 v2 emp.sal%type;
4 v3 emp.comm%type;
5 begin
6 query_emp(7654,v1,v2,v3);
7 dbms_output.put_line(v1);
8 dbms_output.put_line(v2);
9 dbms_output.put_line(v3);
10 end;
11 /
MARTIN
1250
1400
PL/SQL 过程已成功完成。
SQL> select empno,ename,sal,comm from emp where empno=7654;
EMPNO ENAME SAL COMM
---------- ---------- ---------- ----------
7654 MARTIN 1250 1400
参数导入和导出共用变量型的存储过程（in out）
SQL> create or replace procedure format_phone
2 (v_phone_no in out varchar2)
3 is
4 begin
5 v_phone_no:='('||substr(v_phone_no,1,3)||')'||substr(v_phone_no,4,3)||'-'||substr(v_phone_no,7);
6 end format_phone;
7 /
过程已创建。
传变量到调用的模块
SQL> declare
2 v1 varchar2(20);
3 begin
4 v1:='010456789';
5 format_phone(v1);
6 dbms_output.put_line(v1);
7 end;
8 /
(010)456-789
PL/SQL 过程已成功完成。
删除存储过程
DROP PROCEDURE [procedure_name]；
SQL> drop procedure query_emp;
过程已删除。