What is PL SQL for laypeople

Introduction to PL / SQL

Transcript

1 Introduction to PLSQL Procedural extension of the SQL language to include elements such as variables, loops, conditions, exception handling Code runs within the database and is therefore very efficient. In addition, a large number of predefined libraries (packages) are available. Areas of application: anonymous blocks, functions and procedures, methods, trigger Andreas Schmidt Introduction to PLSQL 122

2 Structure of a PLSQL program DECLARE BEGIN EXCEPTION END; - Declarations - actual - program part - error handling Declarations and exception handling are optionally possible any nesting of the blocks within each other Declaration part allows definition of variables, constants, cursors, functions and procedures Variables valid in the block in which they were declared Comments: - I am a comment * I am a multiline comment * Andreas Schmidt Introduction to PLSQL 222

3 data types in PLSQL BINARY_INTEGER Value range NUMBER [(length, decimal place)] 38 places precision CHAR [(length)] maximum length of bytes VARCHAR2 [(length)] BOOLEAN DATE ROWID CURSOR Example DECLARE owner char (10): = me; tablename char (30); bytes number (10): =; today date not zero; ok boolean; pi constant number: =; counter number (5): = 1; CURSOR player_cursor is select * from player order by name; Andreas Schmidt Introduction to PLSQL 322

4 data types in PLSQL Names of tables and columns are available as predefined variables Attribute% TYPE supplies the data type of another variable or a table column Attribute% ROWTYPE supplies the data type of a table line (access to individual fields via. - notation) Examples: DECLARE city_name city.name% type; nevada desert% rowtype; large desert.area% type; capital city_name% type; BEGIN ... size: = nevada.flaeche; Table desert column name in table City Andreas Schmidt Introduction to PLSQL 422

5 Screen output PLSQL programs normally do not generate any screen output special package available for screen output (DBMS_OUTPUT) Screen output with procedure: DBMS_OUTPUT.PUT_LINE (); Screen output can be switched on and off (default OFF) Statement (SQLPLUS session) 1: SET SERVEROUTPUT ON OFF [SIZE ] Example: SQL> set serveroutput on size BEGIN DBMS_OUTPUT.PUT_LINE (Hello designated PLSQL crack !!); END; 1. SQLDeveloper: Menu >> View >> DBMS output (press the plus sign and enter the appropriate database connection) Andreas Schmidt Introduction to PLSQL 522

6 Control Structures Assignments Assignment operator: = a: = b * c; INTO operator with SELECT (only if exactly one data record is returned) declare anzahl_millionen_staedte number; select count (*) into anzahl_millionen_staedte from mondial.city where population>; dbms_output.put_line ('there are' anzahl_millionen_staedte 'megacities in the Mondial database'); FETCH ... INTO (with cursor) FETCH cur_station INTO s_id, x, y; Andreas Schmidt Introduction to PLSQL 622

7 control structures (conditional statements) conditional branch IF THEN END IF; Alternative IF THEN ELSE END IF; several alternatives IF THEN ELSIF THEN ELSIF THEN ELSE END IF; Andreas Schmidt Introduction to PLSQL 722

8 control structures (loops) LOOP ... END LOOP a: = 0; LOOP a: = a + 1; if a = 50 then exit; end if; END LOOP; FOR LOOP ... END LOOP FOR i IN LOOP insert into book (chapter, text) values ​​(i, text (i)); END LOOP; Backward FOR i in REVERSE loop dbms_output.put_line (i); END LOOP; WHILE LOOP ... END LOOP a: = 0; WHILE a <= 50 LOOP b: = b + a; a: = a + 1; END LOOP; Andreas Schmidt Introduction to PLSQL 822

