Introduction to PL/SQL - Purdue University

Introduction to PL/SQL Introduction to PL/SQL

Procedural Language extension for SQL Oracle Proprietary 3GL Capabilities Integration of SQL Portable within Oracle data bases Callable from any client Structure of PL/SQL PL/SQL is Block Structured A block is the basic unit from which all PL/SQL

programs are built. A block can be named (functions and procedures) or anonymous Sections of block 1- Header Section 2- Declaration Section 3- Executable Section 4- Exception Section Structure of PL/SQL

HEADER Type and Name of block DECLARE Variables; Constants; Cursors; BEGIN PL/SQL and SQL Statements EXCEPTION Exception handlers

END; Structure of PL/SQL DECLARE a number; text1 varchar2(20); text2 varchar2(20) := HI; BEGIN ---------- ---------- ----------

END; Important Data Types in PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE etc to_date(02-05-2007','dd-mm-yyyy') { Converts String to Date} Structure of PL/SQL Data Types for specific columns

Variable_name Table_name.Column_name%type; This syntax defines a variable of the type of the referenced column on the referenced table PL/SQL Control Structure PL/SQL has a number of control structures which includes: Conditional controls Iterative or loop controls. Exception or error controls

It is these controls, used singly or together, that allow the PL/SQL developer to direct the flow of execution through the program. PL/SQL Control Structure Conditional Controls IF....THEN....END IF; IF....THEN...ELSE....END IF; IF....THEN...ELSIF....THEN....ELSE....END IF;

PL/SQL Control Structure LOOP ...SQL Statements... EXIT; END LOOP; WHILE loops WHILE condition LOOP ...SQL Statements... END LOOP; FOR loops

FOR IN [REVERSE] .. LOOP .... ..... END LOOP; PL/SQL Control Structure Cursor DECLARE name varchar2(20); Cursor c1 is

select t.name from table t where date is not null; BEGIN OPEN c1; LOOP FETCH c1 into name; exit when c1%NOTFOUND; END LOOP; CLOSE c1;

END; Debuging show error DBMS_OUTPUT.PUT_LINE( .. ); Execution How to execute a function in PL/SQL? Var issue_flag number; exec :issue_flag:=fun_name(arg1,arg2,. . . .);

PRINT :issue_flag; How to execute a procedure in PL/SQL? Exec procedure_name(arg1,arg2,. . . .); THANK YOU References [1] [2] [3]

http://www.csee.umbc.edu/help/oracle8/server.815/a67842/01_oview.htm#740 http://en.wikipedia.org/wiki/PL/SQL http://www.skillbuilders.com/Tutorials-V2/ora9i_Intro_to_PLSQL/ora9i_Intro_to_PLSQL.cfm

Recently Viewed Presentations

  • True or False - portal.ct.gov

    True or False - portal.ct.gov

    : National Center for Education Statistics Bullying and violence are most prevalent in middle schools Percent of schools reporting bullying incidents daily or at least once a week, and the rate per 1,000 students of all recorded violent incidents that...
  • Consent and Confidentiality for Children in New Mexico

    Consent and Confidentiality for Children in New Mexico

    Emancipation. The term emancipation refers to the state of living independently from one's parents. A minor can ask a court to declare that he or she is an emancipated minor. Minors inquiring about emancipation must already be living and functioning...
  • S1/S2 Solo Talk - St Roch's Secondary School

    S1/S2 Solo Talk - St Roch's Secondary School

    The GRC expect you to give an individual talk of 'considerable length' and which is made up of ideas of 'quality, relevance and distinction.' This means you have a lot of work to do preparing your talk, and getting all...
  • Auditory/Visual effects on short-term memory and calculation ...

    Auditory/Visual effects on short-term memory and calculation ...

    Auditory/Visual effects on short-term memory and calculation skills. Anthony DiBello . Grade 9. Central Catholic High School. ... Mastin, Luke. "Short-Term Memory and Working Memory - Types of Memory - The Human Memory." ... Auditory/Visual effects on short-term memory and...
  • Chapter Seven: Abbasid Decline and the Spread of Islamic ...

    Chapter Seven: Abbasid Decline and the Spread of Islamic ...

    Indian achievements were passed to Arabs Colonies of Arabs settled along India's coasts, adopted local customs Provided staging points for Islamic expansion The Coming of Islam to South Asia 10th Century- a Turkish dynasty gained power in Afghanistan 12th Century-...
  • Our Way: CAS Brant's AOP Journey

    Our Way: CAS Brant's AOP Journey

    Purpose Statement (Mission) of Brant FACS. The Brant Family and Children's Services will work with families and the community to safeguard a permanent, nurturing family for all children at risk of abuse, neglect or abandonment.
  • Threshold Graphs - Seton Hall University

    Threshold Graphs - Seton Hall University

    A cycle is a sequence of nodes and edges visiting none more than once, except for the first/last one Degree: # of edges incident on node Trees 6 A tree is a simple graph having the property that there is...
  • m a r u n t e .

    m a r u n t e .

    Zamfir music Un mister istoric: Dacii Din RomaGaleria Braccio Nuovo, dominata de daci Au auzit istoricii nostri de Leonard Velcescu si de nepretuita sa lucrare?