Advanced SQL Stored Procedures Instructor Mohamed Eltabakh meltabakhcs

Advanced SQL: Stored Procedures Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu 1

Today’s Roadmap l Views l Triggers l Assertions l Cursors l Stored Procedures

Stored Procedures & Functions Views Way to register queries inside DBMS Stored Procedures & Functions Way to register code inside DBMS

Stored Procedures & Functions l What is stored procedure? l l l Piece of code stored inside the DBMS SQL allows you to define procedures and functions and store them inside DBMS Advantages l l Reusability: do not need to write the code again and again Programming language-like environment l l Assignment, Loop, For, IF statements Call it whenever needed l From select statement, another procedure, or another function

Stored Procedures in Oracle l Stored procedures in Oracle follow a language called PL/SQL l PL/SQL: Procedural Language SQL l Same language used inside DB triggers cs 3431

Creating A Stored Procedure If exists, then drop it and create it again ‘IS’ or ‘AS’ both are valid CREATE [OR REPLACE] PROCEDURE <procedure. Name> (<param. List>) [IS| AS] <local. Declarations> Begin <procedure. Body>; End; / A parameter in the param. List is specified as: <name> <mode> <type> Mode: IN input parameter (default) OUT output parameter INOUT input and output parameter cs 3431

General Structure CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [, parameter]) ] [IS | AS] [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]; Optional section for exception handling

Example I Define a variable By default, it is IN You can use the procedure name before the parameter name In PL/SQL a ‘; ’ ends a line without execution Execute the command create the procedure

Example II Declaration section Define a cursor that references the input parameter When anything goes wrong, it will come to Exception section

Calling a Stored Procedure l SQL> exec <procedure. Name> [(<param. List>)]; SQL > exec remove_emp (10);

Printing From Stored Procedures Taking three parameters Printing lines to output screen

Features in Stored Procedures IN parameters Create Procedure profiler_control(start_stop IN VARCHAR 2, run_comm IN VARCHAR 2, ret OUT number) AS ret_code INTEGER; BEGIN ret_code : = 10; IF start_stop NOT IN ('START', 'STOP') THEN ret: = 0; ELSIF start_stop = 'START' THEN ret: = 1; ELSE ret: = ret_code; END IF; END profiler_control; / OUT parameters Variable declaration Variable assignment IF statement

More Features: LOOP Statement CREATE PROCEDURE test. Procedure (name varchar 2) AS credit_rating NUMBER : = 0; BEGIN The Loop statement LOOP credit_rating : = credit_rating + 1; IF credit_rating > 3 THEN EXIT; END IF; END LOOP; -- control resumes here IF name > ‘abc’ THEN Return means exit the procedure RETURN; END IF; DBMS_OUTPUT. PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating)); END; /

More Features: CURSOR & FOR Statement Create Procedure Opening. Bal (p_type IN string) AS cursor C 1 Is Select product. Id, name, price From products where type = p_type; Begin For rec in C 1 Loop Insert into Temp values (rec. product. Id, rec. name, rec. price); End Loop; End; /

Return Value l Stored procedures can set output variables l Stored procedures do not return values l Stored functions differ from procedure in that they return values

Stored Functions l Similar to stored procedures except that they return value CREATE [OR REPLACE] FUNCTION <function. Name> RETURN <type> [(<param. List>)] AS <local. Declarations> <function. Body>; The function return a number Select into a variable Return to the called

Stored Functions l All features in stored procedures are valid in in stored functions l Functions have an extra ‘Return’ statement

Using Stored Procedures or Functions l Stored Procedures l l Called from other procedures, functions, triggers, or standalone Stored Functions l In addition to above, can be used inside SELECT statement l In WHERE, HAVING, or projection list

Example I CREATE FUNCTION Max. Num() RETURN number AS num 1 number; BEGIN SELECT MAX (s. Number) INTO num 1 FROM Student; RETURN num 1; END; / SQL> Select * from Student where s. Number = Max. Num(); Calling the function in the Where clause (function will be executed once)

Example II Adding a parameter CREATE FUNCTION Max. Num(last. Name_in varchar 2) RETURN number AS num 1 number; BEGIN SELECT MAX (s. Number) INTO num 1 FROM Student Where last. Name = last. Name_in; RETURN num 1; END; / SQL> Select * from Student S where S. s. Number = Max. Num(S. last. Name); Calling the function in the Where clause (function will execute with each record in Student)

Example III CREATE FUNCTION Max. Num(last. Name_in varchar 2) RETURN number AS num 1 number; BEGIN SELECT MAX (s. Number) INTO num 1 FROM Student Where last. Name = last. Name_in; RETURN num 1; END; / SQL> Select Max. Num(S. last. Name) from Student S; Calling the function in the projection list

Summary of Stored Procedures/Functions l Code modules that are stored inside the DBMS l Used and called repeatedly l Powerful programing language style l Can be called from other procedures, functions, triggers, or from select statement (only functions)

End of Advanced SQL l Views l Triggers l Assertions l Cursors l Stored Procedures/Functions