Home

hogen

Follow Excellence. Success will chase you.

Home About Github Email

plsql 学习第二弹

–第一部分

–1 显示所有部门名
select dname from dept

–2 显示所有雇员名及其全年收入(工资+补助),并指定列别名”年收入”
select ename,(sal+nvl(comm,0))*12 as 年收入 from emp
–3 显示存在雇员的所有部门号
select distinct(deptno) from emp

–4 显示工资超过2850的雇员名和工资
select ename,sal from emp where sal>2850

–5 显示工资不在1500到2850之间的所有雇员名及工资
select ename,sal from emp where sal>2850 or sal<1500

–6 显示雇员代码为7566的雇员名及所在部门号
select ename,deptno from emp where empno=7566

–7 显示部门代码为10和30中工资超过1500的雇员名及工资
select ename,sal from emp where deptno in (10,30) and sal >1500

–8 显示无管理者的雇员名及岗位
select ename,job from emp where mgr is null

–9 显示所有雇员的平均工资、总计工资、最高工资、最低工资
select avg(sal) as 平均工资,sum(sal) as 总计工资,max(sal) as 最高工资,min(sal) as 最低工资 from emp

–10 显示每种岗位的雇员总数、平均工资
select count(*) as 雇员总数,avg(sal) as 平均工资 from emp group by job

–11 显示雇员总数,以及获得补助的雇员数
select count(*) as 雇员总数,count(comm) as 补助员工数 from emp
–12 显示管理者的总人数
select count(distinct mgr) from emp
–13 显示雇员工资的最大差额

select max(sal)-min(sal) from emp

select * from emp
–14 显示部门代码为20的部门号,以及该部门的所有雇员名、雇员工资及岗位
select d.deptno,e.ename,e.sal,e.job from dept d,emp e where d.deptno=20 and e.deptno=d.deptno
select deptno,e.ename,e.sal,e.job FROM emp e where deptno=20
–15 显示获得补助的所有雇员名、补助额以及所在部门号
select ename ,comm,deptno from emp where comm is not null

–16 显示所有雇员的姓名、部门编号、工资,并且列名要显示为中文
select ename as 姓名,deptno as 部门编号,sal as 工资 from emp

–17 显示每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
select avg(sal) ,deptno,job from emp group by cube(deptno,job)

–18 显示工资大于1500的雇员名和工资,并且按照工资的降序排列
select ename,sal from emp where sal>1500 order by sal desc

–19 显示雇员部门编号为10或20的信息(要求使用IN关键字)
select * from emp where deptno in (10,20)

–20 显示雇员名的第二个字母为A的信息
select * from emp where ename like ‘_A%’

–21 显示没有发放补助的雇员信息
select * from emp where comm is null

–22 显示雇员表中记录总数
select count(*) from emp


–第二部分
–1 显示所有雇员名、雇员工资及所在部门名
select e.ename ,e.sal ,d.dname from emp e,dept d where e.deptno=d.deptno

–2 显示部门代码为20的部门名,以及该部门的所有雇员名、雇员工资及岗位
select d.deptno ,e.ename,e.sal,e.job from emp e,dept d where e.deptno=d.deptno and d.deptno=20

–3 显示所有雇员名、雇员工资及工资级别
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal >s.losal and e.sal <s.hisal

–4 显示雇员”SCOTT”的管理者名
select ename from emp where empno=(select mgr from emp where ename=’SCOTT’)

–5 显示获得补助的所有雇员名、补助额以及所在部门名
select e.ename,e.comm,d.dname from emp e,dept d where e.comm is not null and e.deptno=d.deptno

–6 查询EMP表和SALGRADE表,显示部门代码为20的雇员名、工资及其工资级别
select e.ename,e.sal,s.grade from emp e,salgrade s where e.deptno=20 and e.sal>s.losal and e.sal<s.hisal

–7 显示部门代码为10的所有雇员名、部门名,同时显示其他部门名(用左连接或右连接)
select e.ename ,d.dname from dept d left join emp e on d.deptno=10 and e.deptno=d.deptno;
select from emp where deptno=10;
select
from dept where deptno=10;
–8 显示部门代码为10的所有雇员名、部门名,同时显示其他雇员名(用左连接或右连接)
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno and d.deptno=10

