存储过程:
存储过程是 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、插入数据存储过程
无参存储过程
- CREATE OR REPLACE PROCEDURE insert_emp_proc AS
- BEGIN
- INSERT INTO emp VALUES(7777,'Hello Java','SALESMAN',7839,SYSDATE,1800,4000,30);
- END;
-
- CALL insert_emp_proc();
有参存储过程
- CREATE OR REPLACE PROCEDURE insert_emp_proc(myempno NUMBER,myename VARCHAR2,myjob VARCHAR2,mymgr NUMBER,mydate DATE,mysal NUMBER,mycomm NUMBER,mydeptno NUMBER)
- AS
- BEGIN
- INSERT INTO emp VALUES(myempno,myename,myjob,mymgr,mydate,mysal,mycomm,mydeptno);
- END;
- <pre name="code" class="sql">
3、更新数据存储过程
- CREATE OR REPLACE PROCEDURE update_emp_proc(myempno IN NUMBER,myename IN VARCHAR2) AS
- BEGIN
- UPDATE emp SET ename=myename WHERE empno=myempno;
- END;
- <pre name="code" class="sql">CREATE OR REPLACE PROCEDURE update_emp_proc(myempno NUMBER,myename VARCHAR2) AS
- mysql VARCHAR2(500);
- BEGIN
- mysql:='UPDATE emp SET ename=:1 WHERE empno=:2';
- EXECUTE IMMEDIATE mysql USING myename,myempno;
- END;</pre><pre name="code" class="sql">
- CALL update_emp_proc(999,'123')</pre>
4、删除数据存储过程
- CREATE PROCEDURE del_emp_proc(myemp NUMBER)
- AS
- BEGIN
- DELETE emp WHERE empno=myemp;
- END;
-
- CALL del_emp_proc(999);
5、查询数据存储过程
①、查询单条数据
-
- CREATE OR REPLACE PROCEDURE selname_emp_proc(myempno IN NUMBER,myname OUT VARCHAR2)
- AS
- BEGIN
- SELECT ename INTO myname FROM emp WHERE empno=myempno;
- dbms_output.put_line(myname);
- END;
-
- DECLARE
- myname VARCHAR2(50);
- BEGIN
- selname_emp_proc(7788,myname);
- END;
②、查询所有数据
PL/SQL 中 SELECT 语句只返回一行数据。如果超过一行数据,那么就要使用显式游标,INTO 子句中要有不 SELECT子句中相同列数量的变量。INTO 子句中也可以是记录变量。
-
- CREATE OR REPLACE procedure selAll_emp_proc
- AS
- CURSOR sel_emp IS SELECT * FROM emp;
- rowresult emp%ROWTYPE;
- BEGIN
- OPEN sel_emp;
- LOOP FETCH sel_emp INTO rowresult;
- EXIT WHEN sel_emp%NOTFOUND;
- dbms_output.put_line('员工名:'||rowresult.ename||'工资:'||rowresult.sal);
- END LOOP;
- CLOSE sel_emp;
- END;
- <pre name="code" class="sql">
感谢原著分享:http://blog.csdn.net/u011955534/article/details/14647217