SQL语句

子查询 加一列作为统计

select UserName,Month,Salary,    
    Cummulation=(    
        select SUM(Salary)     
        from     
            User_Salary i    
        where     
            i.UserName=o.UserName and i.Month<=o.Month    
        )    
from User_Salary o    
order by 1,2   

select     
   A.*,B.*    
from     
   User_Salary A inner join User_Salary B    
ON     
   A.UserName = B.UserName     
where     
   B.Month <= A.Month     
order by 1,2

select    
     A.UserName,A.Month,MAX(A.Salary) as Salary,SUM (B.Salary) as Accumulation    
from   
     User_Salary A inner join User_Salary B   
 ON    
     A.UserName = B.UserName   
 where    
     B.Month <= A.Month   
 group by   
     A.UserName,A.Month   
order by       
     A.UserName,A.Month 

查询特殊符号ESCAPE定义转义符

在使用LIKE关键字进行模糊查询时，“%”、“_”和“[]”单独出现时，会被认为是通配符。为了在字符数据类型的列中查询是否存在百分号（%）、下划线（_）或者方括号（[]）字符，就需要有一种方法告诉DBMS，将LIKE判式中的这些字符看作是实际值，而不是通配符。关键字ESCAPE允许确定一个转义字符，告诉DBMS紧跟在转义字符之后的字符看作是实际值。

 //查询10%
select  *  from  deptre    where  deptname LIKE '%10!%%' ESCAPE '!'
//查询_
select  *  from  deptre    where  deptname LIKE '%!_%' ESCAPE '!'        
select  *  from  deptre    where  deptname LIKE '%!_!%%' ESCAPE '!'  

存储过程函数

基本结构 
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

函数相对多了一个返回值而已。

-- Create sequence  
create sequence SEQ_TEST
minvalue 0
maxvalue 999999
start with 3
increment by 1
nocache;
--create procedure 清空序列存储过程
create or replace procedure clean_seq is
  n    number(10);
  tsql varchar2(200);
begin
  select SEQ_TEST.NEXTVAL into n from dual;
  n :=-n;
  execute immediate 'alter sequence SEQ_TEST increment by  1';
  tsql := 'alter sequence SEQ_TEST increment by ' || n || ' nocache';
  execute immediate tsql;
  execute immediate 'select SEQ_TEST.NEXTVAL  from dual'
    into n;
  tsql := 'alter sequence SEQ_TEST increment by 1 nocache';
  execute immediate tsql;
end;

select SEQ_TEST.NEXTVAL  from dual;
select SEQ_TEST.NEXTVAL  from dual;
select SEQ_TEST.NEXTVAL  from dual;
begin
  -- Call the procedure
  clean_seq;
end;