plsql_blocks

PL/SQL: 

PL/SQL

Introduction: 

Introduction PL/ SQL naturally, efficient and safely extends SQL. Its basic strength is in providing server side, stored procedures, robust, portable and secured Query language. SQL VS PL/SQL 1. No Procedural Capability 2. Time Consuming Processing 3. No Error Handling Procedures. 4. No Sharing of code 5. Low performance 6. Long code 1. Procedural Capability 2. Reduced Network Traffic 3. Error Handling Procedures 4. Facilitate Sharing of code 5. Improved Transaction Performance 6. Portable Code

PL/SQL Block Types: 

PL/SQL Block Types Anonymous DECLARE BEGIN -statements EXCEPTION END; Procedure PROCEDURE <name> IS BEGIN -statements EXCEPTION END; Function FUNCTION <name> RETURN <datatype> IS BEGIN -statements EXCEPTION END;

Anonymous Block: 

Anonymous Block When someone wishes to remain anonymous, that person goes unnamed. So it is with the anonymous PL/SQL block. Unlike the other two types of PL/SQL blocks (the procedure and the function), the anonymous block has no name associated with it. So we cant able to save this block in database

PL/SQL BLOCK STRUCTURE: 

PL/SQL BLOCK STRUCTURE DECLARE (optional) - variable declarations BEGIN (required) - SQL statements - PL/SQL statements or sub-blocks EXCEPTION (optional) - actions to perform when errors occur END; (required)

DECLARATION SECTION: 

DECLARATION SECTION The declaration section is the first section of the PL/SQL block. It contains definitions of PL/SQL identifiers such as variables, constants, cursors and so on. Example DECLARE v_first_name VARCHAR2(35) ; v_last_name VARCHAR2(35) ; v_counter NUMBER := 0 ;

PL/SQL Variable Types: 

PL/SQL Variable Types Scalar (char, varchar2, number, date, etc) Composite (%rowtype) Reference (pointers) LOB (large objects) Note: Non PL/SQL variables include bind variables, host (“global”) variables, and parameters.

Variable Naming Conventions: 

Variable Naming Conventions Two variables can have the same name if they are in different blocks (bad idea) The variable name should not be the same as any table column names used in the block.

PL/SQL is strongly typed: 

PL/SQL is strongly typed All variables must be declared before their use. The assignment statement : = is not the same as the equality operator = All statements end with a ;

PL/SQL Sample Program: 

PL/SQL Sample Program Set serveroutput on DECLARE v_inv_value number(10,2); v_price number(8,2) := 10.25; v_quantity number(8,0) := 400; BEGIN v_inv_value := v_price * v_quantity; dbms_output.put('The value is: '); dbms_output.put_line(v_inv_value); END; /

PL/SQL Sample Program (with user input): 

PL/SQL Sample Program (with user input) Set serveroutput on Accept p_price Prompt 'Enter the Price: ' DECLARE v_inv_value number(8,2); v_price number(8,2); v_quantity number(8,0) := 400; BEGIN v_price := &p_price ; v_inv_value := v_price * v_quantity; dbms_output.put_line('******'); dbms_output.put_line('price * quantity='); dbms_output.put_line(v_inv_value); END; / Note: PL/SQL not designed for user interface programming

PL/SQL Comments: 

PL/SQL Comments DECLARE v_salary number(9,2) := 40000; BEGIN /* this is a multi-line comment that will be ignored by the pl/sql interpreter */ v_salary := v_salary * 2; -- nice raise END; -- end of program

With Select Query: 

With Select Query DECLARE v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(35); v_last_name VARCHAR2(35); BEGIN SELECT first_name, last_name INTO v_first_name, v_last_name FROM student WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||v_last_name); End;

Conditions For Select Query: 

Conditions For Select Query Always return a single tuple It contains more than one column but variables are needed to bind columns.

Control Structure in PL/SQL: 

