Oracle-PS\SQL基本语法

0.导入数据

$imp system/test tables=(xs,kc,xs_kc) file=C:\xskc.dmp
$imp system/test tables=(part_book1,lend) file=C:\xs_lend_part_book1.dmp

1.打开输出

SET  SERVEROUTPUT ON

2. PS\SQL基本语法

DECLARE
      V_xm VARCHAR2(8):='Jame';
      V_zym VARCHAR2(10):='计算机';
      V_zxf number(2):=45;
    BEGIN
      UPDATE XS SET ZXF = V_zxf WHERE XM=V_XM;
      IF SQL%NOTFOUND THEN 
       DBMS_OUTPUT.PUT_LINE('没有该人，请插入记录: ');
       INSERT INTO XS(XH,XM,ZYM,ZXF) VALUES('007',V_xm,V_zym,V_zxf);
       END IF;
    END;

3.为表增加一个分区

    ALTER TABLE part_book1
    ADD PARTITION part3 VALUES(default)  tablespace system;

4.分割分区表

    ALTER TABLE PART_BOOK1 
     SPLIT PARTITION part3 AT (TO_DATE('20140101',YYYYMMDD))
     INTO (PARTITION PART3,PARTITION PART4);

5.查询分区表

     SELECT * FROM dba_tab_partitions where table_name='PART_BOOK1';

6. PS\SQL基本语法

     DECLARE
            row_id ROWID;
             info VARCHAR2(100);
   BEGIN
     UPDATE SCOTT.DEPT SET DEPTNO=90 WHERE DNAME='RESEARCH'
     RETURNING ROWID,dname || '  :  '||  TO_CHAR(deptno)||'  :  '||loc INTO row_id,info;
     DBMS_OUTPUT.PUT_LINE('ROWID ----'||row_id);
     DBMS_OUTPUT.PUT_LINE(info);
     END;

7.删除表EMP的外键

  ALTER TABLE SCOTT.EMP DROP CONSTRAINT FK_DEPTNO;   

8.&临时变量

     SELECT &X+1 FROM DUAL;

9.&&永久变量

     SELECT &&X+1 FROM DUAL;