1 / 19

PL/SQL

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

neci
Télécharger la présentation

PL/SQL

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. PL/SQL Yaşar Can ORTAÇTEPE

  2. Introduction to PL/SQL What is SQL? Structured Query Language, is a database language designed for managing data inrelational database management systems [1].

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

  4. History of PL/SQL What is ? • 1978: Oracle Version 1. • The most advanced, powerful, and stable relational databases in the world.

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

  6. 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]

  7. Why PL/SQL? Advantages of PL/SQL Tight Integration with SQL Procedural Language Capability

  8. Why PL/SQL? Advantages of PL/SQL Higher Productivity Full Portability

  9. Why PL/SQL? Advantages of PL/SQL TIGHT SECURITY ERROR HANDLING

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

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

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

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

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

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

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

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

  18. 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]

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

More Related