自定义博客皮肤VIP专享

*博客头图:

格式为PNG、JPG,宽度*高度大于1920*100像素,不超过2MB,主视觉建议放在右侧,请参照线上博客头图

请上传大于1920*100像素的图片!

博客底图:

图片格式为PNG、JPG,不超过1MB,可上下左右平铺至整个背景

栏目图:

图片格式为PNG、JPG,图片宽度*高度为300*38像素,不超过0.5MB

主标题颜色:

RGB颜色,例如:#AFAFAF

Hover:

RGB颜色,例如:#AFAFAF

副标题颜色:

RGB颜色,例如:#AFAFAF

自定义博客皮肤

-+
  • 博客(44)
  • 收藏
  • 关注

原创 执行计划初学一

SQL> explain plan for select  sysdate from dual;SQL>  select * from table(dbms_xplan.display());

2015-10-29 13:46:50 197

转载 having用法

select departmentid,jobid,avg(salary),count(*)from employeesgroup by departmentid , jobidhaving count(*) > 1

2015-10-12 21:35:56 382

转载 统计各班成绩第一名的同学信息

NAME   CLASS S                             ----- ----- ----------------------     fda    1      80                         ffd    1      78                         dss    1      95            

2015-10-09 16:40:17 641

转载 按照日期降序排序,取前五条数据

select *            from (select t1.hr_no,                         t1.hr_date,                         (row_number() over(order by t1.hr_date desc)) mm                    from T_HR t1) t2   

2015-10-09 15:06:14 2425

转载 多个字段修改

update AAA   set (AA, BB) =       (select 'tao', 'jing' from dual) where proxy = 'aaa'

2015-10-06 22:21:51 475

转载 使用insert和select选项,可以把数据从一张表复制到另外一张表

insert into employees  (employeeid, name, email)  select 210, name, email from candidates where name = 'test';

2015-10-06 21:10:26 299

转载 以员工号和工资为参数,修改指定员工的工资。使用不带占位符的动态dml语句实现

create or replace procedure dyn_updatesal(p_empno employees.employeeid%type,                                          p_sal   number) as  v_str varchar2(100);begin  v_str := 'update employees

2015-10-04 20:56:48 561

转载 根据参数指定的列名和值查询员工信息(使用动态sql语句)

create or replace procedure dyn_sql_test(p_col varchar2, p_value varchar2) as  v_sal employees.salary%type;  v_str varchar2(100);begin  v_str := 'select salary from employees where ' || p_col

2015-10-04 18:52:55 1270

转载 返回多个值

尽量不要在函数定义中使用out或者in out模式的参数。通常,函数利用return语句显示返回一个值。如果需要返回多个值,可以使用带out或in out模式参数的过程返回

2015-10-04 17:09:09 426

转载 创建存储过程,以部门编号为参数返回该部门的人数及平均工资(返回一个值可以通过函数来实现,但如果要返回多个值,需要使用out或in out模式参数实现)

