blogjava's web log

create ?? table ?first
(
??no? varchar2 ( 10 ),
??name? varchar2 ( 10 )
)
alter ? table ??first?modify?(no? number ); -- 修改表
alter ? table ?first? add (id? number ) -- 添加列
create ? table ?first1?
(
??no? number ,
??name? varchar ( 20 )
);
alter ? table ?first? add ? primary ? key (no); -- 添加主鍵
select ? * ? from ?first
select ? * ? from ?first
delete ?first? where ?no = ' 11 '
-- 建序列
create ?sequence?firstsquence

select ?firstsquence.nextvar? from ?dual;
select ?firstsquence.currval? from ?dual;

insert ? into ?first? values ( ' 001 ' , ' wujunjun ' );
insert ? into ?first? values ( ' 002 ' , ' wujun ' );
insert ? into ?first? values ( ' 003 ' , ' lover ' );
insert ? into ?first? values ( ' 004 ' , ' blogjava ' );
select ? * ? from ?first
drop ? table ?first

declare
vname?first.name % type;
begin
? select ?name? into ?vname? from ?first? where ?no < ' 003 ' ;
?dbms_output.put_line( ' 成功 ' );
?exception?
?? when ?too_many_rows? then
???dbms_output.put_line( ' 查詢返回不止一行 ' );
??? when ?others? then
????dbms_output.put_line( ' 發生了其他錯誤 ' );
??
end ;

-- 自己定義異常?
declare
?myexception?exception;
?pragma?exception_init(myexception, - 1 );
? begin
??? insert ? into ?first? values ( ' 001 ' , ' 11 ' );
???exception
??? when ?myexception? then
????Dbms_Output.put_line( ' 錯誤 ' );
??? when ?others? then
???dbms_output.put_line( ' 其他錯誤 ' );
? end ?;
?? -- 拋出???
? declare ?
?myexception?exception;
??xm?first.no % type: = ' 001 ' ;
?? begin
??
?? if ?(xm = ' 002 ' )? then
??raise?myexception;
?? else
???? insert ? into ?first? values ( ' 005 ' , ' 222 ' );
??? end ? if ;
???
???exception?
??? when ?myexception? then
???dbms_output.put_line( ' myexception?error! ' );
?? when ?others? then
??raise_application_error( ' -20001 ' , ' error?message ' );
?? end ;
??
-- %found用法
declare
?xm?first.no % type;
begin ?
? -- delete?from?first?where?name='001';
? select ?name? into ?xm? from ?first? where ?no = ' 002 ' ;
? if ?(sql % found = true)? then
??dbms_output.put_line( ' found ' );
?? else
????? insert ? into ?first? values ( ' 002 ' , 33 );
?????dbms_output.put_line( ' not?found ' );
? end ? if ;
end ;

-- 游標輸出no?和name
declare
?xm?first % rowtype;
? cursor ?cc? is ? select ? * ? from ?first;
begin ?
?? for ?aa? in ?cc
??loop
?? -- ??if(aa.name='002')?then
???dbms_output.put_line(aa.no || aa.name);
?? -- ?end?if;
?? end ?loop;
end ;

declare
TYPE?ref_cursor? IS ?REF? CURSOR ;


-- 建包
create ? or ? replace ?package?PKG_HOTLINE? is
????
????type?HotlineCursorType? is ?REF? CURSOR ;
　　 function ?getHotline? return ?HotlineCursorType;
　 end ;
-- 包主體
create ? or ? replace ?package?body?PKG_HOTLINE? is
??? function ?getHotline? return ?HotlineCursorType? is
??????hotlineCursor?HotlineCursorType;
　　 begin
????? open ?hotlineCursor? for ? select ? * ? from ?hotline;
　　? return ?hotlineCursor;
　　 end ;
　 end ;
　 begin

end ;

create ? or ? replace ?package?pack11? is
?type?ref_cursor1? is ?ref? cursor ?;
? function ?getCursor? return ?ref_cursor1;
end ;

create ? or ? replace ?package?body?pack11? is
?
? function ?getCursor? return ?ref_cursor1? is
??cc?ref_cursor1;
? begin
?? open ?cc? for ? select ? * ? from ?first;
?? return ?cc;
? end ;
end ;