pl/sql dbms_DBMS中PL / SQL的块结构

悠悠 2023-03-05 12:43 9阅读 0赞

pl/sql dbms

**PL/SQL is Block Structured**. What is Block? PL/SQL programs are built on the basic units. These basic units are the blocks. Blocks can be named as Functions and Procedures or Anonymous Blocks as well. There is a different section of blocks:

**PL / SQL是块结构的** 。 什么是座？ PL / SQL程序建立在基本单元上。 这些基本单位是块。 块也可以命名为“功能和过程”或“匿名块”。 块有不同的部分：

1.  Header Section
    
    标头部分
2.  Declaration Section
    
    申报科
3.  Executable Section
    
    可执行部分
4.  Exception Section
    
    例外部分

This is how **Block Structure works in PL/SQL**...

这就是**块结构在PL / SQL中的工作方式** ...

HEADER
        Type and Name of block 
        DECLARE 
        Variables; Constants; Cursors; 
        BEGIN 
        PL/SQL and SQL Statements
        EXCEPTION 
        Exception handlers
        END;

The **blocks in PL/SQL** are defined by the keywords **DECLARE**, **BEGIN**, **EXCEPTION** and **END** which divides the block into three sections:

**PL / SQL**中的**块**由关键字**DECLARE** ， **BEGIN** ， **EXCEPTION**和**END**定义，该关键字将块分为三个部分：

1.  **Declarative:** Declarative statements are those statements that are used to declare variables, constants and other code elements, which can be further used in that block.
    
    **声明式：**声明式语句是用于声明变量，常量和其他代码元素的语句，可以在该块中进一步使用它们。
2.  **Executable:** Executable statements are those statements that are run when the block is executed.
    
    **可执行文件：**可执行语句是在执行块时运行的那些语句。
3.  **Exception Handling:** Exception Handling is the special structured section that can be used to "catch" or trap any exceptions that are raised when the executable section runs.
    
    **异常处理：**异常处理是特殊的结构化部分，可用于“捕获”或捕获可执行部分运行时引发的任何异常。

To run this section, only the executable section is required. You don't have to declare anything in a block and you also don't have to catch an exception raised in that block.

要运行此部分，仅需要可执行部分。 您不必在块中声明任何内容，也不必捕获在该块中引发的异常。

It is an important point to be taken into count, **a block is itself an executable statement, and so nested blocks can be applied in it**.

要考虑的重要一点是， **块本身就是可执行语句，因此可以在其中应用嵌套的块** 。

**For e.g. –**

**例如–**

DECLARE
        a number;
        text1 varchar2 (20);
        text2 varchar2 (20):= "HI";
        BEGIN
        ------------------------------
        END;

**The important data types in PL/SQL are these:**

**PL / SQL中的重要数据类型如下：**

NUMBER, INTEGER, CHAR, VARCHAR2, DATE etc.

NUMBER，INTEGER，CHAR，VARCHAR2，DATE等

To convert the string into the data: **to\_date('02-05-2007', 'dd-mm-yyyy')**

要将字符串转换为数据： **to\_date('02 -05-2007'，'dd-mm-yyyy')**

**E.g. 1- How to write a HELLO WORLD! Program in PL/SQL?**

**例如1-如何编写HELLO WORLD！** **用PL / SQL编程？**

The **HELLO WORLD! Program** contains an executable section that invokes the procedure DBMS\_OUTPUT.put\_line to display the text on the screen.

**HELLO WORLD！** **程序**包含一个可执行节，该节调用过程DBMS\_OUTPUT.put\_line在屏幕上显示文本。

**Syntax:**

**句法：**

BEGIN
    DBMS_OUTPUT.put_line('HELLO WORLD!');
    END;

**E.g. 2- How to input E.g. 1 in a form of string in a PL/SQL program?**

**例2-如何在PL / SQL程序中以字符串形式输入例1？**

