PL/SQL Stored procedure programming

PL/SQL Stored procedure programming

/**author Huangchaobiao
*email:[email protected]
*/

PL/SQL stored procedure programming (UP)
1. Overview of Oracle Application editing methods
A: 1) pro*c/c++/: C language and database approach, more common than OCI;
2) ODBC
3) Oci:c language and database to deal with the method, and proc very similar to the bottom, rarely used;
4) SQLJ: A very new way to access the Oracle database in Java, not many people;
5) JDBC
6) PL/sql: stored in the data run, other methods for database access outside the database;

2. PL/SQL
A: 1) PL/SQL (procedual language/sql) is a language that has been added to the process of processing on the basis of the standard.
2) Oracle Client Tools access the operating language of the Oracle server;
3) Oracle's expansion of SQL;

4. The pros and cons of PL/SQL
A: Advantages:
1) Structured modular programming, not object-oriented;
2) Good portability (regardless of which operating system Oracle is running on);
3) Good maintainability (compiled and stored in database);
4) Improve system performance;

Disadvantages
1) It is not easy to migrate applications to heterogeneous databases (Oracle only);

5. The difference between SQL vs. pl
Answer: sql:1) Fourth generation language (smart language);
2) What to do, no matter how;
3) Lack of process and control statements;
4) No algorithm
PL/SQL:1) extended variables and types;
2) extended control structure;
3) extended processes and functions;
4) Extended object types and methods

Chapter II

PL/SQL Program structure

1. PL/SQL blocks
A: 1) Declaration part, DECLARE (not less);
2) Executive section, BEGIN ... END;
3) exception handling, EXCEPTION (can not);

2. PL/SQL Development environment
Answer: You can use any plain text editor, for example: VI

3. PL/SQL Character set
Answer: PL/SQL is not sensitive to case

4. Naming rules for identifiers
A: 1) the letter begins;
2) followed by any non-empty characters, numbers, currency symbols, underscores, or #;
3) The maximum length is 30 characters (eight characters or so is the most suitable);

5. Variable declaration
Answer: syntax
Var_name type [Constant][not null][:=value];
Note: 1) The declaration can have a default value can also not;
2) If [Constant][not NULL], the variable must have an initial value;
3) The assignment statement is ": =";
4) variables can be considered as a field in the database;
5) Specify that the variable that is not initialized is null;

Chapter III

1. Data type
A: 1) scalar type: Digital type, character type, Boolean type, date type;
2) Combination type: RECORD (Common), TABLE (Common), varray (less used)
3) Reference type: REF CURSOR (cursor), ref object_type
4) LOB (Large Object)

2.%TYPE
A: A variable has the same type as a field in a table in the database
Example: V_firstname studengts.first_name%type;

3. Record type
A: TYPE Record_name is record (/* where Type,is,record is the keyword, record_name is the variable name */
Field1 type [not NULL][:=EXPR1],/* Comma separated by each equivalent member */
Field2 type [not NULL][:=EXPR2],/* If a field qualifies not NULL, then it must have an initial value */
.../* All fields that are not initialized will be initially null
FIELDN type [not NULL][:=EXPRN]);

4.%rowtype
Answer: Returns a database-defined type
DECLARE
V_sturec Student%rowtype; /*student is the name of the table */

Note: One step is complete compared to a record in 3, while 3 is defined in two steps: A. All the member variables must be declared; B. Instantiate variables;

5. Table Type
Answer: Type Tabletype is TABLE of TYPE INDEX by Binary_integer;
Example: DECLARE
TYPE t_stutable is TABLE of Student%rowtype INDEX by Binary_interger;
V_student t_stutable;
BEGIN
SELECT * into v_student (+) from Student WHERE id = 1001;
END;
Note: 1) The limit of the number of lines is determined by the scope of Binary_integer;

6. Scope and visibility of variables
A: 1) the execution block can be embedded in the execution block;
2) The outer layer of the inner execution block is not visible;
3) The modification of the outer layer execution block variable will affect the value of the external block variable;

Fourth Chapter

1. Conditional statements
Answer: IF Boolean_expression1 Then
...
elsif Boolean_expression2 Then/* Note is elsif, not elseif*/
The./*else statement is not required, but end IF; is required */
ELSE
...
END IF;

