- Developing stored procedures and functions Using SQL*Plus to create procedures and functions Using procedure builder

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

2. 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 Yangjun Chen ACS-3902

3. 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 Yangjun Chen ACS-3902

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

5. - 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 VARCHAR2, 3> p_fur_color IN VARCHAR2 4> ) RETURN VARCHAR2 IS 5> BEGIN Yangjun Chen ACS-3902

6. 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. Yangjun Chen ACS-3902

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

8. - 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; Yangjun Chen ACS-3902

9. - 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; Yangjun Chen ACS-3902

10. 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; Yangjun Chen ACS-3902

11. 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. Yangjun Chen ACS-3902

12. 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 Yangjun Chen ACS-3902

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

14. 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 Yangjun Chen ACS-3902

15. 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. Yangjun Chen ACS-3902

16. 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_01a.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> Yangjun Chen ACS-3902

17. 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. Yangjun Chen ACS-3902

18. - Opening the program unit editor click on Program  Program Unit Editor on the Procedure Builder menu bar. Yangjun Chen ACS-3902

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

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

21. - Placing a PL/SQL into a library Yangjun Chen ACS-3902

22. - 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. Yangjun Chen ACS-3902

23. (window) Yangjun Chen ACS-3902

24. Yangjun Chen ACS-3902

25. 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 AVAILABILITY • ---------- --------------------- • BILLY 20-NOV-2002 • MILLY 21-NOV-2002 • JILLY 05-DEC-2002 • BEEKY 26-DEC-2002 • GRUB 04-DEC-2002 Yangjun Chen ACS-3902