-developing stored procedures and functions using sql*plus to create procedures and functions
DESCRIPTION
-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 - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/1.jpg)
Jan. 2008 Yangjun Chen ACS-3902 1
SQL in Oracle
- Developing stored procedures and functions
•Using SQL*Plus to create procedures and functions
•Using procedure builder to create procedures and functions
![Page 2: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/2.jpg)
Jan. 2008 Yangjun Chen ACS-3902 2
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
![Page 3: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/3.jpg)
Jan. 2008 Yangjun Chen ACS-3902 3
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
![Page 4: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/4.jpg)
Jan. 2008 Yangjun Chen ACS-3902 4
SQL in Oracle
- Using the edit command line from the SQL*Plus prompt
![Page 5: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/5.jpg)
Jan. 2008 Yangjun Chen ACS-3902 5
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 VARCHAR2,
3> p_fur_color IN VARCHAR2
4> ) RETURN VARCHAR2 IS
5> BEGIN
![Page 6: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/6.jpg)
Jan. 2008 Yangjun Chen ACS-3902 6
SQL in Oracle
5> BEGIN6> IF p_fur_color = ‘ORANGE’ AND
p_tail_length = ‘SHORT’ THEN7> RETURN ‘SHORT-TAILED ORANGE ONE’;8> ELSEIF p_fur_color = ‘RED’ AND
p_tail_length = ‘LONG’ THEN9> RETURN ‘SHORT-TAILED FIREY ONE’;10> END IF;11> END;
SQL> /Function created.
ORSQL> @test_mouse_typeFunction created.
![Page 7: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/7.jpg)
Jan. 2008 Yangjun Chen ACS-3902 7
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(val1, val2, ...)
SQL> EXECUTE delete_employee(‘49384’)
SQL procedure successfully completed.
SQL>
ORBEGIN
process_junk(x, y);END;
![Page 8: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/8.jpg)
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;
![Page 9: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/9.jpg)
Jan. 2008 Yangjun Chen ACS-3902 9
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;
![Page 10: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/10.jpg)
Jan. 2008 Yangjun Chen ACS-3902 10
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;
![Page 11: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/11.jpg)
Jan. 2008 Yangjun Chen ACS-3902 11
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.
![Page 12: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/12.jpg)
Jan. 2008 Yangjun Chen ACS-3902 12
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
![Page 13: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/13.jpg)
Jan. 2008 Yangjun Chen ACS-3902 13
SQL in Oracle
DECLARE
my_return_var NUMBER;
BEGIN
SELECT return_hypotenuse(3, 4)
INTO my_return_var
FROM dual;
![Page 14: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/14.jpg)
Jan. 2008 Yangjun Chen ACS-3902 14
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
![Page 15: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/15.jpg)
Jan. 2008 Yangjun Chen ACS-3902 15
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.
![Page 16: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/16.jpg)
Jan. 2008 Yangjun Chen ACS-3902 16
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_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>
![Page 17: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/17.jpg)
Jan. 2008 Yangjun Chen ACS-3902 17
SQL in Oracle
• Using Procedure Builder GUI to develop PL/SQL
- Program unit editorThe 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 editorThe 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.
![Page 18: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/18.jpg)
Jan. 2008 Yangjun Chen ACS-3902 18
SQL in Oracle
- Opening the program unit editorclick on Program Program Unit Editor on the Procedure
Builder menu bar.
![Page 19: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/19.jpg)
Jan. 2008 Yangjun Chen ACS-3902 19
SQL in Oracle
- Giving the name for a PL/SQL code
![Page 20: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/20.jpg)
Jan. 2008 Yangjun Chen ACS-3902 20
SQL in Oracle
- Producing a PL/SQL code and compiling it
![Page 21: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/21.jpg)
Jan. 2008 Yangjun Chen ACS-3902 21
SQL in Oracle
- Placing a PL/SQL into a library
![Page 22: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/22.jpg)
Jan. 2008 Yangjun Chen ACS-3902 22
SQL in Oracle
- Opening the stored program unit editorFirst, 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.
![Page 23: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/23.jpg)
Jan. 2008 Yangjun Chen ACS-3902 23
SQL in Oracle
(window)
![Page 24: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/24.jpg)
Jan. 2008 Yangjun Chen ACS-3902 24
SQL in Oracle
![Page 25: -Developing stored procedures and functions Using SQL*Plus to create procedures and functions](https://reader036.vdocuments.us/reader036/viewer/2022062422/56813b3a550346895da4106f/html5/thumbnails/25.jpg)
Jan. 2008 Yangjun Chen ACS-3902 25
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 AVAILABILITY---------- ---------------------BILLY 20-NOV-2002MILLY 21-NOV-2002JILLY 05-DEC-2002BEEKY 26-DEC-2002GRUB 04-DEC-2002