Oracle数据库学习笔记四——存储过程的值传递和引用传递

编程语言中的4种子例程：
由两种行为定义，即形式值是否返回以及参数列表是值传递还是引用传递。
如果返回输出，子例程就是函数，如果不返回，就是过程。
所以4中子例程为：
1.值传递函数
2.引用传递函数
3.值传递过程
4.引用传递过程


pl/sql值传递过程由下列5条规则定义：
1.所有形参必须使用in模式定义为只写变量。（只能传入，不能传出）
2.所有形参必须是局部作用域的变量，不能再过程执行期间改变。（不能给in模式的参数赋值）
3.所有形参可使用任意有效的sql或pl/sql数据类型
4.所有形参可以有默认的初值
5.任何从sql查询到函数的系统引用游标类型的强制转换都是不可写的，因此必须通过in模式参数进行传递。

  这包括哪些作为显示游标变量传递的以及使用cursor函数的强制转换。

--值传递：调用时接受值的副本，不返回输出变量。 
 --使用值传递过程跨单个事务作用域管理多个DML语句。
 
 create or replace procedure add_user(
   username varchar2,
   age number,
   address varchar2,
   street_address varchar2
 )  is
 v_id number;
 
 --声明内部函数（内部pl/sql块） 从指定序列中获得下一个值，使用了本地动态sql
 function get_sequence_value(sequence_name varchar2) return number is
    pragma autonomous_transaction;
    id_value number;
    statement varchar2(2000);
 begin
    statement :='begin'                              ||chr(10)
              ||' select '||sequence_name||'.nextval'||chr(10)
              ||' into :id_value'                    ||chr(10)
              ||' from dual;'                        ||chr(10) 
              ||'end;';   
    execute immediate statement using out id_value;
    return id_value;
  end get_sequence_value;
begin
    savepoint add_user;       --设置保存点
   
    v_id :=get_sequence_value('SQ_USER');
    
    insert into tb_user(userid,name,age) values(v_id,username,age);
      
    if address is not null then
         --向地址表中插入值
         if street_address is not null then
            --向街道表中插入值
          end if;
    end if;
    
    exception
       when others then
          rollback to add_user;   --回滚到保存点
          raise_application_error(-20001,sqlerrm);  --抛出异常
end add_user;

--引用传递过程

--被调用时接受值的副本，不返回输出变量，可改变实参的值。返回实参引用给调用程序单元。

pl/sql引用传递过程由下列5条规则定义：

1.必须至少有一个形参通过使用out或in out模式定义。

2.所有形参在过程操作中可更改的局部作用域变量。

3.所有形参可使用任意有效的sql或pl/sql数据类型。

4.所有in模式形参可以有默认的初值。

5.任何从sql查询到过程的系统引用游标类型的强制转换都是不可写的，因此必须通过in模式参数进行传递。

  （即作为参数的系统引用游标不能再写入数据，只能是in模式的参数）

--例：引用传递过程
 function get_sequence_value(sequence_name varchar2) return number is
    pragma autonomous_transaction;
    id_value number;
    statement varchar2(2000);
 begin
    statement :='begin'                              ||chr(10)
              ||' select '||sequence_name||'.nextval'||chr(10)
              ||' into :id_value'                    ||chr(10)
              ||' from dual;'                        ||chr(10) 
              ||'end;';   
    execute immediate statement using out id_value;
    return id_value;
  end get_sequence_value;
  
  
 create or replace procedure add_user(
   username varchar2,
   age number,
   userid out number,   --输出参数（引用传递）
 )  is
 v_id number;
begin
    savepoint add_user;       --设置保存点
   
    userid :=get_sequence_value('SQ_USER');  --为输出参数赋值
    
    insert into tb_user(userid,name,age) values(userid,username,age);
     
    exception
       when others then
          rollback to add_user;   --回滚到保存点
          raise_application_error(-20001,sqlerrm);  --抛出异常
end add_user;