PLSQL Agenda Basic PLSQL block structure Example procedures

PL/SQL Agenda: • Basic PL/SQL block structure • Example procedures and functions • Executing PL/SQL from SQL*Plus • Exceptions • Packages

More PL/SQL - block structure Block Header IS Declaration Header -- defines type of block (procedure, function, anonymous) and the way it is called -- declare any variables used in the block BEGIN Execution Section -- use variables and other PL/SQL objects to perform actions EXCEPTION Exception Section -- handle any problems that arise during execution of the block END; REQUIRED

PL/SQL… • Is not case sensitive • Uses the same datatypes as SQL – Also has boolean, record, table, varray and LOB – Allows reference datatypes • %type and %rowtype • Allows comments – /* and */ for multiline and -- for single line • Uses : = for assignment • Uses ; to indicate end of line (instruction)

PL/SQL - example DECLARE loop_count BINARY_INTEGER : = 0; BEGIN LOOP INSERT INTO count_table VALUES (loop_count); DBMS_output. put_line (‘loop_count is ’ || to_char(loop_count)); loop_count : = loop_count + 1; EXIT WHEN loop_count = 6; END LOOP; END;

Scott. GIVE_RAISE CREATE OR REPLACE PROCEDURE give_raise ( p_deptno IN number, p_raise_percent IN number ) AS BEGIN update emp set sal = sal + (sal * p_raise_percent *. 01) where deptno = p_deptno; commit; END give_raise;

Another example… CREATE OR REPLACE FUNCTION get_company_name (comp_id_in IN company. id%TYPE) RETURN varchar 2 IS cname company. name%TYPE; BEGIN SELECT name INTO cname FROM company WHERE id = comp_id_in; RETURN cname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN OTHERS DBMS_OUTPUT. PUT_LINE (‘error code= ‘ || SQLCODE); DBMS_OUTPUT. PUT_LINE (‘error msg= ‘ || SQLERRM); END;

Cursors • Implicit – Created automatically in Oracle – Don’t need to be declared – Can be used only when 1 and only 1 record is returned • Explicit – Declared in declaration section – attributes specifying the state of the cursor • %notfound, %rowcount, %isopen – Use “cursor for loop” to work with FOR item IN cursor LOOP … END LOOP; • Substitute cursor name and row variable for cursor and item • Opens cursor and fetches results into cursor automatically

Another PL/SQL example… DECLARE current_bldg_code VARCHAR 2(5); CURSOR location_cursor IS SELECT room, capacity FROM location WHERE bldg_code = current_bldg_code FOR UPDATE of capacity; location_row location_cursor%rowtype; BEGIN current_bldg_code : = ‘LIB’; FOR location_row IN location_cursor LOOP UPDATE location Set capacity = capacity +1 WHERE CURRENT OF location_cursor; END LOOP; COMMIT; END;

Using PL/SQL - functions • From SQL*Plus command line – Functions require that you create a variable in SQL*Plus to hold the value returned first variable cname varchar 2(100) then select get_company_name(1) from dual; • valid commands to execute stored function f 1 returns number) from SQL*Plus. – – – exec : a : = f 1; execute : a : = f 1; call f 1() into : a; begin : a : = f 1; end; select f 1 from dual;

Using PL/SQL - procedures • From SQL*Plus command line – Procedures don’t return a value, so Execute Give_raise(20, 10); • valid commands to execute stored procedure p 1 from SQL*Plus. – – exec p 1; execute p 1; call p 1(); begin p 1; end;

Debugging PL/SQL • Remember the Oracle error message facility – will help figure out the problem • Comment out lines that may be causing problems • Use dbms_output. put_line to show variable values • Fix from the top down – 1 error may cause multiple error messages • Data dictionary views: user_errors, user_source

Exception types • • NO_DATA_FOUND TOO_MANY_ROWS DUP_VAL_ON_INDEX And more… • Create your own

Another PL/SQL example… FUNCTION build_name (name_in IN VARCHAR 2, sex_in IN VARCHAR 2) RETURN VARCHAR 2 IS unknown_sex EXCEPTION; name_out VARCHAR 2(100); BEGIN IF sex_in =‘M’ THEN name_out : = ‘Mr. ‘ || name_in; ELSIF sex_in =‘F’ THEN name_out : = ‘Ms. ‘ || name_in; ELSE RAISE unknown_sex END IF; RETURN name_out; EXCEPTION WHEN unknown_sex THEN DBMS_OUTPUT. PUT_LINE (‘Unable to determine gender of individual!’); END;

Packages • Structure of Packages – package header vs. package body • Advantages of Packages – invalidated objects – hiding details of code – code maintainability

Oracle provided packages • • DBMS_output DBMS_job DBMS_pipe And more… Set serveroutput on size 4000; Dbms_output. put_line (‘text to be displayed’);