–9 显示部门代码为10的所有雇员名、部门名,同时显示其他部门名和雇员名(用全连接)
select e.ename,d.dname from emp e full join dept d on e.deptno=d.deptno and d.deptno=10

–10显示”BLAKE”同部门的所有雇员,但不显示”BLAKE”
SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename=’BLAKE’) AND ename <> ‘BLAKE’


–第三部分
–1、选择在部门30中员工的所有信息
select * from emp where deptno=30
–2、列出职位为(MANAGER)的员工的编号,姓名
select empno,ename from emp where job=’MANAGER’;
–3、找出奖金高于工资的员工
select ename from emp where comm>sal
–4、找出每个员工奖金和工资的总和
select sal+nvl(comm,0) from emp
–5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
select ename from emp where job=’MANAGER’ union select ename from emp where deptno=20 and job =’CLERK’
–6、找出部门10中既不是经理也不是普通员工,而且工资大于等于2000的员工
select ename from emp where job not in (‘MANAGER’,’CLERK’) and sal>=2000
–7、找出有奖金的员工的不同工作
select distinct(job) from emp where comm is not null
–8、找出没有奖金或者奖金低于500的员工
select ename from emp where comm is null or comm<500
–9、显示雇员姓名,根据其服务年限,将最老的雇员排在最前面
select ename ,hiredate from emp order by sysdate-hiredate desc
–10、找出每个月倒数第三天受雇的员工
select ename ,hiredate from emp where last_day(hiredate)-hiredate=2
–11、分别用case和decode函数列出员工所在的部门,
SELECT EMP.ename,CASE
WHEN EMP.DEPTNO=10 THEN ‘部门10’
WHEN EMP.DEPTNO=20 THEN ‘部门20’
WHEN EMP.DEPTNO=30 THEN ‘部门30’
ELSE ‘无部门’
END 部门
FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

select ename,decode(deptno,10,’部门10’,20,’部门20’,30,’部门30’,40,’部门40’,’无安排’) from emp
select from emp
–12、分组统计各部门下工资>500的员工的平均工资
SELECT deptno 部门号,round(AVG(sal),0) 平均工资 FROM emp WHERE sal>500 GROUP BY deptno
–13、统计各部门下平均工资大于500的部门
select dname from dept ,(select deptno ,avg(sal) from emp group by deptno having avg(sal)>500) e
where dept.deptno=e.deptno
–14、算出部门30中得到最多奖金的员工奖金
select max(comm) from emp where deptno=30;
–15、算出部门30中得到最多奖金的员工姓名
select ename from emp where comm=(select max(comm) from emp where deptno=30);
–16、算出每个职位的员工数和最低工资
select count(
), job ,min(sal) from emp group by job

–17、列出员工表中每个部门的员工数,和部门no
select deptno ,count(*) from emp group by deptno

–18、得到工资大于自己部门平均工资的员工信息
– 查出每个部门的平均工资另做一张表
select * from emp e,
(select deptno,avg(sal) as sal2 from emp group by deptno) e2
where e.deptno=e2.deptno and e.sal >e2.sal2
–19、分组统计每个部门下,每种职位的平均奖金(也要算没奖金的人)和总工资(包括奖金)
select deptno,job,avg(comm),sum(sal) from emp group by deptno,job
–20、笛卡尔集

–21、显示员工ID,名字,直属主管ID,名字
select e1.empno,e1.ename ,e1.mgr,e2.ename from emp e1,
(select ename, empno from emp ) e2 where e2.empno=e1.mgr
–22、DEPT表按照部门跟EMP表左关联
select * from dept left join emp on dept.deptno=emp.deptno

–23、列出员工表中每个部门的员工数,和部门no
select deptno ,count() from emp group by deptno
–24、列出员工表中每个部门的员工数(员工数必须大于3),和部门名称
select d.dname,t.count_emp from dept d,(select deptno,count(
) count_emp from emp group by deptno having count(*)>3)t
where d.deptno = t.deptno

select d.dname ,e2.num from dept d,
(select count(*) as num,deptno from emp group by deptno) e2
where d.deptno=e2.deptno and num>3
–25、找出工资比jones多的员工
select ename from emp where sal>(select sal from emp where ename =’JONES’)
–26、列出所有员工的姓名和其上级的姓名
select e1.ename,e2.ename from emp e1,
(select ename ,empno from emp) e2
where e1.mgr=e2.empno
–27、以职位分组,找出平均工资最高的两种职位

