Oracle基础练习

缺乏、安全感 2022-04-08 14:45 185阅读 0赞

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;
    --数据库优化