DB Objects
Owners
Grant
Revoke
Grant
(*) with admin option
Grant
Revoke
Revoke
Roles
Il ruolo ….
È un set di system & object privileges
Non appartiene ad alcuno schema
Può essere “grantato” ad un owner oppure ad un altro ruolo
Può essere enabled / disabled
È integralmente descritto nel dizionario dati
What Is an Oracle Precompiler?
An Oracle Precompiler is a programming tool that allows you to embed SQL statements in a high-level
source program. The precompiler accepts the source program as input, translates the embedded SQL
statements into standard Oracle runtime library calls, and generates a modified source program that
you can compile, link, and execute in the usual way.
Sorgente LP + SQL
Precompilazione
Precompilato LP
Compilazione
Codice oggetto
Librerie (del DBMS)
Collegamento
Eseguibile
#include<stdlib.h>
main(){
exec sql begin declare section;
char *NomeDip = "Manutenzione";
char *CittaDip = “Torino";
int NumeroDip = 20;
exec sql end declare section;
exec sql connect to utente@librobd;
if (sqlca.sqlcode != 0) {
printf("Connessione al DB non riuscita\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:CittaDip,:NumeroDip);
exec sql disconnect all;
}
}
PL/SQL, Oracle’s procedural extension of SQL
Advanced fourth-generation programming language (4GL)
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing
language that offers the following advantages:
- Support for SQL
- Support for object-oriented programming
- Better performance
- Higher productivity
- Full portability
- Tight integration with Oracle
- Tight security
PL/SQL Program Constructs
Anonymous
block
DECLARE
Application
trigger
BEGIN
EXCEPTION
Database
trigger
Stored
procedure/
function
Application
procedure/
function
END;
Package
Object type
PL/SQL Control Structures
IF condition
THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;
LOOP
...
IF condition
THEN
EXIT;
END IF;
END LOOP;
FOR i IN 1..3 LOOP
sequence_of_statements
END LOOP;
WHILE condition LOOP
sequence_of_statements
END LOOP;
Cursor
Programma
select …
Buffer
DBMS
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
my_ename VARCHAR2(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND;
Analyze Data with PL/SQL Code using my_ename, my_empno, my_sal;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN PL/SQL Code;
END;
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
my_ename emp.ename%TYPE;
my_empno emp.empno%TYPE;
my_sal emp.sal%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND;
Analyze Data with PL/SQL Code using my_ename, my_empno, my_sal;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN PL/SQL Code;
END;
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp ORDER BY sal DESC;
c1_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;
Analyze Data with PL/SQL Code using c1_rec.ename ……
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN PL/SQL Code;
END;
What Are Subprograms?
Subprograms are named PL/SQL blocks that can take parameters and be invoked.
PL/SQL has two types of subprograms
procedures
functions.
[CREATE [OR REPLACE]]
PROCEDURE procedure_name[(parameter[, parameter]...)]
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
What Is a PL/SQL Package?
A package is a schema object that groups logically related PL/SQL types, items,
and subprograms.
Packages usually have two parts, a specification and a body, although sometimes
the body is unnecessary.
Advantages of PL/SQL Packages
Modularity
Easier Application Design
Information Hiding
Added Functionality
Better Performance
CREATE OR REPLACE PACKAGE emp_actions AS -- spec
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp
VALUES (empno_seq.NEXTVAL, ename,
job, mgr, SYSDATE, sal,
comm, deptno);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Dynamic SQL
Static SQL statements: do not change from execution to execution.
You need dynamic SQL in the following situations:
•You want to execute a SQL data definition statement (such as CREATE), a data control
statement (such as GRANT), or a session control statement (such as ALTER SESSION). In
PL/SQL, such statements cannot be executed statically.
• You want more flexibility. For example, you might want to defer your choice of schema
objects until run time. Or, you might want your program to build different search conditions
for the WHERE clause of a SELECT statement. A more complex program might choose
from various SQL operations, clauses, etc.
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[,
define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT]
bind_argument]...];
where:
dynamic_string is a string expression that represents a SQL statement or
PL/SQL block
define_variable is a variable that stores a SELECTed column value
record
is a user-defined or %ROWTYPE record that stores a
SELECTed row
bind_argument is an expression whose value is passed to the dynamic SQL
statement or PL/SQL block.
Using the OPEN-FOR, FETCH, and CLOSE Statements
OPEN {cursor_variable | :host_cursor_variable} FO
[USING bind_argument[, bind_argument]...];
FETCH {cursor_variable | :host_cursor_variable}
INTO {define_variable[, define_variable]... | rec
CLOSE {cursor_variable | :host_cursor_variable};
Using Cursor Attributes
Every cursor has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
When appended to the cursor name, they return useful information about the
execution of static and dynamic SQL statements.
PL/SQL Wrapper
The PL/SQL wrapper is a stand-alone utility that hides
application internals by converting PL/SQL source code into
portable object code.
Wrapping offers:
Platform independence
Dynamic loading
Dynamic binding
Dependency checking
Normal importing and exporting when invoked
Examples of Wrapping
WRAP
CREATE PACKAGE banking
AS
min_bal := 100;
no_funds EXCEPTION;
...
...
END banking;
/
CREATE PACKAGE banking
wrapped
012abc463e ...