select rownum ,t.* from
(select job ,avg(sal) sal from emp group by job order by sal desc) t
where rownum<3

–28、查找出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
select e.ename,d.dname from emp e,dept d,(select max(sal) s from emp where deptno=20) t
where e.deptno in (10,30,40) and e.deptno=d.deptno and e.sal>t.s

–29、得到平均工资大于2000的工作职种
select job ,avg(sal) from emp group by job having avg(sal)>2000
–30、分部门得到工资大于2000的所有员工的平均工资,并且平均工资还要大于2500
select avg(sal),deptno from (select sal ,deptno from emp group by deptno,sal having sal>2000) group by deptno having avg(sal)>2500
–31、得到每个月工资总数最少的那个部门编部门的号,部门名称,部门位置
select from dept d,
(select t.
,rownum from (select deptno ,sum(sal) from emp group by deptno order by sum(sal) asc) t where rownum=1) s
where d.deptno=s.deptno
—32、分部门得到平均工资等级为2级(等级表)的部门编号
select t.deptno from salgrade sa,
(select deptno,avg(sal) s from emp group by deptno) t
where t.s>sa.losal and t.s<hisal and sa.grade=2;

select from salgrade
select
from dept
–33、查找出部门10和部门20中,工资最高第3名到工资第5名的员工的员工名字,部门名字,部门位置
select e1.ename,d.dname,d.loc from dept d,
( select rownum rn ,e. from
( select
from emp where deptno in(10,20) order by sal desc) e
where rownum <6) e1 where e1.rn>2 and e1.deptno=d.deptno

select from emp
–34、查找出收入(工资加上奖金),下级比自己上级还高的员工编号,员工名字,员工收入
select e1.ename,e1.empno,e1.s from
(select emp.
,sal+nvl(comm,0) as s from emp) e1,
(select emp. ,sal+nvl(comm,0) as s1 from emp ) e2 where e1.s>e2.s1 and e1.mgr=e2.empno
–35、查找出职位和’MARTIN’ 或者’SMITH’一样的员工的平均工资
select avg(sal) from emp where job in
(select job from emp where ename in (‘MARTIN’,’SMITH’))
–36、查找出不属于任何部门的员工
select
from emp e where e.deptno not in(select deptno from emp)
–37、按部门统计员工数,查处员工数最多的部门的第二名到第五名(列出部门名字,部门位置)
select d.dname,d.loc from dept d,
(select t.,rownum rn from
(select count(
) c,deptno from emp group by deptno order by c desc) t
where rownum<6) e where e.rn>1 and d.deptno=e.deptno
–38、查询出king所在部门的部门号\部门名称\部门人数 (多种方法)
select d.deptno,d.dname,t2.c from dept d,
(select deptno from emp e where ename=’KING’)t,
(select count(*) c ,deptno from emp group by deptno)t2 where d.deptno=t.deptno and d.deptno=t2.deptno

–39、查询出king所在部门的工作年限最大的员工名字
select ename from
(select rownum,ename,hiredate from emp where deptno=
(select deptno from emp where ename=’KING’ ) order by hiredate asc)
where rownum=1
–40、查询出工资成本最高的部门的部门号和部门名称
select e.deptno,d.dname,e.msal
from
(
select t.deptno,t.msal,rownum rn
from
(select deptno,max(sal) msal from emp group by deptno order by max(sal) desc)t where rownum= 1)e,dept d
where e.deptno = d.deptno

select sal
select from (select row_number() over (partition by deptno order by empno) rn,emp. from emp)
declare
V_FLAG BOOLEAN ;
V_REC BOOLEAN :=FALSE; –此值改为TRUE、NULL、FALSE进行不同的比较
V_AVA BOOLEAN:=NULL;
begin
V_FLAG:=V_REC AND V_AVA;
IF V_FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE (‘TRUE’);
ELSIF V_FLAG=FALSE THEN
DBMS_OUTPUT.PUT_LINE (‘FALSE’);
ELSE
DBMS_OUTPUT.PUT_LINE (‘NULL’);
END IF;
end;

begin
for var_count in reverse 1 .. 10 loop
dbms_output.put_line(var_count);
if var_count = 6 then
goto ot;
end if;
end loop;
<>
for var_count1 in 1 .. 10 loop
dbms_output.put_line(var_count1);
if var_count1 = 7 then
goto gt;
end if;
end loop; –这里必须要有1个语句
<>
null;
end;


