Oracle基础练习

select * from user_tables;
--dept 部门
--emp  员工表
select lower(ename),initcap(job) from emp;
--连接
select concat(ename,job) from emp;
--大小写转换
select concat(lower(ename),initcap(job)) from emp;
--补位
select ename from emp;
select ename,lpad(ename,7,' ') from emp;
select ename,rpad(ename,7,' ') from emp;
--截取
select ename,trim('S' from ename) from emp;
select ename,trim('H' from ename) from emp;
select * from emp;
--小数处理
select sal+500.55,round((sal+500.55),1) from emp;
select trunc((sal+500.55),1) from emp;
--隐式转换 显式转换
select to_char(sal,'$999,999,999.99') from emp;
select to_char(sal,'$000,000,000.00') from emp;
--dual虚表
select to_number('333') from dual;
select sysdate from dual;
insert into emp values(1101,'jie','Manager',7369,sysdate,5000,0,10);
select to_char(hiredate,'yyyy/MM/DD HH24:MI:SS') from emp;
--日期函数
select add_months(sysdate,12) "明年今天" from dual;
select add_months(sysdate,-12) "去年今天" from dual;
select extract(year from sysdate) "当前年份" from dual;
select extract(month from sysdate) "当前月份" from dual;
select extract(day from sysdate) "当前日期" from dual;
select last_day(sysdate) "闰月" from dual;
select next_day(sysdate,'星期一') from dual;
select months_between(sysdate,'01-1月-2019')from dual;
--NVL函数 （空值转换函数）
select ename,sal+comm from emp;
select ename,sal+nvl(comm,0) from emp;
select empno,ename,nvl(comm,'0') from emp;

create table u(
id int not null primary key,
username varchar2(16) not null
)
select * from u;

--创建主键自增序列
create sequence seq_u_id
start with 1
increment by 1
cache 20
minvalue 1
maxvalue 9999
nocycle;

insert into u(id,username) values (seq_u_id.nextval,'csj');

select seq_u_id.currval from dual;

--rownum 伪类
select rownum,emp.* from emp;
select rownum,emp.* from emp where rownum<=5;
--分页查询
select * from
(select rownum rn,emp.*  from emp where rownum<=10)
where rn>=5;
select * from
(select emp.*,rownum rn from emp)
where rn between 10 and 15;
--PL/SQL块
select * from emp;
declare --声明变量 可选
v_deptno dept.deptno%type;--与某个表中字段相同类型
begin--必选
   insert into dept(deptno,dname,loc)values(71,'sss','China');
   select deptno into v_deptno from dept where deptno=71;--使用into 临时保存变量的值
   insert into emp(empno,ename,deptno) values(1102,'zun',v_deptno);--使用变量
   commit;
   --ddl 自动提交之前所有的dml命令 create alert drop truncate
   exception --异常处理 可选
     when others then
       rollback;
end;--必选

--存储过程
create or replace procedure proc_updateEmp(empno1 in number,sal1 in number)
is
begin
  update emp set sal=sal1 where empno=empno1;
  commit;
end proc_updateEmp;

--调用存储过程
begin
  proc_updateEmp(1101,8000);
end;

create or replace procedure proc_saveEmp(ename1 in char,sal1 in number,empno1 out number)
is 
begin
  insert into emp(empno,ename,sal) values(seq_u_id.nextval,ename1,sal1);
  select seq_u_id.currval into empno1 from dual;
  commit;
end proc_saveEmp;
 
set serveroutput on;
declare empno number;
begin
  proc_saveEmp('lzyq',8800,empno);
  dbms_output.put_line(empno); 
end;

--函数（必须有返回值）
create or replace function fun_f1(id number)
return number is
sal1 number;
begin
  select sal into sal1 from emp where empno=id;
  return sal1;
end;

--调用函数
--var sale number;
--call fun_f1(1101) into :sale;

--使用create package命令创建包  
create package pkg_sal is
procedure updata_sal(name varchar2,new_sal number);
function annual_income(name varchar2) return number;
end;
--使用create package body创建包体  
create or replace package body pkg_sal is
procedure updata_sal(name varchar2,new_sal number) is
  begin
    update emp set sal=new_sal where ename=name;
  end;
  function annual_income(name varchar2) return number is
    income number(7,2);
    begin
      select sal*12+nvl(comm,0) into income from emp where ename=name;
      return income;
    end;
end;

select * from dept;
select * from emp;
--数据库优化