Pl sql procedural language structured query language
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

PL/SQL Procedural Language / Structured Query Language PowerPoint PPT Presentation


  • 103 Views
  • Uploaded on
  • Presentation posted in: General

PL/SQL Procedural Language / Structured Query Language. What is PL/SQL?. PL/SQL is Oracle's extension to SQL with design features of programming languages. The data manipulation and query statements are included in the procedural units of codes.

Download Presentation

PL/SQL Procedural Language / Structured Query Language

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Pl sql procedural language structured query language

PL/SQLProcedural Language / Structured Query Language


What is pl sql

What is PL/SQL?

  • PL/SQL is Oracle's extension to SQL with design features of programming languages.

  • The data manipulation and query statements are included in the procedural units of codes.

  • PL/SQL allows the applications to be written in a PL/SQL procedure or a package and stored within an Oracle server, where these PL/SQL codes can be used as shared libraries or applications, thus enhancing the integration and code reuse.

  • Oracle pre-compiles PL/SQL codes prior to the actual code execution thus improving the performance.


What is pl sql1

What is PL/SQL?

See Handout: Chapter 2

Client Side

SQL Application

PL/SQL Application

Server Side

SQL Statement Processor

PL/SQL Engine


Pl sql code structure

PL/SQL Code Structure?

  • DECLARE

  • -- optional, which declares and define variables, cursors and user-defined exceptions.

  • BEGIN-- mandatory

  • - SQL statements

  • - PL/SQL statements

  • EXCEPTION-- optional, which specifies what actions to take when error occurs.

  • END; -- mandatory


Pl sql example 1

PL/SQL Example 1

-- Declare an Integer variable i.e. v1

-- Assign a value of 3

-- Print it on screen

SET SERVEROUTPUT ON

DECLAREv1  NUMBER(3);BEGINv1 := 3;DBMS_OUTPUT.PUT_LINE('v1=' || v1); 

END;


Pl sql example 2

PL/SQL Example 2

DECLARETYPE v_arr IS VARRAY(25) of NUMBER(3);v1 v_arr;

v_empno employee.empno%TYPE;BEGIN

v1(2) := 3;    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2)); 

END;


Dml statements within pl sql

DML Statements within PL/SQL

DECLARE    v_sal employee.sal%TYPE;

BEGIN

    INSERT INTO employee VALUES (6, ‘John Smith', 10000);

    UPDATE employee SET sal = sal + 5000 WHERE empno = 6;

SELECT sal INTO v_sal FROM employee WHERE empno = 6;

    DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal); 

COMMIT;

END;


Control statements

Control Statements

Conditions Checking

IF <condition> THEN

[ELSIF <condition> THEN]

[ELSE <condition> THEN]

END IF;


Control statements1

Control Statements

LOOP, FOR and WHILE Loops

(1)

LOOP

...

EXIT WHEN <condition>

END LOOP;

(2)

FOR counter IN lower_bound .. upper_bound 

...

END LOOP;

(3)

WHILE <condition> LOOP

...

END LOOP;


Cursor manipulation

Cursor Manipulation

See Handout: Chapter 8

DECLARE

CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%';

v_a employee.empno%TYPE;

v_b employee.name%TYPE;

v_c employee.sal%TYPE;

BEGIN     OPEN csr_ac (‘Jo');     LOOP           FETCH csr_ac INTO v_a, v_b, v_c;         EXIT WHEN csr_ac%NOTFOUND;                       

         DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);     END LOOP;    CLOSE csr_ac;END;


Cursor manipulation1

Cursor Manipulation

See Handout: Chapter 8

DECLARE

CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%';

v_a employee.empno%TYPE;

v_b employee.name%TYPE;

v_c employee.sal%TYPE;

BEGIN     OPEN csr_ac (‘Jo');     LOOP           FETCH csr_ac INTO v_a, v_b, v_c;         EXIT WHEN csr_ac%NOTFOUND;                       

         DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);     END LOOP;    CLOSE csr_ac;END;


Pl sql procedures functions

PL/SQL Procedures/Functions

  • PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output.

  • The special feature about PL/SQL is that a procedure/function argument can be of input (indicating the argument is read-only), output (indicating the argument is write-only) or both (both readable and writable).

  • A function is called as part of an expression.

  • For example, the function sal_ok might be called as follows:

  • IF sal_ok(new_sal, new_title) THEN ...


Procedures functions examples

Procedures/Functions Examples

PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) ISBEGIN INSERT INTO employee VALUES (emp_id, name, 1000);END hire_employee;

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS min_sal REAL; max_sal REAL;

BEGIN

SELECT losal, hisal INTO min_sal, max_sal

FROM sals

WHERE job = title;

RETURN (salary >= min_sal) AND (salary <= max_sal);

END sal_ok;


  • Login