lect(5) PLSQL(4).pptx

Database Management Systems Procedural Language SQL Part(4) Example Write PL/SQL program to print hello on the screen. Create Or Replace Procedure PrintH Is Begin Dbms_output.Put_line(’hello World’); End;  Parameters The parameters (or arguments) are optiona...

Database Management Systems Procedural Language SQL Part(4) Example Write PL/SQL program to print hello on the screen. Create Or Replace Procedure PrintH Is Begin Dbms_output.Put_line(’hello World’); End;  Parameters The parameters (or arguments) are optional Parameters are defined similarly to how variables are declared. e.g. (X INT)  Example Write PL/SQL procedure to display the value of its integer parameter CREATE OR REPLACE PROCEDURE DISPN (N INT) IS BEGIN DBMS_OUTPUT.PUT_LINE(’N is ’ || N); END; IN, OUT, IN OUT There are various different parameter varieties: – giving the procedure data via parameters. This is the default (IN). – get data from the procedure, via an OUT parameter To do that simply specify OUT in between the parameter name and its type. Example Create Or Replace Write PL/SQL Procedure Sum_ab (A Int, B Int, C Out Int) Is procedure that return Begin C := A + B; the sum of two End; ___________________________ integers. Then call this procedure in Declare X INT:=&X; PL/SQL program. Y INT:=&Y; Z INT; BEGIN Sum_ab(X,Y,Z); DBMS_Output.Put_Line(“the sum of “||X||”and”||Y||”is”|| Z); End; IN OUT Parameter read the parameter, CREATE OR REPLACE then change it, with IN PROCEDURE DOUBLEN (N IN OUT OUT. INT) IS Example: write a BEGIN procedure that doubles N := N * 2; a number. END; Dropping Procedures The general format of a DROP is: DROP PROCEDURE procedure_name; e.g. DROP PROCEDURE doubleN; Functions Functions are special types of procedures that have the capability to return a value. The general format of a function is: CREATE OR REPLACE FUNCTION function_name (function_params) RETURN return_type IS BEGIN function_body RETURN something_of_return_type; END; Example write a function that Create Or Replace computes the sum of FUNCTION Add_two two numbers (A Int,B Int) RETURN INT Is Begin RETURN (A + B); End; Functions con’d To run it, we’ll write a small piece of code that calls this: BEGIN DBMS_OUTPUT.PUT_LINE(’RESULT IS: ’ || ADD_TWO(12,34)); END; Which produces the output: RESULT IS: 46 Dropping Functions To drop a function: DROP FUNCTION function_name;