Oracle 数据库所有查询命令

目录

1.单行查询........................................................................................................................................ 2 2.基本查询........................................................................................................................................ 3 3.限定返回的行 ................................................................................................................................ 4 4.逻辑运算........................................................................................................................................ 5 5.排序 ............................................................................................................................................... 6 6.函数 ............................................................................................................................................... 6 7.分组查询........................................................................................................................................ 9 8.多表查询...................................................................................................................................... 10 9.多表连接...................................................................................................................................... 13 10.子查询 ....................................................................................................................................... 14 11.创建和管理表............................................................................................................................ 20 12.约束 ........................................................................................................................................... 23 13.视图，序列，索引 .................................................................................................................... 25 14.其他数据库对象 ........................................................................................................................ 26 15.pl sql基础 .................................................................................................................................. 28

1.单行查询

--查询所有员工的姓 select e.last_name as 姓 from employees e

--消除重复的姓

select distinct e.last_name as 姓氏 from employees e

--计算员工的月收入（工资+佣金）

select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金 from employees

--计算员工的年收入

select salary*(nvl(commission_pct,0)+1)*12 as 年收入 from employees

--查询员工的姓名

select e.first_name||' '||e.last_name \from employees e

--查询位置为1700的部门名称（不重复） select distinct d.department_name as 部门名称 from departments d

where d.location_id=1700

--查询工资高于10000的员工 select *

from employees where salary>10000

--查询工资低于3000的员工 select *

from employees where salary<3000

--查询在1998年入职的员工 select *

from employees e

where to_char(e.hire_date,'yyyy')='1998'

--查询没有佣金的员工

select *

from employees e

where commission_pct is null

--查询姓以B开头的员工 select *

from employees e

where e.last_name like 'B%'

--查询部门号为10或者20或者30的员工 select *

from employees e

where e.department_id in(10,20,30)

2.基本查询

--查询所有员工的姓 select last_name from employees;

--消除重复的姓

select distinct last_name from employees;

--创建一个查询，以显示employees表中的唯一职务代码 select distinct t.job_id from employees t;

--创建一个查询，使其显示每位员工的姓氏、职务代码、聘用日期和员工编号，并且首先显示员工编号。为hire_date列提供一个别名：startdate

select t.employee_id,t.last_name,t.job_id,t.hire_date as startdate from employees t;

--计算员工的月收入（工资+佣金）

select t.salary+t.salary*nvl(t.commission_pct,0) from employees t; --计算员工的年收入

select 12*(t.salary+t.salary*nvl(t.commission_pct,0)) from employees t; --查询员工的姓名

select t.first_name||' '||t.last_name from employees t;

--显示与职务标识连接的姓氏，它们之间由逗号和空格分隔，这列数据命名为 Employee and Title

select t.job_id||', '||t.last_name as \from employees t;

--创建一个查询，使其显示employees表的所有数据，用逗号分隔各列，命名列为THE_OUTPUT select t.employee_id||','||t.first_name||','||t.last_name||','|| t.email||','||t.phone_number||','||t.hire_date||','||t.job_id||','||

t.salary||','||t.commission_pct||','||t.manager_id||','||t.department_id as \from employees t;

============================================================================

3.限定返回的行

--查询位置为1700的部门名称（不重复） select distinct t.department_name from departments t

where t.location_id=1700;

--创建一个查询，显示员工编号为176的员工的姓氏和部门编号 select t.last_name,t.department_id from employees t

where t.employee_id=176;

--查询工资高于10000的员工的姓氏和薪资 select t.last_name,t.salary from employees t where t.salary>10000;

--查询工资低于3000的员工 select t.*

from employees t where t.salary<3000;

--查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期

select t.last_name,t.job_id,t.hire_date from employees t where t.hire_date between to_date('19980220','yyyyMMdd') and to_date('19980501','yyyyMMdd');

--显示在1994年聘用的每位员工的姓氏和聘用日期 select t.last_name,t.hire_date from employees t where t.hire_date between to_date('19940101','yyyyMMdd') to_date('19950101','yyyyMMdd'); --*查询没有佣金的员工 select t.*

from employees t

and

where t.commission_pct is null; --查询姓以B开头的员工 select t.*

from employees t

where t.last_name like 'B%';

--查询部门号为10或者20或者30的员工 select t.*

from employees t

where t.department_id in (10,20,30); --查询没有经理的所有员工的姓氏和职称 select t.last_name,t.job_id from employees t

where t.manager_id is null;

