tailieunhanh - PL/SQL

Modes: IN: procedure must be called with a value for the parameter. Value cannot be changed OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (., call by reference) IN OUT: value can be sent, and changes to the parameter are seen by the user | PL/SQL Objectives Why PL-SQL ? Language features Basic Structure of PL/SQL program Data Types Control Flow in PL-SQL Loops in PL-SQL Why PL SQL ? PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages like procedures, loops, variables, objects etc. Resulting a structural language that is more powerful than SQL PL SQL, Is there any Advantage ? In case of SQL to send 3 queries we will need three network trips between client and server. In PL-SQL we bundle any number of queries in a block and in single network trip task is done. SQL Query1 SQL Query2 SQL Query3 Client Server SQL Query1 SQL Query2 SQL Query3 Client Server PL-SQL Block Language features Supports constructs like any other 4th generation language: Variables and Data types Loops and Control statements Procedures and Functions Packages Triggers Objects Records (Its like structure in C language) PL SQL program structure Declare Begin Exception End; --It ends the program PL SQL nested block > Declare Begin > Declare Begin Exception End; Exception End; PL SQL Block Remember : Declare is optional and only required when variables need to be declared. Exception is optional and required when Error/Exception handling is done. Begin and End are mandatory as all logic and queries are written inside it. Declare Begin Exception End; PL SQL program- Sample I BEGIN Insert into Dept values(70,’HR’,’Pune’); Insert into Dept values(80,’PSD’,’Mumbai’); Insert into Dept values(90,’ESG’,’Pune’); END; --This program will insert three records at the same time in the table dept. PL SQL program- Sample II -- This program displays the sum of two numbers DECLARE v_num1 Number; v_num2 Number; v_sum Number; BEGIN V_num1 := &Number1; V_num2 := &Number2; V_sum := v_num1 + v_num2 ; . | PL/SQL Objectives Why PL-SQL ? Language features Basic Structure of PL/SQL program Data Types Control Flow in PL-SQL Loops in PL-SQL Why PL SQL ? PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages like procedures, loops, variables, objects etc. Resulting a structural language that is more powerful than SQL PL SQL, Is there any Advantage ? In case of SQL to send 3 queries we will need three network trips between client and server. In PL-SQL we bundle any number of queries in a block and in single network trip task is done. SQL Query1 SQL Query2 SQL Query3 Client Server SQL Query1 SQL Query2 SQL Query3 Client Server PL-SQL Block Language features Supports constructs like any other 4th generation language: Variables and Data types Loops and Control statements Procedures and Functions Packages Triggers Objects Records (Its like structure in C language) PL SQL program structure Declare