Writing executable statements

Содержание

Слайд 2

Objectives After completing this lesson, you should be able to do

Objectives

After completing this lesson, you should be able to do the

following:
Identify lexical units in a PL/SQL block
Use built-in SQL functions in PL/SQL
Describe when implicit conversions take place and when explicit conversions have to be dealt with
Write nested blocks and qualify variables with labels
Write readable code with appropriate indentation
Use sequences in PL/SQL expressions
Слайд 3

Lexical Units in a PL/SQL Block Lexical units: Are building blocks

Lexical Units in a PL/SQL Block

Lexical units:
Are building blocks of any

PL/SQL block
Are sequences of characters including letters, numerals, tabs, spaces, returns, and symbols
Can be classified as:
Identifiers: v_fname, c_percent
Delimiters: ; , +, -
Literals: John, 428, True
Comments: --, /* */
Слайд 4

PL/SQL Block Syntax and Guidelines Literals Character and date literals must

PL/SQL Block Syntax and Guidelines

Literals
Character and date literals must be enclosed

in single quotation marks.
Numbers can be simple values or in scientific notation.
Statements can span several lines.

name := 'Henderson';

1

2

3

Слайд 5

Commenting Code Prefix single-line comments with two hyphens (--). Place multiple-line

Commenting Code

Prefix single-line comments with two hyphens (--).
Place multiple-line comments between

the symbols /* and */.
Example:

DECLARE
...
v_annual_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_annual_sal := monthly_sal * 12;
--The following line displays the annual salary DBMS_OUTPUT.PUT_LINE(v_annual_sal);
END;
/

Слайд 6

SQL Functions in PL/SQL Available in procedural statements: Single-row functions Not

SQL Functions in PL/SQL

Available in procedural statements:
Single-row functions
Not available in procedural

statements:
DECODE
Group functions
Слайд 7

SQL Functions in PL/SQL: Examples Get the length of a string:

SQL Functions in PL/SQL: Examples

Get the length of a string:
Get the

number of months an employee has worked:

v_desc_size INTEGER(5);
v_prod_description VARCHAR2(70):='You can use this product with your radios for higher frequency';
-- get the length of the string in prod_description
v_desc_size:= LENGTH(v_prod_description);

v_tenure:= MONTHS_BETWEEN (CURRENT_DATE, v_hiredate);

Слайд 8

Using Sequences in PL/SQL Expressions Starting in 11g: Before 11g: DECLARE

Using Sequences in PL/SQL Expressions

Starting in 11g:
Before 11g:

DECLARE
v_new_id NUMBER;
BEGIN
SELECT

my_seq.NEXTVAL INTO v_new_id FROM Dual;
END;
/

DECLARE
v_new_id NUMBER;
BEGIN
v_new_id := my_seq.NEXTVAL;
END;
/

Слайд 9

Data Type Conversion Converts data to comparable data types Is of

Data Type Conversion

Converts data to comparable data types
Is of two types:
Implicit

conversion
Explicit conversion
Functions:
TO_CHAR
TO_DATE
TO_NUMBER
TO_TIMESTAMP
Слайд 10

Слайд 11

Data Type Conversion date_of_joining DATE:= '02-Feb-2000'; date_of_joining DATE:= 'February 02,2000'; date_of_joining

Data Type Conversion

date_of_joining DATE:= '02-Feb-2000';

date_of_joining DATE:= 'February 02,2000';

