mg4377娱乐娱城官网_mg4377娱乐手机版_www.mg4377.com

Oracle存款和储蓄进度重临游标实例详解,sqlserv

时间:2019-10-05 13:02来源:mg4377娱乐娱城官网
创建表: mg4377娱乐娱城官网,1.囤积进程 Oracle存款和储蓄进度重临游标实例详解,sqlserver带游标存款和储蓄进程及调用函数。存款和储蓄进程之四—游标,存款和储蓄进度游标 游标在仓

创建表:

mg4377娱乐娱城官网,1.囤积进程

Oracle存款和储蓄进度重临游标实例详解,sqlserver带游标存款和储蓄进程及调用函数。存款和储蓄进程之四—游标,存款和储蓄进度游标

 游标在仓库储存进程和函数中央银行使。语法仿佛在放置的SQL中。游标是只读的及不滚动的,只好在一个主旋律上进展遍历,不能够在记录之间自由进退,不可能跳过一些记录,所以每一次读完未来就应有移动到下二个笔录。游标必需在宣称管理程序从前被声称,而且变量和条件必得在宣称光标或管理程序从前被声称。

一、游标

  1、定义

   DECLARE 游标名称 CUMuranoSO凯雷德 FO瑞虎 查询语句

   这一个讲话声澳优(Ausnutria Hyproca)个光标。也足以在子程序中定义多个光标,不过一个块中的每三个光标必得有独一的名字。

   2、OPEN语句

    OPEN 游标名称

    这一个讲话张开先前评释的游标。

  3、FETCH语句

    FETCH 游标名称 INTO 变量[,  变量2] ...

    这些语句用钦赐的展开游标读取下一行(固然有下一行的话),何况发展游标指针。

   4、CLOSE语句

    CLOSE 游标名称

    这些语句关闭先前开荒的游标。假使未被刚烈地关闭,游标在它被声称的复合语句的末尾被关闭。

