oracle存储过程详解

张兴康

1. 过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
   
2. 示例1：声明存储过程，该过程返回dept表行数
   
3. DECLARE
   
4. PROCEDURE getDeptCount
   
5. AS
   
6. deptCount INT;
   
7. BEGIN
   
8. SELECT COUNT(*) INTO deptCount FROM DEPT;
   
9. DBMS_OUTPUT.PUT_LINE('DEPT表的共有记录数：'||deptCount);
   
10. END getDeptCount;
    
11. BEGIN
    
12. getDeptCount[()];
    
13. END;

复制代码

1. 注意：此存储过程getDeptCount只在块运行时有效。
   
2. 示例2：创建不带参数的存储过程，该过程返回dept表行数
   
3. CREATE OR REPLACE PROCEDURE getDeptCount
   
4. AS | IS
   
5. deptCount int;
   
6. BEGIN
   
7. SELECT COUNT(*) INTO deptCount FROM dept;
   
8. DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录');
   
9. END [getDeptCount];

复制代码

1. 当我们创建的存储过程没有参数时，在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分，存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样，EXCEPTION和声明部分都是可选的。
   
2. 当我们创建的过程带有错误时，我们可以通过SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
   
3. 使用以下代码可以执行存储过程：
   
4. BEGIN
   
5. getDeptCount;
   
6. END;
   
7. 以上存储过程还可以通过以下代码来简化调用：
   
8. EXEC getDeptCount[;]
   
9. CALL  getDeptCount();

复制代码

• 并不是所有的存储过程都可以用这种方式来调用
• 定义无参存储过程时，存储过程名后不能加()
• 在块中或是通过EXEC调用存储过程时可以省略()
• 通过CALL调用无参存储过程必须加上()
  

1. 示例3：创建带有输入参数的存储过程，该过程通过员工编号打印工资额
   
2. CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)  --参数的数据类型不能指定长度
   
3. AS
   
4. salary emp.sal%TYPE;
   
5. BEGIN
   
6. SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
   
7. DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);
   
8. EXCEPTION
   
9. WHEN NO_DATA_FOUND THEN
   
10. DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
    
11. END;

复制代码

1. 当定义的存储过程含有参数时，参数的数据类型不能指定长度。参数还有输入和输出之分，本例中没有指定，默认情况为输入参数，也可显示的指定某个参数是输入参数，如(eNo IN NUMBER)。同示例1不同，该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程：
   
2. BEGIN
   
3. getSalaryByEmpNo(7788);
   
4. END;
   
5. 或者
   
6. EXEC getSalaryByEmpNo(7788);  或者
   
7. CALL getSalaryByEmpNo(7788);
   
8. 但是如果传给一个存储过程的参数是变量时，必须使用BEGIN  END块，如下：
   
9. DECLARE
   
10. no emp.empNo%TYPE;
    
11. BEGIN
    
12. no:=7788;
    
13. getSalaryByEmpNo(no);
    
14. END;

复制代码

1. 如果某个包中含有常量，也可以通过如下的方式调用：
   
2. EXEC getSalaryByEmpNo(ConstantPackage.no);
   
3. 但这种方式不能再使用CALL调用。
   
4. 示例4：创建含有输入和输出参数的存储过程，该过程通过员工编号查找工资额，工资额以输出参数返回
   
5. CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)
   
6. AS
   
7. BEGIN
   
8. SELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;
   
9. EXCEPTION
   
10. WHEN NO_DATA_FOUND THEN
    
11. DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
    
12. END;

复制代码

1. 当过程中含有输出参数时，调用时必须通过BEGIN  END块，不能通过EXEC或CALL调用。如：
   
2. DECLARE
   
3. salary NUMBER(7,2);
   
4. BEGIN
   
5. getSalaryByEmpNo(7788,salary);
   
6. DBMS_OUTPUT.PUT_LINE(salary);
   
7. END;

复制代码

张兴康

fteair

张兴康

张兴康

fteair

fteair

张兴康

fteair

张兴康