SQL入门到精通

1.help index     help set(查看set的可选项)    help column                user_tables视图很重要
2.set sqlblanklines on
3.select * from dept where deptno=20;
   select * from dept where deptno=&t;
4.list 或 l 看输入语句信息
5.c/fron/from
6.del 2 3 4
7.save d:\backup\test.txt                 @d:\backup\test.txt            get d:\backup\test.txt
8.edit                      (编辑上一条的命令)  还有  ED   (系统的默认编辑器)
9.col deptno HEADING "编号";                 col bytes format 999,999,999
10.desc dept;          (查看一个表的结构)
11.break on publish;           (相同出版社的进行分类）comp count label  "计数" of  books_name  ON publish;
12.把执行命令和结果 一起保存到TXT文件中  spool d:\backup\spool.txt     select * from books; spool off;
spool /usr/app/plsq_test.prn    select * from books; spool off;
                                                                            ORACLE数据类型
1.数值类型
number:没有规定精度或范围的数值就是number(38)
number(9,2):9个数字长度，小数点左边最多7个，右边最多两个
number(3):3位数的整数
2.字符类型
char  ,nchar,varchar2,nvarchar2,raw,long,long raw，大对象。
2.1大对象
  lob：每种类型的LOB都可以存储4GB的数据。在ORACLE中可以使用dbms_lob程序包来处理lob对象。
2.2  lob类型
 LOB包括：blob,clob,nclob,bfile等类型。BLOB是二进制对象，用于存储图像，音频，视频等数据。

 

                                                                            SQL（structured query language）语言基础
一.
1.数据定义语言DDL
   CREATE   ALTER    DROP
    create table abc (a varchar2(10),b char(10));
    alter table abc add c number;
    drop table abc;
   drop user scan;
    alter table abc drop column c;
    create table ttt as (select * from e);
    alter user scan DEFAULT TABLESPACE SCAN;
    alter user scan identified by scan;
    alter user scan ACCOUNT LOCK;    (锁定帐号)
使用替换变量插入数据：
  insert into d values(&id,&name);

1)
set VERIFY OFF
select product_name from tablename where quantity>=&minimum_quantity_sold;
set VERIFY ON

set VERIFY OFF
select product_name from tablename where purchase_date='&date_you_want_to_select';
set VERIFY ON
2)
ACCEPT 命令
ACCEPT variable_name PROMPT 'prompt_text'

set verify off
set echo  off
accept v_earliest_date prompt 'earliest date ?(dd-mm-yy): '
accept v_latest_date prompt 'thank you latest date ? (dd-mm-yy): '
select product_name,quantity from tablename where purchase_date between '&v_earliest_date' and '&v_latest_date'
order by quantity asc;
set verify on
set echo on
2.数据控制 语言DCL
   GRANT REVOKE
   grant connect ,resource to scan;
      grant select on abc to scan;
     revoke grant on abc from scan;                 (撤消权限)
    GRANT ALL ON e TO scan WITH GRANT OPTION;
    grant execute on  e  TO scan;
    grant create user to scan;
3.数据操作语言
   SELECT   INSERT  DELETE   UPDATE
    insert into abc(a,b) values ('cde','www');
    update abc set b='ttt';            update abc set b='www' where a='abc';
    delete from abc where a='abc';
    delete from abc;
二.常用系统函数
单行函数
1）系统变量
sysdate systimestamp current_date  current_timestamp user userenv
select userenv('terminal') from dual;(显示计算机的名称)
2）数字函数
round :将数字四舍五入到指定的精度。 round(input_value,decimal_places_of_pricision)
round(1234.5678,3)=1234.568    round(1234.5678,0)=1235   round(1234.5678,-1)=1230
trunc:截取数字的精确度
trunc(1234.5678,3)=1234.567
3)文本函数
upper(全大写)  lower（全小写）   initcap（开始字母大写）
length(数据长度)      substr(source_text,starting_character_position,number_of_characters)
instr(source_text,text_to_locate,starting_character_position)  can. small   '.'   4
select item_desc
          substr(item_desc,1,instr(item_desc,'.',1)-1) CATEGORY
          substr(item_desc,instr(item_desc,'.',1)+2,99) ITEM_SIZE