--显示员工名字中的第三个字母为“a”的所有员工的姓氏 select t.last_name from employees t

where t.last_name like '__a%';

4.逻辑运算

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK） (需用子查询，暂不做)

--找出有佣金的员工的都做什么工作（无重复） select distinct t.job_id from employees t

where t.commission_pct is not null;

--找出不收取佣金或收取的佣金高于100的员工 select *

from employees t

where t.commission_pct is null or t.salary*t.commission_pct>100;

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（CLERK）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料(需用子查询，暂不做) --显示员工姓氏中有“a”和“e”的所有员工的姓氏 select *

from employees t

where t.last_name like '%a%' or t.last_name like '%e%' --显示职务为销售代表（SA_REP）或仓库管理员（ST_CLERK）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金 select t.last_name,t.job_id,t.salary from employees t

where (t.job_id='SA_REP' or t.job_id='ST_CLERK') and t.salary not in (2500,3500,7000);

In this lesson, you should have learned how to use joins to display data from multiple tables by using: Equijoins Nonequijoins Outer joins Self-joins Cross joins Natural joins

Full (or two-sided) outer joins **/

/*1.Write a query for the HR department to produce the addresses of all the departments.

Use the LOCATIONS and COUNTRIES tables. Show the location ID, street address, city, state or province, and country in the output.

Use a NATURAL JOIN to produce the results.*/

select lct.location_id,lct.street_address,lct.state_province,cty.country_name from locations lct natural join countries cty;

/*2.The HR department needs a report of all employees.

Write a query to display the last name, department number, and department name for all the employees.*/

select emp.last_name,department_id,dpt.department_name from employees emp natural join departments dpt;

/*3.The HR department needs a report of employees in Toronto.

Display the last name, job, department number, and the department name for all employees who work in Toronto.*/

select emp.last_name,emp.job_id,dpt.department_id,dpt.department_name

from employees emp join departments dpt on emp.department_id=dpt.department_id join locations lct on dpt.location_id=lct.location_id where lct.city='Toronto';

/*4.Create a report to display employees’ last name and employee number along with their manager’s last name and manager number.

Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Save your SQL statement as lab_06_04.sql. Run the query.*/ --自联结 select emp.last_name Employee,emp.employee_id Emp#,mgr.last_name Manager,mgr.employee_id Mgr#

from employees emp join employees mgr on emp.manager_id=mgr.employee_id;

/*5.Modify lab_06_04.sql to display all employees including King, who has no manager.

Order the results by the employee number. Save your SQL statement as lab_06_05.sql. Run the

query in lab_06_05.sql.*/ select emp.last_name Employee,emp.employee_id Emp#,mgr.last_name Manager,mgr.employee_id Mgr#

from employees emp left outer join employees mgr on emp.manager_id=mgr.employee_id order by Emp#;

/*6.Create a report for the HR department that displays employee last names, department numbers,

and all the employees who work in the same department as a given employee.

Give each column an appropriate label. Save the script to a file named lab_06_06.sql.*/ select emp.last_name Employee,emp.department_id,colleague.last_name colleague

from employees emp join employees colleague on emp.department_id=colleague.department_id where emp.employee_id<>colleague.employee_id order by Employee;

/*7.The HR department needs a report on job grades and salaries.

To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table.

Then create a query that displays the name, job, department name, salary, and grade for all employees.*/

--如果没有job_grades表,执行下列语句 /*

--建表

create table JOB_GRADES (

LOWEST_SAL NUMBER(6), HIGHEST_SAL NUMBER(6), GRADE_LEVEL CHAR(1) )

--插入数据

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (30000, 40000, 'F');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (20000, 30000, 'E');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (15000, 20000, 'D');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (8500, 15000, 'C');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (5500, 8500, 'B');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (2000, 5000, 'A'); commit; */

select emp.last_name Employee,emp.salary,g.grade_level

from employees emp join job_grades g on emp.salary between g.lowest_sal and g.highest_sal;

--附加练习

/*##8.The HR department wants to determine the names of all the employees who were hired after Davies.

Create a query to display the name and hire date of any employee hired after employee Davies.*/ select emp.last_name Employee,emp.hire_date

from employees emp join employees clg on emp.hire_date > clg.hire_date where clg.last_name='Davies' order by emp.hire_date;

/*##9.The HR department needs to find the names and hire dates of all the employees who were hired before their managers,

along with their managers’ names and hire dates. Save the script to a file named lab_06_09.sql.*/

select emp.last_name Employee,emp.hire_date,mgr.last_name Manager,mgr.hire_date mgr_hire_day