–第四部分
–1、输出九九乘法表
11=1
1
2=2 22=4
1
3=3 23=6 33=9
14=4 24=6 34=9 44=16

create or replace procedure pro(in_num number)
is
in_x number:=1;
in_y number;
begin
while in_x<=in_num
loop
in_y:=1;
while in_y<=in_x
loop
dbms_output.put(in_y||’‘||in_x||’=’||in_xin_y||’ ‘);
in_y:=in_y+1;
end loop ;
dbms_output.put_line(‘ ‘);
in_x:=in_x+1;
end loop;
end ;

call pro(5);

–2、输出名为SMITH的雇员的薪水和职位
create or replace procedure pro2(v_ename in emp.ename%type)
is
v_emp_sal emp.sal%type;
v_emp_job emp.job%type;
begin
select sal,job into v_emp_sal,v_emp_job from emp where ename=v_ename ;
dbms_output.put_line(v_ename||’的薪水和职位分别为’||v_emp_sal||’ , ‘||v_emp_job);
end;

call pro2(‘SMITH’);

–3、接收部门编号,输出部门名和地理位置(DEPT表)
create or replace procedure pro3(v_dno in number,v_dname out varchar2,v_dloc out varchar2)
is
begin
select dname,loc into v_dname,v_dloc from dept where deptno=v_dno;
end;

declare
dname varchar2(12);
dloc varchar2(12);
begin pro3(&dno,dname,dloc);
dbms_output.put_line(dname||’ ‘||dloc);
end;
–4、接收雇员号,输出该雇员的工资和提成,没有佣金的用0替代。(用%type实现)
create or replace procedure pro4(v_eno in emp.empno%type,emp_sal out emp.sal%type,emp_comm out emp.comm%type)
is
begin
select sal,nvl(comm,0) into emp_sal,emp_comm from emp where empno=v_eno;
end;

declare
sal emp.sal%type;
comm emp.comm%type;
begin
pro4(&eno,sal,comm);
dbms_output.put_line(sal||’===’||comm);
end;
–5、接收雇员号,输出该雇员的所有信息,没有佣金的用0替代。(用%rowtype实现)
DECLARE
CURSOR emp_cur(empno_cur emp.empno%TYPE) IS SELECT * FROM emp WHERE empno=empno_cur;
emp_recode emp%ROWTYPE;
BEGIN
OPEN emp_cur(&empno_cur);
LOOP
FETCH emp_cur INTO emp_recode;
EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(emp_recode.ename||’–>>’||emp_recode.job||’—>>>’||NVL(emp_recode.comm,0));
END LOOP;
CLOSE emp_cur;
END;

–6、接收一个雇员名或雇员编号,判断他的job,根据job不同,为他增加相应的sal(用if-elsif实现,不要改动到基表,创建一个emp1表与emp表一摸一样)
clerk +500
salesman +1000
analyst +1500
otherwise +2000

select from emp1
create table emp1 as select
from emp

DECLARE
v_job emp1.job%TYPE;
v_empno emp1.empno%TYPE := &empno;
BEGIN
SELECT job INTO v_job FROM emp1 WHERE emp1.empno=v_empno;
dbms_output.put_line(v_job);
IF v_job=’CLERK’ THEN
UPDATE emp1 SET sal = sal+100 WHERE emp1.empno=v_empno;–要带上where条件否则会全加
ELSIF v_job=’SALESMAN’ THEN
UPDATE emp1 SET sal = sal+200 WHERE emp1.empno=v_empno;
ELSIF v_job=’MANAGER’ THEN
UPDATE emp1 SET sal = sal+300 WHERE emp1.empno=v_empno;
ELSIF v_job=’ANALYST’ THEN
UPDATE emp1 SET sal = sal+400 WHERE emp1.empno=v_empno;
ELSE
UPDATE emp1 SET sal = sal+500 WHERE emp1.empno=v_empno;
END IF;
END;

–7、用loop循环结构,为dept1表增加50-90这些部门
CREATE TABLE dept1
AS
SELECT FROM dept WHERE 1=0
SELECT
FROM dept1
DELETE FROM dept1

