by ·0评论

Oracle PL/SQL编程详解

1. PL/SQL 程序设计简介

SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。

1.1 SQL与PL/SQL

1.1.1 什么是PL/SQL?

PL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。

1.2 PL/SQL的优点或特征

1.2.1 有利于客户/服务器环境应用的运行

对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。

1.2.2 适合于客户环境

PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。

1.2.3 过程化

PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。

1.2.4 模块化

PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。

1.2.5 运行错误的可处理性

使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。

1.2.6 提供大量内置程序包

ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。
当然还有其它的一些优点如：更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。

继续阅读

by ·0评论

什么是存储过程呢？

定义：

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

讲到这里,可能有人要问：这么说存储过程就是一堆SQL语句而已啊？

Microsoft公司为什么还要添加这个技术呢?

那么存储过程与一般的SQL语句有什么区别呢?

存储过程的优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

存储过程的种类：

1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,

如 sp_help就是取得指定对象的相关信息

2.扩展存储过程 以XP_开头,用来调用操作系统提供的功能

exec master..xp_cmdshell ‘ping 10.8.16.1’

3.用户自定义的存储过程,这是我们所指的存储过程

常用格式

Create procedure procedue_name

[@parameter data_type][output]

[with]{recompile|encryption}

as

sql_statement

解释:

output：表示此参数是可传回的

with {recompile|encryption}

recompile:表示每次执行此存储过程时都重新编译一次

encryption:所创建的存储过程的内容会被加密

如:

表book的内容如下

编号 书名 价格

001 C语言入门 $30

002 PowerBuilder报表开发 $52

实例1:查询表Book的内容的存储过程

create proc query_book

as

select * from book

go

exec query_book

实例2:加入一笔记录到表book,并查询此表中所有书籍的总金额

Create proc insert_book

@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

with encryption ———加密

as

insert book(编号,书名，价格） Values(@param1,@param2,@param3)
select @param4=sum(价格) from book
go

执行例子:
declare @totalprice money
exec insertbook ‘003’,’Delphi 控件开发指南’,$100,@totalprice
print ‘总金额为’+convert(varchar,@totalprice)
go
存储过程的3种传回值:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中

实例3：设有两个表为Product,Order,其表内容如下：
Product
产品编号 产品名称 客户订数
001 钢笔 30
002 毛笔 50
003 铅笔 100
order
产品编号 客户名 客户订金
001 南山区 $30
002 罗湖区 $50
003 宝安区 $4
请实现按编号为连接条件,将两个表连接成一个临时表,该表只含编号.产品名.客户名.订金.总金额,
总金额=订金*订数,临时表放在存储过程中

代码如下:
Create proc temp_sale
as
select a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金 as总金额
into #temptable from Product a inner join order b on a.产品编号=b.产品编号
if @@error=0
print ‘Good’
else
print ‘Fail’
go

by ·0评论

一、数据定义语言 ｄｄｌ（data definition language)

数据定义语言是指对数据的格式和形态下定义的语言，它是每个数据库要建立时候时首先要面对的，举凡数据分哪些表格关系、表格内的有什麽字段主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。

１、建立表格：

一、数据定义语言 ｄｄｌ（data definition language)

数据定义语言是指对数据的格式和形态下定义的语言，它是每个数据库要建立时候时首先要面对的，举凡数据分哪些表格关系、表格内的有什麽字段主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。

１、建立表格：
create table table_name(
column1 datatype [not null] [not null primary key],
column2 datatype [not null],
…）
说明：　
datatype –是数据的格式，详见表。
not null –可不可以允许数据有空的（尚未有资料填入）。
primary key –是本表的主键。

２、更改表格　
alter table tablename
add column columnname datatype
说明：增加一个字段（没有删除某个字段的语法。）

alter table tablename
add primary key (columnname)
说明：更改表得的定义把某个字段设为主键。

alter table tablename
drop primary key (columnname)
说明：把主键的定义删除。

３、建立索引　
create index indexname on tablename (column_name)
说明：对某个表格的字段建立索引以增加查询时的速度。

４、删除表，删除索引
drop tablename
drop indexname

二、数据类型 datatypes
smallint
16 位元的整数。

interger
32 位元的整数。

decimal(p,s)
p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数
点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。

float
32位元的实数。

double
64位元的实数。

char(n)
n 长度的字串，n不能超过 254。

varchar(n)
长度不固定且其最大长度为 n 的字串，n不能超过 4000。

graphic(n)
和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为
了支援两个字元长度的字体，例如中文字。

vargraphic(n)
可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。

date
包含了 年份、月份、日期。

time
包含了 小时、分钟、秒。

timestamp
包含了 年、月、日、时、分、秒、千分之一秒。

三、数据操作语言 ｄｍｌ （data manipulation language)
数据定义好之後接下来的就是数据的操作。数据的操作不外乎增加记录（insert)、查询数据（query）、更改记录（update) 、删除记录（delete）四种模式，以下分别介绍他们的语法：

１、增加记录：
insert into tablename (column1,column2,…)
values ( value1,value2, …)
说明：
1.若没有指定column 系统则会按表格内的字段顺序填入资料。
2.字段的数据类型和所填入的数据必须吻合。
3.tablename 也可以是视图 view_name。

