oracle学习笔记

• 1、检查oracle服务
  ps -ef |grep oracle
  2、启动oracle服务
  sqlplus '/ as sysdba'
  sql>startup
  sql>exit
  3、启动监听
  lsnrctl status
  lsnrctl start
  lsnrctl stop
  4、查询数据库实例名
  select name from v$database;
  select instance_name from v$instance;
  5、查看表结构
    desc v$database;
  6、查看用户 show user
  7、查看登录的实例 show parameter db_name;
  8、显示及设置显示宽度 show linesize;set linesize 200;
  9、显示及设置数据分页大小 show pagesize;set pagesize 200;
  10、创建用户指定密码、表空间、配额 create user user1 identified by password1 default tablespace users quota 10M on users;
  grant create session,create table,create view to user1;
  删除用户 drop user xxx cascade;
  11、导出命令EXP EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y 完全导出，必须具备相关权限
  EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC 导出指定用户的全部对象
  EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(table1) 导出指定表
  12、导入命令IMP IMP SYSTEM/MANAGER BUFFER=64000 FILE=C:\FULL.DMP FULL=Y 完全导入
  IMP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP FROMUSER=SONIC TOUSER=SONIC 导入指定用户的对象
    EXP SONIC/SONIC    BUFFER=64000 FILE=C:\SONIC.DMP OWNER=SONIC TABLES=(table1) 
  13、SQLPLUS连接登录命令
  sqlplus username/password  如：普通用户登录  sqlplus scott/tiger
  · sqlplus username/password@net_service_name 如: sqlplus scott/tiger@orcl
  sqlplus  username/password as sysdba 如：sqlplus sys/admin as sysdba
  sqlplus username/password@//host:port/sid 
  注意：sys和system需要以sysdba登录
  在进入sql*plus之后，可以使用conn连接到其他用户，如：conn sys/admin as sysdba
  14、spool命令实例 set echo on --是否显示执行命令的内容
  set feedback off --是否显示 *   rows   selected
  set heading off --是否显示字段的名称
  set verify off --是否显示替代变量被替代前后的语句。fil
  set trimspool off --去字段空格
  set pagesize 20
  set linesize 20 
  define fil='/home/oracle/fil' --存数据的文件名为/home/oracle/fil
  prompt 'spooling to &fil'
  spool &fil
  select sysdate currtime from dual; --执行语句，或者使用‘@something.sql’
  spool off
  15、sqlplus变量使用 定义变量 define dt='abc';
  变量引用 select &dt from dual;
  16、PL/SQL变量使用方式 variable char_bv char(20)
  exec :char_bv := 'Hello World' ;
  select * from t where char_colum = :char_bv;
  17、sqlplus查询结果中文乱码 export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" 
  18、查询procedure是否存在
  select owner,name,type from all_source where owner='ZXDBG_80' and type='PROCEDURE' and    name='SP_GETLUCK';
  =============================================================
  1、查询当前系统时间 select sysdate from dual
  2、字符串连接符号"||"   select sysdate||'abc' from dual
  3、不等值判断"<>"   select sysdate from dual where 1<>2
  4、between(包含临界值) select sysdate from dual where 2 between 1 and 3
  select sysdate from dual where 2 >=1 and 2 <=3
  5、简单使用字符串和时间比较 select sysdate from dual where sysdate >= '29-5月-2014' --特定的日期格式
  6、通配符"_","%"分别代替一个、N个字符。
  7、指定转义字符 select * from (select 'abcdef%ghi' as somthing from dual)  t1  where t1.somthing like '%def$%g%' escape '$';
  8、字符串截取 select substr('abcdefg',1,3) from dual;--从第一个字符开始截取长度为3的子串，结果为"abc"
  9、ASCII码与字符互转 select chr(65)||chr(97)||ascii('a')||ascii('A') from dual;--结果为"Aa9765"
  10、四舍五入 select round(3.456,2) from dual;--保留2位小数，四舍五入
  11、数字转成字符串 select to_char(salary,'$999,999,999.999') from (select 3452345.23 as salary from dual);--结果是"$3,452,345.230"。"$,L,9,0"分别表示美元符、本地货币、数字、数字（最高位无值时以0占位）
  12、字符串转成数字 select to_number('$1,234.56','$9,999.99') from dual;--结果是"1234.56"
  13、日期转成字符串 select to_char(sysdate,'YYYY-MM-DD HH-MI-SS')||'----'||to_char(sysdate,'YYYY-MM-DD HH24-MI-SS') from dual;--结果是"2014-05-29 02-39-27----2014-05-29 14-39-27"
  14、字符串转成日期 select to_date('1990-02-12 13:59:23','YYYY-MM-DD HH24:MI:SS') from dual;--结果是"1990/02/12 13:59:23".其中DD与DD24分别是12与24小时进制
  15、算术计算中null值问题 select 2*3+nvl(null,0) from dual;--函数nvl的第一参数为null时，以0代替
  16、组函数之max,min,avg,sum，count
  17、先去重复后统计 select count(distinct somthing) from (select 123 somthing from dual);--该语句没有意义，只是展示一种用法
  18、对多个字段的组合进行分组 select deptno,job,count(salary) from emp group by deptno,job;--按照deptno,job的组合进行分组，然后进行count
  19、左外连接 select e1.ename,e2.ename from emp e1 left outer join emp e2 on (e1.mgr = e2.eno);--查询员工及其经理的名字，out关键字可省略（left、right、full关键字会显示未匹配连接条件的左表记录、右表记录、两表记录）
  20.PL/SQL简单打印helloworld set serveroutput on;
  begin
  dbms_output.put_line('hello world');
  end;
  /
  21.PL/SQL数据类型之表字段类型 v_param1 table1.column1%type 指定参数v_param1的类型为表table1的字段column1的类型
  22.PL/SQL数据类型之数组类型 实例
     declare
  type type_custom_specified is table of table1.column1%type index by binary_integer;
  v_param type_custom_specified;
     begin
  v_param(-1):=233;
  v_param(0):=234;
  v_param(1):=235;
      dbms_output.put_line(v_param(-1));
     end;
  定义：自定义数据类型type_custom_specified，数组类型，存放的数据类型为表table1的字段column1的类型，索引 类型为binary_integer。
  使用：v_param(0) 索引值可以为负数。
  
  
  23.PL/SQL数据类型之Record类型 实例
  declare
   type type_custom_specified is record (
  column1 table1.column1%type,
  column2 table1.column2%type,
  column3 table1.column3%type
  );
   v_param type_custom_specified;
  begin
  v_param.column1:=123;
  v_param.column2:=234;
  v_param.column3:=2234;
  end;
  定义：自定义数据类型type_custom_specified，含义是之“记录”。
  可以使用"%rowtype"来定义为某表的记录格式，如,declare type_custom_specified table1%rowtype
  使用：使用符号"."来指向该变量的其他成员。
  24.PL/SQL关键字 sql%rowcount 被影响的记录数
  sqlcode 操作返回码，能直接在sql语句中使用，必须先将其赋给变量后，才能在sql语句中使用
  sqlerrm 操作错误码的错误信息，能直接在sql语句中使用，必须先将其赋给变量后，才能在sql语句中使用
  25.PL/SQL分支结构 if () then 
  ...;
  elsif ()then
  ...;
  else 
  ...;
  end if;
  
  
  26.PL/SQL循环结构之LOOP循环 loop
  ...;
  exit when();
  end loop;
  
  
  27.PL/SQL循环结构之WHILE循环 while () loop
  ...;
  end loop;
  
  
  28.PL/SQL循环结构之FOR循环 for k in 1..10 loop
  ...;
  end loop;顺序1-10
  for k in reverse 1..10 loop 
  ...;
  end loop;逆序10-1
  
  
  29.PL/SQL之Exception处理 begin ...;
  exception
  when too_many_rows then
  ...;
  when no_data_found then
  ...;
        when others then
  ...;
  end;
  30.PL/SQL之游标cursor 实例一:静态显式游标
  declare
      cursor c is select * from s80rechargecode where rownum <6;
      v_p c%rowtype;
  begin
       open c;
       if(c%isopen) then
               dbms_output.put_line('cursor is is open.');
        else
               dbms_output.put_line('cursor is is closed.');
        end if;
        loop
           fetch c into v_p;
             if(c%found) then
                    dbms_output.put_line(c%rowcount||'foud:'||v_p.DESCRIPTION);
              end if;
         exit when(c%notfound);
         end loop;
         close c;
         if(c%isopen) then
               dbms_output.put_line('cursor is is open.');
                else
               dbms_output.put_line('cursor is is closed.');
                end if;
  end;
  使用for循环更简单
  declare
      cursor c is select * from s80rechargecode where rownum <6;
  begin
        for v_p in c loop     
           dbms_output.put_line(c%rowcount||'foud:'||v_p.DESCRIPTION);
         end loop;
  end;
  实例二：带参数的游标
  declare
      cursor c(code varchar2) is
              select * from s80rechargecode where rechargecode like '%'||code||'%';
  begin
        for v_p in c('productID') loop     
           dbms_output.put_line(c%rowcount||'foud:'||v_p.DESCRIPTION);
         end loop;
  end;
  31.PL/SQL绑定变量和动态SQL
  declare
  v_i binary_integer;
  v_j number(10);
  v_s varchar2(100);
  v_str varchar2(500);
  begin
    v_j:=0;
    v_s:='s80vouchlog01';
     v_str :='select count(*) from '||v_s ;
     execute immediate v_str into v_j  ;
     dbms_output.put_line(v_j);
  end;