Oracle 存储

存储

存储过程是一个 PL/SQL 程序块，且过程被创建后以实体对象的形式保存在数据库中，以供用户使用。存储过程可以接受零个或多个参数作为输入（in）或输出（out）、或既作输入又做输出（in/out）。与函数不同，存储过程不能由 SQL语句直接使用，只能通过execut 命令或PL/SQL程序块内部调用。

create [or replace] procedure 过程名 [(参数列表)] is|as
    声明语句段 ; 
begin 
    执行语句段 ; 
[Exception 
    异常处理语句段 ;]
end 过程名; 

调用存储过程

直接调用存储过程：

-- 1.执行存储过程方式call/exec
call procedure_name();
execute procedure_name();
-- 也可以所以成如下形式
exec procedure_name();

-- 2.plsql代码块中直接执行存储过程：
DECLARE
BEGIN
  procedure_name();
END;  

--3.命令窗口执行存储过程:
execute procedure_name();

注：如果Oracle SQL Developer 中调用存储过程，想让 DBMS_OUTPUT.PUT_LINE 成功输出，就需要把SERVEROUTPUT 选项设置为ON状态。

存储过程的创建(无参数)

create or replace procedure myPro as
    cursor myCur is
        select * from db_user where age>24 ; --声明游标
begin
    for c1 in myCur loop --提取出一条游标中的数据赋值给整行记录变量c1
        dbms_output.put_line('从游标中取到的姓名:'||c1.name || ',年龄:' || c1.age);
        --插入数据
        insert into db_user2(id,name,age) values(c1.id,c1.name,c1.age); 
    end loop;
    commit;
end;

注：

• 过程创建完毕之后，在控制台并不会输出结果，或者过程中代码不会直接被执行，只是跟创建一个表对象一样，在 oracle 数据库中生成了一个存储过程对象。
• 且需要注意，无参数的情况不需要在过程后面加上括号'()'。
• 若有 INSERT,UPDATE,DELETE 一定需要输入 COMMIT 。

。带参数的存储创建

过程创建时有三种参数可供选择

• in 参数：读入参数，主程序向过程传递参数值。
• out 参数：读出参数，过程向主程序传递参数值。
• in out 参数：双向参数，过程与主程序双向交流。

CREATE OR REPLACE PROCEDURE PROC_out
(
    V_EMPNO IN NUMBER,
    v_ename OUT VARCHAR2) 
IS
BEGIN
    SELECT ename INTO v_ename FROM EMP WHERE EMPNO = V_EMPNO;
    --DBMS_OUTPUT.PUT_LINE('编号:' || V_EMPNO || ',姓名:' || v_ename);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('无此编号信息');
END;

--调用带out模式参数,必须通过plsql代码块方式直接调用
--当然也可以直接右键procedure_name选择‘test’进行输入输出
DECLARE
  v_x emp.ename%TYPE; 
BEGIN
  PROC_out(7369,v_x);
  DBMS_OUTPUT.PUT_LINE('编号:' || 7369 || ',姓名:' || v_x);
END; 

过程的执行权限授予其他用户

GRANT ALTER ANY TABLE TO u01;
GRANT EXECUTE ON scott.PROC_out TO u01;

--切u01用户调用scott用户存储过程
DECLARE
  v_x scott.emp.ename%TYPE; 
BEGIN
  scott.PROC_out(7369,v_x);
  DBMS_OUTPUT.PUT_LINE('编号:' || 7369 || ',姓名:' || v_x);
END;  

删除过程

DROP PROCEDURE procedure_name();

案例：

存储过程参数是动态游标类型

/*
过程名:proc_ref
目标:实现按照输入的部门编号,输出动态游标结果集
参数            模式     类型
  v_deptno        in       整数型
  v_emp           out      动态游标类型(Sys_Refcursor)
*/
create or replace procedure pc_ref(v_deptno IN number, v_emp OUT sys_refcursor) is
  v_e emp%rowtype;
begin
  open v_emp for 'select * from emp where deptno = :deptno' using v_deptno;
  loop
    fetch v_emp into v_e;
    exit when v_emp%notfound;
    dbms_output.put_line('姓名:' || v_e.ename || '  部门名称:' || v_e.deptno);
  end loop;
  close v_emp;
end;

--调用|执行存储过程
declare
  type mytype is ref cursor;
  v_emp mytype;
begin
  pc_ref(10, v_emp);
end;

授权用户测试过程或者函数脚本

grant debug any procedure to scott;
grant debug connect session to scott;
--grant debug any procedure,debug connect session to scott;
--plsql developer工具-->找到过程名-->右键添加调试信息-->右键测试