oracle pl/sql模板代碼怎么寫

oracle pl/sql模板代碼怎么寫，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

select * from scott.emp;

==============for 循環(huán)=======================

declare
    empno NUMBER(4,0);
 ename VARCHAR2(10 BYTE);
    sal NUMBER(7,2);
begin
    for REC in
        (select empno, ename, sal from scott.emp)
    loop
        begin
            empno := REC.empno;
            ename := REC.ename;
            sal := REC.sal;
            /*---------------------------------寫入一條明細(xì)開始------------------------------*/
            if NVL(empno,0) <> 0 then
                begin
                    dbms_output.put_line('記錄： ' || empno || ' ' || ename || ' ' || sal );
                end;
            end if;
            /*---------------------------------寫入一條明細(xì)開始------------------------------*/
        end;
    end loop;
end;
/

--===============帶參數(shù)的游標(biāo)==================--
DECLARE
   dept_code emp.deptno%TYPE; --聲明列類型變量三個
   emp_code   emp.empno%TYPE;
   emp_name   emp.ename%TYPE;
   CURSOR emp_cur(deptparam NUMBER) IS
     SELECT empno, ename FROM EMP WHERE deptno = deptparam; --聲明顯示游標(biāo)
BEGIN
   dept_code := &部門編號; --請用戶輸入想查看的部門編號
   OPEN emp_cur(dept_code); --打開游標(biāo)
   LOOP
     --死循環(huán)
     FETCH emp_cur
       INTO emp_code, emp_name; --提取游標(biāo)值賦給上面聲明的變量
     EXIT WHEN emp_cur%NOTFOUND; --如果游標(biāo)里沒有數(shù)據(jù)則退出循環(huán)
     DBMS_OUTPUT.PUT_LINE(emp_code || '' || emp_name); --輸出查詢
   END LOOP;
   CLOSE emp_cur; --關(guān)閉游標(biāo)
END;

--=================REF游標(biāo)==================--

ACCEPT tab FROMPT '你想查看什么信息?員工(E)或部門信息(D):'; --使用ACCEPT命令彈出對話框讓用戶輸入數(shù)據(jù)
DECLARE
   TYPE refcur_t IS REF CURSOR; --聲明REF游標(biāo)類型
   refcur     refcur_t; --聲明REF游標(biāo)類型的變量
   pid        NUMBER;
   p_name     VARCHAR2(100);
   selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1)); --截取用戶輸入的字符串并轉(zhuǎn)換為大寫
BEGIN
   IF selection = 'E' THEN
     --如果輸入的是'E',則打開refcurr游標(biāo),并將員工表查詢出來賦值給此游標(biāo)
     OPEN refcur FOR
       SELECT EMPNO ID, ENAME NAME FROM EMP;
     DBMS_OUTPUT.PUT_LINE('=====員工信息=====');
   ELSIF selection = 'D' THEN
     --如果輸入是'D',則打開部門表
     OPEN refcur FOR
       SELECT deptno id, dname name FROM DEPT;
     DBMS_OUTPUT.PUT_LINE('=====部門信息======');
   ELSE
     --否則返回結(jié)束
     DBMS_OUTPUT.PUT_LINE('請輸入員工信息(E)或部門信息(D)');
     RETURN;
   END IF;
   FETCH refcur
     INTO pid, p_name; --提取行
   WHILE refcur%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE('#' || pid || ':' || p_name);
     FETCH refcur
       INTO pid, p_name;
   END LOOP;
   CLOSE refcur; --關(guān)閉游標(biāo)
END;

--===================動態(tài)SQL=================--
VARIABLE maxsal NUMBER; --聲明變量
EXECUTE :maxsal := 2500; --執(zhí)行引用并給變量賦值
DECLARE
   r_emp EMP%ROWTYPE; --聲明一個行類型變量
   TYPE c_type IS REF CURSOR; --聲明REF游標(biāo)類型
   cur       c_type; --聲明REF游標(biāo)類型的變量
   p_salary NUMBER; --聲明一個標(biāo)量變量
BEGIN
   p_salary := :maxsal; --引用變量
   --使用USING語句將引用到的值傳給動態(tài)SQL語句'SAL >: 1'中的'1'
   OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'
     USING p_salary;
   DBMS_OUTPUT.PUT_LINE('薪水大于' || p_salary || '的員工有:');
   LOOP
     FETCH cur
       INTO r_emp;
     EXIT WHEN cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('編號:' || r_emp.empno || '姓名:' || r_emp.ename ||
                          '薪水:' || r_emp.sal);
   END LOOP;
   CLOSE cur; --關(guān)閉游標(biāo)
END;

-- 例子：
CREATE OR REPLACE PROCEDURE x_ne_change
AS
   CURSOR cur_new
   IS
      SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
             omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
             trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
             max_pdch, device_type, software_version, dumpfre_type, site_no,
             cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
             TIMESTAMP
        FROM appuser.K_C_CELL
       WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 2000;

   CURSOR cur_old (c_no NUMBER)
   IS
      SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
             omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
             trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
             max_pdch, device_type, software_version, dumpfre_type, site_no,
             cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
             TIMESTAMP
        FROM appuser.K_C_CELL
       WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no;
