1 / 11

Introduction to PL/SQL

Learn the basic structure of PL/SQL code, including declaring variables, using SQL statements, handling variables, and programming guidelines.

dschmidt
Download Presentation

Introduction to PL/SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Introduction to PL/SQL Francis Thottungal

  2. The outline • The basic PL/SQL code structure is : • DECLARE -- optional, which declares and define variables, cursors and user-defined exceptions. • BEGIN -- mandatory • - SQL statements • - PL/SQL statements • EXCEPTION -- optional, which specifies what actions to take when error occurs. • END; -- mandatory

  3. Hello World DECLAREv1  Varchar2(50);BEGIN   v1 := ‘Hello World’;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END;

  4. SQL Plus Usage At command prompt: C:>SQL SET SERVEROUTPUT ON DECLAREv1  Varchar2(50);BEGIN   v1 := ‘Hello World’;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END; / Note that a PL/SQL block is terminated by a slash / or a line byitself.

  5. EXAMPLE-2 DECLAREv1  NUMBER(3);BEGIN   v1 := 3;   DBMS_OUTPUT.PUT_LINE('v1=' || v1); END;

  6. Handling Variables • Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such  array type VARRAY. • Only TRUE and FALSE can be assigned to BOOLEAN type of variables. • AND, OR, NOT operators can be used to connect BOOLEAN values. • % TYPE attribute can be used to define a variable which is of type the same as a database column's type definition. • Users can customize the variable types by using TYPE ... IS ... statement.

  7. The following code block illustrates the use of TYPE..IS... and VARRAY. In this sample, a type v_arr is defined as an variable array of maximum 25 elements which are of type NUMBER(3).  Then a variable v1 is defined as type v_arr.    This sample code also demonstrates the use of %TYPE attribute. DECLARETYPE v_arr IS VARRAY(25) of NUMBER(3); v1 v_arr; v_empnoemployee.empno%TYPE;BEGIN     v1(2) := 3;    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2));  v_empno  := 4; END;

  8. Programming Guidelines • Single-line comments are prefixed with two dashes --. • Multiple-line comments can be enclosed with the symbols /* and */. • Variables and function identifiers can contain up to 30 characters, and should not have the same name as a database column name. • Identifiers must begin with an alphanumerical character. • SQL functions can be used in PL/SQL. • Code blocks can be nested and unqualified variables can locally scoped. • It is recommended that variable names are prefixed by v_, and parameter names in procedures/functions are prefixed by _p.

  9. DECLAREv_salemployee.sal%TYPE;BEGIN     INSERT INTO employee VALUES (6, 'TOM LEE', 10000);     UPDATE employee SET sal = sal + 5000 WHERE empno = 6; •       SELECT sal INTO v_sal FROM employee WHERE empno = 6;     DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);      DELETE FROM employee WHERE empno = 6;       COMMIT;END;/

  10. A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers. • Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.

  11. DECLARE CURSOR csr_ac (p_name VARCHAR2) ISSELECT empno, name, salFROM employeeWHERE name LIKE '%p_name%'; BEGIN FOR rec_ac IN csr_ac ('LE')LOOP   DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal); END LOOP ; CLOSE csr_ac; END; /

More Related