2. Looping statements
Answer: 1) Loop
...
IF boolean_expr then/* *
EXIT; /* EXIT when boolean_expr */
END IF; /* */
END LOOP;
2) while boolean_expr LOOP
...
END LOOP;
3) for loop_counter in [REVERSE] low_blound.. High_bound LOOP
...
END LOOP;
Note: A. Plus reverse means descending, from the end boundary to the starting boundary, the descending step is one;
B. Low_blound initial boundary; High_bound end border;

3. Goto statement
Answer: GOTO label_name;
1) can only be jumped from the inner block to the external block;
2) Set label:<<label_name>>
3) Example:
LOOP
...
IF D%rowcount =
GOTO L_close;
END IF;
...
END LOOP;
<<l_close>>;
...

4. NULL statement
A: Add an empty statement to the statement block to complement the integrity of the statement. Example:
IF boolean_expr Then
...
ELSE
NULL;
END IF;

5. SQL in/PL
Answer: 1) only DML SQL can be used directly in PL/n;

Fifth Chapter

1. Cursors (cursor)
A: 1) function: Used to extract multiple rows of data sets;
2) Statement: A. General declaration: Delcare Cursor Cursor_name is select_statement/* The contents of the cursor must be a query statement */
B. With parameter declaration: Delcare CURSOR C_stu (p_id student.id%type) SELECT * FROM student WHERE id = p_id;
3) Open cursor: opening cursor_name; /* Equivalent to executing the SELECT statement and storing the execution result in cursor;
4) Take the number from the cursor: A. FETCH cursor_name into var1, var2, ...; /* The number, type, and order of variables are consistent with the fields in the table; */
B. FETCH cursor_name into Record_var;
Note: Remove the value from the cursor into the variable and fetch one record at a time per fetch;
5) Close the cursor: close cursor_name;
Note: A. The cursor should be closed after use;
B. The closed cursor cannot fetch and close again;
C. Closing the cursor is equivalent to emptying the contents of the cursor in memory;

2. Properties of Cursors
Answer: 1)%found: whether there is a value;
2)%notfound: Whether there is no value;
3)%isopen: Whether it is open state;
4)%rowcount:cursor the current record number;

3. Fetch loops for cursors
Answer: 1) LOOP
FETCH cursor into ...
EXIT when Cursor%notfound; /* Exit when the cursor is not recorded */
END LOOP;
2) while Cursor%found LOOP
FETCH cursor into ...
END LOOP;
3) for Var in cursor LOOP
FETCH cursor into ...
END LOOP;

Sixth chapter

1. Exceptions
Answer: DECLARE
...
E_toomanystudents EXCEPTION; /* DECLARE exception */
...
BEGIN
...
RAISE e_toomanystudents; /* Trigger Exception */
...
EXCEPTION
When E_toomanystudents and/* Triggers an exception */
...
When OTHERS and/* handles all other exceptions */
...
END;

PL/SQL stored procedure programming (bottom)

1. Stored Procedures (PROCEDURE)
A: The creation process:
CREATE [OR REPLACE] PROCEDURE proc_name
[(arg_name[{in| Out| In Out}]type,
arg_name[{in| Out| In Out}]type)]
{Is|as}
Procedure_body
1) In: Indicates that the parameter cannot be assigned (only to the right of the equal sign);
2) Out: Indicates that the parameter can only be assigned (only on the left side of the equals sign);
3) in out: Indicates that the type can be both assigned and can be transmitted value;

2. Examples of stored procedures
Answer: CREATE OR REPLACE PROCEDURE modetest (
P_inparm in number,
P_outparm out number,
P_inoutparm in Out number)
Is
V_localvar number; /* DECLARE part */
BEGIN
V_localvar:=p_inparm; /* Execute part */
p_outparm:=7;
p_inoutparm:=7;
...
EXCEPTION
.../* Exception handling section */
END modetest;

3. Example of calling procedure
Answer: 1) Anonymous block can be adjusted;
2) Other procdeure can be called;
Cases:
DECLARE
V_VAR1 number;
BEGIN
Modetest (V_VAR1, 10);
END;
Note: V_var1 equals 7 at this time

4. Specify the mode of the argument
A: 1) location notation: All parameters are added to the call, and the actual participation parameter corresponds in order one by one;
2) name notation: The name of the formal parameter is given when called, and the argument is given
Modetest (p_inparm=>12, P_outparm=>v_var1, p_inout=>10);
Note: A. Two methods can be mixed with each other;
B. When mixing the first parameter must be specified by location.

