- 博客(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 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
转载 查询各个部门的部门号,部门名,部门人数及部门平均工资
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关注的人