from employees emp join employees mgr on emp.manager_id=mgr.employee_id and emp.hire_date < mgr.hire_date;

9.多表连接

--using 子句

SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id);

--on子句

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id);

--使用ON子句创建多表连接

SELECT employee_id, city, department_name FROM employees e JOIN departments d

ON d.department_id = e.department_id JOIN locations l

ON d.location_id = l.location_id;

--左外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

--右外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

right outer join departments d

ON (e.department_id = d.department_id) ;

--满外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

--增加条件连接

SELECT e.employee_id, e.last_name, e.department_id, d.department_id,d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149

10.子查询

/*

预习自测:

什么是子查询?

子查询能解决什么类型的问题? 子查询可以用在什么位置? 子查询有哪些类型? */ /*

核心知识: 1.子查询概念

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

子查询就是按顺序执行系列查询并将前一个查询的结果作为下一个查询使用的值; 2.子查询语法

SELECT select_list FROM table

WHERE expr operator (SELECT select_list

FROM table);

注:operator 包含比较表达式,如: >, =, IN ,等 2.1 子查询(内查询)先于主查询(外查询)执行 2.2 子查询的结果用于外查询 3.子查询可以用于什么位置? where/having/from子句

4.举例:查询工资比Abel高的员工的姓氏和工资 SELECT last_name, salary FROM employees WHERE salary >

(SELECT salary

FROM employees

WHERE last_name = 'Abel');

5.使用指南:

5.1 子查询用括号包含

5.2 将子查询放在比较运算符右边以增加可读性

5.3 单行子查询使用单行运算符,多行子查询使用多行运算符(IN,ANY,ALL) 6.区分单行与多行子查询

6.1 单行子查询:返回单行数据,适用的比较运算符为=,>,<,<>,>=,<= 6.2 多行子查询:返回多行数据,适用的比较运算符为IN,ANY,ALL SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

7.子查询中的空值:

in(...)的含义等价于:= any(...),所以子查询中是否有空值,对结果没有影响;

但是,not in(...)的含义等价于:<> all(...),如果子查询中出现空值,整个表达式为空

自然:任意比较 all(...),如果子查询中出现空值,整个表达式为空 */

/*--------------强大的课后练习-----------------*/ --以下语句是否有问题?有还是没有? /*

select *

from employees e

where e.department_id =

(select d.department_id from departments d where d.department_name in('Marketing','IT')); */

--查询部门名称为Marketing和IT的员工信息

select *

from employees e

where e.department_id in

(select d.department_id from departments d

where d.department_name in('Marketing','IT')); --查询不是经理的员工的信息 select *

from employees e

where e.employee_id not in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null); --查询出所有经理的信息

select e.last_name,e.department_id from employees e

where e.employee_id in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null); --查询工资比10号部门中其中一个员工低的员工信息 select *

from employees e where e.salary

where e1.department_id=10);

--查询工资比10号部门都要低的员工信息 select *

from employees e where e.salary<

(select min(e1.salary) from employees e1

where e1.department_id=10); --如果要显示这个最低工资 select e.last_name,e.salary,s1.ms from employees e,

(select min(e1.salary) ms from employees e1

where e1.department_id=10) s1 where e.salary

--列出与“Sewall”(指的是last_name)从事相同工作的所有员工及部门名称 select e.last_name,d.department_name from employees e,departments d

where e.department_id=d.department_id

and e.job_id=(select job_id from employees where last_name='Sewall') and e.last_name<>'Swall';

--显示和Austin同部门，工资低于Baer的雇员有哪些 select e.last_name from employees e

where e.department_id=(select department_id from employees where last_name='Austin') and e.salary<(select salary from employees where last_name='Baer');

--找出部门90中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK） select *from employees where department_id=90; select e.last_name,e.job_id from employees e where (

e.department_id=90 and

e.employee_id in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null) ) or(

e.department_id=20 and e.job_id like '%CLERK%' ) ;

--显示每个部门的名称、地点、员工人数以及该部门所有员工的平均薪资，将平均薪资舍入到小数点后两位。

SELECT d.department_name 部门, d.location_id 地点,

s.empnum 部门员工数, s.avgsal 部门平均工资 FROM departments d,

(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal FROM employees e1 GROUP BY e1.department_id) s WHERE d.department_id=s.dptid;

--列出薪金高于公司平均薪金的所有员工,薪资,所在部门名称,上级领导姓名,工资等级 SELECT d.department_name 部门, e.last_name 员工, mgr.last_name 主管, g.grade_level 工资等级 FROM employees e

