plsql.zip_plsql_zip资源-CSDN文库

共2个文件

pdf：1个

txt：1个

版权申诉

76 浏览量 2022-09-23 07:50:03 上传评论收藏 133KB ZIP 举报

PLSQL，全称为Procedural Language/Structured Query Language，是Oracle数据库系统中用于扩展SQL的编程语言。它结合了SQL的查询能力与过程性编程语言的控制结构，为数据库应用程序开发提供了强大的工具。在本压缩包文件“plsql.zip_plsql_zip”中，包含了两份与PLSQL相关的资源：一个名为“plsql.pdf”的文档和一个名为“www.pudn.com.txt”的文本文件。 "plsql.pdf"很可能是一个详细的PLSQL教程或参考手册，涵盖了从基础概念到高级特性的全面内容。通常，这样的文档会讲解如何定义变量、编写过程和函数、处理异常、创建存储过程和触发器，以及如何使用游标进行迭代操作。此外，还可能包含有关PL/SQL的数据类型、控制流程语句（如IF-THEN-ELSE、CASE、FOR循环）和块结构（DECLARE-BEGIN-END）的详细信息。通过阅读这份PDF，开发者可以学习如何有效地利用PLSQL进行数据库交互和程序设计。 "www.pudn.com.txt"可能是一个链接或引用，指向pudn.com网站上的更多PLSQL学习资源。pudn.com是一个知名的资源分享网站，包含大量技术文档、代码示例和学习资料。这个文本文件可能是对其他相关教程、示例代码或解答常见问题的链接集合，帮助读者进一步深化对PLSQL的理解和应用。在PLSQL中，重要的是理解其与SQL的关系。SQL用于查询和操作数据，而PLSQL则允许创建复杂的业务逻辑和数据处理过程。它们一起构成了Oracle数据库应用程序的核心。PLSQL支持子程序（包括函数和过程）、记录类型、游标、异常处理和动态SQL，这些特性使得开发者能够编写高效、健壮且可维护的代码。在实际开发中，PLSQL常用于实现数据库级别的业务规则，例如在数据插入、更新或删除时执行的验证和计算。存储过程和函数可以封装常用的操作，提高性能并减少网络流量。异常处理机制使得程序能够优雅地处理错误，而不会中断整个事务。动态SQL则允许在运行时构造和执行SQL语句，提高了代码的灵活性。 PLSQL是Oracle数据库开发不可或缺的一部分，提供了丰富的功能和强大的编程能力。通过深入学习和实践“plsql.zip_plsql_zip”中的资源，开发者不仅可以掌握PLSQL的基本语法，还能掌握如何利用它来构建高效、稳定的数据库应用程序。对于想要在Oracle环境中进行开发的IT专业人士来说，精通PLSQL是一项非常重要的技能。

资源推荐

资源详情

资源评论

收起资源包目录

plsql.zip （2个子文件）

www.pudn.com.txt 218B

plsql.pdf 152KB

4 Application Programming

4.1 PL/SQL

4.1.1 Introduction

The development of database applications typically requires language constructs similar to those

that can be found in programming languages such as C, C++, or Pascal. These constructs are

necessary in order to implement complex data structures and algorithms. A major restriction

of the database language SQL, however, is that many tasks cannot be accomplished by using

only the provided language elements.

PL/SQL (Procedural Language/SQL) is a procedural extension of Oracle-SQL that oﬀers lan-

guage constructs similar to those in imperative programming languages. PL/SQL allows users

and designers to develop complex database applications that require the usage of control struc-

tures and procedural elements such as procedures, functions, and modules.

The basic construct in PL/SQL is a block. Blocks allow designers to combine logically related

(SQL-) statements into units. In a block, constants and variables can be declared, and variables

can be used to store query results. Statements in a PL/SQL block include SQL statements,

control structures (loops), condition statements (if-then-else), exception handling, and calls of

other PL/SQL blocks.

PL/SQL blocks that specify procedures and functions can be grouped into packages. A package

is similar to a module and has an interface and an implementation part. Oracle oﬀers several

predeﬁned packages, for example, input/output routines, ﬁle handling, job scheduling etc. (see

directory $ORACLE

HOME/rdbms/admin).

Another important feature of PL/SQL is that it oﬀers a mechanism to process query results

in a tuple-oriented way, that is, one tuple at a time. For this, cursors are used. A cursor

basically is a pointer to a query result and is used to read attribute values of selected tuples

into variables. A cursor typically is used in combination with a loop construct such that each

tuple read by the cursor can be processed individually.

In summary, the major goals of PL/SQL are to

• increase the expressiveness of SQL,

• process query results in a tuple-oriented way,

• optimize combined SQL statements,

• develop modular database application programs,

• reuse program code, and

• reduce the cost for maintaining and changing applications.

