`
haibin369
  • 浏览: 60275 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

PL/SQL的基本应用

    博客分类:
  • SQL
阅读更多

一、PL/SQL代码基本结构


Header(块头单元)

只有命名代码块(例如存储过程,函数)才有,这个单元确定了这个命名块或者程序的调用方式。

Declaration Section (声明单元,可选)

声明变量、常量、用户自定义的数据类型以及游标等。

Execution Section(执行单元)

主程序体,包含何种合法的PL/SQL语句。

Exception Section(异常处理单元,可选)

异常处理程序,当主程序体中代码出现异常时执行这一部分的代码。

 

二、基本流程控制结构

IF条件判断

IF condition_1 THEN 
  statements_1
ELSIF condition_2 THEN 
  statements_2
......
[ELSIF condition_n THEN 
   statements_n]
[ELSE
   else_statements]
END IF;

 

CASE表达式匹配

CASE expression
  WHEN result_1 THEN
    statements_1
  WHEN result_2 THEN
    statements_2
  ......
  [WHEN result_n THEN
     statements_n]
  [ELSE
     else_statements]
END CASE;
 

 LOOP循环

LOOP
  statements
  [IF exit_condition THEN
     EXIT;]
END LOOP;

 或者

LOOP
  statements
  [EXIT WHEN exit_condition]
END LOOP;

 

WHILE循环 

WHILE loop_condition LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 

数值FOR循环

FOR index IN [REVERSE] lowest_num .. highest_num
LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 例:

BEGIN
  --正向循环
  --i从2到10循环执行循环体(自动步进固定为1)
  --输出为2345678910
  FOR i IN 2 .. 10 LOOP
    DBMS_OUTPUT.put(i);
  END LOOP;
  DBMS_OUTPUT.new_line();

  --反向循环
  --i从10到2循环执行循环体(自动步进固定为-1)
  --与正向循环一样,起始数和结束数必须小的在前
  --输出为1098765432
  FOR I IN REVERSE 2 .. 10 LOOP
    DBMS_OUTPUT.put(I);
  END LOOP;
  DBMS_OUTPUT.new_line();
END;

 

游标FOR循环

--cursor_name为游标的名字
--explicit_SELECT_statement为合法的SELECT语句
FOR record IN {cursor_name | explicit_SELECT_statement} LOOP
  statements
  [IF exit_condition THEN 
     EXIT;
   END IF;]
  [EXIT WHEN exit_condition]
END LOOP;

 例:

--测试表与测试数据
CREATE TABLE for_test (
  col NVARCHAR2(10)
);
INSERT INTO for_test values('1');
INSERT INTO for_test values('2');
INSERT INTO for_test values('3');


DECLARE
  CURSOR test_cursor IS
    SELECT * FROM for_test;
BEGIN
  --遍历游标
  --输出123
  FOR record IN test_cursor LOOP
      DBMS_OUTPUT.put(record.col);
  END LOOP;
  DBMS_OUTPUT.new_line();
  
  --遍历SELECT语句的结果集
  --输出123
  FOR record IN (SELECT * FROM for_test) LOOP
      DBMS_OUTPUT.put(record.col);
  END LOOP;
  DBMS_OUTPUT.new_line();
END;

 

 三、存储过程

存储过程(Stored Procedure)是一组为了完成特定功能的PL/SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

CREATE [OR REPLACE] PROCEDURE sp_name[(param1 [IN|OUT|IN OUT] TYPE1[, param2 [IN|OUT|INOUT] TYPE2...])] IS
  [declarations]
BEGIN
  statements
[EXCEPTION
   statements]
END [sp_name];

 例:

 创建存储过程:

--计算base的平方并保存在result里
CREATE OR REPLACE PROCEDURE square(base IN NUMBER, result OUT NUMBER) IS
BEGIN
  result := base*base;
END square;

 调用存储过程:

--调用存储过程
--输出9
DECLARE
  result NUMBER;
BEGIN
  square(3, result);
  DBMS_OUTPUT.PUT_LINE(result);
END;

 删除存储过程:

--删除存储过程square
DROP PROCEDURE square;

 

四、函数

函数(Function)与存储过程类似,也是一组为了完成特定功能的PL/SQL语句集,但是函数是通过RETURN语句而不是通过OUT或者IN OUT参数返回数据。

CREATE [OR REPLACE] FUNCTION func_name[(param1 TYPE1[, param2 TYPE2...])] IS
RETURN return_type
  [declarations]
BEGIN
  statements
  RETURN return_value
  [statements]
  [RETURN return_value]
[EXCEPTION
   statements]
END [func_name];

 例:

创建函数:

--计算并返回base的平方
CREATE OR REPLACE FUNCTION square(base NUMBER) RETURN NUMBER IS
BEGIN
  RETURN(base * base);
END square;

 调用函数:

--调用函数
--输出9
DECLARE
  result NUMBER;
BEGIN
  result:=square(3);
  DBMS_OUTPUT.PUT_LINE(result);
END;

 删除函数:

DROP FUNCTION square;

 

 五、游标

游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理。

1. 显示游标

基本使用语法

DECLARE
  var_1 type_1;
  [var_2 type_2;
   ...
   var_n type_n;]
  CURSOR cursor_name IS explicit_SELECT_statement
BEGIN
  OPEN TEST_CSR;
  FETCH cursor_name INTO var_1[, var_2, ..., var_n];
  [statements]
  CLOSE TEST_CSR;
END;

 例:

DROP TABLE for_test;
--测试表与测试数据
CREATE TABLE for_test (
  col_1 NUMBER,
  col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');

--输出1:a,5:e,2:b,4:d,3:c,3:c,1:a,5:e,2:b,4:d,3:c,3:c,
DECLARE
  var_1  NUMBER;
  var_2  NVARCHAR2(10);
  var_row for_test%ROWTYPE;
  CURSOR my_cursor IS SELECT * FROM for_test;
BEGIN
  OPEN my_cursor;
  --处理第一行,把数据存到与每一列相对应的变量里
  FETCH my_cursor INTO var_1, var_2;
  DBMS_OUTPUT.PUT(var_1||':'||var_2||',');
  
  --把数据存到一个代表一行数据的变量里
  WHILE my_cursor%FOUND LOOP
    FETCH my_cursor INTO var_row;
      DBMS_OUTPUT.PUT(var_row.col_1||':'||var_row.col_2||',');
  END LOOP;
  
  DBMS_OUTPUT.NEW_LINE();
  CLOSE my_cursor;
END;

2. 隐式游标

每一次执行一个SQL DML语句(包括INSERT、UPDATE、MERGE或者DELETE),或者SELECT INTO语句(把数据库的一行返回到一个PL/SQL数据结构中),PL/SQL都会自动声明和使用一个隐式的游标。

DECLARE
  variable_definitions;
BEGIN
  SELECT column_list INTO variable_list [rest_SELECT_statement]
  [statements]
END;

 例:

DECLARE
  var_1 NUMBER;
  var_2 NVARCHAR2(10);
BEGIN
  SELECT * INTO var_1, var_2 FROM for_test WHERE col_1=1;
  DBMS_OUTPUT.put_line(var_2);
END;

 3. 游标属性

%FOUND

若当前游标指向有效的一行,返回TRUE,否则返回FALSE。当游标刚打开还没有FETCH数据时,这个属性会返回FALSE,因此应该在打开游标后先FETCH第一条数据,然后再根据这个属性进行循环获取数据。

%NOTFOUND

与%FOUND相反。

%ISOPEN

如果游标已经打开则返回TRUE,否则返回FALSE。

%ROWCOUNT

返回到目前为止,已经从游标中取出的记录数量。

    

 4. 游标变量

与静态(显式与隐式)游标相比,游标变量不同于特定的查询绑定,而是在打开游标时才能确定所对应的查询,因此游标变量可以依次对应多个查询。

--测试表与测试数据
CREATE TABLE for_test (
  col_1 NUMBER,
  col_2 NVARCHAR2(10)
);
INSERT INTO for_test values(1, 'a');
INSERT INTO for_test values(5, 'e');
INSERT INTO for_test values(2, 'b');
INSERT INTO for_test values(4, 'd');
INSERT INTO for_test values(3, 'c');

--打印出给定table的数据
--table_name为表名(输入)
CREATE OR REPLACE PROCEDURE my_sp(table_name VARCHAR2) IS
  TYPE CSR_REF IS REF CURSOR;
  test_cursor CSR_REF;
  var_1        NUMBER;
  var_2        NVARCHAR2(10);
BEGIN
  OPEN test_cursor FOR
    'SELECT * FROM '||table_name;
  FETCH test_cursor
    INTO var_1, var_2;
  DBMS_OUTPUT.PUT(var_1||','||var_2||',');
    
  WHILE test_cursor%FOUND LOOP
    FETCH test_cursor
      INTO var_1, var_2;
    DBMS_OUTPUT.PUT(var_1||','||var_2||',');
  END LOOP;
  CLOSE test_cursor;
  DBMS_OUTPUT.NEW_LINE;
END ;

 调用

BEGIN
  --输出1,a,5,e,2,b,4,d,3,c,3,c,
  my_sp('for_test');
END;

 

  • 大小: 22.3 KB
分享到:
评论

相关推荐

    pl/sql最新中文手册

    1. **PL/SQL基础**:手册可能会从基础开始,介绍PL/SQL的基本结构,包括声明变量、常量、游标、记录类型等。还会讲解如何编写存储过程、函数和触发器。 2. **控制流程语句**:这包括条件判断(IF-THEN-ELSIF-ELSE)...

    PL/SQL 基本知识

    PL/SQL是Oracle数据库系统中的一个核心组件,全称为“Procedural Language/Structured Query Language”,它是SQL的扩展,增加了编程元素,使得开发者能够编写存储过程、函数、触发器等数据库应用程序。这篇博客主要...

    Oracle PL/SQL实战(待续)

    总而言之,Oracle PL/SQL是Oracle数据库开发的关键技能,掌握其核心概念和应用技巧对于数据库管理员和开发者来说至关重要。通过实践和学习"Oracle PL/SQL实战(待续)"的相关内容,我们可以提升在Oracle环境中解决...

    pl/sql developer11.0

    PL/SQL Developer是一款由Allround Automations公司开发的专业Oracle数据库开发工具,专为编写、调试、测试和管理PL/SQL代码而设计。标题中的“pl/sql developer11.0”指的是该软件的第11个主要版本。在本文中,我们...

    oracle 9i pl/sql程序设计笔记

    PL/SQL程序的基本单位是块,这些块可以是匿名的、带名的,或以子程序的形式存在。 **2.1.1 PL/SQL块的类型** 1. **匿名块(Anonymous Block)**:这类块通常是临时性的,仅用于一次性执行,并不存储在数据库中。...

    Oracle PL/SQL程序设计(第5版)(上下册)

    - **最佳实践**:总结出一系列实用的建议和技巧,帮助读者更好地理解和掌握PL/SQL的应用场景。 ### 结论 《Oracle PL/SQL程序设计(第5版)》是一本全面介绍Oracle 11g中PL/SQL特性的书籍,适合数据库开发人员、管理...

    oracle pl/sql从入门到精通 配套源代码

    Oracle PL/SQL是一种强大的编程语言,它结合了SQL(结构化查询语言)的数据库操作功能与PL/SQL的程序设计特性,广泛应用于Oracle数据库的开发和管理。这本书"Oracle PL/SQL从入门到精通"的配套源代码,显然是为了...

    ORACLE PL/SQL从入门到精通

    ORACLE PL/SQL是从入门到精通的专业知识,涵盖了数据库开发与管理的多个方面,包括...这本书籍将为读者提供一个全面、系统的学习路径,帮助数据库管理员、开发人员深入理解并掌握ORACLE PL/SQL的强大功能和应用技巧。

    Oracle PL/SQL实例编程(PL/SQL经典书籍)

    Oracle PL/SQL是一种强大的编程语言,它将数据库管理和应用程序逻辑紧密结合起来,是Oracle数据库系统中的核心组件之一。这本书“Oracle PL/SQL实例编程”显然旨在深入讲解如何利用PL/SQL进行实际开发工作,通过实例...

    PL/SQL编程基础知识

    ### PL/SQL编程基础知识 #### 一、PL/SQL简介 PL/SQL(Procedural Language for SQL)是一种过程化语言,它结合了SQL的数据操纵功能和过程化语言的...对于初学者来说,掌握这些基本概念是学习和应用PL/SQL的关键。

    PL/SQL下载

    PL/SQL是Oracle数据库系统中的一个关键组成部分,它是一种结合了SQL语言与过程编程的语言,专为数据库管理和应用程序开发设计。PL/SQL全称为“Procedural Language/Structured Query Language”,它提供了丰富的控制...

    Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发

    1. **PL/SQL基础**:涵盖PL/SQL的基本语法,包括变量声明、常量定义、条件语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE, FOR)、异常处理(BEGIN-EXCEPTION-END)以及子程序(PROCEDURE和FUNCTION)的创建与调用。...

    Oracle PL/SQL实例精解 数据库建立代码

    Oracle PL/SQL是一种强大的编程语言,它结合了SQL的数据库操作能力和PL/SQL的结构化编程特性,使得数据库开发者能够创建复杂的应用程序和数据库逻辑。在"Oracle PL/SQL实例精解 数据库建立代码"中,我们将深入探讨...

    Oracle PL/SQL学习官方教材

    在Oracle数据库环境中,PL/SQL是开发高效、可靠和可维护的数据库应用程序的关键工具。以下是对"Oracle PL/SQL学习官方教材"中可能包含的主要知识点的详细解释: 1. **基础概念**:首先,教材会介绍PL/SQL的基本概念...

    Oracle数据库实用教程第三章 PL/SQL程序设计.pptx

    本章节主要介绍了 PL/SQL 程序设计的基础知识,包括 PL/SQL 的优点、运行 PL/SQL 程序、PL/SQL 块结构、PL/SQL 基本语法、PL/SQL 处理流程、异常处理、游标、存储过程和函数、触发器等。 PL/SQL 的优点包括: * ...

    精通pl/sql

    1. **PL/SQL基础**:介绍PL/SQL的基本语法,包括变量声明、常量定义、条件判断语句(IF-THEN-ELSIF-ELSE)、循环结构(WHILE、FOR)、异常处理(BEGIN-EXCEPTION-END)等,以及如何编写存储过程、函数和触发器。...

    PL/SQL编程(电子版)

    【PL/SQL编程】是Oracle数据库中用于数据库管理和应用程序开发的一种高级过程语言,它结合了SQL查询语言的威力和高级编程语言的灵活性。本章详细介绍了PL/SQL的基础知识,帮助初学者快速掌握该语言。 **4.1 PL/SQL...

    PL/SQL基础编程,实例自写

    PL/SQL程序的基本结构由三个部分组成: 1. **声明部分(DECLARE)**:定义变量、常量、游标、异常和局部存储过程或函数。 2. **执行部分(BEGIN...END)**:包含实际的业务逻辑和数据处理操作,可以是控制语句、SQL...

    PL/SQL語法大全

    通过学习PL/SQL,开发人员和DBA可以创建复杂的数据库应用程序,实现高效的数据处理、事务控制和错误处理,从而充分利用Oracle数据库的强大功能。在实践中,PL/SQL是构建高效、稳定和可维护的Oracle解决方案的关键...

    PL/SQL 详解

    PL/SQL块是构成PL/SQL程序的基本单位。每个PL/SQL块都有三个部分:声明部分、执行部分和异常处理部分。 - **声明部分**:定义变量、常量和其他对象。 - **执行部分**:包含具体的逻辑处理代码。 - **异常处理部分**...

Global site tag (gtag.js) - Google Analytics