CREATE SEQUENCE seq_deptno
START WITH 50
INCREMENT BY 10
MAXVALUE 90
NOCYCLE
NOCACHE
SELECT seq_deptno.nextval FROM dual;

DECLARE
in_x NUMBER := 1;
BEGIN
WHILE(in_x <=5)
LOOP
INSERT INTO dept1 VALUES(seq_deptno.nextval,’’,’’);
in_x := in_x+1;
END LOOP;
END;

–8、接收一个雇员名,输出该雇员的所有内容,(用%rowtype实现),当没有这个雇员时 (no_data_found),用异常来显示错误提示
declare
v_ename emp.ename%type:=’&ename’;
v_emp emp%rowtype;
begin
select * into v_emp from emp where ename=v_ename;
dbms_output.put_line(v_emp.empno||’ -‘||v_emp.ename||’ -‘||v_emp.job);
exception
when no_data_found then
dbms_output.put_line(‘输入的雇员不存在’);
end;

–9、编写一个PL/SQL程序块以计算某个雇员的年度薪水总额,并将年薪输出来
declare
v_sum number;
v_ename emp.ename%type:=’&ena’;
begin
select (sal+nvl(comm,0))*12 into v_sum from emp where ename=v_ename;
dbms_output.put_line(v_ename||’的年薪是:’||v_sum);
end;

–10、输入部门编号,按照下列加薪比例执行(用CASE实现,创建一个emp1表,修改emp1表的数据)
deptno raise(%)
10 5%
20 10%
30 15%
40 20%

CREATE TABLE emp1
AS
SELECT * FROM emp;

DECLARE
v_in_deptno emp1.deptno%TYPE := &deptno;
BEGIN
CASE v_in_deptno
WHEN 10 THEN UPDATE emp1 SET sal = sal(1+0.05) WHERE emp1.deptno=v_in_deptno;
WHEN 20 THEN UPDATE emp1 SET sal = sal
(1+0.1) WHERE emp1.deptno=v_in_deptno;
WHEN 30 THEN UPDATE emp1 SET sal = sal(1+0.15) WHERE emp1.deptno=v_in_deptno;
WHEN 40 THEN UPDATE emp1 SET sal = sal
(1+0.20) WHERE emp1.deptno=v_in_deptno;
END CASE;
END;

SELECT * FROM emp1

–11、编写一个PL/SQL程序块以向emp1表添加新雇员编号(7901-7910,其中empno字段为主键)
–提示:原表中有7902雇员,因此插入时需用条件判断,不插入7902
select * from emp1
begin
for n in 7901..7910 loop
if n!=7902 then
insert into emp1(empno) values(n);
end if;
end loop;
end;

–12、接受2个数相除,并显示结果,如果除数为0,则抛出异常,并捕获异常,显示错误提示
declare
a number:=&a;
b number:=&b;
e exception ;
begin
if b!=0 then
dbms_output.put_line(a/b);
else
raise e;
end if;
exception
when e then
dbms_output.put_line(‘被除数不能为0’);
end;

–附加题:
–13、自己创建一张userinfo表,包含两个字段username,password,表中的记录信息取自emp表ename,empno字段,写一个
create table userinfo as select ename as username,empno as passwords from emp
drop table userinfo
select * from userinfo

declare
v_uname userinfo.username%type:=’&una’;
v_upass userinfo.passwords%type:=&upa;
r_upass userinfo.passwords%type;
begin
select passwords into r_upass from userinfo where username=v_uname;
dbms_output.put_line(r_upass);
if r_upass is not null then
if r_upass=v_upass then
dbms_output.put_line(‘登录成功!’);
else
dbms_output.put_line(‘密码错误!’);
end if ;
else
dbms_output.put_line(‘账号不存在!’);
end if;
end;
–PL/SQL程序,模拟登陆的过程,用户分别输入用户名和密码,对于登陆成功和失败分别给出提示信息

–14、用userinfo表,写一个PL/SQL程序,模拟注册的过程,用户分别输入用户名和密码,对于注册成功和失败分别给出提示信息

–<最低工资调整 >
–1. 最低工资标准:1000
–2-. 流程:
–(1).找到当前所有员工中的最低工资
– (2).根据(1)计算出 上调率
– 上调率=(最低标准工资 - 当前员工中的最低工资) /员工当前工资
– (3). 根据 (2) 的上调率来调整所有员工工资(销售部的员工不参与本次调整)
– 某员工上调金额=该员工调整前的工资 上调率
– 调整后的某员工工资=该员工调整前的工资+上调金额
drop table emp2;
create table emp2 as select
from emp;

