一．基本概念
Oracle驱动用oracle安装目录可以找到oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar
1.数据
数据就是信息
2.数据处理
对各种形式的数据进行收集，存储，加工和传播的行为
3.数据管理
对数据库进行分类，组织，存储和维护等操作
4.数据库
存储数据的仓库，存储一些互有联系的数据的集合 5.1人工管理 5.2文件管理 5.3数据库系统 5.数据库的发展
6.数据库管理软件
6.1 sqlserver 微软 中型项目
6.2 access 微软 小型项目 6.3 mysql 小型项目 6.4 oracle 大型项目
cmd下输入sqlplus
1.输入用户名：sys@oracle as sysdba
2.输入用户名：sys/double@oracle as sysdba
7.sys：超级管理员
system：管理员 连接方式选择 normal
8.数据库的层次结构
数据库
表空间
表
二．创建权限
1.
--创建表空间
--datafile 表空间对应的一个或者多个文件，如果有多个文件用 逗号分隔
--size 表空间的大小
--autoextend on 自动增长
CREATE TABLESPACE MH
DATAFILE 'd:\work01.dbf' SIZE 5M AUTOEXTEND ON;
2. --创建用户
--create user 指定用户名
--iDENTIFIED BY 指定密码
--DEFAULT TABLESPACE 指定默认的表空间，如果不指定，使用系统的默认表空间
SYSTEM
CREATE USER MH001
IDENTIFIED BY MH123456
DEFAULT TABLESPACE MH;
3.给用户分配权限
--connect 允许用户使用数据库并在数据库中创建其他对象
--resource 允许用户使用数据库中的空间
GRANT CONNECT, RESOURCE TO MH001;
GRANT SELECT, UPDATE, INSERT ON scott.emp TO MH001;
赋予管理员权限
Connect as要选择sysdba，然后grant sysdba to admin;
4. --修改用户名密码
ALTER USER MH001
IDENTIFIED BY MH12345;
5. --删除用户
DROP USER MH001;
6. --回收权限
REVOKE SELECT ON scott.emp FROM MH001;
三．数据类型
--字符串数据类型
--char:存放固定长度的字符串，范围 1-2000字节,如果不指定大小默认1个字节,效率高 --varchar2:存放可变长度的字符串,范围 1-4000字节,必须指定大小,节约磁盘空间
--数字类型
--number(p,s):p 表示精度(数字的总位数，最大38) ，s表示范围，表示小数点右边的数字的位数
--存放整数：number 表示位数为38位的整数，number(9) 表示位数为9位的整数
--存放小数：number(10,2)
--日期类型
--date '1980-05-06' '1980/05/56' '05/12/1980'
四．创建表
--primary key 设置主键，主键的值不允许重复，每张表只能有一个主键。
--not null 必须填写.
CREATE TABLE stuinfo
(
sno NUMBER PRIMARY KEY,
sname VARCHAR2(10) NOT NULL,
ssex CHAR(2) ,
sage NUMBER(3) NOT NULL,
address VARCHAR(50)
);
CREATE TABLE subject
(
sno NUMBER PRIMARY KEY,
subname VARCHAR(20)
);
CREATE TABLE score
(
sno NUMBER ,
subject NUMBER(20),
score NUMBER(5,2)
)
--插入日期
insert into test values(to_date('2010-08-08 14:'10:20, 'yyyyMMdd HH24:mi:ss' ));
五．约束
--主键约束,将2个字段设为一个主键
ALTER TABLE score ADD CONSTRAINT PK_sno_subject PRIMARY KEY(sno,subject); --设置外键约束
ALTER TABLE score ADD CONSTRAINT FK_sno_sno FOREIGN KEY(sno) REFERENCES stuinfo(sno);
ALTER TABLE score ADD CONSTRAINT FK_subject_sno FOREIGN KEY(subject) REFERENCES subject(sno);
--唯一约束(unique)
ALTER TABLE stuinfo ADD CONSTRAINT uq_sage unique(sno);
--检查约束(check)
ALTER TABLE stuinfo ADD CONSTRAINT CK_sage CHECK(sage>=20 AND sage<=80); ALTER TABLE stuinfo ADD CONSTRAINT CK_ssex CHECK(ssex='男' OR ssex='女'); --默认约束
在定义表的时候创建
CREATE TABLE test
(
sname VARCHAR2(10) DEFAULT ('a') not null
);
INSERT INTO test VALUES(DEFAULT);
--撤销约束
alter table student drop constraint ck_student;
六．简单查询
--合并列，取别名
SELECT ename || '_' || job AS ejob FROM scott.emp;
--运算
SELECT sal*2 sal FROM scott.emp;
--与(and)、或(or)、非(not)
--查询年龄等于20岁的男同学姓名
SELECT sname FROM stuinfo WHERE sage=20 AND ssex='男';
--查询年龄大于20，或者性别为男的学生信息;
SELECT * FROM stuinfo WHERE sage>20 OR ssex='男';
--查询年龄不大于20的学生信息
SELECT * FROM stuinfo WHERE sage<=20;
SELECT * FROM stuinfo WHERE NOT sage>20;
--查询年龄在20-21之间的学生信息
SELECT * FROM stuinfo WHERE sage>=20 AND sage<=21;
SELECT * FROM stuinfo WHERE sage BETWEEN 20 AND 21;
--查询年龄为20，21，25的学生信息
SELECT * FROM stuinfo WHERE sage=20 OR sage=21 OR sage=25;
SELECT * FROM stuinfo WHERE sage IN (20,21,25);
--通配符
-- _ 表示有且仅有一个任意字符 _abc 第一个字符时任意字符，后面3个是abc
-- % 表示0个到多个任意字符 '张%' 以张开头
-- 包含 '张' 字符 '%张%'
--模糊查询
--查询姓张的学生信息
SELECT * FROM stuinfo WHERE sname LIKE '张%';
--查询NULL值
SELECT * FROM stuinfo WHERE ssex IS NULL;
SELECT * FROM stuinfo WHERE ssex IS NOT NULL;
--按照年龄升序排列,order by排序 asc升序 desc降序
SELECT * FROM stuinfo ORDER BY sage ASC;
SELECT * FROM stuinfo ORDER BY sage DESC;
--按照年龄升序排列，如果年龄相同，就按照学号升序排序
SELECT * FROM stuinfo ORDER BY sage ASC,sno DESC;
--去除重复值
SELECT DISTINCT sage FROM stuinfo;
连接
表a有5个字段，表b有3个字段，则一般的链接有5*3=15个字段。
--内链接,只显示都有的
SELECT * FROM scott.emp se INNER JOIN scott.dept sd ON se.deptno=sd.deptno;
--左外连接，以左边的表为标准
SELECT * FROM scott.emp se LEFT JOIN scott.dept sd ON se.deptno=sd.deptno;
--右外连接，以右边的表为标准
SELECT * FROM scott.emp se RIGHT JOIN scott.dept sd ON se.deptno=sd.deptno;
--多表连查
SELECT se.*,sd.dname FROM scott.emp se,scott.dept sd WHERE se.deptno=sd.deptno AND sd.dname='SALES';
--子查询
SELECT se.*,sd.dname FROM scott.emp se WHERE se.deptno=(SELECT sd.deptno FROM scott.dept sd WHERE sd.dname='SALES');
--查询语句顺序
select 列名 from 表名 连接名 where 条件 grop by 列名 order by 列名 having 分组后条件
--查寻工资最高的前三条记录
select * from (select * from student order by sc desc) where rownum<=3;
--分组后再加条件用having
select sum(score),sno from score group by sno having sum(score)>100
--decode的用法 小王-王晶 小李-李连杰 其他-王力宏
decode(sname,'小王','王晶','小李','李连杰','王力宏');
七．函数
--转换ASCII码
SELECT ASCII('a') FROM dual;
--连接字符串
SELECT concat('abc','def') FROM dual;
SELECT 'abc' || 'def' FROM dual;
--求字符串长度
SELECT length('abcdfe') FROM dual;
SELECT length('我是') FROM dual;
--去除左边的空格
SELECT '[' || ltrim(' abc') FROM dual;
--去除右边的空格
SELECT '[' || rtrim('abc ') || ']' FROM dual;
--同时去除左右两边的空格
SELECT rtrim(ltrim(' abc ')) FROM dual;
--替换字符串
SELECT REPLACE('alblb1','1','l') FROM dual;
SELECT REPLACE('abcdaac','a','e') FROM dual;
--返回大于或等于给出数字的最小整数
SELECT CEIL(32.5) FROM dual; --33
--对给定的数字取整数
SELECT FLOOR(32.5) FROM dual; -- 32
SELECT floor(-32.5) FROM dual; -- -33
--按照指定的精度进行舍入
SELECT round(32.5) FROM dual; --33
SELECT round(32.4) FROM dual; --32
--截断,(和floor类是)
SELECT TRUNC(32.5) FROM dual;
SELECT trunc(-32.5) FROM dual;
SELECT trunc(32.4445,2) FROM dual;
--日期函数
--增加或减去月份
SELECT add_months(SYSDATE,3) FROM dual;
SELECT add_months(to_date('1985-02-04','yyyy-MM-dd'),2) FROM dual;
SELECT * FROM scott.emp;
SELECT add_months(hiredate,1) FROM scott.emp;
--返回日期的最后一天
SELECT last_day(SYSDATE) FROM dual;
--两个日期相差的月份
SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM scott.emp;
--给出日期date和星期x之后 计算下一个星期的日期
SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual;
--得到系统当前日期
SELECT SYSDATE FROM dual;
--将字符串转换成数字
SELECT TO_NUMBER('29854.2323') FROM dual;
SELECT to_number('34.56','99999.9999') FROM dual;
--条件函数
SELECT * FROM scott.emp;
SELECT decode(deptno,30,'市场部',20,'学术部',10,'电脑部'),deptno FROM scott.emp; --与空值（NULL） 相关
SELECT * FROM scott.emp;
SELECT * FROM stuinfo;
SELECT sname, nvl(address,'没得地方') address FROM stuinfo;
--不为空替换为有地方，为空替换为没得地方
SELECT nvl2(address,'有地方住','没的地方住') FROM stuinfo;
INSERT INTO stuinfo VALUES(1,'a','男',20,NULL);
--统计(聚合)函数(必须记住)
--最大值 ：查询最高工资是多少
SELECT MAX(sal) FROM scott.emp;
--最小值
SELECT MIN(sal) FROM scott.emp;
--平均值
SELECT AVG(sal) FROM scott.emp;
--求和
SELECT SUM(sal) FROM scott.emp;
SELECT SUM(sal) FROM scott.emp WHERE sal>1500;
--统计记录条数
SELECT COUNT(*) FROM scott.emp WHERE sal>1500;
------------分组(group by)
SELECT * FROM score;
--求每一门课程的平均成绩
SELECT AVG(score),subject FROM score GROUP BY subject;
--求每一个学生的总成绩
SELECT SUM(score),sno FROM score GROUP BY sno;
--求总成绩大于100分的学生,分组之后的条件只能有 having
SELECT SUM(score),sno FROM score GROUP BY sno HAVING SUM(score)>100;
八．数据库对象
--同义词
--私有的同义词
--创建或者替换某个同义词
CREATE OR REPLACE SYNONYM emp FOR scott.emp;
SELECT * FROM emp;
--共有同义词
CREATE OR REPLACE PUBLIC SYNONYM emp FOR scott.emp;
SELECT * FROM emp;
要授权才能用
GRANT SELECT ON syn TO user2;
--删除同义词
--删除共有的同义词
DROP PUBLIC SYNONYM emp;
--删除私有的同义词
DROP SYNONYM myemp;
SELECT * FROM stuinfo;
--序列
--start with 要生成序列的第一个序号
--increment by 指定序号之间的间隔
--maxvalue 序列的最大值
--minvalue 序列的最小值
--cycle 序列达到指定的最大或者最小值后从头开始生成 默认 nocycle不重新生成 --cache 缓存序号，nocache不缓存，不写默认缓存20个序号
CREATE SEQUENCE seq_stuinfo_sno
START WITH 1
INCREMENT BY 1
MAXVALUE 99
MINVALUE 1
--访问序号
--得到下一个序号
SELECT seq_stuinfo_sno.nextval FROM dual;
SELECT seq_stuinfo_sno.currval FROM dual;
INSERT INTO stuinfo VALUES(seq_stuinfo_sno.nextval,'aa','男',19,'重庆');
--更改序号
--不能修改start with参数
ALTER SEQUENCE seq_stuinfo_sno
MAXVALUE 10
CYCLE
CACHE 2;
--删除序列
DROP SEQUENCE seq_stuinfo_sno
--视图,临时表
CREATE OR REPLACE VIEW view_emp
AS SELECT * FROM scott.emp WHERE deptno=30;
SELECT * FROM view_emp;
SELECT * FROM scott.emp WHERE deptno=30;
UPDATE scott.emp SET sal=5000 WHERE empno=7499; SELECT * FROM (SELECT * FROM scott.emp); --删除视图
DROP VIEW view_emp;
--
九．PLSQL
--1.PLSQL语句块语法
DECLARE --声明部分
BEGIN --可执行代码
EXCEPTION--异常部分，不是必须的
END;
--例：
DECLARE
vno NUMBER := 30;
BEGIN
IF vno>20 THEN
dbms_output.put_line('vno:' || vno);
ELSE
dbms_output.put_line('vno:的值太小'); END IF;
END;
--2.PLSQL声明变量
--2.1 声明变量并赋值
DECLARE
vno NUMBER := '&vno';
BEGIN
dbms_output.put_line('vno=' || vno);
END;
--2.2 声明变量，后赋值
DECLARE
vno NUMBER;
vname VARCHAR2(20);
BEGIN
vno := 7369;
--将scott.emp中的ename赋给vname变量
SELECT ename INTO vname FROM scott.emp WHERE empno=vno; dbms_output.put_line('vno=' || vno);
dbms_output.put_line('vname=' || vname);
END;
--3.数据类型
--3.1 数字类型 NUMBER
--3.2 可变长度字符类型 VARCHAR2 最大的字节长度4000
-- 3.3 固定长度字符类型 CHAR 最大字节长度2000
-- 3.4 属性类型
--- %TYPE 和表里的字段对应
-- %ROWTYPE 和表里面的行对应
DECLARE
vname scott.emp.ename%TYPE;
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=7369; dbms_output.put_line('vname=' || vname);
END;
--4.控制结构
--4.1 条件控制
--4.1.1 简单if
DECLARE
vno NUMBER := '&vno';
BEGIN
IF vno>10 THEN
dbms_output.put_line('vno=' || vno);
END IF;
END;
-- 4.1.2 多重if
DECLARE
vno NUMBER := '&no';
BEGIN
IF vno=10 THEN
dbms_output.put_line('vno=10');
ELSIF vno=20 THEN
dbms_output.put_line('vno=20');
ELSE
dbms_output.put_line('vno=50');
END IF;
END;
-- 4.2 case语句
-- 4.2.1 使用选择器
DECLARE
vno NUMBER := '&vno';
BEGIN
CASE vno
WHEN 1 THEN
dbms_output.put_line('vno=1'); WHEN 2 THEN
dbms_output.put_line('vno=2'); WHEN 3 THEN
dbms_output.put_line('vno=3'); ELSE
dbms_output.put_line('vno=其他'); END CASE;
END;
-- 4.2.2 不使用选择器
DECLARE
vno NUMBER := '&vno';
BEGIN
CASE
WHEN vno=1 THEN
dbms_output.put_line('vno=1'); WHEN vno=2 THEN
dbms_output.put_line('vno=2'); WHEN vno=3 THEN
dbms_output.put_line('vno=3'); ELSE
dbms_output.put_line('vno=其他'); END CASE;
END;
-- 5.循环
-- 5.1 loop循环
DECLARE
vno NUMBER :=1;
BEGIN
LOOP
IF vno>10 THEN
EXIT;--退出循环
END IF;
dbms_output.put_line(vno);
vno := vno+1;
END LOOP;
END;
--使用 exit when 退出loop循环
DECLARE
vno NUMBER :=1;
BEGIN
LOOP
EXIT WHEN vno>10;--退出循环的条件 dbms_output.put_line(vno);
vno := vno+1;
END LOOP;
END;
--5.2 while循环
DECLARE
vno NUMBER :=1;
BEGIN
WHILE vno<10
LOOP
dbms_output.put_line(vno);
vno := vno+1;
END LOOP;
END;
--5.3 for循环
DECLARE
vno NUMBER :=1 ;
BEGIN
FOR vno IN 1..20
LOOP
dbms_output.put_line(vno);
END LOOP;
END;
--九九乘法表
DECLARE
BEGIN
FOR i IN 1..9
LOOP
FOR j IN 1..i
LOOP
dbms_output.put(i || '*' || j || '=' || (i*j) || ' ');
END LOOP;
dbms_output.put_line('');
END LOOP;
END;
--6.异常
--6.1 系统异常
--case_not_found case语句中的选项，与用户输入的数据不一致 --no_data_found 没有找到数据
--too_many_rows 找到的数据太多
--zero_divide 除数为0
DECLARE
vname scott.emp.ename%TYPE;
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno='&eno'; dbms_output.put_line('名字是：'|| vname);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('没有这个员工');
WHEN too_many_rows THEN
dbms_output.put_line('返回的数据太多');
END;
--6.2 自定义异常
DECLARE
myexception EXCEPTION;
vno NUMBER := '&vno';
BEGIN
IF vno>100 THEN
RAISE myexception; --抛出异常
END IF;
dbms_output.put_line(vno);
EXCEPTION
WHEN myexception THEN
dbms_output.put_line('数字太大');
when *** then when others then
END;
--6.3 引发应用程序异常
-- raise_application_error(error_number,error_message);
--error_number :用户自定义的错误编号，编号的范围 -20000到-20999之间的整数 --error_message:用户定义的错误信息
DECLARE
vno NUMBER :='&vno';
BEGIN
IF vno>20 THEN
RAISE_application_error(-20001,'数字太大');
END IF;
dbms_output.put_line(vno);
END;
--7 事务
--7.1 事务：是一组相关数据改变的集合。在一个事务中的数据保持一致。同时更改成功，或者同时更改失败。
--7.2 控制事务的两个命令
--commit 提交事务
--rollback 回滚事务
CREATE TABLE card
(
cardid VARCHAR2(20) PRIMARY KEY,
money NUMBER
);
INSERT INTO card VALUES('11111',100);
INSERT INTO card VALUES('22222',100);
SELECT * FROM card;
--转账,11111用户的钱，转500到22222用户中
DECLARE
v_money card.money%TYPE;
BEGIN
UPDATE card SET money=money+50 WHERE cardid='22222';
SELECT money INTO v_money FROM card WHERE cardid='11111';
IF v_money<50 THEN
ROLLBACK;--回滚
ELSE
UPDATE card SET money=money-50 WHERE cardid='11111';
END IF;
COMMIT; --提交事务
END;
SELECT * FROM card;
--7.3 控制事务好处
-- 1.保持数据的一致性
--2.使用户在数据永久生效前看到修改的数据结构
--3.将相关的数据组织在一起，一个事务中的数据要么都改变成功，要么都改变失败 --4.在下面几种情况中，事务将自动隐式提交
--a.执行DDL(数据定义语言)语句时,create alter drop
--b.执行DCL（数据控制语言） ,grant revoke
--c.从sql*plus正常退出时,使用quit 或者exit
--5.在以下几种情况中，事务将自动回滚
--a.强行退出sql*plus
--b.客户端到服务器连接异常中断
--c.系统崩溃
--8.游标
--8.1 隐式游标
--%found 找到数据
--%notfound 没有找到数据
--%ISOPEN 如果游标打开，得到true，否则得到false
--%rowcount 游标当前提取数据的数量.
DECLARE
BEGIN
UPDATE scott.emp SET sal=20 WHERE empno=7369;
IF SQL%NOTFOUND THEN --判断最靠近的那条sql语句 dbms_output.put_line('没有找到数据');
ELSE
dbms_output.put_line('更新了' || SQL%ROWCOUNT || '数据');
END IF;
COMMIT;
END;
--8.2 显示游标
--步骤：
--1.声明游标
--2.打开游标
--3.从游标中提取数据
--4.关闭游标
--显示游标的属性
--%found
--%notfound,默认初值为false
--%isopen
--%rowcount,默认初值为0，表示游标中的第几行
--例子：使用显示游标读取数据，显示emp表中的所有员工名字
DECLARE
CURSOR cur_emp IS SELECT ename FROM scott.emp; --声明游标
vname scott.emp.ename%TYPE;
BEGIN
IF NOT cur_emp%ISOPEN THEN
OPEN cur_emp; --打开游标
END IF;
LOOP
FETCH cur_emp INTO vname; --读取一条数据
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line(cur_emp%ROWCOUNT || '.' || vname);
END LOOP;
CLOSE cur_emp; --关闭游标
END;
--查询多列
DECLARE
CURSOR cur_emp IS SELECT ename,empno FROM scott.emp; --声明游标 vname scott.emp.ename%TYPE;
vno scott.emp.empno%TYPE;
BEGIN
IF NOT cur_emp%ISOPEN THEN
OPEN cur_emp; --打开游标
END IF;
LOOP
FETCH cur_emp INTO vname,vno; --读取一条数据
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line(cur_emp%ROWCOUNT || '.' || vname || ' 编号：' || vno); END LOOP;
CLOSE cur_emp; --关闭游标
END;
--8.3 使用游标修改数据 ,
--注意：在声明的时候，要加上 for update
--在使用的时候，current of 游标名 代表当前行
--修改工资低于1000 加200
DECLARE
CURSOR cur_emp IS SELECT sal FROM scott.emp FOR UPDATE;
vsal NUMBER;
BEGIN
OPEN cur_emp;--打开游标
LOOP
FETCH cur_emp INTO vsal;
EXIT WHEN cur_emp%NOTFOUND;
IF vsal<1000 THEN
UPDATE scott.emp SET sal=vsal+200 WHERE CURRENT OF cur_emp; --更新游标当前行的数据
END IF;
END LOOP;
CLOSE cur_emp;--关闭游标
COMMIT;--提交事务
END;
--8.4 带参数的显示游标
--例子： 输入部门编号，显示该部门的员工的编号和名字
DECLARE
CURSOR cur_emp(dno NUMBER) IS SELECT empno,ename FROM scott.emp WHERE deptno=dno;
vempno scott.emp.empno%TYPE;
vename scott.emp.ename%TYPE;
BEGIN
OPEN cur_emp('&deno'); --打开游标时候，传入参数
LOOP
FETCH cur_emp INTO vempno,vename;
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line('编号：' || vempno || ' 名字：' || vename);
END LOOP;
CLOSE cur_emp; --关闭游标
END;
--8.5 循环游标，使用for循环提取游标数据
--1.自动打开游标,自动关闭游标
--2.提取和处理游标中的一行数据
--3.每次提取的是一行数据
--例子： 输入部门编号，显示该部门的员工的编号和名字
DECLARE
CURSOR cur_emp IS SELECT * FROM scott.emp;
--emp_row scott.emp%ROWTYPE;
BEGIN
FOR emp_row IN cur_emp
LOOP
dbms_output.put_line('编号：' || emp_row.empno || '姓名：' || emp_row.ename); END LOOP;
END;
--scott.emp表 如果10号部门，工资低于1000 加50，工资低于2000 加100
-- 如果20号部门，工资低于1500 加50，工资低于2000 加100
-- 如果30号部门，工资全部加100
-- 其他部门，工资加50
十．子程序和程序包
1.子程序
子程序：已经命名的plsql语句块，它们存储在数据库中，可以指定参数，可以从任意数据库客户端或者
应用程序中调用它们。主要包括：存储过程和函数.
子程序的优点：
a.模块化：通过子程序，可以将程序分解为可管理的，明确的逻辑模块。
b.可重用性:子程序在创建并执行后，就可以在任意的应用程序中使用。
c.安全性：用户可以设置权限，使得访问数据库的唯一方式就是通过用户提供的过程和函数。
2.存储过程
2.1 创建存储过程，实现，根据员工编号查询员工姓名。
CREATE OR REPLACE PROCEDURE findname
AS
vname VARCHAR2(20);
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=7369;
dbms_output.put_line('姓名是：' || vname);
END;
调用存储过程
BEGIN
findname();
END;
2.2带输入参数的存储过程,实现，根据员工编号查询员工姓名,要求员工编号通过参数传递。 CREATE OR REPLACE PROCEDURE findname(vno NUMBER)
AS
vname VARCHAR2(20);
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=vno;
dbms_output.put_line('姓名是：' || vname);
END;
注：跟在begin后面的sql查询语句里必须要有into
调用带参数的存储过程
BEGIN
findname(7369);
END;
2.3 过程的参数：IN(输入参数,默认)、OUT(输出参数)、IN OUT(输入输出参数)
2.3.1 IN 参数：输入参数，默认就是in 例子2.2
2.3.2 OUT 参数,根据员工编号，返回员工姓名
CREATE OR REPLACE PROCEDURE finname(vno IN NUMBER,vname OUT VARCHAR2) AS
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=vno;
END;
调用存储过程
DECLARE
vname VARCHAR2(20);
BEGIN
finname(7369,vname);
dbms_output.put_line(vname);
END;
2.3.3 IN OUT 参数,根据编号查询工资
CREATE OR REPLACE PROCEDURE findsal(vnumber IN OUT NUMBER)
AS
BEGIN
SELECT sal INTO vnumber FROM scott.emp WHERE empno=vnumber;
END;
调用存储过程
DECLARE
v NUMBER:=7369;
BEGIN
findsal(v);
dbms_output.put_line(v);
END;
2.4 删除存储过程
DROP PROCEDURE finname;
3.函数
函数：与存储过程很相似，主要特征是必须返回一个值，函数只能带in参数
3.1 创建函数： 根据用户编号查询用户姓名
CREATE OR REPLACE FUNCTION find_name(vno NUMBER)
RETURN VARCHAR2
AS
vname VARCHAR2(20);
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=vno;
RETURN vname;
END;
调用函数
DECLARE
vname VARCHAR(20);
BEGIN
vname := find_name(7369);
dbms_output.put_line(vname);
dbms_output.put_line(find_name(7369));
END;
3.3 存储过程和函数的比较
过程 函数
作为plsql语句执行 作为表达式的一部分调用 在规范中不包括return 必须包含return
不返回任何值 必须返回单个值
4.程序包
程序包：是一种数据库对象，对相关的plsql子程序等进行封装.
主要包含两个部分:程序包规范（包头） 和 程序包主体(包体) 在程序包规范中主要是声名类型，变量子程序等(相当于接口) 程序包主体中实现包规范中定义的内容(相当于实现类)
4.1 创建程序包
4.1.1创建包规范
CREATE OR REPLACE PACKAGE pack_mh
AS
PROCEDURE findName(vno NUMBER);--声名存储过程
FUNCTION findsal(vno NUMBER) RETURN NUMBER;--声名函数 END pack_mh;
4.1.2 创建程序包主体,名字必须和包规范的名字一样
CREATE OR REPLACE PACKAGE BODY pack_mh
IS
--实现存储过程
PROCEDURE findName(vno NUMBER)
AS
vname VARCHAR2(20);
BEGIN
SELECT ename INTO vname FROM scott.emp WHERE empno=vno; dbms_output.put_line(vname);
END findName;
--实现函数
FUNCTION findsal(vno NUMBER) RETURN NUMBER
AS
vsal NUMBER;
BEGIN
SELECT sal INTO vsal FROM scott.emp WHERE empno=vno; RETURN vsal;
END findsal;
END pack_mh;
4.2 调用程序包中的过程和函数
DECLARE
BEGIN
pack_mh.findName(7369);--调用过程
dbms_output.put_line(pack_mh.findsal(7369));--调用函数 END;
4.3 删除程序包
DROP PACKAGE pack_mh;
DROP PACKAGE BODY pack_mh;
在java中调用存储过程
Connection conn = null;
调用函数
String sql = “{? = call find_name(?)}”; CallableStatement cs = null; try { } conn = ConnDAO.getConn(); cs = conn.prepareCall("{call pro_interbar(?,?,?,?)}"); cs.setString(1, hiyuanNumber); cs.setString(2, pwd); cs.setInt(3, machineNumber2); //此处为out型参数 cs.registerOutParameter(4, java.sql.Types.INTEGER); cs.execute(); //得到返回值 int flag = cs.getInt(4); if(flag==1) { info = "卡号或密码不正确"; } else if(flag==2) { info = "此卡正在被使用"; } else if(flag==3) { } e.printStackTrace(); info = "余额不足"; info = "其他错误"; } else if(flag==4) { } catch (Exception e) {
Callablestatement ct;
Ct.registerpoutparameter(1,java.sql.types.varchar);
Ct.setint(2,sno);
Ct.getString(1);
注：函数中，在异常中也要写return语句
十一。触发器 1. 什么是触发器
一段特殊的存储过程，特点是不能手动调用，只能在满足触发条件时自动执行。
2.分类
数据库级、模式级、表级、行级
3.触发器的执行先后分类
前触发器: 实现对更新数据的替换，原理替换 :NEW 表中的数据
后触发器
4.触发器中的两张表
:NEW
:OLD
5.触发器的简单测试的表
DROP TABLE test
CREATE TABLE test
(
a NUMBER,
b VARCHAR(20)
);
INSERT INTO test VALUES(1,'a1');
INSERT INTO test VALUES(2,'b1');
6.创建触发器,表级触发器(语句级)
CREATE OR REPLACE TRIGGER tir_test_au
AFTER UPDATE
ON test
DECLARE
BEGIN
dbms_output.put_line('OK!');
END;
测试语句
UPDATE test SET b='b';
7.创建触发器，行级触发器
CREATE OR REPLACE TRIGGER tir_test_aur
AFTER UPDATE
ON test
FOR EACH ROW --表明触发器是行级触发器
DECLARE
BEGIN
dbms_output.put_line('行级触发器OK!');
END;
UPDATE test SET b='a';
表级（语句级）触发器：一条sql语句只触发一次
行级触发器：操作了几条数据，就会触发几次。
删除触发器
DROP TRIGGER tir_test_aur;
DROP TRIGGER tir_test_au;
8.触发器中的:OLD 表和 :NEW 表
new表是需要存储到数据库中的表，old表是从数据库中取出来的表
8.1 INSERT触发器中的两张临时表
CREATE OR REPLACE TRIGGER tri_test_air
AFTER INSERT
ON test
FOR EACH ROW
DECLARE
BEGIN
dbms_output.put_line(:new.a);
dbms_output.put_line(:old.a);
dbms_output.put_line('OK!');
END;
INSERT INTO test VALUES(3,'c');
执行上面后的结果说明 INSERT 操作时 :new表中有数据，:old表中没有数据
8.2 update触发器中的两张临时表
CREATE OR REPLACE TRIGGER tri_test_aur
AFTER UPDATE
ON test
FOR EACH ROW
DECLARE
BEGIN
dbms_output.put_line(:new.b);
dbms_output.put_line(:old.b);
dbms_output.put_line('OK!');
END;
UPDATE test SET b='d' WHERE a=2;
执行上面后的结果说明：在update操作时，:new表中存储新数据，:old表中存储原有的数据。
8.3 delete触发器中的两张临时表
CREATE OR REPLACE TRIGGER tri_test_aud
AFTER DELETE
ON test
FOR EACH ROW
DECLARE
BEGIN
dbms_output.put_line(:new.b);
dbms_output.put_line(:old.b);
dbms_output.put_line('OK!');
END;
DELETE test WHERE a=3;
执行上面的操做结结果说明：在delete操作时，:NEW表中没有数据,:old表中存放原有的数据 小结：
INSERT :NEW 有数据 :old表无数据
UPDATE :new表有数据 :old表有数据
DELETE :new表无数据 :old表有数据
8.4 插入数据时，修改值
CREATE OR REPLACE TRIGGER tri_test_bir
BEFORE INSERT
ON test
FOR EACH ROW
DECLARE
BEGIN
:new.b='f';
END;
INSERT INTO test VALUES(4,'d');
通常用于插入自动增长列
CREATE SEQUENCE se_test_a
START WITH 1
INCREMENT BY 1;
创建触发器实现自动增长
CREATE OR REPLACE TRIGGER tri_test_birseq
BEFORE INSERT
ON test
FOR EACH ROW
DECLARE
BEGIN
SELECT se_test_a.nextval INTO :new.a FROM dual;
END;
插入测试数据,实现自动增长
INSERT INTO test(b) VALUES('aa');
INSERT INTO test(b) VALUES('bb');
8.5 将几种操作集中一起
CREATE OR REPLACE TRIGGER tri_test_aidur
AFTER INSERT OR UPDATE OR DELETE
ON test
FOR EACH ROW
DECLARE
BEGIN
IF inserting THEN
dbms_output.put_line('增加操作');
END IF;
IF updating THEN
dbms_output.put_line('修改操作');
END IF;
IF deleting THEN
dbms_output.put_line('删除操作');
END IF;
dbms_output.put_line('OK');
END;
INSERT INTO test(b) VALUES('cc')
UPDATE test SET b='xx' WHERE a=1;
DELETE FROM test WHERE a=1
8.6 update触发器的of参数的使用
CREATE OR REPLACE TRIGGER tri_test_aur
AFTER UPDATE OF b --当test表中 b列被修改的时候才会触发 ON test
FOR EACH ROW
DECLARE
BEGIN
--遇到异常自动回滚
raise_application_error(-20001,'b列不允许修改'); END;
UPDATE test SET b='dd' WHERE a=1;
UPDATE test SET a=3 WHERE a=1;
8.7 BEFORE 和 after触发器的比较
触发器执行时有两个步骤
1.执行DML更新语句(INSERT UPDATE DELETE);
2.执行相应的触发器
BEFORE 触发器先执行步骤2 ，再执行步骤1
AFTER 触发器先执行步骤1，再执行步骤2
所以对于before 触发器，可以在数据未写入前对:new表的值进行更改.
对于after 触发器，不能对:NEW表中的值进行修改。
SELECT * FROM test;
十二。表的导入和导出
导出：
1. 在dos下输入exp
2. 输入用户名和密码
3. 输入存放的路径
4. 全选yes
5. 输入要导出的表的名字
导入：
1. 在dos下输入imp
2. 输入用户名和密码
3. 输入导入的表的路径
4. 选no
5. 全选yes
在pl/sql中修改数据
1. 用select * from userInfo for update
2. 点击解锁键。 --复制表结构
CREATE TABLE table_structs AS SELECT * FROM test WHERE 1!=1;
SELECT * FROM table_structs;
--复制表的内容
CREATE TABLE table_contents AS SELECT * FROM test;
SELECT * FROM table_contents;
十三.SQL注入漏洞
例：在用户名栏输入 ' or 1=1 or 1='1
用preparedstatement可以防止
十四。
数据库中的参数不需要指定大小
例：procedure pro_stu(varchar2 vname)
记住返回值要声明为out类型
十五。Odbc和jdbc连接
public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
URL = public static final String
"jdbc:oracle:thin:@localhost:1521:oracle"; public static final String URL = "jdbc:odbc:oracle";
十六，扩展
//得到表的结构
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnCount()); for(int i=1;i<=rsmd.getColumnCount();i++ ) { System.out.println(rsmd.getColumnName(i)); System.out.println(rsmd.getColumnTypeName(i)); } DatabaseMetaData dmd = conn.getMetaData(); System.out.println(dmd.getDriverName()); System.out.println(dmd.getDriverVersion()); System.out.println(dmd.getURL()); System.out.println(dmd.getUserName()); //得到数据库的相关信息 十七。
// 不自动提交
try {
conn.setAutoCommit(false); st = conn.createStatement(); st.executeUpdate(sql1); st.executeUpdate(sql2); int a = 3/0; conn.commit(); // TODO: handle exception e.printStackTrace(); try { } conn.rollback(); } catch (Exception e) { //批处理
Statement st = null;
try {
conn.setAutoCommit(false); st = conn.createStatement(); st.addBatch(sql1); st.addBatch(sql2); st.executeBatch(); //出错后回滚 int a = 3/0;
conn.commit(); // TODO: handle exception e.printStackTrace(); try { } conn.rollback(); } catch (Exception e) {
十八。随机排序
Select * from quest order by dbms_random.value
十九。分页
SELECT * FROM ( SELECT u.*,ROWNUM r FROM users u ) WHERE r>3 AND r<=6