Expert In Oracle PL/SQL Elysium Spark Note

1. PL/SQL Basics

• PL/SQL Block Structure:
  
  Copy Code Copied Use a different Browser

  
  DECLARE
      -- Declarations
  BEGIN
      -- Executable statements
  EXCEPTION
      -- Exception handling
  END;
  
  

• Anonymous Block Example:
  
  Copy Code Copied Use a different Browser

  
  DECLARE
      v_message VARCHAR2(50);
  BEGIN
      v_message := 'Hello, PL/SQL!';
      DBMS_OUTPUT.PUT_LINE(v_message);
  END;
  
  

2. Data Types

• Common Data Types:
  • VARCHAR2(size): Variable-length string.
  • NUMBER(p,s): Numeric data with precision and scale.
  • DATE: Date and time values.
  • BOOLEAN: True, false, or null.
  • TABLE: PL/SQL collections (arrays).

3. Control Structures

• Conditional Statements:
  
  Copy Code Copied Use a different Browser

  
  IF condition THEN
      -- statements
  ELSIF another_condition THEN
      -- statements
  ELSE
      -- statements
  END IF;
  
  

• Looping:
  • Basic Loop:
    
    Copy Code Copied Use a different Browser

    
    LOOP
        -- statements
        EXIT WHEN condition;
    END LOOP;
    
    

  • For Loop:
  • Copy Code Copied Use a different Browser

    
    Copy code 
    FOR i IN 1..10 LOOP 
    -- statements 
    END LOOP;
    

  • While Loop
  • Copy Code Copied Use a different Browser

    
    sql 
    Copy code 
    WHILE condition LOOP 
    -- statements 
    END LOOP;
    

4. Cursors

• Implicit Cursor:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  SELECT column1 INTO variable FROM table WHERE condition;
  

• Explicit Cursor:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  DECLARE 
  CURSOR cursor_name IS 
  SELECT column1 FROM table WHERE condition;
   v_variable cursor_name%ROWTYPE;
  BEGIN
  OPEN cursor_name; 
  LOOP 
  FETCH cursor_name INTO v_variable; 
  EXIT WHEN cursor_name%NOTFOUND;
   -- Process v_variable 
  END LOOP; 
  CLOSE cursor_name; 
  END;
  

5.Exception Handling

• Basic Exception Handling:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code
  BEGIN 
  -- statements
  EXCEPTION 
  WHEN NO_DATA_FOUND THEN
   -- handle exception WHEN OTHERS THEN
   -- handle other exceptions 
  END;
  
  

• Create Procedure:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  CREATE OR REPLACE PROCEDURE procedure_name (param1 IN data_type, param2 OUT data_type) AS 
  BEGIN 
  -- Procedure logic 
  END procedure_name;
  

• Create Function:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  CREATE OR REPLACE FUNCTION function_name (param1 IN data_type) RETURN data_type AS 
  BEGIN 
  -- Function logic 
  RETURN value; 
  END function_name;
  

  


  Calling Procedures/Functions:

  Copy Code Copied Use a different Browser

  
  sql
  Copy code 
  EXECUTE procedure_name(param1, param2); 
  SELECT function_name(param1) FROM dual;
  

7. Packages

• Create Package Specification:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  CREATE OR REPLACE PACKAGE package_name AS 
  PROCEDURE proc_name; 
  FUNCTION func_name RETURN data_type; 
  END package_name;
  

• Create Package Body:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  CREATE OR REPLACE PACKAGE BODY package_name AS 
  PROCEDURE proc_name AS 
  BEGIN 
  -- Implementation 
  END proc_name;FUNCTION func_name RETURN data_type AS 
  BEGIN 
  -- Implementation 
  RETURN value; 
  END func_name; 
  END package_name
  

   

8. Collections

• Associative Array:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  TYPE array_type IS TABLE OF data_type INDEX BY PLS_INTEGER;
  

  Nested Table:

  Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  TYPE table_type IS TABLE OF data_type;
  

• Using Collections:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  DECLARE 
  TYPE array_type IS TABLE OF VARCHAR2(50); 
  v_array array_type; 
  BEGIN 
  v_array := array_type('Value1', 'Value2'); 
  FOR i IN 1..v_array.COUNT LOOP 
  DBMS_OUTPUT.PUT_LINE(v_array(i)); 
  END LOOP; 
  END;
  

9. Dynamic SQL

• Using EXECUTE IMMEDIATE:
• Copy Code Copied Use a different Browser

  
  sql
   Copy code 
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_name' INTO variable;
  

• Binding Variables:
• Copy Code Copied Use a different Browser

  
  sql 
  Copy code 
  DECLARE v_sql VARCHAR2(100); 
  v_count NUMBER; 
  BEGIN 
  v_sql := 'SELECT COUNT(*) FROM table_name WHERE column_name = :1'; 
  EXECUTE IMMEDIATE v_sql INTO v_count USING value; 
  END;
  

10. Common Built-in Functions

• String Functions:
  • UPPER(), LOWER(), SUBSTR(), LENGTH(), TRIM()
• Date Functions:
  • SYSDATE, ADD_MONTHS(), MONTHS_BETWEEN(), TRUNC()
• Numeric Functions:
  • ROUND(), FLOOR(), CEIL()