欧美日韩二区三区,亚洲精品国产精品国自产观看,亚洲综合视频

oracle語句備查

備用。。

PLSQL表

CREATE?OR?REPLACE?PROCEDURE?MY_PLSQL_TABLE?AS

??--定義一個PL/SQL表

??TYPE?MyType?IS?Table?OF?Rs_Employees.Name%Type?

????Index?By?Binary_Integer;

??--定義二個變量

??MyTable?MyType;

??i???????binary_integer:=0;

??--通過循環(huán)取出PL/SQL表中的第一條記錄的序號及內(nèi)容

??Procedure?MyOutPut?Is

??Begin

????I?:=?MyTable.First;

????Dbms_Output.put_line('第'||To_char(I)||'行為：'||MyTable(I));

????Loop

??????I?:=?MyTable.Next(I);

??????Dbms_Output.put_line('第'||To_char(I)||'行為：'||MyTable(I));

??????Exit?When?I>=?MyTable.Last;

????End?Loop;

??End;

BEGIN

??--通過游標(biāo)往PL/SQL表中寫入數(shù)據(jù)

??FOR?tmp_cur?in?(SELECT?HeTongId,Name?

???????????????????From?Rs_Employees?

???????????????????Where?HeTongId<='WL-090010')

??LOOP

????i??????????:=?i?+?1;

????MyTable(i)?:=?tmp_cur.Name;

????Dbms_Output.put_line('原表中合同號為：'||tmp_cur.hetongid||'??姓名為：'||tmp_cur.Name);

????Dbms_Output.put_line('PL/SQL表中姓名為：'||Mytable(i));

??END?LOOP;

??--跳過前面的順序，有意增加一條記錄

??MyTable(80)?:=?'XW';

??--?顯示PL/SQL表相關(guān)信息

??Dbms_Output.put_line('');

??Dbms_Output.put_line('PL/SQL表的總行數(shù)為：??'||MyTable.count||'行');

??Dbms_Output.put_line('PL/SQL表的第一行為：??'||MyTable.First||'?'||MyTable(MyTable.First));

??Dbms_Output.put_line('PL/SQL表的最后一行為：'||MyTable.Last||'?'||MyTable(MyTable.Last));

??--顯示最后一條相關(guān)信息

??Dbms_Output.put_line('');

??Dbms_Output.put_line('PL/SQL表的第80行為：????'||MyTable(80));

??Dbms_Output.put_line('PL/SQL表的第80行之后為：'||MyTable.Next(80));

??--示范通過循環(huán)取出PL/SQL表中的每一條記錄的序號及內(nèi)容

??Dbms_Output.put_line('');

??MyOutPut;

??--示范從PL/SQL表中刪除行

??Dbms_Output.put_line('');

??MyTable.Delete(3);

??Dbms_Output.put_line('已從PL/SQL表刪除第3行');

??Dbms_Output.put_line('現(xiàn)在PL/SQL表的總行數(shù)為：??'||MyTable.count||'行');

??--通過循環(huán)取出PL/SQL表中的每一條記錄的序號及內(nèi)容

??MyOutPut;

??--示范從PL/SQL表中刪除行

??Dbms_Output.put_line('');

??MyTable.Delete;

??Dbms_Output.put_line('已從PL/SQL表刪除全部行');

??Dbms_Output.put_line('現(xiàn)在PL/SQL表的總行數(shù)為：??'||MyTable.count||'行');

??--取消以下注釋將會引發(fā)異常

??Dbms_Output.put_line('');

??--MyOutPut;

?Exception

???--修改上面的代碼，有意觸發(fā)此異常

???--此示例說明了，只有對PL/SQL表中進(jìn)行了賦值的記錄才可以引用；但不需要按順序?qū)γ織l

???--記錄進(jìn)行賦值；如果試圖訪問沒有賦值的記錄，將會引發(fā)錯誤；通過集合函數(shù)對PL/SQL表

???--進(jìn)行操作時，如果超出了記錄范圍，則返回空值???

???When?Others?then

?????Dbms_Output.put_line('發(fā)生了錯誤！'||I);

END;

記錄

DECLARE

??--聲明一個記錄類型

??TYPE?TYPE_RSRECORD?IS?RECORD?

???(HETONGID???RS_EMPLOYEES.HeTongId%Type,

????NAME???????RS_EMPLOYEES.Name%type,

????SEX????????RS_EMPLOYEES.Sex%type,

????DEPARTMENT?RS_EMPLOYEES.Department%type,

????HIREDATE???RS_EMPLOYEES.Hiredate%type);

