create table stuinfo1
(
stuid int primary key,
stuname varchar(10) not null,
stusex char(2) check(stusex='male' or stusex='female'),
stubir date
);
insert into stuinfo values ('001','Zhang San','male',to_date ('1999-1-1','yyyy-mm-dd'));
insert into stuinfo values ('002','Reese','male',to_date ('1997-12-1','yyyy-mm-dd'));
insert into stuinfo values ('003', 'Wang Wu', 'female', to_date ('1995-3-4', 'yyyy-mm-dd'));
insert into stuinfo values ('004','haha','male',to_date ('2000-7-5','yyyy-mm-dd'));
insert into stuinfo values ('005', 'haha', 'male', to_date ('1993-7-5', 'yyyy-mm-dd'));
--System predefined exceptions
declare
v_name stuinfo.stuname%type;
begin
select stuname into v_name from stuinfo;
--exception
-- when too_many_rows then
-- dbms_output.put_line('Query results are redundant, cannot be assigned');
end;
--user-defined exceptions
declare
money_error exception;
money1 int := 1000;
money2 int :=& transfer amount;
begin
if money1-money2 < 0 then
raise_application_error (-20001, 'Insufficient balance');
--raise money_error;
else
dbms_output.put_line('Transfer successful');
end if;
--exception
-- when money_error then
-- dbms_output.put_line('Insufficient balance');
end;
--non-predefined exceptions
declare
myerror exception;
pragma exception_init(myerror,-1);
begin
insert into stuinfo values ('001','aa','female',to_date('1995-3-4','yyyy-mm-dd'));
exception
when myerror then
dbms_output.put_line('Violation of primary key uniqueness constraint');
end;
insert into stuinfo1 values (1,'sdf','male','');
select * from tb1;
drop table tb1;
--dynamic sql
begin
execute immediate 'create table tb1(tid int)';
end;
declare
sqlString varchar2(200);
tId int:=2;
begin
sqlString:='insert into tb1 values (:1)';
execute immediate sqlString using tId;
end;
select * from stuinfo;
--Implicit cursor
begin
DBMS_OUTPUT. PUT_LINE('Before inserting:'|| SQL%ROWCOUNT);
update stuinfo set stusex = 'male';
DBMS_OUTPUT. PUT_LINE('After inserting:'|| SQL%ROWCOUNT);
END;
|
Posted on 3/19/2015 9:28:41 AM
|
|
|
