ORACLE & MS SQL

select into 语法不再有效，但可以用create table as select 列 from 表方式
left,inner,right join仍然可用.

MS SQL（isnull）== ORACLE(NVL)
MS SQL（GetDate()）== ORACLE(Sysdate)

insert语法,oracle规定insert后一定要有into,insert into 表（列名）values（值)仍然有效
insert into 表 select 列 from 表

create table student2 as select test.student.sid,NVL(test.result.mark,100) as mark from test.student left join test.result on

test.student.sid = test.result.sid;
 
select * from student2;

delete test.student where test.student.sid=5;
delete from test.student where test.student.sid=5;

存储过程：
/*****************************************************
create procedure or replace
    procedurename(
--参数有in,out，可以设置default值
    )
    as --可以用is也可以用as,建议用as
    begin
       
    end;
--调用时必须用exec procedurename(参数);
--不能在里边只写一个select语句
/*****************************************************
--Connect sys/****@study AS SYSDBA
CREATE OR REPLACE PROCEDURE "TEST"."INSERTSTUDENT" (vsid in number,vsname varchar2)
    is
        rejectid number;
    begin
        rejectid:=10;
        if(vsid > rejectid) then
            insert into test.student values(vsid,vsname);
        end if;
    end;
--执行存储过程
exec test.insertstudent910，‘baby’);
exec test.insertstudent(11,'whoami');
select * from test.student;
delete test.student where test.student.sid=11

--下面是一个匿名块
--游标性能很差，但有些场合必须要用
declare
     --定义整形变量,varchar变量，游标变量，注意不需要@
    myvar1 number;
    myvar2 varchar2(30);
    --注意游标的定义方式is
    cursor mycursor is (select * from test.student);
begin
    --打开游标
    open mycursor;
    --做循环
    loop
    --没有 fetch next这种形式
    fetch mycursor into myvar1，myvar2;
    insert into student2 values(myvar1,1);
    --当游标到最后时退出循环
    exit when not mycursor%found;
    end loop;
    --关闭游标，注意，没有DEALLOCATE
    close mycursor;
end;


结构控制语句：
while ...loop ...end loop;
if ....then...elsif...end if;
if...then...else....end if;
loop...exit when...end loop;
游标：
声明declare cursor cursorname is select .....
使用前open
fetch cursorname into
cursorname%found
使用后close

MS SQL                       ORACLE
ISNULL                        NVL
getdate()                   sysdate
字符串+                        ||                 --字符串连接
dateadd                   add_months
datediff                  nonths_between
convert/cast        to_char/to_number/todate