2004-9-7 Tuesday
PL / SQL database programming (on)
Chapter One
1. Oracle App Editing Method Awards: 1) Pro * C / C / ...: C language and database dealing method, more commonly used than OCI; 2) ODBC 3) OCI: C language and database deal, and PROC is very similar, more underlying, very small; 4) SQLJ: Non-new ways to access the Oracle database with Java, there are not many people; 5) JDBC 6) PL / SQL: Stored in the data, other The method is to access databases outside the database;
2. Pl / SQL Answer: 1) PL / SQL (Procedual Language / SQL) is a language that adds process processing on the basis of standard SQL; 2) Oracle client tools access to Oracle servers; 3) Oracle pair SQL expansion;
4. PL / SQL Advances: Advantages: 1) Structured Modular Programming, is not object-oriented; 2) Good portability (regardless of Oracle runs in what operating system); 3) Good maintenance ( Store after compiling in the database); 4) enhance system performance;
Disadvantages 1) Invenment to transfer applications to heterogeneous databases (only for Oracle);
5. SQL and PL / SQL Difference Answer: SQL: 1) The fourth generation language (smart language); 2) What to do, no matter what to do; 3) lack of process and control statements; 4) No algorithm PL / SQL: 1 Extend variable and type; 2) extended control structure; 3) Extended process and function; 4) Extended object type and method
Chapter two
PL / SQL program structure
1. PL / SQL block Answer: 1) NPT, DECLARE (not least); 2) Execution part, begin ... End; 3) abnormal processing, Exception (no);
2. PL / SQL Development Environment Answer: Editing any plain text editor, for example: vi
3. PL / SQL character set: PL / SQL is not sensitive to case sensitive
4. Identifier Name Rules Answer: 1) The beginning of the letter; 2) followed by any non-space character, digital, currency symbol, underscore, or #; 3) maximum length of 30 characters (most appropriate to eight characters);
5. Variable declaration Answer: Syntax VAR_NAME TYPE [Constant] [not null] [: = value]; Note: 1) The default value can be no; 2) If there is [constant] [not null], the variable must be There is an initial value; 3) Assigning statements are ": ="; 4) variables can be considered as a field in the database; 5) Specify that there is no initialization variable to null;
third chapter
1. Data type A: 1) Scalar type: digital, character type, Booler, date type; 2) Combined: Record (common), Table (common), Varray (less) 3) Reference: Ref Cursor (Cursor), Ref Object_Type 4) LOB (Large Object)
2.% TYPE A: Variable has the same type of type example as a field in the table of the database: v_firstname studengts.first_name% type;
3. Record Type Answer: Type Record_name is record (/ * where type, is, record is keyword, record_name is the variable name * / field1 type [not null] [: = expr1], / * Equivalent member Separate with a comma * / Field2 Type [not null] [: = expr2], / * If a field defines not null, then it must have an initial value * / ... / * All the fields that are not initialized will be NULL FIELDN. TYPE [NOT NULL] [: = exprn]); 4.% rowtype A: Return to a database-defined type Declare v_sturec student% rowType; / * student is the name of the table * /
Note: One step is completed by one step compared to 3, and the 3 will define two steps: a. All member variables must be declared; 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 of the number of rows is determined by the range of binary_integer;
6. Scope of variables and visibility: 1) Execution block can be embedded in blocks; 2) The variable of the inner layer execution block is invisible to the outer layer; The value of the layer variable;
Chapter Four
1. Conditional Sicks: if Boolean_Expression1 Then ... Elsif Boolean_Expression2 Then / * Note It is Elsif, not the elseif * / ... / * Else statement is not necessary, but end if; is a must * / else ... END IF;
2. Circular Word Name: 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 is decremented, from the end boundary to the starting boundary, decreasing steps; b. Low_blound start border; high_bound End the boundary;
3. GOTO statement answer: goto label_name; 1) can only be jumped from internal blocks to external blocks; 2) Setting tag: << label_name >> 3) Example: loop ... if D% ROWCOUNT = 50 THEN GOTO L_CLOSE; END If; ... End loop; << l_close >>; ... 4. null statement A: A blank statement in the statement block is used to supplement the integrity of the statement. Example: IF Boolean_Expr Then ... Else Null; endiff;
5. SQL IN PL / SQL Answer: 1) Only DML SQL can be used directly in PL / SQL;
chapter Five
1. Cursor Answer: 1) Role: Used to extract multi-line data sets; 2) Declaration: a. Ordinary declaration: DELCARE CURSOR CURSOR_NAME IS SELECT_STATEMENT / * CURSOR's content must be a query statement * / b. Tape parameters Delcare Cursor C_STU (p_id student.id% type) select * from student where id = p_id; 3) Open the cursor: Open cursor_name; / * is equivalent to executing the SELECT statement and deposits the execution result into CURSOR; 4) from the cursor Number of Number: a. Fetch Cursor_name INTO VAR1, VAR2, ...; / * Variable Quantity, Type, Sequential and Table in Table; * / b. Fetch Cursor_name Into Record_var; Note: Put the value from Cursor In the variable, each FETCH takes a record at a time; 5) Close the cursor: close cursor_name; Note: a. The cursor should be closed after the cursor is used; b. Clearance after the shutdown cannot be fetch and again; c. Close the cursor is equivalent to the memory Cursor's content is emptied;
2. Property of the cursor: 1)% Found: Is there a value; 2)% notfound: Whether there is no value; 3)% isopen: Whether it is open state; 4)% rowcount: Cursor's current record number;
3. Cursor's FETCH loop answer: 1) loop fetch cursor inTo ... exit when cursor% notfound; / * When there is no record in Cursor, exit * / end loop; 2) While Cursor% Found Loop Fetch Cursor INTO ... End loop; 3) for var in cursor loop fetch cursor in ... End loop; Chapter 6
1. Auspend: declare ... e_toomanystudents Exception; / * Declination exception * / ... begin ... raise e_toomanystudents; / * Trigger exception * / ... Exception when e_toomanystudents Then / * trigger exception * / ... When others kilover all other exceptions * / ... END; 2004-9-8 Wednesday
PL / SQL database programming (below)
1. PROCEDURE A: Create 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 on the right side); 2) OUT: Indicates that the parameter can only be assigned (only left side); 3) IN OUT: indicates that Types can be assigned values;
2. A stored procedure example: CREATE OR REPLACE PROCEDURE ModeTest (p_InParm IN NUMBER, p_OutParm OUT NUMBER, p_InOutParm IN OUT NUMBER) IS v_LocalVar NUMBER; / * Declaration section * / BEGIN v_LocalVar: = p_InParm; / * operative * / p_OutParm: = 7; p_inoutparm: = 7; ... Exception ... / * exception handling part * / end modeSt; 3. Call Procedure Example A: 1) Anonymous block can be adjusted; 2) Other procdeure can be called; example: Declare v_var1 number; begin modetest (12, v_var1, 10); end; Note: V_var1 is equal to 7
4. Specify the mode of the argument: 1) Location label: Additional parameters are added when calling, and participate in the sequence of sequence; 2) Name labeling: When calling, give the export-top, and give NODETEST (p_inparm => 12, p_outparm => v_var1, p_inout => 10); Note: a. Two methods can be mixed; b. The first parameter must be specified by the position when mixing.
5. Function and Procedure Difference Answer: 1) Process Call itself is a PL / SQL statement (can be called directly in the command line); 2) The function call is part of the expression;
6. Declaration of functions: create [or report] procedure proc_name [(arg_name [{in | out | in out}] type, arg_name [{in | out | in out}] type)] return type {is | Procedure_body Note: 1) The function that does not return statement will be an error; 7. Delete process and function Answer: Drop Procedure Proc_name; Drop function func_name;
chapter eight
1. Package: 1) Package is a PL / SQL structure that can store the related objects; 2) The package can only be stored in the database, and cannot be local; 3) Package is a statement with name; 4 ) Equivalent to a declaration portion of a PL / SQL block; 5) Anything appears in the declaration portion of the block can appear in the package; 6) The package can include processes, functions, cursors and variables; 7) can be from other PLs / SQL blocks Reference packets, package provides global variables available for PL / SQL. 8) There is a header and a package body, such as no functions and processes in the header, the body can not be required.
2. Baotou A: 1) The header contains information about the content of the package, and the header does not contain the code of any process. 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. Body Answer: 1) The main body is optional, such as no function and process in the header, the package body can not be required. 2) The cover body is stored in different data dictionary. 3) If the Baotou compiled is unsuccessful, the body cannot be compiled correctly. 4) The cover body contains all the processes and functions of all processes 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. The role of the package: 1) The process of calling the package outside the package (add the package): pak_test.addstudent (100010, 'cs', 101); 2) Can directly use the header in the package body Objects and processes (no need to add package);
5. Weighing: 1) The process and function in the same package can be overloaded; 2) The same process or function name, but the parameters are different; 6. Initial answer: 1) Packing In the database; 2) When the first call is called, the package is transferred from the database and is initialized; 3) All variables defined in the package are allocated in memory; 4) Each session will have its own package A copy of the inner variable.
Chapter nine
1. Trigger A: 1) The same point of the trigger and the process / function is the execution block with the name; b. There are declarations, executives and exception part; 2) Different triggers and process / functions Point a. The trigger must be stored in the database; b. The trigger is automatically executed;
2. Create A Trigger: 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: 1) Three statements (Insert / update / delete); 2) Two types (after / after); 3) two levels (Row-Level / Statement-Level); So there are 3 x 2 x 2 = 12
4. Restrictions on the trigger: 1) Do not use transaction control statements; 2) No long or long RAW variables; 3) The table that can be accessed is limited.
5. Recipients that the body of the trigger can be accessed: 1) You cannot read or modify any change table (a table being modified by the DML statement); 2) You can not read or modify the restriction table (with constraints) Main key, unique value, foreign key column.
