Plsql

PL/SQL Concepts

Presentation Made By Parama Hamsa

PL/SQL Architecture  PL/SQL resides in two environments: The Oracle database server Oracle tools  PL/SQL block is an anonymous / Unnamed block

Figure

Advantages of PL/SQL  PL/SQL is a completely portable, highperformance transaction processing language that offers the following advantages: Support for SQL Support for object-oriented programming Better performance Higher productivity Full portability Tight integration with Oracle Tight security

PL/SQL Block  DECLARE birthdate DATE; BEGIN DECLARE birthdate DATE; BEGIN ... IF birthdate = outer.birthdate THEN ... ENDIF END; ... Exception …….  END;

PL/SQL Data Type Conversion  Data Type Conversion occurs in:  Implicit Conversion Number Data type to char / varchar does fall under this category  Explicit Conversion Date type data does need an explicit conversion

PL/SQL Control Structures

 Conditional Control: IF and CASE Statement

 Iterative Control: LOOP and EXIT Statement

 Sequential Control: GOTO and NULL Statem Contd.

PL/SQL Control Structures  IF-THEN-ELSE Statement IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF;

PL/SQL Control Structures  CASE Statement CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade');

LOOP and EXIT Statements  Exit LOOP ... IF credit_rating < 3 THEN ... EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here WHILE-LOOP WHILE condition LOOP sequence_of_statements END LOOP;

Cursors  Cursors are of two types: Implicit Cursor PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. Explicit Cursor For queries that return more than one row, you must declare an explicit cursor

Cursor Processing  Declare A cursor You name and associate it with a Sql select statement  Syntax CURSOR cursor_name [(parameter[, parameter]...)] [RETURN return_type] IS select_statement;

Cursor Processing  Opening a Cursor Opening the cursor executes the query and identifies the result set. For ex: Open c1  Passing cursor parameters use the OPEN statement to pass parameters to a cursor DECLARE emp_name emp.ename%TYPE; salary emp.sal%TYPE; CURSOR c1 (name VARCHAR2, salary NUMBER) IS OPEN c1(emp_name, 3000);

Cursor Processing  Fetching with a Cursor The FETCH statement retrieves the rows in the result set one at a time. Each fetch retrieves the current row and then advances the cursor to the next row in the result set. For ex: FETCH c1 INTO my_empno, my_ename, my_deptno;

Cursor Processing  Close Cursor The CLOSE statement disables the cursor, and the result set becomes undefined. Once a cursor is closed, you can reopen it. Any other operation on a closed cursor raises the predefined exception INVALID_CURSOR.

Cursors  Using Cursor FOR Loops In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

Cursor For Loops DECLARE result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN FOR c1_rec IN c1 LOOP /* calculate and store the results */ result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; COMMIT; END;

Subprograms  Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.  Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exceptionhandling part

Subprograms  Advantages of Subprograms Provides extensibility That is, they let you tailor the PL/SQL language to suit your needs. Provides Modularity That is, they let you break a program down into manageable, well-defined modules. This supports top-down design and the stepwise refinement approach to problem solving. Promotes reusability and maintainability Aids abstraction, the mental process of deriving a universal from particulars

Reference  http://downloadwest.oracle.com/docs/cd/B10501_01/appd ev.920/a96624/