PL/SQL

1. PL/SQL

2. PL/SQL Procedures • Procedures • A subprogram is a program that performs a particular task. • These subprograms are combined to form larger programs. • A subprogram can be invoked by another subprogram or program which is called the calling program.

3. PL/SQL Procedures • A subprogram can be created: • At schema level • Inside a package • Inside a PL/SQL block

4. PL/SQL Procedures • A schema level subprogram is a standalone subprogram. • It is created with the CREATE PROCEDURE or CREATE FUNCTION statement. • It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement. • A subprogram created inside a package is a packaged subprogram. • It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. • PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.

5. PL/SQL Procedures • PL/SQL provides two kinds of subprograms: • Functions: return a single value, mainly used to compute and return a value. • Procedures: do not return a value directly, mainly used to perform an action.

6. PL/SQL Procedures • A procedure is created with the CREATE OR REPLACE PROCEDURE statement. • Syntax • CREATE [OR REPLACE] PROCEDURE procedure_name • [(parameter_name [IN | OUT | IN OUT] type [, ...])] • {IS | AS} • BEGIN • < procedure_body > • END procedure_name;

7. PL/SQL Procedures • [OR REPLACE] option allows modifying an existing procedure. • The parameter list contains name, mode and types of the parameters. • IN represents that value will be passed from outside • OUT represents that this parameter will be used to return a value outside of the procedure. • procedure-body contains the executable part. • The AS keyword is used instead of the IS keyword for creating a standalone procedure.

8. PL/SQL Procedures • CREATE OR REPLACE PROCEDURE check • AS • BEGIN • dbms_output.put_line('haipopo'); • END; • When the code executed it displays • Procedure created

9. PL/SQL Procedures • A standalone procedure can be called in two ways: • Using the EXECUTE keyword • Calling the name of the procedure from a PL/SQL block • Example • EXECUTE checks; • Output haipopo • The procedure can also be called from another PL/SQL block: • example • begin • checks; • end; • Output • haipopo

10. PL/SQL Procedures • A standalone procedure is deleted with the DROP PROCEDURE statement. • Syntax for deleting a procedure is: • DROP PROCEDURE procedure-name; • To delete checks procedure • BEGIN • DROP PROCEDURE checks; • END;

11. PL/SQL Procedures • Parameter Modes in Subprograms

12. PL/SQL Procedures • IN & OUT Modes Example • DECLARE • a int; • b int; • c int; • procedure sum(X IN int, y IN int, z OUT int) IS • BEGIN • Z:=X+Y; • END; • BEGIN • a:= &a; • b:= &b; • sum(a, b, c); • dbms_output.put_line(' sum= ' || c); • END; • /

13. PL/SQL Procedures • IN OUT Modes example • DECLARE • a int; • procedure power(X IN OUT int) IS • BEGIN • X:=X*X; • END; • BEGIN • a:= &a; • power(a); • dbms_output.put_line(' sum= ' || a); • END; • /

14. PL/SQL Procedures • Methods for Passing Parameters • Actual parameters could be passed in three ways: • Positional notation • Named notation • Mixed notation • POSITIONAL NOTATION • In positional notation, call the procedure as: • sum(a, b, c); • In positional notation, the first actual parameter is substituted for the first formal parameter; the second actual parameter is substituted for the second formal parameter, and so on. • So, a is substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.

15. PL/SQL Procedures • Methods for Passing Parameters • NAMED NOTATION • In named notation, the actual parameter is associated with the formal parameter using the arrow symbol ( => ). • So the procedure call would look like: • sum(x=>a, y=>b, z=>c);

16. PL/SQL Procedures • Methods for Passing Parameters • MIXED NOTATION • In mixed notation, can mix both notations in procedure call; however, the positional notation should precede the named notation. • The following call is legal: • sum(a, b, z=>c); • But this is not legal: • sum(x=>a, b, c, d);

17. PL/SQL Function • Same as procedure it returns value • A standalone function is created using the CREATE FUNCTION statement • CREATE [OR REPLACE] FUNCTION function_name • [(parameter_name [IN | OUT | IN OUT] type [, ...])] • RETURN return_datatype • {IS | AS} • BEGIN • < function_body > • END [function_name];

18. PL/SQL Function • CREATE OR REPLACE function check_function • return int as • a int:=10; • b int:=20; • c int; • BEGIN • c:=a+b; • return c; • END; • Function created

19. PL/SQL Function • Calling a Function • declare • x int; • begin • x:=check_function(); • dbms_output.put_line(x); • end; • Outout • 30

20. PL/SQL Function • Count no of record in the tab1 table • CREATE OR REPLACE FUNCTION totalCustomers • RETURN number AS • total number(2) := 0; • BEGIN • SELECT count(*) into total FROM customers; • RETURN total; • END;

21. PL/SQL Function • Calling the functiondeclare • x int; • begin • x:=totalcount(); • dbms_output.put_line('total no of rows '||x); • end; • Output • Total no of rows 2

22. PL/SQL Function • declare • a int; • b int; • c int; • function add(x in int, y in int) return int is • declare • z int; • begin • z:=x+y; • return z; • end; • begin • a:=10; • b:=20; • c:=add(a, b); • dbms_output.put_line('sum '||c); • end; • /