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

2024-03-29 20:30•移动端•阅读 4968

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

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

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

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

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)

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)

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

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

select * from class6;

begin

-- 打开游标

open cur_Titles;

-- 关闭游标

close cur_Titles;

exception

when CURSOR_ALREADY_OPEN then --重复打开问题-- CURSOR_ALREADY_OPEN

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

)

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

)

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 --关键点：这个地方没有逗号。--

)

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 --返回类型--

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;

上一篇 »PL/SQL连接Oracle数据库
下一篇 »Oracle存储过程中使用参数输出游标类型结合动态SQL

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

相关推荐

oracle pl/sql中使用自定义数据类型

Oracle9i + PL_SQL学习笔记

Oracle数据库基础，权限分类、管理角色、PL/SQL语言、SQL注入

Oracle PL/SQL中如何使用%TYPE和%ROWTYPE？转帖

二十四、oracle pl/sql 变量

[最近学习]Oracle 9i - PL/SQL ，一

oracle，sql基础篇系列

oracle pl sql 行转列 ，数据翻转实现

oracle pl sql 行转列，数据翻转实现