Functions of SQL begin end blocks

PL/SQL stored procedure programming favorites

/** Author huangchaobiao
* Email: huangchaobiao111@163.com
*/

PL/SQL stored procedure programming (I)
1. Overview of Oracle Application Editing Methods
A: 1) Pro * C/C ++/...: the method of dealing with databases in C language, which is more common than OCI;
2) ODBC
3) OCI: the method of dealing with databases in C language. It is similar to proc, which is lower-level and rarely used;
4) sqlj: a new method to access the Oracle database using Java. There are not many users;
5) JDBC
6) PL/SQL: stores and runs in data. Other methods are used to access the database outside the database;

2. PL/SQL
Answer: 1) PL/SQL (procedual language/SQL) is a procedural processing language based on standard SQL;
2) The operating language for Oracle client tools to access the Oracle server;
3) Oracle SQL expansion;

4. Advantages and Disadvantages of PL/SQL
Answer: advantages:
1) Structured Modular programming, not object-oriented;
2) Good portability (regardless of the operating system in which Oracle runs );
3) good maintainability (stored in the database after compilation );
4) improve system performance;

Disadvantages
1) Migration of applications to heterogeneous databases is not convenientProgram(For Oracle only );

5. Differences between SQL and PL/SQL
A: SQL: 1) fourth generation language (Intelligent language );
2) what to do, no matter how to do it;
3) process and control statements are missing;
4) NoneAlgorithm 
PL/SQL: 1) extended variables and types;
2) extended control structure;
3) Expansion Process and functions;
4) extended object types and Methods

Chapter 2

PL/SQL program structure

1. PL/SQL Block
Answer: 1) Statement, declare (indispensable );
2) execution part, begin... end;
3) exception handling, exception (may not );

2. PL/SQL Development Environment
A: You can use any plain text editor for editing, such as VI.

3. PL/SQL Character Set
Answer: PL/SQL is case insensitive.

4. identifier naming rules
Answer: 1) It must start with a letter;
2) followed by any non-space characters, numbers, currency symbols, underscores, or #;
3) the maximum length is 30 characters (about 8 characters );

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

Chapter 3

1. Data Type
Answer: 1) scalar type: numeric, numeric, Boolean, and date type;
2) combination: Record (commonly used), table (commonly used), and varray (rarely used)
3) reference type: ref cursor (cursor) and ref object_type
4) lob (large object)

2.% Type
A: The variable has the same type as a field in the database table.
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],/* use commas to separate equivalent members */
Field2 type [not null] [: = expr2],/* If a field is not null, it must have an initial value */
.../* All fields not initialized will be initially null
Fieldn type [not null] [: = exprn]);

4.% rowtype
A: A database-defined type is returned.
Declare
V_sturec student % rowtype;/* student indicates the table name */

Note: Compared with setting a record in 3, one step is completed, while 3 defines two steps: A. All member variables must be affirmed; B. instantiate variables;

5. Table type
A: 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 (100) from student where id = 1001;
End;
Note: 1) the limit on the number of rows is determined by the range of binary_integer;

6. Scope and visibility of Variables
Answer: 1) the execution block can be embedded into the execution block;
2) the variable of the execution block in the layer is invisible to the external layer;
3) Modifying block variables at the internal layer will affect the value of block variables at the external layer;

Chapter 4

1. Condition statements
A: If boolean_expression1 then
...
Elsif boolean_expression2 then/* Note that elsif is not elseif */
.../* The else statement is not required, but the end if; statement is required */
Else
...
End if;

2. Loop 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. When reverse is added, it indicates a decrease. The step from the end boundary to the start boundary is one;
B. low_blound start boundary; high_bound end boundary;

3. GOTO statement
A: goto label_name;
1) only internal blocks can be jumped to external blocks;
2) Set tags: <label_name>
3) Example:
Loop
...
If D % rowcount = 50 then
Goto l_close;
End if;
...
End loop;
<L_close>;
...

4. null statement
A: add a statement in the statement block to supplement the statement integrity. Example:
If boolean_expr then
...
Else
NULL;
End if;

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

Chapter 5

1. cursor)
Answer: 1) Role: used to extract multi-row datasets;
2) Statement: A. General statement: delcare cursor cursor_name is select_statement/* the content of 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: Open cursor_name;/* is equivalent to executing the SELECT statement and saving the execution result to cursor;
4) number retrieved from the cursor: A. Fetch cursor_name into var1, var2,...;/* The number, type, and sequence of the variables must be consistent with the fields in the Table ;*/
B. Fetch cursor_name into record_var;
Note: extract the value from the cursor and put it into the variable. One record is obtained every fetch;
5) Close the cursor: Close cursor_name;
Note: A. The cursor should be closed after use;
B. The closed cursor cannot be fetch or closed again;
C. Closing the cursor is equivalent to clearing the contents of the cursor in the memory;

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

