Oracle存储过程返回游标实例详解

创建表:

1.仓储进程

储存进度之四—游标,存款和储蓄进度游标

 游标在蕴藏进程和函数中使用。语法就如在停放的SQL中。游标是只读的及不滚动的,只好在三个样子上展开遍历,无法在记录之间自由进退,不可能跳过好几记录,所以每一趟读完今后就应该移动到下一个笔录。游标必需在表明管理程序此前被声称,况兼变量和标准化必须在宣称光标或管理程序此前被声称。

一、游标

  1、定义

   DECLARE 游标名称 CU帕杰罗SO奥迪Q5 FOEscort 查询语句

   这几个讲话声美赞臣(Beingmate)个光标。也足以在子程序中定义四个光标,可是叁个块中的每三个光标必得有独一的名字。

   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;

    五次询问的结果一致,则游标试行例行。

复制代码 代码如下:
CREATE OR REPLACE PROCEDURE PROCSENDEMAIL(P_TXT VARCHAR2,
P_SUB VARCHAR2,
P_SENDOR VARCHAR2,
P_RECEIVER VARCHAR2,
P_SERVER VARCHAR2,
P_PORT NUMBER DEFAULT 25,
P_NEED_SMTP INT DEFAULT 0,
P_USER VARCHAR2 DEFAULT NULL,
P_PASS VARCHAR2 DEFAULT NULL,
P_FILENAME VARCHAR2 DEFAULT NULL,
P_ENCODE VARCHAR2 DEFAULT ‘bit 7’)
AUTHID CURRENT_USER IS

有俩种艺术
一种是声称系统游标,一种是宣称自定义游标,然后前面操作一样,参数类型为 
in out 或out 
(1)证明个人系统游标.(推荐) 

威尼斯人网上娱乐 1威尼斯人网上娱乐 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

复制代码 代码如下:
/*
效益:用oracle发送邮件
第一职能:1、匡助多收件人。
2、帮忙汉语
3、帮衬抄赠给别人
4、支持胜出32K的附属类小部件
5、协助多行正文
6、辅助多附件
7、扶助文件附件和二进制附属类小部件
8、支持HTML格式
8、支持
作者:suk
参数表达:
p_txt :邮件正文
p_sub: 邮件标题
p_SendorAddress : 发送给旁人邮件地址
p_ReceiverAddress :
接收地址,能够相同的时候发送到八个地点上,地址之间用”,”大概”;”隔离
p_EmailServer : 邮件服务器地址,能够是域名依然IP
p_Port :邮件服务器端口
p_need_smtp:是不是需求smtp认证,0象征无需,1象征必要
p_user:smtp验证必要的客户名
p_pass:smtp验证需求的密码
p_filename:附属类小部件名称,必需含有完整的门道,如”d:tempa.txt”。
能够有两个附属类小部件,附属类小部件名称只看见用逗号大概分部分隔
p_encode:附属类小部件编码调换格式,当中 p_encode=’bit 7′ 表示文本类型附属类小部件
p_encode=’base64′ 表示二进制类型附属类小部件
注意:
1、对于文本类型的附属类小部件,不能够用base64的办法发送,不然出错
2、对于多少个附属类小部件只可以用一样种格式发送

代码如下:

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 列名=值

*/
L_CRLF VARCHAR2(2) := UTL_TCP.CRLF;
L_SENDORADDRESS VARCHAR2(4000);
L_SPLITE VARCHAR2(10) := ‘++’;
BOUNDARY CONSTANT VARCHAR2(256) := ‘—–BYSUK’;
FIRST_BOUNDARY CONSTANT VARCHAR2(256) := ‘–‘ || BOUNDARY || L_CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := ‘–‘ || BOUNDARY || ‘–‘ ||
L_CRLF;
MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := ‘multipart/mixed;
boundary=”‘ ||
BOUNDARY || ‘”‘;
/* 以下一些是出殡和埋葬大二进制附件时用到的变量 */
L_FIL BFILE;
L_FILE_LEN NUMBER;
L_MODULO NUMBER;
L_PIECES NUMBER;
L_FILE_HANDLE UTL_FILE.FILE_TYPE;
L_AMT BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016
*/
L_FILEPOS PLS_INTEGER := 1; /* pointer for the file */
L_CHUNKS NUMBER;
L_BUF RAW(2100);
L_DATA RAW(2100);
L_MAX_LINE_WIDTH NUMBER := 54;
L_DIRECTORY_BASE_NAME VARCHAR2(100) := ‘DIR_FOR_SEND_MAIL’;
L_LINE VARCHAR2(1000);
L_MESG VARCHAR2(32767);
/* 以上部分是发送大二进制附属类小部件时用到的变量 */
TYPE ADDRESS_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
MY_ADDRESS_LIST ADDRESS_LIST;
TYPE ACCT_LIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
MY_ACCT_LIST ACCT_LIST;

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
 

