1 / 13

PL/SQL Procedural Language / Structured Query Language

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.

hamlin
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. PL/SQLProcedural Language / Structured Query Language

  2. 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.

  3. What is PL/SQL? See Handout: Chapter 2 Client Side SQL Application PL/SQL Application Server Side SQL Statement Processor PL/SQL Engine

  4. 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

  5. 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;

  6. 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;

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

  8. Control Statements Conditions Checking IF <condition> THEN [ELSIF <condition> THEN] [ELSE <condition> THEN] END IF;

  9. 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;

  10. 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;

  11. 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;

  12. 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 ...

  13. 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;

More Related