??--定義一個記錄變量

??Rs_REcord???TYPE_RSRECORD;

??--定義一個游標(biāo)

??Cursor?MyCursor?Is

????SELECT?*?From?Rs_Employees?

?????Where?HeTongId<='WL-090010';

BEGIN

??--通過游標(biāo)往記錄中寫入數(shù)據(jù)

??Open?MyCursor;

??Loop

????Fetch?MyCursor?Into?Rs_Record;

????Exit?When?MyCursor%Notfound;

????Dbms_Output.put_line('合同號為：'||Rs_Record.hetongid||'??姓名為：'||Rs_Record.Name);

??End?Loop;

??Close?MyCursor;

END;

可變數(shù)組

DECLARE

??TYPE?itemcode1??IS?varray(5)?of?varchar2(5);

??TYPE?qty_ord1???IS?varray(5)?of?Number(5);

??TYPE?qty_deld1??IS?varray(5)?of?Number(5);

??v_itemcode?????itemcode1;

??v_qty_ord??????qty_ord1?:=?qty_ord1(1,2);

BEGIN

??IF?v_itemcode?is?NULL?Then

????DBMS_OUTPUT.put_line('v_itemcode包含空值');

??END?IF;

??IF?v_qty_ord?is?NULL?Then

????DBMS_OUTPUT.put_line('v_qty_ord包含空值');

??ELSE

????DBMS_OUTPUT.put_line('v_qty_ord非空');

??END?IF;

END;

批量綁定

--先建立一張表，用于測試

CREATE?TABLE?VENDOR

?(VENCODE?VARCHAR2(5),

??VENNAME?VARCHAR2(15));

--測試批量綁定??????

DECLARE?

??--定義二張PL/SQL表

??TYPE?NumTab??Is?Table?Of?VarCHAR2(5)??INDEX?BY?BINARY_INTEGER;

??TYPE?NameTab?Is?Table?Of?VarCHAR2(15)?INDEX?BY?BINARY_INTEGER;

??vnums??NumTab;

??vNames?NameTab;

??--三個時間變量

??t1?varchar2(5);

??t2?varchar2(5);

??t3?varchar2(5);

??--捕獲當(dāng)前時間的過程

??Procedure?get_time(t?Out?Number)?Is

??BEGIN

????SELECT?TO_CHAR(SYSDATE,'SSSSS')?INTO?t?FROM?DUAL;

??END;

BEGIN

??FOR?j?IN?1..20000

??LOOP

????vnums(j)??:=j;

????vNames(j)?:='vendor'?||?To_char(j);

??End?loop;

??get_time(t1);

??--用FOR循環(huán)插入

??For?i?In?1..20000?

??LOOP

????Insert?Into?vendor?(vencode,venname)

??????Values(vnums(i),vnames(i));

??END?LOOP;

??get_time(t2);

??--用FORALL插入

??FORALL?i?In?1..20000

????Insert?Into?vendor?(vencode,venname)

??????Values(vnums(i),vnames(i));

??get_time(t3);

????

??DBMS_OUTPUT.put_line('執(zhí)行時間（秒）');

??DBMS_OUTPUT.put_line('--------------------------');

??DBMS_OUTPUT.put_line('For循環(huán)：'||To_char(t2-t1));

??DBMS_OUTPUT.put_line('ForAll：?'||To_char(t3-t2));

End;

抽象數(shù)據(jù)類型

CREATE?OR?REPLACE?TYPE?address_ty?AS?OBJECT

??(street_no???????????number(3),

???street_name?????????varchar2(20),

???city????????????????varchar2(20),

???state???????????????varchar2(20));

CREATE?TABLE?vend_mast

?(vencode????varchar2(5),

??venname????varchar2(15),

??venadd?????address_ty,

??tel_no?????number(10));

INSERT?INTO?vend_mast?VALUES

?('v100','john',address_ty(110,'Clinton?Rd??','Rosewood','Columbia'),

??234465987);

SELECT?*?FROM?vend_mast;

select?a.venadd.city?from?vend_mast?a;

UPDATE?vend_mast?a

??set?a.venadd.street_no?=?10

??WHERE?venname='john';

DELETE?FROM?vend_mast?a

??WHERE?a.venadd.city='Rosewood';

DROP?TYPE?address_ty;

CREATE?INDEX?streetnum?ON?vend_mast?(venadd.street_no);

CREATE?OR?REPLACE?TYPE?Student_typ?AS?OBJECT?

??(ssn????number,

???Name???varchar2(30),

???Address?varchar2(100))?NOT?FINAL

