Oracle 知识点

Oracle 知识点

Oracle 存储过程中的知识点

1、Oracle 存储过程的创建与调用

知识点1：Oracle存储过程的创建与执行（Oracle中称为“执行存储过程”，MySQL中称为“调用存储过程”。除了说法不同外，两者的语法也有所不同！）

-- 创建Oracle存储过程
CREATE OR REPLACE PROCEDURE testpro
	(num_id IN NUMBER, 
	rowVar_student OUT student%ROWTYPE) IS
BEGIN
	SELECT * into rowVar_student FROM student where id=num_id;
  dbms_output.put_line('ID:'||rowVar_student.id||', name:'||rowVar_student.name||', sex:'||rowVar_student.sex||', age:'||rowVar_student.age); --控制台输出
END;
/




-- 调用Oracle存储过程
--SET serveroutput ON
DECLARE 
  rowVar_student student%ROWTYPE;
BEGIN
  testpro(2, rowVar_student);
  --dbms_output.put_line('ID:'||rowVar_student.id||', name:'||rowVar_student.name||', sex:'||rowVar_student.sex||', age:'||rowVar_student.age);
END;
/

特别注意：
（1）创建存储过程，除了可以在命令行窗口执行，也可以在SQL窗口执行。但是执行存储过程，只能在命令行窗口执行，在SQL窗口中无法执行成功（会提示“ORA-00900: 无效 SQL 语句”）。
（2）在命令行窗口执行存储过程等PL/SQL语言编程时，最后需要输入/执行。

知识点2：当Oracle存储过程中涉及数据表操作时，需要 commit; 才能更新到数据库。

create or replace procedure mypro(param_id IN NUMBER) is
BEGIN
	insert into student(id,name,sex,age) values(param_id,'小红',1,15);
	dbms_output.put_line('插入记录成功！');
end;
/


execute mypro(8)
commit;

特别注意：
当存储过程中涉及数据表操作（增加，修改，删除）,那么在执行完存储过程后，切记一定要执行commit;语句，才能将结果更新到数据库中！

知识点3：Oracle执行存储过程的方法。

Oracle执行存储过程的方法：

execute mypro(7)

等价于

begin
  mypro(7);
end;
/

特别注意：
（1）"execute 存储过程名"后面可以不用分号;，而“begin…end”中的存储过程名后面必须要有分号;。
（2）“begin…end”后面必须要有分号;，不然会报错。

2、Oracle 存储过程的创建与调用2

知识点4：存储过程返回参数中使用游标的案例

--创建存储过程，用于分页查询
--传入参数：pageNo 查询的页码，pageSize 每页的条数；输出参数：vrows 使用一个引用游标用于接收多条结果集。普通游标无法做到，只能使用引用游标
create or replace procedure pro_query_emp_limit(pageNo in number,pageSize in number,vrows out sys_refcursor) is
begin
  --存储过程中只进行打开游标，将 select 查询出的所有数据放置到 vrows 游标中，让调用着进行获取
open vrows for select t.empno,t.ename,t.job,t.mgr,t.hiredate,t.sal,t.comm,t.deptno from (select rownum r,t1.* from scott.emp t1) t 
     where t.r between ((pageNo-1) * pageSize+1) and pageNo * pageSize;
end;
/



 
--数据库中使用引用游标读取上面的存储过程返回的值。下面只是加深理解，和 java 调用无关
declare
     vrows sys_refcursor ;--声明引用游标
     vrow scott.emp%rowtype; --定义变量接收遍历到的每一行数据
begin
     pro_query_emp_limit(4,3,vrows);--调用存储过程
     loop 
       fetch vrows into vrow; -- fetch into 获取游标的值
       exit when vrows%notfound; -- 如果没有获取到值，则退出循环
       dbms_output.put_line('编号：'|| vrow.empno || ' 姓名：'|| vrow.ename || ' 薪水：'|| vrow.sal);
     end loop;
end;
/