date_of_joining DATE:= TO_DATE('February 02,2000','Month

DD, YYYY');

1

2

3

Слайд 12

Nested Blocks PL/SQL blocks can be nested. An executable section (BEGIN

Nested Blocks

PL/SQL blocks can be nested.
An executable section (BEGIN … END)

can contain nested blocks.
An exception section can contain nested blocks.
Слайд 13

Nested Blocks: Example DECLARE v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE'; BEGIN DECLARE v_inner_variable VARCHAR2(20):='LOCAL

Nested Blocks: Example
DECLARE
v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
DECLARE
v_inner_variable VARCHAR2(20):='LOCAL VARIABLE';
BEGIN

DBMS_OUTPUT.PUT_LINE(v_inner_variable);
DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;
DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;
Слайд 14

Variable Scope and Visibility DECLARE v_father_name VARCHAR2(20):='Patrick'; v_date_of_birth DATE:='20-Apr-1972'; BEGIN DECLARE

Variable Scope and Visibility
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';

v_date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
/

1

2

Слайд 15

Слайд 16

Qualify an Identifier BEGIN > DECLARE v_father_name VARCHAR2(20):='Patrick'; v_date_of_birth DATE:='20-Apr-1972'; BEGIN

Qualify an Identifier

BEGIN <>
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972';
BEGIN
DECLARE
v_child_name VARCHAR2(20):='Mike';

v_date_of_birth DATE:='12-Dec-2002';
BEGIN
DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '
||outer.v_date_of_birth);
DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
END;
END outer;
Слайд 17

Determining Variable Scope: Example BEGIN > DECLARE v_sal NUMBER(7,2) := 60000;

Determining Variable Scope: Example

BEGIN <>
DECLARE
v_sal NUMBER(7,2) := 60000;
v_comm NUMBER(7,2)

:= v_sal * 0.20;
v_message VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
v_sal NUMBER(7,2) := 50000;
v_comm NUMBER(7,2) := 0;
v_total_comp NUMBER(7,2) := v_sal + v_comm;
BEGIN
v_message := 'CLERK not'||v_message;
outer.v_comm := v_sal * 0.30;
END;
v_message := 'SALESMAN'||v_message;
END;
END outer;
/

1

2

Слайд 18

Слайд 19

Operators in PL/SQL Logical Arithmetic Concatenation Parentheses to control order of

Operators in PL/SQL

Logical
Arithmetic
Concatenation
Parentheses to control order of operations
Exponential operator (**)

Same

as in SQL
Слайд 20

Operators in PL/SQL: Examples Increment the counter for a loop. Set

Operators in PL/SQL: Examples

Increment the counter for a loop.
Set the value

of a Boolean flag.
Validate whether an employee number contains a value.

loop_count := loop_count + 1;

good_sal := sal BETWEEN 50000 AND 150000;

valid := (empno IS NOT NULL);

Слайд 21

Programming Guidelines Make code maintenance easier by: Documenting code with comments

Programming Guidelines

Make code maintenance easier by:
Documenting code with comments
Developing a case

convention for the code
Developing naming conventions for identifiers and other objects
Enhancing readability by indenting
Слайд 22

Indenting Code For clarity, indent each level of code. BEGIN IF

Indenting Code

For clarity, indent each level of code.

BEGIN
IF x=0 THEN

y:=1;
END IF;
END;
/

DECLARE
deptno NUMBER(4);
location_id NUMBER(4);
BEGIN
SELECT department_id,
location_id
INTO deptno,
location_id
FROM departments
WHERE department_name
= 'Sales';
...
END;
/

Слайд 23

Quiz You can use most SQL single-row functions such as number,

Quiz

You can use most SQL single-row functions such as number, character,

conversion, and date single-row functions in PL/SQL expressions.
True
False
Слайд 24

Summary In this lesson, you should have learned how to: Identify

Summary

In this lesson, you should have learned how to:
Identify lexical

units in a PL/SQL block
Use built-in SQL functions in PL/SQL
Write nested blocks to break logically related functionalities
Decide when to perform explicit conversions
Qualify variables in nested blocks
Use sequences in PL/SQL expressions
Слайд 25

Practice 3: Overview This practice covers the following topics: Reviewing scoping

Practice 3: Overview

This practice covers the following topics:
Reviewing scoping and nesting

rules
Writing and testing PL/SQL blocks
Слайд 26

Слайд 27

Слайд 28

- Предыдущая
Вклад Т. Шульца и Г. Беккера в развитие теории человеческого капитала
Следующая -
Introduction to PL/SQL

Похожие презентации

Локальные и глобальные компьютерные сети
Система автономной навигации антропоморфного робота
Творческий проект по информатике. Новогодняя анимация
Информационные процессы в естественных и искусственных системах 10 класс
Группа Fps Team
Алгоритми і проекти
Автоматизированные информационные системы Федеральной налоговой службы
№2 Организация сети с использованием коммутатора
20 секретных функций Microsoft Word
Manual QA course. Базы данных ( Lecture 16.1)
WinApi. Основные типы данных
Информационная деятельность человека (3 класс)
Алгоритмическая конструкция Ветвление. 8 класс
Презентация "Информационные процессы в информационных объектах" - скачать презентации по Информатике
Решение логических задач
Опасности Интернета. Вирусы
Технологии построения web-интерфейсов
С компьютером на "ты"
Программы для восстановления данных на жестких дисках
Прикладная информатика. (Лекция 1)
Информационная культураества
Журнал транзакций
Автоматизированная информационная система результатов обучающихся в детской спортивной школе
Архитектура операционных систем
Виды геометрических моделей, их свойства. Понятия о каркасном и твердотельном моделировании. (Лекция 6)
Игры на ПК и на айфон
Правила безопасности в кабинете информатики Презентацию выполнил ученик 8б класса Заикин Кирилл
Схема распайки разъёма XLR. Основы технологий обработки аудио- и видеоинформации
Вы можете прислать нам Вашу презентацию и мы опубликуем ее на сайте.
Обратная связь
Для правообладателей
Email: Нажмите что бы посмотреть