1 / 15

PL/SQL

PL/SQL. Agenda: Basic PL/SQL block structure Example procedures and functions Executing PL/SQL from SQL*Plus Exceptions Packages. REQUIRED. More PL/SQL - block structure. -- defines type of block (procedure, function, anonymous) and the way it is called

mio
Download Presentation

PL/SQL

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/SQL Agenda: Basic PL/SQL block structure Example procedures and functions Executing PL/SQL from SQL*Plus Exceptions Packages

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

  3. 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)

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

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

  6. Another example… CREATE OR REPLACE FUNCTION get_company_name (comp_id_in IN company.id%TYPE) RETURN varchar2 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;

  7. 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, %found, %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

  8. Another PL/SQL example… DECLARE current_bldg_code VARCHAR2(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; From Morrison & Morrison, p. 247

  9. 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 varchar2(100) then select get_company_name(1) from dual; • valid commands to execute stored function f1 returns number) from SQL*Plus. • exec :a := f1; • execute :a := f1; • call f1() into :a; • begin :a := f1; end; • select f1 from dual;

  10. 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 p1 from SQL*Plus. • exec p1; • execute p1; • call p1(); • begin p1; end;

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

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

  13. Another PL/SQL example… FUNCTION build_name (name_in IN VARCHAR2, sex_in IN VARCHAR2) RETURN VARCHAR2 IS unknown_sex EXCEPTION; name_out VARCHAR2(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;

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

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

More Related