l9 l10 server side programming
DESCRIPTION
TRANSCRIPT
![Page 1: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/1.jpg)
Database SystemsDatabase Systems
1
Server side Database Server side Database Programming using PL/SQLProgramming using PL/SQL
Version 1.1Version 1.1
![Page 2: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/2.jpg)
2
![Page 3: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/3.jpg)
What is PL/SQL? SQL
Structured language to access databaseANSI StandardDeclarative
○ Specifies what to access but not how to - SELECT id, first_name, family_name FROM
authors PL/SQL
Developed by Oracle as a procedural extension to SQL
Declare variables, IF..ELSE, WHILE & FOR loops, functions, procedures and other features found in a programming language
3
![Page 4: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/4.jpg)
Server Side Programming PL/SQL executes inside DBMS
DBMS maintains Relational dataAlso stores and executes Procedural code!
AdvantagesResults from one query can be used as a
basis for the next query without having to pull data from DBMS to client side for processing!
4
![Page 5: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/5.jpg)
Client Side Procedural logic DBMS maintains relational data Client side programs implement procedural
logic
Is there a problem? Performance impact due increased network
traffic Cumulative effect if many clients run at the same
time Code re-use may not be possible
Many applications may incorporate processing that are quite similar but sharing may not be easy!
5
![Page 6: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/6.jpg)
PL/SQL - Basics Block structured
Basic program unit is a blockContains variables, code and error handler
A BLOCK is contained within BEGIN and END statements with executable commands in betweenMust contain some commands, even if they do
nothing! PL/SQL programs must at least contain 1
block Blocks can be nested (block within another
block)
6
![Page 7: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/7.jpg)
PL/SQL – Block Syntax
DECLARE
variable declarations
BEGIN
program code
EXCEPTION
exception handler code
END;
7
Optional
![Page 8: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/8.jpg)
PL/SQL - EXCEPTIONs EXCEPTIONS
“unexpected errors” that occur during execution occurs at run-time not at compile time!
EXCEPTION HANDLER Code that executes when EXCEPTION occursMakes the code more robust
Oracle Server has many pre-defined errorsno_data_found, value_error, too_many_rows, others
8
![Page 9: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/9.jpg)
PL/SQL - Anonymous BlockDECLARE
today date;
BEGIN
SELECT sysdate INTO today FROM dual;
DBMS_OUTPUT.PUT_LINE (‘Today -’ || today);
END;
• What exactly happens in this code?• What is DUAL? (revision test !)
9
![Page 10: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/10.jpg)
PL/SQL - Named Block Has a name and stored in Oracle Server Contains Header section
name, key word - a function, procedure or triggertype of value it returns in case of function
At the time of creation, the code within the named block is NOT executed but compiled and stored in Oracle Server
10
![Page 11: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/11.jpg)
PL/SQL - Data type - CharacterDECLARE
family_name VARCHAR2 (20);
Age NUMBER(3);
Assignment
family_name := ‘Anderson’;
Age := 21;
11
![Page 12: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/12.jpg)
PL/SQL – %Type %TYPE
To map a variable directly to the same datatype as the table column
DECLAREauthor_id AUTHORS.ID%TYPE;
PL/SQL variable ‘author_id’ is of same datatypewhich is used to define column name ‘id’ of table AUTHORS.
If column type changes PL/SQL Code would still work! {Example: VARCHAR2(20) to VARCHAR(30)}
12
![Page 13: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/13.jpg)
PL/SQL - Scope Rules
Variables, procedures and functions can be referenced by the code executing inside the block in which they are defined
Understanding of scope of variables, functions is especially important in the context of nested blocks!
13
![Page 14: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/14.jpg)
PL/SQL Scope ExampleDECLARE
father_name VARCHAR2(20):='Patrick';date_of_birth DATE:='20-Apr-1972';
BEGINDECLARE child_name VARCHAR2(20):='Mike'; date_of_birth DATE:='12-Dec-2002';
BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name: '||
father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth); DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);END;
DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);END;/
14
![Page 15: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/15.jpg)
PL/SQL Operators Expressions consist of PL/SQL operators
and operandsArithmetic Operators
○ **, *, /, +, -Comparison Operators
○ =, <>, !=, <, >, <=, >=, LIKE, BETWEEN, IN, IS NULL
Logical Operators○ AND, OR, NOT
String Operator – Concatenation using ‘||’
15
![Page 16: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/16.jpg)
PL/SQL – NULL
NULL means “UNKNOWN” value Use IS NULL or IS NOT NULL to check
for NULL value NULL value comparison with ‘= NULL’
or ‘!= NULL’ may produce unpredictable results!
Use NVL function when appropriateNVL (<expression>, <value if expression is NULL>)
16
![Page 17: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/17.jpg)
PL/SQL – SELECT sampleDECLARE name VARCHAR2(20); surname VARCHAR2(20);BEGIN SELECT first_name, family_name INTO name, surname FROM AUTHORS WHERE id = 1 ; DBMS_OUTPUT.PUT_LINE (‘Row selected is : ‘ || name || ‘-’ || surname);END;
17
![Page 18: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/18.jpg)
Performing DML Operations from PL/SQL (INSERT, UPDATE, and DELETE)
You can write INSERT, UPDATE, and DELETE statements directly in PL/SQL programs, without any special notation:
%ROWCOUNT Attribute: How Many Rows Affected So Far?
ExampleSET SERVEROUTPUT ON; BEGIN UPDATE employees SET salary = salary * 1.05
WHERE ...; dbms_output.put_line('Updated ' || SQL%ROWCOUNT || '
salaries.'); END; /
18
![Page 19: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/19.jpg)
PL/SQL – Program Flow Control Conditional execution
IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIFCASE
Repeated execution until some condition LOOP-END LOOP, FOR-LOOP-END
LOOPWHILE-LOOP-END LOOPEXIT WHEN
Jump to code section GOTO
19
![Page 20: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/20.jpg)
PL/SQL – Conditional ExecutionIF <condition> THEN statement1; statement2; …..END IF; can be evaluated to TRUE, FALSE or
NULL – statement1, statement2 etc., are executed only if it evaluates to TRUE
20
![Page 21: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/21.jpg)
IF Example
IF sales > quota THEN compute_bonus(empid);
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
21
![Page 22: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/22.jpg)
PL/SQL – IF-THEN-ELSE
IF <condition> THEN statement1; statement2; …..ELSE statement3; statement4; …..END IF;
22
![Page 23: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/23.jpg)
PL/SQL IF-THEN-ELSIF
IF <condition1> THEN statement1; …..ELSIF <condition2> statement3; ……ELSE …..END IF;
23
![Page 24: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/24.jpg)
Example
BEGIN ... IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus :=
500; ELSE bonus := 100; END IF; INSERT INTO payroll VALUES
(emp_id, bonus, ...); END;
24
![Page 25: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/25.jpg)
25
![Page 26: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/26.jpg)
PL/SQL – Simple Loop The EXIT-WHEN statement lets you complete a
loop if further processing is impossible or undesirable.
When the EXIT statement is encountered, the condition in the WHEN clause is evaluated.
If the condition is true, the loop completes and control passes to the next statement.E.G.
LOOP statement1; …… EXIT {WHEN ….} ;END LOOP;
26
![Page 27: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/27.jpg)
PL/SQL LOOP Example In the following example, the loop
completes when the value of total exceeds 25,000:
LOOP ... total := total + salary; EXIT WHEN total > 25000; -- exit loop if
condition is true END LOOP;
27
![Page 28: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/28.jpg)
PL/SQL – FOR LOOP
FOR counter IN number1..number2
LOOP
statement1;
……
END LOOP;
FOR counter IN REVERSE number1..number2
28
![Page 29: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/29.jpg)
PL/SQL – FOR LOOP ExampleFOR i IN 1..3 LOOP -- assign the values 1,2,3 to
i
sequence_of_statements -- executes three times
END LOOP;
29
![Page 30: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/30.jpg)
PL/SQL – WHILE LOOP
WHILE <condition>
LOOP
statement1;
…..
END LOOP;
30
![Page 31: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/31.jpg)
Pre-defined Errors - examples NO_DATA_FOUND
A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.
TOO_MANY_ROWSA SELECT INTO statement returns more than one
row. ZERO_DIVIDE
A program attempts to divide a number by zero.
31
![Page 32: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/32.jpg)
PL/SQL Error Handling example
DECLARE comm_missing EXCEPTION; -- declare own exception name VARCHAR2(20); surname VARCHAR2(20);BEGIN SELECT first_name, family_name INTO name, surname FROM AUTHORS; IF commission IS NULL THEN RAISE comm_missing; -- raise exception END IF; bonus := (salary * 0.10) + (commission * 0.15);EXCEPTION WHEN comm_missing THEN DBMS_OUPUT.PUT_LINE(‘Sorry cannot calculate bonus as there is no
commission’); WHEN TOO_MANY_ROWS THEN DBMS_OUPUT.PUT_LINE(‘To much information to store ‘);END;/
32
![Page 33: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/33.jpg)
Subprograms An ideal way of writing Writing Reusable
PL/SQL Code PL/SQL has two types of subprograms
called procedures and functions, which can take parameters and be invoked (called).
a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:
33
![Page 34: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/34.jpg)
Procedure ExamplePROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN ... END award_bonus; ...
34
![Page 35: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/35.jpg)
Function Example
CREATE OR REPLACE FUNCTION square(
original NUMBER) RETURN NUMBER AS
original_squared NUMBER;
BEGIN
original_squared := original * original;
RETURN original_squared;
END;
/
35
![Page 36: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/36.jpg)
Packages PL/SQL lets you bundle logically related types,
variables, cursors, and subprograms into a package
The packages defines a simple, clear, interface to a set of related procedures and types.
Packages usually have two parts: a specification and a body.
The specification defines the application programming interface; it declares the types, constants, variables, exceptions, cursors, and subprograms.
The body fills in the SQL queries for cursors and the code for subprograms.
36
![Page 37: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/37.jpg)
Packages Example
37
![Page 38: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/38.jpg)
Database Triggers A database trigger is a stored subprogram
associated with a database table, view, or event. The trigger can be called once, when some event
occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement.
The trigger can be called after the event, to record it or take some followup action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules.
38
![Page 39: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/39.jpg)
Triggers Example
CREATE TRIGGER audit_sal
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit VALUES ...
END;
39
![Page 40: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/40.jpg)
Cursors
A cursor is a pointer to the private memory area allocated by the Oracle server.
There are two types of cursors:Implicit cursors: Created and managed
internally by the Oracle server to process SQL statements
Explicit cursors: Explicitly declared by the programmer
40
![Page 41: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/41.jpg)
Processing Explicit Cursors The following three commands are used to
process an explicit cursor:○ OPEN○ FETCH○ CLOSE
Every explicit cursor has the following four attributes:
○ cursor_name%FOUND○ cursor_name%ISOPEN○ cursor_name%NOTFOUND○ cursor_name%ROWCOUNT
41
![Page 42: L9 l10 server side programming](https://reader036.vdocuments.us/reader036/viewer/2022081414/54bd2e474a79593a3a8b45e8/html5/thumbnails/42.jpg)
Cursor Example
42