Oracle Class6-1. PL/SQL 简介(数据类型,逻辑比较,控制结构,错误处理)

最新推荐文章于 2024-06-16 09:46:44 发布

dfsafsd32323

最新推荐文章于 2024-06-16 09:46:44 发布

阅读量140

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/simpledev/archive/2013/05/22/3092922.html

版权

------------------------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;

转载于:https://www.cnblogs.com/simpledev/archive/2013/05/22/3092922.html

dfsafsd32323

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle Class6-1. PL/SQL 简介(数据类型,逻辑比较,控制结构,错误处理)

------------------------2013-5-16------------------------1.sql概念，主要特点，使用sql工具，如何访问及本书实例两个数据表2.单个表查询3.多个表查询4.复杂的嵌套查询5.查询函数6.sql录入数据7.sql删除数据8.sql更新数据sql已经被ANSI（美国国家标准化组织）确定为数据库系统的工业标准。DQL：查询DD...
复制链接

扫一扫