9 Exception handling Handling of errors and exceptional situations by exception handler If recording occurs, the normal process is aborted and error handling is carried out. Error handling takes place in the EXCEPTION block. Exceptions can be predefined or user-defined. Example (predefined exception) DECLARE a_city mondial.city% rowtype; city_name varchar2 (20): = 'Karlsruhe'; - alternatively: 'Metropolis' 'Springfield' BEGIN SELECT * INTO a_city FROM mondial.city WHERE name = city_name; dbms_output.put_line (a_city.name 'has' a_city.population 'inhabitants'); EXCEPTION when no_data_found then dbms_output.put_line ('city' city_name 'does not exist'); when too_many_rows then dbms_output.put_line ('city' city_name 'there are multiple'); END; Andreas Schmidt Introduction to PLSQL 922

10 predefined exceptions Faculty IWI exception Oracle Error SQLCODE ACCESS_INTO_NULL ORA COLLECTION_IS_NULL ORA CURSOR_ALREADY_OPEN ORA DUP_VAL_ON_INDEX ORA INVALID_CURSOR ORA INVALID_NUMBER ORA LOGIN_DENIED ORA NO_DATA_FOUND ORA NOT_LOGGED_ON ORA PROGRAM_ERROR ORA ROWTYPE_MISMATCH ORA STORAGE_ERROR ORA SUBSCRIPT_BEYOND_COUNT ORA SUBSCRIPT_OUTSIDE_LIMIT ORA TIMEOUT_ON_RESOURCE ORA TOO_MANY_ROWS ORA VALUE_ERROR ORA ZERO_DIVIDE ORA Notes: Andreas Schmidt Introduction in PLSQL 1022

11 Exception handling - own exceptions DECLARE exep_moloch exception; a_city mondial.city% rowtype; city_name varchar2 (20): = 'San Francisco'; - alternatively: 'Mexico City' BEGIN SELECT * INTO a_city FROM mondial.city WHERE name = city_name; if a_city.population> then raise exep_moloch; end if; dbms_output.put_line (a_city.name 'has' a_city.population 'inhabitants'); EXCEPTION when exep_moloch then dbms_output.put_line ('city' ein_stadt.name 'is too big for me'); when others then dbms_output.put_line ('error:' sqlerrm); END; Andreas Schmidt Introduction to PLSQL 1122

12 CURSOR SQL statement generally returns a set of tuples back Cursor for the sequential processing of the result set Declaration: DECLARE ... cursor c1 is select * from land; Access to the content via OPEN FETCH CLOSE Status of the cursor% ISOPEN% FOUND% NOTFOUND% ROWCOUNT Andreas Schmidt Introduction to PLSQL 1222

13 CURSOR program code: declare cursor cur_french_city is select * from mondial.city c where c.country = 'F' and c.population is not null order by c.population desc; a_city mondial.city% rowtype; OPEN cur_french_city; FETCH cur_french_city INTO a_city; WHILE cur_french_city% found LOOP dbms_output.put_line ('data set:' cur_french_city% rowcount 'city:' a_city.name '(' a_city.population ')'); FETCH cur_french_city INTO a_city; END LOOP; CLOSE cur_french_city; Output: Data set: 1 City: Paris () Data set: 2 City: Marseille (800550) Data set: 3 City: Lyon (415487) Data set: 4 City: Toulouse (358688) Data set: 5 City: Nice (342439) Data set: 6 city : Strasbourg (252338) Data set: 7 City: Nantes (244995) Data set: 8 City: Bordeaux (210336) Data set: 9 City: Montpellier (207996) Data set: 10 City: Saint Etienne (199396) Data set: 11 City: Rennes (197536 ) Data set: 12 City: Le Havre (195854) Data set: 13 City: Reims (180620) ... Andreas Schmidt Introduction to PLSQL 1322

14 CURSOR short form (without OPEN, FETCH, CLOSE) declare cursor cur_french_city is select * from mondial.city c where c.country = 'F' and c.population is not null order by c.population desc; FOR a_city in cur_french_city LOOP dbms_output.put_line ('data set:' cur_french_city% rowcount 'city:' a_city.name '(' a_city.population ')'); END LOOP; Andreas Schmidt Introduction to PLSQL 1422

