www.quontrasolutions.com
info@quontrasolutions.com
Introduction to PL SQL Online Training Classes
info@quontrasolutions.com
WHILE – LOOP - END LOOP DECLARE … v_deptno dept.deptno%TYPE := 50; v_counter integer; …
BEGIN
… v_counter := 1; WHILE v_counter <= 5 LOOP INSERT INTO dept(deptno) VALUES(v_deptno); v_deptno := v_deptno + 10; END LOOP; …
END; /
OUTPUT SET SERVEROUTPUT ON; DECLARE v_sum_sal v_deptno
emp.sal%TYPE; emp.deptno%TYPE := 10;
BEGIN SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE('The sum is ‘ || TO_CHAR(v_sum_sal)); END; /
Anonymous Block DECLARE v_id
INTEGER;
BEGIN v_id := 1234567; DELETE FROM EMP WHERE id = v_id; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No record exists'); /
END;
Nesting Anonymous Blocks
Exceptions Examples are NO_DATA_FOUND OTHERS To display details of oracle standard error message – EXCEPTION – WHEN OTHERS THEN – DBMS_OUTPUT.PUT_LINE(‘Error detail is: ‘|| SQLERRM)
Procedure • Is a block with a name • The DECLARE key word is not used • Parameters can be – IN – OUT – IN OUT
• Is stored (USER_SOURCE)
Creating or Replacing a Procedure CREATE OR REPLACE PROCEDURE pname( BEGIN
EXCEPTION END; /
) IS
Creating or Replacing a Procedure SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE proc_test(p_empno IN VARCHAR2) IS v_job v_sal
EMP.job%TYPE; EMP.sal%TYPE;
BEGIN SELECT job, sal INTO v_job,v_sal FROM emp WHERE empno = p_empno; DBMS_OUTPUT.PUT_LINE('job is '||v_job); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR...'); END; /
SQL> Show errors SQL> execute proc_test(5893);
Invoking a Procedure DECLARE … BEGIN … proc_test(’23’); … END; / Or SQL> exec proc_test(‘1123’)
Another Example SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6 7 8 9 10 11 12* SQL>
create or replace procedure test_proc is v_id INTEGER; v_empno emp.empno%TYPE; BEGIN v_id := 1234567; select empno into v_empno FROM EMP WHERE empno = v_id; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No record exists'); END; /
Procedure created. SQL> exec test_proc No record exists PL/SQL procedure successfully completed. SQL> show errors
(to see errors for procedures, functions)
www.quontrasolutions.com
info@quontrasolutions.com
For More Details Contact us
Quontra Solutions Visit: http://www.quontrasolutions.com/ Email: info@quontrasolutions.com Call Now : (404)-900-9988. (USA)
info@quontrasolutions.com
Contdâ&#x20AC;Ś