oracle RETURNING 子句使用方法_Oracle

oracle RETURNING 子句使用方法

2019-12-26 14:23mdxy-dxy Oracle

这篇文章主要介绍了oracle RETURNING 子句使用方法,需要的朋友可以参考下

RETURNING 自己通常结合DML 语句使用。（INSERT UPDATE DELETE）

使用方法：

				?

									UPDATE table_name SET expr1

									RETURNING column_name

									INTO xxx

INSERT: 返回的是添加后的值
UPDATE：返回时更新后的值

DELETE：返回删除前的值

RETURNING 可以再sqlplus 和plsql中使用

如果是plsql就如上面的代码，xxx为声明的变量名

如果是sqlplus，xxx 可以为变量，即

				?

									VARIABLE var_name varchar2(10)

									UPDATE table_name SET expr1

									RETURNING column_name INTO :var_name;

这里的 :var_name 使用的是绑定变量

另外，RETURNING 貌似可以与 RETURN通用

INSERT INTO VALUES 支持 RETURNING

INSERT INTO SELECT、和MERGE 语句不支持 RETURNING

例子1：

建表语句：

				?

									CREATE TABLE TEST111(

									    A1 VARCHAR(10),

									    A2 VARCHAR(20)

									);

									CREATE SEQUENCE TEST111_S1

									START WITH 1

									INCREMENT BY 1

									CACHE 20

									MAXVALUE 999999999999999999999999999

									CYCLE;

				?

									DECLARE

									 SEQ NUMBER;

									BEGIN

									 INSERT INTO TEST111 VALUES(TEST111_S1.NEXTVAL,'AAA2')

									 RETURNING A1 INTO SEQ;

									 DBMS_OUTPUT.PUT_LINE(SEQ);

									END; 

									DECLARE

									 SEQ NUMBER;

									BEGIN

									 INSERT INTO TEST111 VALUES(TEST111_S1.NEXTVAL,'AAA3');

									 SELECT TEST111_S1.CURRVAL INTO SEQ FROM DUAL;

									 COMMIT;

									 DBMS_OUTPUT.PUT_LINE(SEQ);

									END;

例子2：
再另外，RETURNING 可以与BULK COLLECT 结合（批量绑定，另外一个是 FORALL）

				?

									DECLARE

									TYPE table_type IS TABLE OF column_name%TYPE;

									v_tab table_type;

									BEGIN

									 UPDATE table_name

									 SET expr1

									 RETURNING column_name BULK COLLECT INTO v_tab;

									 FOR i IN v_tab.first .. v_tab.last LOOP

									 DBMS_OUTPUT.put_line( l_tab(i));

									END LOOP;

									COMMIT;

									END;

ORA-06547:INSERT,UPDATE或DELETE语句必须使用RETURNING子句

产生这个错误的原因：

returning into子句作用于insert，update，delete，上而select则不行，应该用into。

报错的存储如下：

				?

									create or replace procedure p_stu_info(s_id number, s_name varchar2) is

									 v_name  varchar2(10);

									 v_age  number;

									 v_ErrMsg varchar2(200);

									begin

									 execute immediate 'select name,age from student_test where id=:1 and name=:2'

									  using s_id, s_name

									  returning into v_name, v_age;

									 dbms_output.put_line(v_name || '的年龄为：' || to_char(v_age));

									exception

									 when others then

									  v_ErrMsg := SUBSTRB(SQLERRM, 1, 200);

									  dbms_output.put_line('找不到相应学生');

									end p_stu_info;

改成下面这样就ok了：

				?

									create or replace procedure p_stu_info(s_id number, s_name varchar2) is

									 v_name  varchar2(10);

									 v_age  number;

									 v_ErrMsg varchar2(200);

									begin

									 execute immediate 'select name,age from student_test where id=:1 and name=:2'

									  into v_name, v_age

									  using s_id, s_name;

									 dbms_output.put_line(v_name || '的年龄为：' || to_char(v_age));

									exception

									 when others then

									  v_ErrMsg := SUBSTRB(SQLERRM, 1, 200);

									  dbms_output.put_line('找不到相应学生，错误原因：'||v_ErrMsg);

									end p_stu_info;