Here, block declares the variable of the type VARCHAR2 (string) whose maximum length is 100 bytes which can hold the string **'HELLO WORLD!'** which is given as input by the user. DBMS\_OUTPUT.put\_line then accepts the variable and not the literal string in order to display it on the screen.

在这里，block声明了VARCHAR2类型的变量(字符串) ，最大长度为100个字节，可以容纳字符串**“ HELLO WORLD！”。** 由用户输入。 然后， DBMS\_OUTPUT.put\_line接受变量而不是文字字符串，以便在屏幕上显示它。

**Syntax:**

**句法：**

DECLARE
    l_messageVARCHAR2 (100):='HELLO WORLD!';
    BEGIN
    DBMS_OUTPUT.put_line(l_message);
    END;

**E.g. 3- How to catch any exception if they might occur in E.g. 2?**

**例3-如果例2中可能发生异常，如何捕获？**

Here, an exception section is added in order to catch the exception (WHEN OTHERS) that might be raised when an error is found in the program and displays an error message which is returned by the SQL (provided by Oracle).

在此处，添加了一个异常部分，以捕获在程序中发现错误时可能引发的异常(当出现其他异常时)，并显示由SQL返回的错误消息(由Oracle提供)。

**Syntax:**

**句法：**

DECLARE
    l_messageVARCHAR2 (100):='HELLO WORLD!';
    BEGIN
    DBMS_OUTPUT.put_line(l_message);
    EXCEPTIONWHENOTHERS
    THEN
    DBMS_OUTPUT.put_line(SQL);
    END;

**E.g. 4- How to nest the blocks as well as join together multiple strings in E.g. 3?**

**例4-如何在例3中嵌套块以及将多个字符串连接在一起？**

Here, In order to nest the blocks, two variable of the type VARCHAR2 (string) whose maximum length is 100 bytes are needed to be declared and a concatenation (||) operator is used to join together multiple strings.

在这里，为了嵌套块，需要声明两个最大长度为100字节的VARCHAR2(字符串)类型的变量，并使用串联( || )运算符将多个字符串连接在一起。

**Syntax:**

**句法：**

DECLARE 
    l_messageVARCHAR2 (100):= 'HELLO'; 
    BEGIN 
    DECLARE 
    l_message2 VARCHAR2 (100):= l_message||' WORLD!';
    BEGIN 
    DBMS_OUTPUT.put_line(l_message2); 
    END; 
    EXCEPTION WHEN OTHERS 
    THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); 
    END;

There are various tools to execute PL/SQL code. The most basic and popular one is SQL\*Plus. It is a command-line interface and is used to execute SQL statements as well as PL/SQL blocks.

有多种工具可以执行PL / SQL代码。 最基本和最受欢迎的一种是SQL \* Plus。 它是一个命令行界面，用于执行SQL语句以及PL / SQL块。

When some developers are solely dependent on SQL\*Plus there are many others who use different IDE (Integrated Development Environment) for the same. The most popular IDE's are:

当某些开发人员仅依赖于SQL \* Plus时，还有许多其他开发人员使用不同的IDE(集成开发环境)。 最受欢迎的IDE是：

1.  Oracle SQL Developer, from Oracle
    
    来自Oracle的Oracle SQL Developer
2.  Toad and SQL Navigator, from Quest Software
    
    Toad和SQL Navigator，来自Quest Software
3.  PL/SQL Developer, from AllroundAutomations
    
    PL / SQL Developer，来自AllroundAutomations

> 翻译自: [https://www.includehelp.com/pl-sql/block-structure-of-pl-sql-in-dbms.aspx][https_www.includehelp.com_pl-sql_block-structure-of-pl-sql-in-dbms.aspx]

pl/sql dbms

[https_www.includehelp.com_pl-sql_block-structure-of-pl-sql-in-dbms.aspx]: https://www.includehelp.com/pl-sql/block-structure-of-pl-sql-in-dbms.aspx