1 / 22

PL/SQL

PL/SQL. 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. PL/SQL Procedures.

egan
Download Presentation

PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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; • /

More Related