±±´óÇàÄñoracleѧϰ±Ê¼Ç25

¹ý³ÌÖеÄÊÂÎñ
¶¨Òå¹ý³Ìp1
create or replace procedure p1
as
begin
insert into student values(5,'xdh','m',sysdate);
rollback;
end;
¶¨Òå¹ý³Ìp2
create or replace procedure p2
as
begin
update student set stu_sex = 'a' where stu_id = 3;
p1;
end;
Ö´Ðйý³Ìp2

exec p2;
Ö´ÐÐÍê±Ï·¢ÏÖ±íÖÐÊý¾ÝûÓбä¸ü£¬ËµÃ÷p1ÖеÄrollbackÓï¾ä½«p2ÖеÄupdateÓï¾äÒ²»Ø¹öÁË¡£
×ÔÖ÷ÊÂÎñ´¦Àí
²½Ö裺

Ö÷ÊÂÎñ´¦ÀíÆô¶¯×ÔÖ÷ÊÂÎñ´¦Àí

Ö÷ÊÂÎñ´¦Àí±»ÔÝÍ£

×ÔÖ÷ÊÂÎñ´¦Àísql²Ù×÷

ÖÐÖ¹×ÔÖ÷ÊÂÎñ´¦Àí

»Ö¸´Ö÷ÊÂÎñ´¦Àí


pragma autonomous_transaction

ÓÃÓÚ±ê¼Ç×Ó³ÌÐò
ÔÚp1µÄas beginµ±ÖмÓÈë pragma autonomous_transaction  ºóÖ´ÐУ¬·¢ÐÐp2µÄupdateÓï¾äÉúЧ£¬p1ÖеÄÊÂÎñ×÷Ϊ×ÔÖ÷ÊÂÎñÀ´´¦Àí£¬²»Ó°ÏìÖ÷ÊÂÎñ¡£
³ÌÐò°ü
Ïà¹Ø¶ÔÏóµÄ·â×°

-³ÌÐò°ü¹æ¸ñ˵Ã÷

    ÉùÃ÷×Ó³ÌÐò£¬²»°üº¬ÊµÏÖ

create package °üÃû is|as ±äÁ¿ÉùÃ÷|ÀàÐͶ¨Òå|Òì³£ÉùÃ÷|ÓαêÉùÃ÷|º¯Êý˵Ã÷|¹ý³Ì˵Ã÷

pragma restrict_references(º¯ÊýÃû,WNDS[,WNPS][,RNDS][,RNPS])

end [°üÃû];

create or replace package StuPackages

is

  type curRefStudent is REF CURSOR RETURN student%rowtype;

  procedure insertStudent(stuid in student.stu_id%type,stuname in student.stu_name%type,stusex in student.stu_sex%type,studate in student.stu_birthday%type);
Function QueryStudent(stuid in student.stu_id%type) return student%rowtype;
end StuPackages;

-³ÌÐò°üÖ÷Ìå

    ¶¨Òå×Ó³ÌÐò£¬ÊµÏÖÉùÃ÷²¿·Ö

create package body °üÃû is|as ±äÁ¿ÉùÃ÷|ÀàÐͶ¨Òå|Òì³£ÉùÃ÷|ÓαêÉùÃ÷|º¯Êý¶¨Òå|¹ý³Ì¶¨Òå

end [°üÃû];
CREATE OR REPLACE
PACKAGE BODY STUPACKAGES AS
procedure insertStudent(stuid in student.stu_id%type,stuname in student.stu_name%type,stusex in student.stu_sex%type,studate in student.stu_birthday%type) AS
i INTEGER;
Student_Exist EXCEPTION;
BEGIN
select count(*) into i from student where stu_id = stuid;
if i>0 then
raise Student_Exist;
else
insert into student values(stuid,stuname,stusex,studate);
commit;
end if;