Oracle存储过程-增删改查与调用

存储过程：

存储过程是 SQL, PL/SQL, Java 语句的组合， 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。是存放在数据库服务器上的SQL语句块，其效率高于同等SQL语句6-10倍

下面通过例子让你了解存储过程对数据的增删查改（对oracle中的emp操作）

一、Oracle存储过程语法：

Create [or replace] procedure 存储过程名称 (输入参数或输出参数)]as

变量

Begin

执行主体

End；

二、IN, OUT , IN OUT 用来修饰参数。

IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。

OUT 表示 PRCEDURE 通过这个变量将值传回给调用者。

IN OUT 则是这两种的组合。

三、执行存储过程方式：

1、Call 存储过程名称（参数）；

2、Execute 存储过程名称（参数）；

注意：在oracle 数据库中，call命令任何窗口都能使用，但是execute只能在命令窗口使用，否则会报无效的SQL语句的异常。

四、在存储过程中需要注意事项：

1、在oracle数据库存储过程中，表别名不能用as

2、在oracle数据库存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录则必须使用游标处理

3、在使用select....into....时必须保证数据库有该数据，否则报”no data found”异常

4、在存储过程中，别名不能和字段名相同，否则虽然编译能通过，但是运行结果会报错

五、存储过程基本语法

1、无参存储过程

基本格式

CREATE PROCEDURE 存储过程名字

  AS

  BEGIN

    --执行的内容

END;

2、插入数据存储过程

无参存储过程

[sql]  view plain  copy

1. CREATE OR REPLACE PROCEDURE insert_emp_proc AS  
2. BEGIN  
3.   INSERT INTO emp VALUES(7777,'Hello Java','SALESMAN',7839,SYSDATE,1800,4000,30);  
4.   END;  
5. --调用存储过程  
6. CALL insert_emp_proc();  

有参存储过程

[sql]  view plain  copy

1. CREATE OR REPLACE PROCEDURE insert_emp_proc(myempno NUMBER,myename VARCHAR2,myjob VARCHAR2,mymgr NUMBER,mydate DATE,mysal NUMBER,mycomm NUMBER,mydeptno NUMBER)  
2. AS  
3. BEGIN  
4.  INSERT INTO emp VALUES(myempno,myename,myjob,mymgr,mydate,mysal,mycomm,mydeptno);  
5. END;  
6. <pre name="code" class="sql">--调用存储过程</pre>CALL insert_emp_proc(999,'三九系列','坑爹',7689,SYSDATE,1800,4000,30);  

3、更新数据存储过程

[sql]  view plain  copy

1. CREATE OR REPLACE PROCEDURE update_emp_proc(myempno IN NUMBER,myename IN VARCHAR2) AS  
2. BEGIN  
3.   UPDATE emp SET ename=myename WHERE empno=myempno;  
4.   END;  

[sql]  view plain  copy

1. 使用动态SQL时：  

[sql]  view plain  copy

1. <pre name="code" class="sql">CREATE OR REPLACE PROCEDURE update_emp_proc(myempno NUMBER,myename VARCHAR2) AS  
2. mysql VARCHAR2(500);  
3. BEGIN  
4.   mysql:='UPDATE emp SET ename=:1 WHERE empno=:2';  
5.   EXECUTE IMMEDIATE mysql USING myename,myempno;  
6.   END;</pre><pre name="code" class="sql">--  
7. CALL update_emp_proc(999,'123')</pre>  

4、删除数据存储过程

[sql]  view plain  copy

1. CREATE PROCEDURE del_emp_proc(myemp NUMBER)  
2. AS  
3. BEGIN  
4.   DELETE emp WHERE empno=myemp;  
5.   END;  
6.   
7. CALL del_emp_proc(999);  

5、查询数据存储过程

①、查询单条数据

[sql]  view plain  copy

1. ----查询数据存储过程  
2. CREATE OR REPLACE PROCEDURE selname_emp_proc(myempno IN NUMBER,myname OUT VARCHAR2)  
3. AS  
4. BEGIN  
5.   SELECT ename INTO myname FROM emp WHERE empno=myempno;  
6.   dbms_output.put_line(myname);  
7.   END;  
8. ----调用  
9. DECLARE  
10. myname VARCHAR2(50);  
11. BEGIN  
12.   selname_emp_proc(7788,myname);  
13.   END;  

②、查询所有数据

PL/SQL 中 SELECT 语句只返回一行数据。如果超过一行数据，那么就要使用显式游标，INTO 子句中要有不 SELECT子句中相同列数量的变量。INTO 子句中也可以是记录变量。

[sql]  view plain  copy

1. --------用游标显示查询所有的结果  
2. CREATE OR REPLACE procedure selAll_emp_proc  
3. AS  
4. CURSOR sel_emp IS SELECT * FROM emp;--定义游标,该游标指向查询结果  
5. rowresult emp%ROWTYPE;  
6. BEGIN  
7.   OPEN sel_emp;--打开游标  
8.   LOOP FETCH sel_emp INTO rowresult;--将游标中的值赋给rowresult  
9.     EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环  
10.     dbms_output.put_line('员工名：'||rowresult.ename||'工资：'||rowresult.sal);  
11.   END LOOP;  
12.   CLOSE sel_emp;--关闭游标  
13.   END;  
14. <pre name="code" class="sql">----调用<span style="font-family:Arial,Helvetica,sans-serif"> </span></pre>CALL selAll_emp_proc(); 

感谢原著分享：http://blog.csdn.net/u011955534/article/details/14647217