oracle 小东西记录

s5ps398v6x

alter table table_name modify id number;修改列类型
alter table table_name add state number;添加列
sysdate 直接插入当前时间
TO_DATE('2013-06-26 23:59:59','yyyy-mm-dd hh24:mi:ss') 转换时间

select * from Base_OfficialStudent bos
left join Base_StudentAttributionOfTeach bsao on bos.studentid=bsao.studentid
left join Base_Class_Teacher bct on bsao.contactid=bct.contactid
left join Base_Class bc on bct.classid=bc.classid order by bos.creationdate desc


distinct 合并数据

insert into table(字段)
select 1,'张大' from dual
union all
select 1,'张大' from dual


pl/sql里运行存储过程不用加exec或者execute

declare
begin
pro_name();
end;


赋值方法
字段:=value
乘法表

begin
for i in 1..9 loop
for j in 1..i loop
dbms_output.put(j||'*'||i||'='||j*i||' ');
if j*i<10 then
dbms_output.put(' ');
end if;
end loop;
dbms_output.new_line();
end loop;  
end;


if then
elsif then
else
end if;
这里要注意是elsif不是elseif

select * from cat_tab ct left join cat_act ca on ct.id=ca.catid left join act_tab act on act.id=ca.actid order by ct.id asc


不带+号的表是左关联(右关联)那张表

select * from cat_tab ct,cat_act ca,act_tab act where ca.catid(+)=ct.id and ca.actid=act.id(+) order by ct.id asc