1 / 22

Owners

DB Objects. Owners. Grant. Revoke. (*) with admin option. Grant. 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

malcolm-patton
Download Presentation

Owners

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB Objects Owners Grant Revoke (*) with admin option Grant 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

  2. Precompilazione Precompilato LP Compilazione Codice oggetto Librerie (del DBMS) Collegamento Eseguibile 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

  3. #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; } }

  4. PL/SQL, Oracle’s procedural extension of SQL Advanced fourth-generation programming language (4GL)

  5. 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

  6. Stored procedure/function Anonymousblock DECLARE BEGIN Applicationprocedure/function Applicationtrigger EXCEPTION END; Databasetrigger Package Object type PL/SQL Program Constructs

  7. FOR i IN 1..3 LOOP sequence_of_statements END LOOP; LOOP ... IF condition THEN EXIT; END IF; END LOOP; IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF; WHILE condition LOOP sequence_of_statements END LOOP; PL/SQL Control Structures

  8. Cursor Programma select … DBMS Buffer

  9. DECLARE my_ename VARCHAR2(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; LOOP INTOmy_ename, my_empno, my_sal; END LOOP; CLOSE c1; EXCEPTION END; CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; FETCH c1 EXIT WHEN c1%NOTFOUND; Analyze Data with PL/SQL Code using my_ename, my_empno, my_sal; WHEN OTHERS THEN PL/SQL Code;

  10. DECLARE my_ename emp.ename%TYPE; my_empno emp.empno%TYPE; my_sal emp.sal%TYPE; BEGIN OPEN c1; LOOP INTOmy_ename, my_empno, my_sal; END LOOP; CLOSE c1; EXCEPTION END; CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; FETCH c1 EXIT WHEN c1%NOTFOUND; Analyze Data with PL/SQL Code using my_ename, my_empno, my_sal; WHEN OTHERS THEN PL/SQL Code;

  11. DECLARE c1_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP INTOc1_rec; END LOOP; CLOSE c1; EXCEPTION END; CURSOR c1 is SELECT ename, empno, sal FROM emp ORDER BY sal DESC; FETCH c1 EXIT WHEN c1%NOTFOUND; Analyze Data with PL/SQL Code using c1_rec.ename …… WHEN OTHERS THEN PL/SQL Code;

  12. 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];

  13. 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.

  14. 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;

  15. 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;

  16. 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.

  17. 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.

  18. Using the OPEN-FOR, FETCH, and CLOSE Statements OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string [USING bind_argument[, bind_argument]...]; FETCH {cursor_variable | :host_cursor_variable} INTO {define_variable[, define_variable]... | record}; 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.

  19. 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

  20. WRAP CREATE PACKAGE banking wrapped 012abc463e ... Examples of Wrapping CREATE PACKAGE banking AS min_bal := 100; no_funds EXCEPTION; ... ... END banking; /

More Related