5. The difference between function and procedure (Procedure)
A: 1) The procedure call itself is a PL/SQL statement (which can be called directly from the command line via the EXEC statement);
2) A function call is part of an expression;

6. Declaration of functions
Answer: CREATE [OR REPLACE] FUNCTION proc_name
[(arg_name[{in| Out| In Out}]type,
arg_name[{in| Out| In Out}]type)]
RETURN TYPE
{Is|as}
Procedure_body
Note: 1) A function that does not return a statement will be an error;

7. Deleting procedures and functions
Answer: DROP PROCEDURE proc_name;
DROP FUNCTION Func_name;

Eighth Chapter

1. Package
A: 1) the package is a PL/SQL structure that can store related objects together;
2) The package can only be stored in the database, not local;
3) The package is a declaration with a name;
4) equivalent to the declaration portion of a PL/SQL block;
5) Anything that appears in the declaration section of a block can appear in the package;
6) The package can contain procedures, functions, cursors and variables;
7) packages can be referenced from other PL/SQL blocks, and the package provides global variables that are available for PL/SQL.
8) package has Baotou and the package body, such as the Baotou does not have any functions and procedures, the package body can not be required.

2. Baotou
A: 1) Baotou contains information about the contents of the package, the header does not contain any process code.
2) Syntax:
CREATE [OR REPLACE] Package pack_name {Is|as}
procedure_specification|function_specification|variable_declaration|type_definition|exception_declaration| Cursor_declaration
END Pack_name;
3) Example:
CREATE OR REPLACE Package pak_test as
PROCEDURE removestudent (P_stuid in Students.id%type);
TYPE t_stuidtable is TABLE of Students.id%type INDEX by Binary_integer;
END pak_test;

3. Package Body
A: 1) the package body is optional, such as there are no functions and procedures in the Baotou, the package body can not be required.
2) The package body and the Baotou are stored in different data dictionaries.
3) If the header compilation is unsuccessful, the package body cannot compile correctly.
4) The package body contains all the code for all the procedures and functions declared in the header.
5) Example:
CREATE OR REPLACE Package BODY Pak_test as
PROCEDURE removestudent (P_stuid in Students.id%type) is
BEGIN
...
END removestudent;
TYPE t_stuidtable is TABLE of Students.id%type INDEX by Binary_integer;
END pak_test;

4. Scope of the package
A: 1) in the package out of the process (need to add the package name): Pak_test. Addstudent (100010, ' CS ', 101);
2) The object and process declared in the header can be directly used in the package body (no need to add the package name);

5. Overloading of the package neutron program
A: 1) the procedures and functions in the same package can be overloaded;
2) The same process or function name, but the parameters are different;

6. Initialization of the package
Answer: 1) The package is stored in the database;
2) in the first call, the packet is transferred from the database into the memory and initialized;
3) All variables defined in the package are allocated memory;
4) Each session will have its own copy of the variable within the package.

Nineth Chapter

1. Triggers
Answer: 1) The same point as the trigger and the procedure/function
A. are all execution blocks with names;
B. There are declarations, enforcement bodies and abnormal parts;
2) different points of trigger and process/function
A. Triggers must be stored in the database;
B. Triggers are automatically executed;

2. Create a Trigger
Answer: 1) Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
{before| After} triggering_event on table_reference
[For each ROW [when Trigger_condition]]
Trigger_body;
2) Example:
CREATE or REPLACE TRIGGER updatemajorstats after INSERT or DELETE or UPDATE on students
DECLARE
CURSOR C_statistics is
SELECT * from students GROUP by major;
BEGIN
...
END up;

3. Triggers
A: 1) three statements (Insert/update/delete);
2) Two types (before/after);
3) two different levels (row-level/statement-level);
So a total of 3 x 2 x 2 = 12

4. Limitations OF Triggers
A: 1) You should not use transaction control statements;
2) cannot declare any long or long raw variables;
3) Limited access to the table.

5. Tables that the principal of the trigger can access
A: 1) You cannot read or modify any table of changes (the table being modified by DML statements);
2) You cannot read or modify the primary key, unique value, or foreign key column of a restricted table (a table with constraints).

PL/SQL Stored procedure programming