-
returning
2010-05-18 14:29:00RETURNING JIHUA_NUM, SHIJ_NUM INTO??????????参考的资料是《PL/SQL用户指南与参考》2、赋值语句Returning: insert into t1 values ( 90 , SERVICE , BEIJING ) Returning rowid , name into row_id,info;RETURNING JIHUA_NUM, SHIJ_NUM INTO??????????
参考的资料是《PL/SQL用户指南与参考》
2、赋值语句Returning:insert into t1 values ( 90 , 'SERVICE' , 'BEIJING' )
Returning rowid , name into row_id,info;
注:只限insert、update、delete一条记录时进行赋值3、自定义参数——&:可SQL和PLSQL中均可使用自定义参数,即需要自己输入值的参数select * from ldcom where comcode=&a andname=&b;PLSQL:declare i int := &a; n int := &b;4、绑定变量的使用:可使用绑定变量的办法提高SQL效率SQL> variable x number ;
SQL> exec :x := 8600 ;
SQL> select * from ldcom where comcode= :x ;
注:PLSQL中的declare即隐士绑定,无需再申明可用Print查看绑定变量 SQL> print x;也可使用查询 SQL> select :x from dual;实际的简单应用:variable x number ;
declare
v_date date ;
begin
for i in 1 .. 10 loop
:x := i;
select sysdate +:x into v_date from dual;
dbms_output.put_line(v_date);
end loop ;
end ;
/
在execute immediate中的应用:declare
v_x t1.num% type ;
begin
execute immediate 'update t1 set num=8888 where id=:a returning num into :b'
using 2 returning into v_x;
dbms_output.put_line(v_x);
end ;
注意returning的返回值在动态SQL中的操作格式
5、%TYPE和%ROWTYPE的区别:%TYPE针对某一字段类型(数组也是单一的类型)%ROWTYPE针对某一整表的类型(游标也是整表)6、计数循环的逆序法:for i in reverse 10 .. 100 loop
注意reverse的位置不要记错7、一般的游标使用法:1、游标的正常使用需要四步①定义游标cursor c2(dept_no number default 10 ) is----注意定义dept_no的方法
select name ,agentcode from laagent where rownum <= dept_no;
②打开游标open c3(dept_no => 20 ); ---- 可以重新定义 dept_no
③提取游标数据fetch c2 into dept_name,dept_loc; ---- 字符类型、个数相等
fetch c3 into deptrec; ----deptrec 为 rowtype
exit when c3% notfound ;
④关闭游标CLOSE c3;
⑤游标属性
% FOUND -- 布尔型属性,当最近一次读记录时成功返回 , 则值为 TRUE ;
% NOTFOUND -- 布尔型属性,与 %FOUND 相反;
% ISOPEN -- 布尔型属性,当游标已打开时返回 TRUE ;
% ROWCOUNT -- 数字型属性,返回已从游标中读取的记录数。
2、 使用for自动打开、提取、关闭游标FOR c1_rec IN c1 LOOP
FOR c1_rec IN ( SELECT dname, loc FROM dept) LOOP
3、注:单一的SQL语句都是一个隐式的游标,属性为SQL% FOUND -- 布尔型属性,当最近一次读记录时成功返回 , 则值为 TRUE ;
SQL% NOTFOUND -- 布尔型属性,与 %FOUND 相反;
SQL% ISOPEN -- 布尔型属性,当游标已打开时返回 TRUE ;
SQL% ROWCOUNT -- 数字型属性,返回已从游标中读取的记录数。
例如:DELETEFROM emp WHERE deptno=v_deptno;IF SQL % NOTFOUND THEN ...
8、For Update锁定数据:CURSOR emp_cursor is select empno,sal
from emp where deptno=v_deptno for update of sal nowait ;
注意:其中的of sal,for update可以精确到某一个或几个字段在使用了For Update之后可以在delete和update语句中使用current of cursor_name子句for emp_record in emp_cursor loop
if emp_record.sal < 1500 then
update emp set sal= 1500 where current of emp_cursor;
end if ;
end loop ;
但需注意:只能针对for update的表进行修改。
9、Package中的子程序可以重载即函数名相同,参数个数不同10、PL/SQL的执行顺序在同一Package中,若要使用自身定义的Function或Procedure,则必须是之前定义过的。同理,即便使用参数,也必须在前面定义过。也就是说:一般最终的执行程序,会放在Package的最后,除非将所有部分都申明成Public。 -
Returning
2010-12-31 20:34:26我们经常做的事情是创建一个对象,操作这个对象,然后返回这个对象: [code="ruby"] def build_options options = Hash.new options[:key1] = 'value1' ...Rails提供了returning,使用ruby最常...我们经常做的事情是创建一个对象,操作这个对象,然后返回这个对象:
def build_options
options = Hash.new
options[:key1] = 'value1'
options[:key2] = 'value2'
options
end
Rails提供了returning,使用ruby最常用的block技术,简化这个步骤:
class Object
def returning(value)
yeild (value)
return value
end
end
现在我们的代码:
def build_options
returning Hash.new do |options|
options[:key1] = 'value1'
options[:key2] = 'value2'
end
end -
RETURNING
2009-04-21 16:17:00利用 DML的RETURNING 语句可以返回刚刚操作的记录的信息,然后可以马上用于判断,这样避免了再次使用SQL语句做一次SELECT,提高了程序的性能,具体例子如下,注意UPDATE和DELETE返回值的差别: CREATE OR REPLACE ...利用 DML的RETURNING 语句可以返回刚刚操作的记录的信息,然后可以马上用于判断,这样避免了再次使用SQL语句做一次SELECT,提高了程序的性能,具体例子如下,注意UPDATE和DELETE返回值的差别:
CREATE OR REPLACE PROCEDURE test_returning
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
INSERT INTO emp(empno,ename,sal)VALUES(1234,'SHADOW',4000)
RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(SQL%ROWCOUNT);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);UPDATE emp SET sal=3000 WHERE empno=1234
RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(SQL%ROWCOUNT);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
DELETE FROM emp WHERE empno=1234
RETURNING ename,sal INTO v_ename,v_sal;
dbms_output.put_line(SQL%ROWCOUNT);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_sal);
END;
/运行如下:
SQL> @g:/orcl/returning.sql
过程已创建。
SQL> exec test_returning
1
SHADOW
4000
1
SHADOW
3000
1
SHADOW
3000PL/SQL 过程已成功完成。
参考http://hi.baidu.com/kongzong/blog/item/184f97cafd39ff86c8176825.html
-
Support RETURNING
2021-01-11 11:28:37t really about returning itself, but about making it easy to support RETURNING. I've tried to break it down to individually review-able commits. There's one bigger commit "Combine router ... -
Returning nil
2020-12-08 22:25:36<div><p><code>underKeyboardLayoutConstraint.setup...<p>After doing this my app keeps crashing and returning nil value</p><p>该提问来源于开源项目:evgenyneu/UnderKeyboard</p></div> -
Returning clause
2021-01-01 16:38:58<div><p>Thanks for your project, that help with writing pure SQL queries 🙂...Http://knexjs.org/#Builder-returning</p>该提问来源于开源项目:sqlkata/querybuilder</p></div> -
returning error
2021-01-08 14:57:50<p>I am hoping for something as simple returning status_code=400 for bad request and a list of errors to the front end</p><p>该提问来源于开源项目:graphql-python/graphene-django</p></div> -
Postgres RETURNING
2021-01-03 04:07:33m reading book about Postgres and I would like to use RETURNING *. I found it isn't possible for now in Linq2db. <p>Is any simple way how can I implement in my project? Can you give me advice how ... -
returning costomers
2020-12-08 19:01:53<p>It would be very helpful, if the clients list could indicate returning costomers. <p><em>Original issue: http://code.google.com/p/fritzing/issues/detail?id=1684</em></p>该提问来源于开源项目... -
Returning nil when returning NULL shared pointers
2020-11-26 10:02:58<div><p>Returning a null RefCountedObjectPtr or RefCounterPtr must return nil into Lua. <p>Currently it returns a userdata with NULL inside.</p><p>该提问来源于开源项目:vinniefalco/LuaBridge</p>... -
returning index position instead of returning value
2020-12-27 07:54:47<div><ul>[X] I have searched ...<p><code>MultiSlider</code> onChange returning index position instead of returning value.</p><p>该提问来源于开源项目:ptomasroos/react-native-multi-slider</p></div> -
RETURNING语句
2016-05-23 15:06:301.DELETE的情况,RETURNING返回的肯定是DELETE之前的结果; INSERT的情况,RETURNING返回的一定是INSERT之后的结果。 UPDATE的情况,RETURNING返回的一定是UPDATE之后的结果。 2.RETURNING语句似乎和RETURN通用。...1.DELETE的情况,RETURNING返回的肯定是DELETE之前的结果;
INSERT的情况,RETURNING返回的一定是INSERT之后的结果。
UPDATE的情况,RETURNING返回的一定是UPDATE之后的结果。
2.RETURNING语句似乎和RETURN通用。(即两个可以相互替换)
3.INSERT INTO VALUES语句支持RETURNING语句,而INSERT INTO SELECT语句不支持。
4.MERGE语句不支持RETURNING语句。
ORACLE的DML语句中可以指定RETURNING语句。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。
在使用RETURNING子句是应注意以下几点限制:
1.不能与DML语句和远程对象一起使用;
2.不能检索LONG类型信息;
3.当通过视图向基表中插入数据时,只能与单基表视图一起使用。
问题:
使用UPDATE语句的时候,RETURNING得到的结果是UPDATE之前的结果还是UPDATE之后的结果?
例:SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30)); 表已创建。
SQL> SET SERVEROUT ON SQL> DECLARE V_NAME VARCHAR2(30); BEGIN INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME); V_NAME := NULL; UPDATE T SET NAME = 'YTK' RETURNING NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME); V_NAME := NULL; DELETE T RETURNING NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME); END; / INSERT: YANGTK UPDATE: YTK DELETE: YTK PL/SQL 过程已成功完成。
显然,UPDATE操作的RETURNING语句是返回UPDATE操作之后的结果。
顺便总结几个RETURNING操作相关的问题:
1.RETURNING语句似乎和RETURN通用。SQL> SET SERVEROUT ON SQL> DECLARE V_NAME VARCHAR2(30); BEGIN INSERT INTO T VALUES (1, 'YANGTK') RETURN NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('INSERT: ' || V_NAME); V_NAME := NULL; UPDATE T SET NAME = 'YTK' RETURN NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('UPDATE: ' || V_NAME); V_NAME := NULL; DELETE T RETURN NAME INTO V_NAME; DBMS_OUTPUT.PUT_LINE('DELETE: ' || V_NAME); END; / INSERT: YANGTK UPDATE: YTK DELETE: YTK PL/SQL 过程已成功完成。
2.RETURNING语句也可以使用SQLPLUS的变量,这样,RETURNING语句不一定非要用在PL/SQL语句中。SQL> VAR V_NAME VARCHAR2(30) SQL> INSERT INTO T VALUES (1, 'YANGTK') RETURNING NAME INTO :V_NAME; 已创建 1 行。 SQL> PRINT V_NAME V_NAME -------------------------------- YANGTK SQL> UPDATE T SET NAME = 'YTK' RETURNING NAME INTO :V_NAME; 已更新 1 行。 SQL> PRINT V_NAME V_NAME -------------------------------- YTK SQL> DELETE T RETURNING NAME INTO :V_NAME; 已删除 1 行。 SQL> PRINT V_NAME V_NAME -------------------------------- YTK
3.INSERT INTO VALUES语句支持RETURNING语句,而INSERT INTO SELECT语句不支持。MERGE语句不支持RETURNING语句。SQL> MERGE INTO T USING (SELECT * FROM T) T1 ON (T.ID = T1.ID) WHEN MATCHED THEN UPDATE SET NAME = T1.NAME WHEN NOT MATCHED THEN INSERT VALUES (T1.ID, T1.NAME) RETURNING NAME INTO :V_NAME; RETURNING NAME INTO :V_NAME *第 5 行出现错误: ORA-00933: SQL 命令未正确结束
SQL> INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME; INSERT INTO T SELECT * FROM T RETURNING NAME INTO :V_NAME *第 1 行出现错误: ORA-00933: SQL 命令未正确结束
-
hang after returning certificate
2020-12-09 07:17:03Returning certificate Returning key pair Making certificate for WebRTC Returning certificate Session:8792605950890241566 Old state:STATE_INIT New state:STATE_RECEIVEDTERMINATE Type:urn:xmpp:jingle:... -
WaitForNotification Returning Nil
2021-01-09 05:30:00The result of <code>WaitForNotification</code> sometimes returning nil on both returning values. How to resolve this problem?</p><p>该提问来源于开源项目:jackc/pgx</p></div> -
Delete with Returning
2021-01-11 00:18:10ve noticed that the <code>ORM</code> does not support <code>Returning()</code> with <code>Delete(), at least that's my assumption looking ... -
oracle RETURNING 子句使用方法
2020-12-16 04:37:25RETURNING 自己通常结合DML 语句使用。(INSERT UPDATE DELETE) 使用方法: UPDATE table_name SET expr1 RETURNING column_name INTO xxx INSERT: 返回的是添加后的值 UPDATE:返回时更新后的值 DELETE:返回删除... -
Add RETURNING keyword
2020-12-02 06:09:52<div><p>Postgresql has a keyword <code>RETURNING</code> used in insert/update/delete commands</p><p>该提问来源于开源项目:andialbrecht/sqlparse</p></div> -
Returning URLs
2018-12-29 15:11:37返回 URL (Returning URLs) 区别 REST 架构风格与其他基于网络风格的中心特征是它强调组件之间的统一接口。—— Roy Fielding,架构风格与基于网络的软件体系结构设计 通常,从 Web API 返回绝对 URI (例如 ... -
update with returning: true option not returning updated object
2020-12-08 18:03:34}, { where: { id }, returning: true }).then( (updatedUsers, [user]) => res.json(user), error => res.send(error) ); I wanted to do this with the sequelize-typescript version but I'm ... -
PYMOBUS returning none
2020-11-26 17:41:35<div><p>I am using pymodbus library to set up ... When I try reading registers, it is returning "none" How do i solve this?</p><p>该提问来源于开源项目:riptideio/pymodbus</p></div> -
Added no_returning option for Postgres to insert without RETURNING clause
2021-01-09 02:39:55<div><p>Motivation for this PR: enable import without <code>RETURNING</code> clause (right now for every INSERT statement <code>RETURNING</code> clause is applied). This will allow to use Postgres ... -
Error returning json
2020-12-26 00:39:34<div><p>https://osf.io/mrajv/files/Proposal_Replication.docx/</p> <p>is returning json. <p>Errors should never return json. 该提问来源于开源项目:CenterForOpenScience/osf.io</p></div> -
Returning irregular arrays
2020-11-29 21:54:16<div><p>I am having problems returning irregular arrays to a UDF. As a simple example, calling the following code: <pre><code> .func def rtn_array(x, y): return x, y </code></pre> <p>If x and y are ... -
ADID returning nil
2020-12-26 07:42:35<div><p>Anywhere we try ... it always returning nil. Everything else seems to be working as expected. <p>SDK version: 4.18.3 iOS version: 13.2.3</p><p>该提问来源于开源项目:adjust/ios_sdk</p></div> -
Insert without RETURNING
2021-01-11 01:54:57<div><p>Hey, <p>I'm having data races when ... Is there a way to generate queries that do not use the <code>RETURNING</code> clause? <p>Thanks</p><p>该提问来源于开源项目:go-pg/pg</p></div> -
insert returning id
2020-11-30 22:01:53insert returning id" (http://www.postgresql.org/docs/9.4/static/sql-insert.html) functionality, is it possible to use this with existing functionality or is this something not yet implemented?</p>...
-
西门子PLC实例教程777个(密码1234).zip
-
一尺之棰
-
C# chart实时数据显示简单示例工程文件
-
VASP安装教程(centos)
-
在linux:ubuntu下搭建vsftpd
-
游戏代码-LOL卡萨丁斩杀伤害计算器
-
Kmeans_demo.zip
-
【数据分析-随到随学】互联网行业业务指标及行业数
-
C/C++编程全家桶(Daozy极限编程)
-
LEInstaller.exe
-
RabbitMQ消息中间件实战(附讲义和源码)
-
【数据分析-随到随学】SPSS调查问卷统计分析
-
单片机完全学习课程全五季套餐
-
gcruntime-7.4.1-windows-installer.exe
-
2021最新Kubernetes(k8s)集群实战精讲
-
【数据分析实战训练营】Hive详解
-
GlobeLand30中国区域土地利用数据3年完整成品数据(2000-2010-2020)下载地址
-
8086+8253+8259+8255可设置时间时钟
-
JavaEE框架(Maven+SSM)全程实战开发教程(源码+讲义)
-
商业银行会计学的经典资料.zip