15 CURSOR without explicit cursor variable: FOR a_city in (select * from mondial.city c where c.country = 'F' and c.population is not null order by c.population desc) LOOP dbms_output.put_line ('data set:' cur_french_city% rowcount 'City:' a_city.name '(' a_city.population ')'); END LOOP; Andreas Schmidt Introduction to PLSQL 1522

16 CURSOR with parameters in where clause selection criteria can be specified Values ​​can be passed when opening the cursor, e.g .: OPEN cur_city ('d'); Ideal for nested tables Example declare cursor cur_city (country_id char) is select * from mondial.city c where c.country = country_id and c.population is not null order by c.population desc; FOR a_city in cur_city ('d') LOOP dbms_output.put_line ('data set:' cur_city% rowcount 'city:' a_city.name '(' a_city.population ')'); END LOOP; END; Andreas Schmidt Introduction to PLSQL 1622

17 Functions, procedures Definition of procedures and functions within a PLSQL block Integration of procedures and functions as database objects Transfer parameters can be declared as IN, OUT and IN OUT. Transfer parameters can have a default value. Parameter types without a size specification. Example procedure header. procedure spielzug (player_id in integer, destination_station in integer, ticket in varchar default 'Taxi') is - implementation; Procedure function body corresponds to a PLSQL block without the keyword DECLARE Andreas Schmidt Introduction to PLSQL 1722

18 Functions, procedures Function declaration corresponds to procedure declaration with the difference that the result type of the function is specified. Return of the calculated results using the RETURN keyword. Example: function minimum (a in number, b in number) return number is if a

19 Example declare m number; function minimum (a in number, b in number) return number is if a b then res: = a; else res: = b; end if; dbms_output.put_line ('minimum of 12 and -7:' minimum (12, -7)); maximum (2, -4, m); dbms_output.put_line ('maximum of 2 and -4:' m); Andreas Schmidt Introduction to PLSQL 1922

20 ProceduresFunctions as database objects Saving of functionsProcedures as named database objects FunctionsProcesses can be called from SQLPLUS TOAD ..., PLSQL Code or via OCI, ODBC, JDBC. Call (definition right side); SQL> dbms_output.put_line ('fak (5) =' fak (5)); Example of a function declaration: create or replace function fak (z in integer) return number is f number: = 1; for i in 2..z loop f: = f * i; end loop; return f; alternative short form (1-line): SQL> exec dbms_output.put_line ('fak (5) =' fak (5)); Andreas Schmidt Introduction to PLSQL 2022

21 Procedure functions as database objects Errors in the definition of a procedure function can be displayed with the SQLPLUS command show errors. Calling PLSQL functions also within SQL statements. Functions can be assigned execution rights (encapsulation). Functions procedures can be declared as EXTERNAL (implementation in Java, C ++ ,. ..) table user_source contains alll user-defined database objects SQL> desc user_source Name Type NAME VARCHAR2 (30) TYPE VARCHAR2 (12) LINE NUMBER TEXT VARCHAR2 (4000) select text from user_source where name = 'fak' and type = 'function' order by line; Andreas Schmidt Introduction to PLSQL 2122

22 Literature Further Sources of Information Introduction to PLSQL: Oracle PLSQL Programming, 2nd Edition; Steven Feuerstein & Bill Pribyl; German translation by Dorothea Reder; O Reilly; 2nd edition April 2003; ISBN X; Pages 1084; Oracle PLSQL - short & good, 2nd edition; Steven Feuerstein, Bill Pribyl & Chip Dawes; German translation by Wolfgang Gabriel & Lars Schulten; 2nd edition September 2003; ISBN; Pages 134; 8.90 Overview of PLSQL (Oracle pages): Andreas Schmidt Introduction to PLSQL 2222