select * from emp2 where job !='SALESMAN';
select * from emp

–参考实现:
–隐式游标+局部record变量 –>遍历该隐式游标–>根据record变量 sal, deptno, empno来更新该员工工资,
declare
v_rate number;
v_minsal number;
type v_record is record(
sal2 emp2.sal%type,
deptno2 emp2.deptno%type,
empno2 emp2.empno%type
);
v_row v_record;
begin
select min(sal) into v_minsal from emp2;
dbms_output.put_line(v_minsal);
for v_row in (select sal,deptno,empno from emp2 where job!=’SALESMAN’) loop
exit when sql%notfound ;
v_rate:=(2000-v_minsal)/v_row.sal;
update emp2 set sal=v_row.sal*(1+v_rate) where empno=v_row.empno;
end loop;
end;

declare
v_cnt number;
begin
select count() into v_cnt from user_tables where table_name=’EMP1’;
if v_cnt>0 then
dbms_output.put_line(‘表存在!’);
else
execute immediate ‘create table emp1 as select
from emp’;
end if ;
end;
drop table emp1;
select * from emp1;

 select sign(100),sign(-100),sign(0) from dual;
 select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;
 select floor(3.1),floor(2.8+1.3),floor(0) from dual;
 select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;
 select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;
 select sqrt(64),sqrt(10) from dual;
 select dbms_random.random from dual;
 select * from tabs;
 select upper('Hello') from dual;
 select lower('HELLO') from dual;
 select substr('hello ww',2,5) from dual;
 SELECT SUBSTR('hello world!',2) from dual; 
 SELECT SUBSTR('hello world!rr',-4,3) from dual; 
 SELECT SUBSTR('helloeworld!',3,4) from dual; 
 select next_day(sysdate,'') from dual;
 select sysdate from dual
select  trunc(sysdate,'dd') from dual
select sysdate+1/24 from dual

select d.dname,d.deptno from dept d,(select count() ,deptno from emp group by deptno having count()>=all(select count(*) from emp group by deptno)
)e where e.deptno=d.deptno

create or replace function func (a number)
return number as
b number;
begin
b:=a+a*2;
return b;
end;

begin
dbms_output.put_line(func(1));
end;

create or replace function func2(eno emp.empno%type)
return number as
v_days number;
hire emp.hiredate%type;
begin
select hiredate into hire from emp where empno=eno;
v_days:=ceil(sysdate-hire);
return v_days;
end;

select func2(7788) from dual
–包头
create or replace package pg
is
cursor cs is select * from emp ;
function fun(eno emp.empno%type)
return number;
procedure proce2(eno in emp.empno%type,ena out emp.ename%type);
end pg;
–包体
create or replace package body pg
as
function fun(eno emp.empno%type)
return number
is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=eno;
return v_sal;
end fun;

procedure proce2(eno in emp.empno%type,ena out emp.ename%type)
as
begin
select ename into ena from emp where empno=eno;
end proce2;
end pg;

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
v_sal:=pg.fun(7788);
dbms_output.put_line(v_sal);
pg.proce2(7788,v_ename);
dbms_output.put_line(v_ename);
end;

declare
cursor curs is select * from emp;
begin
for e in curs
loop
dbms_output.put_line(e.ename);
end loop;
end;

declare
type emp_cur is ref cursor;
e_cursor emp_cur;
e emp%rowtype;
begin
open e_cursor for select * from emp ;
loop
fetch e_cursor into e;
exit when e_cursor%notfound;
dbms_output.put_line(e.ename);
end loop;
end;

–1.编写一个程序块,从emp表中显示名为”SMITH”的雇员的薪水和职位。
declare
v_sal emp.sal%type;
v_job emp.job%type;
begin
select sal,job into v_sal,v_job from emp where ename=’SMITH’;
dbms_output.put_line(v_sal||’—‘||v_job);
end;

–1.通过使用游标来显示dept表中的部门名称。
declare
type ref_dept_cursor is ref cursor;
dept_cursor ref_dept_cursor;
d dept%rowtype;
begin
open dept_cursor for select * from dept;
loop
fetch dept_cursor into d;
exit when dept_cursor%notfound;
dbms_output.put_line(d.dname);
end loop;
end;

