登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

Oracle PL/SQL入门教程  

2009-05-11 12:40:38|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
1.1 PL/SQL简介

  PL/SQL是ORACLE的过程化语言,包括一整套的数据类型、条件结构、循环结构和异常处理结构,PL/SQL可以执行SQL语句,SQL语句中也可以使用PL/SQL函数。

1.2 创建PL/SQL程序块

DECLARE

BEGIN

EXCEPTION
END;
1.3 PL/SQL数据类型

名称
类型
说明

NUMBER
数字型
能存放整数值和实数值,并且可以定义精度和取值范围

BINARY_INTEGER
数字型
可存储带符号整数,为整数计算优化性能

DEC
数字型
NUMBER的子类型,小数

DOUBLE PRECISION
数字型
NUMBER的子类型,高精度实数

INTEGER
数字型
NUMBER的子类型,整数

INT
数字型
NUMBER的子类型,整数

NUMERIC
数字型
NUMBER的子类型,与NUMBER等价

REAL
数字型
NUMBER的子类型,与NUMBER等价

SMALLINT
数字型
NUMBER的子类型,取值范围比INTEGER小

VARCHAR2
字符型
存放可变长字符串,有最大长度

CHAR
字符型
定长字符串

LONG
字符型
变长字符串,最大长度可达32,767

DATE
日期型
以数据库相同的格式存放日期值

BOOLEAN
布尔型
TRUE OR FALSE

ROWID
ROWID
存放数据库的行号


例子:
DECLARE
  ORDER_NO NUMBER(3);
  CUST_NAME VARCHAR2(20);
  ORDER_DATE DATE;
  EMP_NO INTEGER:=25;
  PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;
1.4 处理PL/SQL的异常

1.4.1 PL/SQL的异常

例如:
DECLARE
  X NUMBER;
BEGIN
  X:= 'yyyy';--Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
  DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

实现技术:
EXCEPTION WHEN first_exception THEN

WHEN second_exception THEN

WHEN OTHERS THEN
/*THERS异常处理器必须排在最后,它处理所有没有明确列出的异常。*/

END;

1.4.2 预定义异常

异常名称
ORACLE错误
说明

CURSOR_ALREADY_OPEN
ORA-6511
试图打开一个已打开的光标

DUP_VAL_ON_INDEX
ORA-0001
试图破坏一个唯一性限制

INVALID_CURSOR
ORA-1001
试图使用一个无效的光标

INVALID_NUMBER
ORA-1722
试图对非数字值进行数字操作

LOGIN_DENIED
ORA-1017
无效的用户名或者口令

NO_DATA_FOUND
ORA-1403
查询未找到数据

NOT_LOGGED_ON
ORA-1012
还未连接就试图数据库操作

PROGRAM_ERROR
ORA-6501
内部错误

ROWTYPE_MISMATCH
ORA-6504
主变量和光标的类型不兼容

STORAGE_ERROR
ORA-6500
内部错误

TIMEOUT_ON_RESOURCE
ORA-0051
发生超时

TOO_MANY_ROWS
ORA-1422
SELECT INTD命令返回的多行

TRANSACTION_BACKED_OUT
ORA-006
由于死锁提交被退回

VALUE_ERROR
ORA-6502
转换或者裁剪错误

ZERO_DIVIDE
ORA-1476
试图被零除


 

1.4.3 自定义异常处理

DECLARE
  BAD_ROWID EXCEPTION;
  X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
  SELECT ROWID INTO X FROM TAB
  WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
  DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;

  注意:-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误,该语句的语法如下:
  PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是负数,因为错误号被认为负数,当定义错误时记住使用负号

1.4.4 自定义异常

  异常不一定必须是oracle返回的系统错误,用户可以在自己的应用程序中创
建可触发及可处理的自定义异常
DECLARE
  SALARY_CODE VARCHAR2(1);
  INVALID_SALARY_CODE EXCEPTION;
BEGIN
  SALARY_CODE:='X';
  IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
  RAISE INVALID_SALARY_CODE;
  END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
  DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;

1.5 在PL/SQL中单条记录的查询

  在PL/SQL内,有时在没有定义显式光标的情况下需要查询单条记录,并把记录的数据赋给变量。
DECLARE
  ln_dno NUMBER;
  lvs_dname VARCHAR2(40);
BEGIN
  SELECT DEPT_NO,DEPT_NAME
    INTO ln_dno,lvs_dname
  FROM dept
  WHERE DEPT_NO=1;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||'.'||lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;

1.6 用光标查询多条记录

    光标(CURSOR)是指向一个称为上下文相关区的区域的指针,这个区域在服务器的处理过程全局区(PGA)内,当服务器上执行了一个查询后,查询返回的记录集存放在上下文相关区,通过光标上的操作可以把这些记录检索到客户端的应用程序。

