Basic PL/SQL programming and plsql Programming

Basic PL/SQL programming and plsql Programming
1. Basic Structure of PL/SQL Blocks

DECLARE

/*

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

*/

BEGIN

/*

* Execution part-PL/SQL statements and SQL statements

*/

EXCEPTION

/*

* Exception Handling part -- handling running errors

*/

END;

/* Block end mark */

2. Use variables and constants

1) variables and constants a can be used in PL/SQL blocks. in the declaration part, you must declare B before use. the data type must be specified during declaration. Each row declares an identifier c. use 2) syntax for declaring variables and constants in executable SQL and process statements:

Identifier [CONSTANT] datatype [not null]

[: = | DEFAULT expr];

3) There are two ways to assign values to variables: a. Use the value 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 team ');

END;

3. PL/SQL block type

1) The previous examples of anonymous blocks are all anonymous blocks without names. 2) subroutine a. process: it can complete a series of "methods" for adding, deleting, modifying, and querying actions ". Example: update the salary of an employee with the specified name. The name is case-insensitive.

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); -- the command in SQL * PLUS

Call update_sal ('Scott ', 2000); -- statements called in JAVA

B. function: This function can complete computation and return computation results. Note that data cannot be modified. Example: Calculate twice the number and return it.

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: the code block automatically executed when the data in the table monitored by the trigger changes (add, delete, and modify. D. Package: a package is used for the process and function related to logical "inclusion". It consists of a packet header and a package body. Example: define and call the f_add function in the package.

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) scalar variable a. scalar variable refers to a variable that can only be stored in a single value. B. scalar variables must be defined before use. 2) Common 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 an integer or decimal NUMBER. D. DATE: This data type is used to define DATE and time data. E. BOOLEAN: this data is used to define a BOOLEAN variable. Its values are TRUE, FALSE, or NULL. Note that this type can only be used in PL/SQL, but not in table columns. F. % type: usually used to specify the Data type of a column in a table. It can be understood as "type" (Tip: % read "). Example: print the Department name with the 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;

Running result:

Dept = ACCOUNTING

5. Compound Variables

1) composite variable a. Composite variable refers to the variable used to store multiple values. B. When using composite variables, you must first define "new data types" with "TYPE" and then use these new types to define new variables. 2) composite data type a. Record type: It 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 regarded as a one-dimensional array. Non-continuous index can be used for negative dynamic growth Syntax: type xx is table of type index by integer c. nested table type d. Variable Length array type e. Set type 6.Reference variable 1) reference variable refers to the variable used to store the data address (pointer. 2) by referencing variables, applications can share the same object to reduce space occupation. 3) reference variable type a. ref cursor: The reference CURSOR type defines the CURSOR variable first, and then the select statement used by the CURSOR. The details will be explained in more detail in the future. B. REF obj_type: When referencing an object type to compile an object type application, you can use REF to reference the object type to share the same object. REF is actually a pointer to the object instance.

7. LOB variable

1) the LOB variable refers to the variable used to store large volumes of data. 2) Category a. Internal LOB: it is stored in the database and supports transaction operations (commit, rollback, and save points ). CLOB: stores large batches of character data (specified character set) NCLOB: stores large batches of character data (all character sets) BLOB: stores large batches of binary data B. external LOB: only one type of data is stored in the operating system file and does not support transaction operations. BFILE: stores the pointer to an operating system file.

8. Use SQL * Plus to bind variables

A. When performing data interaction with PL/SQL blocks in SQL * Plus, use SQL * Plus to bind variables. B. When a non-PL/SQL variable is referenced in PL/SQL, a colon (":") must be added 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 compiling PL/SQL blocks, each PL/SQL block contains multiple lines of code, and each line of code is composed of multiple valid units, which are called vocabulary units.

1) PL/SQL vocabulary Unit Classification a. identifiers use identifiers to define constants, variables, exceptions, explicit cursors, cursor variables, parameters, subprograms, and package names. Example: declare v_name emp. ename % type; B. The literal value is written in various specific values of the Code, such as numbers, characters, strings, date values, or boolean values. Example: v_name: = 'lovo '; c. delimiter refers to a single symbol (+,-, *,/) or combination symbol (: =,> =) with a specific meaning ). Example: a: = 10 + 20; 3) Comment a. Single line comment -- single line comment B. Multi-line comment/* multi-line comment */Example

DECLARE

-- Define the v_sal variable

V_sal NUMBER (6, 2 );

BEGIN

/*

Assign values to variables,

Then print the variable

*/

V_sal: = 1000;

Dbms_output.put_line (v_sal );

END;

10. PL/SQL coding rules

1) the advantage of using appropriate writing rules can improve the readability of the code and reduce the difficulty of program maintenance. 2) Rule A. identifier naming Rule a. v _ variable name definition variable B. e _ variable name definition exception c ........ B. The code of the case-insensitive rule is case-insensitive, but it is recommended that the keywords be uppercase. C. Code indent D. nested blocks and variable ranges external blocks can access internal block variables, but not vice versa.