21-04-2012, 01:16 PM
MAJOR TRAINING ON ORACLE (PL/SQL)
MAJOR TRAINING ON ORACLE (PLSQL).ppt (Size: 185.5 KB / Downloads: 51)
The Oracle Database (commonly referred to as Oracle RDBMS or simply Oracle) is a relational database management system (RDBMS) produced and marketed by Oracle Corporation. As of 2009, Oracle remains a major presence in database computing.
WHAT ABOUT ORACLE?
Versions
Oracle 8i
Oracle 9i
Oracle 10g
Oracle 11g
WHAT IS SQL AND SQL*Plus
With the structured query language (SQL),you tell oracle which information you want to select, insert , or delete.
With SQL*Plus, a powerful Oracle product that can take your instructions for oracle, check them for correctness, submit them to oracle, and modify or reformat the response oracle gives, based on orders or directions you’ve in place.
DEVELOPMENT TOOL
TOAD(Tool for Oracle Application Development )
EDIT PLUS
SQL DEVELOPER
COMMAND LINE
NOTEPAD
Types of PL/SQL blocks
Anonymous Block
Named Block(Subprograms/Stored procedure)
Anonymous Block
DECLARE
BEGIN
EXCEPTION
END;
/
PROCEDURES
PL/SQL procedures behave very much like procedures in other programming language.
A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters.
Procedure syntax
Create or replace procedure <procedure> (<parameters>)
... AS
<local_var_declarations>
BEGIN
<procedure_body>
END;
run;
CURSOR IN PL/SQL
A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.
Syntax:
declare
cursor <cursor_name(parameter)>
begin
loop open <cursor_name>
.
.
.
end loop;
end;
/
FUNCTION IN PL/SQL
A function is similar to a procedure except that a function must return a value.You create a function using the CREATE FUNCTION statement in DECLARE section of PL/SQL Block for Oracle 10g Data Base.
syntax :
create or replace function <function_name> [(input/output variable declarations)] return <return_type>
<is|as>
[declaration block]
begin
<pl/sql block with return statement>
[exception
exception block]
end;
PACKAGES
Packages are groups of conceptually linked Functions, Procedures,Variable,Constants & Cursors etc. The use of packages promotes re-use of code. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary.
PAKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE <package_name>
AS
PROCEDURE <procedure_name> ([(input/output variable declarations)]);
END <package_name>;
/
PAKAGE BODY:
CREATE OR REPLACE PACKAGE BODY <package_name>
AS
PROCEDURE <procedure_name> ([(input/output variable declarations)]);
IS
[declaration block]
BEGIN
<PL/SQL block statement>
END;
END <package_name>;
/