BEGIN
   FOR v_new IN cur_new
   LOOP
      FOR v_old IN cur_old (v_new.cell_id)
      LOOP
         BEGIN
            IF v_new.related_id <>; v_old.related_id
            THEN
               INSERT INTO TEST_NE_CHANGE
                           (omc_id, omc_int_id, ne_id,
                            old_value, now_value,
                            modify_item, modify_time
                           )
                    VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
                            v_old.related_id, v_new.related_id,
                            'related_id', v_new.TIMESTAMP
                           );

               COMMIT;
            END IF;

            IF v_new.tch <>; v_old.tch
            THEN
               INSERT INTO TEST_NE_CHANGE
                           (omc_id, omc_int_id, ne_id,
                            old_value, now_value, modify_item, modify_time
                           )
                    VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
                            v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP
                           );

               COMMIT;
            END IF;
         END;
      END LOOP;
   END LOOP;
END;

--- 多行數(shù)據(jù)提取
declare
 cursor cur_tsalary is
    select employeeid,positionid from tsalary whererownum < 10;
 type rec_tsalary isrecord(
 employeeid tsalary.employeeid%type,
 positionid tsalary.positionid%type);
 type all_rec_tsalary_type istableof rec_tsalary;
 all_rec_tsalary all_rec_tsalary_type;
begin
 --一次處理所有
    fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
    for i in1..all_rec_tsalary.countloop      dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
    endloop;
--使用limit分批提出大量數(shù)據(jù)
 open cur_tsalary;
 loop
    fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
    for i in1..all_rec_tsalary.countloop      dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
    endloop;
    exitwhen cur_tsalary%notfound;
 endloop;
 close cur_tsalary;
end;

 -- 例六：%NotFound
 
 BEGIN
   DELETE   FROM  Rs_Employees
     Where  HeTongId = ' WL-090001 ' ;
 
   if  sql % Notfound  then
    Dbms_Output.put_line( ' 沒有找到要刪除的記錄 ' );
   else
    Dbms_Output.put_line( ' 已刪除記錄 ' );
   end   if ;

 END ;
  
  
  
 -- 例七：%RowCount，查詢記錄行數(shù)
 Declare
  v_name Rs_Employees.Name % type;
 BEGIN
   SELECT  Name  Into  v_Name
     FROM  Rs_Employees
     Where  HeTongId = ' WL-090010 ' ;
 
   if  sql % RowCount   > 0   Then
    Dbms_Output.put_line( ' 已從表中選擇行,Name為： ' || v_Name);
   else
    Dbms_Output.put_line( ' 從表中未選擇行 ' );
   end   if ;

 END ;
  
 -- 再演示以下代碼
 BEGIN
   DELETE   FROM  Rs_Employees
     Where  HeTongId <= ' WL-090010 ' ;
 
  Dbms_Output.put_line( ' 已從表中刪除 ' || To_Char(sql % RowCount ) || ' 條記錄 ' );
 END ;  
  
 
 
 -- 例八：顯式游標(biāo)
-- 以下示例在所有游標(biāo)的記錄中的Name字段中加一字串
-- 同時在此示范了如何使用%NotFound屬性
-- set serveroutput on;
 Declare
  v_Id    Rs_Employees.Hetongid % type;
  v_name  Rs_Employees.Name % type;
  v_Count  Number : = 0 ;
 
   Cursor  MyCur  Is
     SELECT  HetongId,Name  FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 ' ;
     
 BEGIN
   -- 打開游標(biāo) 
    Open  MyCur;
   -- 進(jìn)入循環(huán)
   Loop
     Fetch  MyCur  Into  v_id,v_name;
     Exit   When  MyCur % NotFound;
   
     Update  Rs_Employees
       Set  Name  =  Name  ||   ' X '
       Where  HeTongId = v_Id;
    v_Count : =  v_Count  +   1 ;
   End  Loop;
 
  Dbms_Output.put_line( ' 已更新 ' || v_Count || ' 行 ' );
 END ;

 -- 例10：以下示范%RowCount和%IsOpen，同時示范了%RowType的使用
-- 例10：以下示例%RowCount
 Declare
  v_Row   Rs_Employees % RowType;
 
   Cursor  MyCur  Is
     SELECT   *   FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 ' ;
 BEGIN
   if   Not  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游標(biāo)未打開 ' );
     -- 打開游標(biāo) 
      Open  MyCur;
   end   if ;

   -- 進(jìn)入循環(huán)
   Loop
     Fetch  MyCur  Into  v_row;
     Exit   When  MyCur % NotFound;
 
    Dbms_Output.put_line( ' 當(dāng)前已取得 ' || MyCur % RowCount || ' 行 ' );
    Dbms_Output.put_line( ' 姓名： ' || v_row.Name || '      ' || ' 合同號： ' || v_row.HeTongId);
    Dbms_Output.put_line( '' );
   End  Loop;
 
  Dbms_Output.put_line( ' 總共已取得 ' || MyCur % RowCount || ' 行 ' );
 
   if  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游標(biāo)已打開 ' );
     Close  MyCur;
   end   if ;

   if   Not  MyCur % IsOpen  then
    Dbms_Output.put_line( ' 游標(biāo)已關(guān)閉 ' );
   end   if ;
 
 END ;

 -- 例12：查詢嵌套表中數(shù)據(jù)的游標(biāo)