4.1.2 Structure of PL/SQL-Blocks

PL/SQL is a block-structured language. Each block builds a (named) program unit, and

blocks can be nested. Blocks that build a procedure, a function, or a package must be named.

A PL/SQL block has an optional declare section, a part containing PL/SQL statements, and an

optional exception-handling part. Thus the structure of a PL/SQL looks as follows (brackets

[ ] enclose optional parts):

[<Block header>]

[declare

<User deﬁned exceptions>]

begin

[exception

<Exception handling>]

end;

The block header speciﬁes whether the PL/SQL block is a procedure, a function, or a package.

If no header is speciﬁed, the block is said to be an anonymous PL/SQL block. Each PL/SQL

block again builds a PL/SQL statement. Thus blocks can be nested like blocks in conventional

programming languages. The scope of declared variables (i.e., the part of the program in which

one can refer to the variable) is analogous to the scope of variables in programming languages

such as C or Pascal.

4.1.3 Declarations

Constants, variables, cursors, and exceptions used in a PL/SQL block must be declared in the

declare section of that block. Variables and constants can be declared as follows:

<variable name> [constant] <data type> [not null] [:= <expression>];

Valid data types are SQL data types (see Section 1.1) and the data type boolean. Boolean

data may only be true, false,ornull. The not null clause requires that the declared variable

must always have a value diﬀerent from null. <expression> is used to initialize a variable.

If no expression is speciﬁed, the value null is assigned to the variable. The clause constant

states that once a value has been assigned to the variable, the value cannot be changed (thus

the variable becomes a constant). Example:

declare

hire

date date; /* implicit initialization with null */

job

title varchar2(80) := ’Salesman’;

emp

found boolean; /* implicit initialization with null */

salary

incr constant number(3,2) := 1.5; /* constant */

...

begin ...end;

Instead of specifying a data type, one can also refer to the data type of a table column (so-called

anchored declaration). For example, EMP.Empno%TYPE refers to the data type of the column

Empno in the relation EMP. Instead of a single variable, a record can be declared that can store a

complete tuple from a given table (or query result). For example, the data type DEPT%ROWTYPE

speciﬁes a record suitable to store all attribute values of a complete row from the table DEPT.

Such records are typically used in combination with a cursor. A ﬁeld in a record can be accessed

using <record name>.<column name>, for example, DEPT.Deptno.

A cursor declaration speciﬁes a set of tuples (as a query result) such that the tuples can be

processed in a tuple-oriented way (i.e., one tuple at a time) using the fetch statement. A cursor

declaration has the form

cursor <cursor name> [(<list of parameters>)] is <select statement>;

The cursor name is an undeclared identiﬁer, not the name of any PL/SQL variable. A parameter

has the form <parameter name><parameter type>. Possible parameter types are char,

varchar2, number, date and boolean as well as corresponding subtypes such as integer.

Parameters are used to assign values to the variables that are given in the select statement.

Example: We want to retrieve the following attribute values from the table EMP in a tuple-

oriented way: the job title and name of those employees who have been hired

after a given date, and who have a manager working in a given department.

cursor employee

cur (start date date, dno number) is

select JOB, ENAME from EMP E where HIREDATE > start

date

and exists (select ∗ from EMP

where E.MGR = EMPNO and DEPTNO = dno);

If (some) tuples selected by the cursor will be modiﬁed in the PL/SQL block, the clause for

update[(<column(s)>)] has to be added at the end of the cursor declaration. In this case

selected tuples are locked and cannot be accessed by other users until a commit has been

issued. Before a declared cursor can be used in PL/SQL statements, the cursor must be

opened, and after processing the selected tuples the cursor must be closed. We discuss the

usage of cursors in more detail below.

Exceptions are used to process errors and warnings that occur during the execution of PL/SQL

statements in a controlled manner. Some exceptions are internally deﬁned, such as ZERO

DIVIDE.

Other exceptions can be speciﬁed by the user at the end of a PL/SQL block. User deﬁned ex-

ceptions need to be declared using <name of exception> exception. We will discuss exception

handling in more detail in Section 4.1.5

4.1.4 Language Elements

In addition to the declaration of variables, constants, and cursors, PL/SQL oﬀers various lan-

guage constructs such as variable assignments, control structures (loops, if-then-else), procedure

and function calls, etc. However, PL/SQL does not allow commands of the SQL data deﬁnition

language such as the create table statement. For this, PL/SQL provides special packages.

Furthermore, PL/SQL uses a modiﬁed select statement that requires each selected tuple to be

assigned to a record (or a list of variables).

There are several alternatives in PL/SQL to a assign a value to a variable. The most simple

way to assign a value to a variable is

declare

counter integer := 0;

...

begin

counter := counter + 1;

