ORACLE笔记.docx

1、ORACLE笔记 -,刚斗称, 莉 猭- -1,蹲 LST : 秸SQLPLUS祘 sqlplus nbmis/nbmisnbdb SPOOL C:EXAM.LSTSELECT * FROM TABME_NAME;SPOOL OFF -2,蹲 DMP,LOG 秸EXP祘 exp help = y -莉 腊 exp nbmis/nbmisnbdb tables = (TABME1,TABLE2) file = c:dd.dmp log = c:dd.log -3,sql 珹刚 糶 絏 exam1.sql -4, 惠璶结 CONNECT,RESOURCE 舦 碞OK -,Oracle 刚 醚翴 璶

2、羭 家砰瞷. - -DDL粂( 璶残table)-承 drop table odrm; create table odrm(fact_no char(4), fact_odr_no char(13), odr_kind char(1) default 1, odr_qty number(6), constraint p_odrm primary key (fact_no,fact_odr_no), constraint c_odrm_kind check(odr_kind in(1,2) or odr_kind = null);comment on table odrm is 璹虫 郎;com

3、ment on column odrm.fact_no is 紅 絪腹;comment on column odrm.fact_odr_no is 璹虫絪腹;comment on column odrm.odr_kind is 璹虫 ;comment on column odrm.odr_qty is 璹虫计秖; insert into odrm(fact_no,fact_odr_no,odr_kind,odr_qty) values(0236,8PAC01-001,1,100);insert into odrm(fact_no,fact_odr_no,odr_qty) values(0236

4、,8PAC01-002,200);insert into odrm(fact_no,fact_odr_no,odr_kind,odr_qty) values(0236,8PAC01-003,2,300);COMMIT; -承 羬 TABLEcreate global temporary table test (id char(6), v_name varchar2(20)on commit preserve rows; -/on commit delete rows; primary key - constraint p_odrm primary key(fact_no,fact_odr_no

5、) using index tablespace indx foreign key - constraint f_odrm_stylem foreign key (style_no) references stylem(style_no) check - constraint c_odrm_kind check(odr_kind in(1,2) or rmk = null) default - odr_kind char(1) default 1 - alter table table_name add( .)/modify(.) alter table aa add ( constraint

6、 aa_pk primary key(mz,hm) alter table table_name drop (column_name ); alter table sec drop constraint check_name; - - sequence 1, 琩高 snapshot VIEW SELECT 粂 - 2, INSERT粂 琩高 - 3, NSERT粂 VALUES - 4, UPDATE SETcreate sequence seq_factory_id increment by 1 - 碭 start with 1 - 眖1秨璸计 nomaxvalue - 砞竚程 nocycl

7、e - 仓 癹伴 cache 10; - 箇 玻 alter sequence seq_factory_id increment by 1 start with 1 maxvalue 10000 cycle - 10000 眖繷秨 nocache; -玻 drop sequence seq_factory_id CURRVAL - sequence 讽玡 NEXTVAL -糤 sequence 礛 sequence select seq_factory_id.CURRVAL,seq_factory_id.NEXTVAL from dual;-栋 -1, (盢计沮玂 摸 ) :declare t

8、ype t_odrm is record(fact_no char(4), fact_odr_no char(13); type index_odrm is table of t_odrm index by binary_integer; v_index_odrm index_odrm; - begin null;end;/* 计1) count 兵计秖 2) EXISTS 狦 兵 玥 痷 玥 安 3) LIMIT 赣猭 栋 程 计 跑 皚 盢LIMIT 碠甅 NULL 4) FRIST 赣猭 栋 程 5) LAST 赣猭 栋 程 6) NEXT 赣猭 栋 讽玡 7) PRIOR 赣猭 栋 讽

9、玡 8) DELETE 埃栋 兵 玡 9) TRIM 眖栋 场 埃 兵 礚 続 跑 皚 碠甅 10) EXTEND 栋 场睰 兵狡籹 兵 続 跑 皚 碠甅 */-2,碠甅 琌 礚 癘魁栋 declare type t_odrm is record(fact_no char(4), fact_odr_no char(13); type index_odrm is table of t_odrm; v_index_odrm index_odrm; - 碠甅 begin null;end;-3,跑 计舱 create or replace type t_var as varray(5) of var

10、char2(20); - t_var 摸 -as varray(5) : 魁5兜eg.create or replace type t_var as varray(5) of varchar2(20);create type t_person as object (name char(10), data char(8), address char(20), sex char(4), tel char(10)create table test_array (id char(4), person t_person, remark t_var ); - create index in_test_ar

11、ray_address on test_array(person.address); -糶 戈 insert into test_array values(0001,t_person(,20081120,狥拆,8633119),t_var(1 筁 ,2 筁 ,3 筁 );insert into test_array values(0002,t_person(糂,20081120,瞏 ,8633229),t_var(1 );insert into test_array values(0003,t_person(知,20081120, , ,5715562),t_var(5 瓣);insert i

12、nto test_array values(0004,t_person(碸,20081120, ,213564),null);insert into test_array values(0005,t_person(糂,20081120, , ,236541),null);commit;-UPDATEupdate test_array a set a.person.address = where a.id = 0001 -拔 遏 陪戈 select a.person.name from test_array a ;- 惠璶闽羛跑秖 aselect a.*, b.* from test_array

13、 a ,table(a.remark) b select * from table ( select remark from test_array where id = 0001)declare cursor cur_array is select * from test_array; v_id test_array.id%type; v_person test_array.person%type; v_remark t_var; begin v_remark := t_var(); open cur_array; loop fetch cur_array into v_id,v_person

14、,v_remark; exit when cur_array%notfound ; dbms_output.put_line(v_id| |v_person.name|v_person.sex); for rec_1 IN (select * from table ( select remark from test_array where id = v_id) ) loop dbms_output.put_line(rec_1.column_value); end loop; end loop; close cur_array;end; DECLARE CURSOR CUR_TEST IS S

15、ELECT * FROM TEST_ARRAY; V_PERSON t_var;BEGIN FOR REC_1 IN CUR_TEST LOOP DBMS_OUTPUT.PUT_LINE(REC_1.PERSON.NAME); V_PERSON := REC_1.REMARK1; foR i IN 1 .V_PERSON.COUNT LOOP DBMS_OUTPUT.put_line(V_PERSON(i); END LOOP; END LOOP;END; -PL/SQL粂 -1,跑秖 竡declare row int; subtype s_odr_no is odrm.fact_odr_no

16、%type; -倒竡 摸 v_odr_no s_odr_no; v_odr_no2 v_odr_no%type; subtype s_odrm is odrm%rowtype; v_s_odrm s_odrm; type t_odrm is record(fact_no char(4), -癘魁 fact_odr_no char(13); v_t_odrm t_odrm; type index_odrm is table of t_odrm index by binary_integer; -计舱 v_index_odrm index_odrm; cursor cur_odrm is sele

17、ct fact_odr_no,odr_qty from odrm; type v_t_cur_odrm is table of cur_odrm%rowtype index by binary_integer; -笴夹. 耕種 v_cur_odrm v_t_cur_odrm;begin v_odr_no := 8PAC01-001; select fact_odr_no into v_odr_no2 from odrm where fact_no = 0236 and fact_odr_no = v_odr_no; dbms_output.put_line(v_odr_no2); select

18、 * into v_s_odrm from odrm where fact_no = 0236 and fact_odr_no = v_odr_no; dbms_output.put_line(v_s_odrm.fact_no); dbms_output.put_line(v_s_odrm.fact_odr_no); select fact_no,fact_odr_no into v_t_odrm from odrm where rownum = 1; dbms_output.put_line(v_t_odrm.fact_no| | v_t_odrm.fact_odr_no); row :=

19、0; for rec_1 in(select * from odrm) loop row := row + 1; v_index_odrm(row).fact_no := rec_1.fact_no; v_index_odrm(row).fact_odr_no := rec_1.fact_odr_no; end loop; row := v_index_odrm.first; while row is not null loop dbms_output.put_line(row| |v_index_odrm(row).fact_odr_no); row := v_index_odrm.next

20、(row); end loop; row := 0; for rec_1 in cur_odrm loop row := row + 1; v_cur_odrm(row).fact_odr_no := rec_1.fact_odr_no; end loop; row := v_cur_odrm.first; while row is not null loop dbms_output.put_line(row| |v_cur_odrm(row).fact_odr_no); row := v_cur_odrm.next(row); end loop; end;-gotoDECLAREBEGIN

21、DBMS_OUTPUT.put_line(1); GOTO continue; DBMS_OUTPUT.put_line(2); DBMS_OUTPUT.put_line(3);END; -2,笴夹-笆篈笴夹dbms_sql:declare v_cursor int; i int; rows int; v_qty number(6); v_sql varchar2(2000); type t_odrm is record (fact_odr_no odrm.fact_odr_no%type, odr_qty odrm.odr_qty%type); v_odrm t_odrm; type t_i

22、ndex_odrm is table of t_odrm index by binary_integer; v_index_odrm t_index_odrm; begin - SQL v_qty := 100; v_sql := select fact_odr_no,odr_qty from odrm where odr_qty |v_qty; v_cursor := dbms_sql.open_cursor; -承 笴夹 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native ); -秆猂笆篈笴夹 dbms_sql.native ORACLE dbms_

23、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 := 0; while dbms_sql.fetch_rows(v_cursor) 0 loop i := i + 1; dbms_sql.column_value(v_cursor,1,v_index_odrm(i).fact_odr_no); dbms_sql.column

24、_value(v_cursor,2,v_index_odrm(i).odr_qty); end loop; dbms_sql.close_cursor(v_cursor); -闽超笴夹 i := v_index_odrm.first; while i is not null loop dbms_output.put_line(v_index_odrm(i).fact_odr_no| |v_index_odrm(i).odr_qty); i := v_index_odrm.next(i); end loop; -DDL v_sql := create table test as select f

25、act_odr_no,odr_qty from odrm where 1 = 2; v_cursor := dbms_sql.open_cursor; dbms_sql.parse(v_cursor,v_sql,dbms_sql.native ); rows := dbms_sql.execute(v_cursor); dbms_sql.close_cursor(v_cursor); -DML v_sql := insert into test values(:v_fact_odr_no,:v_qty); v_cursor := dbms_sql.open_cursor; dbms_sql.p

26、arse(v_cursor,v_sql,dbms_sql.native ); i := v_index_odrm.first; while i is not null loop dbms_sql.bind_variable(v_cursor, v_fact_odr_no,v_index_odrm(i).fact_odr_no); dbms_sql.bind_variable(v_cursor, v_qty,v_index_odrm(i).odr_qty); rows := dbms_sql.execute(v_cursor); i := v_index_odrm.next(i); end lo

27、op; dbms_sql.close_cursor(v_cursor); end; -EXECUTE IMMEDIATEdeclare v_sql varchar2(200); type t_odrm is record (fact_odr_no odrm.fact_odr_no%type, odr_qty odrm.odr_qty%type); v_odrm t_odrm;begin v_sql := select fact_odr_no,odr_qty from odrm where fact_odr_no = :1; execute immediate v_sql into v_odrm

28、.fact_odr_no,v_odrm.odr_qty using 8PAC01-003; dbms_output.put_line(v_odrm.fact_odr_no| |v_odrm.odr_qty); v_sql := update odrm set odr_qty = :1 where trim(fact_odr_no) = :2; execute immediate v_sql using 150, 8PAC01-003; v_sql := create table test_odrm as select * from odrm; execute immediate v_sql; end; - 紅耕盽.declare v_sql varchar2(200); type t_cur is ref cursor; cur_odrm t_cur; v_fact_odr_no char(13);begin v_sql := select fact_odr_no from odrm; open cur_odrm for v_sql