Writing PLSQL CodesBelow is a basic structure of a PL/SQL block and some common elements you might use: DECLARE -- Declaration section for variables BEGIN -- Executable section with PL/SQL statements EXCEPTION -- Exception handling section END; /Let's look at each section in more detail : DECLARE Section : This is where you declare variables and other elements that you will use in the PL/SQL block. Syntax : DECLARE variable1 datatype; variable2 datatype; -- other declarationsExample : DECLARE employee_name VARCHAR2(50); employee_salary NUMBER;BEGIN...END Block : This is the main part of the PL/SQL block where you write the procedural code. You can include control structures (IF, LOOP, etc.) and SQL statements here. Syntax : BEGIN -- PL/SQL statements ENDExample : BEGIN Select ename,salary into employee_name, employee_salary from employess where empno=7788; dbms_output.put_line( employee_name||employee_salary); END; /Exception Section : This section is used for handling exceptions (errors) that might occur during the execution of the block. You can include specific exception handlers to deal with different types of errors. Syntax : EXCEPTION WHEN exception1 THEN -- handle exception1 WHEN exception2 THEN -- handle exception2 -- other exception handlersExample : EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);There we include all the sections : DECLARE -- Declaration section employee_name VARCHAR2(50); employee_salary NUMBER; BEGIN -- Executable section Select ename,salary into employee_name,employee_salary from employess where empno=7788; -- Display information DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name); DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || TO_CHAR(employee_salary)); EXCEPTION -- Exception handling section WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘NO DATA TO SHOW’); WHEN others THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; / « Previous Next Topic » (PL/SQL - Control Statements) |