oracle存储过程之入门1

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

一、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;
--调用存储过程
CALL insert_emp_proc(999,'三九系列','坑爹',7689,SYSDATE,1800,4000,30);

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;
使用动态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;
--
CALL update_emp_proc(999,'123')

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;--将游标中的值赋给rowresult
    EXIT WHEN sel_emp%NOTFOUND;--判断：游标不存在时跳出循环
    dbms_output.put_line('员工名：'||rowresult.ename||'工资：'||rowresult.sal);
  END LOOP;
  CLOSE sel_emp;--关闭游标
  END;
----调用 
CALL selAll_emp_proc();