------------------------2013-5-16------------------------
1.sql概念，主要特点，使用sql工具，如何访问及本书实例两个数据表
2.单个表查询
3.多个表查询
4.复杂的嵌套查询
5.查询函数
6.sql录入数据
7.sql删除数据
8.sql更新数据
sql已经被ANSI（美国国家标准化组织）确定为数据库系统的工业标准。
DQL：查询
DDL：建立，删除和修改数据对象
DML：完成数据操作的命令，包括查询。
DCL：控制对数据库的访问，服务器的关闭，启动等。
--\Class1\扩展\Oracle9.0入门_基本SQL.pdf
--\Class6\扩展\Oracle9.0入门_04_SQL.pdf 相同
set serveroutput on 打开输出。
case .. when .. then .. else .. end case条件判断
declare
lv_Age_num number default 99;
lv_Name_char varchar2(8) := 'AZhu';
lc_Sex_char constant char(8) := 'Male';
begin
dbms_output.put_line (lv_Age_num);
dbms_output.put_line ('===========');
lv_Age_num := 15;
lv_Name_char := 'ANiu';
--lc_Sex_char := 'Female';
case lv_Age_num
when 12 then
dbms_output.put_line (lv_Name_char || '大于10!');
when 10 then
dbms_output.put_line (lv_Name_char || '等于10!');
else
dbms_output.put_line (lv_Name_char || '小于10!');
end case;
end;
declare
lv_Age_num number default 99;
lv_Name_char varchar2(8) := 'AZhu';
lc_Sex_char constant char(8) := 'Male';
begin
dbms_output.put_line (lv_Age_num);
dbms_output.put_line ('===========');
lv_Age_num := 9;
lv_Name_char := 'ANiu';
--lc_Sex_char := 'Female';
case
when lv_Age_num > 10 then
dbms_output.put_line (lv_Name_char || '大于10!');
when lv_Age_num = 10 then
dbms_output.put_line (lv_Name_char || '等于10!');
else
dbms_output.put_line (lv_Name_char || '小于10!');
end case;
end;
sql%found判断，返回影响的行数：sql%rowcount
begin
update class6 set cname = 'PP' where cid = 3;
if sql%found then
dbms_output.put_line(sql%rowcount);
else
dbms_output.put_line('OhNo!');
end if;
end;
for循环
for lv_ID_num in 2 .. 4 --退出条件--
loop
end loop;
declare
lv_ID_num2 int := 12;
begin
for lv_ID_num in 8 .. 10 --for变量,--退出条件--
loop
insert into Class6 values (lv_ID_num2, 'ANiu' || to_char(lv_ID_num2));
lv_ID_num2 := lv_ID_num2 + 1; --如果写成lv_ID_num := lv_ID_num + 1; 会报错。
end loop;
commit;
end;
while lv_ID_num < 8 --退出条件--
loop
end loop;
loop
exit when lv_ID_num = 21; --退出条件--
end loop;
declare
lv_Title_char varchar2(80);
lv_Price_num number(19,4);
begin
select cname,cid
into lv_Title_char, lv_Price_num --可以赋予给多个变量--
from class6
where cid='2'; --'2'可以写成变量赋值的方式--
dbms_output.put_line (lv_Title_char);
dbms_output.put_line (lv_Price_num);
end;
declare
-- 定义记录(Record)
type type_Title is record --创建类型--
(
lv_Title_char varchar2(80),
lv_Price_num number(19,4)
);
lv_title type_Title;
lv_TitleID_char varchar2(80);
begin
lv_TitleID_char := 3;
select cname,cid
into lv_title --赋予给记录类型变量--
from class6
where cid= lv_TitleID_char;
dbms_output.put_line (lv_title.lv_Title_char);
dbms_output.put_line (lv_title.lv_Price_num);
end;
###
create type add_type as object(
street varchar2(10), --街道名
city varchar2(10), --城市名
state char(2), --州代码
zip number --邮编
);
### -->ORACLE埃里森4.txt
创建视图
create view viewTitles
as
select cid, cname from class6
declare
-- 定义记录(Record)
lv_title viewTitles%rowtype; --视图类型方式--, --表类型: lv_title class6%rowtype;
lv_TitleID_char varchar2(80);
begin
lv_TitleID_char := 3;
select cid, cname
into lv_title --赋予给记录类型变量--
from class6
where cid= lv_TitleID_char;
dbms_output.put_line (lv_title.cid);
dbms_output.put_line (lv_title.cname);
end;
%type理解,只要是类型都可以引用.
lv_Name_char varchar2(8) := 'AZhu';
lv_Sex_char lv_Name_char%type := 'Male'; --引用变量--
---------------------游标完整例子---------------------
declare
-- 声明游标(关联Select语句)
cursor cur_Titles is select cname,cid from class6;
lv_Title_char class6.cname%type;
lv_Price_num class6.cid%type;
begin
-- 打开游标
open cur_Titles;
-- 提取游标
fetch cur_Titles into lv_Title_char, lv_Price_num;
loop
exit when not cur_Titles%Found; --退出循环条件
dbms_output.put_line ('游标demo========');
dbms_output.put_line (lv_Title_char);
dbms_output.put_line (lv_Price_num);
fetch cur_Titles into lv_Title_char, lv_Price_num; --循环读取
end loop;
-- 关闭游标
close cur_Titles;
end;
--游标类型--
declare
-- 声明游标(关联Select语句)
cursor cur_Titles is select cname,cid from class6;
lv_title_rec cur_Titles%rowtype; --引用游标类型--
begin
-- 打开游标
open cur_Titles;
-- 提取游标
fetch cur_Titles into lv_title_rec;
loop
exit when not cur_Titles%Found; --退出循环条件
dbms_output.put_line ('游标demo========');
dbms_output.put_line (lv_title_rec.cid);
dbms_output.put_line (lv_title_rec.cname);
fetch cur_Titles into lv_title_rec; --循环读取
end loop;
-- 关闭游标
close cur_Titles;
end;
select .. for update ..
规则是：FOR UPDATE语句将锁住查询结果中的元组，这些元组将不能被其他事务的UPDATE，DELETE和FOR UPDATE操作，直到本事务提交。
在newlifeyhj帐户下面建立scott.emp表的结构和数据用来测试:
create table class6emp as
select * from scott.emp;
select sal from class6emp where job = 'CLERK';
SAL
----------
800
1100
950
1300
##修改,取当前游标的记录.where current of c##
create or replace procedure proGeMing2
as
cursor c is select empno, job, sal from class6emp for update;
lv_emp_rec c%rowtype; --那么这行不要
begin
open c; --那么这行不要
--for lv_emp_rec in c --另外一种fetch方式--
loop
fetch c into lv_emp_rec; --那么这行不要
exit when not c%found; --那么这行不要
case lv_emp_rec.job
when 'CLERK' then
update class6emp set sal = sal * 2 where current of c; --c是游标
when 'SALESMAN' then
update class6emp set sal = sal / 2 where current of c;
when 'MANAGER' then
update class6emp set sal = 0 where current of c;
else
update class6emp set sal = 250 where current of c;
end case;
end loop;
close c; --那么这行不要
end;
begin
proGeMing2;
end;
select sal from class6emp where job = 'CLERK';
SAL
----------
1600
2200
1900
2600
create or replace procedure proGeMing2
as
cursor c is select empno, job, sal from class6emp for update;
begin
for lv_emp_rec in c --另外一种fetch方式--
loop
case lv_emp_rec.job
when 'CLERK' then
update class6emp set sal = sal * 2 where current of c; --c是游标
when 'SALESMAN' then
update class6emp set sal = sal / 2 where current of c;
when 'MANAGER' then
update class6emp set sal = 0 where current of c;
else
update class6emp set sal = 250 where current of c;
end case;
end loop;
end;
select sal from class6emp where job = 'CLERK';
SAL
----------
3200
4400
3800
5200
不使用游标的方式,直接查询。那么就不可以用where current of c
create or replace procedure proGeMing2
as
begin
for lv_emp_rec in (select empno, job, sal from class6emp for update)
loop
case lv_emp_rec.job
when 'CLERK' then
update class6emp set sal = sal * 2 where empno = lv_emp_rec.empno;
when 'SALESMAN' then
update class6emp set sal = sal / 2 where empno = lv_emp_rec.empno;
when 'MANAGER' then
update class6emp set sal = 0 where empno = lv_emp_rec.empno;
else
update class6emp set sal = 250 where empno = lv_emp_rec.empno;
end case;
end loop;
end;
select sal from class6emp where job = 'CLERK';
SAL
----------
6400
8800
7600
10400
##带参数的游标## 变量方式
declare
-- 声明参数游标
cursor cur_Titles(p_t class6.cid%type)
is
select cname,cid from class6 where cid = p_t;
lv_Title_char class6.cname%type;
lv_Price_num class6.cid%type;
begin
-- 打开游标
open cur_Titles('2'); --参数游标--
loop
-- 提取游标
fetch cur_Titles into lv_Title_char, lv_Price_num;
exit when not cur_Titles%Found;
dbms_output.put_line ('========');
dbms_output.put_line (lv_Title_char);
dbms_output.put_line (lv_Price_num);
end loop;
-- 关闭游标
close cur_Titles;
-- 打开游标
open cur_Titles('3'); --参数游标--
loop
-- 提取游标
fetch cur_Titles into lv_Title_char, lv_Price_num;
exit when cur_Titles%notFound;
dbms_output.put_line ('========');
dbms_output.put_line (lv_Title_char);
dbms_output.put_line (lv_Price_num);
end loop;
-- 关闭游标
close cur_Titles;
end;
--记录方式存储--
declare
type ttt is record(
lv_Title_char class6.cname%type,
lv_Price_num class6.cid%type
);
-- 声明参数游标
cursor cur_Titles(p_t class6.cid%type)
is
select cname,cid from class6 where cid = p_t;
--lv_Title_char class6.cname%type;
--lv_Price_num class6.cid%type;
objttt ttt;
begin
-- 打开游标
open cur_Titles('2'); --参数游标--
loop
-- 提取游标
--fetch cur_Titles into lv_Title_char, lv_Price_num;
fetch cur_Titles into objttt;
exit when not cur_Titles%Found;
dbms_output.put_line ('========');
dbms_output.put_line (objttt.lv_Title_char);
dbms_output.put_line (objttt.lv_Price_num);
--如果游标有多条记录，那么循环取。
end loop;
-- 关闭游标
close cur_Titles;
end;
loop
-- 提取游标
fetch cur_Titles into objttt;
end loop;
或
-- 提取游标
fetch cur_Titles into objttt;
loop
fetch cur_Titles into objttt; --需要再次提取游标，便于循环。
end loop;
##定义引用游标##
declare
type ttt is record(
lv_Title_char class6.cname%type,
lv_Price_num class6.cid%type
);
-- 定义引用游标
type type_Titles_cur is ref cursor; --这个地方是关键。
-- 声明游标
cur_Titles type_Titles_cur;
objttt ttt;
begin
-- 打开游标
open cur_Titles
for
select cname, cid from class6; --给游标赋予值。
loop
-- 提取游标
fetch cur_Titles into objttt;
exit when not cur_Titles%Found;
dbms_output.put_line ('========');
dbms_output.put_line (objttt.lv_Title_char);
dbms_output.put_line (objttt.lv_Price_num);
end loop;
-- 关闭游标
close cur_Titles;
end;
##循环游标##
-- 循环游标
declare
-- 定义记录(Record)
-- 声明游标
cursor cur_Titles
is
select cid, cname from class6;
begin
-- 自动打开游标
-- 自动提取游标存储到一个自动定义的记录变量
-- 在循环结束部分自动提取下一条记录
-- 当读到游标结尾自动关闭游标
for lv_Title_type in cur_Titles
loop
dbms_output.put_line ('========');
dbms_output.put_line (lv_Title_type.cid);
dbms_output.put_line (lv_Title_type.cname);
end loop;
-- 关闭游标
end;
##隐式游标##
-- 隐式游标
declare
-- 定义记录(Record)
-- 自动声明游标
begin
-- 自动打开游标
-- 自动提取游标存储到一个自动定义的记录变量
-- 在循环结束部分自动提取下一条记录
-- 当读到游标结尾自动关闭游标
for lv_Title_type in (select cid, cname from class6)
loop
dbms_output.put_line ('========');
dbms_output.put_line (lv_Title_type.cid);
dbms_output.put_line (lv_Title_type.cname);
end loop;
-- 关闭游标
end;
PRAGMA EXCEPTION_INIT的用法 --自动触发，而不要手动raise 异常名; --
http://blog.csdn.net/wanggangytsoft/article/details/5408692
ora-01843: 这个错误代表无效的月份一般在日期转化的时候会提示。
ORA-00001: 违反唯一约束条件
ORA-06511 CURSOR_ALREADY_OPEN: 程序尝试打开一个已经打开的游标。一个游标在重新打开之前必须关闭。
ORA-00001 DUP_VAL_ON_INDEX: 唯一索引上有重复值
-- 自定义错误消息
-- Raise_Application_error
raise_application_error(-20001, '没有！');
ORA-01722 invalid number 无效数字
ORA-01476: divisor is equal to zero 这个错误是sql语句中存在除数为0的情况
ora-01422：输出值太多。查询返回的记录行大于1。
NO_DATA_FOUND
##游标异常##
declare
-- 声明游标
cursor cur_Titles
is
select * from class6;
begin
-- 打开游标
open cur_Titles;
open cur_Titles;
-- 关闭游标
close cur_Titles;
exception
when CURSOR_ALREADY_OPEN then --重复打开问题-- CURSOR_ALREADY_OPEN
dbms_output.put_line('Cursor is Opened!');
dbms_output.put_line('Cursor is Opened!');
end;
#存储过程参数 in 是参数的默认模式。# in, out, in out三种类型。
##创建存储过程##
create or replace procedure GetTitle
(
p_titleid in class6.cid%type := '2',
p_title out class6.cid%type,
p_price out class6.cname%type
)
as
begin
select cid, cname into p_title, p_price from class6 where cid = p_titleid;
end;
##调用存储过程##
declare
lv_title_char class6.cid%type;
lv_price_num class6.cname%type;
begin
GetTitle (p_title => lv_title_char, p_price => lv_price_num); --参数名称要和过程定义的相同--，否则参数个数不匹配。
--GetTitle (4,lv_title_char, lv_price_num); --参数接受，个数匹配--这样也可以的!!! --
dbms_output.put_line(lv_title_char);
dbms_output.put_line(lv_price_num);
end;
create or replace procedure GetTitle
(
p_title out class6.cid%type,
p_price out class6.cname%type
)
as
begin
select cid, cname into p_title, p_price from class6 where cid = 2;
end;
declare
lv_title_char class6.cid%type;
lv_price_num class6.cname%type;
begin
GetTitle (lv_title_char, lv_price_num); --参数接受，个数匹配--
dbms_output.put_line(lv_title_char);
dbms_output.put_line(lv_price_num);
end;
create or replace procedure GetTitleByTitleID2
(
p_TitleID in class6.cid%type,
p_Title out class6.cid%type,
p_Price out class6.cname%type --关键点：这个地方没有逗号。--
)
as
begin
select cid, cname into p_Title, p_Price from class6 where cid = p_TitleID;
end;
exec 不要写，否则会报错。
set serveroutput on; 打开，否则无法看到输出信息，pl/sql developer 测试窗口可以运行存储过程。
set serveroutput on;
declare
lv_Title class6.cid%type;
lv_Price class6.cname%type;
begin
GetTitleByTitleID2 ( 2, lv_Title,lv_Price);
dbms_output.put_line('----' || lv_Title);
dbms_output.put_line('====' || lv_Price);
end;
综合存储过程和调用与过程的对比。
declare
lv_Title class6.cid%type;
lv_Price class6.cname%type;
begin
select cid, cname into lv_Title, lv_Price from class6 where cid = 2;
dbms_output.put_line('----' || lv_Title);
dbms_output.put_line('====' || lv_Price);
end;
#类型的引用可以基于表，也可以基于视图。!!!#
##函数## 与存储过程的区别，是函数是有返回值的。
--创建函数
create or replace function funGetTotalQtyByTitleID
(
p_titleid in class6.cid%type
)
return class6.cname%type --返回类型--
as
p_totalqty class6.cname%type;
begin
select cname into p_totalqty from class6 where cid = p_titleid;
return (p_totalqty); --返回值--与返回类型一一匹配
end;
#函数的调用#与系统函数的调用和使用是一样的
select funGetTotalQtyByTitleID('4') from dual;
-- 赋值语句
declare
p_totalqty class6.cname%type;
begin
p_totalQty := funGetTotalQtyByTitleID('5');
dbms_output.put_line(p_totalqty);
end;
