Download
1 / 19
190 likes | 353 Views
PL/SQL. Yaşar Can ORTAÇTEPE. Introduction to PL/SQL. What is SQL?. Structured Query Language , is a database language designed for managing data in relational database management systems [1]. What is PL/SQL?. Procedural programming language functionality!.
E N D
PL/SQL Yaşar Can ORTAÇTEPE
Introduction to PL/SQL What is SQL? Structured Query Language, is a database language designed for managing data inrelational database management systems [1].
What is PL/SQL? Procedural programming language functionality! • PL/SQL is,Oracle's ProceduralLanguage extension to SQL [2]. • PL/SQL extends SQL by addingprocedures,functions, loops, variables,objects etc.
History of PL/SQL What is ? • 1978: Oracle Version 1. • The most advanced, powerful, and stable relational databases in the world.
History of PL/SQL Oracle’s Early Times... • As the industry matured developersneeded to build complicated formulas, exceptions, and rules . • In 1991, Oracle Version 6.0 released witha key component which is called "procedural option" or PL/SQL [3].
Why PL/SQL? Advantages of PL/SQL Better performance Block Structures SQL:Multiple Network TripsPL/SQL:Single Block,Single Trip [5] PL/SQL consists of blocks of code [3]
Why PL/SQL? Advantages of PL/SQL Tight Integration with SQL Procedural Language Capability
Why PL/SQL? Advantages of PL/SQL Higher Productivity Full Portability
Why PL/SQL? Advantages of PL/SQL TIGHT SECURITY ERROR HANDLING
Structure of PL/SQL Program Structure Declare <Optional and only required when variables need to be declared> <All Variables, cursors, exception etc are declared here> Begin <Mandatory> <All programming logic, queries, program statements are written here> Exception <Optional and required when Error/Exception handling is done> <All Error Handling code is written here> End; <Mandatory> <Ends the program>
Structure of PL/SQL Nested Block Structure <Outer Block> Declare Begin <Inner Block> Declare Begin Exception End; <Inner Block Ends> Exception End; <Outer Block Ends>
Structure of PL/SQL PL/SQL ARCHITECTURE • The PL/SQL enginecompiles and executes PL/SQL blocks and subprograms [6]. • The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine [6]. PL/SQL Engine[6]
Data Types • A scalardata type has no internal components. It holds a single value. • A composite data type has internal components that can be manipulated individually. • A reference data type references a specific database field or record and assume the data type of the associated field or record • A LOB (Large OBject)data type stores large amounts of unstructured data. PL/SQL Engine[6]
Variable Declaration • Variable Name <Data Type> birthday DATE; pi REAL := 3.14159;A • Type : You can also make your own type in program and use in the declare section to declare variable [7]. Type t_name is Varchar2(50); Customer_name t_name;
IF deptno = 10 Then DBMS_OUTPUT.PUT_LINE (‘HR'); ELSIF deptno = 20 Then DBMS_OUTPUT.PUT_LINE (‘IT'); ELSE DBMS_OUTPUT.PUT_LINE (‘Invalid'); END IF; CASE WHEN deptno =10 Then DBMS_OUTPUT.PUT_LINE (‘HR'); WHEN deptno =20 Then DBMS_OUTPUT.PUT_LINE (‘IT'); ELSE DBMS_OUTPUT.PUT_LINE (‘Invalid'); Conditional Statements IF & CASE
Iterative Statements FOR & WHILE • FOR i IN 1 .. 100 • LOOP • DBMS_OUTPUT.PUT_LINE(i); • END LOOP; • i:=1; • WHILE i < 10 • LOOP • DBMS_OUTPUT.PUT_LINE(i); • i:=i+1; • END LOOP;
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN Execution section EXCEPTION Exception section END; Procedures General Syntax How to execute a Stored Procedure? 1) From the SQL prompt. EXECUTE [or EXEC] procedure_name(parameters); 2) Within another procedure – simply use the procedure name. procedure_name (parameters);[4]
CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS Declaration_section BEGIN Execution_section Return return_variable; EXCEPTION exception section Return return_variable; END; Functions General Syntax How to execute a PL/SQL Function? 1) Since a function returns a value we can assign it to a variable. employee_name := employer_details_func(parameters); 2) As a part of a SELECT statement SELECT name, salaryFROM employee where id= employer_details_func(parameters); [4]
Last Word Sources [1] http://en.wikipedia.org/wiki/SQL [2] http://www.orafaq.com/wiki/PL/SQL [3] http://docstore.mik.ua/orelly/oracle/prog2/ [4] http://plsql-tutorial.com/ [5]http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#CJ AGBBAD [6] http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm [7] http://elearning.najah.edu/OldData/pdfs/pl-sql1.ppt Thank You
