Master Oracle SQL and PL/SQL from database architecture, queries, and joins to stored procedures, packages, and triggers.

Before starting: SQL SELECT, variables and conditions.

Practice project: Extend the examples into a small Oracle SQL and PL/SQL study demo. Explain the setup, one normal case and one failure or boundary case.

Start here: concepts and guided practice

  1. PL/SQL: calculate inside a block

    PL/SQL adds procedural statements to Oracle SQL. DECLARE introduces local variables and BEGIN starts the executable part. DBMS_OUTPUT writes a message buffer that the client must display.

    Oracle Database with SQL*Plus or compatible script runner. SET SERVEROUTPUT ON and slash are client commands, not SQL.

    SET SERVEROUTPUT ON
    DECLARE
      points NUMBER := 7;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(points * 2);
    END;
    /
    Check the output
    14 when server output is enabled.

    Change points to 0 and run the whole block.

  2. PL/SQL: classify a score

    IF can choose between procedural actions. PL/SQL uses THEN and END IF. This block is Oracle-specific, not portable SQL syntax.

    Oracle Database with SQL*Plus or compatible script runner. SET SERVEROUTPUT ON and slash are client commands, not SQL.

    SET SERVEROUTPUT ON
    DECLARE
      score NUMBER := 60;
    BEGIN
      IF score >= 60 THEN
        DBMS_OUTPUT.PUT_LINE('Pass');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Practice');
      END IF;
    END;
    /
    Check the output
    Pass

    Run with 59, 60 and 61.

Open the Oracle learning path on DevStudyHub · Play coding games on this blog

logoblog

No comments:

Post a Comment

Please do not enter any spam link in the comment box.