select * from emp
–2.使用For循环,接受一个部门号,从emp表中显示该部门的所有雇员的姓名,工作和薪水。
declare
v_dno emp.deptno%type:=&dno;
begin
for f in (select ename,job,sal from emp where deptno=v_dno) loop
exit when sql%notfound;
dbms_output.put_line(f.ename||’–’||f.job||’–’||f.sal);
end loop;
end;

–3.使用带参数的游标,实现第2题
declare
cursor dept_cursor(dno emp.deptno%type) is select * from emp where deptno=dno;
f emp%rowtype;
begin
open dept_cursor(&dno) ;
loop
fetch dept_cursor into f;
exit when dept_cursor%notfound;
dbms_output.put_line(f.ename||’–’||f.job||’–’||f.sal);
end loop;
end;

drop table emp2;
create table emp2 as select from emp;
–4.编写一个PL/SQL程序块,从emp表中对名字以”A”或”S”开始的所有雇员按他们基本薪水的10%给他们加薪。
select
from emp2;
declare
cursor emp_cursor is select sal,ename from emp2 where ename like ‘A%’ or ename like ‘S%’;
v_sal emp2.sal%type;
v_ename emp2.ename%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_sal,v_ename;
exit when emp_cursor%notfound;
update emp2 set sal =sal*(1+0.1) where ename=v_ename;
end loop;
end;


drop table ret_emp
create table ret_emp as select from emp
select
from ret_emp
truncate table ret_emp

create or replace trigger tri2
before delete on emp2 for each row
begin
insert into ret_emp values (:OLD.empno,:OLD.ename,:OLD.job,:OLD.mgr,:OLD.hiredate,:OLD.sal,:OLD.comm,:OLD.deptno);
end;

delete from emp2 where deptno=30;

–创建一个函数,它以部门号作为参数传递并且使用函数显示那个部门名称与位置。
create or replace function deptinfo(dno dept.deptno%type)
return SYS_REFCURSOR
is
d_cursor SYS_REFCURSOR;
dna dept.dname%type;
dloc dept.loc%type;
begin
open d_cursor for
select dname,loc into dna,dloc from dept where deptno=dno;
return d_cursor;
end;

declare
type dinfo is record(
v_dna dept.dname%type,
v_dloc dept.loc%type
);
v_dinfo dinfo;
type rc is ref cursor return dinfo;
v_dcursor rc;
begin
v_dcursor :=deptinfo(20);
loop
fetch v_dcursor into v_dinfo;
exit when v_dcursor%notfound;
dbms_output.put_line(v_dinfo.v_dna||’—‘||v_dinfo.v_dloc);
end loop;
end;

–创建一个行级别触发器,停止用户删除雇员名为”SMITH”的记录。
create or replace trigger trg_01
before delete on emp2 for each row
begin

if :old.ename=’SMITH’ then
RAISE_APPLICATION_ERROR(-20001,’no’,true);
end if;
end;
delete from emp2 where ename=’SCOTT’;

–创建一个语句级别触发器,不允许用户在”Sundays”使用emp表
create or replace trigger trg_02
before insert or update or delete on emp
begin
if to_char(sysdate,’Dy’) =’星期二’ then
case
when inserting then raise_application_error(-20002,’不能操作emp’);
when updating then raise_application_error(-20002,’不能操作emp’);
when deleting then raise_application_error(-20002,’不能操作emp’);
end case;
end if;
end;

update emp set ename=’a’;

select from emp ;
select
from dept;

–1
declare
v_ename emp.ename%type:=’&ena’;
v_sal emp.sal%type;
v_hire emp.hiredate%type;
v_ena emp.ename%type;
begin
select ename,sal,hiredate into v_ena,v_sal,v_hire from emp where ename=v_ename;
dbms_output.put_line(v_ename||’–’||v_sal||’–’||v_hire);
end;
–2
create or replace procedure getSalGrade(ai_ename in emp.ename%type,ao_salgrade out salgrade.grade%type)
is
begin
select s.grade into ao_salgrade from salgrade s ,emp e where e.ename=ai_ename and e.sal<s.hisal and e.sal>s.losal;
end;
–3
create or replace function maxNum(a in number,b in number,c in number)
return number
is
maxnum number;
begin
select greatest(a,b,c) into maxnum from dual;
return maxnum;
end;
–bianyi
declare
v_max number;
begin
v_max:=maxNum(1,2,3);
dbms_output.put_line(v_max);
end;