對象表.

CREATE?TYPE?vend_ty?AS?Object

?(vencode?varchar2(5),

??venname?varchar2(20),

??venadd1?varchar2(20),

??venadd2?varchar2(20),

??venadd3?varchar2(20),

??tel_no?number(6));

????

CREATE?TABLE?vend_master?OF?vend_ty

?(vencode?CONSTRAINT?VC_PK?PRIMARY?KEY);

INSERT?INTO?vend_master?values?

??(vend_ty('v201','John','10','Fezinnith','Mexico',948456));

SELECT?vencode?FROM?vend_master;

對象視圖

CREATE?TABLE?item

?(itemcode?varchar2(10),

??item_on_hand?number(10),

??item_sold?number(10));

CREATE?OR?REPLACE?TYPE?ITEM_TYPE?AS?OBJECT

?(itemcode?varchar2(10),

??item_on_hand?number(10),

??item_sold?number(10));

????

CREATE?VIEW?ITEM_VIEW?OF?ITEM_TYPE?

?with?object?oid(itemcode)?As

?SELECT?*?FROM?ITEM?WHERE?ITEM_ON_HAND?<?20;

INSERT?INTO?ITEM?VALUES?('i201',10,5);

INSERT?INTO?item_view?Values?(item_type('i102',15,50));

select?*?from?item_view;

DELETE?FROM?ITEM_VIEW?WHERE?ItEMCODE='i102';

可變數(shù)組

CREATE?TYPE?itemcode??AS?varray(5)?of?varchar2(5);

CREATE?TYPE?qty_ord???AS?varray(5)?of?number(5);

CREATE?TYPE?qty_deld??AS?varray(5)?of?number(5);

CREATE?TABLE?ORDER_DETAIL

?(ORDERNO????VARCHAR2(5),

??ITEM_VA????ITEMCODE,

??QTY_VA?????QTY_ORD,

??QTYD_VA????QTY_DELD);

INSERT?INTO?order_detail?VALUES?

?('o100',itemcode('i100','i101','i102','i103','i104'),

??qty_ord(100,98,49,39,20),

??qty_deld(100,900,800,700,600));

INSERT?INTO?order_detail?VALUES?

?('o101',itemcode('i102','i103','i104'),

??qty_ord(100,98,20),

??qty_deld(100,900));

???

SELECT?*?FROM?ORDER_DETAIL;

嵌套表

CREATE?TYPE?ord_ty?As?Object

?(itemcode??varchar2(5),

??qty_ord???number(5),

??qty_deld??number(5));

CREATE?TYPE?ord_nt?AS?Table?OF?ord_ty;

CREATE?TABLE?order_master

?(orderno????varchar2(5),

??odate??????date,

??vencode????varchar2(5),

??dets???????ord_nt)??

??NESTED?TABLE?dets?STORE?AS?ord_nt_tab;

INSERT?INTO?order_master?VALUES?

??('o100',To_date('18-07-99','dd-mm-yy'),'v001',

????ord_nt(

??????ord_ty('i100',10,5),

??????ord_ty('i101',50,25),

??????ord_ty('i102',5,5)

?????)

??);

????

INSERT?INTO?TABLE?(SELECT?p.dets?

??FROM?order_master?p

??WHERE?p.orderno='o100')

??Values?('i103',30,25);

SELECT?*?FROM?TABLE?(SELECT?t.dets?FROM?order_master?t?

??Where?t.orderno?=?'o100');

UPDATE?TABLE?(SELECT?e.dets?from?order_master?e

??WHERE?e.orderno?=?'o100')?p

??SET?VALUE(p)?=?ord_ty('i103',50,45)

??Where?p.itemcode?=?'i103';

DELETE?FROM?TABLE?(SELECT?e.dets?from?order_master?e

??WHERE?e.orderno?=?'o100')?p

??Where?p.itemcode?=?'i103';

posted on 2006-05-09 08:49 record java and net 閱讀(712) 評論(1) 編輯收藏所屬分類: Database


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問
相關(guān)文章: SqlServer 2005 報表自動部署 sql備忘-osql 實(shí)用工具 oracle語句備查 oracle練習(xí)(異常) 監(jiān)聽啟動的問題 oracle游標(biāo)備忘 oracle基本語句(一)

# re: oracle語句備查 2006-05-09 10:44 寒晴天

blogjava's web log

oracle語句備查

評論

導(dǎo)航

常用鏈接

留言簿(44)

新聞檔案

2.動態(tài)語言

3.工具箱

9.文檔教程

友情鏈接

搜索

最新評論