1、 constraint c_odrm_kind check(odr_kind in(,2) or odr_kind = null);comment on table odrm is 璹虫 郎;comment 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,od
2、r_qty) values(02368PAC01-001,100);insert into odrm(fact_no,fact_odr_no,odr_qty) values(8PAC01-002,200);8PAC01-003,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(fac
3、t_no,fact_odr_no) 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() or rmk = null) default - odr_kind char(1) default - alter table table_name add( .)/modify(.) alter table aa add (
4、 constraint 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 -
5、砞竚程 nocycle - 仓 癹伴 cache 10; - 箇 玻 alter sequence seq_factory_id increment by 1 start with 1 maxvalue 10000 cycle - 10000 眖繷秨 nocache; -玻 drop sequence seq_factory_idCURRVAL - sequence 讽玡 NEXTVAL -糤 sequence 礛 sequence select seq_factory_id.CURRVAL,seq_factory_id.NEXTVAL from dual;-栋 -1, (盢计沮玂 摸 ) :
6、declare type 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) PRI
7、OR 赣猭 栋 讽玡 8) DELETE 埃栋 兵 玡 9) TRIM 眖栋 场 埃 兵 礚 続 跑 皚 碠甅 10) EXTEND 栋 场睰 兵狡籹 兵 続 跑 皚 碠甅 */-2,碠甅 琌 礚 癘魁栋 type index_odrm is table of t_odrm; - 碠甅 -3,跑 计舱 create or replace type t_var as varray(5) of varchar2(20); - t_var 摸 -as varray(5) : 魁5兜eg.create type t_person as object (name char(10), data char(
8、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_array_address on test_array(person.address);-糶 戈 insert into test_array values(0001,t_person(20081120狥拆8633119),t_var(1 筁 2 筁 3 筁 );0002糂瞏 86332291 0003知 5715562
9、5 瓣0004碸213564),null);0005236541commit;-UPDATEupdate test_array a set a.person.address = where a.id = -拔 遏 陪戈 select a.person.name from test_array a ;- 惠璶闽羛跑秖 aselect a.*, b.* from test_array a ,table(a.remark) b select * from table ( select remark from test_array where id = ) cursor cur_array is se
10、lect * from test_array; v_id test_array.id%type; v_person test_array.person%type; v_remark t_var; v_remark := t_var(); open cur_array; loop fetch cur_array into v_id,v_person,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 t
11、able ( select remark from test_array where id = v_id) ) loop dbms_output.put_line(rec_1.column_value); end loop; close cur_array;DECLARE CURSOR CUR_TEST IS SELECT * 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; fo
12、R i IN 1 .V_PERSON.COUNT LOOP DBMS_OUTPUT.put_line(V_PERSON(i); END LOOP;END;-PL/SQL粂 -1,跑秖 竡 row int; subtype s_odr_no is odrm.fact_odr_no%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), -癘魁 v_t_odrm t_o
13、drm; -计舱 cursor cur_odrm is select 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; v_odr_no := select fact_odr_no into v_odr_no2 from odrm where fact_no = and fact_odr_no = v_odr_no; dbms_output.put_line(v_odr_no
14、2); select * into v_s_odrm from odrm 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 := 0; for rec_1 in(select * from odrm) loop=
15、 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; while row is not null loop dbms_output.put_line(row|v_index_odrm(row).fact_odr_no);= v_index_odrm.next(row); for rec_1 in cur_odrm loop v_cur_odrm(row).fact_odr_no := v_cur_
16、odrm.first;|v_cur_odrm(row).fact_odr_no);= v_cur_odrm.next(row);-goto DBMS_OUTPUT.put_line(); GOTO continue; 3-2,笴夹-笆篈笴夹dbms_sql: 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;
17、 type t_index_odrm is table of t_odrm index by binary_integer; v_index_odrm t_index_odrm; - 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
18、_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 : while dbms_sql.fetch_rows(v_cursor) 0 loop= i + 1; dbms_sql.column_value(v_cursor,1,v_index_odrm(i).fact_odr_no); dbms_sql.column_value(
19、v_cursor,2,v_index_odrm(i).odr_qty); dbms_sql.close_cursor(v_cursor); -闽超笴夹 while i is not null loop dbms_output.put_line(v_index_odrm(i).fact_odr_no|v_index_odrm(i).odr_qty);= v_index_odrm.next(i); -DDL create table test as select fact_odr_no,odr_qty from odrm where 1 = 2 -DMLinsert into test value
20、s(:v_fact_odr_no,:v_qty) while i is not null loop 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); -EXECUTE IMMEDIATE v_sql varchar2(200);select fact_odr_no,odr_qty from odrm where fact_odr_no = : execute immediate v_sql into v_odrm.fact_odr
21、_no,v_odrm.odr_qty using dbms_output.put_line(v_odrm.fact_odr_no|v_odrm.odr_qty);update odrm set odr_qty = :1 where trim(fact_odr_no) = : execute immediate v_sql using 150, create table test_odrm as select * from odrm execute immediate v_sql; - 紅耕盽. type t_cur is ref cursor; cur_odrm t_cur; v_fact_odr_no char(13);select fact_odr_no from odrm open cur_odrm for v_sql
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2