-- 1 創(chuàng)建類型
    CREATE   OR   REPLACE  TYPE emp_type  As  Object
   (eno    number ,
    ename  varchar2 ( 20 ),
    esal   number );
 -- 2 使用Table of 子句創(chuàng)建Table類型
    CREATE  TYPE emp_nt  AS   Table   Of  emp_type;
 -- 3 使用emp_nt數(shù)據(jù)類型創(chuàng)建myemp表
    CREATE   TABLE  myemp
   (deptno      number ,
    edet       emp_nt)
    NESTED  TABLE  edet Store  As  myemployee;
 -- 4 初始化myemp的數(shù)據(jù)
    Insert   Into  myemp  values
    ( 10 ,emp_nt(emp_type( 1000 , ' James ' ,  10000 ),
               emp_type( 1001 , ' Daniel ' , 20000 )));
   Commit ;
 
 -- 執(zhí)行下列代碼  
 Declare
  sal   number ;
  ena   varchar2 ( 20 );
 
   Cursor  MyCur  Is
     SELECT  a.esal,a.ename
       FROM  the
      ( Select  edet  From  myemp
          Where  deptno = 10 ) a;
 BEGIN
   -- 打開游標(biāo) 
    Open  MyCur;
   -- 進(jìn)入循環(huán)
   Loop
     Fetch  MyCur  Into  sal,ena;
     Exit   When  MyCur % NotFound;
    Dbms_Output.put_line(ena || '      ' || sal);
   End  Loop;
 
   Close  MyCur;
 END ;

 -- 例13：此例改自例10，示范循環(huán)游標(biāo)的用法
 Declare
   Cursor  MyCur  Is
     SELECT   *   FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 ' ;
 BEGIN
 
   For  tmp_cur  In  MyCur
  Loop
    Dbms_Output.put_line( ' 當(dāng)前已取得 ' || MyCur % RowCount || ' 行 ' );
    Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || '      ' || ' 合同號： ' || tmp_cur.HeTongId);
   End  Loop;

   -- 在這種情況下，下面這條語句不能執(zhí)行
    -- Dbms_Output.put_line('當(dāng)前已取得'||MyCur%RowCount||'行');
 END ;

 -- 再示范帶參數(shù)的游標(biāo)
 Declare
   Cursor  MyCur(m_HeTongId Rs_Employees.Hetongid % type)  Is
     SELECT   *   FROM  Rs_Employees
       Where  HeTongId <= m_HeTongId;
 BEGIN
 
   For  tmp_cur  In  MyCur( ' WL-090020 ' )
  Loop
    Dbms_Output.put_line( ' 當(dāng)前已取得 ' || MyCur % RowCount || ' 行 ' );
    Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || '      ' || ' 合同號： ' || tmp_cur.HeTongId);
   End  Loop;

   -- 在這種情況下，下面這條語句不能執(zhí)行
    -- Dbms_Output.put_line('當(dāng)前已取得'||MyCur%RowCount||'行');
 END ;

 -- 再示范以下寫法
-- 在循環(huán)游標(biāo)中使用查詢
 Declare
  v_HeTongId  Rs_Employees.Hetongid % type;
 BEGIN
  v_HeTongId : =   ' WL-090020 ' ;
 
   For  tmp_cur  In  ( SELECT   *   FROM  Rs_Employees
                     Where  HeTongId <= v_HeTongId)
  Loop
    Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || '      ' || ' 合同號： ' || tmp_cur.HeTongId);
   End  Loop;
 END ;

 -- 例15：示范游標(biāo)變量
-- 此例要在Command window或Sql Plus中示范，結(jié)果會有點區(qū)別
-- SET SERVEROUTPUT ON;
 Declare
  TYPE r1_cur  IS  REF  CURSOR ;
  var1 r1_cur;
  no      varchar2 ( 20 );
 
  v_czy  sc_chukudan.czy % type;
  v_pid  sc_chukudanDetail.Productid % type;
 
 BEGIN
  no : =   ' &你選擇的 ' ;

   IF   UPPER (no)  =   ' MASTER '   then
     OPEN  var1  For
       Select  Czy  FROM  sc_chukudan
         Where  ChuKuDanId  =   ' SCKD04020001 ' ;
     FETCH  var1  into  v_Czy;
    Dbms_Output.put_line( ' 操作員是： ' || v_czy);
     CLOSE  var1;
   ELSE
     OPEN  var1  For
       Select  Productid  FROM  sc_chukudanDetail
         Where  ChuKuDanId  =   ' SCKD04020001 ' ;
    LOOP
       FETCH  var1  into  v_pid;
       EXIT   WHEN  var1 % NotFound;
      Dbms_Output.put_line( ' 生產(chǎn)通知單ID是： ' || v_pid);
     END  LOOP;
     CLOSE  var1;
   END   IF ;   
 END ;

 -- 例16:　游標(biāo)中的更新和刪除
-- 此例改自例8
 Declare
   -- 當(dāng)打開此游標(biāo)，將鎖住了相關(guān)記錄
    Cursor  MyCur  Is
     SELECT  Name  FROM  Rs_Employees
       Where  HeTongId <= ' WL-090010 '
       For   Update   OF  Name;
     
 BEGIN
   For  tmp_cur  in  MyCur
  Loop
     Update  Rs_Employees
       Set  Name  =  Name  ||   ' X '
       Where   Current   of  MyCur;
   End  Loop;
 END ;
 
 
 -- 示例一：Create Table 命令，區(qū)別較小
 Create   Table  vendor_master