1.6.1 使用光标的基本方法

DECLARE
  CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  OPEN C1;
  FETCH C1 INTO VNAME;
  WHILE C1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||' '||VNAME);
    FETCH C1 INTO VNAME;
  END LOOP;
END;

属性
含量

%FOUND
布尔型属性,当最近一次该记录时成功返回,则值为TRUE

%NOTFOUND
布尔型属性,它的值总与%FOUND属性的值相反

%ISOPEN
布尔型属性,当光标是打开时返回TRUE

%ROWCOUNT
数字型属性,返回已从光标中读取的记录数


 

1.6.2 使用光标FOR循环

DECLARE
  CURSOR C1 IS
  SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
BEGIN
  FOR I IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
  NULL;
END;

1.6.3 带参数的光标

DECLARE
  CURSOR C1(VIEW_PATTERN VARCHAR2) IS
    SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE VIEW_NAME LIKE VIEW_PATTERN||'%' AND
    ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  FOR I IN C1('USER_AR') LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE();
  FOR I IN C1('USER') LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('AAA');
END;

1.7 创建代表数据库记录和列的变量

变量名 基表名.列名%TYPE
DECLARE
  D_NO DEPT.DEPT_NO%TYPE;
  D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
  SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME
  FROM DEPT;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
END;

变量名 基表名%ROWTYPE
DECLARE
  D VEQU12%ROWTYPE;
BEGIN
  SELECT ASSET12ID,ASSET12NAME
    INTO D.ASSET12ID, D.ASSET12NAME
    FROM VEQU12;
  DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
  NULL;
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
说明:
  当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时,可以实际使用%TYPE属性和%ROWTYPE属性,使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时,用户的PL/SQL代码仍可正常工作。

1.8 怎样用PL/SQL表实现数组功能

  PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。
  Type <类型名> Is
  Table Of <数据类型>
  Index by Binary_Integer;
以下为一个例子:
Declare
  Type Array_type is
    Table Of Number
    Index
1.1 PL/SQL简介

  PL/SQL是ORACLE的过程化语言,包括一整套的数据类型、条件结构、循环结构和异常处理结构,PL/SQL可以执行SQL语句,SQL语句中也可以使用PL/SQL函数。

1.2 创建PL/SQL程序块

DECLARE

BEGIN

EXCEPTION
END;
1.3 PL/SQL数据类型

名称
类型
说明

NUMBER
数字型
能存放整数值和实数值,并且可以定义精度和取值范围

BINARY_INTEGER
数字型
可存储带符号整数,为整数计算优化性能

DEC
数字型
NUMBER的子类型,小数

DOUBLE PRECISION
数字型
NUMBER的子类型,高精度实数

INTEGER
数字型
NUMBER的子类型,整数

INT
数字型
NUMBER的子类型,整数

NUMERIC
数字型
NUMBER的子类型,与NUMBER等价

REAL
数字型
NUMBER的子类型,与NUMBER等价

SMALLINT
数字型
NUMBER的子类型,取值范围比INTEGER小

VARCHAR2
字符型
存放可变长字符串,有最大长度

CHAR
字符型
定长字符串

LONG
字符型
变长字符串,最大长度可达32,767

DATE
日期型
以数据库相同的格式存放日期值

BOOLEAN
布尔型
TRUE OR FALSE

ROWID
ROWID
存放数据库的行号


例子:
DECLARE
  ORDER_NO NUMBER(3);
  CUST_NAME VARCHAR2(20);
  ORDER_DATE DATE;
  EMP_NO INTEGER:=25;
  PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;
1.4 处理PL/SQL的异常

1.4.1 PL/SQL的异常

例如:
DECLARE
  X NUMBER;
BEGIN
  X:= 'yyyy';--Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
  DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

实现技术:
EXCEPTION WHEN first_exception THEN

WHEN second_exception THEN

WHEN OTHERS THEN
/*THERS异常处理器必须排在最后,它处理所有没有明确列出的异常。*/

END;

1.4.2 预定义异常

异常名称
ORACLE错误
说明

CURSOR_ALREADY_OPEN
ORA-6511
试图打开一个已打开的光标

DUP_VAL_ON_INDEX
ORA-0001
试图破坏一个唯一性限制

INVALID_CURSOR
ORA-1001
试图使用一个无效的光标

INVALID_NUMBER
ORA-1722
试图对非数字值进行数字操作

LOGIN_DENIED
ORA-1017
无效的用户名或者口令

NO_DATA_FOUND
ORA-1403
查询未找到数据

NOT_LOGGED_ON
ORA-1012
还未连接就试图数据库操作

PROGRAM_ERROR
ORA-6501
内部错误

ROWTYPE_MISMATCH
ORA-6504
主变量和光标的类型不兼容

STORAGE_ERROR
ORA-6500
内部错误