join employees mgr on e.manager_id=mgr.employee_id join departments d on e.department_id=d.department_id

join job_grades g on e.salary between g.lowest_sal and g.highest_sal WHERE e.salary>(select avg(salary) from employees)

--查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资,及工资等级

--方法1

SELECT d.department_name 部门, s.empnum 部门员工数, s.avgsal 部门平均工资, e.salary 最低工资工资,

e.last_name 部门最低工资雇员, g.grade_level 工资等级 FROM employees e, departments d,

(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal,MIN(e1.salary) minsal

FROM employees e1 GROUP BY e1.department_id) s, job_grades g

WHERE e.department_id=d.department_id AND d.department_id=s.dptid AND e.salary=s.minsal

AND e.salary between g.lowest_sal AND g.highest_sal;

--方法2:

select d.department_name 部门, d.department_id 部门号, s1.empcount 部门员工数, s1.avgsal 部门平均工资, s1.minsal 部门最低工资,

e.last_name 部门最低工资雇员, g.grade_level 工资等级 from employees e

join departments d on e.department_id=d.department_id

join job_grades g on e.salary between g.lowest_sal and g.highest_sal join

(select e1.department_id dptid,COUNT(*) empcount,AVG(e1.salary) avgsal,MIN(e1.salary) minsal from employees e1

group by department_id) s1

on e.department_id=s1.dptid and e.salary=s1.minsal;

/*------------------英文练习题-------------*/

/*1.The HR department needs a query that prompts the user for an employee last name.

The query then displays the last name and hire date of any employee in the same department as the employee

whose name they supply (excluding that employee).

For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding

Zlotkey).*/

select e.last_name,e.hire_date,e.department_id from employees e

where e.department_id=( select e1.department_id from employees e1

where e1.last_name=&last_name) and e.last_name<>&last_name;

/*2.Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary.*/ SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE e.salary>(select avg(salary) from employees) order by e.salary;

/*3.Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains the letter “u.” Save your SQL statement as lab_07_03.sql. Run your query.*/ SELECT e.employee_id, e.last_name

FROM employees e

WHERE e.department_id in(select department_id from employees where last_name like '%u%');

/*4.The HR department needs a report that displays the last name, department number, and job ID of all employees

whose department location ID is 1700.*/ select e.last_name,e.department_id,e.job_id from employees e

where department_id in(select department_id from departments where location_id=1700)

--5.Create a report for HR that displays the last name and salary of every employee who reports to King.

select e.last_name,e.salary from employees e

where e.manager_id in(select employee_id from employees

where last_name='King')

/*6.Create a report for HR that displays the department number, last name, and job ID for every employee

in the Executive department.*/

select e.department_id,e.last_name,e.job_id from employees e

where e.department_id in(select department_id

from departments d

where d.department_name='Executive')

/*7.Modify the query in lab_07_03.sql to display the employee number, last name, and salary of all employees

who earn more than the average salary, and who work in a department with any employee whose last name contains a “u.”

Resave lab_07_03.sql as lab_07_07.sql. Run the statement in lab_07_07.sql. */

SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE e.department_id in(select department_id from employees where last_name like '%u%') and e.salary>(select avg(salary) from employees);

11.创建和管理表

/*

创建和管理表,预习自检: 1.有哪些数据库对象? 表：用于存储数据

视图：一个或者多个表中的数据的子集 序列：数字值生成器

索引：提高某些查询的性能 同义词：给出对象的替代名称 2.建表是要指定哪些内容? 3.如何建表时为列指定默认值? 4.如何使用子查询语法创建表?

5.如何为已有表新增列,删除列,修改列,为新增列定义默认值? 6.如何标记列为\7.如何批量删除\列 8.如何删除表 9.如何更改表名?

10.如何舍去表中的内容? 11.如何为表,列添加注释?

12.oracle有哪些常用的数据类型? */ /*

使用sql语句完成以下练习: */

*/

update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*

7.You need a sequence that can be used with the PRIMARY KEY column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. */

create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*

8.To test your sequence, write a script to insert two rows in the DEPT table.

Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. Run the commands in your script. */

insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Education'); insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Administration'); /*9.Create a nonunique index on the NAME column in the DEPT table.*/ create index on DEPT(name);

--查看索引

select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;

14.其他数据库对象

/*

1.The staff in the HR department wants to hide some of the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table.

They want the heading for the employee name to be EMPLOYEE(列名). */