(
 vencode  varchar2 ( 5 ),
 venname  varchar2 ( 20 ),
 venadd1  varchar2 ( 20 ),
 venadd2  varchar2 ( 20 ),
 venadd3  varchar2 ( 20 )
 )
 
 -- 示例二：Alter Table Modify 命令，區(qū)別較大
 Alter   Table  vendor_master Modify (venname  varchar2 ( 25 ))

 -- 示例三：Alter Table Add 命令，區(qū)別較小，主要是數(shù)據(jù)類型
 Alter   Table  vendor_master
   add  (tel_no  number ( 12 ),
       tngst_no  number ( 12 ))
      
 -- 示例四：Drop Column 命令：完全一樣
 Alter   Table  vendor_master  Drop   Column  tngst_no

 -- 示例五：Oracle 獨有
 alter   Table  vendor_master  set  unused(tel_no)

 -- 示例六：Truncate Table命令 ：完全一樣
 truncate   table  vendor_master

 -- 示例八：Desc命令：完全不一樣
 Desc  vendor_master

 -- 示例九：Drop Table 命令：完全一樣
 drop   table  vendor_master

 -- 示例10：Insert命令
 Insert   into  vendor_master  values  ( ' v001 ' , ' John smith ' , ' 11 E main st ' , ' West Avenue ' , ' alabama ' , 1234567 )

 -- 以下這種方法只在Orace中有效，l但不推薦使用此方法
 Insert   into  vendor_master  values  ( ' &vencode ' , ' &venname ' , ' &venadd1 ' , ' &venadd2 ' , ' &venadd3 ' , & telno)

 -- 示例15：Select命令
 select   *   from  vendor_master

 -- 示例20：Update命令 ：注意大小寫
 update  vendor_master  set  tel_no  =   987654   where  vencode = ' V001 '    --  'v001'
 
 
 -- 示例24：Grant 和 Revoke命令
 grant   all   on  vendor_master  to  sys
 revoke   all   on  vendor_master  from  sys

 **********************************************************************************************
 // 用戶

connect system / manager @ydgl ;

 -- 刪除已有的用戶和表空間
 
 -- drop tablespace freemandatabase;
-- drop tablespace tempfreemandatabase;
 
 -- 創(chuàng)建表空間
 create  tablespace FreeManDataBase
datafile  ' c:/FreeManDataBase.ora '
size 25M;

 -- 創(chuàng)建臨時表空間
 create   temporary  tablespace tempFreeManDataBase
