Oracle DBA Forum  

Вернуться   Oracle DBA Forum > Oracle University Official Study Notes (ENG) > Oracle 10g PL/SQL Fundamentals > Oracle 10g PL/SQL Fundamentals

Ответ
 
Опции темы Опции просмотра
  #1  
Старый 26.12.2009, 17:13
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию 01 Introduction to PL/SQL

__________________
Телеграм чат
Ответить с цитированием
  #2  
Старый 26.12.2009, 17:13
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Objectives



Lesson Aim

This lesson introduces PL/SQL and PL/SQL programming constructs. You learn about the benefits of PL/SQL. You also learn to use iSQL*Plus as a development environment for PL/SQL.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:15.
Ответить с цитированием
  #3  
Старый 26.12.2009, 17:13
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию What Is PL/SQL?



What Is PL/SQL?

Structured Query Language (SQL) is the primary language used to access and modify data in relational databases. There are only a few SQL commands, so you can easily learn and use them. Consider an example:

Код:
SELECT first_name, department_id, salary FROM employees;
The SQL statement shown above is simple and straightforward. However, if you want to alter any data that is retrieved in a conditional manner, you soon encounter the limitations of SQL.

Consider a slightly modified problem statement: For every employee retrieved, check the department_id and the salary. Depending on the departments performance and also the employees salary, you may want to provide varying bonuses to the employees.

Looking at the problem, you know that you have to execute the preceding SQL statement, collect the data, and apply logic to the data. One solution is to write a SQL statement for each department to give bonuses to the employees in that department. Remember that you also have to check the salary component before deciding the bonus amount. This makes it a little complicated. You now feel that it would be much easier if you had conditional statements. PL/SQL is designed to meet such requirements. It provides a programming extension to already-existing SQL.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:18.
Ответить с цитированием
  #4  
Старый 26.12.2009, 17:13
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию About PL/SQL



About PL/SQL

PL/SQL defines a block structure for writing code. Maintaining and debugging the code is made easier with such a structure. One can easily understand the flow and execution of the program unit.

PL/SQL offers modern software engineering features such as data encapsulation, exception handling, information hiding, and object orientation. It brings state-of-the-art programming to the Oracle server and toolset. PL/SQL provides all the procedural constructs that are available in any third-generation language (3GL).
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:27.
Ответить с цитированием
  #5  
Старый 26.12.2009, 17:14
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию PL/SQL Environment



PL/SQL Environment

The slide shows the PL/SQL execution environment in the Oracle database server. A PL/SQL block contains procedural statements and SQL statements. When you submit the PL/SQL block to the server, the PL/SQL engine first parses the block. The PL/SQL engine identifies the procedural statements and SQL statements. It passes the procedural statements to the procedural statement executor and passes the SQL statements to the SQL statement executor individually.

The diagram in the slide shows the PL/SQL engine within the database server. The Oracle application development tools can also contain a PL/SQL engine. The tool passes the blocks to its local PL/SQL engine. Therefore, all procedural statements are executed locally and only the SQL statements are executed in the database. The engine used depends on where the PL/SQL block is being invoked from.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:29.
Ответить с цитированием
  #6  
Старый 26.12.2009, 17:14
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Benefits of PL/SQL



Benefits of PL/SQL

Integration of procedural constructs with SQL: The most important advantage of PL/SQL is the integration of procedural constructs with SQL. SQL is a nonprocedural language. When you issue a SQL command, your command tells the database server what to do. However, you cannot specify how to do it. PL/SQL integrates control statements and conditional statements with SQL, giving you better control of your SQL statements and their execution. Earlier in this lesson, you saw an example of the need for such integration.

Improved performance: Without PL/SQL, you would not be able to logically combine SQL statements as one unit. If you have designed an application containing forms, you may have many different forms with fields in each form. When a form submits the data, you may have to execute a number of SQL statements. SQL statements are sent to the database one at a time. This results in many network trips and one call to the database for each SQL statement, thereby increasing network traffic and reducing performance (especially in a client/server model).

