dbpl - Database Procedural Programming PL/SQL and Embedded...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Procedural Programming PL/SQL and Embedded SQL CS2312
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
PL/SQL PL/SQL is Oracle's procedural language extension to SQL. PL/SQL combines SQL with the procedural functionality of a structured programming language, such as IF . .. THEN, WHILE, and LOOP. The PL/SQL engine used to define, compile, and execute PL/SQL program units. A component of many Oracle products, including Oracle Server.
Background image of page 2
Procedures and Functions A set of SQL and PL/SQL statements grouped together as a unit ( block ) to solve a specific problem or perform a set of related tasks. An anonymous block is a PL/SQL block that appears within your application and it is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. Functions always return a single value to the caller; procedures do not return values to the caller. Packages are groups of procedures and functions.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Procedure PL/SQL Example CREATE PROCEDURE credit_labmark (sno NUMBER, cno CHAR, credit NUMBER) AS old_mark NUMBER; new_mark NUMBER; BEGIN SELECT labmark INTO old_mark FROM enrol WHERE studno = sno and courseno = cno FOR UPDATE OF labmark; new_ mark := old_ mark + credit; UPDATE enrol SET labmark = new_mark WHERE studno = sno and courseno = cno; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO enrol(studno, courseno, labmark, exammark) VALUES(sno, cno, credit, null); WHEN OTHERS THEN ROLLBACK; END credit_labmark ; PL/SQL statement . SQL statement . Locks enrol EXECUTE credit_labmark (99234,’CS2312’,20)
Background image of page 4
Function create function get_lab_mark(sno number, cno char) return number as f_lab_mark number; no_mark exception; begin select labmark into f_lab_mark from enrol where studno = sno and courseno = cno; if f_lab_mark is null then raise no_mark; else return(f_lab_mark); end if exception when no_mark then …. .return(null); end;
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Stored Procedures Created in a user's schema and stored, centrally, in compiled form in the database as a named object that can be: interactively executed by a user using a tool like SQL*Plus called explicitly in the code of a database application, such as an Oracle Forms or a Pre compiler application, or in the code of another procedure or trigger When PL/SQL is not stored in the database, applications can send blocks of PL/SQL to the database rather than individual SQL statements reducing network traffic. . . . . HIRE_EMP(…); . Program code . Program code . . Program code . . Program code HIRE_EMP(…) BEGIN END; Database Applications Stored Procedure Database
Background image of page 6
Architecture Procedure Begin Procedurecall Procedurecall SQL Procedurecall SQL End; Procedural Statement Executor PL/SQL Engine SQL Statement Executor SQL Database Program code Program code Procedure call Program code Program code SGA Oracle Server Database Application
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Benefits of Stored Procedures I Security Control data access through procedures and functions.
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 32

dbpl - Database Procedural Programming PL/SQL and Embedded...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online