tempfile  ' c:/tempFreeManDataBase.ora '
size 25M;

 -- 創(chuàng)建用戶
 create   user  zong identified  by  " 123456 "
 default  tablespace FreeManDataBase
 temporary  tablespace tempFreeManDataBase;

 -- 賦權(quán)限
 grant  connect  to  zong;
 grant  resource  to  zong;
 grant  dba  to  aaa;

 -- 登錄
 connect zong / 123456 @ydgl ;

 create   table  zong.ccc(bh  varchar2 ( 10 ), xm  varchar2 ( 10 ), age  number , salary  number , birthday date)
  -- 事務(wù)處理 
     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
    insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
   savepoint ppp;
    insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
    rollback   to  ppp;
    insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
    update  zong.ccc  set  bh = ' 008 '  ,salary = 5000 ,age = 33   where  bh = ' 004 ' ;
     commit  ;
 --  清除數(shù)據(jù)
     truncate   table  zong.cc
    delete   from  zong.ccc  where  bh = ' 006 '
 
 
   create   table  zong.firsttable (xm  varchar2 ( 10 ),age  number ( 4 ),salary  number ( 7 , 2 ))
   -- 添加列
    alter   table  zong.firsttable  add  (kk  number ( 10 ), birthday date)
   -- 更新列類型
    alter   table  zong.firsttable modify (xm  number ( 2 ), birthday  varchar2 ( 10 ))
 
   -- 收回權(quán)限
     revoke  dba  from  zong
   -- 授予管理員角色
     grant  dba  to  zong
  
    -- 授予對象權(quán)限
     grant   select  , update   on  firsttable  to  system
  
    -- 刪除表
     drop   table  zong.ccc
  
  

 -- 集合操作
 Create   Table  zong.YYY(xm  Varchar2 ( 10 ),age  Number ( 8 ));
 Insert   Into  zong.yyy  Values ( ' aaa ' , 10 )
 Insert   Into  zong.yyy  Values ( ' bbb ' , 20 )

 Create   Table  zong.xxx(xm  Varchar2 ( 10 ),age  Number ( 8 ));
 Insert   Into  zong.xxx  Values ( ' aaa ' , 10 )
 Insert   Into  zong.xxx  Values ( ' ccc ' , 30 )

 Select   *   From  zong.yyy  Union   Select   *   From  fei.xxx

 Select   *   From  zong.yyy  Union   All   Select   *   From  fei.xxx

 Select   *   From  zong.yyy  Intersect   Select   *   From  fei.xxx

 Select   *   From  zong.yyy Minus  Select   *   From  fei.xxx
 
  *********************************************
 **********************************************
 -- 字符串函數(shù)
    select   ascii ( ' A ' ) A, ascii ( ' a ' ) a, ascii ( ' 0 ' ) zero, ascii ( '   ' )  space   from  dual
 
   select  chr( 54740 ) zhao,chr( 65 ) chr65  from  dual
 
   select  concat( ' 010- ' , ' 88888888 ' ) || ' 連接 '  實例  from  dual
 
   select  initcap( ' smith ' ) upp  from  dual;
 
   select  instr( ' oracle traning ' , ' ra ' , 1 , 2 ) instring  from  dual
 
   select  lpad(rpad( ' gao ' , 10 , ' * ' ), 17 , ' * ' ) from  dual;
 
   select   ltrim ( rtrim ( ' gao qian jing     ' , '   ' ), '   ' )  from  dual;
 
   select  substr( ' 13088888888 ' , 3 , 8 )  from  dual;
   select   replace ( ' he love you ' , ' he ' , ' i ' )  from  dual;
 
 
  -- 數(shù)學(xué)函數(shù)
    select   floor ( 2345.67 )  from  dual;
   select  mod( 10 , 3 ),mod( 3 , 3 ),mod( 2 , 3 )  from  dual;
   select   round ( 55.5 ), round ( - 55.4 ),trunc( 55.5 ),trunc( - 55.5 )  from  dual;
   select   sign ( 123 ), sign ( - 100 ), sign ( 0 )  from  dual;
 
 
   -- 日期函數(shù)
     select  to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ), 2 ), ' yyyymm ' )  from  dual;
    select  to_char(sysdate, ' yyyy.mm.dd ' ),to_char((sysdate) + 1 , ' yyyy.mm.dd ' )  from  dual;
    select  last_day(sysdate)  from  dual;
 
    select  months_between( ' 19-12月-1999 ' , ' 19-3月-1999 ' ) mon_between  from  dual;
    select  months_between(to_date( ' 2000.05.20 ' , ' yyyy.mm.dd ' ),to_date( ' 2005.05.20 ' , ' yyyy.mm.dd ' )) mon_betw  from  dual;
  
    select  to_char(sysdate, ' yyyy.mm.dd hh34:mi:ss ' ) 北京時間,to_char(new_time
  (sysdate, ' PDT ' , ' GMT ' ), ' yyyy.mm.dd hh34:mi:ss ' ) 埃及時間  from  dual;
 
   select  next_day( ' 18-5月-2001 ' , ' 星期五 ' ) next_day  from  dual;
   //
   select   round (sysdate, ' year ' )  from  ccc;
 
   select  to_char(sysdate, ' dd-mm-yyyy day ' )  from  dual;
   select   *   from  ccc  where  birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 120 ;
   select   *   from  ccc  where  birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 30 ;
    select   *   from  ccc  where  birthday > to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' );
  
  
   select  to_char(t.d, ' YY-MM-DD ' )  from  (
 select  trunc(sysdate,  ' MM ' ) + rownum - 1   as  d
 from  dba_objects
 where  rownum  <   32 ) t
 where  to_char(t.d,  ' MM ' )  =  to_char(sysdate,  ' MM ' )  -- 找出當(dāng)前月份的周五的日期
 and  trim(to_char(t.d,  ' Day ' ))  =   ' 星期五 ' 

 -- 類型轉(zhuǎn)換函數(shù)
 select  to_char(sysdate, ' yyyy/mm/dd hh34:mi:ss ' )  from  dual;

 select  to_number( ' 1999 ' )  year   from  dual;

 -- 系統(tǒng)函數(shù)
 select  username, user_id   from  dba_users  where   user_id = uid;
 select   user   from  dual;

 -- 集合函數(shù)
   create   table  table3(xm  varchar ( 8 ),sal  number ( 7 , 2 ));
  insert   into  table3  values ( ' gao ' , 1111.11 );
  insert   into  table3  values ( ' gao ' , 1111.11 );
  insert   into  table3  values ( ' zhu ' , 5555.55 );
 
  -- select avg(distinct sal) from gao.table3;
   -- select max(distinct sal) from scott.emp;
 
 
 -- 分組函數(shù)和統(tǒng)計函數(shù) 
   select  deptno, count ( * ), sum (sal)  from  scott.emp  group   by  deptno;
  select  deptno, count ( * ), sum (sal)  from  scott.emp  group   by  deptno  having   count ( * ) >= 5 ;
  select  deptno, count ( * ), sum (sal)  from  scott.emp  having   count ( * ) >= 5   group   by  deptno ;
  select  deptno,ename,sal  from  scott.emp  order   by  deptno,sal  desc ;

 **********************************************************************************************
     CREATE   TABLE  "ZONG"."CCC"("BH"  VARCHAR2 ( 10 ), "XM"  VARCHAR2 ( 10 ), "AGE"  NUMBER , "SALARY"  NUMBER , "BIRTHDAY" DATE)

     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
 
     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 001 ' , null , 33 , 4444 ,to_date( ' 1979-1-1 ' , ' yyyy-mm-dd ' ));
  
     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
  
     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
 
     insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
 
     select  bh 編號, nvl(xm, ' ggg ' )  as  姓名  from  ccc  where  bh = ' 001 '
 
     select  bh 編號,  nvl2(xm, ' yes ' , ' no ' )  as  姓名   from  ccc
 
    -- select bh 編號,NULLIF('bbb','aaa') from ccc
   
    -- is null 的用法
   
    select   *   from  ccc   where  xm  is   null
    select   *   from  ccc   where  xm   is   NOT   null
  
    -- not in的用法
    
     select   *   from  ccc  where   birthday  between   to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' )  and   to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
     select   *   from  ccc  where   birthday  not    between   to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' )  and   to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
  
   --  in的用法
       select   *   from  ccc  where   xm  in ( ' aaa ' , ' peng ' , ' cao ' )
      select   *   from  ccc  where   xm   not   in ( ' aaa ' , ' peng ' , ' cao ' )
    
   -- like的用法和=、!=、<、>、<=、>=的用法
    
     select   *   from  ccc  where  age > 24   and  age  <= 56   and  xm  like   ' %a% '
 
 
 
  
  
      --
      create    table   sales (xm  varchar2 ( 10 ), dTime date,  count   number , totalmoney  number ,city  varchar2 ( 10 ))
   
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
    
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
   
     select  xm, sum ( count ) 數(shù)量, sum (totalmoney) 金額 ,city  from  sales  group   by  xm , count  ,totalmoney,rollup(city)  order   by  xm , count  ,totalmoney,city
   
     -- group分組語句
     select  xm, sum ( count ) 數(shù)量, sum (totalmoney) 金額 ,city  from  sales  group   by  xm , count  ,totalmoney,rollup(city)   having   count > 2000   order   by  xm , count  ,totalmoney,city
   
     -- rollup函數(shù)
      select  xm,  sum ( count ) 數(shù)量,city  from  sales  group   by  xm , count  ,rollup(city)  order   by  xm , count  ,city
   
   
   
   -- 事務(wù)級臨時表是指臨時表中的數(shù)據(jù)只在事務(wù)生命周期中存在。當(dāng)一個事務(wù)結(jié)束（commit or rollback），Oracle自動清除臨時表中數(shù)據(jù)
      CREATE  GLOBAL  TEMPORARY   TABLE  admin_work_area
        (startdate DATE,
         enddate DATE,
         class  CHAR ( 20 ))
       ON   COMMIT   DELETE  ROWS;
    create   table  permernate( a  number );
    insert   into  admin_work_area  values (sysdate,sysdate, ' temperary table ' );
  
    insert   into  permernate  values ( 1 );
    commit ;
    select   *   from  admin_work_area;
    select    *   from  permernate;

 -- 會話級臨時表是指臨時表中的數(shù)據(jù)只在會話生命周期之中存在，當(dāng)用戶退出會話結(jié)束的時候，Oracle自動清除臨時表中數(shù)據(jù)
 
      drop   table  admin_work_area;
      CREATE  GLOBAL  TEMPORARY   TABLE  admin_work_area
        (startdate DATE,
         enddate DATE,
         class  CHAR ( 20 ))
       ON   COMMIT  PRESERVE  ROWS;
    create   table  permernate( a  number );
    insert   into  admin_work_area  values (sysdate,sysdate, ' temperary table ' );
  
    insert   into  permernate  values ( 2 );
    commit ;
    select   *   from  admin_work_area;
    select    *   from  permernate;

 **********************************************************************************************
 // 鎖

  create    table   sales (xm  varchar2 ( 10 ), dTime date,  count   number , totalmoney  number ,city  varchar2 ( 10 ))
   
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
     insert   into  sales  values ( ' 張三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
    
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
      insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
     insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
   
   
   
   
     select   *   from  sales  where  xm = ' 張三 '   for   update   of   count
   
     update  sales  set   count = 30000   where  xm = ' 張三 '
   
    
     -- 另一用戶登錄
      update  ydgl.sales  set   count = 30000   where  xm = ' 張三 '
   
   
     -- 在多個用戶在同一張表中放置鎖時,其他用戶等待上一用戶的時間.
      select   *   from  sales  where  xm = ' 張三 '   for   update   of   count   wait  10 
   
   
  
   
     -- 只作查詢
     lock  table  sales  in  share  mode
   
   
     -- 能刪除,更新,插入除鎖定外的其他行
    
   lock  table  sales  in  share  update  mode
  
  
    -- 排他鎖 ,在同一時間點上,只有一個用戶在表中放置排他鎖.
     lock  table  sales  in  exclusive  mode
   
     -- 避免延遲時間,鎖在用戶之間的傳遞時間,不等待,立即提示錯誤信息
       lock  table  sales  in  exclusive  mode nowait
    **********************************************************************************************
 -- --創(chuàng)建臨時表  
  /**/ /* create temporary tablespace mydb
tempfile 'f:/mydb.ora'
size 10m; */
 
 -- --創(chuàng)建抽象數(shù)據(jù)類型
 create   or   replace  type address_ty  as  object
(street_no  number ( 3 ),
street_name  varchar2 ( 20 ),
city  varchar2 ( 20 ),
state  varchar2 ( 20 ));

 -- --查看抽象數(shù)據(jù)類型實際值
 select  attr_name,length,attr_type_name
 from  user_type_attrs
 where  type_name = ' ADDRESS_TY ' ;

 -- --創(chuàng)建應(yīng)用了抽象數(shù)據(jù)類型的表
 create   table  vend_mast
(vencode  varchar2 ( 5 ),
venname  varchar2 ( 15 ),
venadd address_ty,
tel_no  number ( 10 ));

 -- --查看表結(jié)構(gòu)
 desc  vend_mast;

 select  column_name,data_type  from  user_tab_columns  where
       table_name = ' VEND_MAST ' ;
      
 -- --插入記錄
 insert   into  vend_mast  values
( ' v100 ' , ' john ' ,address_ty( 110 , ' Clinton Rd ' ,
 ' Rosewood ' , ' Columbia ' , 234465987 );

 -- --查看記錄
 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 ' ;

 -- --強行刪除抽象數(shù)據(jù)類型
 drop  type address_ty force;

 -- --創(chuàng)建應(yīng)用了抽象數(shù)據(jù)類型的表的索引
 create   index  streetnum  on  vend_mast(venadd.street_no);

 -- --查看索引
 select  owner,index_name,index_type,table_owner,table_name,table_type
 from  all_indexes
 where  owner = ' SCOTT ' ;

 -- --創(chuàng)建不能繼承的對象
 create   or   replace  type Student_typ  as  object
(Ssn  number ,
Name  varchar2 ( 30 ),
Address  varchar2 ( 100 )) not  final;

 -- --修改是否能繼承
 alter  type Student_typ  not  final;

 create  type t  as  object
(x  number ,)
 not  instantiable member  function  func1  return   number )
 not  instantiable  not  final;

 -- --創(chuàng)建可變數(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 );

 -- --基于可變數(shù)組創(chuàng)建表
 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 , 47 , 29 , 20 ),
              qty_deld( 100 , 900 , 800 , 700 , 600 ));

 -- --查看整體
 select   *   from  order_detail
 -- --單個
 select  item_va  from  order_detail
 -- --查看可變數(shù)組內(nèi)容
 select   *   from   table (
 -- --select * from order_detail a where a.orderno='o100')
 select  a.item_va  from  order_detail a  where  a.orderno = ' o100 ' )

 -- --嵌套表