FROM tablename;
ltrim(去掉左边的空格)  rtrim    trim
日期处理：
 trunc:去掉日期中的时间部分
add_months:返回一个月份不同但天数相同的日期。add_months('starting_date',number_of_months)
last_day('date')         select last_day(sysdate)+1 from dual;
months_between(later_date,earlier_date)                    select round(months_between(sysdate,last_stock_date)) from tablename;
数据类型转换
to_char(input_value,'format_code')
select to_char(sysdate,'mm-dd-yyyy  hh24:mi:ss') now from dual;
to_date(input_value,'format_code')
select to_date(sysdate,'mm-dd-yyyy ') now from dual;
其它函数
decode
decode(incoming_source,
            incoming_value_1,outgoing_result_1,
            incoming_value_2,outgoing_result_2,
            .......
            default_outgoing_result_if_no_match
            )
nvl
nvl(input_value,result_if_input_value_is_null)

1.字符
    length,ltrim,replace,rtrim,substr,trim
   select length('abcde') from dual;            select lengthb('avc楞') from dual;(字节)
    select trim(' abcde ') from dual;(去掉空字符)  
    select substr('abcdefg',3,3) from dual; select substr('abcdefg' ,length('abcdefg')-3+1,3) from dual;
  2.日期
       select sysdate from dual;
select * from tablename where purchase_date between (sysdate-30) and sysdate;
       select current_date from dual;
       alter session set nls_date_format='dd- mon-yyyy hh:mi:ss';
       select next_day(sysdate,'星期二' )from dual;
3.转换
       select to_char(sysdate,'yyyy-mm-dd   hh24:mi:ss') from dual;
       select to_date('12-3月-04') from dual;
        select to_number ('333') from dual;
4.聚集函数（不能做为条件放在where语句的后面）
          sum ,avg, max , min, count
           select sum(c) from abc;
           select count(*) from abc;
           select count(c) from abc;
5.其它
    decode, nvl, null, order
    select user from dual;
    select sum(decode(sex,'男',1,0)) 男人数,sum(decode(sex,'女',1,0)) 女人数 from e;
    select a1,nvl(a2,'未输入') a2 from aa;
    select * from aa where a1 is null;    select *  from aa where a2 is not null;
    select * from aa order by a1 desc;
    select * from aa order by a1 asc;
    select  distinct a1 from aa;
6.生成随机数
SELECT DBMS_RANDOM.VALUE(下限﹐上限) FROM DUAL;
随机整数
select ceil(dbms_random.value(0,3)) from dual;
 select trunc(dbms_random.value(0,4))   from   dual
问题解决：修改表后怎么让其保存         解决办法：commit
3.分组语句
        select publish ,sum(price*qty) from books group by publish;     (publish与by **** 相匹配 )
        select publish ,sum(price*qty) from books group by publish having         sum(price)>200;
4.模糊查询(即使用通配符)
          select * from abc where a like 'a%';  （%g代表任意个字符）
          select * from abc where a like '%a%';
5.表的链接
        create table e(EID varchar(10),ENAME varchar(10),SEX varchar(10), ID varchar(10))
        insert into e  values ('001' ,'赵1','男','01');
   
       create table d(ID varchar(10) ,NAME varchar(10));

      select  EID 编号,ENAME 姓名,SEX 性别,e.ID 所在部门 from e,d where e.id=d.id;
      select  EID 编号,ENAME 姓名,SEX 性别,d.name 所在部门 from e,d where e.id=d.id;
    内连接：两个表的相匹配
    select  EID 编号,ENAME 姓名,SEX 性别,d.name 所在部门 from e join d on e.id=d.id;
   外连接：不完全匹配。
   左连接：以 左边的表为主：select  EID 编号,ENAME 姓名,SEX 性别,d.name 所在部门 from e,d where e.id=d.id(+);
   右连接：以 右边的表为主：select  EID 编号,ENAME 姓名,SEX 性别,d.name 所在部门 from e,d where e.id(+)=d.id;
