oracle数据库PL/SQL学习的例子

最新推荐文章于 2023-06-12 08:59:14 发布

珣珣

最新推荐文章于 2023-06-12 08:59:14 发布

阅读量130

点赞数

分类专栏： ORACLE随笔文章标签：数据库

本文链接：https://blog.csdn.net/xunxun118/article/details/84010120

版权

ORACLE随笔专栏收录该内容

15 篇文章 2 订阅

订阅专栏

oracle记录的学习.
声明一个记录
begin
declare
type myrecord is record(
id number,
user_type varchar(20)
);

realrecord myrecord;
begin
select id,user_type into realrecord from base_user where user_name='zhangjiaxun';

DBMS_OUTPUT.PUT_LINE(realrecord.id||','||realrecord.user_type);
end;

end;

声明一个表的记录类型.

begin
declare
realrecord base_user%rowtype;

begin
select * into realrecord from base_user where user_name='zhangjiaxun';

DBMS_OUTPUT.PUT_LINE(realrecord.id||','||realrecord.user_type);
end;

end;

以一个表的字段声明一个记录类型.
begin
declare
type myrecord is record
(
id base_user.id%type,
user_type base_user.user_type%type
);

realrecord myrecord;
begin
select id,user_type into realrecord from base_user where user_name='zhangjiaxun';

DBMS_OUTPUT.PUT_LINE(to_char(realrecord.id)||','||realrecord.user_type);
end;

end;

游标的学习

begin
declare
srecord S%ROWTYPE;
currentrow number;
cursor scursor is select s1,s2 from S ;
begin
open scursor;
fetch scursor into srecord;

while scursor%FOUND loop
     currentrow := scursor%rowcount;
     if(currentrow mod 2=0) then
        begin
        insert into a(a1,a2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('偶数');
     else
        begin
        insert into b(b1,b2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('奇数');
     end if;
     fetch scursor into srecord;
end loop;
close scursor;
exception
when others then
begin
    if scursor%isopen then
     close scursor;
end if;
     raise;
end;
end;

end;

for循环游标.

begin
declare
srecord S%ROWTYPE;
currentrow number;
cursor scursor is select s1,s2 from S ;
begin

    for srecord in scursor loop
     currentrow := scursor%rowcount;
     if(currentrow mod 2=0) then
        begin
        insert into a(a1,a2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('偶数');
     else
        begin
        insert into b(b1,b2)values(srecord.s1,srecord.s2);
        commit;
        exception
        when others then
        rollback;
        end;
        DBMS_OUTPUT.PUT_LINE('奇数');
     end if;
     end loop;
exception
when others then
begin
    if scursor%isopen then
     close scursor;
end if;
     raise;
end;
end;

end;