-- --創(chuàng)建抽象數(shù)據(jù)類型即對象
 create   or   replace  type ord_ty  as  object (
       itemcode  varchar2 ( 5 ),
       qty_ord  number ( 5 ),
       qty_deld  number ( 5 ));

 -- --表中包含嵌套表一定要基于一個對象創(chuàng)建一個新的對象作為嵌套表
 create   or   replace  type ord_nt  as   table   of  ord_ty;

 -- --創(chuàng)建包含嵌套表的表
 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  t.dets  from  order_master t  where  t.orderno = ' o100 ' ;

 -- --查看嵌套表中的信息 
 select   *   from   table ( select  t.dets  from  order_master t
 where  t.orderno = ' o100 ' );

 -- --修改
 update   table ( select  t.dets  from  order_master t
                     where  t.orderno = ' o100 ' ) t
                            set  value(t) = ord_ty( ' i103 ' , 50 , 45 )
                                where  t.itemcode = ' i103 ' ;

 -- --刪除嵌套表的值
 delete   from   table ( select  t.dets  from  order_master t
        where  t.orderno = ' o100 ' ) t
        where  t.itemcode = ' i102 ' ;
      
 -- --把嵌套表中已存在的記錄添加到創(chuàng)建的表中
 insert   into  order_master  values ( ' o202 ' ,to_date( ' 2003-3-5 ' , ' YY-MM-DD ' ),
 ' v101 ' , cast (multiset( select   *   from   table ( select  dets  from  order_master
 where  orderno = ' o201 ' ))  as  ord_nt));
      
 -- --創(chuàng)建對象
 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 ));

 drop   table  vend_master;

 -- --創(chuàng)建對象表,對象中不能定義約束，在對象表中可通過關(guān)鍵字constraint定義
 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 ));

 -- --查看地址（表中所分配的OID）
 select  ref(a)  from  vend_master a;

 -- --創(chuàng)建一個指向抽象數(shù)據(jù)類型的表
 create   table  ord_master(
orderno  varchar2 ( 5 ),
vendet ref vend_ty); -- --數(shù)據(jù)類型為指向抽象數(shù)據(jù)類型的類型
 
 -- --類似將查詢記錄插入一個表的語法插入記錄
 insert   into  ord_master( select ' o301 ' ,ref(a) from  vend_master a
 where  vencode = ' v201 ' );

 -- --查看所有記錄
 select   *   from  ord_master;

 select  deref(a.vendet)  from  ord_master a;

 delete   from  vend_master  where  vencode = ' v201 ' ;

 -- --對象視圖
 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 -- --表名of類型名with object oid
 (itemcode)  as
 select   *   from  item  where  item_on_hand < 20 ;

 insert   into  item  values ( ' i201 ' , 10 , 5 );
 -- --插入值調(diào)用函數(shù)
 insert   into  item_view  values (item_type( ' i102 ' , 15 , 50 ));

 create   view  nt_view  of  ord_ty  with  object oid(itemcode)
 as   select   *   from   table ( select  d.dets
 from  order_master d  where  d.orderno = ' o201 ' );

 create   table  itemfile(
        itemcode  varchar2 ( 5 )  primary   key ,
        itemdesc  varchar2 ( 20 ),
        p_category  varchar2 ( 20 ),
        qty_hand  number ( 5 ),
        re_level  number ( 5 ),
        max_level  number ( 5 ),
        itemrate  number ( 9 , 2 ));

 create   table  order_detail (
        orderno  varchar2 ( 5 ),
        itemcode  varchar2 ( 5 ),
        qty_ord  number ( 5 ),
        qty_deld  number ( 5 ), primary   key (orderno,qty_ord,qty_deld),
         foreign   key (itemcode)  references  itemfile(itemcode));

 create   or   replace  type itemfile_ty  as  object
        (itemcode  varchar2 ( 5 ),
        itemdesc  varchar2 ( 20 ),
        p_category  varchar2 ( 20 ),
        qty_hand  number ( 5 ),
        re_level  number ( 5 ),max_level  number ( 5 ),
        itemrate  number ( 9 , 12 ));

 create   view  itemfile_ov  of  itemfile_ty
        with  object oid(itemcode)
        as   select   *   from  itemfile;

 select  make_ref(itemfile_ov,itemcode)  from  itemfile;

 create   view  order_detail_ov
 as 
        select  make_ref(itemfile_ov,itemcode) items,orderno,qty_ord,qty_deld
               from  order_detail;

 -- --不能正確運行
 select  deref(a.items)  from  order_detail_ov a;

 **********************************************************************************************
 
 
 
 -- --創(chuàng)建抽象數(shù)據(jù)類型
 create   or   replace  type add_ty  as  object(
Street  varchar2 ( 25 ),
City  varchar2 ( 15 ),
State  varchar2 ( 10 ),
Zip  number );

 -- --基于抽象數(shù)據(jù)類型創(chuàng)建表
 create   table  customer(
Customer_id  number ( 4 ),
person add_ty);

 -- --插入記錄
 insert   into  customer  values (
 1001 ,add_ty( ' No.2 downhill st. ' , ' Los Angles ' , ' California ' , 700023 ));

 insert   into  customer  values (
 1002 ,add_ty( ' No.120 stepahead rd. ' , ' houston ' , ' texas ' , 701024 ));

 -- --查詢記錄
 select  customer_id,c.person.city  from  customer c
 where  c.person.state = ' texas ' ;

 -- --刪除記錄
 delete   from  customer a
 where  a.person.zip = 701024 ;

 -- --創(chuàng)建可變數(shù)組
 create  type Phone  as  varray( 2 )  of   Number ( 8 );

 -- --使用可變數(shù)組創(chuàng)建表
 create   table  Employee(
Eno  number ( 4 ),
name  varchar2 ( 15 ),
phone phone);

 -- --插入數(shù)據(jù)
 insert   into  Employee  values (
 1000 , ' George ' ,Phone( 67343344 , 3432342 ));

 delete   from  Employee  where  name = ' gxj ' ;

 select   *   from  employee;

 select  phone  from  employee;

 -- --創(chuàng)建對象
 create  type person_details  as  object(
name  varchar2 ( 15 ),
age  number ( 2 ),
desg  varchar2 ( 15 ));

 create  type person_detail_table_ty  as   table   of  person_details;

 create   table  other_info_person(
dept_name  varchar2 ( 10 ),
dept_no  number ( 3 ),
person_info person_detail_table_ty)
nested  table  person_info store  as  person_store_table;

 -- --創(chuàng)建抽象數(shù)據(jù)類型
 create   or   replace  type Dept_type  as  object(
Deptno  number ( 2 ),
Dname  varchar2 ( 14 ),
Loc  varchar2 ( 13 ));

 -- --創(chuàng)建表
 create   table  Student(
Name  varchar2 ( 15 ),
Dept_detail Dept_type);

 -- --插入數(shù)據(jù)
 insert   into  Student  values (
 ' Jessica ' ,Dept_type( 20 , ' Computer ' , ' Chicago ' ));

 insert   into  Student  values (
 ' Peter ' ,Dept_type( 40 , ' Electronics ' , ' California ' ));

 -- --查詢數(shù)據(jù)
 select   *   from  Student;

 select  name, a.dept_detail.Deptno  from  Student a
 where  a.Dept_detail.Loc = ' Chicago ' ;

 insert   into  Employee  values (
 1002 , ' Dick ' ,Phone( 33444876 , 87876565 ));
 insert   into  Employee  values (
 1003 , ' Jones ' ,Phone( 54576545 , 52457779 ));
   plsql
