数据库配置文件已经安装到 D:\oracle\product\10.2.0,同时其他选定的安装组件也已经安装到 D:\oracle\product\10.2.0\db_1。iSQL*Plus URL 为:http://mys:5560/isqlplusiSQL*Plus DBA URL 为:http://mys:5560/isqlplus/dba
plsqldev-trial.exe
toad for oracle
-----------------------------------------------------------------------------------------------------------------------
创建用户
SQL> create user drp1 identified by drp1;
创建表空间
SQL> desc dba_users;
SQL> select username, DEFAULT_TABLESPACE from dba_users;
将drp1的表空间修改为ts_drp1
SQL> alter user drp1 default tablespace ts_drp1;
给用户drp1授权
SQL> grant create session, create table, create view, unlimited tablespace to drp1;
SQL> show user;
User is "SYS"
切换用户到drp1
SQL> conn drp1/drp1;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as drp1
导入sql脚本
SQL> show user;
User is "drp1"
SQL> @D:\share\05_DRP\sql_script\drp_create.sql;
**--**--**--^ java 编程 jdbc ^--**--**--**
--------------------------------------------------------------------------------------------------------
Class.forName("oracle.jdbc.driver.OracleDriver");
// new oracle.jdbc.driver.OracleDriver();
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from dept");
while(rs.next()){
System.out.println(rs.getString("deptno"));
System.out.println(rs.getInt("deptno"));
}
--------------------------------------------------------------------------------------------------------
//增加序列值，获取序列值
conn sys/tiger as sysdba;
grant create SEQUENCE TO itcast;
conn itcast/itcast;
create sequence studentPKSequence start with 1 increment by 1;
select studentpksequence.nextval from dual;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
sqlplus sys/密码(我的是tiger） as sysdba
SQL> alter user scott account unlock;
ed 进入记事本方式的编辑模式 最后不加分号，退出保存后 / 回车运行
set serveroutput on;
第3节 desc emp
desc dept
desc salgrade
select * from salgrade;
select * from dept;
select * frome emp;
select ename, sal*12 from emp;
desc dual
select sysdate from dual;
第4节 select
select ename, sal*12 anuual_sal from emp;
select ename, sal*12 "anuunal sal" from emp;
select ename,comm from emp; //空值与0不同
select ename, sal*12+comm from emp;//含有任何空值的数学表达式都是空值
select ename||sal from emp;
select ename || 'kjhkjsdkgsdgfsk' from emp;//连接数据和字符串
select ename || 'dklhhg''dkjhksehf' from emp;//连接数据和两个不同的字符串，用两个单引号分开
第5节 distinct
select distinct deptno from emp;//重复的数据不显示
select distinct deptno , job from emp;//两个重复的组合不显
第6节 where
select * from dept;
select * from emp;
select * from emp where deptno = 10;//将部门编码是10的成员显示出来
select * from emp where ename = 'CLARK';
select * from emp where sal > 1500;
select ename, sal from emp where deptno <>10;
select ename, sal, deptno from emp where deptno <> 10;
select ename, sal from emp where ename > 'CBA';
select sal from emp where sal between 800 and 1500;
select sal from emp where sal >= 800 and sal <= 1500;
select ename, sal, comm from emp where comm is null;//显示空值
select ename, sal, comm from emp where comm is not null;
select ename, sal, comm from emp where sal in (800, 1500, 2000);//取等于3者的值
select ename, sal, comm from emp where ename in ('SMITH', 'KING', 'ABC');
select ename, sal, hiredate from emp where hiredate > '20-2月-81'
select ename, sal from emp where deptno = 10 and sal >1000;
select ename, sal from emp where deptno = 10 or sal >1000;
select ename, sal from emp where dsal not in <800, 1500, 2000>;
select enmae from emp where ename like '%ALL%';
select ename from emp where ename like '_A%';
select enmae from emp where ename like '%\%%';
select enmae from emp where ename like '%$*%' escape '$';
第7节 order by
select * from dept;
select empno, ename from dept order by deptno desc;//按降序排列
select empno, ename from emp order by empno asc;//按升序排列 asc 可以不写
select empno, ename from emp where deptno <> 10 order by empno asc;//deptno 不等于10
select ename, sal, deptno from emp order by deptno asc, ename desc;
第8节
select ename, sal*12 annual_sal from emp
2 where ename not like '_A%' and sal > 800
3 order by sal desc;
select lower(ename) from emp;//ename转换为小写
select ename from emp where lower(ename) like '_a%';
select ename from emp where ename like '_a%' or ename like '_A%';
select substr(ename,2,3) from emp;//从第二个字符开始，一共截3个字符；
select chr(65) from dual;//把ansiic编码转换为字母
select ascii('A') from dual;
select round(23.652) from dual;
select round(23.653,2) from dual;//四舍五入到2位小数；
select round(23.653,-1) from dual;//四舍五入到个位数；
**** select to_char(sal,'$99,999.9999') from emp;//转换输出类型
**** select to_char(sal,'L99,999.9999') from emp;//转换输出类型，本地货币
select to_char(sal,'L00,000.0000') from emp;//转换输出类型，本地货币
select hiredate from emp;
select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;//12进制显示时间
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from emp;//24进制显示时间
第9节
select ename, hiredate from emp where hiredate > '1921-2-20 12:34:56';
**** select ename, hiredate from emp where hiredate >
2 to_date<'1921-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal > 888.88;
**** select sal from emp where sal > to_number('$1,250.00','$9,999.99');
**** select ename, sal*12 + nvl(comm,0) from emp;//将空值转换为0；
第10节 组函数
select max(sal) from emp;
select min(sal) from emp;
select avg(sal）from emp;
select to_char(avg(sal), '9,999.99') from emp;
select round(avg(sal),2) from emp;
select sum(sal) from emp;
***** select count(*) from emp;
select count(*) from emp where deptno = 10;
select count(ename) from emp;
select count(comm) from emp;
select count(distinct deptno) from emp;
第11节 group
select sal, deptno from emp;
select avg(sal) from emp group by deptno;//分组函数
select deptno, avg(sal) from emp group by deptno;
select max(sal) from emp group by deptno, job;
select deptno, job, max(sal) from emp group by deptno, job;
select max(sal) from emp;
select ename from emp where sal = (select max(sal) from emp);
select deptno, max(sal) from emp group by deptno;
//出现在select列表中的字段没有出现在组函数里，就必须出现在group by 中
第12节 having
select avg(sal),deptno from emp group by deptno;
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;//对分组进行限制
select from where group by having order by
select avg(sal) from emp where sal > 1200 group by deptno
2 having avg(sag) >1500 order by avg(sag) desc;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第13节 子查询
select ename from emp where sal = (select max(sal) from emp);
select sal from emp where sal >(select avg(sag)from emp);
--求部门中哪些人的薪水最高
select ename, sal,deptno from emp
2 join (select max(sal) max_sal, deptno from emp group by deptno) t on
3 (emp.sal = t.max_sal and emp.deptno = t.deptno);
第14节
select empno, enam, mgr from emp;
select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
第15节
select ename, dname, grade d from emp e, dept d, salgrade s
2 where e.deptno = d.deptno and e.sal between s.losal and s.hisal and
3 job <> 'CLERK'；
等值连接
select ename ,dname from emp dept;
select ename, dname from emp cross join dept;//笛卡尔乘积 cross join
select ename, dname from emp, dept where emp.deptno = dept.deptno;
select ename, dname from emp join dept on(emp.deptno = dept.deptno);
select ename, dname from emp join dept using (deptno);
非等值连接
select ename, grade from emp e join salgrade s on ( e.sal between s.losal and s.hisal);
select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s
2 on (e.sal between s.losal and s.hisal) where ename not like '_A%';
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
select e1.ename, e2.ename from emp e1 left outer join emp e2 on//左外连接
2 (e1.mgr = e2.empno);
select * from dept;
select ename, dname from emp e right join dept d on(e.deptno = d.deptno);//右外连接
select ename, dname from emp e full join dept d on (e.deptno = d.deptno);//全外连接
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第16节
--求部门平均薪水的等级
select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t
2 join salgrade s on (t.avg_sal between s.losal and s.hisal)
第17节
--求部门平均薪水的等级
select avg(grade) from (select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal))t group by deptno
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第18节
--雇员中哪些人事经理人
select ename from emp where empno in(select mgr from emp);
select ename from emp where empno in(select distinct mgr from emp);
第19节
--不准用组函数，求薪水的最高值
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal< e2.sal) );
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第20节
--求平均薪水最高的部门的部门编号
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select deptno, avg(sal) avg_sal from emp group by deptno)
)
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno)
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
--求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select deptno, avg(sal) avg_sal from emp group by deptno)
)
)
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第22节
---求平均薪水的等级最低的部门的部门名称
select dname, t1.deptno, grade, avg_sal from
(
select deptno,grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno,grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
)
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第23节
conn sys/tiger as sysdba;
grant create table, create view to scott;
conn scott/tiger
create view v&_dept_avg_sal_info as
select deptno,grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
select dname, t1.deptno, grade, avg_sal from
v&_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
select min(grade) from v&_dept_avg_sal
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第24节
--求部门经理人中平均薪水最低的部门名称
--求比普通员工最高薪水还要高的
select ename from emp
where empno in ( select distinct mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
)
第25节 create_new_user_and_insert
conn sys/tiger sysdba;
drop user liuchao;
cmd D:\>cd javab
cmd D:\javab>exp
cmd 用户名: scott/tiger
sqlplus create user liuchao identified by liuchao default tablespace users quota 10M on users;
grant create session, create table, create view to liuchao;
cmd D:\>cd javab
D:\javab>imp
用户名: liuchao/liuchao
sqlplus desc dept;
insert into dept values (50, 'game', 'bj');
select * from dept;
rollback;
create table dept2 as select * from dept;
create table salgrade2 as select * from salgrade;
create table emp3 as select * from emp;
insert into dept2 values (50,'game','bj');
insert into dept2 (deptno,dname) values (60, 'game2);
insert into dept2 select * from dept;
第26节
SQL面试题
有3个表S,C,SC
S(SNO,SNAME)代表（学号，姓名）
C(CNO,CNAME,CTEACHER)代表（课号，课名，教师）
SC(SNO,CNO,SCGRADE)代表（学号，课号成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
select sname from s join sc on (s.sno = sc.sno)join c (c.cno = sc.cno) where c.cteacher <> 'liming';
2，列出2门以上（含2门）不及格学生的姓名和平均成绩
select * where sno in (select sno from sc where scgrade < 60
group by sno having count (*) >=2);
3，即学过1号课程又学过2号课所有学生的姓名
select sname from s where sno in (select sno from sc where cno = 1 and cno
in ( select distinct sno from sc where cno =2));
update emp2 set sal = sal*2, ename = ename||'-' where deptno < 25;
select ename, sal from emp2 where deptno = 10;
delete from dept2 where deptno = 10;
rollback;
create table t (a varchar2(10));
desc t;
drop table t;
update emp2 set sal = sal*2;
delete from dept2;
insert into salgrade values (6,1000,2000);
rollback;//回退3行
update emp2 set sal = sal*2;
committ;//提交
rollback;//已经提交，不能回退
update dept2 set deptno = deptno * 2;
create table t (a varchar2(20));
rollback;//已执行ddl语句，不能回退 dcl语句（grant授权语句），也不能回退，自动提交
select * from dept2//正常断开链接，自动提交，非正常断开，非自动提交。
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第27节 create_table
create table stu
(
id number(6) ，
name varchar2(20)，
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50)
)
第28节
insert into stu(name, email) values ('a','a');
insert into stu(name, email) values ('b','b');
第29节
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,//键值约束
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) unique,
constraint stu_class_fk foreign key (class) references class(id),//外键约束
constraint stu_id_pk primary key(id),//主键约束（非空 唯一）
constraint stu_name_email_uni unique(email,name)//表级约束
)
create table class
(
id number(4) primary key,//被参考的外键必须是主键
name varchar2(20) not null
)
第30节
第31节
alter table stu add(addr varchar2(100));
desc stu;
alter table stu drop (addr);
alter table stu add(addr varchar2(100);
alter table stu modify(addr varchar2（150));
alter table stu modify (addr varchar2(50));
delet from class ;//违反约束条件
alter table stu drop constraint stu_class_fk;//修改约束条件
delete from class;
rollback;
alter table stu add constraint stu_class_fk foreign key(class) references class (id);
drop table stu;
第32节
desc user_tables;//数据字典表
select table_name from user_tables;//表名字
select view_name from user_views;//视图名字
select constraint_name from user_constraint//约束名字;
select constraint_name, table_name from user_constraints;
desc dictionary;//数据字典表的表
select table_name from dictionary where table_name like 'USER%';
第33节 索引 视图
create index idx_stu_email on stu (email);
drop index idx_stu_email;
select index_name from user_indexes;//查看索引名字
select view_name from user_views;
desc v$_dept_avg_sal_info;
create view v$_stu as select id,name, age from stu;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第34节 序列
create table article
(
id number,
title varchar2(1024),
cont long
)
create sequence seq;
select seq.nextval from dual;
insert into article values (seq.nextval,'a','b');
第35节 三范式（在同一个表中）
第一范式 要有主键 列不可分
第二范式 组合主键，不存在非主键依赖部分主键
第三范式 不能存在传递依赖
第36节 BBS
第37节
第38节 PL_SQL
set serveroutput on;
begin
dbms_output.put_line('HelloWorld');
end;
/
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第39节
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
/
declare
v_num number :=0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
/
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第40节
----变量声明的规则
1，变量名不能使用保留字，如from、select等
2，第一个字符必须是字母
3，变量名最多包含30个字符
4，不要与数据库的表或者列同名
5，每一行只能声明一个变量
----常用变量类型
1，binary_integer :整数，主要用来计数而不是用来表示字段类型
2，number：数字类型
3，char：定长字符串
4，varchar2：变长字符串
5，date：日期
6，long 长字符串，最长2GB
7，boolean：布尔类型，可以取值为true，false和null值
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
----变量声明
declare
v_temp number(1);
v_count binary_integer :=0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName';
begin
dbms_output.put_line('v_temp value:'||v_count);
end;
show user;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
--变量声明，使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第41节
---Table变量类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第42节
--Record变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno :=50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
--使用%rowtype声明record变量类型
declare
v_temp dept%rowtype;
begin
v_temp.deptno :=50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第43节
PLSQL语句输出只能且必须输出一条语句
--SQL语句的运用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename ||' '||v_sal);
end;
--SQL语句的运用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
---insert语句
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
----%rowcount语句
declare
v_deptno emp2.deptno%type := 10;
v_count number;
begin
update emp2 set sal = sal/2 where deptno = v_deptno;
--select deptno into v_deptno from emp2 where empno = 7369;//1条语句被影响
--select count(*) into v_count from emp2; //1条语句被影响
dbms_output.put_line(sql%rowcount ||'条语句被影响');//3条语句被影响
commit;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第44节
DML语句 insert delete update
DDL语句 数据定义语言 create create table
DCL语句 授权语句grant to
begin
execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
--if语句
--取出7369的薪水，如果<1200，则输出'low',如果<2000则输出'middle',否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif (v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp2
where empno = 7839;
if(v_sal <2500) then
update emp2 set sal = sal*2;
elsif(v_sal > 2500 ) then
update emp2 set sal = sal/2;
else
dbms_output.put_line(v_sal);
end if;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第45节
--循环
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when ( i >=11);
end loop;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第46节 异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
commit;
end;
select * from errorlog;
select to_char(errdate, 'YYYY-MM-DD HH24:MI:SS') from errorlog;//查看出错时间
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第47节 游标cursor
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
cursor属性 isopen //打开了，则返回true
notfound //没有打开，则返回true
found //找到了，则返回true
rollcount //fetch到多少条记录，则返回多少条记录
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when (c%notfound);
dbms_output.put_line(v_emp.ename);//若这条语句在exit之前，则最后一条记录为没有时，打印上一条记录。
end loop;
close c;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第48节
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while (c%found） loop
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);//若这条语句在exit之前，则最后一条记录为没有时，打印上一条记录。
end loop;
close c;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
----带参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
--v_temp c%rowtype;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
/
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
declare
cursor c
is
select * from emp2 for update;
--v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
/
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第49节 创建存储过程
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp2 set sal = sal + 10 where current of c;
elsif (v_emp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
exec p;//执行语句
begin
p;
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number )
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp +1;
end;
show error//编译错误时，显示错误
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number :=5;
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第50节
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
select lower(ename), sal_tax(sal) from emp;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
触发器
create or replace trigger trig
after insert or delete or update on emp2
begin
if inserting then
insert into emp2_log values (USER,'insert',sysdate);
elsif updating then
insert into emp2_log values (USER,'update',sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
/
update emp2 set sal = sal*2 where deptno = 30;
select * from emp2_log;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create or replace trigger trig
after insert or delete or update on emp2 for each row//每一行的操作都加一个触发器
begin
if inserting then
insert into emp2_log values (USER,'insert',sysdate);
elsif updating then
insert into emp2_log values (USER,'update',sysdate);
elsif deleting then
insert into emp2_log values (USER, 'delete', sysdate);
end if;
end;
update emp2 set sal = sal*2 where deptno = 30;
select * from emp2_log;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第51节
drop trigger trig;
update dept set deptno = 99 where deptno = 10;//违反约束条件，不能修改
create or replace trigger trig //创建触发器
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
update dept set deptno = 99 where deptno = 10; //违反约束条件，但可以更改
select deptno from dept;
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
第52节
--树状结构的存储与展示
drop table article;
create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),--0 代表非叶子节点，1代表叶子节点
alevel number(2)
)
insert into article values(1,'蚂蚁大战大象',0,0,0);
insert into article values(2,'大象被打趴下',1,0,1);
insert into article values(3,'蚂蚁也不好过',2,1,2);
insert into article values(4,'瞎说',2,0,2);
insert into article values(5,'没有瞎说',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么没有可能',6,1,2);
insert into article values(8,'可能行很大的',6,1,2);
insert into article values(9,'大象进医院了',2,0,2);
insert into article values(10,'蚂蚁是护士',9,1,3);
create or replace procedure p (v_pid article.pid%type) is
cursor c is select * from article where pid = v_pid;
begin
for v_article in c loop
dbms_output.put_line(v_article.cont);
if (v_article.isleaf = 0) then
p (v_article.id);
end if;
end loop;
end;
/
exec p(0);
**********************************************************
显示结果为：
蚂蚁大战大象
大象被打趴下
蚂蚁也不好过
瞎说
没有瞎说
大象进医院了
蚂蚁是护士
怎么可能
怎么没有可能
可能行很大的
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
for i in 0..v_level loop
v_preStr := v_preStr ||'****';
end loop;
for v_article in c loop
dbms_output.put_line(v_preStr||v_article.cont);
if (v_article.isleaf = 0) then
p (v_article.id,v_level + 1);
end if;
end loop;
end;
exec p(0,0);
显示结果为：
蚂蚁大战大象
****大象被打趴下
********蚂蚁也不好过
********瞎说
************没有瞎说
********大象进医院了
************蚂蚁是护士
****怎么可能
********怎么没有可能
********可能行很大的
-------------------------------------------------------------------------------------
*************************************************************************************
-------------------------------------------------------------------------------------
create table student(
sid number constraint sid not null,
sname varchar2(20),
magor varchar2(20),
birth date,
score float(126)
);
浙公网安备 33010602011771号