PL/SQL Programming basics

1. The infrastructure of a PL/SQL block

DECLARE

/*

* Definition section--Define constants, variables, complex data types, cursors, user-defined exceptions

*/

BEGIN

/*

* Execute partial--PL/SQL statements and SQL statements

*/

EXCEPTION

/*

* Exception Handling section--handling run errors

*/

END;

/* Block end tag */

2. Using variables and constants

1) Variable and constant A can be used in PL/SQL blocks. The declaration section declares that B must be declared before use. You must specify a data type when declaring, and each row declares an identifier C. Use 2 in SQL statements and procedure statements in the executable section to declare the syntax of variables and constants:

identifier [CONSTANT] datatype [not NULL]

[:= | DEFAULT expr];

3) There are two ways to assign a value to a variable: a. Use an assignment statement: =b. Use the SELECT into statement for example:

DECLARE

V_name VARCHAR2 (10);

BEGIN

Select Dname into V_name

From Dept

where Deptno = 90;

Dbms_output.put_line (' dname: ' | | v_name);

EXCEPTION when No_data_found Then

Dbms_output.put_line (' No such department ');

END;

3. Types of PL/SQL blocks

1) Anonymous blocks the previous examples do all anonymous blocks that do not have a name. 2) sub-program A. Process: Able to complete a series of additions and deletions to the action of the "method." Example: Update specified name employee salary, the name does not differ from small to lowercase.

Create or replace procedure Update_sal

(P_ename VARCHAR2, p_newsal number)

As

BEGIN

UPDATE emp SET sal = p_newsal

WHERE lower (ename) = lower (p_ename);

Commit

END;

Call:

EXEC update_sal (' Scott ', 2000); Commands in the--sql*plus

Call Update_sal (' SCOTT ', 2000); Statements that are called in--java

B. Function: Ability to complete the calculation and return the calculation results, note that the data cannot be modified. Example: Computes a number twice times after and returns.

Create or Replace function F_add (a number)

return number

As

Begin

return a*2;

End

Call:

Select F_add (2) from dual;

Select F_add (DEPTNO) from dept;

C. Trigger: A code block that is automatically executed when the data of the table being monitored by the trigger is changed (modified). D. Package: The package is used to logically "contain" the related procedures and functions, which consists of the Baotou and the package body two parts. Example: Define the previous function F_add in the package and call it.

Create or Replace package My_bao is

function F_add (a number) return number;

End

/

Create or Replace package body My_bao is

function F_add (a number) return number

As

Begin

return a*2;

End

End

/

Call: Select My_bao.f_add (2) from dual;

4.Scalar variable 1) a scalar variable a. A scalar variable is a variable that can only be stored in a single value. B. Scalar variables must be defined before use. 2) commonly used scalar type A. VARCHAR2 (n): This data type is used to define a variable-length string, n<=4000. B. CHAR (n): This data type is used to define a fixed-length string, n<=2000. C. Number (total digits, decimal places): defines integers or decimals. D. Date: This data type is used to define date and time data. E. Boolean: This data is used to define a Boolean variable whose value is true, FALSE, or null. Note This type can only be used in PL/SQL, and table columns do not have this type. F.%type: Typically used to specify the data type of a column of a table, which can be understood as "type" (Tip:% read "). Example: Print department name with department Number 10.

Declare

V_name Dept.dname%type;

Begin

Select Dept.dname into V_name

From Dept

where deptno = 10;

Dbms_output.put_line (' dept = ' | | V_name);

End

Operation Result:

Dept = ACCOUNTING

5. Composite variables

1) compound variable A. A compound variable is a variable that holds multiple values. B. When using a composite variable, you must first define a "new data type" with the type, and then define the new variable with these new types. 2) composite data type A. Record type: Can be simply understood as a variable with multiple "attributes."

DECLARE

TYPE Emp_record_type is record (

Name Emp.ename%type,

Salary emp.sal%type);

EMP Emp_record_type;

BEGIN

SELECT ename,sal,job into EMP

from EMP WHERE empno=7788;

Dbms_output.put_line (' Employee Name: ' | | emp. name);

END;

B. The index table type PL/SQL Index table is similar to the collection interface, or it can be considered as a 1-d array. A discontinuous index can be a negative dynamic growth syntax: Type XX is the table of type index by int c. Nested table Type D. Variable-length array type E. Collection type 6.Reference variable 1) reference variable refers to a variable that holds the data address (pointer). 2) Benefits by using reference variables, you can make your application share the same objects, thereby reducing the footprint. 3) Reference variable type A. REF cursor: A reference to a cursor type defines a cursor variable before defining the SELECT statement used by that cursor. This will be explained in more detail in the following cursors. B. Ref Obj_type: When writing object types with reference object types, in order to share the same object, you can use ref to reference the object type, and ref is actually a pointer to an object instance.

7. LOB variables

1) LOB variable LOB variable refers to a variable that is used to store large amounts of data. 2) Category A. Internal lob: stored in the database and supports transactional operations (commit, fallback, savepoint). CLOB: Storing large-volume character data (specifying a character set) NCLOB: Storing bulk character data (all character sets) BLOBs: Storing large batches of binary data B. External LOB: There is only one type of data that is stored in the operating system file and does not support transactional operations. BFILE: Storing pointers to operating system files

8. Binding variables using Sql*plus

A. When data is interacting with PL/SQL blocks in Sql*plus, you need to use the Sql*plus binding variable to complete. B. When referencing non-PL/SQL variables in PL/SQL, you must add a colon (":") before the non-PL/SQL variable.

sql> var name varchar2 (10);

Sql> BEGIN

2 SELECT ename into:name from emp

3 WHERE empno = 7788;

4 End;

5/

sql> Print name;

Name

---------

SCOTT

9. PL/SQL Vocabulary Unit

When you write a PL/SQL block, each PL/SQL block contains multiple lines of code, and each line of code is composed of more than one legitimate unit, which is called a lexical unit.

1) PL/SQL lexical unit category A. Identifiers allow you to define constants, variables, exceptions, explicit cursors, cursor variables, parameters, subroutines, and the names of packages by using identifiers. Example: Declare V_name emp.ename%type;b. Literal writing in code of various specific numeric values, such as numbers, characters, strings, date values, or Boolean values. Example: v_name: = ' Lovo '; c. Delimiter delimiter refers to a single symbol (+ 、-、 *,/) or a combined symbol (: =, >=) with a specific meaning. Example: A: = 10 + 20; 3) Note A. Single-line Comment-Single line comment B. Multiline Comment/* multi-line Comment */example

DECLARE

--Define V_sal variables

V_sal number (6,2);

BEGIN

/*

Assign a value to a variable,

And then print this variable

*/

V_sal: = 1000;

Dbms_output.put_line (v_sal);

END;

10.pl/sql Code Authoring Rules

1) Benefits using appropriate writing rules can improve the readability of the code and reduce the difficulty of maintaining the program. 2) Rule A. Identifier naming rule A. V_ variable name defines variable B. E_ variable name defines exception C. .... B. The casing rule code is not case-sensitive, but it is recommended to capitalize the keyword. C. Code indent D. Nested blocks and variable extents external blocks can access variables of the inner block, or vice versa.

PL/SQL Programming basics