1.PL/SQL 基本练习
-- 基本plsql 练习
-- 1--编写一个PL/SQL块来实现：查询员工表中的工资最高的员工的姓名，工资，职位以及入职日期。
declare
e_name emp.ename%type;
e_sal emp.sal%type;
e_job emp.job%type;
e_hiredate emp.hiredate%type;
begin
select ENAME,SAL,JOB,HIREDATE into e_name,e_sal,e_job,e_hiredate from(
select e.*,max(sal) over() T from EMP e) where SAL = T;
DBMS_OUTPUT.PUT_LINE(e_name||','||e_job||','||e_sal||','||e_hiredate);
end;
-- 2--编写一个PL/SQL块来实现：查询部门中的平均工资最高的部门的名称以及部门号以及平均工资。
declare
d_name DEPT.DNAME%type;
dno DEPT.DEPTNO%type;
avg_sal number;
begin
select * into dno,d_name ,avg_sal from (
select e.DEPTNO,DNAME,avg(sal) from emp e join dept d on
e.DEPTNO = d.DEPTNO group by e.DEPTNO,DNAME order by avg(sal) desc ) where ROWNUM = 1 ;
DBMS_OUTPUT.PUT_LINE(d_name||','||dno||','||avg_sal);
end;
-- 3--编写一个PL/SQL块，将所有的员工的信息以及部门信息输出到另外的一张表中。
drop table emp_t;
create table emp_t as(
select e.*,DNAME,loc from emp e right join DEPT d on e.DEPTNO = d.DEPTNO
);
declare
type emp_s is table of emp_t%rowtype;
emp_f emp_s;
begin
select e.*,dname,loc bulk collect into emp_f
from emp e left join DEPT d on e.DEPTNO = d.DEPTNO;
for i in emp_f.FIRST..emp_f.LAST loop
DBMS_OUTPUT.PUT_LINE(emp_f(i).ENAME);
end loop;
end;
-- 注意：提前创建好另外一张表,在导入之前先要删除另外一张表中原来的数据。
-- 4编写一个PL/SQL块：在控制台上输出emp中编号为7369的姓名，工资，入职日期和职位，要求在定义变量的时候使用record类型定义。
declare
type t_y is record (e_names emp.ename%type,e_sal emp.SAL%type,e_hiredate emp.HIREDATE%type,e_job emp.JOB%type);
tt t_y;
begin
select ENAME,SAL,HIREDATE,JOB into tt from emp where EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(tt.e_names||','||tt.e_sal||','||tt.e_job||','||tt.e_hiredate);
end;
-- 6.编写一个PL/SQL块：输入一个员工的姓名，如果该员工不存在则输出员工不存在，
-- 如果员工存在，则输出该员工所在部门的平均工资以及该部门的人数。
declare
t varchar2(100) :='&t';
mount number;
avg_sal number;
n number;
begin
select count(*) into n from emp where ename = t;
if n >0 then
select avg(SAL),count(*) into avg_sal,mount from EMP where DEPTNO = (select DEPTNO from EMP where ename=t);
DBMS_OUTPUT.PUT_LINE('部门平均工资: '|| avg_sal ||'人数: '|| mount);
else
DBMS_OUTPUT.PUT_LINE('员工不存在');
end if;
end;
-- --1.编写一个PL/SQL块，计算1-100的和，但是7的倍数以及以7结尾的数字除外。
declare
sum1 number := 0;
begin
for i in 1..100 loop
if mod(i,7)=0 or mod(i,10) =0 then
continue ;
end if;
sum1 := i + sum1;
end loop;
DBMS_OUTPUT.PUT_LINE(sum1);
end;
-- --2.输入一个年，月，日，输出该天是这一年的第多少天。
declare
years number := &years;
months number := &month;
days number := &days;
sum_t number :=0;
begin
for i in 1..months-1 loop
case when i in(1,3,5,7,8,10,12) then
sum_t := sum_t+31;
when i in (4,6,9,11) then
sum_t := sum_t +30;
when i = 2 then
if mod(years,4)=0 and mod(years,100) <> 0 and mod(years,400) =0 then
sum_t := 29 +sum_t;
else
sum_t := 28+sum_t;
end if;
end case;
end loop;
sum_t := sum_t + days;
DBMS_OUTPUT.PUT_LINE(years||'年的'||sum_t||'天');
end;
-- --3.输出100-999之间的水仙花数:水仙花数：153=1*1*1+5*5*5+3*3*3=153.
declare
x number;
y number;
z number;
begin
for i in 100..999 loop
-- 百位
x := floor(i/100);
-- 个位
z := mod(i,10);
-- 十位
y := mod(floor(i/10),10);
if power(x,3) + power(y,3) + power(z,3) = i then
DBMS_OUTPUT.PUT_LINE('水仙花数: '||i);
end if;
end loop;
end;
2.数据库类面试题
-- 1、有两个表 DictA和 DictB，均有 id和 desc两个字段，如果 DictB的 id在 DictA中也有，
-- 就把 DictB的 desc更新成 DictA中对应的 desc。
merge into DICTB t using DICTA r on (t.ID = r.ID)
when matched then
update set t.DESCS = r.DESCS;
-- 2、原表（表名：Course）如左图所示，为便于阅读,写出 SQL语句，显示如右表所示（及格分
-- 数为 60）：
select s.*, case when SCORE<60 then 'FAIL'
else 'PASS' end mark
from AA1 s;
-- 3、把左表班级 A中的所有男生，“迁移”到右表班级 B中，SQL语句怎么写？
select * from CLASSA;
select * from CLASSB;
merge into CLASSB b using (select * from CLASSA where GENDER ='男') a on (a.NAME=b.NAME)
WHEN NOT MATCHED THEN
insert values (a.NAME,a.GENDER,a.STUNUM);
-- 4、如今有一个表 TMONTH如左表所示，要求把这个表中字段 C_DAY（字符类型）转成 DATE
-- 类型（别名 D_DAY），结果如右图所示。用 SQL语句表达出来。
create table tmonth(c_date varchar2(1000));
insert into tmonth values('20131231');
insert into tmonth values('20140213');
insert into tmonth values('20130429');
commit;
select to_date(c_date,'YYYY/MM/DD') d_date from tmonth;
-- 5、如今有 2张表，查询结果如图 1所示，图 1上方是 A表-学生选课表，下方是 B表-学科
-- 表，请用 SQL语句表达出图 2的效果（注意匹配不到的数据，要转成未知，如图 2的第 5
-- 行所示）。
select * from OPTION_COURSE;
select * from COUS;
select SID,COURSE from OPTION_COURSE d left join
COUS v on d.COURSEID= v.COURSEID order by sid;
-- 6、现在有一张通话记录流水表，如下图所示。现在要求通过 SQL查询，找出每个人最近一
-- 次的通话记录（日期、姓名、通话记录），如何实现这个 SQL查询？
select * from (
select a.*, row_number() over (partition by NAME order by DATE1 desc) RN
from RECORD_PHONE_AAA a) where RN =1;
-- 7、如下雇员表 Emp表所示，通过 SQL语句找出重复的 Name。
select NAME from ERP1 group by NAME having count(*)>1;
-- 8、如下雇员表 Emp表所示，通过 SQL语句找名字中不带 J的员工姓名。
select * from ERP1 where not regexp_like(NAME,'J');
-- 9、成绩表 TSCORE中，用 SQL表达出，按班级编号分组统计每个班的人数，最高分，最
-- 低分，平均分，并按平均分降序排序。
select count(*) 人数,max(SCORE) 最高分,min(SCORE) 最低分,avg(SCORE) 平均分
from B1GRADE group by C_CLASS order by 平均分 desc ;
-- 10、现有一张销量表 SALE，数据如左表所示。要求写出 SQL语句，完成建一个视图 V_SALE，
-- 求销量的去年同比和上月环比，如右表所示：
select s.*,regexp_replace(trunc(((lag(SELL,1) over(order by MONTHSS)-sell)/sell*100),4)||'%','(^%$)|(0%)',0) 环比,
regexp_replace(trunc(((lag(SELL,12) over(order by MONTHSS)-SELL)*100/SELL),4) ||'%','(0%)|(^%$)',0) 同比
from SALE s;
-- 11、左表 Sales表是每个月的销量总额，用 SQL表达出：统计每个季度的销量总额，如右
-- 表所示：
with tmp as(
select substr(MONTHSS,1,4)||(case
when substr(MONTHSS,5,2) in ('01','02','03') then 'Q1'
when substr(MONTHSS,5,2) in ('04','05','06') then 'Q2'
when substr(MONTHSS,5,2) in ('07','08','09') then 'Q3'
when substr(MONTHSS,5,2) in ('10','11','12') then 'Q4' end) sell_q,sell
from SALE)
select sell_q,sum(SELL) from tmp group by sell_q order by sell_q;
-- 12、更新表 ClassA的字段 Name为 FirstName+一格空格+lastName。如左表更新成右表
-- 的模样，请写出更新的 SQL语句。
select * from s_1;
update s_1 set NAME= FIRSTNAME||' '||LASTNAME ;
-- 13,UsingtheSQLstatement,youaregoingtoclassifythecolumnAgewhichisonthe
-- lefttable,theresultwillbeshownastherighttable:
select l.*, case when age <20 then '20-'
when age <30 then '20~30'
when age <40 then '30~40'
when age <50 then '40~50'
else '50+' end ty
from L1 l;
-- 15,Using the SQL statement,you are going to create a function F_MAX(PI_VAR1IN
-- NUMBER,PI_VAR2INNUMBER),with 2 input parameter which are PI_VAR1 and PI_VAR2.
-- If the value of PI_VAR1 is larger than PI_VAR2,the value of PI_VAR1 is returned,
-- otherwise the value of PI_VAR2 is returned
create or replace function F_max(pI_var1 number,pI_var2 number) return number is
begin
if pI_var1>pI_var2 then
return pI_var1;
else
return pI_var2;
end if;
end;
select F_max(4,4.5566) from dual;
3.CURSOR 基本练习
-- -使用游标来操作。
-- 1.已知一个客户手机号码表，T_CUSTMOBILE(CUSTNO NUMBER,MOBI
-- LENO VARCHAR2(20));请通过程序实现对该表数据进行逐条遍历，找出手
-- 机号码合法的客户号，并将客户号保存到T_TMPCUST(CUSTNO
-- NUMBER);表中，并考虑当数据量较大的情况下实现分批提交事务。
-- 已知手机号码合法的检查规则:号码由长度11位的数字组成，并由13、15、
-- 18开头。
create table phone_random(id number(30),
phone varchar2(4000))
truncate table phone_random;
select distinct phone from phone_random
where regexp_like(phone,'1[356789]\d{9}');
--随机电话号码
declare
num1 number(20);
begin
for i in 1..100000000 loop
num1:=floor(dbms_random.value(10000000000,19999999999));
insert into phone_random values(i,to_char(num1));
end loop;
end;
create table phone_no(phone varchar2(4000));
truncate table phone_no;
declare
type RT is table of phone_no%rowtype;
GT RT;
cursor TG is select distinct phone from phone_random
where regexp_like(phone,'1[356789]\d{9}');
n1 number :=0;
begin
open TG;
loop
fetch TG bulk collect into GT limit 10000;
n1 := n1+1;
exit when GT.count =0 ;
dbms_output.put_line('第几批数据迁移: '||n1);
for i in GT.first..GT.last loop
insert into phone_no values(GT(i).phone);
end loop;
end loop;
close TG;
end;
-- 2.定义一个循环语句，将变量“var count”从1增加到10
declare
var_count number:=0;
begin
for i in 1..10 loop
var_count := var_count+1;
DBMS_OUTPUT.PUT_LINE(var_count);
end loop;
end;
-- 3.定义一个循环语句，使用游标检索emp表中所有行，并定义一个变量“var_rows”，该变量统计检索到的行数。
declare
cursor cur_l is select * from EMP;
type e_k is table of emp%rowtype;
e_l e_k;
count_row number :=0;
begin
open cur_l;
loop
fetch cur_l bulk collect into e_l limit 1;
exit when e_l.COUNT=0;
count_row := 1+count_row;
end loop;
DBMS_OUTPUT.PUT_LINE('emp行数: '||count_row);
close cur_l;
end;
-- 4.如果您已经给出了两个表：客户表和订单输入表
-- 客户表包含客户ID、客户名称
-- 订单输入表包含客户ID、订单号、订购项目、数量，但并非所有客户都在订单输入表中有记录。
-- 请构造SQL语句以检索订单条目表中没有记录的所有客户ID
select 客户ID from 客户表 s left join 订单输入表 j on j.客户ID =s.客户ID where 数量 is null;
-- 5.两张表：A表和B表。a表10条记录，b表3记录。以下sql语句将产生多少条记录。
-- 从a、b中选择
-- Select * from a,b 30
-- 6. 编写一段代码，使用以下显式游标和select中的BULKCOLLECT来查询员工表中的数据，要进行批量查询，每次查询3条数据；
declare
cursor cur_k is select * from EMP;
type t_emp is table of emp%rowtype;
v_emp t_emp;
counts number :=0 ;
begin
open cur_k;
loop
fetch cur_k bulk collect into v_emp limit 3;
exit when v_emp.COUNT=0;
counts := counts + 1;
DBMS_OUTPUT.PUT_LINE('第'||counts||'批数据');
for i in v_emp.first..v_emp.LAST loop
DBMS_OUTPUT.PUT_LINE(v_emp(i).ENAME||', '||v_emp(i).DEPTNO);
end loop;
end loop;
end;
-- 7. 假设我们在表列中有一个SQL DML语句，编写一段代码，获取表“tab_command”的所有行，并从该表中动态执行存储在“sqlcmd”列中的SQL语句。
-- sql_cmd VARCHAR2（100）
declare
cursor cur_k is select * from tab_command;
begin
for i in cur_k loop
execute immediate i.sql_cmd;
end loop;
end;
4.函数,存储基本练习
--1.编写一个函数，返回指定姓名的员工所在部门的平均工资。
create or replace function fun_avg(e_name varchar2) return number
is
avg_sal number;
begin
select avg(sal) into avg_sal from EMP e where exists(select * from EMP where e.DEPTNO=DEPTNO and ENAME=e_name);
return avg_sal;
end;
select fun_avg('SMITH') from DUAL;
--2.编写一个函数,输入一个学号，返回该学生成绩的名次。（以SC为例）
create or replace function fun_num(s_no varchar2) return number
is
rnk number;
begin
select rk into rnk from(select s.*,ROWNUM rk from
(select SNO,sum(SCORE) from SC group by SNO order by sum(SCORE) desc) s) where SNO=s_no;
return rnk;
end;
select fun_num('s001') from dual;
--3.编写一个函数，输入一个日期，返回该日期是当前的第几天。
create or replace function fun_date(d_t date) return number
is
num1 number;
begin
select (sysdate-d_t) into num1 from DUAL;
return num1;
end;
select fun_date(date'2023-04-21') from dual;
--4.编写一个函数，输入一个部门号和职位，返回该部门中该职位的平均工资。
create or replace function func_t(dno number,j_ob varchar2) return number
is
avg_sal number;
begin
select avg(sal) into avg_sal from EMP where DEPTNO=dno and JOB=j_ob group by job;
return avg_sal;
end;
select func_t(30,'SALESMAN') from dual;
--5.编写一个函数，输入一个日期字符串，返回当天是星期几。
create or replace function func_dt(dt varchar2) return varchar2
is
ft varchar2(100);
begin
select to_char(to_date(dt,'YYYYMMDD'),'dy') into ft from dual;
return ft;
end;
select func_dt('20240414') from DUAL;
--6.编写一个存储过程，输入一个员工的编号，如果该员工属于10号部门，则将员工的工资上调100，
-- 如果员工属于20号部门则工资上调200，如果属于30号部门则上调300，其他部门上调400，
-- 将员工的原来的工资和上调之后的工资输出，并且如果没有该员工则通过异常进行处理。
create or replace procedure pro_count(deno number,sal1 out number,sal2 out number)
is
begin
select sal, case DEPTNO when 10 then sal+100
when 20 then sal+200
when 30 then sal+300
else sal+400 end into sal1,sal2
from EMP where EMPNO =deno;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('错误代码: '||sqlcode||'错误信息: '||sqlerrm);
end;
declare
sal1 number;
sal2 number;
begin
pro_count(7369,sal1,sal2);
DBMS_OUTPUT.PUT_LINE('调薪前: '||sal1||' 调薪后: '||sal2);
end;
--7.编写一个存储过程，实现输入一个部门的编号，输出该部门中的员工的姓名，职位，使用表类型。
create or replace type obj_tt as object
(ename1 varchar2(1000),job1 varchar2(1000));
create or replace type tt_e is table of obj_tt;
create or replace procedure pro_avg(dno number,tt out tt_e) is
begin
select obj_tt(ENAME,JOB) bulk collect into tt from EMP where DEPTNO=dno;
end;
declare
kk tt_e;
begin
pro_avg(20,kk);
for i in kk.first..kk.last loop
DBMS_OUTPUT.PUT_LINE(kk(i).ename1);
end loop;
end;
7824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