–4
create table Country(
country_oid varchar2(15) primary key,
country_code varchar2(25) not null unique,
country_name varchar2(60) not null,
country_desc varchar2(1000),
country_grade number(3) not null
)
insert into Country values(‘1001’,’CH’,’ 中国’,’发展中国家’,102);
insert into Country values(‘1000’,’US’,’ 美国’,’发达国家’,100);
update Country set country_name where country_oid=’1001’;

–5
select from Country_log
create table Country_log as select
from Country;
truncate table Country_log
alter table Country_log add country_log_oid varchar2(15) not null primary key
alter table Country_log add log_time timestamp not null;
alter table Country_log add oper_user_name varchar2(25) default ‘SCOTT’

–6
– Create sequence
create sequence SEQ02
minvalue 1
start with 1
increment by 1
nocache;

create or replace trigger trg_Country
before update or delete on Country for each row
begin
insert into Country_log values(:old.country_oid,:old.country_code,:old.country_name,:old.country_desc,:old.country_grade,seq02.nextval,sysdate,’AA’);
end;

–7
select from emp1;
update emp1 set sal =sal+sal
(decode(deptno,10,0.1,20,0.15,30,0.2,0.18) )

–8
select from emp1
create table emp1 as select
from emp
update emp1 set sal=sal*(1+months_between(sysdate,hiredate)/100)

–9
declare
v_eno emp.empno%type:=&eno;
v_ra number :=&ra;
begin
update emp1 set sal=sal*(1+v_ra/100) where empno=v_eno;
end;

–10
create or replace package package_help
is
function getMaxSal(dno dept.deptno%type) return number;
function getManagerName return varchar2;
end package_help;

create or replace package body package_help
as
function getMaxSal(dno dept.deptno%type) return number
is
max_sal emp.sal%type;
begin
select max(sal) into max_sal from emp group by deptno having deptno=dno;
return max_sal;
end getMaxSal;

function getManagerName return varchar2
is
v_ename emp.ename%type;
begin
select ename into v_ename from emp where mgr is null;
end getManagerName;
end package_help;

–11
create or replace procedure add_dept
is
dno dept1.deptno%type;
eno emp1.empno%type;
begin
dno:=50;
eno:=8000;
for f1 in 1..3 loop
insert into dept1(deptno) values(dno);
for f2 in 1..10 loop
insert into emp1(empno,deptno) values(eno,dno);
eno:=eno+1;
end loop;
dno:=dno+10;
end loop;
end;

declare
v number;
begin
v:=10;
for f in 1..9 loop
dbms_output.put_line(v);
v:=v+10;
end loop;
end;

begin
add_dept;
end;

select from dept1;
select
from emp1;
drop table dept1;
drop table emp1;
create table dept1 as select from dept;
create table emp1 as select
from emp;

select from dept;
select
from emp
–13
select * from emp

create or replace procedure pro_emp
is
cursor emp_re is select * from emp where sal>1800;
e emp%rowtype;
begin
open emp_re ;
loop
fetch emp_re into e;
exit when emp_re%notfound;
dbms_output.put_line(e.empno||’–’||e.ename||’–’||e.job||’–’||e.mgr||’–’||e.hiredate||’–’||e.sal||’–’||e.comm);
end loop;
end;

–14
create or replace function fun_dept
return varchar2
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno=
(select deptno from emp group by deptno having count() >=all(select count() from emp group by deptno));
return dna;
end;

–15

create or replace function fun_dept1
return varchar2
is
dna dept.dname%type;
begin
select dname into dna from dept d,
(select rownum ,e.deptno from (
select count() ,deptno from(
select
from emp where ename like ‘%A%’) group by deptno order by count(*) desc) e where rownum=1) e
where d.deptno=e.deptno;
return dna;
end;

select fun_dept1 from dual

select dname from dept d,
(select rownum ,e.deptno from (
select count() ,deptno from(
select
from emp where ename like ‘%A%’) group by deptno order by count(*) desc) e where rownum=1) e
where d.deptno=e.deptno;


hogen

2017-09-28
Home About Github Email