3. The fetch loop of the cursor
Answer: 1) loop
Fetch cursor...
Exit when cursor % notfound;/* exit after the cursor is not recorded */
End loop;
2) while cursor % found Loop
Fetch cursor...
End loop;
3) for VAR in cursor Loop
Fetch cursor...
End loop;

Chapter 6

1. Exception
A: declare
...
E_toomanystudents exception;/* declares an exception */
...
Begin
...
Raise e_toomanystudents;/* trigger exception */
...
Exception
When e_toomanystudents then/* trigger exception */
...
When others then/* handle all other exceptions */
...
End;

 

PL/SQL stored procedure programming (II)

1. Stored Procedure (Procedure)
A: creation process:
Create [or replace] Procedure proc_name
[(Arg_name [{In | Out | in out}] type,
Arg_name [{In | Out | in out}] type)]
{Is |}
Procedure_body
1) In: indicates that the parameter cannot be assigned a value (only on the right of the equal sign );
2) Out: indicates that this parameter can only be assigned a value (only on the left of the equal sign );
3) In out: indicates that this type can be assigned values or passed values;

2. Stored Procedure example
A: Create or replace procedure modetest (
P_inparm in number,
P_outparm out number,
P_inoutparm in out number)
Is
V_localvar number;/* declaration part */
Begin
V_localvar: = p_inparm;/* execution part */
P_outparm: = 7;
P_inoutparm: = 7;
...
Exception
.../* Exception Handling part */
End modetest;

3. Example of calling procedure
Answer: 1) Anonymous blocks can be called;
2) Other procdeure can be called;
Example:
Declare
V_var1 number;
Begin
Modetest (12, v_var1, 10 );
End;
Note: v_var1 is 7 at this time.

4. Specify the real parameter Mode
Answer: 1) Location Identification Method: All parameters are added during the call, and the real participation parameters are matched in order;
2) name marking method: the name of the form parameter is given during the call and the real parameter is given.
Modetest (p_inparm => 12, p_outparm => v_var1, p_inout => 10 );
Note: A. The two methods can be mixed;
B. When mixing, the first parameter must be specified by location.

5. Differences between functions and procedure
A: 1) the process call itself is a PL/SQL statement (which can be directly called through the exec statement in the command line );
2) function call is part of an expression;

6. function declaration
A: Create [or replace] Procedure proc_name
[(Arg_name [{In | Out | in out}] type,
Arg_name [{In | Out | in out}] type)]
Return type
{Is |}
Procedure_body
Note: 1) a function that does not return a statement will be an error;

7. Deletion process and Function
A: Drop procedure proc_name;
Drop function func_name;

Chapter 8

1. Package
Answer: 1) The package is a PL/SQL structure that stores related objects;
2) packages can only be stored in the database, not local;
3) A package is a declaration with a name;
4) it is equivalent to the declaration part of a PL/SQL block;
5) anything that appears in the block declaration part 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. The packages provide global variables that can be used for PL/SQL.
8) the packet has a packet header and a packet body. If there is no function or process in the packet header, the packet body does not need it.

2. Baotou
Answer: 1) the header contains information about the content of the packet. the header does not contain any processCode.
2) Syntax:
Create [or replace] package pack_name {is |}
Procedure_specification | function_specification | variable_declaration | type_definition | prediction_declaration | cursor_declaration
End pack_name;
3) Example:
Create or replace package pak_test
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 subject
Answer: 1) The package subject is optional. If the packet header does not contain any functions or procedures, the package subject is not required.
2) the subject and header of the package are stored in different data dictionaries.
3) if the packet header fails to be compiled, the package subject cannot be correctly compiled.
4) The package body contains the code of all processes and functions declared in the header.
5) Example:
Create or replace package body pak_test
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. Package Scope
Answer: 1) when calling the package outside the package (the package name must be added): pak_test.addstudent (100010, 'cs ', 101 );
2) the objects and processes declared in the header can be directly used in the package owner (the package name is not required );

5. overload of package neutron programs
Answer: 1) the process and function in the same package can be reloaded;
2) the same process or function name, but different parameters;

6. Package Initialization
Answer: 1) The package is stored in the database;
2) when the package is called for the first time, it is transferred to the memory from the database and initialized;
3) all variables defined in the package are allocated with memory;
4) Each session will have a copy of its own package variables.

Chapter 9

1. Trigger
Answer: 1) similarities between triggers and processes/functions
A. All execution blocks with names;
B. All declarations, execution bodies, and exceptions;
2) differences between triggers and processes/functions
A. The trigger must be stored in the database;
B. The trigger is 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. Trigger
Answer: 1) three statements (insert/update/delete );
2) two types (before/after );
3) two levels (Row-level/Statement-level );
So a total of 3x2x2 = 12

4. Trigger restrictions
A: 1) transaction control statements should not be used;
2) No long or long raw variables can be declared;
3) tables that can be accessed are limited.

5. tables accessible to the trigger's subject
answer: 1) No change table can be read or modified (the table being modified by the DML statement);
2) you cannot read or modify the primary keys, unique values, and foreign key columns of a restricted table (tables with constraints.