"use strict"; const {Fragment: _Fragment, jsx: _jsx, jsxs: _jsxs} = arguments[0]; const {useMDXComponents: _provideComponents} = arguments[0]; function _createMdxContent(props) { const _components = { code: "code", h3: "h3", li: "li", ol: "ol", p: "p", pre: "pre", strong: "strong", ul: "ul", ..._provideComponents(), ...props.components }, {Definition, English, Explanation, Hinglish, ImportantQA, MCQ, SubTopicHeader, Summary} = _components; if (!Definition) _missingMdxReference("Definition", true); if (!English) _missingMdxReference("English", true); if (!Explanation) _missingMdxReference("Explanation", true); if (!Hinglish) _missingMdxReference("Hinglish", true); if (!ImportantQA) _missingMdxReference("ImportantQA", true); if (!MCQ) _missingMdxReference("MCQ", true); if (!SubTopicHeader) _missingMdxReference("SubTopicHeader", true); if (!Summary) _missingMdxReference("Summary", true); return _jsxs(_Fragment, { children: [_jsx(SubTopicHeader, { id: "4.1", title: "PL/SQL Blocks & Control Structures" }), "\n", _jsxs(Definition, { term: "Procedural Language / SQL (PL/SQL)", children: [_jsx(English, { children: _jsx(_components.p, { children: "PL/SQL is Oracle Corporation's procedural extension to SQL, combining the data manipulation power of SQL with the control flow structures of procedural programming." }) }), _jsx(Hinglish, { children: _jsx(_components.p, { children: "PL/SQL (Procedural Language/SQL) Oracle ka SQL extension hai jo SQL ke data manipulation features ke saath loops, conditions aur exception handling jaise procedural features ko combine karta hai." }) })] }), "\n", _jsxs(Explanation, { children: [_jsxs(English, { children: [_jsx(_components.h3, { children: "PL/SQL Block Structure" }), _jsx(_components.p, { children: "PL/SQL is a block-structured language. Every PL/SQL program is written in blocks. A block consists of three main sections:" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Declaration Section (Optional):" }), " Used to declare variables, constants, cursors, and user-defined exceptions. Starts with ", _jsx(_components.code, { children: "DECLARE" }), "."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Execution Section (Mandatory):" }), " Contains SQL statements and PL/SQL control blocks. Starts with ", _jsx(_components.code, { children: "BEGIN" }), " and ends with ", _jsx(_components.code, { children: "END;" }), "."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Exception Section (Optional):" }), " Used to handle runtime errors or exceptions. Starts with ", _jsx(_components.code, { children: "EXCEPTION" }), "."] }), "\n"] }), _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DECLARE\n v_emp_name VARCHAR2(50);\n v_salary NUMBER(10,2);\nBEGIN\n SELECT Name, Salary INTO v_emp_name, v_salary\n FROM Employee WHERE EmpId = 101;\n \n DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name || ', Salary: ' || v_salary);\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n DBMS_OUTPUT.PUT_LINE('No employee found with ID 101');\nEND;\n/\n" }) })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "PL/SQL Block Structure" }), _jsx(_components.p, { children: "PL/SQL block-structured language hai. Iske programs teen sections mein likhe jate hain:" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Declaration Section (Optional):" }), " Variables, constants, aur cursors declare karne ke liye. Starts with ", _jsx(_components.code, { children: "DECLARE" }), "."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Execution Section (Mandatory):" }), " actual SQL queries aur control logic likhne ke liye. Starts with ", _jsx(_components.code, { children: "BEGIN" }), " and ends with ", _jsx(_components.code, { children: "END;" }), "."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Exception Section (Optional):" }), " runtime errors ya exceptions handle karne ke liye. Starts with ", _jsx(_components.code, { children: "EXCEPTION" }), "."] }), "\n"] }), _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DECLARE\n v_emp_name VARCHAR2(50);\n v_salary NUMBER(10,2);\nBEGIN\n SELECT Name, Salary INTO v_emp_name, v_salary\n FROM Employee WHERE EmpId = 101;\n \n DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name || ', Salary: ' || v_salary);\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n DBMS_OUTPUT.PUT_LINE('No employee found with ID 101');\nEND;\n/\n" }) })] }), _jsxs(English, { children: [_jsx(_components.h3, { children: "Control Structures" }), _jsx(_components.p, { children: "PL/SQL provides control structures to change the flow of execution:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Conditional Selection:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "IF-THEN-ELSIF-ELSE:" }), " Evaluates conditions sequentially.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "IF v_salary > 50000 THEN\n v_grade := 'A';\nELSIF v_salary > 30000 THEN\n v_grade := 'B';\nELSE\n v_grade := 'C';\nEND IF;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "CASE Statement:" }), " Evaluates a single selector expression or search conditions."] }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Loop structures:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Simple LOOP:" }), " Runs infinitely until an explicit exit condition is met.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "LOOP\n v_counter := v_counter + 1;\n EXIT WHEN v_counter > 10;\nEND LOOP;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "WHILE Loop:" }), " Runs as long as a condition evaluates to TRUE.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "WHILE v_counter <= 10 LOOP\n v_counter := v_counter + 1;\nEND LOOP;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "FOR Loop:" }), " Runs a fixed number of times over a range.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "FOR i IN 1..10 LOOP\n DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);\nEND LOOP;\n" }) }), "\n"] }), "\n"] }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Control Structures" }), _jsx(_components.p, { children: "PL/SQL execution flow ko control karne ke liye ye structures deta hai:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Conditions:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "IF-THEN-ELSIF-ELSE:" }), " Conditions ko check karne ke liye.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "IF v_salary > 50000 THEN\n v_grade := 'A';\nELSIF v_salary > 30000 THEN\n v_grade := 'B';\nELSE\n v_grade := 'C';\nEND IF;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "CASE Statement:" }), " Selector values check karne ke liye."] }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Loops:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Simple LOOP:" }), " Tab tak chalta hai jab tak condition ke basis par EXIT statement execute na ho.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "LOOP\n v_counter := v_counter + 1;\n EXIT WHEN v_counter > 10;\nEND LOOP;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "WHILE Loop:" }), " Jab tak condition TRUE hai loop chalta rahega.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "WHILE v_counter <= 10 LOOP\n v_counter := v_counter + 1;\nEND LOOP;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "FOR Loop:" }), " Ek fixed numeric range (e.g., 1 to 10) tak loop run karne ke liye.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "FOR i IN 1..10 LOOP\n DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);\nEND LOOP;\n" }) }), "\n"] }), "\n"] }), "\n"] }), "\n"] })] })] }), "\n", _jsxs(Summary, { children: [_jsx(English, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "PL/SQL combines SQL data access with procedural control structures." }), "\n", _jsx(_components.li, { children: "A standard block contains DECLARE (optional variables), BEGIN (execution), and EXCEPTION (handling)." }), "\n", _jsx(_components.li, { children: "Flow is managed using IF-THEN-ELSE/CASE conditionals and Simple, WHILE, and FOR loops." }), "\n"] }) }), _jsx(Hinglish, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "PL/SQL procedural programming controls ke saath SQL queries ko support karta hai." }), "\n", _jsx(_components.li, { children: "Block structure mein DECLARE (vars), BEGIN (exec), aur EXCEPTION (error handle) hote hain." }), "\n", _jsx(_components.li, { children: "Execution flows ko manage karne ke liye IF-ELSIF conditionals aur loops (Simple, WHILE, FOR) use hote hain." }), "\n"] }) })] }), "\n", _jsx(ImportantQA, { questions: [{ "q": "Describe the basic structure of a PL/SQL block with an explanation of each section.", "marks": 4, "a": { "components": [{ "type": "Explanation", "content": "A PL/SQL block is divided into three sections:\n1. **Declaration Section:** Starts with `DECLARE`. Used to define variables, variables data types, cursors, and user exceptions. This section is optional.\n2. **Execution Section:** Starts with `BEGIN` and ends with `END;`. Contains SQL statements and procedural structures to manipulate data. This section is mandatory.\n3. **Exception Handling Section:** Starts with `EXCEPTION`. Used to catch and handle runtime errors generated during execution. This section is optional.PL/SQL block teen sections mein divided hota hai:\n1. **Declaration Section:** `DECLARE` keyword se start hota hai. Ismein saare variables, data-types aur cursors define hote hain. (Optional section).\n2. **Execution Section:** `BEGIN` se start aur `END;` se khatam hota hai. Ismein active execution statements aur logic likha jata hai. (Mandatory section).\n3. **Exception Section:** `EXCEPTION` keyword se start hota hai. Execution ke dauran aane wale runtime errors ko trace aur solve karne ke liye iska use hota hai. (Optional section)." }] } }, { "q": "Write a PL/SQL block to print numbers from 1 to 10 using a WHILE loop.", "marks": 4, "a": { "components": [{ "type": "Explanation", "content": "```sql\nDECLARE\n v_num NUMBER := 1;\nBEGIN\n WHILE v_num <= 10 LOOP\n DBMS_OUTPUT.PUT_LINE('Number: ' || v_num);\n v_num := v_num + 1;\n END LOOP;\nEND;\n/\n``````sql\nDECLARE\n v_num NUMBER := 1;\nBEGIN\n WHILE v_num <= 10 LOOP\n DBMS_OUTPUT.PUT_LINE('Number: ' || v_num);\n v_num := v_num + 1;\n END LOOP;\nEND;\n/\n```" }] } }] }), "\n", _jsx(MCQ, { questions: [{ "id": "e5dbcd37-5973-472f-882c-0c4429f62551", "q": "Which section of a PL/SQL block is mandatory?", "options": ["Declaration Section", "Execution Section", "Exception Section", "None of the above"], "answer": 1, "hint": "It starts with the keyword 'BEGIN'.", "explanation": "The execution section (BEGIN...END;) is the only mandatory section in a PL/SQL block." }, { "id": "599bf234-0e99-4bb2-a140-41fd97d4725d", "q": "Which keyword is used to start the declaration section in a PL/SQL block?", "options": ["VARIABLES", "DECLARE", "BEGIN", "START"], "answer": 1, "hint": "It literally means to declare something.", "explanation": "DECLARE is the keyword used to initiate the optional declaration section." }, { "id": "dccd8005-9465-4293-8f06-9a2dbc7014a3", "q": "What is the correct assignment operator in PL/SQL?", "options": ["=", "==", ":=", "<-"], "answer": 2, "hint": "It is a colon followed by an equals sign.", "explanation": "PL/SQL uses := as the assignment operator to assign values to variables." }, { "id": "1d669035-9433-4342-b6b0-c8bf10244abd", "q": "Which loop in PL/SQL always executes at least once and requires an explicit EXIT condition?", "options": ["WHILE Loop", "FOR Loop", "Simple LOOP", "Cursor FOR Loop"], "answer": 2, "hint": "It has no conditional check at the header level.", "explanation": "A Simple LOOP starts executing immediately without pre-checking conditions, requiring an EXIT WHEN statement inside." }, { "id": "59448319-17a6-4bd0-bfd5-7f5b6ef2abb7", "q": "What PL/SQL utility package is used to print outputs to the console window?", "options": ["SYSTEM.OUT", "DBMS_OUTPUT", "PRINT_LINE", "CONSOLE_LOG"], "answer": 1, "hint": "It starts with DBMS.", "explanation": "DBMS_OUTPUT.PUT_LINE is the standard Oracle PL/SQL subprogram used to display output text." }, { "id": "67b0e647-867b-487f-bcb1-be062af63973", "q": "Which clause is used in a SELECT statement in PL/SQL to assign column values directly to declared variables?", "options": ["TO", "INTO", "ASSIGN", "VALUE"], "answer": 1, "hint": "Think of transferring data 'into' a variable container.", "explanation": "In PL/SQL, a SELECT statement must contain the INTO clause to map retrieved columns to local variables." }, { "id": "ecb43d24-f7a3-419c-8a69-ece33afea60f", "q": "What happens when a SELECT INTO statement returns more than one row in PL/SQL?", "options": ["It returns the first row and ignores others", "It throws a TOO_MANY_ROWS runtime exception", "It automatically converts the variable into an array", "It returns a NULL result"], "answer": 1, "hint": "SELECT INTO expects exactly one row. More than one triggers an exception.", "explanation": "A SELECT INTO query expects exactly one row. If it returns multiple rows, Oracle throws the TOO_MANY_ROWS exception." }, { "id": "45a2824a-ed88-464f-b6c0-479b82d7d7ef", "q": "What is the variable data type attribute used to declare a variable with the exact same data type as a table column?", "options": ["%TYPE", "%ROWTYPE", "SAME_TYPE", "LIKE"], "answer": 0, "hint": "It checks the TYPE of a column.", "explanation": "Using variable_name table_name.column_name%TYPE declares a variable with the dynamic matching type of the database column." }, { "id": "66900879-3a2d-4b9f-a5c0-a89fed4b6198", "q": "In a FOR loop, if we want to run the loop in reverse order, which keyword should be used?", "options": ["REVERSE", "BACKWARD", "DOWNTO", "DESC"], "answer": 0, "hint": "It is a standard English word meaning opposite direction.", "explanation": "Using the REVERSE keyword (e.g. FOR i IN REVERSE 1..10) executes the loop index from high to low." }, { "id": "7b16f2b3-89ca-4788-9f84-c175a590fc9d", "q": "Which attribute is used to declare a record variable that represents an entire row of a table?", "options": ["%TYPE", "%ROWTYPE", "%RECORD", "%ROW"], "answer": 1, "hint": "It refers to the TYPE of a whole ROW.", "explanation": "The %ROWTYPE attribute allows declaring a record variable that holds a complete row structure of the referenced table." }], topicId: "4.1" })] }); } function MDXContent(props = {}) { const {wrapper: MDXLayout} = { ..._provideComponents(), ...props.components }; return MDXLayout ? _jsx(MDXLayout, { ...props, children: _jsx(_createMdxContent, { ...props }) }) : _createMdxContent(props); } return { default: MDXContent }; function _missingMdxReference(id, component) { throw new Error("Expected " + (component ? "component" : "object") + " `" + id + "` to be defined: you likely forgot to import, pass, or provide it."); }