Oracle-cursor动态游标

Oracle-cursor动态游标


type 动态游标类型名 is ref cursor;  ---- 声明一个动态游标类型,紫色填写一样内容

游标名 动态游标类型; ---- 声明一个动态游标类型的变量,这个变量就是动态游标类型的,也就是动态游标

open 游标名 for SQL语句; ---- 打开游标,并且把SQL语句和游标关联起来

close 游标名;
--创建过程

create or replace procedure proc_select

is

sql_select varchar(400);

sql_row tablesp%rowtype;

type cur_select is ref cursor; --声明一个动态游标类型,名字叫cur_select,因为游标不是类型所以要声明一个动态游标类型

curs cur_select;  --声明一个动态游标变量,名字叫curs

begin

  sql_select:='select * from tablesp'; -- 需要执行的SQL语句

  open curs for sql_select;    -- 打开游标,并且SQL执行结果存放到curs中

  loop

    fetch curs into sql_row;  -- 把curs中的一条记录赋值为 sql_row

    dbms_output.put_line(sql_row.tid||'-'||sql_row.tname||'-'||sql_row.tage);

    exit when curs%notfound;  -- 退出循环

  end loop;

  close curs;

end;
PROCEDURE get_itemcodebyitemdesc(p_org_id NUMBER,
p_mitemdesc VARCHAR2,
p_mitemname VARCHAR2,
x_ret_code OUT VARCHAR2,
x_ret_msg OUT VARCHAR2,
x_ret_data OUT SYS_REFCURSOR) IS

v_program VARCHAR2(100);
v_timedone VARCHAR2(100);
v_mcid NUMBER;
v_compname VARCHAR(100);
v_count NUMBER;
c_row PcbTrace%rowtype;
TYPE ref_cursor_type IS REF CURSOR;
curs ref_cursor_type;
ls_condition varchar2(4000);
ls_sql varchar2(8000);
ls_temp varchar(4000);
/*type c_row IS record (
program VARCHAR2(240),
TimeDone VARCHAR2(240),
mcid VARCHAR2(240))\*%ROWTYPE INDEX BY BINARY_INTEGER*\;*/
BEGIN
x_ret_msg := '';
x_ret_code := '0';
/*
DECLARE
--类型定义
CURSOR c_job IS
SELECT b.program,
to_char(b.TimeDone, 'YYYY-MM-DD HH24:MI:SS:FF3') TimeDone,
b.mcid
FROM PanelBlockTrace a
INNER JOIN PcbTrace b
ON a.pcbid = b.pcbid
WHERE a.BlockID = p_mitemname;

c_row c_job%ROWTYPE;*/
BEGIN
ls_temp := '
SELECT b.program,
b.TimeDone,
b.mcid
FROM PanelBlockTrace a
INNER JOIN PcbTrace b
ON a.pcbid = b.pcbid
WHERE a.BlockID = p_mitemname ';
ls_temp := ls_temp || ls_condition;
open curs for ls_temp;
loop
fetch curs
into c_row; -- 把curs中的一条记录赋值为 c_row
SELECT COUNT(CompName)
INTO v_count
FROM ReferenceTrace
WHERE ProgramName = c_row.program
AND McID = c_row.mcid
AND StartTime = (SELECT MAX(StartTime)
FROM ReferenceTrace
WHERE ProgramName = c_row.program
AND McID = c_row.mcid
AND StartTime <=
TO_TIMESTAMP(to_char(c_row.TimeDone, 'YYYY-MM-DD HH24:MI:SS:FF3'),
'YYYY-MM-DD HH24:MI:SS:FF3')
AND REFERENCE = p_mitemdesc)
AND reference = p_mitemdesc;
IF v_count > 0 THEN
SELECT CompName
INTO v_compname
FROM ReferenceTrace
WHERE ProgramName = c_row.program
AND McID = c_row.mcid
AND StartTime = (SELECT MAX(StartTime)
FROM ReferenceTrace
WHERE ProgramName = c_row.program
AND McID = c_row.mcid
AND StartTime <=
TO_TIMESTAMP(to_char(c_row.TimeDone, 'YYYY-MM-DD HH24:MI:SS:FF3'),
'YYYY-MM-DD HH24:MI:SS:FF3')
AND REFERENCE = p_mitemdesc)
AND reference = p_mitemdesc;
EXIT;
END IF;

EXIT WHEN v_compname IS NOT NULL; -- 退出循环
exit when curs%notfound; -- 退出循环

end loop;

close curs;
END;
OPEN x_ret_data FOR
SELECT DISTINCT 'SUP' "DESCCODE", b.mitemcode, b.mitemname, b.mitemdesc
FROM tblmitem_b b
WHERE b.mitemname = v_compname
AND b.orgid = p_org_id;
EXCEPTION
WHEN OTHERS THEN
x_ret_code := SQLCODE;
x_ret_msg := SQLERRM || ': ' ||
substr(dbms_utility.format_error_backtrace, 1, 200);
x_ret_data := NULL;
END;
 

博文最后更新时间:


评论

  • gfhrto

    OQ3LLz <a href="http://lqavflrjwtpx.com/">lqavflrjwtpx</a>, [url=http://qcruxbbxsdqd.com/]qcruxbbxsdqd[/url], [link=http://yfugyjdjsmzd.com/]yfugyjdjsmzd[/link], http://jwvkywsehscl.com/

  • johnansog

    ZPErHO http://pills2sale.com/vjUe79ndRq341pIo

  • dobsonz

    IFAyPY http://pills2sale.com/vjUe79ndRq341pIo

  • GdqUQ

    Meds information sheet. Long-Term Effects. <a href="https://pregabalin24x7.top">can i purchase generic pregabalin pills</a> in the USA Actual what you want to know about medicament. Read now.

  • JaniceWaics

    buy cialis <a href="https://mycialistabs.com/">mycialistabs</a> how much cialis to take

  • Tiffanyhot

    cialis pill <a href="https://toptadalafiltabs.com/">buy cialis online usa</a> generic cialis available in canada

发表评论

博客统计

访问量:5097396

博文总数:750 评论总数:909180

原创126 翻译20 转载604