pl sql procedural language structured query language
Download
Skip this Video
Download Presentation
PL/SQL Procedural Language / Structured Query Language

Loading in 2 Seconds...

play fullscreen
1 / 13

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


  • 171 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PL/SQL Procedural Language / Structured Query Language' - hamlin


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
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 THEN

[ELSIF THEN]

[ELSE THEN]

END IF;

control statements1
Control Statements

LOOP, FOR and WHILE Loops

(1)

LOOP

...

EXIT WHEN

END LOOP;

(2)

FOR counter IN lower_bound .. upper_bound 

...

END LOOP;

(3)

WHILE 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;

ad