create or replace procedure proc_return_deptinfo(p_deptno employees.departmentid%type,                                                 p_avgsal out employees.salary%type,                        

2015-10-04 16:38:44 10712

转载 创建存储过程,以部门编号为参数,查询并返回该部门平均工资,以及该部门中比该部门平均工资高的员工信息

create or replace procedure proc_show_emp(p_deptno employees.departmentid%type) as  v_sal employees.salary%type;begin  select avg(salary)    into v_sal    from employees   where department

2015-10-04 16:23:58 12884

转载 输出OTHERS异常处理器捕获的错误代码及错误描述信息

declare  e_highlimit exception;  v_sal employees.salary%type;begin  select salary    into v_sal    from employees   where lastname = 'smith' update employees set salary = salary + 500 

2015-10-04 15:46:47 431

转载 进行异常捕获,查询lastname为smith的员工信息,如果该员工不存在,则插入一条新记录;如果存在多个同名的员工,则输出其员工号,姓名和工资

declare  v_emp employees%rowtype;begin  select * into v_emp from employees where lastname = 'smith';  dbms_output.put_line(v_emp.employeeid || ' ' || v_emp.lastname || ' ' ||                

2015-10-04 10:01:24 1947

转载 用户定义异常的抛出,修改120号员工的工资,保证修改后工资不超过8000元

declare  e_highlimit exception;  v_sal employees.salary%type;begin  update employees     set salary = salary + 500   where employeeid = 120  returning salary into v_sal;  if v_sal > 80

2015-10-04 09:47:54 585

转载 利用记录类型变量更新记录

declare  v_dept departments%Rowtype;begin  v_dept.departmentid   := 30;  v_dept.locationid     := 1100;  v_dept.departmentname := 'computer';  update departments set row = v_dept where dep

2015-10-04 09:26:57 255

转载 查询109号员工的员工号,员工名,工资,使用记录类型变量的字段接收查询结果

declare  v_emp employees%Rowtype;begin  select employeeid, firstname, salary    into v_emp.employeeid, v_emp.firstname, v_emp.salary    from employees   where employeeid = 109;  dbms_out

2015-10-04 09:21:24 425

转载 声明一个记录类型,定义记录类型变量,保存员工信息

declare  Typoe t_emp is record(deptno employees.departmentid%type,                        fname employees.firstname%type,                        sal employees.salary%type);  v_emp t_emp;be

2015-10-03 22:22:50 954

转载 查询员工的员工号,员工名以及员工的经理号,如果没有经理,则显示No Manager

select employeeid,                       firstname nvl(to_char(managerid), 'No Manager') manager                  from employees

2015-10-03 11:23:37 3197

转载 查询在2月份入职的所有员工信息

select *                  FROM employees                 where extract(month from hiredate = 2)

2015-10-03 11:06:31 8785 1

转载 日起函数取年月日

select extract(YEAR from sysdate) year,               extract(month from sysdate) month,               extract(day from sysdate) day,               extract(hour from systimestamp) hour,       

2015-10-03 11:01:59 427

转载 日期函数使用

select sysdate,       add_months(sysdate, 2),       next_day(sysdate, 2),       last_day(sysdate),       round(sysdate, 'MONTH'),       trunc(sysdate, 'MONTH')  from dual

2015-10-03 10:55:11 236

转载 字符函数lpad,rpad,ltrim,rtrim,substr使用

select lpad('abc', 5, '#') leftpad,       rpad('abc', 5, '#') rightpad,       ltrim('abcd', 'a') lefttrim,       rtrim('abcde', 'e') righttrim,       substr('abcd', 2, 3) substring  from dua

2015-10-03 10:31:45 930

转载 删除没有任何员工的部门信息

delete from departments d where not exists (select * from employees where departmentid = d.departmentid)

2015-10-02 22:37:07 2709

转载 删除分区表students子分区sex_male中年龄大于30岁的记录

delete from students PARTITION(sex_male) where sage > 30

2015-10-02 22:30:26 444

转载 将120号员工的工资与奖金设置为与130号员工的工资与奖金相同

update employees e set(salary, pct) =       (select salary, pct from employees where employeeid = 130) where employeeid = 120

2015-10-02 22:16:54 544

转载 将110号员工的工资修改为其所在部门的平均工资多100元

update employees e set salary = (select avg(salary) from employees where departmentid = e.departmentid) where employeeid = 110

2015-10-02 22:12:10 836

转载 将50号部门的员工工资设置为30号部门的平均工资加300

update employees set salary =       300 + (select avg(salary) from employees where departmentid = 30) where departmentid = 50

2015-10-02 21:57:27 933

转载 将分区表students子分区sex_male中年龄大于30的记录的classid修改为40

update students PARTITION(sex_male) s SET s.classid = 40 where sage > 30

2015-10-02 21:52:49 498

转载 显示150号员工的依次领导信息

select employeeid, lastname, managerid, salary  from employees start with employeeid = 150connect by employeeid = prior managerid

2015-10-02 11:53:58 212

转载 按从领导到员工的顺序显示employees表中员工与领导之间的关系

select employeeid, lastname, managerid  from employees start with employeeid = 100connect by prior employeeid = managerid;

2015-10-02 11:50:40 535

转载 查询人数最多的部门的信息

select *  from departments where departmentid in       (select departmentid          from employees         group by departmentid        having count(*) >= ALL (select count(*)          

2015-10-02 11:40:56 8225

转载 查询各个部门的部门号,部门名,部门人数及部门平均工资

create table aaa as select * from test1;

2015-10-02 11:38:29 4066

转载 查询各个部门的部门号,部门名,部门人数及部门平均工资

select d.departmentid,                         departmentname,                         ds.amount,                         ds.avgsal                          from departments d,              

2015-10-02 11:23:58 17566

转载 查询各个员工的员工号,员工名及其所在部门的平均工资

select employeeid, firstname, d.avgsal                  from employee,                 (select departmentid, avg(salary)                    from employee                   group by departmenti

2015-10-02 11:17:03 8826

转载 查询是领导的员工信息

select e.id, e.name, e.salary  from employee e where Exists (select * from employee a where e.employeeid = a.managerid)

2015-10-02 11:05:55 2913

转载 查询比本部门平均工资高的员工信息

select e.id, e.name, e.salary  from employee e where salary > (select avg(salary) from employee a where a.employee.id = 105)

2015-10-02 11:00:48 9366

转载 查询与159号员工的工资,职位都相同的员工信息

select id, name, salary, jobid          from employee         where (salary, jobid) =               (select salary, jobid from employee where id = 159)

2015-10-02 10:58:42 755

转载 查询比50号部门某个员工工资高的员工信息

select e.id, e.name, e.salary  from employee e where salary > ANY(select salary from employee a where a.department_id = 50)

2015-10-02 10:50:32 800

转载 查询与20号部门某个员工工资相等的员工信息

select e.id, e.name, e.salary  from employee e where salary in (select salary from employee a where a.employee.id = 20)

2015-10-02 10:42:28 2666

转载 查询比105号员工工资高的员工的员工号,员工名,员工工资信息

select e.id, e.name, e.salary  from employee e where salary > (select salary from employee a where a.employee.id = 105)

2015-10-02 10:38:19 926

空空如也

空空如也

TA创建的收藏夹 TA关注的收藏夹

TA关注的人

提示
确定要删除当前文章?
取消 删除