--例二：創(chuàng)建具有LOB數(shù)據(jù)類型的表
CREATE TABLE vendor_master
 (vencode    varchar2(5),
  venname    varchar2(15),
  venadd1    varchar2(20),
  venadd2    varchar2(20),
  venadd3    varchar2(20),
  tel_no     number(6),
  msg        CLOB);
 
--例三：初始化LOB值
INSERT INTO vendor_master VALUES
 ('v201','aryay','10','first st','mds',475859,
  '這是我們的初始化LOB值');
 
select * from vendor_master;

--例9：條件控制
select * from rs_employees
  where hetongid='WL-090001';

DECLARE
  v_department   rs_employees.department%type;
BEGIN
  SELECT department INTO v_department
    FROM rs_employees
    WHERE HeTongId='WL-090001';
   
  IF v_department = '車間工人' THEN
    UPDATE rs_employees
      SET department='不是工人'
      WHERE HeTongId='WL-090001';
  ELSE
    UPDATE rs_employees
      SET department='車間工人'
      WHERE HeTongId='WL-090001';
  END IF;
END;
/

--CASE語句示例（下面的寫法有錯）
SET SERVEROUT ON;
DECLARE
  I number:=2;
BEGIN
  CASE
    WHEN (I=1) THEN dbms_output.put_line('Result is 1');
    WHEN (I=2) THEN dbms_output.put_line('Result is 2');
  END CASE;
END;

--和上面的區(qū)別是什么
SET SERVEROUT ON;
DECLARE
  I number:=2;
BEGIN
  CASE I(用于選擇器)
    WHEN 1 THEN dbms_output.put_line('Result is 1');
    WHEN 2 THEN dbms_output.put_line('Result is 2');
  END CASE;
END;
/

--例11：簡單循環(huán)：在Test Window中執(zhí)行
--SET SERVEROUT ON;
DECLARE
  a    NUMBER := 100;
BEGIN
  LOOP
    a := a+25;
    EXIT WHEN A=250;
  END LOOP;
  dbms_output.put_line(TO_CHAR(a));
END;

--例12：While循環(huán)，此值書上有錯
DECLARE
  i    NUMBER :=0;
  J    NUMBER :=0;
BEGIN
 while i<=100 Loop
   J := J+1;
   i := i+2;
 end loop;
 dbms_output.put_line('j的值是'||j);
END;
 
--例13：FOR循環(huán)，結(jié)果是5050
DECLARE
  i  number :=0;
  j  number :=0;
BEGIN
  for i in 1..100
  loop
    j := j + 1;
  end loop;
  dbms_output.put_line('j的值是'||j);
END;

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

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進(jìn)一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。