create view EMPLOYEES_VU as

select employee_id,last_name as EMPLOYEE,department_id from employees ; /*

2.Confirm that the view works. Display the contents of the EMPLOYEES_VU view. */

select * from user_views

where view_name='EMPLOYEES_VU'; /*

3.Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers. */

select EMPLOYEE,department_id from EMPLOYEES_VU; /*

4.Department 50 needs access to its employee data.

Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50.

You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO.

For security purposes, do not allow an employee to be reassigned to another department through the view. */

create view DEPT50(EMPNO,EMPLOYEE,DEPTNO) as

select employee_id,last_name,department_id from employees

where department_id=50 with read only; /*

5.Display the structure and contents of the DEPT50 view. */

desc DEPT50;

select text from user_views where view_name='DEPT50'; /*

6.Test your view. Attempt to reassign Matos to department 80. */

update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*

7.You need a sequence that can be used with the PRIMARY KEY column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. */

create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*

8.To test your sequence, write a script to insert two rows in the DEPT table.

Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration.

Confirm your additions. Run the commands in your script. */

insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Education'); insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Administration'); /*9.Create a nonunique index on the NAME column in the DEPT table.*/ create index on DEPT(name); --查看索引

select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;

15.pl sql基础

/*

1.在声明部分声明各种标量变量,并尝试在程序体中使用select语句为某个标量变量赋值 */

declare

-- Local variables here

hire_date DATE;

first_name VARCHAR2(25):='zhangsan'; salary NUMBER(8,2); isLeader BOOLEAN; age pls_integer; blood_type CHAR DEFAULT 'O';

acct_id INTEGER(4) NOT NULL := 9999; begin

-- 直接赋值 isLeader:=true;

--常用方式,利用select对变量赋值 select sysdate into hire_date from dual;

select first_name into first_name from employees where employee_id=100; dbms_output.put_line(first_name);

dbms_output.put_line(to_char(hire_date)); end; /*

2.声明行级记录变量,并尝试在程序体中使用select语句为整个记录赋值,尝试为单个分量赋值 */

declare

emp_rec employees%ROWTYPE;--emp_rec的结构与emp表的结构一致

cursor c1 IS

SELECT dep.department_id,dep.department_name ,dep.location_id FROM departments dep;

dept_rec c1%ROWTYPE;--也可以用在游标上

Begin

select dep.department_id,dep.department_name ,dep.location_id into dept_rec

FROM departments dep

where dep.department_id=20;--这里不能返回多行 dbms_output.put_line(dept_rec.department_name);

emp_rec.first_name:='zhang'; emp_rec.salary:=3000;

end; /*

3.在声明部分定义游标,并尝试或获取游标中的值 */

declare

cursor c_emp is

select a.employee_id,a.first_name||' '||a.last_name as ename ,a.job_id ,a.salary from employees a

where a.department_id=20;

total_salary employees.salary%type :=0; begin

--依次提取游标中的行,赋值给c_emp_r，c_emp_r不用事先声明 for c_emp_r in c_emp loop

total_salary := total_salary+c_emp_r.salary; dbms_output.put_line(c_emp_r.ename); end loop;

dbms_output.put_line('工资sum:'||to_char(total_salary)); end; /*

4.根据指定id获得员工的工资,工资在1000-3000内的输出A,3000-5000之间的输出B,5000-8000之间的输出C,8000以上的输出D 使用if-else和case两种方式来完成 */

declare

-- Local variables here

id integer;

v_salary employees.salary%type; v_grade char(1); begin id:=:id;

select salary into v_salary from employees t where t.employee_id=id; if v_salary>=1000 and v_salary<3000 then v_grade:='A';

elsif v_salary>=3000 and v_salary<5000 then v_grade:='B';

elsif v_salary>=5000 and v_salary<8000 then v_grade:='C'; elsif v_salary>=8000 then v_grade:='D'; else null; end if;

dbms_output.put_line('工资级别：'||v_grade); end;

--5.使用FOR循环求1-100之间的素数 declare

-- Local variables here i integer;

v_flag boolean; begin

-- Test statements here for i in 2..100 loop v_flag:=true; for j in 2..i/2 loop if mod(i,j)=0 then begin

v_flag:=false; exit; end; end if; end loop; if v_flag then

dbms_output.put(rpad(to_char(i),8,' ')); end if; end loop;

dbms_output.new_line; end;

--6.(选做)使用LOOP循环求1-100之间的素数

--7.打印99乘法表 declare

