oracle笔记

 
oracle没有自增字段这样的功能，但是通过触发器(trigger)和序列(sequence)可以实现。  

create table t_table
(
id number(4) primary key,
is_leaf char(1) default 'y' check (is_leaf in('y','n')),
is_client char(1) default 'n' check (is_client in('y','n') )
);

 

建一个序列，代码为：
create sequence seq_t minvalue 1 maxvalue 99999999 start with 1 increment by 1 nocache order;

 

建解发器代码为：
CREATE OR REPLACE TRIGGER 0.trg_t_table
BEFORE INSERT ON  t_table  FOR EACH ROW
BEGIN SELECT seq_t_table.nextval INTO :new.id FROM dual;
END;

 

 
可以使用sequence的地方： 
- 不包含子查询、snapshot、VIEW的 SELECT 语句 
- INSERT语句的子查询 或 VALUES中 
- UPDATE 的 SET中   

可以看如下例子： 
INSERT INTO emp VALUES   (empseq.nextval,   SYSDATE,  1200,  NULL,  20);  
 
- 在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。

- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。

 

 

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

2、 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

3、得到序列的下一个值或当前值，用下面语句

    select your_seq.nextval from dual;--获得序列your_sequence的下一个值

    select your_seq.currval from dual;--获得序列your_sequence的当前值

4、可以用做计算器 select 7*9 from dual;

该表只有一行一列，其实该表和系统中的其他表一样，一样可以执行插入、更新、删除操作，还可以执行drop操作。

 

 

数据类型 -- 变量 复合类型  参照 和 LOB等

 

varchar2(n) n最大值为32767 但在PL/SQL块中最大不应超过4000字节
number(p, s) p表示数字的总位数, s表示小数点后的位数
date 其数据长度为固定长度(7字节),
boolean 变量的值true/false/null。注意是PL/SQL的数据类型,表列不能采用该数据类型

 

 

在PL/SQL中定义变量
 v_sal number(6, 2);
 v_sal emp.sal%type; // 根据emp.sal的变量确定新变量的类型和长度 

 

复合变量 如record(记录)  // 还有嵌套表、变长数组两种复合类型
declare
 type emp_record_type is record(
  salary emp.sal%type,
  titil emp.job%type
 );
 emp_record emp_record_type;
begin
 select sal,job into emp_record from emp where empno=779;
end;

 

参照变量 是用于存放数值指针的变量, 游标变量(ref cursor)和对象类型变量(ref obj_type)

declare
 type c1 is ref cursor;
 emp_cursor c1;
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 open emp_cursor for
  select ename, sal from emp where deptno=10;
 loop
  fetch emp_cursor into v_name, v_sal;
  exit when emp_cursor%notfound;
  dems_output.put_line(v_ename);
 end loop;
 close emp_cursor;
end;

 

对象类型变量就是指向对象的引用 

 

使用算术表达式

连接字符串select ename || 'is a ' || job as "emp detail" from emp;

 

 like(not like )操作符    %和_
 in(not in)操作符 select ename,sal from emp where sal in (800, 1250); 可跟子查询
 between and
 is null 操作符
 逻辑操作符 and or not  // not优先级最高 and其次 or最低 如果要改变优先级需要括号
 order by   //  order by sal asc, comm desc 

 

select distinct site_id , userid  from dlog_diary  可以
select userid , distinct site_id  from dlog_diary  不可以
distinct(userid, site_id)是没有这种用法

 

分组函数 
max min avg sum count variance(取列或表达式的方差)
除了count(*)之外, 其他分组函数都会忽略null行
结合all、distinct

 

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

 

 

多行子查询 返回多行记录
select ename,sal deptno from emp where sal > all (select sal from emp where dpteno=30)
select ename,sal deptno from emp where sal > any (select sal from emp where dpteno=30)

 

 

相关子查询 exists
select .. from .. where exists (select子查询)

 

UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复

 

INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不同的地方是， UNION 基本上是一个 OR ，而 INTERSECT 则比较像 AND 。 UNION 是联集，而 INTERSECT 是交集。

minus(差集)  条件是列需要对应

 

内连接用于返回满足连接条件的记录
外连接不仅返回满足连接条件的所有记录, 还会返回不满足连接条件的记录
inner join表示内连接 , left join/right join/full join都为外连接
// 使用on指定连接条件

 

使用with子句重用子查询
 with summary as (
  select dname,sum(sal) as dept_total from emp, dept where emp.deptno=dept.deptno group by dname
 )
 select dname, dept_total from summary where dept_total > (select sum(dept_total)*1/3 from summary);

 

 

 

