PL/MySQL 是否存在？

PL/MySQL does it exist?

提问by user1081596

In Oracle there is PL/SQL, a powerful imperative language. Is there anything similar for MySQL?

在 Oracle 中有 PL/SQL，这是一种强大的命令式语言。MySQL有没有类似的东西？

回答by Michael Capobianco

While MySQL does have similar components, no, you cannot use PL\SQL in MySQL. The same goes for T-SQL used by MS SQL Server.

虽然 MySQL 确实有类似的组件，但不能在 MySQL 中使用 PL\SQL。MS SQL Server 使用的 T-SQL 也是如此。

MySQL has plenty of documentation on it at their website.

MySQL在他们的网站上有很多关于它的文档。

As much as I'd hate to refer to Yahoo Answers, there's a good explanation Here

尽管我很不想参考 Yahoo Answers，但这里有一个很好的解释

You'll see that both PL\SQL and T-SQL are Turing-complete, and probably provide slightly more functionality. But MySQL has plenty of ways to perform similar tasks.

您会看到 PL\SQL 和 T-SQL 都是图灵完备的，并且可能提供更多的功能。但是 MySQL 有很多方法可以执行类似的任务。

Here is the content from the Yahoo Answers post:

以下是 Yahoo Answers 帖子中的内容：

MySQL is a multithreaded, multi-user SQL database management system (DBMS)1which has, according to MySQL AB, more than 10 million installations.

Libraries for accessing MySQL databases are available in all major programming languages with language-specific APIs. In addition, an ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database, such as ASP or Coldfusion. The MySQL server and official libraries are mostly implemented in ANSI C.

