SQL in Oracle Developing stored procedures and functions

SQL in Oracle - Developing stored procedures and functions • Using SQL*Plus to create procedures and functions • Using procedure builder to create procedures and functions Jan. 2008 Yangjun Chen ACS-3902 1

SQL in Oracle Using SQL*Plus to create procedures and functions • Entering PL/SQL code in SQL*Plus • Invoking procedures and functions in SQL*Plus • Code compilation in Oracle using SQL*Plus Jan. 2008 Yangjun Chen ACS-3902 2

SQL in Oracle • Entering PL/SQL code in SQL*Plus There are three ways to enter PL/SQL code into Oracle using SQL*Plus: - Entering PL/SQL at the SQL prompt Jan. 2008 Yangjun Chen ACS-3902 3

SQL in Oracle - Using the edit command line from the SQL*Plus prompt Jan. 2008 Yangjun Chen ACS-3902 4

SQL in Oracle - Writing the entire PL/SQL block using a text editor and the file with a. sql extention The code can be loaded using the get command. SQL> get test_mouse_type. sql 1> CREATE FUNCTION test_mouse_type( 2> p_tail_length IN VARCHAR 2, 3> p_fur_color IN VARCHAR 2 4> ) RETURN VARCHAR 2 IS 5> BEGIN Jan. 2008 Yangjun Chen ACS-3902 5

SQL in Oracle 5> BEGIN 6> IF p_fur_color = ‘ORANGE’ AND p_tail_length = ‘SHORT’ THEN 7> RETURN ‘SHORT-TAILED ORANGE ONE’; 8> ELSEIF p_fur_color = ‘RED’ AND p_tail_length = ‘LONG’ THEN 9> RETURN ‘SHORT-TAILED FIREY ONE’; 10> END IF; 11> END; SQL> / Function created. OR SQL> @test_mouse_type Function created. Jan. 2008 Yangjun Chen ACS-3902 6

SQL in Oracle • Invoking PL/SQL code in SQL*Plus - Executing stored PL/SQL code in SQL*Plus is handled with the execute command. The syntax is execute procedurename(val 1, val 2, . . . ) SQL> EXECUTE delete_employee(‘ 49384’) SQL procedure successfully completed. SQL> OR BEGIN process_junk(x, y); END; Jan. 2008 Yangjun Chen ACS-3902 7

SQL in Oracle - Function calling is a little bit different DECLARE my_return_var NUMBER; BEGIN my_return_var : = return_hypotenuse(3, 4); END; OR SELECT return_hypotenuse(3, 4) FROM dual; Jan. 2008 Yangjun Chen ACS-3902 8

SQL in Oracle - Function calling is a little bit different DECLARE my_return_var NUMBER; BEGIN my_return_var : = return_hypotenuse(3, 4); END; OR SELECT return_hypotenuse(3, 4) FROM dual; Jan. 2008 Yangjun Chen ACS-3902 9

SQL in Oracle • Code compilation in Oracle using SQL*Plus - get command loads and compiles a PL/SQL code. - What to do if there are problems. Oracle will return with a message “Warning: Procedure created with compilation error. ” Method 1: Using USER_ERRORS or ALL_ERRORS relations in the Oracle dictionary. SQL> select * from user_errors; Jan. 2008 Yangjun Chen ACS-3902 10

SQL in Oracle Method 2: Using SHOW ERRORS command. SQL> create procedure flibber as 2> begin 3> select * where my_thing = 6; 4> end; 5> / Warning: Procedure created with compilation errors. Jan. 2008 Yangjun Chen ACS-3902 11

SQL in Oracle SQL> show errors Errors for PROCEDURE FIBBER LINE/COL ERROR -----------------------------------------3/10 PLS-00103: Encountered the symbol “where” when expecting one of the following: from into Jan. 2008 Yangjun Chen ACS-3902 12

SQL in Oracle DECLARE my_return_var NUMBER; BEGIN SELECT return_hypotenuse(3, 4) INTO my_return_var FROM dual; Jan. 2008 Yangjun Chen ACS-3902 13

SQL in Oracle Using Procedure Builder to create procedures and functions • Using Procedure Builder command line to develop PL/SQL • Using Procedure Builder GUI to develop PL/SQL client-side PL/SQL server-side PL/SQl • Running PL/SQL codes in Procedure Builder Jan. 2008 Yangjun Chen ACS-3902 14

SQL in Oracle • Using Procedure Builder command line to develop PL/SQL - Similar to SQL*Plus, enter all the code and operations via the command line interface. - Using the help command, one can find all the commands available. - It is used mainly for development of PL/SQL codes. Jan. 2008 Yangjun Chen ACS-3902 15

SQL in Oracle Example: Assume that you have a PL/SQL code stored in a file find_mouse. sql. PL/SQL>. load file find_mouse. sql PL/SQL>. attach library file mouse_lib_01 a. pll PL/SQL> / To run a PL/SQl code, simply enter the name of a function or a procedure. PL/SQl> text_io. put_line(‘Hello, World. ”); Hello, World. PL/SQL> Jan. 2008 Yangjun Chen ACS-3902 16

SQL in Oracle • Using Procedure Builder GUI to develop PL/SQL - Program unit editor The program unit editor is a module that allows the developer to rapidly develop client-side PL/SQL procedure, function, package specifications, and package bodies. - Stored program unit editor The stored program unit editor is a module that allows the developer to code and modify server-side PL/SQL code of the Oracle database. To use it, you must be connected to a database and able to browse through the database stored procedures, functions, and packages. Jan. 2008 Yangjun Chen ACS-3902 17

SQL in Oracle - Opening the program unit editor click on Program Unit Editor on the Procedure Builder menu bar. Jan. 2008 Yangjun Chen ACS-3902 18

SQL in Oracle - Giving the name for a PL/SQL code Jan. 2008 Yangjun Chen ACS-3902 19

SQL in Oracle - Producing a PL/SQL code and compiling it Jan. 2008 Yangjun Chen ACS-3902 20

SQL in Oracle - Placing a PL/SQL into a library Jan. 2008 Yangjun Chen ACS-3902 21

SQL in Oracle - Opening the stored program unit editor First, connect to a database: Click on File Connect or CTRL-J. You provide an Oracle login in and password, along with a database schema name so Procedure Builder knows where to look for its network connection information. Second, click on Program Sored Program Unit Editor on the Procedure Builder menu bar. Now you develop a stored procedure. Jan. 2008 Yangjun Chen ACS-3902 22

SQL in Oracle (window) Jan. 2008 Yangjun Chen ACS-3902 23

SQL in Oracle Jan. 2008 Yangjun Chen ACS-3902 24

SQL in Oracle • Running PL/SQL codes in Procedure Builder Once a program unit is compiled and stored, you can invoked it simply by typing in the name of the procedure at the interactive PL/SQL prompt with the Procedure Builder CUI. The code will appear in the PL/SQL Interpreter window. PL/SQL> LIST_AVAILABLE_MICE(‘ 09 -NOV-2000’) MOUSE -----BILLY MILLY JILLY BEEKY GRUB Jan. 2008 AVAILABILITY ----------20 -NOV-2002 21 -NOV-2002 05 -DEC-2002 26 -DEC-2002 04 -DEC-2002 Yangjun Chen ACS-3902 25