Oracle常用代码总结(5)

java开发中的总结 大家可以看看哦

>char)
select to_char(hiredate,'fmDD:Month:YYYY') from guyuan;（格式化时间—>char）
select fast_name,hiredate,to_char(hiredate,'fmDD:Month:YYYY HH24:MI:ss AM' ) FROM guyuan;（月日年，时分秒 上午->char）
select fast_name,hiredate,to_char(hiredate,'YYYY:Month:fmDD HH24:MI:ss AM' ) FROM guyuan;(年月日 时分秒->char)
select sysdate,to_char(sysdate,'YYYY:MI:FMDD HH12:MM:SS') from dual;（系统当前时间->char）
select fast_name,hiredate,to_char(hiredate,'DD-MM-YY') FROM guyuan;(日-月-年)
select hiredate,to_char(hiredate,'dd-mm-RR hh:mm:ss') from guyuan;(日月年 时分秒)

数字格式化：(number->char)
select employee_id,salary,to_char(employee_id,'999999') from guyuan;(数字—>char)

通用函数：
select fast_name,manager_id,nvl(to_char(manager_id),'No Manager') from guyuan where manager_id is null;（通用输出结果）
select fast_name,nvl(to_char(commission),'no commission') "no commission" from guyuan where commission is null;(通用)
select fast_name,salary,commission,nvl2(commission,'员工','经理') income from guyuan where department_id in (20,30);(分类显示)
select fast_name,salary,commission,coalesce(commission,salary,10) conn from guyuan order by commission;（第一个值为空，返回表达式，否则运算）
create index ind on scott.dept(dname);(创建索引)

数据库键值约束：
select * from user_constriains;
select * from user_tables;
alter table add {constraint col_unique{name}/col_primary key{id,name}}
alter table abc modify age check(age>20)
alter table abc modify job default 20;
alter table abc modify job null;

create table keys(key_id number(5),key_name varchar2(20) not null,key_length varchar2(20),constraint key_id_primary primary key(key_id));(含有主键的表)










PL/SQL:
1.查询记录，如果查询失败则添加记录
（一）
declare
uname varchar2(20):='liming';
uage number(3):=21;
begin
update users set age=uage where name=uname;
if sql%notfound then
insert into users values (4,uname,uage);
end if;
end;
（二）
declare
uname %type:='cui';（查询时自动匹配类型）
uage users.age%type:=24;
begin
update users set age=uage where name=uname;
if sql%notfound then
insert into users values(5,uname,uage);
end if;
end;

2.打开PL/SQL输出功能
set serveroutput on；
3.输出一条语句
declare
type userRecord is record(uname varchar2(20),uage number(3));
rec userRecord;
begin
select name,age into rec from users where name = 'cui';
dbms_output.put_line(rec.uname||'-'||rec.uage);
end;
4.显示部门20的平均工资是否大于1500
declare
avg_sal number(5);
begin
select avg(salary) into avg_sal from guyuan where department_id = 20;

if avg_sal > 1500 then
dbms_output.put_line('平均工资大于1500');
else
dbms_output.put_line('平均工资不大于1500');
end if;
end;

5.loop循环

if实现