6.子查询
          select * from e where id=(select id from d where id=e.id and id='03');
          select * from e where id in(select id from d  where id=e.id and id='03');                     相关
          select * from e where id not  in(select id from d  where id=e.id and id='03');              不相关
          select * from e where  exists(select id from d where id=e.id );                                      相关
          select * from e where  not exists(select *from d where id=e.id );                                 不相关
       
         select id  from e
         intersect
         select id  from d;       (把两个匹配的显示出来，oracle专有)

        create table ttt as (select * from e);                  (用另一个表来创建一个表)
        create table table_name1 as select * from table_name2;      (用另一个表来创建一个表)
        create talbe ttt as select eid,name from e where eid='001';
        insert into e (eid,ename)  select id,name from d;
多行子查询：
select * from table_name_1 where product_name not in(select distinct product_name from table_name_2) order by procuct_name;
create table table_name1 (purchase_date date, product_name varchar2(25),....);
改变表名
rename old_table_name to new_table_name;
改变表结构：
alter table table_name add new_column_name datetype not null;
alter table table_name add data_load_date varchar2(8);
改变列名
alter table table_name rename column old_column_name to new_column_name;
删除列
alter table table_name drop column column_name;
改变列中的数据类型
alter table table_name modify column_name new_datatype;
改变NULL选项
alter table table_name modify column_name not null;

7.集合运算符
      UNION（两个SELECT 语句中的所有行，删除重复值）,UNION ALL（显示重复值）,
       INTERSECT(求交集),MINUS（第一个SELECT语句返回行减去第二个SELECT语句返回行）
select * from tablename
MINUS
select * from tablename where product_price<0 or product_price>100;

8.格式化查询结果
COLUMN:     column ename heading '雇员姓名' format A15
                       column sal heading '薪水' justify center format $99,999,00
TITLE ,BTITLE:         ttitle '我的帐单'                 btitle '-------------bean公司-----------'
PAUSE:         set pause on;    set pause off;
PAGESIZE:     set pagesize 14;                (增加每一页显示的数据量)
LINESIZE:  show linesize;           set linesize 160;
FEEDBACK:       set feedback on ;
NUMFORMAT:   set numformat 99999999999999999999999,9999999999
LONG:     set long 10000    (为了显示列中的更多数据)
PROMPT:     prompt 显示用户和表空间的清单
&:                 select ename from e wher sal>&amount;         &&:  输入变量一次
9.如何 插入包含撇号的数据
set scan off
insert into table_name values ('fifth product''s name',25,'05-may-06');
set scan on
10.将两个或更多的文本段结合在一起
select product_name || 'was old by' || salesperson from tablesname;
11.查询
select * from tablename where produce_price>50 and product_price<1000;
select * from tablename where produce_price between 50 and 1000;
select * from tablename where produce_price <> 90;(选择不等于90的行)
select * from tablename where produce_price != 90;(选择不等于90的行)
select * from tablename where color in ('red' ,  'green' , 'white');(进行一个组（包含这几个颜色的行）选择)
select * from tablename where last_stock_date='10-JAN-06';
select * from tablename where last_stock_date >'10-JAN-06';
select * from tablename where last_stock_date BETWEEN '01-JAN-06' AND '31-DEC-06';
select * from tablename  order by product_price ,product_name;
select * from tablename  order by product_price DESC;
select distinct produc_name from tablename order by product_name;

12.格式化SQL*PLUS 中的数字
1）对齐小数位
COLUMN procduct_price FORMAT  999.99
2）将数字安每三位一节插入分组符
COLUMN quanlity_on_hand FORMAT 99,999
3）插入货币符号
COLUMN product_price FORMAT $99.99

 

         
                                                                                   三.PL/SQL基础
参数之后的IN表示在调用该过程时可以读取该参数中的输入值。OUT表示过程可以使用该参数将某个值传递回调用它的过程。
过程中的数据类型不可以包含尺寸规范。如不可以NUMBER(10,2).
以下语法声明某个列类型的PL/SQL变量（单行单 列）：variable_name table_name.column_name%TYPE;
以下语法声明某个记录的字段与某个表中的行相同：record_name table_name%ROWTYPE;
以下语法声明某个记录的字段与游标中的列相同：record_name cursor_name%ROWTYPE;(???是列还是行呀？？？？？)
单行单列：v_name employee.employeename%TYPE
DECLARE
              v_name employee.employeename%TYPE
单行多列：Dept_record department%ROWTYPE
 
单列多行：TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;
                     identifier type_name;
