blogjava's web log

blogjava's web log
...

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;
  
  --通过循环取出PL/SQL表中的第一条记录的序号及内容
  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
  --通过游标往PL/SQL表中写入数据
  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表相关信息
  Dbms_Output.put_line('');
  Dbms_Output.put_line('PL/SQL表的总行数为：  '||MyTable.count||'行');
  Dbms_Output.put_line('PL/SQL表的第一行为：  '||MyTable.First||' '||MyTable(MyTable.First));
  Dbms_Output.put_line('PL/SQL表的最后一行为：'||MyTable.Last||' '||MyTable(MyTable.Last));
  
  --显示最后一条相关信息
  Dbms_Output.put_line('');
  Dbms_Output.put_line('PL/SQL表的第80行为：    '||MyTable(80));
  Dbms_Output.put_line('PL/SQL表的第80行之后为：'||MyTable.Next(80));
  
  --示范通过循环取出PL/SQL表中的每一条记录的序号及内容
  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('现在PL/SQL表的总行数为：  '||MyTable.count||'行');
  --通过循环取出PL/SQL表中的每一条记录的序号及内容
  MyOutPut;
  
  --示范从PL/SQL表中删除行
  Dbms_Output.put_line('');
  MyTable.Delete;
  Dbms_Output.put_line('已从PL/SQL表删除全部行');
  Dbms_Output.put_line('现在PL/SQL表的总行数为：  '||MyTable.count||'行');

  --取消以下注释将会引发异常
  Dbms_Output.put_line('');
  --MyOutPut;

 Exception
   --修改上面的代码，有意触发此异常
   --此示例说明了，只有对PL/SQL表中进行了赋值的记录才可以引用；但不需要按顺序对每条
   --记录进行赋值；如果试图访问没有赋值的记录，将会引发错误；通过集合函数对PL/SQL表
   --进行操作时，如果超出了记录范围，则返回空值   
   When Others then
     Dbms_Output.put_line('发生了错误！'||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;
  
  --定义一个游标
  Cursor MyCursor Is
    SELECT * From Rs_Employees 
     Where HeTongId<='WL-090010';
BEGIN
  --通过游标往记录中写入数据
  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;
/

可变数组

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);
  
  --捕获当前时间的过程
  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循环插入
  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('执行时间（秒）');
  DBMS_OUTPUT.put_line('--------------------------');
  DBMS_OUTPUT.put_line('For循环：'||To_char(t2-t1));
  DBMS_OUTPUT.put_line('ForAll： '||To_char(t3-t2));
End;
/

抽象数据类型

 

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';


可变数组

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 阅读(703) 评论(1)  编辑  收藏 所属分类: Database

评论

# re: oracle语句备查 2006-05-09 10:44 寒晴天

嘿嘿。  回复  更多评论   


只有注册用户登录后才能发表评论。
网站导航:
 

导航

常用链接

留言簿(44)

新闻档案

2.动态语言

3.工具箱

9.文档教程

友情链接

搜索

最新评论