比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

 

case表达式 条件分支语句     decode函数
 select ename,sal, case when sal>3000 then 3 when sal>2000 then 2 else 1 end grade
 from emp where deptno = 10 ;

 

 
 标准是：工资在8000元以下的加20％；工资在8000元或以上的加15％，
select decode(sign(salary - 8000),>=0,salary*1.15,<0,salary*1.2,salary) from emp

CREATE OR REPLACE VIEW bank_date_lst
AS
　　Select to_char(tran_date,’yyyy.mm’),
　　SUM( DECODE ( bank_code,’001’, tran_val,0 )) 城西区，
　　SUM( DECODE ( bank_code,’002’, tran_val,0 )) 城南区，
　　SUM( DECODE ( bank_code,’003’, tran_val,0 )) 城东区
　　FROM pay_lst
　　GROUP BY to_char(tran_date,’yyyy.mm’);
直接对该视图进行查询就可

 

数字函数
floor(n) 小于等于数字n的最大整数  相对于ceil(n)
mod(m,n) 返回m/n的余数
power(m,n) m的n次幂
round(n,[m]) 四舍五入运算 m表示小数点在哪里
trunc(n,[m]) 截取数字

 

字符函数
ascii(char) 返回首字符的ascii码值
instr(char1,char2) 字串char2在char1中的位置
length(char)
lpad(char,n,char2) 用字串char2填充char的左边, 要求填充后char的总长达到n // rpad
replace(char, search_string [,replacement_string] )
substr(char,m[,n]) m为0则从首字符开始,为负则从尾部开始
trim(charfrom string) 将charfrom从string两端截取

 

select sysdate - interval '7' MINUTE  from dual

SELECT TO_DATE('2007-11-15','YYYY-MM-DD') + INTERVAL '1' DAY AS A FROM DUAL

select trunc(sysdate ,'YEAR')  from dual
select  to_char(trunc(sysdate ,'YYYY'),'YYYY')  from  dual

 

格式 to_date to_char  to_number

 

    

insert 语句 ：  

values插入 insert into ... values
子查询插入 insert into ... 子查询
 insert into emp(no,ename,sal) select no,sal,ename from emp_bak where no>100; 

 

update语句：

update ... set <col>=<val> [,<col>=val] [where condition]可更新多于1列的数据
子查询更新关联数据
 update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='sky')
 where ename='cat'

 

 

多表插入  可结合all和first
 insert all
 when deptno=10 then into t_dept10
 when deptno=20 then into t_dept20
 when job='clark' then into t_clark
 else into t_other
 select * from emp;

 

delete from .. where 
删除所有表的数据 delete from emp  不会释放表空间
truncate table emp 会释放表空间  delete可以回退但truncate不可回退

 

 

事务  commit、savepoint、rollback

 

rownum  大于某值的查询
rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件不成立。

可使用子查询来解决。注意子查询中的rownum必须要有别名，因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;
 

select * from ( select rownum r,a from yourtable  where rownum <= 20  order by name )
where r > 10  

 

 

程序包的创建

CREATE OR REPLACE PACKAGE pkg_demo
AS
    TYPE myrctype IS REF CURSOR;
    PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
    function get(intID number) return myrctype; 
END pkg_test;

 
CREATE OR REPLACE PACKAGE BODY pkg_demo
AS
    PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
    IS
       sqlstr   VARCHAR2 (500);
    BEGIN
       IF p_id = 0 THEN
          OPEN p_rc FOR
             SELECT ID, NAME, sex, address, postcode, birthday
               FROM student;
       ELSE
          sqlstr :=
             'select id,name,sex,address,postcode,birthday
            from student where id=:w_id';
          OPEN p_rc FOR sqlstr USING p_id;
       END IF;
    END;

    function get(intID number) return myrctype is
      rc myrctype; 
      sqlstr varchar2(500);
    begin
      if intID=0 then

         open rc for select id,name,sex,address,postcode,birthday from student;
      else         
         sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
         open rc for sqlstr using intid;
      end if;
  
      return rc;
    end;

END pkg_test;

 

安装orcale时，它会将当时你电脑的ip地址记录到配置文件中去，后来当你改变ip地址时，服务就打不开了。
解决方法就是改配置文件，所以就改这两个配置文件。
D:\oracle\product\10.1.0\Db_3\NETWORK\ADMIN\listener.ora
D:\oracle\product\10.1.0\Db_3\NETWORK\ADMIN\tnsnames.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))