DECLARE
   TYPE ename_table_type IS TABLE OF employee.employeename%TYPE
    INDEX BY BINARY_INTEGER;
   ename_table   ename_table_type;
BEGIN
   SELECT employeename INTO ename_table(-1) FROM employee
   WHERE employeeno='128';
   dbms_output.put_line('员工名：' ||ename_table(-1));
END;
/

 1.块结构
           head              命名块才有的如函数或过程
           declare           对变量，过程，子程序声明
           execute           执行处理
           exception         捕获和处理异常
                    DECLARE   ....            
                    BEGIN           ....
                    EXCEPTION     ...
                    END;
                    /

EXCEPTION
WHEN exception_name
THEN  actions to take

   2.变量声明的内容
       。赋予变量适当的名称
       。赋予变量正确的数据类型
       。定义变量（变量，记录）
       。控制变量范围
   3.命名规则
        。 变量由字符开头
        。 可以包含：数字，下划线，'$', '#'等
        。变量长度范围：1---30
        。大小写不区分
        。变量名不能是系统关键字

    declare   1_number_constant  constant number:=60;             (常量的赋值)
    begin
     null;
    end;
    /
     
     DECLARE
     x varchar2(10);
     BEGIN
     x:='This is ..';
     DBMS_OUTPUT.PUT_LINE('x的值为：' ||x) ;
     END;
     /
4. 分支语句
    4.1  IF分支
         IF...THEN
         ELSIF ....THEN
         ELSE ...
         END IF

   例子：
DECLARE
a number;
b varchar2(10);
BEGIN
a:=2;
IF a=1 THEN
b:='A';
ELSIF a=2 THEN           --注意ELSIF的写法
b:='B';
ELSE
b:='C';
END IF;
DBMS_OUTPUT.PUT_LINE('b的值为:' ||b);
END;
/
       
      4.2 CASE分支
         CASE
         WHEN ...THEN...
          WHEN...THEN...
         ELSE
         END CASE

DECLARE
a number;
b varchar2(10);
BEGIN
a:=4;
CASE
WHEN a=1  THEN b:='A';
WHEN a=2  THEN b:='B';
WHEN a=3  THEN b:='C';
ELSE
b:='others';
END CASE;
DBMS_OUTPUT.PUT_LINE('b的值为:' ||b);
END;
/
5.循环语句
   5.1基本循环（LOOP)
      LOOP
      ..........
     END LOOP
    