Values to assign to a variable can also be retrieved from the database using a select statement

select <column(s)> into <matching list of variables>

from <table(s)> where <condition>;

It is important to ensure that the select statement retrieves at most one tuple ! Otherwise

it is not possible to assign the attribute values to the speciﬁed list of variables and a run-

time error occurs. If the select statement retrieves more than one tuple, a cursor must be used

instead. Furthermore, the data types of the speciﬁed variables must match those of the retrieved

attribute values. For most data types, PL/SQL performs an automatic type conversion (e.g.,

from integer to real).

Instead of a list of single variables, a record can be given after the keyword into. Also in this

case, the select statement must retrieve at most one tuple !

declare

employee

rec EMP%ROWTYPE;

max

sal EMP.SAL%TYPE;

begin

select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO

into employee

rec

from EMP where EMPNO = 5698;

select max(SAL) into max

sal from EMP;

...

end;

PL/SQL provides while-loops, two types of for-loops, and continuous loops. Latter ones

are used in combination with cursors. All types of loops are used to execute a sequence of

statements multiple times. The speciﬁcation of loops occurs in the same way as known from

imperative programming languages such as C or Pascal.

A while-loop has the pattern

[<< <label name>>>]

while <condition> loop

<sequence of statements>;

end loop [<label name>];

A loop can be named. Naming a loop is useful whenever loops are nested and inner loops are

completed unconditionally using the exit <label name>; statement.

Whereas the number of iterations through a while loop is unknown until the loop completes,

the number of iterations through the for loop can be speciﬁed using two integers.

[<< <label name>>>]

for <index> in [reverse] <lower bound>..<upper bound> loop

end loop [<label name>];

The loop counter <index> is declared implicitly. The scope of the loop counter is only the

for loop. It overrides the scope of any variable having the same name outside the loop. Inside

the for loop, <index> can be referenced like a constant. <index> may appear in expressions,

but one cannot assign a value to <index>. Using the keyword reverse causes the iteration to

proceed downwards from the higher bound to the lower bound.

Processing Cursors: Before a cursor can be used, it must be opened using the open statement

open <cursor name> [(<list of parameters>)] ;

The associated select statement then is processed and the cursor references the ﬁrst selected

tuple. Selected tuples then can be processed one tuple at a time using the fetch command

fetch <cursor name> into <list of variables>;

The fetch command assigns the selected attribute values of the current tuple to the list of

variables. After the fetch command, the cursor advances to the next tuple in the result set.

Note that the variables in the list must have the same data types as the selected values. After

all tuples have been processed, the close command is used to disable the cursor.

close <cursor name>;

The example below illustrates how a cursor is used together with a continuous loop:

declare

cursor emp

cur is select ∗ from EMP;

emp

rec EMP%ROWTYPE;

emp

sal EMP.SAL%TYPE;

begin

open emp

cur;

loop

fetch emp

cur into emp rec;

exit when emp

cur%NOTFOUND;

emp

sal := emp rec.sal;

end loop;

close emp

cur;

...

end;

评论收藏

内容反馈

版权申诉

小波思基

粉丝: 81
资源: 1万+

plsql.zip_plsql_zip

PLSQL.zip_zip

PLSQL.zip_plsql redis_非关系型数据库

PLSQL.zip_Only

PLSQL.zip_oracle_plsql

PLSQL.zip_oracl_oracle pl/sql ppt_pl sql ppt tutorial_pl/sql_pls

plsql.zip_PLSQL Developer_cursor

PLSQL 绿色版_免安装ORACLE.zip

pl_sql.zip_fetch_pl/sql_pl_sql_plsql_plsql java

plsql.zip_plsql_sql_zip

plsql.zip_plsql

PLSQL.zip压缩包

PLSQL_Developer_14.0.0.1961_x86-x64.zip

PLSQL.zip访问数据库工具

SQL_PLSQL.zip_people

plsql12_all.zip

PLSQL.zip，解压，配置一下环境变量，即可使用

oracle-plsql.zip_oracle_pl sql 分组查询_plsql_plsql 分组查询

oracle_plsql.zip_editplus plsql_editplus sql acp_oracle_oracle p

VNC+PLSQL.zip

plsqldeveloper1105_x64_instantclient_11_2.zip

PLSQL Developer.zip

PLSQL_plsqldev9.zip

Plsql_13_instant_18.zip

ORACLE8 PLSQL程序设计_sql_code.zip_oracle pl/sql_pl sql code_pl/sql_

PLSQL_Developer_10.0.5-chinese_peatezhu.zip|PLSQL_Developer_10.0.5-chinese_peatezhu.zip

PLSQL.Developer.v7.1.0.zip

PLSqlDeveloper.zip

instantclient.zip

PLSQLDeveloper64_解压版.zip

最新资源