TIMEOUT_ON_RESOURCE
ORA-0051
发生超时

TOO_MANY_ROWS
ORA-1422
SELECT INTD命令返回的多行

TRANSACTION_BACKED_OUT
ORA-006
由于死锁提交被退回

VALUE_ERROR
ORA-6502
转换或者裁剪错误

ZERO_DIVIDE
ORA-1476
试图被零除


 

1.4.3 自定义异常处理

DECLARE
  BAD_ROWID EXCEPTION;
  X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
  SELECT ROWID INTO X FROM TAB
  WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
  DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;

  注意:-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误,该语句的语法如下:
  PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是负数,因为错误号被认为负数,当定义错误时记住使用负号

1.4.4 自定义异常

  异常不一定必须是oracle返回的系统错误,用户可以在自己的应用程序中创
建可触发及可处理的自定义异常
DECLARE
  SALARY_CODE VARCHAR2(1);
  INVALID_SALARY_CODE EXCEPTION;
BEGIN
  SALARY_CODE:='X';
  IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
  RAISE INVALID_SALARY_CODE;
  END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
  DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;

1.5 在PL/SQL中单条记录的查询

  在PL/SQL内,有时在没有定义显式光标的情况下需要查询单条记录,并把记录的数据赋给变量。
DECLARE
  ln_dno NUMBER;
  lvs_dname VARCHAR2(40);
BEGIN
  SELECT DEPT_NO,DEPT_NAME
    INTO ln_dno,lvs_dname
  FROM dept
  WHERE DEPT_NO=1;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||'.'||lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;

1.6 用光标查询多条记录

    光标(CURSOR)是指向一个称为上下文相关区的区域的指针,这个区域在服务器的处理过程全局区(PGA)内,当服务器上执行了一个查询后,查询返回的记录集存放在上下文相关区,通过光标上的操作可以把这些记录检索到客户端的应用程序。

1.6.1 使用光标的基本方法

DECLARE
  CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  OPEN C1;
  FETCH C1 INTO VNAME;
  WHILE C1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||' '||VNAME);
    FETCH C1 INTO VNAME;
  END LOOP;
END;

属性
含量

%FOUND
布尔型属性,当最近一次该记录时成功返回,则值为TRUE

%NOTFOUND
布尔型属性,它的值总与%FOUND属性的值相反

%ISOPEN
布尔型属性,当光标是打开时返回TRUE

%ROWCOUNT
数字型属性,返回已从光标中读取的记录数


 

1.6.2 使用光标FOR循环

DECLARE
  CURSOR C1 IS
  SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE ROWNUM<=10
    ORDER BY VIEW_NAME;
BEGIN
  FOR I IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
  NULL;
END;

1.6.3 带参数的光标

DECLARE
  CURSOR C1(VIEW_PATTERN VARCHAR2) IS
    SELECT VIEW_NAME
    FROM ALL_VIEWS
    WHERE VIEW_NAME LIKE VIEW_PATTERN||'%' AND
    ROWNUM<=10
    ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  FOR I IN C1('USER_AR') LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE();
  FOR I IN C1('USER') LOOP
    DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('AAA');
END;

1.7 创建代表数据库记录和列的变量

变量名 基表名.列名%TYPE
DECLARE
  D_NO DEPT.DEPT_NO%TYPE;
  D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
  SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME
  FROM DEPT;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
END;

变量名 基表名%ROWTYPE
DECLARE
  D VEQU12%ROWTYPE;
BEGIN
  SELECT ASSET12ID,ASSET12NAME
    INTO D.ASSET12ID, D.ASSET12NAME
    FROM VEQU12;
  DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
  NULL;
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
说明:
  当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时,可以实际使用%TYPE属性和%ROWTYPE属性,使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时,用户的PL/SQL代码仍可正常工作。

1.8 怎样用PL/SQL表实现数组功能

  PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。
  Type <类型名> Is
  Table Of <数据类型>
  Index by Binary_Integer;
以下为一个例子:
Declare
  Type Array_type is
    Table Of Number
    Index by Binary_Integer;
  My_Array Array_type;
Begin
  For I In 1..10 Loop
    My_Array(I) := I*2;
  End Loop;
  For I In 1..10 Loop
    Dbms_Output.Put_line(To_char(My_Array(I)));
  End Loop;
End;




引文来源  [教程转帖分享]Oracle PL/SQL入门教程[大家论坛-大家学习网论坛] by Binary_Integer;
  My_Array Array_type;
Begin
  For I In 1..10 Loop
    My_Array(I) := I*2;
  End Loop;
  For I In 1..10 Loop
    Dbms_Output.Put_line(To_char(My_Array(I)));
  End Loop;
End;




引文来源  [教程转帖分享]Oracle PL/SQL入门教程[大家论坛-大家学习网论坛]
  评论这张
 
阅读(777)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018