plsql

name plsql description Oracle PL/SQL procedural language. Covers stored procedures, functions, packages, triggers, cursors, collections, and exception handling. Use for Oracle database server-side programming. USE WHEN: user mentions "plsql", "Oracle procedures", "Oracle packages", "Oracle triggers", "BULK COLLECT", "FORALL", "DBMS_OUTPUT", "Oracle functions" DO NOT USE FOR: basic Oracle SQL - use `oracle` instead, PostgreSQL - use `plpgsql` instead, T-SQL - use `tsql` instead allowed-tools Read, Grep, Glob, Write, Edit Oracle PL/SQL Core Knowledge Full Reference : See advanced.md for pipelined table functions, packages, collections, BULK COLLECT/FORALL, compound triggers, and advanced cursors. Deep Knowledge : Use mcp__documentation__fetch_docs with technology: oracle for comprehensive documentation. Basic Structure DECLARE -- Variable declarations v_count NUMBER : = 0 ; BEGIN -- Executable statements DBMS_OUTPUT.PUT_LINE( 'Hello World' ); EXCEPTION WHEN OTHERS THEN -- Exception handling DBMS_OUTPUT.PUT_LINE( 'Error: ' || SQLERRM); END ; / Procedures Basic Procedure CREATE OR REPLACE PROCEDURE update_salary( p_employee_id IN NUMBER, p_percentage IN NUMBER ) IS v_current_salary NUMBER; BEGIN SELECT salary INTO v_current_salary FROM employees WHERE employee_id = p_employee_id; UPDATE employees SET salary = salary * ( 1 + p_percentage / 100 ) WHERE employee_id = p_employee_id; COMMIT ; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR( -20001 , 'Employee not found: ' || p_employee_id); END update_salary; / -- Execute EXEC update_salary( 100 , 10 ); -- or BEGIN update_salary( 100 , 10 ); END ; / Procedure with OUT Parameters CREATE OR REPLACE PROCEDURE get_employee_info( p_employee_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER, p_dept_name OUT VARCHAR2 ) IS BEGIN SELECT e.first_name || ' ' || e.last_name, e.salary, d.department_name INTO p_name, p_salary, p_dept_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id = p_employee_id; END ; / -- Call with OUT parameters DECLARE v_name VARCHAR2( 100 ); v_salary NUMBER; v_dept VARCHAR2( 100 ); BEGIN get_employee_info( 100 , v_name, v_salary, v_dept); DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary); END ; / Functions Scalar Function CREATE OR REPLACE FUNCTION calculate_bonus( p_salary IN NUMBER, p_years IN NUMBER ) RETURN NUMBER DETERMINISTIC -- Same inputs always return same output IS v_bonus NUMBER; BEGIN IF p_years >= 10 THEN v_bonus : = p_salary * 0.15 ; ELSIF p_years >= 5 THEN v_bonus : = p_salary * 0.10 ; ELSE v_bonus : = p_salary * 0.05 ; END IF; RETURN v_bonus; END ; / -- Usage in SQL SELECT employee_id, salary, calculate_bonus(salary, years_of_service) as bonus FROM employees; Cursors Implicit Cursor BEGIN UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10 ; DBMS_OUTPUT.PUT_LINE( 'Rows updated: ' || SQL % ROWCOUNT); IF SQL % NOTFOUND THEN DBMS_OUTPUT.PUT_LINE( 'No rows found' ); END IF; END ; / Explicit Cursor DECLARE CURSOR emp_cursor IS SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10 ; v_emp emp_cursor % ROWTYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_emp; EXIT WHEN emp_cursor % NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary); END LOOP; CLOSE emp_cursor; END ; / Cursor FOR Loop (Preferred) BEGIN FOR emp_rec IN ( SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10 ) LOOP DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary); END LOOP; END ; / Collections Associative Array (INDEX BY) DECLARE TYPE salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE name_tab IS TABLE OF VARCHAR2( 100 ) INDEX BY VARCHAR2( 20 ); salaries salary_tab; names name_tab; BEGIN salaries( 1 ) : = 50000 ; salaries( 2 ) : = 60000 ; names( 'EMP001' ) : = 'John Doe' ; names( 'EMP002' ) : = 'Jane Smith' ; DBMS_OUTPUT.PUT_LINE(salaries( 1 )); DBMS_OUTPUT.PUT_LINE(names( 'EMP001' )); END ; / Exception Handling Predefined Exceptions Exception Description NO_DATA_FOUND SELECT INTO returned no rows TOO_MANY_ROWS SELECT INTO returned multiple rows ZERO_DIVIDE Division by zero VALUE_ERROR Numeric or value error INVALID_CURSOR Invalid cursor operation DUP_VAL_ON_INDEX Duplicate value on unique index Exception Handling DECLARE v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999 ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE( 'Employee not found' ); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE( 'Multiple employees found' ); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'Error: ' || SQLCODE || ' - ' || SQLERRM); RAISE; END ; / RAISE_APPLICATION_ERROR BEGIN IF some_condition THEN RAISE_APPLICATION_ERROR( -20001 , 'Custom error message' ); END IF; END ; / Triggers Row-Level Trigger CREATE OR REPLACE TRIGGER trg_emp_salary_check BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW BEGIN IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR( -20001 , 'Salary cannot be negative' ); END IF; IF :NEW.salary > 1000000 THEN RAISE_APPLICATION_ERROR( -20002 , 'Salary exceeds maximum' ); END IF; END ; / Best Practices DO Use packages to organize related code Use BULK COLLECT and FORALL for large datasets Use cursor FOR loops (auto open/close) Define exceptions at package level Use %TYPE and %ROWTYPE for type safety Use bind variables to prevent SQL injection DON'T Use implicit commits in triggers Ignore exceptions Use SELECT INTO without handling NO_DATA_FOUND Create excessive triggers (performance impact) When NOT to Use This Skill Basic Oracle SQL - Use oracle skill for queries, data types, partitioning PL/pgSQL (PostgreSQL) - Use plpgsql skill for PostgreSQL procedures T-SQL (SQL Server) - Use tsql skill for SQL Server procedures Basic SQL - Use sql-fundamentals for ANSI SQL basics Anti-Patterns Anti-Pattern Problem Solution Not using BULK COLLECT Row-by-row processing Use BULK COLLECT for large datasets SELECT INTO without exception Runtime errors Handle NO_DATA_FOUND Not using packages Code disorganization Organize related code in packages Excessive triggers Performance issues Minimize trigger logic WHEN OTHERS without RAISE Silent failures Re-raise or log exceptions Implicit cursors for large sets Memory issues Use explicit cursors with LIMIT Quick Troubleshooting Problem Diagnostic Fix NO_DATA_FOUND SELECT INTO with no rows Add exception handler TOO_MANY_ROWS SELECT INTO with multiple rows Add WHERE or use cursor ORA-06502 numeric error Type conversion failure Check data types, use TO_NUMBER Slow procedure DBMS_PROFILER Use BULK operations Package state lost Session reset Use PRAGMA SERIALLY_REUSABLE or re-initialize Reference Documentation Procedures Functions Triggers Packages Cursors