1 / 22

PL /SQL

PL /SQL. Scripting in Oracle: everything you wanted to know but were afraid to ask. Haley Miller. What is PL/SQL? What are its Advantages What are its components? What is the difference between procedures and functions?. How is SQL incorporated into PL/SQL

erica
Download Presentation

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 Scripting in Oracle: everything you wanted to know but were afraid to ask. Haley Miller

  2. What is PL/SQL? What are its Advantages What are its components? What is the difference between procedures and functions? How is SQL incorporated into PL/SQL What does the code actually look like? Where did you get all this information? Questions I will answer

  3. What is PL/SQL? • Oracle-specific SQL-based language • conglomeration of SQL and a traditional programming languages • allows loops, declarations, branches to subroutines and assignments • procedural, transactional processing language that extends oracle and allows data manipulation not possible in SQL alone

  4. Advantages(abridged) • Gives programmer control • can create constraints other than those allowed in SQL • Oracle allows SOME control relating to values entered into the tables, but some applications may require other constraints: • relate to another record • that apply only in a certain instance (if fieldA=x, then fieldB >90) • organizes repetitive tasks in to logical units

  5. Programmer control:(continued) • extended error handling • more specific error messages • subroutines in case of error • on this error and if value>x then do SubroutineA • on this error and if value<=x then so SubroutineB • flow control • specify forms and events based on input or actions • triggers • subprograms that the database executes automatically in response to specified events.

  6. Advantages [continued] (abridged) • Easy on the server • The PL/SQL engine can process an entire blocks of code at once; even one with multiple SQL statements in one call. • store compiled code directly in the database. • This enables any number of applications or users to share the same functions and procedures; once a given block of code is loaded into memory, any number of users can use the same copy of it simultaneously(although behavior is as though each user had her own copy)

  7. Structure and Syntax • Three sections: • Declarative • Executable • Exception-handling • this example: • a declarative section and an executable block consisting of 2 sub-blocks

  8. The DECLARE Section • define local variables, constants, types, exceptions, and nested subprograms. PL/SQL has a forward declaration, but you can use it only for subprograms. Therefore, you must define all variables, constants, and types before referencing them. • begins with the keyword DECLARE and ends when the keyword BEGIN signals the arrival of the EXECUTABLE section

  9. The EXECUTABLE Section • actual code that the block executes. This is the only part of the block that must always be present. • SQL statements written here • Begins with the word Begin and ends with the word end • May have several begin/end statements with in sub blocks

  10. The EXECUTABLE Section. • PL/SQL supports the following kinds of flow-control statements: • IF statements • Basic loops • FOR loops • WHILE loops • GOTO statements

  11. SQL within PL/SQL • Placed in the exectution section • very few changes: • Insert, select, update, delete etc are all used here along with variables, conditions

  12. The EXCEPTION Section • For handling runtime errors and warnings.

  13. Procedures Vs. Functions • Two types of blocks: • function is used in an expression and returns a value to that expression • computes a value as part of the expression, contains a return value and MUST return a value • procedure is invoked as a standalone statement and passes values to the calling program only through parameters

  14. Parameters: • Used to pass to and from the calling environment • Three modes: • In - contains a value passed from the calling environment • Out - contains a value returned to the calling environment • In/Out - contains a value passed to and returned to the calling environment: often values is modified buy subprogram

  15. Structure of Procedure and Functions • PROCEDURE procedure_name (param_name datatype, param_name datatype...) IS {local declarations} BEGIN {executable code} EXCEPTION END; • FUNCTION function_name (param_name, param_name datatype...) RETURN datatype IS {local declarations} BEGIN {executable code} EXCEPTION {local exception handlers} END;

  16. A REALLY SIMPLE Example • A simple example • Commit; • this line of code commits the record in a given form to a table. It can be hooked to a button on the form and is called a trigger

  17. Example: Insert • Values from the declare section (in brown) a inserted in to the table via the executable section

  18. Example: Delete with constant • Close_stat is a constant specifed in the declare section and called in the Where clause of the Delete Statement

  19. A word about limitations • PL/SQL elements must exist at run-time: • the is not a DDL (data definition language) • this example is not correct

  20. References • Overview of the Oracle7 Server, SQL, and PL/SQL • http://www.hawkesbury.uws.edu.au:8901 /ows-adoc/ap1serv.htm • The ORACLE PL/SQL CBT in Ileana’s office • installed in KitKat in Mc104 • NetG’s PL/SQL CBT

More Related