————————————-再次回到附属类小部件源文件所在目录可能名称————————————–
复制代码 代码如下:
FUNCTION GET_FILE(P_FILE VARCHAR2, P_GET INT) RETURN VARCHAR2 IS
–p_get=1 表示回去目录
–p_get=2 代表回去文件名
L_FILE VARCHAR2(1000);
BEGIN
IF INSTR(P_FILE, ”) > 0 THEN
–windows
IF P_GET = 1 THEN
L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, ”, -1) – 1);
ELSIF P_GET = 2 THEN
L_FILE := SUBSTR(P_FILE, – (LENGTH(P_FILE) – INSTR(P_FILE, ”,
-1)));
END IF;
ELSIF INSTR(P_FILE, ‘/’) > 0 THEN
–linux/unix
IF P_GET = 1 THEN
L_FILE := SUBSTR(P_FILE, 1, INSTR(P_FILE, ‘/’, -1) – 1);
ELSIF P_威尼斯人网上娱乐,GET = 2 THEN
L_FILE := SUBSTR(P_FILE,

调用 


二.

仓储进度 偶尔表 游标

准备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中。游标是只读的及不滚动的,只可以在贰个主旋律上…

  • (LENGTH(P_FILE) – INSTR(P_FILE, ‘/’, -1)));
    END IF;
    END IF;
    RETURN L_FILE;
    END;

代码如下:

创办重返游标的囤积进度:

