2015年10月_it_taojingzhan

原创执行计划初学一

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 1271

转载返回多个值

尽量不要在函数定义中使用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 12885

转载输出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 257

转载查询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 955

转载查询员工的员工号，员工名以及员工的经理号，如果没有经理，则显示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 8787 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 2710

转载删除分区表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 8226

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

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

空空如也

空空如也