Question? Leave a message!




Introduction to PL/SQL

Introduction to PL/SQL
RyanCanon Profile Pic
RyanCanon,United Arab Emirates,Teacher
Published Date:21-07-2017
Website URL
Comment
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/SQL_injection 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/SQL_injection 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 built-in 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 built-in 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 / 56