Question? Leave a message!




Introduction to PL/SQL

Introduction to PL/SQL
Introduction to PL/SQL Kristian Torp Department of Computer Science Aalborg University www.cs.aau.dk/˜torp torpcs.aau.dk December 2, 2011 daisy.aau.dk Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 1 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 2 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 3 / 56Learning Outcomes Learning Outcomes Understand how code can be executed within a DBMS Be able to design stored procedures in general Be able to construct and execute stored procedures on Oracle Knowledge about the pros and cons of stored procedures Note That Concepts are fairly DBMS independent All code examples are Oracle specific Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 4 / 56Prerequisites SQL Knowledge about the SQL select statement Knowledge about SQL modification statements, e.g., insert and delete Knowledge about transaction management, e.g., commit and rollback Knowledge about tables, views, and integrity constraints Procedural Programming Language Knowledge about another programming language of the C family Knowledge about data types, e.g., int, long, string, and Boolean Knowledge of control structures, e.g., if, while, for, and case Knowledge of functions, procedures, parameters, and return values Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 5 / 56Missing Standard Unfortunately, the major DBMS vendors each have their own SQL dialect Motivation Purpose Move processing into the DBMS from client programs (database applications) Advantages Code accessible to all applications I Access from different programming languages Very efficient for data intensive processing I Process large data set, small result returned Enhance the security of database applications I Avoid SQL injection attacks http://en.wikipedia.org/wiki/SQLinjection Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 6 / 56Motivation Purpose Move processing into the DBMS from client programs (database applications) Advantages Code accessible to all applications I Access from different programming languages Very efficient for data intensive processing I Process large data set, small result returned Enhance the security of database applications I Avoid SQL injection attacks http://en.wikipedia.org/wiki/SQLinjection Missing Standard Unfortunately, the major DBMS vendors each have their own SQL dialect Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 6 / 56Overview Functionality SQL extended with control structures I Control structures like if and loop statements Used for I Stored procedures (and functions) I Package (Oracle specific) I Triggers I Types a.k.a. classes (Oracle specific) In very widely used in the industry I see http://www.tiobe.com/index.php/content/paperinfo/ tpci/index.html In the SQL standard called SQL/PSM I PSM = Persistent Storage Model Focus The focus is here on stored procedures and packages Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 7 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 8 / 56Motivation for Stored Procedures The Big Four Benefits Abstraction I Increases readability Implementation hiding I Can change internals without effecting clients Modular programs I More manageable and easier to understand Library I Reuse, reuse, and reuse Note This is not different from any other procedural programming language Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 9 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 10 / 56A Procedure: Hello, World Example (The Start Program) create or replace procedure h e l l o w o r l d i s begin dbms output . p u t l i n e ( ’ Hello , World ’ ) ; end ; Note It is a procedure, i.e., not a function I Both a procedure and a function is called a stored procedure It is a begin and end language, not curly brackets:f andg It uses a builtin library dbms output.put line I The package dbms output has the procedure put line I Uses the dot notation for invoking functions myPackage.myProcedure Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 11 / 56A Function: Calculating Your BMI Example (A BMI Function) create or replace f u n c t i o n bmi ( height i n t , weight f l o a t ) r e t u r n f l o a t i s begin i f height = 0.3 or height 3.0 then dbms output . p u t l i n e ( ’ height must be i n 0 . 3 , 3 . 0 meters ’ ) ; end i f ; i f weight = 0 then dbms output . p u t l i n e ( ’ weight must be p o s i t i v e ’ ) ; e l s i f weight 500 then dbms output . p u t l i n e ( ’No human ’ ’ s weight i s 500 kg ’ ) ; end i f ; r e t u r n weight / height2; end ; Note It takes two parameters height and weight It is a function, i.e., has a return statement It is strongly typed language, i.e., parameters and the return value Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 12 / 56 execute the procedure SQLexecute h e l l o w o r l d ; execute the f u n c t i o n SQLexec bmi (1.87 , 90); r e s u l t s i n an error , value returned by f u n c t i o n must be used Wrap the f u n c t i o n c a l l SQLexec dbms output . p u t l i n e ( bmi (1.87 , 9 0 ) ) ; Note Output from server is not enabled by default in a session Return value of a function cannot be ignored Executing Stored Procedures Example (Execute on Oracle server) to enable output from the server SQLset serveroutput on Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 13 / 56 execute the f u n c t i o n SQLexec bmi (1.87 , 90); r e s u l t s i n an error , value returned by f u n c t i o n must be used Wrap the f u n c t i o n c a l l SQLexec dbms output . p u t l i n e ( bmi (1.87 , 9 0 ) ) ; Note Output from server is not enabled by default in a session Return value of a function cannot be ignored Executing Stored Procedures Example (Execute on Oracle server) to enable output from the server SQLset serveroutput on execute the procedure SQLexecute h e l l o w o r l d ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 13 / 56 Wrap the f u n c t i o n c a l l SQLexec dbms output . p u t l i n e ( bmi (1.87 , 9 0 ) ) ; Note Output from server is not enabled by default in a session Return value of a function cannot be ignored Executing Stored Procedures Example (Execute on Oracle server) to enable output from the server SQLset serveroutput on execute the procedure SQLexecute h e l l o w o r l d ; execute the f u n c t i o n SQLexec bmi (1.87 , 90); r e s u l t s i n an error , value returned by f u n c t i o n must be used Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 13 / 56Note Output from server is not enabled by default in a session Return value of a function cannot be ignored Executing Stored Procedures Example (Execute on Oracle server) to enable output from the server SQLset serveroutput on execute the procedure SQLexecute h e l l o w o r l d ; execute the f u n c t i o n SQLexec bmi (1.87 , 90); r e s u l t s i n an error , value returned by f u n c t i o n must be used Wrap the f u n c t i o n c a l l SQLexec dbms output . p u t l i n e ( bmi (1.87 , 9 0 ) ) ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 13 / 56Executing Stored Procedures Example (Execute on Oracle server) to enable output from the server SQLset serveroutput on execute the procedure SQLexecute h e l l o w o r l d ; execute the f u n c t i o n SQLexec bmi (1.87 , 90); r e s u l t s i n an error , value returned by f u n c t i o n must be used Wrap the f u n c t i o n c a l l SQLexec dbms output . p u t l i n e ( bmi (1.87 , 9 0 ) ) ; Note Output from server is not enabled by default in a session Return value of a function cannot be ignored Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 13 / 56Using SQL in Stored Procedures Example (Use the Data Stored) create or replace f u n c t i o n g e t s t a t u s ( s t u d e n t i d number ) r e t u r n varchar2 i s v s t a t u s varchar2 ( 5 0 ) ; begin s e l e c t sta . dsc i n t o v s t a t u s from student stu , status sta where stu . s t a t i d = sta . s t a t i d and stu . sid = s t u d e n t i d ; r e t u r n v s t a t u s ; end ; Note The declaration of the variable v status The usage of the into keyword in the select statement The usage of the parameter student id in the select statement Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 14 / 56Example (Caller) create or replace procedure c a l l p i s begin p ( ’ Hello ’ ) ; p ( ’ World ’ ) ; end ; Note Can call own and builtin stored procedures Will use the procedure p instead of dbms output.put line You are now officially a PL/SQL library builder Calling Other Procedures Example (Callee) create or replace procedure p ( s t varchar2 ) as begin dbms output . p u t l i n e ( s t ) ; end ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 15 / 56Note Can call own and builtin stored procedures Will use the procedure p instead of dbms output.put line You are now officially a PL/SQL library builder Calling Other Procedures Example (Callee) create or replace procedure p ( s t varchar2 ) as begin dbms output . p u t l i n e ( s t ) ; end ; Example (Caller) create or replace procedure c a l l p i s begin p ( ’ Hello ’ ) ; p ( ’ World ’ ) ; end ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 15 / 56Calling Other Procedures Example (Callee) create or replace procedure p ( s t varchar2 ) as begin dbms output . p u t l i n e ( s t ) ; end ; Example (Caller) create or replace procedure c a l l p i s begin p ( ’ Hello ’ ) ; p ( ’ World ’ ) ; end ; Note Can call own and builtin stored procedures Will use the procedure p instead of dbms output.put line You are now officially a PL/SQL library builder Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 15 / 56Control Structures: A Crash Course I Example (The If Statement) create or replace procedure pb ( val boolean ) i s begin i f val = true then dbms output . p u t l i n e ( ’ true ’ ) ; e l s i f val = f a l s e then dbms output . p u t l i n e ( ’ f a l s e ’ ) ; else dbms output . p u t l i n e ( ’ n u l l ’ ) ; end i f ; end ; Note Is this stupid Recall threevalued logic the root of all evil We will use the procedure pb in the code that follows Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 16 / 56Control Structures: A Crash Course II Example (The While Statement) create or replace procedure count 10 i s i i n t := 1; begin while i 10 loop dbms output . p u t l i n e ( i ) ; i := i + 1; end loop ; Note What is printed 1 to 9 or 1 to 10 PL/SQL also has a for statement, very different from C PL/SQL does not have increment/decrement operators, e.g., i or ++j PL/SQL does not have compound assignments, e.g., i+=7 or j=2 Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 17 / 56Surprises: In General Surprises The code is stored in the DBMS f has been replaced by begin andg by end SQL and programming logic blends very nicely I A strongpoint of PL/SQL Procedures are different from functions The assignment operator is := and not = The comparison operator is = and not == Control structures are quite different from the C world Threevalued logic will time and again surprise you Server output is not enabled by default I Which is a big surprise Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 18 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 19 / 56An Example Example (Various Data Types) create or replace procedure use basic types i s v s t r varchar2 (30) := ’A s t r i n g ’ ; v i n t i n t := 265; c p i constant f l o a t := 3.14159265358979323846264338327950288419; v f l o a t f l o a t := v i n t  c p i ; begin p ( v s t r ) ; p ( v i n t ) ; p ( v f l o a t ) ; end ; Output A string 36893488147419103232 115904311329233965478,149216911761758199 Note Forget what you think of data types and size Very high precision on all number types in both SQL and PL/SQL The size of strings must be defined Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 20 / 56Overview: Scalar Data Types Scalar Data Types Description Type Examples smallint 100, 0, 100 Integers int/integer 1000, 0, 1000 positive 0, 1, 2, 3 number 10.3 Floats dec/decimal 123.456, 3.4 real 123456.7890 varchar2 Hello, ThetaJoin Strings nvarchar2 Tøger, Dæmon char World, Noise Boolean Boolean True, False Date/time date 20070909 timestamp 20090909 12:34:56 Note Not all of these data types are available from within SQL Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 21 / 56Quiz: The Decimal Data Type Example (Rouding) create or replace procedure using decimal i s v dec decimal ( 4 , 2 ) ; begin v dec := 12.34; dbms output . p u t l i n e ( v dec ) ; v dec := 12.344; dbms output . p u t l i n e ( v dec ) ; v dec := 12.347; dbms output . p u t l i n e ( v dec ) ; end ; Questions Will this compile, note that it is decimal(4,2) What will be printed (if it compiles) Are you surprised Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 22 / 56Overview: Other Data Types Special Data Types Description Type Record Composite Varray Table BLOB Large Objects CLOB BFILE REF Reference Types REF CURSOR Note We will only use records in this lecture. Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 23 / 56Anchored Data Types: Type Example (Anchor for a Column) create or replace f u n c t i o n get status anchored ( s t u d e n t i d student . sidtype ) r e t u r n status . dsctype i s v s t a t u s status . dsctype ; begin s e l e c t sta . dsc i n t o v s t a t u s from student stu , status sta where stu . s t a t i d = sta . s t a t i d and stu . sid = s t u d e n t i d ; r e t u r n v s t a t u s ; end ; Note The anchored type using the type Very convenient of maintenance reasons (avoid “hardwiring” types) I Widely used, you are encouraged to use it Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 24 / 56Anchored Data Types: Rowtype Example (Anchor for a Table) create or replace procedure get course rowtype ( course id course . cidtype ) i s v row courserowtype ; v tmp varchar2 ( 5 0 0 ) ; begin s e l e c t  i n t o v row from course c where c . cid = course id ; v tmp := v row . cname jj ’ : ’ jj v row . dsc ; p ( v tmp ) ; end ; Note The anchored type using the rowtype I Creates a record The dot notation for access elements of the record Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 25 / 56Surprises: Data Type Note Strings are a basic type, not an object like in Java or C I A maximum size must be specified The sizes of the basic data type are very different from C and Java Date and time are basic data types I This is very handy The anchored types is something new compared to C and Java Booleans are not a basic data type in SQL but in PL/SQL I This sometimes leads to very annoying problems Support for composite data type is not very good in PL/SQL compared to C and Java LOB objects are plain stupid I But sometimes necessary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 26 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 27 / 56Overview Example create or replace procedure p i n ( val i n i n t ) i s v tmp i n t ; begin v tmp := val + 5; val := val + 5; / i l l e g a l val i s readonly  / end ; Example create or replace procedure p i n o u t ( val i n out i n t ) i s begin val := val + 5; end ; Example create or replace procedure c a l l p s i s v i n i n t := 10; v i n o u t i n t := 10; begin p i n ( v i n ) ; p ( v i n ) ; p i n o u t ( v i n o u t ) ; p ( v i n o u t ) ; end ; When execute call ps prints 10 and 15, why Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 28 / 56Quiz Example create or replace procedure p i n o u t ( val i n out i n t ) i s begin val := val + 5; end ; Example create or replace procedure c a l l p s i s v i n i n t := 10; v i n o u t i n t := 10; begin p i n ( v i n ) ; p ( v i n ) ; p i n o u t ( v i n o u t ) ; p ( v i n o u t ) ; end ; Questions What are the formal parameter(s) What are the actual parameter(s) Is it callbyvalue or callbyreference Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 29 / 56Out Parameters Example create or replace procedure get x y coor ( c oo r i d i n i n t , x coor out i n t , y coor out i n t ) i s begin x coor := round ( c oo r i d / 4 . 2 ) ; stupid c a l c u l a t i o n s y coor := round ( c oo r i d / 7 . 5 ) ; end ; Note in and out parameters can both be used in same procedure The out parameters are writeonly More than one value is “returned” by the procedure The calculation is naturally plain stupid round is the builtin rounding function Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 30 / 56Parameter Mode Mode Mode Description in Formal parameter is readonly out Formal parameter is writeonly in out Formal parameter is readwrite Note in is the default parameter mode if the mode is not specified Stored procedures cannot be overloaded on the parameter signature There is a nocopy compiler hint for in out parameters Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 31 / 56What is Wrong Here create procedure proc 1 ( i i n t ) create f u n c t i o n func 1 ( i i n t ) i s r e t u r n i n t i s begin begin snip complicated s t u f f snip complicated s t u f f r e t u r n i ; r e t u r n ’ h e l l o ’ ; end ; end ; A B create f u n c t i o n func 2 ( i i n t ) create f u n c t i o n func 3 ( i i n t ) r e t u r n i n t i s r e t u r n i n t i s begin begin snip complicated s t u f f snip complicated s t u f f r e t u r n i2; p ( ’ h e l l o world ’ ) ; p ( ’ h e l l o world ’ ) ; end ; end ; C D Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 32 / 56Avoid This Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 33 / 56Additional Comments on Parameters Items to Notice A default value can be provided for each parameter Stored procedures cannot be overloaded on the parameter signature Stored procedures can be called by position or by name Works like in most programming languages, however different syntax Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 34 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 35 / 56Solution Solves the wellknown impedance mismatch problem Generality Knowledge about cursors in PL/SQL is directly transferable to many other programming languages. Overview Definition A cursor is a mechanism that ensure a result set can be identified by a declarative language such as SQL and processed by a procedural language such as PL/SQL or C Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 36 / 56Generality Knowledge about cursors in PL/SQL is directly transferable to many other programming languages. Overview Definition A cursor is a mechanism that ensure a result set can be identified by a declarative language such as SQL and processed by a procedural language such as PL/SQL or C Solution Solves the wellknown impedance mismatch problem Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 36 / 56Overview Definition A cursor is a mechanism that ensure a result set can be identified by a declarative language such as SQL and processed by a procedural language such as PL/SQL or C Solution Solves the wellknown impedance mismatch problem Generality Knowledge about cursors in PL/SQL is directly transferable to many other programming languages. Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 36 / 56The Unix ls command Example (List Tables) create or replace procedure l s i s cursor c t a b l e s i s s e l e c t  from cat ; v table name cat . table nametype ; v type cat . t a b l e t y p etype ; begin open c t a b l e s ; loop fet c h c t a b l e s i n t o v table name , v type ; e x i t when c t a b l e snotfound ; p ( v table name ) ; end loop ; close c t a b l e s ; end ; Note The view tab is a table that contains all table names The cursor is declared, opened, fetched, and closed Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 37 / 56Cursor Attributes Attributes Attribute Type Description notfound Boolean True if a record is fetched unsuccessfully found Boolean True if a record is fetched successfully rowcount Integer The number of records fetched from the cursor isopen Boolean True if cursor is open Note There are additional attributes for bulk operations. Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 38 / 56Quiz: Using rowcount Example create or replace procedure l s c n t i s cursor c t a b l e s i s s e l e c t table name from cat ; v table name cat . table nametype ; begin open c t a b l e s ; loop fet c h c t a b l e s i n t o v table name ; e x i t when c t a b l e snotfound ; p ( c t a b l e srowcount jj ’ ’ jj v table name ) ; end loop ; close c t a b l e s ; end ; Question What is printed Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 39 / 56Quiz: Using isopen Example create or replace procedure ls isopen i s cursor c t a b l e s i s s e l e c t table name from cat ; v table name cat . table nametype ; v s t a t u s boolean := f a l s e ; begin v s t a t u s := c t a b l e sisopen ; pb ( v s t a t u s ) ; open c t a b l e s ; v s t a t u s := c t a b l e sisopen ; pb ( v s t a t u s ) ; loop fet c h c t a b l e s i n t o v table name ; e x i t when c t a b l e snotfound ; end loop ; v s t a t u s := c t a b l e sisopen ; pb ( v s t a t u s ) ; close c t a b l e s ; v s t a t u s := c t a b l e sisopen ; pb ( v s t a t u s ) ; end ; Question What is printed Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 40 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 41 / 56Introduction Idea A class like concept Very good for building libraries I A way to cluster related stored procedures Has a header and a body (think Cstyle languages) Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 42 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 43 / 56Note Many more methods can be envisioned Think objectrelational mapping (ORM) tools Introduction Goal To build a uniform way to address the data stored in table Methods Name Description exist(primary key) Return true if primary key exists to string(primary key) Return string representation of row print(primary key) Convenient way to display a row Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 44 / 56Introduction Goal To build a uniform way to address the data stored in table Methods Name Description exist(primary key) Return true if primary key exists to string(primary key) Return string representation of row print(primary key) Convenient way to display a row Note Many more methods can be envisioned Think objectrelational mapping (ORM) tools Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 44 / 56Quiz Why is the method called exist and not exists Header File: Course Table Example (Header) create or replace package ccourse i s f u n c t i o n e x i s t ( cid course . cidtype ) r e t u r n boolean ; f u n c t i o n t o s t r i n g ( cid course . cidtype ) r e t u r n s t r i n g ; procedure p r i n t ( cid course . cidtype ) ; end ; Note The header lists all the public stored procedures The naming convention table name course package name ccourse The design is influenced by the Object class from Java and C Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 45 / 56Header File: Course Table Example (Header) create or replace package ccourse i s f u n c t i o n e x i s t ( cid course . cidtype ) r e t u r n boolean ; f u n c t i o n t o s t r i n g ( cid course . cidtype ) r e t u r n s t r i n g ; procedure p r i n t ( cid course . cidtype ) ; end ; Note The header lists all the public stored procedures The naming convention table name course package name ccourse The design is influenced by the Object class from Java and C Quiz Why is the method called exist and not exists Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 45 / 56Note The method check valid cid is private Body File: Private Method and Cursor Example (Body) create or replace package body ccourse i s p r i v a t e constant c e r r o r c i d n u l l constant i n t := 20001; a cursor used i n the implementation cursor c u r e x i s t ( cv cid course . cidtype ) i s s e l e c t c . cid , c . cname , c . semester , c . dsc from course c where c . cid = cv cid ; a p r i v a t e method procedure c h e c k v a l i d c i d ( cid course . cidtype ) i s begin i f cid i s n u l l then r a i s e a p p l i c a t i o n e r r o r ( c e r r o r c i d n u l l , ’ Course ID i s n u l l ’ ) ; end i f ; end ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 46 / 56Body File: Private Method and Cursor Example (Body) create or replace package body ccourse i s p r i v a t e constant c e r r o r c i d n u l l constant i n t := 20001; a cursor used i n the implementation cursor c u r e x i s t ( cv cid course . cidtype ) i s s e l e c t c . cid , c . cname , c . semester , c . dsc from course c where c . cid = cv cid ; a p r i v a t e method procedure c h e c k v a l i d c i d ( cid course . cidtype ) i s begin i f cid i s n u l l then r a i s e a p p l i c a t i o n e r r o r ( c e r r o r c i d n u l l , ’ Course ID i s n u l l ’ ) ; end i f ; end ; Note The method check valid cid is private Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 46 / 56Body File: The Exist Method Example (Method) f u n c t i o n e x i s t ( cid course . cidtype ) r e t u r n boolean i s r e c e x i s t c u r e x i s trowtype ; begin c h e c k v a l i d c i d ( cid ) ; precondition open c u r e x i s t ( cid ) ; fet c h c u r e x i s t i n t o r e c e x i s t ; close c u r e x i s t ; r e t u r n ( r e c e x i s t . cid i s not n u l l ) ; end ; Note Uses the private method check valid cid to check preconditions Uses the private cursor cur exist Returns true if a valid primary key is found Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 47 / 56Body File: The to string Method Example (Method) f u n c t i o n t o s t r i n g ( cid course . cidtype ) r e t u r n s t r i n g i s v r v s t r i n g ( 5 1 2 ) ; r e c e x i s t c u r e x i s trowtype ; begin c h e c k v a l i d c i d ( cid ) ; precondition open c u r e x i s t ( cid ) ; fet c h c u r e x i s t i n t o r e c e x i s t ; close c u r e x i s t ; v r v := ’ course name : ’ jj r e c e x i s t . cname jj ’ ’ jj ’ course desc : ’ jj r e c e x i s t . dsc ; r e t u r n v r v ; end ; Note Uses the private method check valid cid to check preconditions Uses the private cursor cur exist Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 48 / 56Body File: The print Methods Example (Method) procedure p r i n t ( cid course . cidtype ) i s begin c h e c k v a l i d c i d ( cid ) ; precondition dbms output . p u t l i n e ( t o s t r i n g ( cid ) ) ; end ; Note Uses the private method check valid cid to check preconditions print calls to string Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 49 / 56Note Similar to executing a stored procedure Access member by the dot notation Example SQLexecute ccourse . p r i n t ( n u l l ) ; Note Results in an error “ORA20001: Course ID is null” Exercising the Package Example SQLset serveroutput on execute the procedure SQLexecute ccourse . p r i n t ( 4 ) ; Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 50 / 56Example SQLexecute ccourse . p r i n t ( n u l l ) ; Note Results in an error “ORA20001: Course ID is null” Exercising the Package Example SQLset serveroutput on execute the procedure SQLexecute ccourse . p r i n t ( 4 ) ; Note Similar to executing a stored procedure Access member by the dot notation Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 50 / 56Exercising the Package Example SQLset serveroutput on execute the procedure SQLexecute ccourse . p r i n t ( 4 ) ; Note Similar to executing a stored procedure Access member by the dot notation Example SQLexecute ccourse . p r i n t ( n u l l ) ; Note Results in an error “ORA20001: Course ID is null” Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 50 / 56Summary: Packages Main Points Can have a public and a private part I Has no protected access modifiers as in Java or C Is used to cluster related stored procedures Cursors, constants, and variables can be shared between methods in a package The foundation for building larger libraries in PL/SQL There is a huge library of builtit packages on Oracle Has very good exception handling facilities Comparison to ObjectOriented Languages No inheritance Only static methods No concept of an object Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 51 / 56Outline 1 Introduction 2 Stored Procedures An Overview Data Types Parameter Parsing Cursors 3 Packages Case Study: Table API 4 Summary Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 52 / 56Advantages Advantages A complete programming language I You are not missing stuff as you sometimes are in SQL In widespread usage in the industry I Adds to your market value Very good integration of programming logic and SQL Impedance mismatch is basically removed I PL/SQL data types are super set of SQL data types I Cursors enable the processing of sets (or bags) Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 53 / 56Disadvantages Disadvantages Proprietary programming language There is a very large number (1000) of reserved words I Can be hard to come up with a variable name that is not a reserved word Pascalfamily language (Cfamily more wellknown) I Which lead to a number of surprises Objectoriented features are “clumsy” I This has not been covered in this lecture Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 54 / 56Additional Information Web Sites www.oracle.com/technology/tech/plsql/index.html PL/SQL’s home www.psoug.org/library.html A very good and complete wiki with PL/SQL information plsqltutorial.com/ A crash course covering many PL/SQL features en.wikibooks.org/wiki/OracleProgramming/SQLCheatsheet A short overview of PL/SQL www.java2s.com/Tutorial/Oracle/CatalogOracle.htm Many good examples, too many commercials Kristian Torp (Aalborg University) Introduction to PL/SQL December 2, 2011 55 / 56
Website URL
Comment