Control Structure in PL/SQL Conditional Control (IF..ELSE Iterative Control (LOOPING) Sequential Control (GOTO..)

CONDITIONAL CONTROL: 

CONDITIONAL CONTROL IF <Conditon> THEN <Action> ELSIF <Condition> THEN <Action> ELSE <Action> END IF;

Write a PL/SQL Block that will accept an account number from the user, Check if the user balance is less than the minimum balance, only then deduct Rs. 100/- from the balance. Table-Acct_mstr: 

Write a PL/SQL Block that will accept an account number from the user, Check if the user balance is less than the minimum balance, only then deduct Rs. 100/- from the balance. Table-Acct_mstr DECLARE mCur_Bal number(11,2); mAcct_no varchar2(7); mMin_bal number(7,2) := 5000.00; BEGIN mAcct_no:=&mAcct_no; Select curbal into mCur_Bal from acct_mstr where acct_no=mAcct_no; IF MCur_Bal < mMin_bal THEN update acct_mstr set curbal=curbal-100 where acct_no= macct_no; End if; End;

ITERATIVE CONTROL: 

ITERATIVE CONTROL Simple Loop LOOP <Statements> END LOOP; Create a simple loop such that a message is displayed when a loop exceeds a particular value. DECLARE i number : = 0; BEGIN LOOP i := i+2; EXIT WHEN i>10; END LOOP; Dbms_output.Put_line(‘Loop completed’); END;

WHILE & FOR LOOP: 

WHILE & FOR LOOP WHILE <condition> LOOP <action> END LOOP; FOR variable IN [REVERSE] start ..end LOOP <action> END LOOP;

EXAMPLES: 

EXAMPLES Write a PL/SQL block to print 1 to 10 with the use of while loop Write a PL/SQL block to print even numbers upto N numbers.(N input by user)

Sequential Control: 

Sequential Control Goto <codeblock name>

SELECT INTO: 

SELECT INTO SET SERVEROUTPUT ON DECLARE v_max_gpa number(3,2); v_numstudents number(4); v_lname students.lname%type; v_major students.major%type; BEGIN select max(gpa) into v_max_gpa from students; DBMS_OUTPUT.PUT_LINE ('The highest GPA is '||v_max_gpa); select count(sid) into v_numstudents from students where gpa = v_max_gpa; IF v_numstudents > 1 then DBMS_OUTPUT.PUT_LINE ('There are '||v_numstudents||' with that GPA'); ELSE select lname, major into v_lname, v_major from students where gpa=v_max_gpa; DBMS_OUTPUT.PUT_LINE ('The student name is '||v_lname); DBMS_OUTPUT.PUT_LINE ('The student major is '||v_major); END IF; END; /

COMMON PL/SQL STRING FUNCTIONS: 

COMMON PL/SQL STRING FUNCTIONS CHR(asciivalue) ASCII(string) LOWER(string) SUBSTR(string,start,substrlength) LTRIM(string) RTRIM(string) LPAD(string_to_be_padded, spaces_to_pad, |string_to_pad_with|) RPAD(string_to_be_padded, spaces_to_pad, |string_to_pad_with|) REPLACE(string, searchstring, replacestring) UPPER(string) INITCAP(string) LENGTH(string)

COMMON PL/SQL NUMERIC FUNCTIONS: 

COMMON PL/SQL NUMERIC FUNCTIONS ABS(value) ROUND(value, precision) MOD(value,divisor) SQRT(value) TRUNC(value,|precision|) LEAST(exp1, exp2…) GREATEST(exp1, exp2…)

%ROWTYPE – Logical array: 

%ROWTYPE – Logical array Set serveroutput on DECLARE v_student students%rowtype; BEGIN select * into v_student from students where sid='123456'; DBMS_OUTPUT.PUT_LINE (v_student.lname); DBMS_OUTPUT.PUT_LINE (v_student.major); DBMS_OUTPUT.PUT_LINE (v_student.gpa); END; /