1 / 25

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

- Developing stored procedures and functions Using SQL*Plus to create procedures and functions Using procedure builder to create procedures and functions. Using SQL*Plus to create procedures and functions Entering PL/SQL code in SQL*Plus Invoking procedures and functions in SQL*Plus

mitch
Download Presentation

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

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

More Related