-- Local variables here i integer; j integer; begin

for i in 1..9 loop for j in 1..i loop

dbms_output.put(i||'*'||j||'='||(i*j)||' '); end loop;

dbms_output.put_line(''); end loop; end;

--8.根据工资查询员工姓名。如果此员工不存在（发出NO_DATA_FOUND异常），打印相应的提示信息。 declare

cursor c_name(p_salary employees.salary%type) is

select last_name from employees t where t.salary=p_salary; begin

for names in c_name('&v_salary') loop

dbms_output.put_line(names.last_name); end loop; exception

when no_data_found then dbms_output.put_line('没有改工资对应的员工'); end;

--9.显示EMP中的第四条记录。游标%rowcount=4 declare

cursor c_emp is

select * from employees t ; begin

for rec in c_emp loop

if(c_emp%rowcount=4) then

dbms_output.put_line(rec.last_name); end if; end loop; exception

when no_data_found then dbms_output.put_line('没有改工资对应的员工'); end; /*

10.根据部门名称(由用户输入)，按以下格式打印各部门人员姓名： 部门名称：RESEARCH

部门人员：SMITH,JONES,FORD */

declare

v_ename employees.last_name%type; v_ename_str varchar2(1000);

v_dname departments.department_name%type; --部门名称 cursor c_emp(dname varchar2) is SELECT e.last_name FROM employees e,departments e.department_id=d.department_id and department_name=dname; begin

v_dname := '&请输入部门名称：'; --v_dname := 'Marketing';

dbms_output.put_line('部门名称:'||v_dname); open c_emp(v_dname); loop

fetch c_emp into v_ename; exit when c_emp%notfound;

--dbms_output.put(v_ename||',');--put会放缓冲区 v_ename_str := v_ename_str||v_ename||','; end loop;

v_ename_str := substr(v_ename_str,1,length(v_ename_str)-1); dbms_output.put_line('部门人员:'||v_ename_str); --dbms_output.new_line;--刷新缓冲区 end; /*

11.针对所有部门，按以下格式打印各部门人员姓名： 部门名称：RESEARCH 部门人员：SMITH,JONES,FORD

部门名称：ACCOUNTING 部门人员：CLARK,KING,MILLER,

如果该部门没有人员,则输出: 部门名称：Treasury

部门人员：该部门没有员工 实现提示： 1）循环每个部门，用其部门号作条件去查员工表 2）用显示cursor完成 3）要求用FOR，会用到嵌套循环。 */

DECLARE

cursor c_department

d where

is select * from departments;

cursor c_emp(p_deptno employees.department_id%type)

is select * from employees where department_id=p_deptno; v_empname varchar2(1000); i integer:=0; begin

for dept in c_department loop

dbms_output.put_line('部门名称：'||dept.department_name); dbms_output.put('部门人员：'); v_empname:=''; i:=0;

for emp in c_emp(dept.department_id) loop

v_empname:=v_empname||emp.last_name||','; i:=i+1; end loop;

if(i=0) then

dbms_output.put_line('该部门没有员工'); else

dbms_output.put_line(substr(v_empname,1,length(v_empname)-1)); end if; end loop; end; /*

12.对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW

YORK工作那么就给他薪金扣除5％;其他情况不作处理*/ declare

cursor c_emp is

select t.employee_id,t.job_id,l.city

from employees t ,departments d ,locations l

where t.department_id=d.department_id and d.location_id=l.location_id; begin

for rec in c_emp loop

if(REGEXP_LIKE (rec.job_id, '.*_MGR') and rec.city='DALLAS') then update employees t set t.salary=t.salary*(1.15) where t.employee_id=rec.employee_id; end if;

if (REGEXP_LIKE (rec.job_id, 'S[HT]_CLERK') and rec.city='NEW YORK') then update employees t set t.salary=t.salary*0.95 where t.employee_id=rec.employee_id; end if; end loop;

end; /*

13.对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪： 81年6月以前的加薪10％ 81年6月以后的加薪5％ */ declare

cursor c_emp is select t.*

from employees t

where t.manager_id in (select employee_id from employees where last_name = 'BLAKE'); begin

for rec in c_emp loop

if rec.hire_date

update employees t set t.salary=t.salary*1.1 where t.employee_id=rec.employee_id; end if;

if rec.hire_date>=to_date('19810601','yyyymmdd') then update employees t set t.salary=t.salary*1.05 where t.employee_id=rec.employee_id; end if; end loop; end;

本文来源：https://www.bwwdw.com/article/mg68.html