MySQL is popular for web applications and acts as the database component of the LAMP, MAMP, and WAMP platforms (Linux/Mac/Windows-Apache-MySQL-PHP/Perl... and for open-source bug tracking tools like Bugzilla. Its popularity as a web application is closely tied to the popularity of PHP, which is often combined with MySQL. PHP and MySQL are essential components for running the popular WordPress blogging platform.

The following features are implemented by MySQL but not by some other RDBMSes:

• Multiple storage engines, allowing you to choose the one which is most effective for each table in the application (in MySQL 5.0, storage engines must be compiled in; in MySQL 5.1, storage engines can be dynamically loaded at run time): o Native storage engines (MyISAM, Falcon, Merge, Memory (heap), Federated, Archive, CSV, Blackhole, Cluster) -Partner-developed storage engines (InnoDB, solidDB, NitroEDB, BrightHouse) -Community-developed storage engines
• Custom storage engines
• Commit grouping, gathering multiple transactions from multiple connections together to increase the number of commits per second.

Note:: MySQL is written in C and C++. The SQL parser uses yacc and home-brewed lexer. A document describing some of the internal structures of the code and the coding guidelines is available from the MySQL web site.

SQL

---

SQL commonly expanded as Structured Query Language, is a computer language designed for the retrieval and management of data in relational database management systems, database schema creation and modification, and database object access control management.

The SQL language is sub-divided into several language elements, including:

• Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.
• Queries which retrieve data based on specific criteria.
• Expressions which can produce either scalar values or tables consisting of columns and rows of data.
• Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and are commonly used to limit the effects of statements and queries, or to change program flow.
• Clauses which are (in some cases optional) constituent components of statements and queries

it works under..concept of query data manipulation data defination transaction control

The SQL:2003 standard makes minor modifications to all parts of SQL:1999, and officially introduces a few new features such as:1

• XML-related features
• window functions
• the sequence generator, which allows standardized sequences
• two new column types: auto-generated values and identity-columns
• the new MERGE statement
• extensions to the CREATE TABLE statement, to allow "CREATE TABLE AS" and "CREATE TABLE LIKE"
• removal of the poorly-implemented "BIT" and "BIT VARYING" data types

PL/SQL

---

PL/SQL is Oracle Corporation's proprietary server-based procedural extension to the SQL database language. (Some other SQL database management systems offer languages similar to PL/SQL.) Its syntax strongly resembles that of Ada.

PL/SQL supports variables, conditions, arrays, and exceptions. Implementations from version 8 of the Oracle RDBMS onwards have included features associated with object-orientation.

The underlying SQL functions as a declarative language. Standard SQL—unlike some functional programming languages—does not require implementations to convert tail calls to jumps. SQL does not readily provide "first row" and "rest of table" accessors, and it cannot easily perform some constructs such as loops. PL/SQL, however, as a Turing-complete procedural language which fills in these gaps, allows Oracle database developers to interface with the underlying relational database in an imperative manner. SQL statements can make explicit in-line calls to PL/SQL functions, or can cause PL/SQL triggers to fire upon pre-defined Data Manipulation Language (DML) events.

PL/SQL stored procedures (functions, procedures, packages, and triggers) which perform DML get compiled into an Oracle database: to this extent their SQL code can undergo syntax-checking. Programmers working in an Oracle database environment can construct PL/SQL blocks of such functionality to serve as procedures, functions; or they can write in-line segments of PL/SQL within SQL*Plus scripts.

While programmers can readily incorporate SQL DML statements into PL/SQL (as cursor definitions, for example, or using the SELECT ... INTO syntax), Data Definition Language (DDL) statements such as CREATE TABLE/DROP INDEX etc require the use of "Dynamic SQL". Earlier versions of Oracle required the use of a complex built-in DBMS_SQL package for Dynamic SQL where the system needed to explicitly parse and execute an SQL statement. Later versions have included an EXECUTE IMMEDIATE syntax called "Native Dynamic SQL" which considerably simplifies matters. Any use of DDL in Oracle will result in an implicit commit. Programmers can also use Dynamic SQL to execute DML where they do not know the exact content of the statement in advance.

PL/SQL offers several pre-defined packages for specific purposes. Such PL/SQL packages include:

• DBMS_OUTPUT - for output operations to non-database destinations
• DBMS_JOB - for running specific procedures/functions at a particular time (i.e. scheduling)
• DBMS_XPLAN - for formatting "Explain Plan" output
• DBMS_SESSION - provides access to SQL ALTER SESSION and SET ROLE statements, and other session information.
• DBMS_METADATA - for extracting meta data from the data dictionary (such as DDL statements)
• UTL_FILE - for reading and writing files on disk
• UTL_HTTP - for making requests to web servers from the database
• UTL_SMTP - for sending mail from the database (via an SMTP server)

Oracle Corporation customarily adds more packages and/or extends package functionality with each successive release of the Oracle DBMS.

MySQL 是一个多线程、多用户 SQL 数据库管理系统 (DBMS) 1，据 MySQL AB 称，该系统已安装超过 1000 万次。

用于访问 MySQL 数据库的库在所有主要编程语言中都可用，并具有特定于语言的 API。此外，名为 MyODBC 的 ODBC 接口允许支持 ODBC 接口的其他编程语言与 MySQL 数据库进行通信，例如 ASP 或 Coldfusion。MySQL 服务器和官方库大多是用 ANSI C 实现的。

MySQL 在 Web 应用程序中很受欢迎，并作为 LAMP、MAMP 和 WAMP 平台（Linux/Mac/Windows-Apache-MySQL-PHP/Perl...以及像 Bugzilla 这样的开源错误跟踪工具）的数据库组件。作为 Web 应用程序的流行与 PHP 的流行密切相关，PHP 经常与 MySQL 结合。PHP 和 MySQL 是运行流行的 WordPress 博客平台的必不可少的组件。

MySQL 实现了以下功能，但其他一些 RDBMS 没有实现：

• 多个存储引擎，让您可以选择对应用中每个表最有效的一个（在 MySQL 5.0 中，必须编译存储引擎；在 MySQL 5.1 中，存储引擎可以在运行时动态加载）： o 原生存储引擎（MyISAM、Falcon、Merge、Memory（heap）、Federated、Archive、CSV、Blackhole、Cluster） - 合作伙伴开发的存储引擎（InnoDB、solidDB、NitroEDB、BrightHouse） - 社区开发的存储引擎
• 自定义存储引擎
• 提交分组，将来自多个连接的多个事务收集在一起以增加每秒提交的次数。

注意：MySQL 是用 C 和 C++ 编写的。SQL 解析器使用 yacc 和自制词法分析器。MySQL 网站提供了描述代码的一些内部结构和编码指南的文档。

SQL

---

SQL 通常扩展为结构化查询语言，是一种计算机语言，旨在用于关系数据库管理系统中的数据检索和管理、数据库模式的创建和修改以及数据库对象访问控制管理。

SQL 语言细分为几个语言元素，包括：

• 可能对模式和数据具有持久影响的语句，或者可能控制事务、程序流、连接、会话或诊断的语句。
• 根据特定条件检索数据的查询。
• 可以生成标量值或由数据的列和行组成的表的表达式。
• 谓词指定可以评估为 SQL 三值逻辑 (3VL) 布尔真值的条件，通常用于限制语句和查询的效果，或更改程序流程。
• 作为语句和查询的组成部分（在某些情况下是可选的）的子句

它在......查询数据操作数据定义事务控制的概念下工作

SQL:2003 标准对 SQL:1999 的所有部分进行了微小的修改，并正式引入了一些新特性，例如：1

• XML 相关的特性
• 窗口函数
• 序列生成器，允许标准化序列
• 两种新的列类型：自动生成的值和标识列
• 新的 MERGE 语句
• CREATE TABLE 语句的扩展，以允许“CREATE TABLE AS”和“CREATE TABLE LIKE”
• 删除实施不佳的“BIT”和“BIT VARYING”数据类型

PL/SQL

---

PL/SQL 是 Oracle Corporation 对 SQL 数据库语言的专有基于服务器的过程扩展。（其他一些 SQL 数据库管理系统提供类似于 PL/SQL 的语言。）它的语法与 Ada 的语法非常相似。

PL/SQL 支持变量、条件、数组和异常。Oracle RDBMS 第 8 版以后的实现包括与面向对象相关的特性。

底层 SQL 用作声明性语言。与某些函数式编程语言不同，标准 SQL 不需要实现将尾调用转换为跳转。SQL 不容易提供“第一行”和“表的其余部分”访问器，并且它不能轻松执行某些结构，例如循环。然而，PL/SQL 作为填补这些空白的图灵完备过程语言，允许 Oracle 数据库开发人员以命令方式与底层关系数据库交互。SQL 语句可以对 PL/SQL 函数进行显式内联调用，或者可以导致 PL/SQL 触发器根据预定义的数据操作语言 (DML) 事件触发。

执行 DML 的 PL/SQL 存储过程（函数、过程、包和触发器）被编译到 Oracle 数据库中：在这个程度上，它们的 SQL 代码可以进行语法检查。在 Oracle 数据库环境中工作的程序员可以构建具有此类功能的 PL/SQL 块以充当过程、函数；或者他们可以在 SQL*Plus 脚本中编写 PL/SQL 的内嵌段。

虽然程序员可以很容易地将 SQL DML 语句合并到 PL/SQL（例如，作为游标定义，或使用 SELECT ... INTO 语法），但数据定义语言 (DDL) 语句（例如 CREATE TABLE/DROP INDEX 等）需要使用“动态 SQL”。早期版本的 Oracle 需要为动态 SQL 使用复杂的内置 DBMS_SQL 包，其中系统需要显式解析和执行 SQL 语句。后来的版本包含了一个名为“Native Dynamic SQL”的 EXECUTE IMMEDIATE 语法，它大大简化了事情。在 Oracle 中使用 DDL 将导致隐式提交。程序员也可以在事先不知道语句的确切内容的情况下使用动态 SQL 来执行 DML。

PL/SQL 提供了几个用于特定目的的预定义包。此类 PL/SQL 包包括：

• DBMS_OUTPUT - 用于对非数据库目标的输出操作
• DBMS_JOB - 用于在特定时间运行特定程序/功能（即调度）
• DBMS_XPLAN - 用于格式化“解释计划”输出
• DBMS_SESSION - 提供对 SQL ALTER SESSION 和 SET ROLE 语句以及其他会话信息的访问。
• DBMS_METADATA - 用于从数据字典中提取元数据（例如 DDL 语句）
• UTL_FILE - 用于在磁盘上读写文件
• UTL_HTTP - 用于从数据库向 Web 服务器发出请求
• UTL_SMTP - 用于从数据库发送邮件（通过 SMTP 服务器）

Oracle Corporation 通常会在 Oracle DBMS 的每个后续版本中添加更多包和/或扩展包功能。

回答by Rory Hunter

I don't know how powerful they are, but this site gives information on writing MySQL stored procedures with SQL;

我不知道它们有多强大，但是这个站点提供了有关使用 SQL 编写 MySQL 存储过程的信息；

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

E.g.

例如

http://www.mysqltutorial.org/sql-cursor-in-stored-procedures.aspx

http://www.mysqltutorial.org/sql-cursor-in-stored-procedures.aspx

回答by Evan Carroll

Terminology Problem

术语问题

Some relational database vendors follow the traditional definition of SQL, which defines it as a declarative language. Examples of these vendors include PostgreSQL, IBM, Oracle. For these databases vendors you write procedural code in their own procedural languages they provide which may or may not make available SQL.

一些关系数据库供应商遵循 SQL 的传统定义，将其定义为声明性语言。这些供应商的示例包括 PostgreSQL、IBM、Oracle。对于这些数据库供应商，您使用他们自己提供的过程语言编写过程代码，这些语言可能会或可能不会提供 SQL。

Some vendors though see their procedural language as an extension to SQL. These vendors don't have any such differentiation. For example, in MySQL a "Stored Program"is declared with LANGUAGE SQLeven though it provides different language-features (like LEAVE, REPEATand UNTILthat are not provided elsewhere). So you have essentially a different "SQL Language" in a "SQL Routine" then you have in a query expression. Other vendors like Microsoft call everything they do with just one term -- "T-SQL". This dropping of a term makes it hard to find equivalent features in other databases, or to know what features are likely to be different.

不过，一些供应商将他们的过程语言视为 SQL 的扩展。这些供应商没有任何这样的区别。例如，在 MySQL 中，即使“存储程序”LANGUAGE SQL提供了不同的语言功能（例如LEAVE，REPEAT并且UNTIL其他地方没有提供），它也被声明为。因此，“SQL 例程”中的“SQL 语言”本质上与查询表达式中的“SQL 语言”不同。像微软这样的其他供应商只用一个术语来称呼他们所做的一切——“T-SQL”。删除一个术语使得很难在其他数据库中找到等效的特征，或者知道哪些特征可能不同。

The procedural features being entirely standardized are likely to be different. While the declarative query syntax is likely to be similar because there is a spec on it.

完全标准化的程序特征可能会有所不同。虽然声明性查询语法很可能是相似的，因为它有一个规范。

回答by Peter

Google for "Getting started with mysql stored procedures", the syntax is not that far from PLSQL.

谷歌搜索“ mysql存储过程入门”，语法与PLSQL相差不远。

    DELIMITER //     
    CREATE PROCEDURE GetAllProducts()
       BEGIN
       SELECT *  FROM products;
    END //
    DELIMITER ;

回答by Peter

No. pl/sqlis a stored procedure language specific to Oracle. Different databases use different languages for stored procedures. See this pageof the mySqldocumentation for their information on stored procedures.

不是。pl/sql是特定于Oracle的存储过程语言。不同的数据库对存储过程使用不同的语言。看到这个页面中的MYSQL它们对存储过程的信息的文件。