Oracle存储过程及参数理解

大家好，又见面了，我是你们的朋友全栈君。一、过程 (存储过程)
    过程是一个能执行某个特定操作的子程序。使用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,salaryOUT 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’;

三、参数的理解