insert into tablename (column1,column2,…)
select columnx,columny,… from anothertable
说明：也可以经过一个子查询（subquery）把别的表格的数据填入。

２、查询数据：
基本查询
select column1,columns2,…
from tablename
说明：把tablename 的特定字段数据全部列出来

select *
from table_name
where column1 = xxx
[and column2 > yyy] [or column3 zzz]
说明：
1.’*’表示全部的字段都列出来。
2.where 之後是接条件表达式，把符合条件的数据列出来。

select column1,column2
from table_name
order by column2 [desc]
说明：order by 是指定以某个字段做排序，[desc]是指从大到小排列，若没有指明，则是从小到大
排列。

组合查询
组合查询是指所查询得数据来源并不只有单一的表格，而是联合一个以上的
表格才能够得到结果的。
select *
from table1,table2
where table1.colum1=table2.column1
说明：
1.查询两个表格中其中 column1 值相同的数据。
2.当然两个表格相互比较的字段，其数据类型必须相同。
3.一个复杂的查询其动用到的表格可能会很多个。

整合性的查询：
select count (*)
from tablename
where columnname = xxx
说明：
查询符合条件的数据有多少项。count(column_name),计算某个字段的记录数。

select sum(column1)
from tablename
说明：
1.计算出总和，所选的字段必须是可数的数字类型。
2.除此以外还有 avg() 是计算平均、max()、min()计算最大最小值的整合性查询。
select column1,avg(column2)
from tablename
group by column1
having avg(column2) > xxx
说明：
1.group by: 以column1 为一组计算 column2 的平均值必须和 avg、sum等整合性查询的关键字
一起使用。
2.having : 必须和 group by 一起使用作为整合性的限制。

复合性的查询
select *
from tablename1
where exists (
select *
from tablename2
where conditions )
说明：
1.where 的 conditions 可以是另外一个的 query。
2.exists 在此是指存在与否。
select *
from tablename1
where column1 in (
select column1
from tablename2
where conditions )
说明：　
1. in 後面接的是一个集合，表示column1 存在集合里面。
2. select 出来的资料形态必须符合 column1。

其他查询
select *
from tablename1
where column1 like ‘x%’
说明：like 必须和後面的’x%’ 相呼应表示以 x为开头的字串。
select *
from tablename1
where column1 in (‘xxx’,’yyy’,..)
说明：in 後面接的是一个集合，表示column1 存在集合里面。
select *
from table_name1
where column1 between xx and yy
说明：between 表示 column1 的值介於 xx 和 yy 之间。

３、更改记录：
update table_name
set column1=’xxx’
where conditoins
说明：
1.更改某个字段，设定其值为’xxx’。
2.conditions 是所要符合的条件、若没有 where 则整个 table 的字段都会全部被更改。

４、删除记录：
delete from table_name
where conditions
说明：删除符合条件的记录。

说明：关于where条件后面如果包含有日期的比较，不同数据库有不同的表达式。具体如下：
(1)如果是access数据库，则为：where mydate>#2000-01-01#
(2)如果是oracle数据库，则为：where mydate>cast(‘2000-01-01’ as date)
或：where mydate>to_date(‘2000-01-01′,’yyyy-mm-dd’)
在delphi中写成：
thedate=’2000-01-01′;
query1.sql.add(‘select * from abc where mydate>cast(‘+””+thedate+””+’ as date)’);

如果比较日期时间型，则为：
where mydatetime>to_date(‘2000-01-01 10:00:01′,’yyyy-mm-dd hh24:mi:ss’)

by ·0评论

说明：复制表(只复制结构,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

说明：显示文章、提交人和最后回复时间

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

说明：外连接查询(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

说明：两张关联表，删除主表中已经在副表中没有的信息

SQL:

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

说明：–

SQL:

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE  
FROM TABLE1,  
 (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE   
 FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND   
 FROM TABLE2   
 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,   
 (SELECT NUM, UPD_DATE, STOCK_ONHAND   
 FROM TABLE2   
 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =   
 TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') – 1, 'YYYY/MM') ) Y,   
 WHERE X.NUM = Y.NUM （+）   
 AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) X.STOCK_ONHAND ) B   
WHERE A.NUM = B.NUM

说明：–

SQL:

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='”&strdepartmentname&”' and 专业名称='”&strprofessionname&”' order by 性别,生源地,高考总成绩

说明：

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

SQL:

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,  
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,   
 SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC   
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration  
 FROM TELFEESTAND a, TELFEE b   
 WHERE a.tel = b.telfax) a   
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')

说明：四表联查问题：

SQL:

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..

说明：得到表中最小的未使用的ID号

SQL:

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID  
FROM Handle  
WHERE NOT HandleID IN (SELECT a.HandleID – 1 FROM Handle a)

相同的记录：

select * from a join b on a.id=b.id

删除相同的记录:

delete a from b ,a where a.id=b.id;

不同的记录

select * from a where a.id not in(select id from b)  
union  
select * from b where b.id not in(select id from a)

查找某段时间内的用户资料

select * from dbms_user_info_def t where crbt_Status='N' and to_char(reg_date,'yyyy-mm-dd hh24:mm:ss') between '2005-01-01 00:00:00' and '2005-01-26 00:00:00';

合并表

insert into a select * from b;