PL/SQL PROCEDUERS WITH VARIOUS COMBINATIONS

-------------------------------------------------------
-- Stored Procedure - No Parameters
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE no_param IS
BEGIN
dbms_output.put_line('No Params');
END no_param;
/

set serveroutput on

exec no_param;


-------------------------------------------------------------
-- Stored Procedure - IN Parameter
-------------------------------------------------------------

CREATE OR REPLACE PROCEDURE in_param (mesg VARCHAR2) IS
BEGIN
dbms_output.put_line(mesg);
END in_param;
/

set serveroutput on

exec in_param('Single IN Parameter');


------------------------------------------------------------------
-- Stored Procedure - OUT Parameter
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE out_param(mesg OUT VARCHAR2) IS
BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50);
BEGIN
out_param(s);
dbms_output.put_line(s);
END;
/

HOST VARIABLE TESTING THE ABOVE PROCEDURE

var x VARCHAR2(30)
exec out_param(:x)
print x

---------------------------------------------------------------------
-- Stored Procedure - IN OUT Parameter
---------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE inout_param (mesg IN OUT VARCHAR2) IS
BEGIN
mesg := mesg || ' an IN OUT Parameter';
END inout_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50) := 'This procedure uses';
BEGIN
inout_param(s);
dbms_output.put_line(s);
END;

---------------------------------------------------------------------
-- Stored Procedure - Multiple Parameters
---------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE many_params (
mesg1 IN VARCHAR2,
mesg2 OUT VARCHAR2,
mesg3 IN OUT VARCHAR2) IS
BEGIN
mesg2 := mesg1 || 'Parameter As The OUT';
mesg3 := mesg3 || 'Returned';
END many_params;
/

set serveroutput on

DECLARE
iparm VARCHAR2(50) := 'This is the IN ';
oparm VARCHAR2(50);
ioparm VARCHAR2(50) := 'And This is the IN OUT ';
BEGIN
many_params(iparm, oparm, ioparm);
dbms_output.put_line(oparm || ' ' || ioparm);
END;
/

create or replace procedure testpr (n1 in number, n2 in number, tot out number, mul out number)
is
begin
tot := n1 + n2;
mul := n1 * n2;
end;

---- TO TEST THE ABOVE PROCEDURE
variable t1 number
variable t2 number
execute testpr(5,6, :t1,t2 )
print t



------------------------------------------------------------------------
-- Stored Procedure - Procedure Without Default
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE no_default(num_rows INTEGER) IS

BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Procedure With Default
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE with_default (
num_rows PLS_INTEGER DEFAULT 20) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < style="font-weight: bold;">------------------------------------------------------------------------
-- Stored Procedure - Positional Notation
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE positional (
min_nr PLS_INTEGER DEFAULT 100,
max_nr PLS_INTEGER DEFAULT 1000) IS
BEGIN
FOR r IN (SELECT table_name FROM user_tables
WHERE num_rows BETWEEN min_nr AND max_nr)
LOOP
dbms_output.put_line(r.table_name);
END LOOP;
END positional;
/

set serveroutput on

exec positional;

exec positional(1);

exec positional(1000, 500000);


------------------------------------------------------------------------
-- Stored Procedure - Named Notation
------------------------------------------------------------------------

exec positional;

exec positional(min_nr => 1);

exec positional(max_nr => 500);

exec positional(max_nr => 5000);

exec positional(max_nr => 10000);

exec positional(max_nr => 50000);

exec positional(max_nr => 999999);


------------------------------------------------------------------------
-- Stored Procedure - Mixed Notation
------------------------------------------------------------------------

exec positional(10, max_nr => 1000);




---------------------------------------------------------------------------------------------------------------------------------------------
-- Dynamic SQL Procedure that Accepts Table Name and WHERE Clause - Execute immediate
---------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE employees_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
delete_rows('employees_temp', 'employee_id = 111');
END;
/


-----------------------------------------------------------------------------------------------
--EXECUTE IMMEDIATE
------------------------------------------------------------------------------------------------
When to Use or Omit the Semicolon with Dynamic SQL

When building up a single SQL statement in a string, do not include any semicolon at the end.

When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:

BEGIN
EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/
----------------------------------------------------------------------------------
-- this procedure can drop any kind of database object
----------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/


-----------------------------------------------------------------
--- Avoiding Deadlocks with Dynamic SQL
-----------------------------------------------------------------
In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to ALTER or DROP a subprogram or package while you are still using it.

CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock!
END;
/