二、实例

  表结构如下:

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', '张三', '21', null);
INSERT INTO `person` VALUES ('2', '李四', '23', null);
INSERT INTO `person` VALUES ('3', '王五', '22', null);
INSERT INTO `person` VALUES ('4', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('8', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('9', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('10', 'wangwu', '23', 'password123');

  1、游标使用REPEAT 

DROP PROCEDURE IF EXISTS proc_test_cursor;
-- 所有人的年龄和
CREATE PROCEDURE proc_test_cursor(
    OUT total INT(11)
)
BEGIN
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT age FROM person;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
    SET total = 0;
    OPEN pcursor;
    REPEAT
        FETCH pcursor INTO t; 
        IF NOT done THEN -- 还有记录
            SET total = total   t;
        END IF;
    UNTIL done END REPEAT;
    CLOSE pcursor; 
END;

CALL proc_test_cursor(@total);
SELECT @total; 
SELECT SUM(age) FROM person; 

 

   三遍询问的结果一律,则游标施行常规。

  2、游标使用while

DROP PROCEDURE IF EXISTS proc_test_cursor_while;
-- id小于某个值的年龄和
CREATE PROCEDURE proc_test_cursor_while(
    IN uid INT(11),
    OUT total INT(11)
)
BEGIN
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT age FROM person WHERE id < uid;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
    SET total = 0;
    OPEN pcursor; 
    WHILE(NOT done) DO
        FETCH pcursor INTO t;
        IF(NOT done) THEN
            SET total = total   t;    
        END IF;
  end WHILE;
    CLOSE pcursor; 
END;

CALL proc_test_cursor_while(3,@total);
SELECT @total;
SELECT SUM(age) FROM person where id < 3;

    一回查询的结果同样,则游标实施常规。

  3、游标中选取update语句

DROP PROCEDURE IF EXISTS proc_test_cursor_update;
-- 年龄大于多少的年龄加某个数
CREATE PROCEDURE proc_test_cursor_update( 
    IN avgage INT(11)
)
BEGIN
    DECLARE num INT DEFAULT 0;
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT id, age FROM person;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
    OPEN pcursor; 
    REPEAT
            FETCH pcursor INTO num, t; 
            IF NOT done THEN -- 还有记录    
                IF t > avgage THEN -- 年龄大于传入的年龄值
                    UPDATE person SET age = age   5 where id = num;
                END IF;
            END IF;
    UNTIL done END REPEAT;
    CLOSE pcursor; 
END;

SET @uage = 20;
SELECT id, username, age FROM person where age > @uage;
CALL proc_test_cursor_update(@uage); 
SELECT id, username, age FROM person where age > @uage;

    一次询问的结果同样,则游标实践符合规律。

有俩种方法
一种是宣称系统游标,一种是声称自定义游标,然后后边操作同样,参数类型为 
in out 或out 
(1)注解个人系统游标.(推荐) 

mg4377娱乐娱城官网 1mg4377娱乐娱城官网 2

CREATE PROCEDURE [dbo].[pro_init_dzz_dy_exception] AS
DECLARE @v_uuid VARCHAR (40) ;
DECLARE @v_operatetime datetime ;
DECLARE @v_userid VARCHAR (40) ;
DECLARE @v_zjhm VARCHAR (30) ;
DECLARE @v_csrq Date ;
DECLARE @v_rdsj Date ;
DECLARE @v_zzsj Date ;
DECLARE @v_idcardmult VARCHAR (1);
DECLARE @v_idcardvalidity VARCHAR (1) ;
DECLARE @v_subzjhmstr VARCHAR (50) ;
DECLARE @v_csrqstr VARCHAR (50) ;
DECLARE @dbname VARCHAR (100) ;
DECLARE @insertSqlStrStart VARCHAR (5000) ;
DECLARE @insertSqlStrMiddle VARCHAR (5000) ;
DECLARE @insertSqlStrEnd VARCHAR (5000) ;
DECLARE @insertSqlStr VARCHAR (5000) ;
DECLARE @querysql VARCHAR (5000) ;
DECLARE @deletesql VARCHAR (5000) ;
DECLARE @yearInterval INT ;
DECLARE @mm INT ; --DECLARE v_all_dy sys_refcursor;
--DECLARE @v_dy_info TABLE T_DZZ_DY_EXCEPTION_INFO_TEMP;
DECLARE allSche CURSOR FOR SELECT
    schemaname
FROM
    s_qkdzzinfo ;
BEGIN

--清空异常信息表
SET @deletesql = 'delete from t_dzz_dy_exception_info';
BEGIN
    TRAN ; EXEC (@deletesql) ; COMMIT TRAN ;

SET @insertSqlStrStart = 'insert into t_dzz_dy_exception_info 
                         (uuid,dzz_dy_id,datatype,errorlevel,errortype,operatetime) 
                         values(' ;
SET @insertSqlStrEnd = ')' ; OPEN allSche FETCH NEXT
FROM
    allSche INTO @dbname ;
WHILE (@@FETCH_STATUS = 0)
BEGIN

--SET @querysql = 'select * from '   dbname   '.t_dy_info where delflag =1' ;
--FAST_FORWARD 
exec('DECLARE v_all_dy CURSOR FOR select userid, zjhm, csrq, rdsj, zzsj, idcardmult, idcardvalidity from ' @dbname '.t_dy_info where delflag = 1 and dylb in (1,2) and dyzt = 1');
--DECLARE v_all_dy CURSOR FOR select REPLACE(userid, ' ', ''), REPLACE(zjhm, ' ', ''), csrq, rdsj, zzsj, idcardmult, idcardvalidity from @dbname.t_dy_info where delflag = 1;
OPEN v_all_dy ; FETCH NEXT
FROM
    v_all_dy INTO @v_userid, @v_zjhm, @v_csrq, @v_rdsj, @v_zzsj, @v_idcardmult, @v_idcardvalidity;
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @v_operatetime =  CONVERT(datetime, GETDATE(), 20);
SET @v_userid = REPLACE(@v_userid, ' ', '');
SET @v_zjhm = REPLACE(@v_zjhm, ' ', '')
SET @v_csrq = CONVERT (DATE,@v_csrq,23);
SET @v_rdsj = CONVERT (DATE,@v_rdsj,23);
SET @v_zzsj = CONVERT (DATE,@v_zzsj,23);
--SET @v_rdsj = @v_dy_info.rdsj ;
--SET @v_zzsj = @v_dy_info.zzsj ;
--SET @v_idcardmult = @v_dy_info.idcardmult ;
--SET @v_idcardvalidity = @v_dy_info.idcardvalidity ;
IF @v_zjhm IS NOT NULL
AND @v_csrq IS NOT NULL
BEGIN
SET @v_subzjhmstr = SUBSTRING (@v_zjhm, 7, 4)   SUBSTRING (@v_zjhm, 11, 2)   SUBSTRING (@v_zjhm, 13, 2) ;
SET @v_csrqstr = CONVERT (
    VARCHAR (100),
    CONVERT (
        DATE,
        CONVERT (VARCHAR(100), @v_csrq, 23),
        20
    ),
    112
)
IF @v_subzjhmstr != @v_csrqstr
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,1,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
IF @v_rdsj IS NOT NULL
AND @v_zzsj IS NOT NULL
BEGIN
SET @yearInterval = dbo.FUNC_getYearsToDates (@v_rdsj ,@v_zzsj) ;
SET @mm = dbo.FUNC_getMonthsToDates (@v_rdsj ,@v_zzsj) ;
IF (
    DateDiff(DAY, '1921-07-01' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1923-06-09' ,@v_rdsj) <= 0
)
OR (
    DateDiff(DAY, '1928-06-18' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1945-04-22' ,@v_rdsj) <= 0
)
OR (
    DateDiff(DAY, '1969-04-01' ,@v_rdsj) >= 0
    AND DateDiff(DAY, '1977-08-11' ,@v_rdsj) <= 0
)
BEGIN
IF DateDiff(DAY ,@v_zzsj ,@v_rdsj) != 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
ELSE

IF DateDiff(DAY, '1923-06-10' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1927-04-26' ,@v_rdsj) <= 0
BEGIN
IF @yearInterval > 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
ELSE
BEGIN
IF @mm != 6
AND @mm != 3
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
ELSE

IF DateDiff(DAY, '1927-04-27' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1928-06-17' ,@v_rdsj) <= 0
BEGIN
IF DateDiff(DAY ,@v_zzsj ,@v_rdsj) != 0
BEGIN
IF @mm != 3
AND @yearInterval != 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
ELSE

IF DateDiff(DAY, '1945-04-23' ,@v_rdsj) >= 0
AND DateDiff(DAY, '1956-09-14' ,@v_rdsj) <= 0
BEGIN
IF @mm != 6
AND @yearInterval != 0
AND @mm != - 1
AND @mm != 12 
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
ELSE
IF @yearInterval != 1
AND @yearInterval != 2
AND (@mm != 6 AND @yearInterval != 0)
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
ELSE
BEGIN
IF @yearInterval != 1
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,2,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END
END
IF @v_idcardvalidity IS NOT NULL
BEGIN
IF @v_idcardvalidity = 1
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,3,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END

IF @v_idcardmult IS NOT NULL
BEGIN
IF @v_idcardmult = 1 
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,4,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
END

IF @v_rdsj IS NOT NULL
AND @v_csrq IS NOT NULL
BEGIN
SET @yearInterval = dbo.FUNC_getYearsDifference (@v_csrq ,@v_rdsj) ;
IF @yearInterval < 19
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,1,5,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END
IF DateDiff(DAY ,@v_csrq ,@v_rdsj) < 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,2,6,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ;
END

END
IF @v_rdsj IS NOT NULL 
BEGIN
IF DateDiff(DAY, '1921-07-01' ,@v_rdsj) < 0
BEGIN
SET @v_uuid = REPLACE(NEWID(), '-', '') ;
SET @insertSqlStrMiddle = ',2,2,7,' ;
SET @insertSqlStr = @insertSqlStrStart   ''''  @v_uuid   ''''   ','   ''''  @v_userid   ''''  @insertSqlStrMiddle  '''' cast(@v_operatetime as varchar) '''' @insertSqlStrEnd ;
BEGIN
    TRAN ; EXEC (@insertSqlStr) ; COMMIT TRAN ; 
END
END
FETCH NEXT
FROM
    v_all_dy INTO @v_userid, @v_zjhm, @v_csrq, @v_rdsj, @v_zzsj, @v_idcardmult, @v_idcardvalidity;
END ; CLOSE v_all_dy ; DEALLOCATE v_all_dy ; FETCH NEXT
FROM
    allSche INTO @dbname ;
END ; CLOSE allSche ; DEALLOCATE allSche ;
END ;

仓库储存进程中游标是怎使用的

ALTER proc [dbo].[积攒进度名]
as
begin
declare 游标名字 cursor for select 列名 from 表名 where 条件--先声明游标指向查询出的结果,一列,只怕多列都得以,条件自定
declare 变量名 varchar(400)--存款和储蓄取到的值
open 游标名 --开启游标
while @@FETCH_STATUS=0--取值
begin
fetch next FROM 游标名 into

代码如下:

1 1 create table tb1(
2 2 
3 3 id int ,
4 4 
5 5 name nvarchar(20)
6 6 
7 7 )

2.概念函数

变量名--那样就将游标指向下一行,获得的率先行值就传给变量了

--供给实践的操作,比方修改某表中的字段
update 表名
set 列名=值

create or replace p_temp_procedure 
( 
cur_arg out sys_refcursor; --方法1 
) 
begin 
open cur_arg for select * from tablename; 
end 

View Code

一.
CREATE FUNCTION [dbo].[FUNC_getMonthsToDates] (@v_rdsj DATE, @v_zzsj DATE) RETURNS INT AS
BEGIN

DECLARE @beginYear INT ;
DECLARE @endYear INT ;
DECLARE @beginMonth INT ;
DECLARE @endMonth INT ;
DECLARE @beginDay INT ;
DECLARE @endDay INT ;
DECLARE @beginBool INT ;
DECLARE @endBool INT ;
DECLARE @yearInterval INT ;
DECLARE @monthInterval INT ;
DECLARE @mm INT ;
SET @beginYear = DatePart(YEAR ,@v_rdsj) ;
SET @endYear = DatePart(YEAR ,@v_zzsj) ;
SET @beginMonth = DatePart(MONTH ,@v_rdsj) ;
SET @endMonth = DatePart(MONTH ,@v_zzsj) ;
SET @beginDay = DatePart(DAY ,@v_rdsj) ;
SET @endDay = DatePart(DAY ,@v_zzsj) ;
SET @yearInterval = @endYear - @beginYear ; --为1说明是当月最后一天喂0说明不是
SET @beginBool = datediff(
    MONTH ,@v_rdsj,
    dateadd(DAY, 1 ,@v_rdsj)
) ;
SET @endBool = datediff(
    MONTH ,@v_zzsj,
    dateadd(DAY, 1 ,@v_zzsj)
) ;
IF DateDiff(DAY ,@v_rdsj ,@v_zzsj) >= 0
BEGIN

IF (
    @yearInterval = 0
    OR @yearInterval = 1
)
BEGIN

IF (
    @endMonth < @beginMonth
    OR @endMonth = @beginMonth
    AND (
        (@endDay < @beginDay)
        AND (@beginBool = 0 AND @endBool = 0)
    )
)
BEGIN

SET @yearInterval = @yearInterval - 1;
END
SET @monthInterval = (@endMonth   12) - @beginMonth ;
IF (
    @endDay < @beginDay
    AND (@beginBool = 0 AND @endBool = 0)
)
BEGIN

SET @monthInterval = @monthInterval - 1 ;
END
SET @monthInterval = @monthInterval % 12 ;
SET @mm = @yearInterval * 12   @monthInterval ;
IF (@beginBool = 1 AND @endBool = 1)
BEGIN

SET @mm = @mm ;
END
ELSE

IF (
    @beginBool = 0
    AND @endBool = 1
    AND @endDay <= @beginDay
)
BEGIN

SET @mm = @mm ;
END
ELSE

IF (
    @beginBool = 0
    AND @endBool = 0
    AND @endDay = @beginDay
)
BEGIN

SET @mm = @mm ;
END
ELSE

BEGIN

SET @mm = -1 ;
END
END
ELSE

BEGIN

SET @mm = -1 ;
END
END
ELSE

BEGIN

SET @mm = -1 ;
END RETURN @mm ;
END

 

where (修改表中的列)=变量名

end
close 游标名--关闭游标

deallocate 游标名--释放游标
end  

调用 


二.

积存进度 临时表 游标

准备t4表和test_t4表
t4有数据 test_t4结谈判t4同样 只是未有数量

[[email protected]#13-7月 -10] SQL>select * from t4;

ID SEX


1 男
2 女
3 太监

[[email protected]#13-7月 -10] SQL>desc test_t4;
名称 是还是不是为空? 类型



ID NUMBER
SEX VARCHAR2(20)

因而测量检验的:

create or replace procedure p_test
is
v_sql varchar2(30000);
v_tmptable varchar2(30);
v_row t4%rowtype;
cursor c is (select * from t4);
begin
v_tmptable:='t3_tmp_t4';
v_sql:='create global temporary table '||v_tmptable||' (id number(4),sex varchar2(20))';
execute immediate v_sql;

open c;
loop
exit when c%notfound;
fetch c into v_row;
v_sql:='insert into '||v_tmptable||' values('||v_row.id||','''||v_row.sex||''')';
execute immediate v_sql;
end loop;
close c;

-- 验证有的时候表中是或不是有多少(该有时表是事务级有时表,一旦付出业务数据就被清空,所以将临时表数据插入到实表中)

execute immediate 'insert into test_t4 select * from '||v_tmptable;
commit;
end p_test;

翻看结果:
[[email protected]#13-7月 -10] SQL>select * from test_t4;

ID SEX


1 男
2 女
3 太监
3 太监

那注解有时表中的数据插入到test_t4表中了,循环有一点点小标题第......余下全文>>  

游标在蕴藏进度和函数中运用。语法就好像在停放的SQL中。游标是只读的及不滚动的,只可以在二个趋势上...

代码如下:

编辑:mg4377娱乐娱城官网 本文来源:Oracle存款和储蓄进度重临游标实例详解,sqlserv

关键词: 数据库 DB DB Oracle