CREATE function [dbo].[FUNC_getYearsDifference](@v_begin DATE,
@v_end DATE)
returns int
as
BEGIN
DECLARE @beginYear INT;
DECLARE @endYear INT;
DECLARE @beginMonth INT;
DECLARE @endMonth INT;
DECLARE @beginDay INT;
DECLARE @endDay INT;
DECLARE @yearInterval INT;
DECLARE @num INT;
SET @num = -1;
SET @beginYear = DatePart (year,@v_begin);
SET @endYear = DatePart (year,@v_end);
SET @beginMonth = DatePart (month,@v_begin);
SET @endMonth = DatePart (month,@v_end);
SET @beginDay = DatePart (day,@v_begin);
SET @endDay = DatePart (day,@v_end);
SET @yearInterval = @endYear – @beginYear;
if DateDiff(day,@v_begin,@v_end) >= 0
begin
if(@endMonth < @beginMonth)
begin
SET @yearInterval = @yearInterval – 1;
end
if(@endMonth = @beginMonth)
begin
if(@endDay < @beginDay)
BEGIN
SET @yearInterval = @yearInterval – 1;
END
else if(@endDay = @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else
begin
SET @yearInterval = @yearInterval;
end
end
else
begin
SET @yearInterval = @yearInterval;
end
end
else
begin
SET @yearInterval = -1;
end
return @yearInterval;
END

———————————————删除directory————————————
复制代码 代码如下:
PROCEDURE DROP_DIRECTORY(P_DIRECTORY_NAME VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ‘drop directory ‘ || P_DIRECTORY_NAME;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

declare 
cur_calling sys_refcursor; 
begin 
p_temp_procedure(cur_calling); --这样这个游标就有值了 
for rec_next in cur_calling loop 
.... 
end loop; 
end; 

威尼斯人网上娱乐 3威尼斯人网上娱乐 4

三.

————————————————–创建directory—————————————–
复制代码 代码如下:
PROCEDURE CREATE_DIRECTORY(P_DIRECTORY_NAME VARCHAR2, P_DIR
VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ‘create directory ‘ || P_DIRECTORY_NAME || ‘ as ”’
||
P_DIR || ””;
EXECUTE IMMEDIATE ‘grant read,write on directory ‘ || P_DIRECTORY_NAME
||
‘ to public’;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

 

 1 create proc tb1_proc (
 2 
 3 @cur cursor varying output
 4 
 5 )
 6 
 7 as
 8 
 9 begin
10 
11   set @cur=cursor for
12 
13   select * from tb1
14 
15 end
16 
17 open @cur

CREATE function [dbo].[FUNC_getYearsToDates](@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 @num INT;
SET @num = -1;
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(@endMonth < @beginMonth)
begin
SET @yearInterval = @yearInterval – 1;
end
if(@endMonth = @beginMonth)
begin
if(@beginBool = 1 and @endBool = 1)
BEGIN
SET @yearInterval = @yearInterval;
END
else if(@beginBool = 0 and @endBool = 1 and @endDay <= @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else if(@beginBool = 0 and @endBool = 0 and @endDay = @beginDay)
BEGIN
SET @yearInterval = @yearInterval;
END
else
begin
SET @yearInterval = -1;
end
end
else
begin
SET @yearInterval = -1;
end
end
else
begin
SET @yearInterval = -1;
end
return @yearInterval;
end

——————————————–分割邮件地址或许附属类小部件地址———————————–
复制代码 代码如下:
PROCEDURE P_SPLITE_STR(P_STR VARCHAR2, P_SPLITE_FLAG INT DEFAULT 1)
IS
L_ADDR VARCHAR2(254) := ”;
L_LEN INT;
L_STR VARCHAR2(4000);
J INT := 0; –表示邮件地址或许附属类小部件的个数
BEGIN
/*拍卖接收邮件地址列表,包罗去空格、将;转变为,等*/
L_STR := TRIM(RTRIM(REPLACE(REPLACE(P_STR, ‘;’, ‘,’), ‘ ‘, ”),
‘,’));
L_LEN := LENGTH(L_STR);
FOR I IN 1 .. L_LEN LOOP
IF SUBSTR(L_STR, I, 1) <> ‘,’ THEN
L_ADDR := L_ADDR || SUBSTR(L_STR, I, 1);
ELSE
J := J + 1;
IF P_SPLITE_FLAG = 1 THEN
–代表管理邮件地址
–前后供给丰裕'<>’,否则广大邮箱将不可能发送邮件
L_ADDR := ‘<‘ || L_ADDR || ‘>’;
–调用邮件发送进程
MY_ADDRESS_LIST(J) := L_ADDR;
ELSIF P_SPLITE_FLAG = 2 THEN
–代表管理附属类小部件名称
MY_ACCT_LIST(J) := L_ADDR;
END IF;
L_ADDR := ”;
END IF;
IF I = L_LEN THEN
J := J + 1;
IF P_SPLITE_FLAG = 1 THEN
–调用邮件发送进程
L_ADDR := ‘<‘ || L_ADDR || ‘>’;
MY_ADDRESS_LIST(J) := L_ADDR;
ELSIF P_SPLITE_FLAG = 2 THEN
MY_ACCT_LIST(J) := L_ADDR;
END IF;
END IF;
END LOOP;
END;

(2)在大庆中说美素佳儿个游表类型,然后调用者申多美滋个这些项目标游标变量,传给再次来到游标的存款和储蓄进度,存款和储蓄进度out那个结果集,这种艺术很麻烦.游标类型无法像索引表一样选拔create
or replace
type方法来创制,所以不得不在包中证明,並且供给使用/来实行,后边的储存进程才认这几个游标类型.(不推荐,可是建议要明了并且要会这种艺术,终究它有它存在的道理) 

View Code

 

————————————————写邮件头和邮件内容——————————————
复制代码 代码如下:
PROCEDURE WRITE_DATA(P_CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
P_NAME IN VARCHAR2,
P_VALUE IN VARCHAR2,
P_SPLITE VARCHAR2 DEFAULT ‘:’,
P_CRLF VARCHAR2 DEFAULT L_CRLF) IS
BEGIN
/* utl_raw.cast_to_raw 对消除普通话乱码难点相当重视*/
UTL_SMTP.WRITE_RAW_DATA(P_CONN,
UTL_RAW.CAST_TO_RAW(CONVERT(P_NAME || P_SPLITE ||
P_VALUE || P_CRLF,
‘ZHS16GBK’)));
END;

代码如下:


—————————————-写MIME邮件尾巴部分—————————————————–
复制代码 代码如下:
PROCEDURE END_BOUNDARY(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
LAST IN BOOLEAN DEFAULT FALSE) IS
BEGIN
UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);
IF (LAST) THEN
UTL_SMTP.WRITE_DATA(CONN, LAST_BOUNDARY);
END IF;
END;

--定义全局变量 
create or replace package pkg_package 
as 
type type_cursor is ref cursor; 
type type_record is record 
( 
test01 varchar2(32), 
test02 varchar2(32), 
test03 varchar2(32) 
); 
end; 

 使用存款和储蓄进程重回的游标:

———————————————-发送附属类小部件—————————————————-
复制代码 代码如下:
PROCEDURE ATTACHMENT(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,
MIME_TYPE IN VARCHAR2 DEFAULT ‘text/plain’,
INLINE IN BOOLEAN DEFAULT TRUE,
FILENAME IN VARCHAR2 DEFAULT ‘t.txt’,
TRANSFER_ENC IN VARCHAR2 DEFAULT ‘7 bit’,
DT_NAME IN VARCHAR2 DEFAULT ‘0’) IS
L_FILENAME VARCHAR2(1000);
BEGIN
–写附属类小部件头
UTL_SMTP.WRITE_DATA(CONN, FIRST_BOUNDARY);
–设置附属类小部件格式
WRITE_DATA(CONN, ‘Content-Type’, MIME_TYPE);
–假如文件名称非空,表示有附属类小部件
DROP_DIRECTORY(DT_NAME);
–创建directory
CREATE_DIRECTORY(DT_NAME, GET_FILE(FILENAME, 1));
–获得附属类小部件文件名称
L_FILENAME := GET_FILE(FILENAME, 2);
IF (INLINE) THEN
WRITE_DATA(CONN,
‘Content-Disposition’,
‘inline; filename=”‘ || L_FILENAME || ‘”‘);
ELSE
WRITE_DATA(CONN,
‘Content-Disposition’,
‘attachment; filename=”‘ || L_FILENAME || ‘”‘);
END IF;
–设置附属类小部件的转移格式
IF (TRANSFER_ENC IS NOT NULL) THEN
WRITE_DATA(CONN, ‘Content-Transfer-Encoding’, TRANSFER_ENC);
END IF;
UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);
–begin 贴附属类小部件内容
IF TRANSFER_ENC = ‘bit 7’ THEN
–假设是文本类型的附属类小部件
BEGIN
L_FILE_HANDLE := UTL_FILE.FOPEN(DT_NAME, L_FILENAME, ‘r’);
–展开文件
–把附件分成多份,那样能够发送超过32K的附属类小部件
LOOP
UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);
L_MESG := L_LINE || L_CRLF;
WRITE_DATA(CONN, ”, L_MESG, ”, ”);
END LOOP;
UTL_FILE.FCLOSE(L_FILE_HANDLE);
END_BOUNDARY(CONN);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(L_FILE_HANDLE);
END_BOUNDARY(CONN);
NULL;
END; –甘休文本类型附属类小部件的管理
ELSIF TRANSFER_ENC = ‘base64’ THEN
–假若是二进制类型的附属类小部件
BEGIN
–把附属类小部件分成多份,那样能够发送当先32K的附件
L_FILEPOS := 1; –复位offset,在出殡和埋葬多少个附属类小部件时,必得重新恢复设置
L_FIL := BFILENAME(DT_NAME, L_FILENAME);
L_FILE_LEN := DBMS_LOB.GETLENGTH(L_FIL);
L_MODULO := MOD(L_FILE_LEN, L_AMT);
L_PIECES := TRUNC(L_FILE_LEN / L_AMT);
IF (L_MODULO <> 0) THEN
L_PIECES := L_PIECES + 1;
END IF;
DBMS_LOB.FILEOPEN(L_FIL, DBMS_LOB.FILE_READONLY);
DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
L_DATA := NULL;
FOR I IN 1 .. L_PIECES LOOP
L_FILEPOS := I * L_AMT + 1;
L_FILE_LEN := L_FILE_LEN – L_AMT;
L_DATA := UTL_RAW.CONCAT(L_DATA, L_BUF);
L_CHUNKS := TRUNC(UTL_RAW.LENGTH(L_DATA) / L_MAX_LINE_WIDTH);
IF (I <> L_PIECES) THEN
L_CHUNKS := L_CHUNKS – 1;
END IF;
UTL_SMTP.WRITE_RAW_DATA(CONN, UTL_ENCODE.BASE64_ENCODE(L_DATA));
L_DATA := NULL;
IF (L_FILE_LEN < L_AMT AND L_FILE_LEN > 0) THEN
L_AMT := L_FILE_LEN;
END IF;
DBMS_LOB.READ(L_FIL, L_AMT, L_FILEPOS, L_BUF);
END LOOP;
DBMS_LOB.FILECLOSE(L_FIL);
END_BOUNDARY(CONN);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FILECLOSE(L_FIL);
END_BOUNDARY(CONN);
RAISE;
END; –停止管理二进制附属类小部件
END IF; –截止管理附属类小部件内容
DROP_DIRECTORY(DT_NAME);
END; –结束进度ATTACHMENT

 

威尼斯人网上娱乐 5威尼斯人网上娱乐 6

———————————————真正发送邮件的进度——————————————–
复制代码 代码如下:
PROCEDURE P_EMAIL(P_SENDORADDRESS2 VARCHAPRADO2, –发送地址
P_RECEIVERADDRESS2 VARCHA昂Cora2) –接受地点
IS
L_CONN UTL_SMTP.CONNECTION; –定义连接
BEGIN
/*伊始化邮件服务器消息,连接邮件服务器*/
L_CONN := UTL_SMTP.OPEN_CONNECTION(P_SERVER, P_PORT);
UTL_SMTP.HELO(L_CONN, P_SERVER);
/* smtp服务器登入校验 */
IF P_NEED_SMTP = 1 THEN
UTL_SMTP.COMMAND(L_CONN, ‘AUTH LOGIN’, ”);
UTL_SMTP.COMMAND(L_CONN,
UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USER))));
UTL_SMTP.COMMAND(L_CONN,
UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASS))));
END IF;
/*设置发送地址和接受地址*/
UTL_SMTP.MAIL(L_CONN, P_SENDORADDRESS2);
UTL_SMTP.RCPT(L_CONN, P_RECEIVERADDRESS2);
/*设置邮件头*/
UTL_SMTP.OPEN_DATA(L_CONN);
WRITE_DATA(L_CONN, ‘Date’, TO_CHAR(SYSDATE, ‘yyyy-mm-dd
hh24:mi:ss’));
/*安装发赠与别人*/
WRITE_DATA(L_CONN, ‘From’, P_SENDOR);
/*安装接收人*/
WRITE_DATA(L_CONN, ‘To’, P_RECEIVER);
/*设置邮件宗旨*/
WRITE_DATA(L_CONN, ‘Subject’, P_SUB);
WRITE_DATA(L_CONN, ‘Content-Type’, MULTIPART_MIME_TYPE);
UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(L_CONN, FIRST_BOUNDARY);
WRITE_DATA(L_CONN, ‘Content-Type’, ‘text/plain;charset=gb2312’);
–单独空一行,不然,正文内容不显得
UTL_SMTP.WRITE_DATA(L_CONN, UTL_TCP.CRLF);
/* 设置邮件正文
把分隔符还原成chr(10)。那根本是为着shell中调用该进程,假使有多行,则先把多行的开始和结果统10%一行,并用
l_splite分隔
然后用
l_crlf替换chr(10)。这一步是必得的,不然将不能够发送邮件正文有多行的邮件
*/
WRITE_DATA(L_CONN,
”,
REPLACE(REPLACE(P_TXT, L_SPLITE, CHR(10)), CHR(10), L_CRLF),
”,
”);
END_BOUNDARY(L_CONN);
–若是文件名称不为空,则发送附属类小部件
IF (P_FILENAME IS NOT NULL) THEN
–根据逗号可能分集团拆分附属类小部件地址
P_SPLITE_STR(P_FILENAME, 2);
–循环境与发展送附属类小部件(在同多少个邮件中)
FOR K IN 1 .. MY_ACCT_LIST.COUNT LOOP
ATTACHMENT(CONN => L_CONN,
FILENAME => MY_ACCT_LIST(K),
TRANSFER_ENC => P_ENCODE,
DT_NAME => L_DIRECTORY_BASE_NAME || TO_CHAR(K));
END LOOP;
END IF;
/*闭馆数据写入*/
UTL_SMTP.CLOSE_DATA(L_CONN);
/*关门连接*/
UTL_SMTP.QUIT(L_CONN);
/*十一分处理*/
EXCEPTION
WHEN OTHERS THEN
NULL;
RAISE;
END;

