oracle数据库存储过程基本语法

         oracle数据库存储过程

一，基本语法
数据类型：
**（一）字符类型：**字符串数据类型还可以依据存储空间分为固定长度类型（CHAR）和可变长度类型（varchar2/nvarchar2)两种
1，char类型：定长字符串，会用空格填充来达到器最大长度，非null的char（12）总是包含12字节信息。char字段最多可以存储2000字节的信息。如果创建表时，不指定char长度，则默认为1。
2，varchar2类型：变长字符串，与char类型不同，它不会使用空格填充至最大长度。varchar2最多可以存储4000字节信息。
3，nvarchar2类型：这是一个包含unicode格式数据的变长字符串。nvarchar2最多可以存储4000字节的信息，长度并不是字节表示了，而是字符数量。比如nuarchar2（10）表示可以存储10个字符。
（二）数字类型
1，number类型：number（p，s）是常见的数据类型
p是precison的英文缩写，即精确缩写，表示有效数字的位数，最多不能超过38个有效数字；
s是scale的英文缩写，表示小数点数字的位数。
2，integer类型：integer是number的子类型，它等同于number（38,0），用来存储整数。若插入，更新的数值有小数，则会被四舍五入。
（三）浮点数
1，binary_float类型： binary_float是32位，单精度浮点数字数据类型。可以支持至少6位精度，每个binary_float的值需要5个字节，包括长度字节。
2，binary_double类型：binary_double是为64位，双精度浮点数字数据类型。每个binary_double的值需要9个字节，包括长度字节。
（四）日期类型
1，date类型：date是最常见的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有关联的属性。为每个日期值，Oracle存储如下信息：世纪，年，月，日，小时，分钟和秒，一般占用7个字节存储空间。
2，timestamp类型：这是一个7个字节或12字节的定宽日期时间数据类型。它与date数据类型不同，因为timestamp可以包含小数秒，带小数秒的timestamp在小数点右边最多可以保留9位数。
3，timestamp with time zone类型：这是timestamp类型的变种，它包含了时区的偏移量的值。
4，timestamp withlocal time zone类型：将时间一数据类库时区进行规范化后进行存储。
（五）lob类型
1，clob类型（Character Large Object）：二进制数据，存储单字节和多字节字符数据。最大长度4G。
2，blob类型（Binary Large Object）：它存储非结构化的二进制数据大对象，它可以被认为是没有字符集义的比特流，一般图像；声音；视频等文件，最大4G。
3，nclob数据类型：存储Unicode类型的数据，最大长度4G。
（六）long&raw&long raw类型
1，long类型：它存储变长字符串（超长字符串），最多达2G的字符数据（2GB是指两千兆字节，而不是两千兆字符）。
2，raw类型：用于存储二进制或字符类型数据，必须指定长度。这种数据类型的数据不会发生字符集转换。可存放多媒体图像声音等。
3，long raw类型：能存储2GB的原始二进制数据，可存放媒体图像声音等。
二，数据库存储过程语法
（一）oracle数据路存储过程的创建（基本结构）
1，带参数的存储过程创建
CREATE OR REPLACE PROCEDURE 存储过程名字 (
–输入参数 IN NUMBER,
–输出参数 OUT NUMBER --可以有多个输入参数和输出参数
) IS
–这个区间可以用来定义变量和创建游标
BEGIN
–执行游标或者执行语句
commit; --对上面的内容进行提交
exception//存储过程异常
when others then
–异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
rollback;
dbms_output.put_line(sqlcode);
dbms_output.put_line(substr(sqlerrm, 1, 512));
END 存储过程名字;
注意：其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。
2，无参存储过程的创建
CREATE OR REPLACE PROCEDURE 存储过程名字
IS
–这个区间可以用来定义变量和创建游标
BEGIN
–执行游标或者执行语句
commit; --对上面的内容进行提交
exception//存储过程异常
when others then
–异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定
rollback;
dbms_output.put_line(sqlcode);
dbms_output.put_line(substr(sqlerrm, 1, 512));
END 存储过程名字;
（二）变量的声明和赋值
1，变量的声明
a、按照数据类型进行声明
格式：变量名 变量的数据类型;
样例：v_name varchar2(500);
b、根据表字段进行声明
格式：变量名 表名.字段名%TYPE
样例：v_name userinfo.name%TYPE;
注意：变量的数据类型和表名的字段数据类型一致
c、根据表的行记录进行声明
格式：变量名 表名/游标名%ROWTYPE
样例：v_user_row userinfo%ROWTYPE;
注意：变量的数据类型和表的行数据类型一致
2，变量的赋值
a、直接赋值，需要使用:=进行赋值
使用的条件：只能在变量的声明a和b才能使用直接赋值
先声明在赋值（声明在上面a和b）
样例：v_name:= ‘小花’;
声明和赋值同时进行
v_name varchar(500) := ‘小花’
b、select 字段 into 变量 from 表名 进行赋值
单个字段复制：
select name into v_name from userinfo;
使用条件：也是只限于a和b使用
行记录复制：
select * into v_user_row from userinfo;
使用条件：也是只限于c使用
c、execute immediate sql语句 into 变量 进行赋值
v_name varchar2(500);
v_name_sql varchar2(500) :=‘select name from userinfo where id=1’;
execute immediate v_name_sql into v_name ;
语句含义是like执行v_name_sql 语句并将结果赋值给v_name。
（三）流程控制语句
1,条件控制语句 IF … THEN … END 或者 IF … ELSIF… THEN …END
基本语法：
IF 条件表达式1 THEN
语句段1
ELSIF 条件表达式2 THEN
语句段2
…
ELSIF 条件表达式n THEN
语句段n
END IF;
样例：如果flag_num=0则输出’早上好’，如果flag_num=1 则输出 ‘中午好’，否则输出’晚上好’，
declare
flag_num int:= 0;
begin
if flag_num=0 then
dbms_output.put_line(‘早上好’);
elsif flag_num=1 then
dbms_output.put_line(‘中午好’);
else
dbms_output.put_line(‘晚上好’);
end if;
end;
2,条件控制语句case
基本语法：
CASE
WHEN 条件表达式1 THEN
语句段1;
WHEN 条件表达式2 THEN
语句段2;
…
ELSE
语句段n;
END CASE;
样例：如果flag_num=0则输出’早上好’，如果flag_num=1 则输出 ‘中午好’，否则输出’晚上好’
declare
flag_num int:= 0;
begin
case
when flag_num=0 then dbms_output.put_line(‘早上好’);
when flag_num=1 then dbms_output.put_line(‘中午好’);
else dbms_output.put_line(‘晚上好’);
end case;
end;
3,循环控制语句
a,loop循环
基本语法：
LOOP
EXIT [WHEN 条件表达式] --循环终止条件,为ture时，退出循环，否则再次执行循环体
语句段;
END LOOP;
样例：
declare
i int:= 0;
begin
loop
EXIT WHEN i > 3;
i:=i+1;
dbms_output.put_line(i);
end loop;
end;
语句解释：初始变量i为0，当i进入loop循环时，i>3则退出循环，i等于i+1,进行打印，然后依次循环，直至i>3时退出循环。
b,while循环
基本语法：
WHILE 条件表达式 LOOP
语句段;
END LOOP;
样例：
declare
i int := 0;
begin
while i < 3 loop
i := i + 1;
dbms_output.put_line(i);
end loop;
end;
语句解释：初始变量i为0，当i进入loop循环时，i<3进入循环，i等于i+1,进行打印，然后依次循环，直至i不小于3时退出循环。
3，for循环
基本语法：（注意：如加上reverse表示倒叙循环执行语句）
FOR 循环变量 in [REVERSE] 初值表达式…终值表达式 LOOP
语句段;
END LOOP;
样例：
declare
i int := 0;
begin
for i IN reverse 1…5 loop
dbms_output.put_line(i);
end loop;
end;
语句解释：初始变量i为0，循环遍历1…5，for循环默认是递增，但是加上reverse 会变成递减，将5赋给i,打印i,直至将1…5全部循环遍历，结束循环，for循环只适用于已知循环次数的遍历。
（四）游标
1，游标的定义：
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。游标存在隐式游标和显式游标。
2，隐式游标
DML操作和单行SELECT语句会使用隐式游标，它们分别是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT … INTO …。
隐式游标使用样例：
a，创建一个包含name,age,addr三个字段的表
create table userinfo(
name varchar2(500);
age number(20);
addr varchar2(500);
)
b，编写插入的代码并用上面的游标属性进行监控
declare
begin
insert into userinfo (name,age,addr) values(‘zs’,18,‘sc’);
dbms_output.put_line(‘游标所影响的行数：’||SQL%rowcount);
if SQL%NotFound then --没有找到上面执行的sql
dbms_output.put_line(‘SQL%NotFound：true’);
else
dbms_output.put_line(‘SQL%NotFound：false’);
end if;
if SQL%Found then --找到上面执行的sql
dbms_output.put_line(‘SQL%Found：true’);
else
dbms_output.put_line(‘SQL%Found：false’);
end if;
if SQL%isopen then --游标是否打开
dbms_output.put_line(‘SQL%isopen：true’);
else
dbms_output.put_line(‘SQL%isopen：false’);
end if;
end;
c，执行的结果：
游标所影响的行数：1
SQL%NotFound：false
SQL%Found：true
SQL%isopen：false
d，编写一个查询的代码对游标属性进行监控
declare
v_info userinfo %ROWTYPE;
begin
select * into v_info from TEST_TABLE where name =‘zs’;
dbms_output.put_line(‘name：’||v_info.name);
dbms_output.put_line(‘age：’||v_info.age);
dbms_output.put_line(‘addr：’||v_info.addr);
dbms_output.put_line(‘游标所影响的行数：’||SQL%rowcount);
if SQL%NotFound then
dbms_output.put_line(‘SQL%NotFound：true’);
else
dbms_output.put_line(‘SQL%NotFound：false’);
end if;
if SQL%Found then
dbms_output.put_line(‘SQL%Found：true’);
else
dbms_output.put_line(‘SQL%Found：false’);
end if;
if SQL%isopen then
dbms_output.put_line(‘SQL%isopen：true’);
else
dbms_output.put_line(‘SQL%isopen：false’);
end if;
end;
e，查询的结果为：
name：zs
age：18
addr：sc
游标所影响的行数：1
SQL%NotFound：false
SQL%Found：true
SQL%isopen：false
3，显示游标
a,游标的声明
（1） 带参数的游标：
CURSOR 游标名 (参数1 数据类型,…)
IS
SELECT语句;
（2） 无参的游标：
CURSOR 游标名
IS
SELECT语句;
注意：SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。
b，打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名(参数1,…) --存在参数
或者
OPEN 游标名 --不存在参数
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
c，提取数据
FETCH 游标名 INTO 变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用 循环语句来实现。控制循环可以通过判断游标的属性来进行。
d，关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。
4,游标的属性
通过游标的属性，来了解游标的执行状态和结果，进而控制游标的执行操作。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
游标的属性有四种:
sql%found （布尔类型， 默认值为null）
sql%notfound（布尔类型, 默认值为null）
sql%rowcount(数值类型， 默认值为0)
sql%isopen(布尔类型)
a, SQL%FOUND和SQL%NOTFOUND
在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，
SQL%FOUND的属性值将是：
.TRUE :INSERT --判断是否插入成功
.TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.–判断修改和删除成功
.TRUE :SELECT INTO至少返回一行 --判断查询成功
当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。
b, SQL%ROWCOUNT
在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL, 对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、delete为0条），SQL%ROWCOUNT的值为0， 而对于update和delete来说表示游标所检索数据库行的个数即更新或者删除的行数。
c，SQL%ISOPEN
SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。