Anonymous Blocks:
An anonymous block is a PL/SQL block that has no name attached to it. They must be generated and utilized in the same session because they will not be saved as database objects on the server.
If using anonymous blockes were the only way we could organize our statements, it would be hard to use PL/SQL to build robust, large and complex application. Instead, PL/SQL supports the definition of named blocks of code called procedures and Functions etc.
Example of anonymous block:
DECLARE
c_id number:=10;
c_name varchar2(50);
c_addr varchar2(50);
BEGIN
SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);
DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);
END;
/
What are Procedures?
A procedure is a group of PL/SQL statements that you can call by name. It can accepts values as input, process the data and return output if required.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)
AS
[ variable1 DATATYPE;variable2 DATATYPE; ...]
BEGIN
executable_statements
[ EXCEPTION
WHEN
exception_name
THEN
executable_statements ]
END;
/
MODE:
Mode is usually one of the following : IN , OUT, or IN OUT.
IN: the caller supplies the value of the parameter.
OUT: the Procedure sets the value of the parameter and the calling program can read it as response.
IN OUT: It means that the calling program may pass the argument and the stored procedure can modifiy the INOUT parameter and pass the new value back to the calling program.
Creating a Procedure:
CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_addr IN VARCHAR2,
c_date_added IN DATE
)
AS
BEGIN
INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);
COMMIT;
dbms_output.put_line('Data successfully Inserted');
END ADD_CUSTOMER;
/
Output: Procedure ADD_CUSTOMER compiled. >> this means we just saved this code in the server database.
Calling a Procedure:
Way1:
BEGIN
ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE);
END;
Way2: order of parameters are not important.
BEGIN
ADD_CUSTOMER
(
c_id => 1,
c_fname => 'Sri',
c_lname => 'Das',
c_addr => '12 AE salt lake kolkata',
c_date_added => SYSDATE
);
END;
Procedure with OUT mode:
CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_addr IN VARCHAR2,
c_date_added IN DATE,
total_count OUT NUMBER
)
AS
BEGIN
INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);
COMMIT;
dbms_output.put_line('Data successfully Inserted');
SELECT COUNT(1) INTO total_count FROM CUSTOMER;
END ADD_CUSTOMER;
/
DECLARE
tcount NUMBER(10);
BEGIN
ADD_CUSTOMER(1,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE, tcount);
dbms_output.put_line('Total Records: ' || tcount);
END;
Procedure with IN OUT mode:
CREATE OR REPLACE PROCEDURE ADD_CUSTOMER
(
c_id IN OUT NUMBER,
c_fname IN VARCHAR2,
c_lname IN VARCHAR2,
c_addr IN VARCHAR2,
c_date_added IN DATE
)
AS
BEGIN
INSERT INTO CUSTOMER(customer_id, first_name, last_name, address, date_added) VALUES (c_id,c_fname,c_lname,c_address,c_date_added);
COMMIT;
dbms_output.put_line('Data successfully Inserted');
SELECT COUNT(1) INTO c_id FROM CUSTOMER;
END ADD_CUSTOMER;
/
DECLARE
tcount NUMBER(10):=120;
BEGIN
ADD_CUSTOMER(tcount,'Sri', 'Das', '12 AE Salt lake Kolkata', SYSDATE,);
dbms_output.put_line('Total Records: ' || tcount);
END;
What are functions?
- A stored function also called a user function or user defined function is a set of PL/SQL statements you can call by name.
- Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.
CREATE [OR REPLACE] FUNCTION function_name
(parameter1 MODE DATATYPE [ DEFAULT EXPTESSION] , parameter2 MODE DATATYPE [ DEFAULT EXPTESSION], ...)
RETURN DATATYPE
AS
[ variable1 DATATYPE;variable2 DATATYPE; ...]
BEGIN
executable_statements
RETURN expression;
[ EXCEPTION
WHEN
exception_name
THEN
executable_statements ]
END;
/
Example:
CREATE OR REPLACE FUNCTION find_salescount
( p_sales_date IN date
) RETURN NUMBER
AS
no_of_sales number := 0;
BEGIN
SELECT count(*) INTO no_of_sales from sales where sales_date = p_sales_date;
RETURN no_of_sales;
END find_salescount;
Calling a function
Way1:
SELECT find_salescount(to_date('01-jan-2023','dd-mon-yyyy')) from dual
Way2:
DECLARE
salescount number := 0;
BEGIN
salescount := find_salescount(to_date('01-jan-2023','dd-mon-yyyy'));
dbms_output.put_line(salescount);
END;
No comments:
Post a Comment