With PL/SQL, you can combine all these SQL statements into a single program unit. The application can send the entire block to the database instead of sending the SQL statements one at a time. This significantly reduces the number of database calls. As the slide illustrates, if the application is SQL intensive, you can use PL/SQL blocks to group SQL statements before sending them to the Oracle database server for execution.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:32.
Ответить с цитированием
  #7  
Старый 26.12.2009, 17:14
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Benefits of PL/SQL



Benefits of PL/SQL (continued)


Modularized program development: A basic unit in all PL/SQL programs is the block. Blocks can be in a sequence or they can be nested in other blocks. Modularized program development has the following advantages:

You can group logically related statements within blocks.
You can nest blocks inside larger blocks to build powerful programs.
You can break your application into smaller modules. If you are designing a complex application, PL/SQL allows you to break down the application into smaller, manageable, and logically related modules.
You can easily maintain and debug the code.


Integration with tools: The PL/SQL engine is integrated in Oracle tools such as Oracle Forms, Oracle Reports, and so on. When you use these tools, the locally available PL/SQL engine processes the procedural statements; only the SQL statements are passed to the database.

Portability: PL/SQL programs can run anywhere an Oracle server runs, irrespective of the operating system and the platform. You do not need to tailor them to each new environment. You can write portable program packages and create libraries that can be reused in different environments.

Exception handling: PL/SQL enables you to handle exceptions efficiently. You can define separate blocks for dealing with exceptions. You will learn more about exception handling later in the course.

PL/SQL shares the same data type system as SQL (with some extensions) and uses the same expression syntax.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:35.
Ответить с цитированием
  #8  
Старый 26.12.2009, 17:14
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию PL/SQL Block Structure



PL/SQL Block Structure

The slide shows a basic PL/SQL block. A PL/SQL block consists of three sections:

Declarative (optional): The declarative section begins with the keyword DECLARE and ends when the executable section starts.
Executable (required): The executable section begins with the keyword BEGIN and ends with END. Observe that END is terminated with a semicolon. The executable section of a PL/SQL block can in turn include any number of PL/SQL blocks.
Exception handling (optional): The exception section is nested within the executable section. This section begins with the keyword EXCEPTION.

In a PL/SQL block, the keywords DECLARE, BEGIN, and EXCEPTION are not terminated by a semicolon. However, the keyword END, all SQL statements, and PL/SQL statements must be terminated with a semicolon.

__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:37.
Ответить с цитированием
  #9  
Старый 26.12.2009, 17:14
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Block Types



Block Types

A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested within another block. There are three types of blocks that make up a PL/SQL program. They are:

Anonymous blocks
Procedures
Functions

Anonymous blocks: Anonymous blocks are unnamed blocks. They are declared inline at the point in an application where they are to be executed and are compiled each time the application is executed. These blocks are not stored in the database. They are passed to the PL/SQL engine for execution at run time. Triggers in Oracle Developer components consist of such blocks. These anonymous blocks get executed at run time because they are inline. If you want to execute the same block again, you have to rewrite the block. You are unable to invoke or call the block that you wrote earlier because blocks are anonymous and do not exist after they are executed.

Subprograms: Subprograms are complementary to anonymous blocks. They are named PL/SQL blocks that are stored in the database. Because they are named and stored, you can invoke them whenever you want (depending on your application). You can declare them either as procedures or as functions. You typically use a procedure to perform an action and a function to compute and return a value.

You can store subprograms at the server or application level. Using Oracle Developer components (Forms, Reports), you can declare procedures and functions as part of the application (a form or report) and call them from other procedures, functions, and triggers within the same application whenever necessary.

Note: A function is similar to a procedure, except that a function must return a value.
__________________
Телеграм чат

Последний раз редактировалось Marley; 25.01.2010 в 16:40.
Ответить с цитированием
  #10  
Старый 26.12.2009, 17:15
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Program Constructs



Program Constructs

The following table outlines a variety of different PL/SQL program constructs that use the basic PL/SQL block. The program constructs are available based on the environment in which they are executed.
__________________
Телеграм чат

Последний раз редактировалось Marley; 03.10.2015 в 23:16.
Ответить с цитированием
Ответ
Опции темы
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход


Текущее время: 18:43. Часовой пояс GMT +3.


Powered by vBulletin®