一、过程 (存储过程) 过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。 示例1： 声明存储过程，该过程返回dept表行数 DECLARE PROCEDURE getDeptCount AS deptCount INT;BEGIN SELECT COUNT(*) INTO d
一、过程 (存储过程)
过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
示例1： 声明存储过程，该过程返回dept表行数
DECLARE
PROCEDURE getDeptCount
AS
deptCount INT;
BEGIN
SELECT COUNT(*) INTO deptCount FROM DEPT;
DBMS_OUTPUT.PUT_LINE( 'DEPT表的共有记录数：'||deptCount);
END getDeptCount;
BEGIN
getDeptCount[()];
END ;
注意：此存储过程getDeptCount只在块运行时有效。
示例2： 创建不带参数的存储过程，该过程返回dept表行数
CREATE OR REPLACE PROCEDURE getDeptCount
AS | IS
deptCount int;
BEGIN
SELECT COUNT(*) INTO deptCount FROM dept;
DBMS_OUTPUT.PUT_LINE( 'dept表共有'||deptCount||'行记录');
END [getDeptCount];
当我们创建的存储过程没有参数时，在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分，存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样，EXCEPTION和声明部分都是可选的。
当我们创建的过程带有错误时，我们可以通过SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
使用以下代码可以执行存储过程：
BEGIN
getDeptCount;
END ;
以上存储过程还可以通过以下代码来简化调用：
EXEC getDeptCount[;]
CALL getDeptCount();
注意：
并不是所有的存储过程都可以用这种方式来调用 定义无参存储过程时，存储过程名后不能加() 在块中或是通过EXEC调用存储过程时可以省略() 通过CALL调用无参存储过程必须加上()
示例3： 创建带有输入参数的存储过程，该过程通过员工编号打印工资额
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER) --参数的数据类型不能指定长度
AS
salary emp.sal%TYPE;
BEGIN
SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo;
DBMS_OUTPUT.PUT_LINE(eNo|| '号员工的工资为'||salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工');
END ;
当定义的存储过程含有参数时，参数的数据类型不能指定长度。参数还有输入和 输出之分，本例中没有指定，默认情况为输入参数，也可显示的指定某个参数是输入参数，如(eNo IN NUMBER)。同示例1不同，该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程：
BEGIN
getSalaryByEmpNo(7788);
END;
或者
EXEC getSalaryByEmpNo(7788); 或者
CALL getSalaryByEmpNo(7788);
但是如果传给一个存储过程的参数是变量时，必须使用BEGIN END块，如下：
DECLARE
no emp.empNo%TYPE;
BEGIN
no:=7788;
getSalaryByEmpNo(no);
END ;
如果某个包中含有常量，也可以通过如下的方式调用：
EXEC getSalaryByEmpNo(ConstantPackage.no);
但这种方式不能再使用CALL调用。
示例4： 创建含有输入和 输出参数的存储过程，该过程通过员工编号查找工资额，工资额以 输出参数返回
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)
AS
BEGIN
SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工');
END ;
当过程中含有 输出参数时，调用时必须通过BEGIN END块，不能通过EXEC或CALL调用。如：
DECLARE
salary NUMBER(7,2);
BEGIN
getSalaryByEmpNo(7788,salary);
DBMS_OUTPUT.PUT_LINE(salary);
END ;
示例5： 创建参数类型既是输入参数也是 输出参数的过程
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER)
AS
BEGIN
SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工');
END ;
调用如下：
DECLARE
no NUMBER(7,2);
BEGIN
no :=7788;
getSalaryByEmpNo( no );
DBMS_OUTPUT.PUT_LINE( no );
END ;
示例6： 创建带有默认值的过程
CREATE OR REPLACE PROCEDURE addEmp
(
empNo NUMBER,
eName VARCHAR2,
job VARCHAR2 := 'CLERK' ,
mgr NUMBER,
hiredate DATE DEFAULT SYSDATE,
sal NUMBER DEFAULT 1000,
comm NUMBER DEFAULT 0,
deptNo NUMBER DEFAULT 30
)
AS
BEGIN
INSERT INTO emp VALUES (empNo,eName,job,mgr,hiredate,sal,comm,deptNo);
END ;
调用如下：
EXEC addEmp(7776, 'zhangsan' , 'CODER' ,7788, '06-1月-2000' ,2000,0,10); --没有使用默认值
EXEC addEmp(7777, 'lisi' , 'CODER' ,7788, '06-1月-2000' ,2000, NULL ,10); --可以使用NULL值
EXEC addEmp(7778, 'wangwu' ,mgr=>7788); --使用默认值
EXEC addEmp(mgr=>7788,empNo=>7779,eName=> 'sunliu' ); --更改参数顺序
示例7：使用NOCOPY编译提示
当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。
DECLARE
TYPE DeptList IS TABLE OF VARCHAR2(10);
dList DeptList:=DeptList( 'CORESUN' , 'CORESUN' , 'CORESUN' , 'CORESUN' );
PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)
AS ...
注意：NOCOPY只是一个提示，而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。
二、维护过程
1、删除存储过程
DROP PROCEDURE Proc_Name;
2、查看过程状态
SELECT object_name,status FROM USER_OBJECTS WHERE object_type= 'PROCEDURE' ;
3、重新编译过程
ALTER PROCEDURE Proc_Name COMPILE;
4、查看过程代码
SELECT * FROM USER_SOURCE WHERE TYPE= 'PROCEDURE' ;
三、参数的理解
-- 输出参数不可以修改解决的方法有两种
--1 把参数改成输入参数
--2 就是参数改成 可输入 输出的参数;
调用过程的 三个方式
1 就是使用call
在只用call方式调用函数的时候,必须加要括号,有参数,还要加参数值
这个方式在命令窗口,调用过程,将不会出现输入的数据.
2 就是使用exec 命令 ,进行命令调用过程, 使用命令,就必须在命令行里面输入
过程名,这个命令窗口中,可加可不加() ,如果有参数的,就一定要加,还有参数值,参数值的类型要与
变量类型相同.
3 在语句块中进行调用过程, 这个方式和命令模式类似,他们都是可要可不要(),
-- 在2 和 3 中的 没有括号的情况是,过程没有参数 ,如果有,就必须要有()
输出参数的特点
1 一个过程中,如果有 输出参数(OUT 参数),在调用过程的使用,也要传入一个参数, 这个参数可以不用在调用的地方
进行赋值,就直接传入一个声明好的一个变量,用来接受存储过程中的 输出参数的值(OUT 参数)
2 输入参数 值不可以改变在过程中,
注意: 在存储过程中,他的参数类型不可以设置它的大小 ;
例如;
CREATE OR REPLACE PROCEDURE hello(
p_name IN VARCHAR2(12),
p_age OUT NUMBER(10,2)
)
IS
BEGIN
如果有 输出参数就必须有有一个参数进行接收 ;
CREATE OR REPLACE PROCEDURE hello(
p_name IN VARCHAR2,
p_age OUT emp.sal%TYPE
)
IS
BEGIN
SELECT emp.sal + 3131 INTO p_age FROM emp WHERE empno = 7788 ;
dbms_output.put_line( p_age);
END ;
--------- 块中调用方法
DECLARE
v_nanme varchar2(12);
v_age NUMBER (12,2);
BEGIN
hello (v_nanme,v_age);
dbms_output.put_line(v_age);
END ;
-- 在这个过程中 传入的v_age 就是接受 存储过程 输出参数的值 ; 类似于Java的中的返回值
-- 理解 in out 参数
CREATE OR REPLACE PROCEDURE hello1 (
p_name IN OUT emp.ename%TYPE
)
IS
BEGIN
-- SELECT emp.ename INTO p_name FROM emp ;
p_name:= 'a;sk , ' || p_name ;
END ;
------------------------------------------------------------------------ --
DECLARE
v_nanme varchar2(12);
BEGIN
v_nanme:= '12312' ;
hello1(v_nanme);
补充：sqlplus中执行含有 输出参数为游标的存储过程
实例6：
sqlplus创建存储过程，使用如下：
SQL > create or replace procedure test1(rcursor out sys_refcursor) as
begin
open rcursor for
select decode(row_number() over (partition by deptno order by ename),
1,
deptno,
null ) deptno,
t.ename
from scott.emp t;
end ;
/
--使用sqlplus执行上面创建的带有游标 输出参数的存储过程
SQL > var cur refcursor
SQL > exec test1(:cur);
PL/ SQL procedure successfully completed.
SQL > print cur;
DEPTNO ENAME
-------- -- ----------
10 CLARK
KING
MILLER
20 ADAMS
FORD
JONES
SCOTT
SMITH
30 ALLEN
BLAKE
JAMES
DEPTNO ENAME
-------- -- ----------
MARTIN
TURNER
WARD
14 rows selected.
查看更多关于Oracle存储过程详解(引用）+补充的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did97485
过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
示例1： 声明存储过程，该过程返回dept表行数DECLARE PROCEDURE getDeptCount AS deptCount INT; BEGIN SELECT COUNT(*) INTO deptCount FROM DEPT; DBMS_OUTPUT.PUT_LINE( 'DEPT表的共有记录数：'||deptCount); END getDeptCount; BEGIN getDeptCount[()]; END ;
注意：此存储过程getDeptCount只在块运行时有效。
示例2： 创建不带参数的存储过程，该过程返回dept表行数CREATE OR REPLACE PROCEDURE getDeptCount AS | IS deptCount int; BEGIN SELECT COUNT(*) INTO deptCount FROM dept; DBMS_OUTPUT.PUT_LINE( 'dept表共有'||deptCount||'行记录'); END [getDeptCount];
当我们创建的存储过程没有参数时，在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分，存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样，EXCEPTION和声明部分都是可选的。
当我们创建的过程带有错误时，我们可以通过SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
使用以下代码可以执行存储过程：BEGIN getDeptCount; END ; 以上存储过程还可以通过以下代码来简化调用： EXEC getDeptCount[;] CALL getDeptCount();
注意：
并不是所有的存储过程都可以用这种方式来调用 定义无参存储过程时，存储过程名后不能加() 在块中或是通过EXEC调用存储过程时可以省略() 通过CALL调用无参存储过程必须加上()示例3： 创建带有输入参数的存储过程，该过程通过员工编号打印工资额
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER) --参数的数据类型不能指定长度 AS salary emp.sal%TYPE; BEGIN SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo; DBMS_OUTPUT.PUT_LINE(eNo|| '号员工的工资为'||salary); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工'); END ;
当定义的存储过程含有参数时，参数的数据类型不能指定长度。参数还有输入和 输出之分，本例中没有指定，默认情况为输入参数，也可显示的指定某个参数是输入参数，如(eNo IN NUMBER)。同示例1不同，该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程：
BEGIN
getSalaryByEmpNo(7788);
END;
或者
EXEC getSalaryByEmpNo(7788); 或者
CALL getSalaryByEmpNo(7788);
但是如果传给一个存储过程的参数是变量时，必须使用BEGIN END块，如下：DECLARE no emp.empNo%TYPE; BEGIN no:=7788; getSalaryByEmpNo(no); END ;
如果某个包中含有常量，也可以通过如下的方式调用：
EXEC getSalaryByEmpNo(ConstantPackage.no);
但这种方式不能再使用CALL调用。
示例4： 创建含有输入和 输出参数的存储过程，该过程通过员工编号查找工资额，工资额以 输出参数返回CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER) AS BEGIN SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工'); END ;
当过程中含有 输出参数时，调用时必须通过BEGIN END块，不能通过EXEC或CALL调用。如：
DECLARE salary NUMBER(7,2); BEGIN getSalaryByEmpNo(7788,salary); DBMS_OUTPUT.PUT_LINE(salary); END ;
示例5： 创建参数类型既是输入参数也是 输出参数的过程
CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER) AS BEGIN SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( '没有找到该编号的员工'); END ;
调用如下：
DECLARE no NUMBER(7,2); BEGIN no :=7788; getSalaryByEmpNo( no ); DBMS_OUTPUT.PUT_LINE( no ); END ;
示例6： 创建带有默认值的过程
CREATE OR REPLACE PROCEDURE addEmp ( empNo NUMBER, eName VARCHAR2, job VARCHAR2 := 'CLERK' , mgr NUMBER, hiredate DATE DEFAULT SYSDATE, sal NUMBER DEFAULT 1000, comm NUMBER DEFAULT 0, deptNo NUMBER DEFAULT 30 ) AS BEGIN INSERT INTO emp VALUES (empNo,eName,job,mgr,hiredate,sal,comm,deptNo); END ;
调用如下：
EXEC addEmp(7776, 'zhangsan' , 'CODER' ,7788, '06-1月-2000' ,2000,0,10); --没有使用默认值 EXEC addEmp(7777, 'lisi' , 'CODER' ,7788, '06-1月-2000' ,2000, NULL ,10); --可以使用NULL值 EXEC addEmp(7778, 'wangwu' ,mgr=>7788); --使用默认值 EXEC addEmp(mgr=>7788,empNo=>7779,eName=> 'sunliu' ); --更改参数顺序
示例7：使用NOCOPY编译提示
当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。DECLARE TYPE DeptList IS TABLE OF VARCHAR2(10); dList DeptList:=DeptList( 'CORESUN' , 'CORESUN' , 'CORESUN' , 'CORESUN' ); PROCEDURE My_Proc(d IN OUT NOCOPY DeptList) AS ...
注意：NOCOPY只是一个提示，而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。
二、维护过程
1、删除存储过程
DROP PROCEDURE Proc_Name;2、查看过程状态
SELECT object_name,status FROM USER_OBJECTS WHERE object_type= 'PROCEDURE' ;
3、重新编译过程
ALTER PROCEDURE Proc_Name COMPILE;
4、查看过程代码
SELECT * FROM USER_SOURCE WHERE TYPE= 'PROCEDURE' ;
三、参数的理解
-- 输出参数不可以修改解决的方法有两种
--1 把参数改成输入参数
--2 就是参数改成 可输入 输出的参数;
调用过程的 三个方式
1 就是使用call
在只用call方式调用函数的时候,必须加要括号,有参数,还要加参数值这个方式在命令窗口,调用过程,将不会出现输入的数据.
2 就是使用exec 命令 ,进行命令调用过程, 使用命令,就必须在命令行里面输入
过程名,这个命令窗口中,可加可不加() ,如果有参数的,就一定要加,还有参数值,参数值的类型要与
变量类型相同.
3 在语句块中进行调用过程, 这个方式和命令模式类似,他们都是可要可不要(),
-- 在2 和 3 中的 没有括号的情况是,过程没有参数 ,如果有,就必须要有()
输出参数的特点
1 一个过程中,如果有 输出参数(OUT 参数),在调用过程的使用,也要传入一个参数, 这个参数可以不用在调用的地方
进行赋值,就直接传入一个声明好的一个变量,用来接受存储过程中的 输出参数的值(OUT 参数)
2 输入参数 值不可以改变在过程中,
注意: 在存储过程中,他的参数类型不可以设置它的大小 ;
例如;CREATE OR REPLACE PROCEDURE hello( p_name IN VARCHAR2(12), p_age OUT NUMBER(10,2) ) IS BEGIN如果有 输出参数就必须有有一个参数进行接收 ;
CREATE OR REPLACE PROCEDURE hello( p_name IN VARCHAR2, p_age OUT emp.sal%TYPE ) IS BEGIN SELECT emp.sal + 3131 INTO p_age FROM emp WHERE empno = 7788 ; dbms_output.put_line( p_age); END ;--------- 块中调用方法
DECLARE v_nanme varchar2(12); v_age NUMBER (12,2); BEGIN hello (v_nanme,v_age); dbms_output.put_line(v_age); END ;-- 在这个过程中 传入的v_age 就是接受 存储过程 输出参数的值 ; 类似于Java的中的返回值
-- 理解 in out 参数
CREATE OR REPLACE PROCEDURE hello1 ( p_name IN OUT emp.ename%TYPE ) IS BEGIN -- SELECT emp.ename INTO p_name FROM emp ; p_name:= 'a;sk , ' || p_name ; END ; ------------------------------------------------------------------------ -- DECLARE v_nanme varchar2(12); BEGIN v_nanme:= '12312' ; hello1(v_nanme);补充：sqlplus中执行含有 输出参数为游标的存储过程
实例6：
sqlplus创建存储过程，使用如下：
SQL > create or replace procedure test1(rcursor out sys_refcursor) as begin open rcursor for select decode(row_number() over (partition by deptno order by ename), 1, deptno, null ) deptno, t.ename from scott.emp t; end ; /--使用sqlplus执行上面创建的带有游标 输出参数的存储过程
SQL > var cur refcursor SQL > exec test1(:cur); PL/ SQL procedure successfully completed. SQL > print cur; DEPTNO ENAME -------- -- ---------- 10 CLARK KING MILLER 20 ADAMS FORD JONES SCOTT SMITH 30 ALLEN BLAKE JAMES DEPTNO ENAME -------- -- ---------- MARTIN TURNER WARD 14 rows selected.查看更多关于Oracle存储过程详解(引用）+补充的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did97485