DECLARE                                                                                                   
x number;                                                                    
BEGIN
x:=0;
LOOP
X:=X+1;
IF X>=3 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('内: x=' ||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外: x=' || x);
END;
/

DECLARE
x number;
BEGIN
x:=0;
LOOP
X:=X+1;
EXIT WHEN X>=3;
DBMS_OUTPUT.PUT_LINE('内: x=' ||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外: x=' || x);
END;
/
      

 

    5.2WHILE循环
        WHILE expression LOOP
         .........
         END LOOP;

DECLARE
X number;
BEGIN
X:=0;
WHILE X<=3 LOOP
x:=x+1;
DBMS_OUTPUT.PUT_LINE('X1=' ||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('X2=' || X);
END;
/
    5.3 FOR循环
         FOR counter IN[REVERSE] start_value..end_value LOOP
          ........
          END LOOP;

            BEGIN
            FOR i IN 1..5 LOOP                                                                 (REVERSE)                                                      
            DBMS_OUTPUT.PUT_LINE('i=' ||i);
             END LOOP;
            DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');
            END;
           /

6.异常处理
    常见系统异常：DUP_VAL_ON_INDEX:向有唯一约束的表中插入重复行
                                NO_DATA_FOUND:在一个SELECT INTO语句中无返回值
                                TOO_MANY_ROWS:SELECT INTO 语句返回了多行
                                 VALUE_ERROY:一个算法、转换、截断或大小约束发生错误
                                 ZERO_DIVIDE:发生被零除

  6.1异常结构
DECLARE
test varchar2(10);
BEGIN
SELECT a INTO test from abc where a='tt';
DBMS_OUTPUT.PUT_LINE(test);
END;
/
 

7.复合变量：记录
    7.1记录声明
       TYPE type_name IS RECORD(
        Variable_name datatype[,
        Variable_name datatype[,
        ......
         );
        Real_name type_name;   

   DECLARE
   TYPE myrecord IS RECORD(
   id varchar2(10),
   name varchar2(10));
   real_record myrecord;
   BEGIN
   SELECT eid,ename INTO real_record FROM e where eid='001';
   DBMS_OUTPUT.PUT_LINE(real_record.id || ',' || real_record.name);
   END;
   /     

 

DECLARE
myrec e%ROWTYPE;
BEGIN
SELECT * INTO myrec FROM e WHERE eid='001';
DBMS_OUTPUT.PUT_LINE(myrec.eid|| ',' || myrec.ename||','|| myrec.sex);
END;
/

%TYPE:用户声明单独的变量                                   %ROWTYPE：声明完整行的记录变量

                                             
                                                                                        PL/SQL高级应用
 
1.游标：对SQL语句进行显式控制，便于对表的行数据逐条进行处理        （打开游标，从游标中获取记录，关闭游标）
    1.1游标的属性:
cursor_name%FOUND     检查从游标中获取记录的最后一次尝试是否成功。成功则返回TRUE
cursor_name%ISOPEN     检查游标cursor_name是否打开,打开则返回TRUE  
cursor_name%NOTFOUND  与FOND相反
cursor_name%ROWCOUNT    游标的SELECT语句返回的行数。
1.2PL/SQL记录
1）基于表    这种记录的列名称与类型相匹配。 因此游标可以选择整个行。
2）基于游标 
3）用户自定义

1.3游标FOR循环
FOR cursor_record IN cursor_name LOOP
  statements
END LOOP;

1.4WHERE CURRENT OF
当打开游标进行更新或删除所选择的行时，可以使用
WHERE CURRENT OF cursor_name
DECLARE
     CURSOR product_cur IS
     SELECT * FROM plsql_product
     FOR UPDATE OF procuct_price;            ???????????????
BEGIN
    FOR product_rec IN product_cur
    LOOP
          UPDATE plsql_product
          SET product_price = (product_rec.product_price * 0.97)
          WHERE CURRENT OF product_cur;
    END LOOP;
END;
/

      DECLARE
      CURSOR  mycur IS
      SELECT * FROM  e;
      myrecord  e%ROWTYPE;
      BEGIN
      OPEN mycur;
      FETCH mycur INTO myrecord;
      WHILE mycur%FOUND  LOOP
       DBMS_OUTPUT.PUT_LINE(myrecord.eid ||',' || myrecord.ename);          
       FETCH mycur INTO  myrecord;
        END LOOP;
        CLOSE mycur;
        END;
          /
           为什么取不出来数据？？？？？？？？？？？？？？？？？？？？？:因为没有设置显示 应该：set serveroutput on

DECLARE
CURSOR  cur_para(idd varchar2)  IS
SELECT  ename  FROM e  WHERE idd=eid;
t_name    e.ename%TYPE;
BEGIN
OPEN cur_para('001');
LOOP
FETCH cur_para INTO t_name;
EXIT WHEN cur_para%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(t_name);
END LOOP;
CLOSE cur_para;
END;
/
     
DECLARE
CURSOR cur_para(idd varchar2) IS
SELECT ename FROM e WHERE eid=idd;
BEGIN
FOR cur IN cur_para('001') LOOP
DBMS_OUTPUT.PUT_LINE(cur.ename);
END LOOP;
END;
/

DECLARE
t_name  e.ename%TYPE;
CURSOR cur(idd varchar2) IS
SELECT ename FROM e WHERE idd=eid;
BEGIN
IF cur%ISOPEN  THEN
DBMS_OUTPUT.PUT_LINE('游标打开了');
ELSE
OPEN cur('003');
END IF;
FETCH cur INTO t_name;
close cur;
DBMS_OUTPUT.PUT_LINE(t_name);
END;
/
          
DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT ename FROM e;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('游标MYCUR 的ROWCOUNT是： ' || mycur%ROWCOUNT);
END LOOP;
CLOSE mycur;
END;
/

DECLARE
     CURSOR emp_cursor IS SELECT employeename,sal FROM employee
     WHERE departmentno='008';
     emp_record emp_cursor%ROWTYPE;--字义游标的类型的记录变量
BEGIN
     IF NOT emp_cursor % ISOPEN THEN
        OPEN  emp_cursor;
      END IF;
LOOP
      FETCH emp_cursor INTO emp_record;
      EXIT WHEN emp_cursor % NOTFOUND;
      dbms_output.put_line(................);
END LOOP;
      CLOSE emp_cursor;
END;
/

DECLARE
      CURSOR emp_cursor IS SELECT employeename FROM employee
       WHERE departmentno = '008';
BEGIN
     FOR  emp_record IN emp_cursor LOOP
                dbms_output.put_line('第' || emp_cursor %rowcount || '个员工：' || emp_record.employeename);
       END LOOP;
END;
/

 

 

2.存储过程
  2.1创建语法
    过程的创建与执行
CREATE OR REPLACE PROCEDURE myproc(idd IN varchar2)
IS
name varchar2(10);
BEGIN
SELECT ename INTO name FROM e where eid=idd;
DBMS_OUTPUT.PUT_LINE(name);
END  myproc;
/
执行方法：
（1）DECLARE
tid varchar2(10);
BEGIN
tid:='001';
myproc(tid);
END;
/ 

（2）BEGIN
myproc('001');
END;
/
（3）
EXECUTE myproc('001');            注意：是输入参数或常量才可以
（4）
CREATE OR REPLACE PROCEDURE
myproc2(tid varchar2,tname out varchar2)
IS
BEGIN
SELECT  ename INTO tname FROM e WHERE eid=tid;
END;
/
DECLARE
id1 varchar2(10);
name1 varchar2(10);
BEGIN
id1:='002';
myproc2 (id1,name1);
END;
/

建立一个输出员工名和新工资的过程，输入参数为员工号
CREATE OR REPLACE PROCEDURE query_sal
    (eno VARCHAR2,name OUT VARCHAR2,salary IN OUT NUMBER)
IS
v_sal NUMBER;
BEGIN
    SELECT employeename,sal INTO name,v_sal
    FROM employee WHERE employeeno = eno;
    salary :=salary+v_sal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
         raise_application_error(-20000,'该员工不存在');
END query_sal;
/
过程建立后，通过定义变量接收输出参数.
SQL>var name VARCHAR2(20)
SQL>var salary NUMBER
SQL>exec:salary:=100
SQL>exec query_sal('003',: name, : salary)
SQL>print name salary

                                                                                       DML触发器
指定触发时机（BEFORE 或 AFTER）、触发事件（INSERT,UPDATE,DELETE）、表名、触发类型、触发条件、以及触发操作。

CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE ON employee
DECLARE
--可以在这里声明私有变量
BEGIN
   IF to_char (SYSDATE，'DY','nls_date_language = AMERICAN') in ('SAT','SUN') THEN
            raise_application_error(-20001,'不能在休息日改变雇员信息');
   END IF;
END tr_sec_emp;

CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR UPDATE OR DELETE ON employee
BEGIN
     IF to_char(sysdate,'HH24') NOT BETWEEN '09' AND '17' THEN
          raise_application_error(-20001,'非工作时间');
    END IF;
END tr_emp_time;

CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal on employee
FOR EACH ROW
WHEN (new.sal 1.2*old.sal)
BEGIN
          raise_application_error(-20001,'工资只升不降，且幅度不超过20%');
END tr_check_sal;

定义登录和退出数据库的触发器
CONN sys/password AS SYSDBA
CREATE TABLE log_table(
      username varchar(20), --记录用户名
      logon_time  date, --记录用户登录时间
      logoff_time date, --记录用户退出时间
      address varchar2(20)) --用户登录的IP地址
/
CREATE OR REPLACE TRIGGER tr_logon
AFTER logon ON DATABASE
BEGIN
      INSERTE INTO log_table(username,logon_time,address)
      VALUES(ora_login_user,sysdate,ora_client_ip_address);
END tr_logon;
/*ora_login_user    返回登录用户的事件属性函数
ora_client_ip_address   事件属性函数*/
/
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE logoff on DATABASE
BEGIN
    INSERT INTO log_table(username,logoff_time,address)
    VALUES(ora_login_user,sysdate,ora_client_ip_address);
END tr_logoff;
/

禁止触发器：ALTER TRIGGER tr_check_sal DISABLE;
激活触发器：ALTER TRIGGER tr_check_sal ENABLE;
禁止或激活表的所有触发器：ALTER TABLE table employee DISABLE ALL TRIGGERS;
                                                    ALTER TABLE table employee ENABLE ALL TRIGGERS;
重新编译触发器：ALTER TRIGGER tr_check_sal COMPLE;
删除触发器：DROP TRIGGER tr_check_sal;

 

显示错误：SHOW ERRORS PROCEDURE myproc;
                    SHOW ERRORS  PACKAGE  ***;
                     SHOW ERRORS  FUNCTION ***;
2.3使用参数

                                                                       PL/SQL数据类型
pls_integer和binary_integer是PL/SQL专用的数据类型
补充：
1.查看其编译级别：show parameter plsql_optimize_level;
2.show all 显示所有的sql*plus 设置
3.删除一个过程、函数：drop procedure procedure_name;   drop function function_name;
4.绑定变量并显示：begin          :x:=4;   end; /             print x;
5.养成一个良好的习惯，写完一个对象后，用show error 进行一下检查。

 

 

                                                                 视图、同义词、序列

1.视图实际上是一条查询语句，占用内存空间
      1.1创建一个视图：就是为了方便查询某些特定的元素而编写的SELECT 语句（可以增强安全性，防止用户看到不该看到的东西）
CREATE OR REPLACE VIEW myview
AS
SELECT * FROM e;

如：SELECT eid,ename,sex,d.name,d.id FROM e,d WHERE d.id=e.id;
   CREATE OR REPLACE VIEW v_e_d
 AS
SELECT   eid,ename,sex,d.name,d.id FROM e,d WHERE d.id=e.id;

INSERT INTO v_e_d VALUES('006','GGG','NV','D','04');
CREATE OR REPLACE VIEW v_read
AS
SELECT eid,ename FROM e
WITH READ ONLY;

DESC v_read;
删除视图
drop view view_name;
2.同义词的使用
 select user from dual;
  2.1创建同一词
create [public] synonym synonym_name for object_name;
 create synonym e for sys.e;
 select * from e;                                select * from sys.e;
 drop synonym e;
3、创建序列
create sequence sequence_name;
create sequence sequence_name (increment by increment_quanlity/start with starting_value/maxvalue highest_value/minvalue lowest_value/cycle);
create sequence myseq;
CREATE SEQUENCE myseq
START WITH 1
INCREMENT BY 3
ORDER
NOCYCLE;

改变其递进值：alter sequence myseq increment by 1;

SELECT  myseq.NEXTVAL  FROM dual;
SELECT myseq.CURRVAL FROM dual;

CREATE TABLE auto(a number,b varchar2(10));
INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');
修改现有序列
alter sequence sequence_name (ncrement by increment_quanlity/start with starting_value/maxvalue highest_value|nomaxvalue/minvalue lowest_value|nominvalue/cycle|nocycle)

desc dba_sequences;
当视图无效时重新进行选择就可以使其生效。
                                                                          索引
1.建立索引（向表中添加索引可以使该表响应查询的速度比平时快31倍）
何时使用索引：索引本身也是表，所以INSERT会延长操作时间2.5倍。使数据输入时间变长，读取速度变快。
create index index_name on table_name(column_name);   (b*_索引,适合多个惟一值)
create bitmap index_name on table_name(column_name); （位图索引，适合少数惟一值）
create table indextext as select * from dba_objects where owner in ('public', 'sys','system' );
analyze table indextext compute statistics;                   (表分析)
analyze index indextest_objectname_idx validate structure;    (索引分析)    
set autotrace trace explain;
select  owner ,object_name from indextext where object_name='dba_indexes';
create index indextest_objectname_idx on indextext(object_name);
查看索引的大小 ：select name,height,lf_blks,pct_used from index_stats;
2.索引的开销很大，最好的办法是使需要的表上创建的索引数量最小化
3.索引的联接、压缩、跳跃
建立联接索引：create index  e_name_idx on e(ename,eid) pctfree 25;
create table e(ename varchar2(10) constraint invent_ename_pk primary key,eid number(10) constraint invent_eid_uq unique);
反转键索引：create index e_ename_idx on e(ename) reverse;

                                                                     安全管理
1.系统授权
grant create user to scan WITH ADMIN OPTION;
2.对象授权
 grant connect ,resource to scan;
      grant select on abc to scan;
     revoke grant on abc from scan;                 (撤消权限)
     revoke  create user from scan;   
GRANT ALL ON e TO scan WITH GRANT OPTION;
    grant execute on  e  TO scan;
    grant create user to scan;
3.通过角色来管理用户(对楖要文件的了解）
     create role myrole;
      grant select on sys.abc to myrole;
     grant myrole to scan;

                                                           表空间的管理
1.创建表空间
create tablespace  tabs
datafile  'e:\oracle\product\tabs.dbf' size=100m;

alter user scan default tablespace tabs;
grant unlimited tablespace ,dba to scan;
表空间的查看：
desc dba_tablespace_groups;
desc dba_users;
2.修改表空间名称：alter tablespace user rename to wjl;
    
                                                             表的管理
1.表的完整性与约束
NOT NULL
create table nn(n1 varchar2(10) not null, n2 char(10));
alter table table_name modify (column_name not null);
UNIQUE
alter table table_name add constraint constraint_name UNIQUE(column_name);
create unique index index_name on table_name( upper(first_name),upper(last_name));  (与创建默认的惟一性索引不同)
CHECK
alter table table_name add constraint constraint_name check(column_name,condition_to_satisfy);
alter table table_name add (constraint reasonable_date check(purchase_date is not null and purchase_date >= '30-JUN-2008'));
启用和禁止现有约束
alter table table_name disable constraint constraint_name;
alter table table_name enable constraint constraint_name;
改变和删除现有约束
alter table table_name modify(column_name null);
alter table table_name modify(column_name not null);
alter table table_name drop constraint constraint_name;
表间关联：
创建主键
alter table table_name add primary key (column_name_1,column_name_2,...);
create table table_name (product_name varchar2（25） primary key, .....);
alter table table_name add constraint constraint_name foreign key (column_name_in_child_table) references parent_table_name;
alter table mm add constraint fk_mm FOREIGN KEY(n1) references nn(n1);
alter table modify (id number,data varchar2(255));
alter table e add constraint ck_e_sex CHECK(sex='男' or sex= '女');
DESC dba_constraints;
2.管理索引
create index my_mm_idx on mm(m1);
create bitmap index bit_e on e(sex);
create unique index myidx on mm(m2);
3.创建索引组织表
     create table states(state_id varchar2(2),state_name varchar2(20), constraint pk_states_state_id primary key (state_id))
       oraganization index
         /
4.删除列和标注不用列
 alter table d drop column ssn;               无法删除属于SYS的表中的列???????????????
5.重命名表
  alter table d rename to dep;
6.改变表的特性
设置CACHE 属性会通知ORACLE将数据块放到最近最少使用的列表，从而强制ORACLE将数据块保留在内存中更长时间
alter table[cache|nocache]
alter table[logging|nologging]
alter table move tablespace
7.删除带有外键约束的表
drop table d cascade constraints;
drop 是删除表，delete 是删除表中的所有数据而没有删除表。
truncate table是用于删除表中所有数据，但不删除表本身的DDL语句，该表的索引也删除。比 DELETE效率更高。而且自动释放所清空记录以前占用的空间；
truncate table[schema.][drop storage|reuse storage]               (不再会回滚)
truncate tablename abc;
alter database datafile 'E:\oracle\product\10.2.0\oradata\orc1\SYSAUX05.DBF' resize 100M;
alter tablespace sysaux drop datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORC1\sysaux01.dbf';

                                                                                   事物处理与并发控制
1.事物处理控制语句包括：
COMMIT,ROLLBACK,SAVEPOINT,ROLLBACK TO SAVEPOINT,SET TRANSACTION,SET CONSTRAINTS.
1.1 savepoint function_name;                            rollback to function_name;
1.2 set transaction read only;                      set transaction read write;
 1.3 set constraint_name deferred;              set constraint all deferred;
1.4锁定：是一种用来控制共享资源并发访问的一机制。     

         
一些功能设置：
   set serveroutput on;
   set autotrace trace stat;
   set autotrace trace explain;
   alter database backup controlfile to trace;  (备份控制文件到E:\oracle\product\10.2.0\admin\orc1\udump)
GRANT SELECT ON sys.e TO scan;    

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12878495/viewspace-683376/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12878495/viewspace-683376/