Slide 1: PROCEDURES
11/23/10 03:13 PM
1
Slide 2: ANANYMOUS BLOCK
NAMED BLOCK
It is compiled each time it is issued. It is not stored in the database. It cannot be called directly from other PL/SQL blocks.
It is compiled once It can be stored in the database. It can called directly from other PL/SQL blocks.
11/23/10 03:13 PM
2
Slide 3: When
a procedure is created, it is first compiled and then stored in the database in compiled form. This compiled code then be run later from another PL/SQL block.
11/23/10 03:13 PM
3
Slide 4: When a procedure is called, control passes to the first executable statement inside the procedure. When the procedure finishes, control resumes at the statement following the procedure call.
Begin sample_proc(50); End;
Create or replace sample_proc(a number) is Begin statement 1; statement 2; statement 3; ------------------statement n; End;
11/23/10 03:13 PM
4
Slide 5: CREATE [OR REPLACE] PROCEDURE procedure_name[(argument[{IN|OUT|IN OUT}] type, -------------argument[{IN|OUT|IN OUT}] type)] {IS|AS} Procedure_body
11/23/10 03:13 PM
5
Slide 6: If
the procedure exists and the OR REPLACE keyword are not present, the CREATE statement will return the oracle error
“ORA-955: Name is already used by an existing object.”
Creating
a procedure is a DDL operation, so an implicit COMMIT is done. IS or AS keyword is used instead of DECLARE in a procedure declaration.
11/23/10 03:13 PM
The
6
Slide 7: CREATE OR REPLACE PROCEDURE procedure_name[parameter_list] AS declaration section Begin executable section Exception exception section End [procedure_name];
11/23/10 03:13 PM
7
Slide 8: Syntax
DROP PROCEDURE procedure_name; If the procedure does not exist, the Drop statement will raise the following error.
“ORA-4043: Object does not exist.”
11/23/10 03:13 PM
8
Slide 9: Actual parameters
Formal parameters Create or replace sample_proc(a number) is Begin statement 1; statement 2; statement 3; ------------------statement n; End;
Begin sample_proc(50); End;
11/23/10 03:13 PM
9
Slide 10: Formal parameters can have 3 modes ----
1. IN 2. OUT 3. IN OUT
IN: The parameters acts like a PL/SQL constant. It is considered read-only and cannot be changed.(read) create or replace procedure sam_proc(a in number, b out number, c in out number) is Begin a:=50; b:=a+c; dbms_output.put_line(b); end; PLS-00363: expression 'A' cannot be used as an assignment target
11/23/10 03:13 PM
10
Slide 11: OUT: Any value the actual parameter has when the procedure is called is ignored. Inside the procedure, the formal parameter acts like an uninitialized PL/SQL variable, and thus has a value of NULL. When the procedure finishes and control returns to the calling environment, the contents of the formal parameter are assigned to the actual parameter. create or replace procedure sam_proc(a in number, b out number, c in out number) is begin dbms_output.put_line('in value '||a); dbms_output.put_line('out value '||b); dbms_output.put_line('in out value '||c); End; Output declare ---------x number(4):=7; in value 7 y number(4):=8; out value z number(4):=9; in out value 9 Begin sam_proc(x,y,z); End; 11/23/10 03:13 PM
11
Slide 12: Combination
of IN and OUT The value of the actual parameter is passed into the procedure when the procedure is invoked. When the procedure finishes and control returns to the calling environment, the contents of the formal parameter are assigned to the actual parameter.
11/23/10 03:13 PM
12
Slide 13: create or replace procedure sam_proc(a in number(4), b out number(4), c in out number(4)) is begin ---If we declare like this then the error follows ------End;
(S2724) Expecting:
)
, := CHARACTER DEFAULT
11/23/10 03:13 PM
13
Slide 14: create or replace procedure sam_proc(a in number, b out number, c in out number) is begin dbms_output.put_line('in value '||a); dbms_output.put_line('out value '||b); dbms_output.put_line('in out value '||c); b:=a+c; dbms_output.put_line('after adding a and c'); dbms_output.put_line('--------------------'); dbms_output.put_line('in value '||a); dbms_output.put_line('out value '||b); dbms_output.put_line('in out value '||c); c:=50; b:=a+c; dbms_output.put_line('after assigning 50 to c and addition of a and c'); dbms_output.put_line('in value '||a); dbms_output.put_line('out value '||b); dbms_output.put_line('in out value '||c); end;
11/23/10 03:13 PM
14
Slide 15: The actual parameter that corresponds to an IN OUT or OUT parameter must be a variable, and cannot be a constant or expression. There must be a location where the returned value can be stored. begin sam_proc(7,8,9); end; ORA-06550: line 2, column 12: PLS-00363: expression '8' cannot be used as an assignment target ORA-06550: line 2, column 14: PLS-00363: expression '9' cannot be used as an assignment target ORA-06550: line 2, column 1: PL/SQL: Statement ignored
11/23/10 03:13 PM
15
Slide 16: declare x number(2):=7; y number(2); z number(1):=9; begin sam_proc(x,y,z); end; ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 6
The ORA-06502 is a runtime error, not a compile error. Thus , the block compiled successfully, and the error was actually raised when the procedure returned
11/23/10 03:13 PM
16
Slide 17: declare x number(4):=7; y number(4):=8; z number(4):=9; begin dbms_output.put_line('before calling'); dbms_output.put_line('--------------'); dbms_output.put_line('x value '||x); dbms_output.put_line('y value '||y); dbms_output.put_line('z value '||z); dbms_output.put_line('In the procedure'); dbms_output.put_line('----------------'); sam_proc(x,y,z); dbms_output.put_line('after calling'); dbms_output.put_line('-------------'); dbms_output.put_line('x value '||x); dbms_output.put_line('y value '||y); dbms_output.put_line('z value '||z); end;
Output --------before calling -------------x value 7 y value 8 z value 9 In the procedure ---------------in value 7 out value in out value 9 after adding a and c -------------------in value 7 out value 16 in out value 9 after assigning 50 to c and addition of a and c in value 7 out value 57 in out value 50 after calling ------------x value 7 y value 57 z value 50
11/23/10 03:13 PM
17
Slide 18: PASSED BY REFERENCE
PASSED BY VALUE
A pointer to the actual parameter is passed to the corresponding formal parameter. It is faster because it avoids the copy. By default IN parameters are examples of passed by reference.
It is copied from the actual parameter into the formal parameter. It is slower because of copy By default OUT, IN OUT parameters are examples of passed by value.
11/23/10 03:13 PM
18
Slide 19:
If there are no parameters for a procedure, there are no parentheses in either the procedure declaration or the procedure call. create or replace procedure noparams as begin dbms_output.put_line('no parameters'); end;
begin noparams; end; Output ---------no parameters
11/23/10 03:13 PM
19
Slide 20: Positional notation: The actual arguments are associated with the formal arguments by position.
CREATE OR REPLACE PROCEDURE callme( p_parameterA VARCHAR2, p_parameterB NUMBER, p_parameterC BOOLEAN, p_parameterD DATE) AS BEGIN NULL; END callme; DECLARE v_variable1 VARCHAR2(10); v_variable2 NUMBER(7,6); v_variable3 BOOLEAN; v_variable4 DATE; BEGIN callme(v_variable1, v_variable2, v_variable3, v_variable4); END;
v_variable1 is associated with p_parameterA, v_variable2 is associated with p_parameterB, and so on. This is known as positional notation. Positional notation is more commonly used.
11/23/10 03:13 PM
20
Slide 21: Named notation: The formal parameter and the actual parameter are both included for each argument. This allows us to rearrange the order of the arguments, if desired.
CREATE OR REPLACE PROCEDURE callme( p_parameterA VARCHAR2, p_parameterB NUMBER, p_parameterC BOOLEAN, p_parameterD DATE) AS BEGIN NULL; END callme;
DECLARE v_variable1 VARCHAR2(10); v_variable2 NUMBER(7,6); v_variable3 BOOLEAN; v_variable4 DATE; BEGIN callme(p_parameterB => v_variable2, p_parameterC => v_variable3, p_parameterD => v_variable4, p_parameterA => v_variable1); END;
11/23/10 03:13 PM
21
Slide 22: POSITIONAL NOTATION
NAMED NOTATION
Relies more on good names for the actual parameters to illustrate what each is used for. Names used for the parameters are independent. If the order of the formal parameters is changed then it can be more difficult to maintain. Requires less coding compare to named notation. Parameters with default values must be at the end of the argument list.
Clearly illustrates the association between the actual and formal parameters. The order used for the parameters is independent. If the names of the formal parameters are changed then it can be more difficult to maintain. Requires more coding compare to positional notation We can use parameters with default values where ever in the argument list.
11/23/10 03:13 PM
22
Slide 23: Similar
to variable declarations, the formal parameters to a procedure can have default values. Syntax:
parameter_name [mode] parameter_type {:=|DEFAULT} initial_value create or replace procedure default_proc(a number,b number := 10,c number default 20) is begin dbms_output.put_line(a); dbms_output.put_line(b); dbms_output.put_line(c); end; output --------begin 7 default_proc(7); 10 end; 20
11/23/10 03:14 PM
23
Slide 24: FUNCTIONS
11/23/10 03:14 PM
24
Slide 25: When
a function is created, it is first compiled and then stored in the database in compiled form. This compiled code then be run later from another PL/SQL block.
11/23/10 03:14 PM
25
Slide 26:
CREATE [OR REPLACE] FUNCTION function_name [(argument[{IN|OUT|IN OUT}] type,
--------------argument[{IN|OUT|IN OUT}] type)] RETURN return_type {IS|AS} function_body
11/23/10 03:14 PM
26
Slide 27: The
RETURN statement is used to return control to the calling environment with a value. There can be more than one RETURN statement in a function, although only one of them will be executed. It is an error for a function to end without executing a RETURN. Syntax: Return expression;
11/23/10 03:14 PM
27
Slide 28: EXAMPLE: create or replace function add_func(a number,b number) return number is c number; begin c:=a+b; dbms_output.put_line('sum of '||a||' and '||b||' is '||c); return c; end; declare x number(4); y number(4); z number(4); begin x:=10; y:=20; z:=add_func(x,y); x:=x+z; y:=y+z; z:=add_func(x,y); end;
OUTPUT sum of 10 and 20 is 30 sum of 40 and 50 is 90
11/23/10 03:14 PM
28
Slide 29: Both
can return more than one value via OUT parameters. Both can have declarative, executable, and exception-handling sections. Both can accept default values. Both can be called using positional or named notation. Thumb Rule: If there is more than one return value, use a procedure. If there is only one return value, a function can be used.
11/23/10 03:14 PM
29
Slide 30: THANK YOU
11/23/10 03:14 PM
30
