PLSQL INFSY 445 Fall 2005 PLSQL n Procedure

PL/SQL INFSY 445 Fall 2005

PL/SQL n Procedure Language SQL 4 Include error handling and control structures 4 Stored and used by application programs 4 Tighten security by granting privileges to stored procedures rather than to database

PL/SQL n Function 4 Names PL/SQL block stored on database server 4 May use parameters 4 Returns one value

PL/SQL n Procedure 4 Named PL/SQL block 4 Works with multiple variables 4 Parameters for input, output, & both • IN – value received by calling application can’t be changed during execution • OUT – parameter value will be calculated during procedure execution • INOUT – parameter value passed to procedure will be changed by the procedure 4 Does not return a value 4 Cannot be used in a SQL statement

PL/SQL n Anonymous Block 4 Not stored by database 4 Embedded in an application program, stored in script file, or manually typed by user

PL/SQL n A unit of PL/SQL code is called a block [DECLARE] BEGIN [EXCEPTION] END; identifies variables & constants identifies executable statements identifies error handlers terminates the block

PL/SQL n Declarative Section 4 Variables or constants used in block identified 4 Variable used to reserve temporary storage 4 Constant is a variable who’s value does not change during execution

PL/SQL n Executable Section 4 SQL statements or PL/SQL statements 4 SQL used to access data in tables 4 PL/SQL focus on data within block 4 Mandatory section

PL/SQL n Exception-Handling Section 4 Actions which should occur during errors in execution of block 4 For example, no rows returned from SELECT statement

PL/SQL n END Section 4 Close PL/SQL block 4 Followed by semicolon

PL/SQL DECLARE c_rateincrease CONSTANT NUMBER(3, 2) : =1. 2; v_title VARCHAR 2(30); v_retail books. retail%TYPE; v_newretail NUMBER(5, 2); BEGIN SELECT title, retail*c_rateincrease INTO v_title, v_retail, v_newretail FROM books WHERE isbn = ‘ 1059831198’ DBMS_OUTPUT. PUT_LINE (‘The new price for ‘ || v_title || ‘ is $’ || v_newretail); END;

PL/SQL n SELECT statement in PL/SQL does not display results as in SQL*Plus n PUT_LINE function of DBMS_OUTPUT package is used to display results n May need to turn on buffer to see results 4 SET SERVEROUTPUT ON 4 Command entered at SQL> n SQL> enter / to execute code in block

PL/SQL n Variable Names 4 c_ 4 g_ 4 v_ 4 Up to 30 characters, numbers, or special symbols 4 Name must begin with character 4 Should not be same as column names n Data types 4 books. title%TYPE = assume data type of this column

PL/SQL n Initialize variable 4 Assignment operator is : = 4: =1. 2

PL/SQL n SELECT statement 4 Slight modification of syntax in PL/SQL 4 Cursor • Implicit cursor = automatic, memory used to stored returned row of data • Explicit cursor = created and managed by user 4 INTO clause used to identify variables of the implicit cursor 4 Because only one row can be returned with implicit cursor, WHERE clause is required

PL/SQL n May add, update, and delete data in tables using PL/SQL BEGIN INSERT INTO publisher VALUES (6, ‘A New Publisher’, ‘Jay Wright’, ‘ 800 -555 -3591’); COMMIT; END; /

PL/SQL n Control Structures 4 IF…ELSE 4 LOOP 4 FOR loop 4 WHILE loop