************************************************************第三讲*************************************************
create table dept as select * from scott.dept; --从scott.dept表中创建表
create table emp as select * from scott.emp;
select * from dept;
select * from emp;
--查询所有员工及其所在的部门信息
select emp.*,dname from emp,dept where emp.deptno=dept.deptno
--查询所有部门及其部门下的员工(没有员工的部门也要查询出来)
select d.*,e.ename from dept d left join emp e on d.deptno=e.deptno
select d.*,e.ename from dept d,emp e where d.deptno=e.deptno(+);
--查询所有部门编号大于20及其部门下的员工
select d.*,e.ename from dept d left join emp e on d.deptno=e.deptno where d.deptno>20
select d.*,e.ename from dept d,emp e where d.deptno=e.deptno and d.deptno>20
--查询所有雇员及其经理的名称
select e.ename ,manager.mname from scott.emp,scott.emp manager where e.mgr=manager.empno
--查询各个部门的最低薪水、最高薪水和平均薪水
select dname,min(sal) as 最低薪水,max(sal) as 最高薪水,avg(sal) as 平均薪水 from dept d,emp e where d.deptno=e.deptno group by dname
--查询员工数大于5的部门最高、最低、平均工资
select d.dname,min(sal) as 最低薪水,max(sal) as 最高薪水,avg(sal) as 平均薪水 from dept d,emp e where d.deptno=e.deptno group by dname having count(e.empno)>5;
--查询公司所有的非部门经理的员工的编号和名称
select empno,ename from emp where empno not in(select distinct mgr from emp where mgr is not null)
--查询工资大于4000的所有部门经理的信息
select emp.empno,emp.ename,emp.sal from emp where emp.sal>4000
select * from (select empno ,ename,sal from emp where empno in (select distinct mgr from emp)
) where sal > 4000
--查询emp表中有但newemp表中没有的数据
select * from emp minus select * from newemp
--假设三个表有共同的字段，将两个表中相同的数据插入到第三个表，语句如何写？
insert into a (select * from b intersect select * from c)
--查询所有员工，列出姓名、入职时间, 时间显示成以下格式，假设日期为"2008年10月21日"语句该怎么写
select ename,to_char(hiredate,'yyyy')||'年'||to_char(hiredate,'mm')||'月'||to_char(hiredate,'dd')||'日'from emp;
--查询oracle数据库中hr方案下emp 表中第三条到第五条纪录
select emp.* from (select rownum rno ,e.* from scott.emp e ) emp
where emp.rno between 3 and 5;
********************************************第四讲**************************************************************
--返回-3.5的绝对值；
select abs(-3.5) from dual;
--返回大于或等于-3.5的最小整数；
select ceil(-3.5) from dual
--返回小于或等于-3.5的最小整数；
select floor(-3.5) from dual;
--保留两位小数，取-3.5653四舍五入的值；
select round(-3.5653,'2')from dual;
--保留两
位小数，截取-3.5653的值；
select trunc(-3.5653,'2') from dual;
--求-3.5的符号，正号用1表示，负号用-1表示，零用0表示
select sign(-3.5)from dual;
--求5取模3
select mod(5,3) from dual;
--求-2的8次方
select power(2,8) from dual;
--求141的平方根
select sqrt(141)from dual;
--用一个sql语句将"你好！"和"世界！"连接起来
select '你好'||'世界' from dual;
--截取字符串"fdseiownvoeg"中的"sei"。
select substr('fdseiownvoeg','3','3')from dual;
--求字符串"I Love You"的长度
select length('I Love You') from dual;
--将"Andy"的所有字母变为小写
select lower('Andy') from dual;
--将"Andy"的所有字母变为大写
select upper('Andy') from dual;
--将" Andy and James "的左边空格去掉
select ltrim(' Andy and James ') from dual;
--将" Andy and James "的右边空格去掉
select rtrim(' Andy and James ') from dual;
--将" Andy and James "的所有空格去掉
select trim(' Andy and James ') from dual;
--将" Andy and James "中所有的"a"替换成"i"
select replace('andy and James','a','i') from dual;
--查找字符串"fdsfaldsfadewripoewmvproet"中"fad"出现的位置
select instr('fdsfaldsfadewripoewmvproet','fad') from dual;
--有员工表（id，name，salary，birthday）
create table employee(
id number(6),
name varchar2(10),
salary number(6),
birthday date
)
select * from scott.emp
select * from employee
--查询所有员工，但是将生日显示成如"1990年12月12日"的格式
select ename,to_char(hiredate,'yyyy')||'年'||to_char(hiredate,'mm')||'月' ||to_char(hiredate,'dd')||'日' from scott.emp
--插入新员工，并将"1990年12月12日"作为他的生日插入到数据库中去
insert into employee values(1,'aa',3000,to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'))
--查询所有员工，但是将工资1200显示成为"1,200.00"的格式
select id,name,to_char(salary,'9,999.99') from employee
--创建表EMP，DEPT，WORK，并定义表的主键和外键。
create table emp
(
eid varchar2(6) not null primary key,
ename varchar2(10),
bdate date,
sex varchar2(2) default('m'),
city varchar2(20)
)
select * from emp;
create table dept
(
did varchar2(3),
dname varchar2(20),
dcity varchar2(20)
)
select * from dept;
create table work
(
eid varchar2(6),
did varchar2(3),
startdate date,
salary int
)
select * from work
insert into emp values('A10001','aaa',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),'男','北京')
insert into emp values('A10002','bbb',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),'女','北京')
insert into emp values('A10003','ccc',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),'女','上海')
insert into emp values('A10004','ddd',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),'男','上海')
insert into emp values('A10005','eee
',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),'男','深圳')
insert into emp values('A10006','fff',to_date('1990年1月12日','yyyy"年"mm"月"dd"日"'),'女','深圳')
insert into emp values('A10007','ggg',to_date('1990年1月12日','yyyy"年"mm"月"dd"日"'),'女','深圳')
insert into emp values('P10008','hhh',to_date('1990年1月12日','yyyy"年"mm"月"dd"日"'),'女','深圳')
insert into emp values('S10009','kkk',to_date('1990年1月12日','yyyy"年"mm"月"dd"日"'),'女','深圳')
insert into dept values('A1','市场部','北京')
insert into dept values('A2','研发部','上海')
insert into dept values('A3','财务部','深圳')
insert into work values('A10001','A1',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),3000)
insert into work values('A10002','A1',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2000)
insert into work values('A10003','A2',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),4000)
insert into work values('A10004','A2',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2500)
insert into work values('A10005','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2800)
insert into work values('A10006','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),3200)
insert into work values('A10007','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2800)
insert into work values('P10008','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2000)
insert into work values('S10009','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2300)
insert into work values('S10010','A3',to_date('1990年12月12日','yyyy"年"mm"月"dd"日"'),2300)
--查询"研发"部门的所有员工的基本信息
select e.* from emp e,dept d,work w where d.dname='研发部' and e.eid=w.eid and d.did=w.did
--查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。
select * from dept where did in(select did from(select did ,count(*) as num from work group by did order by num desc,did asc)
c where rownum=1);
--显示部门人数大于5的每个部门的编号，名称，人数
select d.did, d.dname,count(*) from dept d, work w where d.did = w.did group by d.did,d.dname having count(*) >5;
select d.did,d.dname, w.num from dept d,( select did,count(*) as num from work group by did having count(*) >5) w where d.did =w.did;
--查询出工资比其所在部门平均工资高的所有职工信息。
select * from emp where eid in(select eid from work w,(select did,avg(salary) as avgsalary from work group by did) temp
where w.salary > temp.avgsalary and w.did=temp.did);
select d.deptno,avg(sal) from dept d,emp e where d.deptno=e.deptno group by d.deptno
--显示部门人数大于5的每个部门的最高工资，最低工资
select did ,max(salary) as 最高工资,min(salary) as 最低工资 from work group by did
having count(*)>5
--列出员工编号以字母P至S开头的所有员工的基本信息
select * from emp where substr(eid,'1','1') between 'P' and 'S'
--删除年龄超过60岁的员工
delete from emp where month_between(sysdate,bdate)>720
--为工龄超过10年的职工增加10%的工资
update work set salary=salary*1.1 where months_between(sysdate,bdate)>120;
**************************************************第五讲*******************************************************
--创建一个序列，第一次从5循环到10，以后再从0开始循环
create sequence seq
start with 5
minvalue 0
maxvalue 10
increment by 1
cycle
cache 2
select seq.nextval from dual
select seq.currval from dual
drop sequence seq
--建表EMPLOYEE（ID，NAME，SAL，HIREDATE）
create table employee
(
id varchar2(6) not null,
name varchar2(20),
sal number(6),
hiredate data
)
--使用JDBC连接Oracle
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection
{
public static String driver="oracle.jdbc.driver.OracleDriver";
public static String url="jdbc:oracle:thin:@ST0708:1521:orcl";
public static String username="system";
public static String password="tsinghuait";
public static Connection conn=null;
static{
try {
Class.forName(driver);
conn=DriverManager.getConnection(url, username, password);
System.out.println("数据库链接成功");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection()
{
return conn;
}
public static void main(String[] args)
{
getConnection();
}
}
--创建序列，使用JDBC往表中插入数据，通过序列实现主键自增
create sequence seq1
start with 1
increment by 1
maxvalue 1000
minvalue 1
cache 5
nocycle
******************************************第六讲*****************************************************************
--员工表（ID，姓名，职位，工龄，工资）
create table employee
(
id varchar2(10),
ename varchar2(20),
zhiwei varchar2(20),
gongling int,
sal number
)
select * from employee
drop table employee
--编写PL/SQL块，插入3个员工
begin
insert into employee values('1001','AA','经理',5,3000);
insert into employee values('1002','BB','主任',2,4000);
insert into employee values('1003','CC','董事长',3,10000);
insert into employee values('1004','DD','技术支持',4,2000);
end;
delete from employee;
--编写PL/SQL块，取出所有员工，使用游标遍历每一位员工，修改工资=工资*（1+工龄/100），同时计算出所有员工共加了多少工资，输出。
declare
sumh number:=0;
oneemp employee%rowtype;
cursor cur_emp is select * from employee;
begin
open cur_emp;
dbms_output.put_line('ID'||' '||'姓名'||' '||'职位'||' '||'工龄'||' '||'工资');
loop
fetch cur_emp into
oneemp;
exit when cur_emp%notfound;
sumh:=sumh+oneemp.sal*oneemp.gongling/100;
update employee set sal=oneemp.sal*(1+oneemp.gongling/100) where id=oneemp.id;
dbms_output.put_line(oneemp.id||' '||oneemp.ename||' '||oneemp.zhiwei||' '||oneemp.gongling||' '||oneemp.sal);
end loop;
dbms_output.put_line(sumh||'########');
close cur_emp;
end;
--修改上一步，如果加薪之后工资高于5000，则停止本次加薪
declare
oneemp employee%rowtype;
cursor cur_emp is select * from employee;
begin
open cur_emp;
dbms_output.put_line('ID'||' '||'姓名'||' '||'职位'||' '||'工龄'||' '||'工资');
loop
fetch cur_emp into oneemp;
exit when cur_emp%notfound;
if oneemp.sal<=5000 then
update employee set sal=oneemp.sal*(1+oneemp.gongling/100) where id=oneemp.id;
dbms_output.put_line(oneemp.id||' '||oneemp.ename||' '||oneemp.zhiwei||' '||oneemp.gongling||' '||oneemp.sal);
end if;
end loop;
close cur_emp;
end;
select * from employee;
--编写PL/SQL检查职位与工资范围是否符合以下标准，将不合标准的员工姓名、工资显示出来，否则提示"所有工资符合标准"
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above.
begin
insert into employee values('1001','A','Salesman',5,3000);
insert into employee values('1002','B','Analyst',2,4000);
insert into employee values('1004','C','Clerk',4,2000);
end;
declare
b boolean:=true;
oneemp employee%rowtype;
cursor cur_emp is select * from employee;
begin
open cur_emp;
loop
fetch cur_emp into oneemp;
exit when cur_emp%notfound;
case oneemp.zhiwei
when 'Clerk' then
if oneemp.sal not between 1500 and 2500 then
b:=false;
dbms_output.put_line(oneemp.ename||' '||oneemp.sal);
end if;
when 'Salesman' then
if oneemp.sal not between 2501 and 3500 then
b:=false;
dbms_output.put_line(oneemp.ename||' '||oneemp.sal);
end if;
when 'Analyst' then
if oneemp.sal not between 3501 and 4500 then
b:=false;
dbms_output.put_line(oneemp.ename||' '||oneemp.sal);
end if;
else
if oneemp.sal<4501 then
b:=false;
dbms_output.put_line(oneemp.ename||' '||oneemp.sal);
end if;
end case;
end loop;
if b then dbms_output.put_line('所有工资符合标准');end if;
close cur_emp;
end;
***********************************************第七讲**************************************************
*****************
--1.创建一个带参数的存储过程，其中的输入参数用于接收员工号，默认值为'7654'，--------------------------------------
--然后在emp 表中查询该员工的姓名、部门、薪水信息，通过dbms_output 包输出。----------------------------------------
create or replace procedure mypro(eno in scott.emp.empno%type)
as
cursor mycur is select e.ename,d.dname,e.sal from scott.emp e,scott.dept d where e.deptno=d.deptno and e.empno=eno;
ename scott.emp.ename%type;
dname scott.dept.dname%type;
sal scott.emp.sal%type;
begin
open mycur;
loop
fetch mycur into ename,dname,sal;
exit when mycur%notfound;
dbms_output.put_line(ename||' '||dname||' '||sal);
end loop;
close mycur;
end;
declare
eno scott.emp.empno%type:='7654';
begin
mypro(eno);
end;
--2.创建一个存储过程，在emp 表中查询出那些薪水高于4000 的员工分别属于什么部门，-------------------------------------
--以及相应部门的名称信息。使用dbme_output 包输出相应的信息----------------------------------------------------------
create or replace procedure mypro
as
cursor mycur is select e.ename,e.sal,d.deptno,d.dname,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno and sal>4000;
ename scott.emp.ename%type;
sal scott.emp.sal%type;
deptno scott.dept.deptno%type;
dname scott.dept.dname%type;
loc scott.dept.loc%type;
begin
open mycur;
loop
fetch mycur into ename,sal,deptno,dname,loc;
exit when mycur%notfound;
dbms_output.put_line(ename||' '||sal||' '||dname||' '||dname||' '||loc);
end loop;
close mycur;
end;
begin
mypro;
end;
--3.修改2）中建立的存储过程，增加一个参数，接收要查询的薪水值--------------------------------------------------------
create or replace procedure mypro1(xinshui in scott.emp.sal%type)
as
cursor mycur is select e.ename,e.sal,d.deptno,d.dname,d.loc from scott.emp e,scott.dept d where e.deptno=d.deptno and sal>xinshui;
ename scott.emp.ename%type;
sal scott.emp.sal%type;
deptno scott.dept.deptno%type;
dname scott.dept.dname%type;
loc scott.dept.loc%type;
begin
open mycur;
loop
fetch mycur into ename,sal,deptno,dname,loc;
exit when mycur%notfound;
dbms_output.put_line(ename||' '||sal||' '||dname||' '||dname||' '||loc);
end loop;
close mycur;
end;
begin
mypro(4000);
end;
--4.修改1）中建立的存储过程，查询出指定员工emp1 的信息后，通过调用3)题中建立的存储过程，-----------------------------
--查询有哪些员工的薪水高于员工emp1 ----------------------------------------------------------------------------------
create or replace procedure mypro(xingming in scott.emp.ename%type,xinshui out scott.emp.sal%type)
as
cursor mycur is select e.* from scott.emp e,scott.dept d where e.deptno=d.deptno and e.ename=xingming;
rowdata scott.emp%rowtype;
begin
open mycur;
loop
fetch mycur into rowdata;
exit when mycur%notfound;
xinshui:=rowdata.sal;
end loop;
close mycur;
end;
declare
xingming scott.emp.ename%type:='SCOTT';
xinshui scott.emp.sal%type;
begin
mypro(xingming,xinshui);
begin
mypro1(xinshui);
end;
end;
--5.创建触发器，修改部门信息的时候级联修改该部门下的所有员工的部门编号。-----------------------------------------------
create or replace trigger update_dept after update on scott.dept for each row
begin
update scott.dept set deptno=:new.deptno where deptno = :old.deptno;
end update_dept;
--6.创建触发器，删除部门时级联删除该部门下所有员工。-------------------------------------------------------------------
create or replace trigger del_dept after delete on scott.dept for each row
begin
delete scott.emp where deptno = :old.deptno;
end del_dept;
--7.多表视图中创建替代触发器，可以通过修改视图行修改对应的表数据。-----------------------------------------------------
create or replace view myview
as
select e.empno,e.ename,e.sal,e.deptno,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;
select * from myview
create or replace trigger mytri instead of insert on myview for each row
begin
insert into scott.dept(deptno,dname) values(:new.deptno,:new.dname);
insert into scott.emp(empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
end mytri
/************************************ 课外作业 ******************************/
/*1、拆分一个连续字符串(提示用存储过程循环拆分)
declare @s as varchar(100)
set @str='ph,p,cod,do,cu'
要求得到结果：
ph
p
cod
do
*/
--参考答案:
create or replace procedure splitStr(
str in varchar2, --需要分割的字符串
s in varchar2 --分割符
)
as
ss varchar2(1000); --存放每次截取的字符串
bindex int :=1; --每次截取的字符串的开始下标
sindex int :=0; --每次查找到的分割符的下标
len int :=0; --字符串的长度
begin
select length(str) into len from dual;
loop
select instr(str , s, bindex,1) into sindex from dual;
exit when sindex <=0 ;
select substr(str,bindex, sindex-bindex) into ss from dual;
dbms_output.put_line(ss);
bindex:=sindex+1;
end loop;
select substr(str,bindex, len-bindex+1) into ss from dual; --输入最后一个分割字符串
dbms_output.put_line(ss);
end;
--执行：
set serveroutput on;
declare
str varchar2(100):='ab,c,def,gh';
s varchar2(10):=',';
begin
splitstr(str,s);
end;
/*
2、行列转换--合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
（提示：创建一个合并的函数）
*/
--参考答案:
create or replace function func_fenbu(
fid numb
er
) return varchar2
as
str varchar2(100):='0';
pid fenbu.pid%type;
cursor cur_id is select pid from fenbu where id=fid;
begin
open cur_id;
loop
fetch cur_id into pid ;
exit when cur_id%notfound;
if str='0' then
str := pid || '';
else
str := str || ',' || pid;
end if;
end loop;
close cur_id;
return str;
end;
select distinct id,func_fenbu(id) from fenbu;
/*
3. 员工表（员工ID，姓名，工资，状态），帐号表（员工ID，用户名，密码，状态）
编写一个触发器，如果员工状态变为离职，通过触发器将其帐号变为禁用
*/
-- 参考解答:
create table scott.emp1(
id number(5) primary key,
name varchar2(10),
sal number(17,2),
status varchar2(10)
);
create table scott.account(
id number(5) primary key,
username varchar2(10),
password varchar2(10),
status varchar2(10)
);
create or replace trigger scott.update_status after update on scott.emp1 for each row
begin
if :new.status ='离职' then update scott.account set status = '禁用' where scott.account.id = :old.id;
end if;
end;
-- 准备数据
insert into scott.emp1 values(1,'AA',2000,'在职');
insert into scott.account values(1,'aaa','111','启用');
commit;
select * from scott.emp1;
select * from scott.account;
-- 测试
update scott.account set status='启用' where id=1;
-- 触发操作
update scott.emp1 set status = '离职' where id = 1;
-- 4. 已知book(id, name ,price)表，如何实现向表book中插入纪录的时候id自动增长
create table book(
id number(5) ,
name varchar2(10),
price number(17,2)
);
create sequence book_id_seq increment by 1 start with 1 minvalue 1 maxvalue 1000;//创建一个序列
create or replace trigger book_insert
before
insert on book
for each row
begin
select book_id_seq.nextval into :new.id from dual;
end;
--测试
insert into book (name,price) values('QQ',100.0);
select * from book;
drop sequence book_id_seq;
drop table book;
/*
题目：
2. 原始表:
姓名 金额 日期
张三 100 1998/4/8
张三 10 1998/7/4
里斯 20.5 1999/8/9
张三 10 1999/8/7
王五 30.5 1998/1/1
刘二 22.5 2000/8/4
里斯 30 2001/8/9
赵毅 2 2002/8/4
王五 14 2000/2/21
张三 52 1999/12/9
王八 33 2005/8/9
刘二 20 2007/8/9
.....
.....
要求一个过程,输出:
姓名 1998年总金额 1999年总金额 2000年总金额 2001年总金额 2002年总金额 ......(列出所有年份) 该员工全部金额
张三 110 10 0 200 10 ..... 988
....
....(所有员工信息)
请写出该存储过程(不能建立临时表或表变量)
思路说明
1，分析题目要求
通过题目中表结构和要求输出的结构进行比较，可以得出一下结论
将表中的数据求和计算后，行和列交换位
置，即年份变成标题，员工求和后的信息作为数据打印
2，解题思路
a，观察下面的源代码，可以发现有一个游标cur1用于取出表中出现的年份，去掉了重复行
在循环中提取出各个年份的值，拼接成了一个字符串，然后输出到第一行作为标题显示
b（*）重点部分的行列交换，那么我们需要一个SQL语句将交换前的数据计算好
观察并运行游标cur2中的SQL语句的结果，可以发现规律。
c，按照题目要求打印字符串，就是将取出的每一个员工的所有数据拼接成一个字符串打印出来
ps，注意PL/SQL块的基本结构和游标的使用方法
除主线以外的代码，可自行琢磨一下。
如果有同学有其他更好想法可练习我。
*/
declare
--声明了两个游标：cur1用于查询出标题中的所有年份
-- cur2用于按照年份计算员工的工资总额
-- 可执行其中的SQL部分，观察规律
cursor cur1 is select distinct to_char(mdate,'yyyy') as yearnum from mytable;
cursor cur2 is select name,sum(case when iyear= yearnum then sal else 0 end) as sal ,yearnum from (select distinct to_char(mdate,'yyyy') as yearnum from mytable) tyears,(select name,sal,to_char(mdate,'yyyy') as iyear from mytable) info group by grouping sets (name,yearnum),(name);
-- 与游标中的字段类型保持一致，代表了游标中的一行数据
usersal cur2%rowtype;
years cur1%rowtype;
--用于存储标题信息的字符变量
title varchar2(200):='姓名 ';
--计数器变量，标题中取出了多少个年份就要记录个数，在去工资总额时用于判断员工的记录数
yearcount number:=0;
--计数器变量，记录从cur2中取出的员工的记录数，以便比较
usercount number:=0;
--用于存储员工工资总额的字符变量
usercontent varchar2(200);
begin
open cur1; --打开游标
open cur2; --打开游标
--第一次循环，取出标题信息
loop
fetch cur1 into years;
exit when cur1%notfound;
title :=title || years.yearnum||'年度 ';
--记数，一共取出了多少个年份
yearcount :=yearcount+1;
end loop;
dbms_output.put_line(title||' 总金额');
--第二次循环，取出员工工资信息
loop
fetch cur2 into usersal;
exit when cur2%notfound;
--拼接字符串，进行行列交换的操作
if usercount = 0 then
usercontent := usercontent || https://www.doczj.com/doc/3e16492219.html,||' '||usersal.sal;
else
usercontent := usercontent ||' '||usersal.sal;
end if;
--记数
usercount :=usercount+1;
--判断两个计数器变量的值，如果从员工信息中取出的记录数大于年份的个数，
--表示该员工的记录已经取完，将计数器变量清空，字符串变量清空，
--以便取下一个员工的信息
if usercount>yearcount then
dbms_output.put_line(usercontent);
usercount:=0;
usercontent :='';
end if;
end loop;
close cur1;
close cur2;
end;
***************************************************
----------------------------------10的阶乘----------------------------------
declare
jc number:=1;
cj number:=1;
begin
loop
cj:=cj*jc;
jc:=jc+1;
exit when jc>10;
end loop;
dbms_output.put_line(cj);
end;
------1到100之间能被7或3整除的奇数和，打出是哪些奇数，和这个奇数的和--------
declare
i number:=1;
j number:=1;
begin
for i in 1..100
loop
if (i mod 3=0 or i mod 7=0) and i mod 2<>0 then
dbms_output.put_line(i);
j:=j+i;
end if;
end loop;
dbms_output.put_line(j);
end;
------查询出scott用户下的DEPT表的数据，并打出所有的数据----------------------
declare
a scott.dept%rowtype;//声明表记录类型
cursor cur_dept is select * from scott.dept;//声明游标
begin
open cur_dept;//打开游标
dbms_output.put_line('deptno'||' '||'dname'||' '||'loc');
loop
fetch cur_dept into a;//提取游标
exit when cur_dept%notfound;
dbms_output.put_line(a.deptno||' '||a.dname||' '||a.loc);
end loop;
close cur_dept;
end;
******************************************************************************************************
/*
题目：
2. 原始表:
姓名 金额 日期
张三 100 1998/4/8
张三 10 1998/7/4
里斯 20.5 1999/8/9
张三 10 1999/8/7
王五 30.5 1998/1/1
刘二 22.5 2000/8/4
里斯 30 2001/8/9
赵毅 2 2002/8/4
王五 14 2000/2/21
张三 52 1999/12/9
王八 33 2005/8/9
刘二 20 2007/8/9
.....
.....
要求一个过程,输出:
姓名 1998年总金额 1999年总金额 2000年总金额 2001年总金额 2002年总金额 ......(列出所有年份) 该员工全部金额
张三 110 10 0 200 10 ..... 988
....
....(所有员工信息)
请写出该存储过程(不能建立临时表或表变量)
思路说明
1，分析题目要求
通过题目中表结构和要求输出的结构进行比较，可以得出一下结论
将表中的数据求和计算后，行和列交换位置，即年份变成标题，员工求和后的信息作为数据打印
2，解题思路
a，观察下面的源代码，可以发现有一个游标cur1用于取出表中出现的年份，去掉了重复行
在循环中提取出各个年份的值，拼接成了一个字符串，然后输出到第一行作为标题显示
b（*）重点部分的行列交换，那么我们需要一个SQL语句将交换前的数据计算好
观察并运行游标cur2中的SQL语句的结果，可以发现规律。
c，按照题目要求打印字符串，就是将取出的每一个员工的所有数据拼接成一个字符串打印出来
ps，注意PL/SQL块的基本结构和游标的使用方法
除主线以外的代码，可自行琢磨一下。
如果有同学有其
他更好想法可练习我。
*/
declare
--声明了两个游标：cur1用于查询出标题中的所有年份
-- cur2用于按照年份计算员工的工资总额
-- 可执行其中的SQL部分，观察规律
cursor cur1 is select distinct to_char(mdate,'yyyy') as yearnum from mytable;
cursor cur2 is select name,sum(case when iyear= yearnum then sal else 0 end) as sal ,yearnum from (select distinct to_char(mdate,'yyyy') as yearnum from mytable) tyears,(select name,sal,to_char(mdate,'yyyy') as iyear from mytable) info group by grouping sets (name,yearnum),(name);
-- 与游标中的字段类型保持一致，代表了游标中的一行数据
usersal cur2%rowtype;
years cur1%rowtype;
--用于存储标题信息的字符变量
title varchar2(200):='姓名 ';
--计数器变量，标题中取出了多少个年份就要记录个数，在去工资总额时用于判断员工的记录数
yearcount number:=0;
--计数器变量，记录从cur2中取出的员工的记录数，以便比较
usercount number:=0;
--用于存储员工工资总额的字符变量
usercontent varchar2(200);
begin
open cur1; --打开游标
open cur2; --打开游标
--第一次循环，取出标题信息
loop
fetch cur1 into years;
exit when cur1%notfound;
title :=title || years.yearnum||'年度 ';
--记数，一共取出了多少个年份
yearcount :=yearcount+1;
end loop;
dbms_output.put_line(title||' 总金额');
--第二次循环，取出员工工资信息
loop
fetch cur2 into usersal;
exit when cur2%notfound;
--拼接字符串，进行行列交换的操作
if usercount = 0 then
usercontent := usercontent || https://www.doczj.com/doc/3e16492219.html,||' '||usersal.sal;
else
usercontent := usercontent ||' '||usersal.sal;
end if;
--记数
usercount :=usercount+1;
--判断两个计数器变量的值，如果从员工信息中取出的记录数大于年份的个数，
--表示该员工的记录已经取完，将计数器变量清空，字符串变量清空，
--以便取下一个员工的信息
if usercount>yearcount then
dbms_output.put_line(usercontent);
usercount:=0;
usercontent :='';
end if;
end loop;
close cur1;
close cur2;
end;
--带参数的游标
declare
cursor paramCur(cno number) is select * from scott.emp where empno=cno;
crow scott.emp%rowtype;
begin
open paramCur(7499);
loop
fetch paramCur into crow;
exit when paramCur%notfound;
end loop;
dbms_output.put_line(crow.ename);
close paramCur;
end;
--对过程的认识
create or replace procedure firstPro
as
num number;
begin
select count(*) into num from scott.emp;
dbms_output.put_line(num);
end firstPro;
begin
firstPro();
end;
--过程的参数
create or replace procedure firstPro(num in scott.emp.empno%type,
pname out scott.emp.ename%type) as
cursor paramCur is select * from scott.emp where empno=num;
rowData scott.emp%rowtype;
begin
open paramCur;
loop
fetch paramCur into rowData;
exit when paramCur%notfou
nd;
pname := rowData.ename;
end loop;
close paramCur;
end firstPro;
declare
tname scott.emp.ename%type:='null';
tno scott.emp.empno%type:=7499;
begin
execute firstPro(tno,tname);
dbms_output.put_line(tname);
end;
--函数的认识
create or replace Function firstFun(num in scott.emp.empno%type)
return scott.emp.ename%type
as
fname scott.emp.ename%type;
cursor fdata is select * from scott.emp where empno=num;
rowdata scott.emp%rowtype;
begin
open fdata;
loop
fetch fdata into rowdata;
exit when fdata%notfound;
fname := rowdata.ename;
end loop;
close fdata;
return fname;
end firstFun;
begin
dbms_output.put_line(firstFun(7499));
end;
select firstFun(7499) from dual;
--过程函数互相调用
create or replace Function firstFun(fno in scott.emp.empno%type)
return scott.emp.ename%type
as
fname scott.emp.ename%type;
begin
firstPro(fno,fname);
return fname;
end firstFun;
select firstFun(7499) from dual;
--包的认识
create or replace package firstPack as
--全局变量
fname scott.emp.ename%type;
--类型声明
type my_cur1 is ref cursor;
--游标声明
cursor my_cur2(cno scott.emp.empno%type) is select * from scott.emp where empno=cno;
--函数声明
Function get_my_cur1 return my_cur1;
--过程声明
procedure get_my_cur2(pno in scott.emp.empno%type);
end;
--包体的认识
create or replace package body firstPack as
Function get_my_cur1 return my_cur1 as
imp_my_cur1 my_cur1;
begin
open imp_my_cur1 for select * from scott.emp;
return imp_my_cur1;
end get_my_cur1;
procedure get_my_cur2(pno in scott.emp.empno%type)
as
rowData scott.emp%rowtype;
begin
open my_cur2(pno);
loop
fetch my_cur2 into rowData;
exit when my_cur2%notfound;
end loop;
fname := rowData.ename;
close my_cur2;
end get_my_cur2;
end firstPack;
select firstPack.get_my_cur1 from dual;
begin
firstPack.get_my_cur2(7499);
dbms_output.put_line(firstPack.fname);
end;
--触发器认识-语句级触发器
create table mylog(
eventname varchar2(20),
updateobject varchar2(20),
oldvalue varchar2(20),
newvalue varchar2(20),
updatedate date default sysdate
);
drop trigger firsttrigger;
select * from myLog;
-------触发器
create or replace trigger firsttrigger
before update on scott.emp
begin
:old :new
insert into myLog values('update','scott.emp','old','new',sysdate);
end;
-------
select * from scott.emp where empno = 7499;
update scott.emp set sal=4000 where empno=7499 or empno=7369;
--行级触发器
create or replace trigger firsttrigger
before update on scott.emp
for each row when (new.sal>=old.sal*1.25)
begin
dbms_output.put_line('更新的员工编号为：'||:old.empno);
dbms_output.put_line('更新的员工姓名为：'||:old.ename);
dbms_output.put_line('更新之前的员工工资为：'||:old.sal);
dbms_output.put_line('更新之前的员工工资为：'||:new.sal);
end;
create view Vmylog as select * from MYLOG;
--替代触发器
creat
e or replace trigger firsttrigger
instead of insert on Vmylog
begin
dbms_output.put_line(:new.eventname);
end;
drop trigger firsttrigger;
insert into Vmylog values('ooo','sdf','100000','null',to_date('20090909','yyyymmdd'));
select * from Vmylog;
--L7课外练习，分割字符串
/*
拆分一个连续字符串(提示用存储过程循环拆分)
declare @s as varchar(100)
set @str='ph,p,cod,do,cu'
要求得到结果：
ph
p
cod
do
cu
*/
--解题思路1：过程的递归调用
--分析题目中的规律：每次截取字符串中分隔符前面的部分
--写出一个相应的方法，然后递归调用自己，就可以完成该功能
create or replace procedure pchar(target in varchar2,sym in varchar2)
as
/*
参数说明
target 被分割的字符串
sym 分隔符
*/
startWith number :=1;--字符串开始位置
endWith number :=1;--字符串结束位置
symposition number:=1;--分隔符所在的位置
begin
symposition := instr(target,sym,1);--从字符串开始部分查找分隔符
if symposition > startwith then--如果分隔符位置大于开始位置（1），则表明已经找到分给
endWith := symposition -1;--需要截取的字符串就分隔符所在位置的前一个
dbms_output.put_line(substr(target,startWith,endWith));--截取字符串输出
pchar(substr(target,symposition+1),sym);--截取剩下的部分递归调用自己
elsif symposition = startWith then--如果分隔符的位置等于表示字符串开头就是分隔符
pchar(substr(target,symposition+1),sym);--截取剩下的部分递归调用自己
elsif symposition < startWith then--如果小于1表示该字符串中没有分隔符
dbms_output.put_line(target);--输出已经没有分割符的字符串
end if;
end pchar;
--解题思路2：循环截取 参数说明同上
create or replace procedure pchar(target in varchar2,sym in varchar2)
as
tlength number:=0;--字符串的总长度
startWith number:=1;--截取字符串时的开始位置
sstr varchar2(200);--存储截取后的字符串
symposition number:=1;--分隔符所在的位置
begin
tlength := Length(target);
loop
symposition := instr(target,sym,startWith);
sstr := substr(target,startWith,symposition-startWith);
if symposition = 0 then
dbms_output.put_line(substr(target,startWith,tlength-startWith+1));
exit;
end if;
dbms_output.put_line(sstr);
startWith :=symposition+1;
end loop;
end pchar;
declare
target varchar2(200):='ph,p,cod,do,cu';
sym varchar2(2):=',';
begin
pchar(target,sym);
end;
--L7课外练习 行列合并
/*
2. 行列转换--合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
（提示：创建一个合并的函数）
*/
--分析题目：要求创建一个合并函数，用在查询中调用的函数
--该函数可根据id查到表中所有的pid值，并拼接成一个字符串返回
create table A (
id number,
pid varchar2(10)
)
begin
insert into
A values(1,1);
insert into A values(1,2);
insert into A values(1,3);
insert into A values(2,1);
insert into A values(2,2);
insert into A values(3,1);
end;
create or replace function crow (tid in A.Id%type)
return varchar2
as
cursor mycur is select * from A where id = tid;
rowdata A%rowtype;
resultStr varchar2(200);
begin
open mycur;
loop
fetch mycur into rowdata;
exit when mycur%notfound;
resultStr :=resultStr||rowdata.pid||',';
end loop;
close mycur;
return resultStr;
end crow;
select distinct id,crow(id) as pid from;
--触发器
/*
3. 员工表（员工ID，姓名，工资，状态），帐号表（员工ID，用户名，密码，状态）
编写一个触发器，如果员工状态变为离职，通过触发器将其帐号变为禁用
*/
--思路：利用行级触发器，监听员工表中的update事件
--如果某些行发生变化，则取出相应的行更新后的数据，来更新账号表
create table employee(
empid number,
ename varchar2(20),
sal number(6,2),
status varchar2(1)
)
create table eaccount(
empid number,
username varchar2(20),
epassword varchar2(20),
status varchar2(1)
)
begin
insert into employee values(1,'andy',1000.00,'Y');
insert into employee values(2,'tom',1100.00,'Y');
insert into employee values(3,'jim',1200.00,'Y');
insert into eaccount values(1,'andy','111111','Y');
insert into eaccount values(2,'tom','111111','Y');
insert into eaccount values(3,'jim','111111','Y');
end;
create or replace trigger changeStatus
after update on employee for each row
begin
update eaccount set status = :new.status where empid = :new.empid;
end;
select * from employee;
select * from eaccount;
update employee set status = 'N' where empid=1;
--触发器2
/*
4. 已知book(id, name ,price)表，如何实现向表book中插入纪录的时候id自动增长
*/
--思路：比较麻烦的做法：为表创建视图
/*
创建表
创建序列用来自增
创建触发器，替换视图的insert动作，在触发器中利用序列的自增追加到book表中
*/
create table book(
bid number,
bname varchar2(100),
price number(4,2)
)
create view Vbook as select * from book;
drop sequence bookseq;
create sequence bookseq
minvalue 1
start with 1
increment by 1
nocycle;
drop trigger rowseq;
create or replace trigger rowseq
instead of insert on Vbook
begin
insert into book values(bookseq.nextval,:new.bname,:new.price);
end;
select * from book;
insert into Vbook (bname,price)values('java',10.00);