–成立重返游标的仓库储存进程 

 1 declare @my_cur cursor
 2 
 3 declare @id int, @name nvarchar(20)
 4 
 5 exec tb1_proc @my_cur output
 6 
 7 --open @cursor    -- @cursor already opened
 8 
 9 fetch next from @my_cur into @id, @name
10 
11 while(@@fetch_status=0)
12 
13   begin
14 
15     print '编号:' + convert(nvarchar,@id)
16 
17     print '姓名:' + @name
18 
19     print '......................'
20 
21     fetch next from @my_cur into @id, @name
22 
23   end

—————————————————主过程—————————————————–
复制代码 代码如下:
BEGIN
L_SENDORADDRESS := ‘<‘ || P_SENDOR || ‘>’;
P_SPLITE_STR(P_RECEIVE途乐); –管理邮件地址
FOR K IN 1 .. MY_ADDRESS_LIST.COUNT LOOP
P_EMAIL(L_SENDORADDRESS, MY_ADDRESS_LIST(K));
END LOOP;
/*管理邮件地址,依照逗号分割邮件*/
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

代码如下:

View Code

PROCSENDEMAIL(‘邮件内容’,’邮件宗旨’,’发件人’,’收件人’,’邮件服务器ip地址’,25,1,’客商名’,’密码’,”,’base64′);
专心邮件服务器必供给用ip,用域名不行。

create or replace procedure p_temp_procedure 
( 
cur_out_arg out pkg_package.type_cursor 
) 
is 
begin 
open cur_out_arg for select * from test; 
end; 

 注意:存款和储蓄进程中开创游标后要张开

 

–调用 

代码如下:

declare 
cur_out_arg pkg_package.type_cursor; 
rec_arg pkg_package.type_record; 
begin 
p_temp_procedure(cur_out_arg); 
fetch cur_out_arg into rec_arg; 
dbms_output.put_line(rec_arg.test01); 
dbms_output.put_line(rec_arg.test02); 
dbms_output.put_line(rec_arg.test03); 
end; 

转:

发表评论

电子邮件地址不会被公开。 必填项已用*标注