130 likes | 379 Views
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.
E N D
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/SQL? See Handout: Chapter 2 Client Side SQL Application PL/SQL Application Server Side SQL Statement Processor PL/SQL Engine
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 -- 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 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 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 Conditions Checking IF <condition> THEN [ELSIF <condition> THEN] [ELSE <condition> THEN] END IF;
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 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 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 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 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;