ORACLE笔记Word文档下载推荐.docx
《ORACLE笔记Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《ORACLE笔记Word文档下载推荐.docx(21页珍藏版)》请在冰点文库上搜索。
constraintc_odrm_kindcheck(odr_kindin('
'
2'
)orodr_kind=null));
commentontableodrmis'
璹虫郎'
;
commentoncolumnodrm.fact_nois'
紅絪腹'
commentoncolumnodrm.fact_odr_nois'
璹虫絪腹'
commentoncolumnodrm.odr_kindis'
璹虫ず'
commentoncolumnodrm.odr_qtyis'
璹虫计秖'
insertintoodrm(fact_no,fact_odr_no,odr_kind,odr_qty)values('
0236'
8PAC01-001'
100);
insertintoodrm(fact_no,fact_odr_no,odr_qty)values('
8PAC01-002'
200);
8PAC01-003'
300);
COMMIT;
--承羬TABLE
createglobaltemporarytabletest
(idchar(6),
v_namevarchar2(20))
oncommitpreserverows;
--/oncommitdeleterows;
primarykey--constraintp_odrmprimarykey(fact_no,fact_odr_no)usingindextablespaceindx
foreignkey--constraintf_odrm_stylemforeignkey(style_no)referencesstylem(style_no))
check--constraintc_odrm_kindcheck(odr_kindin('
)orrmk=null))
default--odr_kindchar
(1)default'
--э
altertabletable_name
add(..)/modify(..)
altertableaa
add(constraintaa_pkprimarykey(mz,hm))
altertabletable_name
drop(column_name);
altertablesec
dropconstraintcheck_name;
---------Ω---------
--ㄏノsequenceよ1,ぃ琩高snapshotVIEWSELECT粂
--2,INSERT粂琩高い
--3,NSERT粂VALUESい
--4,UPDATESETい
createsequenceseq_factory_id
incrementby1--–Ω碭
startwith1--眖1秨﹍璸计
nomaxvalue--ぃ砞竚程
nocycle--仓ぃ癹伴
cache10;
--箇玻ネぶ
altersequenceseq_factory_id
incrementby1
startwith1
maxvalue10000
cycle--10000眖繷秨﹍
nocache;
--–Ω玻ネ
dropsequenceseq_factory_id
CURRVAL--sequence讽玡
NEXTVAL--糤sequence礛sequence
selectseq_factory_id.CURRVAL,seq_factory_id.NEXTVALfromdual;
---------栋---------
--1,ま(盢计沮玂摸い)
:
declare
typet_odrmisrecord(fact_nochar(4),
fact_odr_nochar(13));
typeindex_odrmistableoft_odrmindexbybinary_integer;
v_index_odrmindex_odrm;
--ま
begin
null;
end;
/*まいㄧ计
1)count兵ヘ计秖
2)EXISTS狦﹚兵ヘ玥痷玥安
3)LIMIT赣よ猭栋程じ计ヘΤ跑皚Τ盢LIMITノ碠甅㎝まㄤNULL
4)FRIST赣よ猭栋いㄏノ程ま
5)LAST赣よ猭栋いㄏノ程ま
6)NEXT赣よ猭栋い讽玡ㄏノま
7)PRIOR赣よ猭栋い讽玡ㄏノま
8)DELETE埃栋い兵ヘǎ玡ㄒ
9)TRIM眖栋Ю场埃┪兵ヘ礚続ノ跑皚㎝碠甅
10)EXTEND栋Ю场睰兵ヘ┪狡籹Τ兵ヘ続ノ跑皚㎝碠甅*/
--2,碠甅琌礚癘魁栋
typeindex_odrmistableoft_odrm;
--碠甅
--3,跑计舱
createorreplacetypet_varasvarray(5)ofvarchar2(20);
--ミt_var摸
--asvarray(5)ボ:
–魁Τ5兜
eg.
createtypet_personasobject
(namechar(10),
datachar(8),
addresschar(20),
sexchar(4),
telchar(10))
createtabletest_array
(idchar(4),
persont_person,
remarkt_var);
--ミま
createindexin_test_array_addressontest_array(person.address);
--糶戈
insertintotest_arrayvalues('
0001'
t_person('
㏄'
20081120'
狥拆'
╧'
8633119'
),t_var('
1る筁ㄊ'
2る筁'
3る筁'
));
0002'
糂'
瞏'
8633229'
1るセ'
0003'
知'
ㄊ'
'
5715562'
5る瓣'
0004'
碸'
213564'
),null);
0005'
236541'
commit;
--UPDATE
updatetest_arraya
seta.person.address='
wherea.id='
--拔遏陪ボ戈
selecta.person.namefromtest_arraya;
--惠璶ㄏノ闽羛跑秖a
selecta.*,b.*fromtest_arraya,table(a.remark)b
select*fromtable(selectremarkfromtest_arraywhereid='
)
cursorcur_array
is
select*fromtest_array;
v_idtest_array.id%type;
v_persontest_array.person%type;
v_remarkt_var;
v_remark:
=t_var();
opencur_array;
loop
fetchcur_arrayintov_id,v_person,v_remark;
exitwhencur_array%notfound;
dbms_output.put_line(v_id||'
||v_person.name||v_person.sex);
forrec_1IN(select*fromtable(selectremarkfromtest_arraywhereid=v_id))loop
dbms_output.put_line(rec_1.column_value);
endloop;
closecur_array;
DECLARE
CURSORCUR_TESTIS
SELECT*FROMTEST_ARRAY;
V_PERSONt_var;
BEGIN
FORREC_1INCUR_TESTLOOP
DBMS_OUTPUT.PUT_LINE(REC_1.PERSON.NAME);
V_PERSON:
=REC_1.REMARK1;
foRiIN1..V_PERSON.COUNTLOOP
DBMS_OUTPUT.put_line(V_PERSON(i));
ENDLOOP;
END;
---------PL/SQL粂ēㄏノ---------
--1,跑秖﹚竡
rowint;
subtypes_odr_noisodrm.fact_odr_no%type;
--倒ノめ﹚竡摸㏑
v_odr_nos_odr_no;
v_odr_no2v_odr_no%type;
subtypes_odrmisodrm%rowtype;
v_s_odrms_odrm;
typet_odrmisrecord(fact_nochar(4),--癘魁
v_t_odrmt_odrm;
--计舱
cursorcur_odrmis
selectfact_odr_no,odr_qtyfromodrm;
typev_t_cur_odrmistableofcur_odrm%rowtypeindexbybinary_integer;
--まノ笴夹..ゑ耕Τ種
v_cur_odrmv_t_cur_odrm;
v_odr_no:
='
selectfact_odr_nointov_odr_no2fromodrm
wherefact_no='
andfact_odr_no=v_odr_no;
dbms_output.put_line(v_odr_no2);
select*intov_s_odrmfromodrm
dbms_output.put_line(v_s_odrm.fact_no);
dbms_output.put_line(v_s_odrm.fact_odr_no);
selectfact_no,fact_odr_nointov_t_odrm
fromodrm
whererownum=1;
dbms_output.put_line(v_t_odrm.fact_no||'
||v_t_odrm.fact_odr_no);
row:
=0;
forrec_1in(select*fromodrm)loop
=row+1;
v_index_odrm(row).fact_no:
=rec_1.fact_no;
v_index_odrm(row).fact_odr_no:
=rec_1.fact_odr_no;
=v_index_odrm.first;
whilerowisnotnullloop
dbms_output.put_line(row||'
||v_index_odrm(row).fact_odr_no);
=v_index_odrm.next(row);
forrec_1incur_odrmloop
v_cur_odrm(row).fact_odr_no:
=v_cur_odrm.first;
||v_cur_odrm(row).fact_odr_no);
=v_cur_odrm.next(row);
--goto
DBMS_OUTPUT.put_line('
);
GOTOcontinue;
<
<
continue>
>
3'
--2,笴夹
--笆篈笴夹
dbms_sql:
v_cursorint;
iint;
rowsint;
v_qtynumber(6);
v_sqlvarchar2(2000);
typet_odrmisrecord
(fact_odr_noodrm.fact_odr_no%type,
odr_qtyodrm.odr_qty%type);
v_odrmt_odrm;
typet_index_odrmistableoft_odrmindexbybinary_integer;
v_index_odrmt_index_odrm;
--SQL
v_qty:
=100;
v_sql:
selectfact_odr_no,odr_qtyfromodrmwhereodr_qty>
||v_qty;
v_cursor:
=dbms_sql.open_cursor;
--承笴夹
dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
--秆猂笆篈笴夹dbms_sql.nativeORACLEセ
dbms_sql.define_column(v_cursor,1,v_odrm.fact_odr_no,13);
--だ﹚竡SQL粂い琿癸莱跑秖
dbms_sql.define_column(v_cursor,2,v_odrm.odr_qty);
rows:
=dbms_sql.execute(v_cursor);
--笲︽笴夹
i:
whiledbms_sql.fetch_rows(v_cursor)>
0loop
=i+1;
dbms_sql.column_value(v_cursor,1,v_index_odrm(i).fact_odr_no);
dbms_sql.column_value(v_cursor,2,v_index_odrm(i).odr_qty);
dbms_sql.close_cursor(v_cursor);
--闽超笴夹
whileiisnotnullloop
dbms_output.put_line(v_index_odrm(i).fact_odr_no||'
||v_index_odrm(i).odr_qty);
=v_index_odrm.next(i);
---DDL
createtabletestasselectfact_odr_no,odr_qtyfromodrmwhere1=2'
--DML
insertintotestvalues(:
v_fact_odr_no,:
v_qty)'
whileiisnotnullloop
dbms_sql.bind_variable(v_cursor,'
v_fact_odr_no'
v_index_odrm(i).fact_odr_no);
v_qty'
v_index_odrm(i).odr_qty);
----EXECUTEIMMEDIATE
v_sqlvarchar2(200);
selectfact_odr_no,odr_qtyfromodrmwherefact_odr_no=:
executeimmediatev_sqlintov_odrm.fact_odr_no,v_odrm.odr_qtyusing'
dbms_output.put_line(v_odrm.fact_odr_no||'
||v_odrm.odr_qty);
updateodrmsetodr_qty=:
1wheretrim(fact_odr_no)=:
executeimmediatev_sqlusing150,'
createtabletest_odrmasselect*fromodrm'
executeimmediatev_sql;
--紅ゑ耕盽ノ..
typet_curisrefcursor;
cur_odrmt_cur;
v_fact_odr_nochar(13);
selectfact_odr_nofromodrm'
opencur_odrmforv_sql