◎ Checkpoint is used to recover only the data of the point ◎ View a few table select * from tab; ◎ ED can be edited ◎ / repeat command ◎ Merger string || ◎ View table structure DESC table name ◎ SQL in string Use single quotes ◎ The main construction can consist of multiple fields ◎ foreign key cannot be empty, and must exist in another table ◎ left-way link, that is, the left side is displayed all the links and the right side, there is no, the left is blank Fill in the blank ◎ You can use the @ 加 文 name to perform the PLSQL statement in SQLPLUS ◎ Use% Type to make the variables in the stored procedure agree to the type in the table ◎ If the statement acquires the Name field in STU_TB01 consistent declare v_name varchar2 (20): = 'huangzh'; v_id number: = 999; c_id constant number: = 1000; begin dbms_output.put_line (v_name); dbms_output.put_line (c_id); select name into v_name from stu_tb01 where id = v_id; dbms_output.put_line (v_name); END; / ◎ Use record to define structural declare v_name varcha2 (20): = 'Huangzh'; v_id number: = 999; c_id constant number: = 1000; type my_rec is record (V1 Number , v2 varchar (20)); v_rec stu_tb01% rowtype; begin dbms_output.put_line (v_name); dbms_output.put_line (c_id); select name into v_name from stu_tb01 where id = v_id; dbms_output.put_line (v_name); select * into v_rec From stu_tb01 where id = v_id; dbms_output.put_line (v_rec.id); dbms_output.put_line (v_rec.name); end; /
◎ The above use% ROWTYPE can correspond to the type of record and the table, so that the type of type change is changed, then this type can also change ◎ or more can be declared by my_rec v_rec to declare the record type variable ◎ Data in additional records The Table type in the database can be used in the database is similar to the MAP type in C . is record (v1 number, v2 varchar (20)); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; begin dbms_output.put_line (v_name); dbms_output.put_line (c_id); select name into v_name from stu_tb01 where id = v_id; dbms_output.put_line (v_name); select * into v_rec from stu_tb01 where id = v_id; dbms_output.put_line (v_rec.id); dbms_output.put_line (v_rec.name); select * into v_student ( 1001) from Stu_TB01 WHERE ID = V_ID; DBMS_OUTPUT.PUT_LINE (v_student (1001) .id); dbms_output.put_line (v_student (1001) .name); END; / ◎ Oracle control statement declare v_name varcha2 (20): = 'huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; type my_rec is record (v1 number, v2 varchar (20)); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; begin dbms_output.put_line (v_name); dbms_output.put_line (c_id); select name into v_name from stu_tb01 where id = v_id; dbms_output.put_line (v_name); select * into v_rec from stu_tb01 where id = v_id; dbms_output.put_line (v_rec.id); dbms_output.put_line (v_rec.name); select * into v_student (1001) from stu_tb01 where id = V_ID; dbms_output.put_line (v_student (1001) .id); dbms_output.put_line (v_student (1001) .name); loop dbms_output.put_line ('Hello'); if (v_cnt> =
10) THEN EXIT; End if; v_cnt: = v_cnt 1; end loop; while v_cnt> 0 loop dbms_output.put_line ('Hello'); v_cnt: = v_cnt-1; end loop; for vin reverse 1..5 LOOP DBMS_OUTPUT.PUT_LINE ('Huangzh'); end loop; end; / ◎ NGLE IF (TRUE) THEN NULL; END IF; ◎ Method using a cursor Declare v_name varcha2 (20): = 'Huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; v_str varchar2 (200); type my_rec is record (v1 number, v2 varchar (20)); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; cursor stu_cur is // defining a cursor select id, name from stu_tb01; begin dbms_output.put_line (v_name); dbms_output.put_line (c_id); select name into v_name from stu_tb01 where id = v_id; dbms_output.put_line ( v_name); select * into v_rec from stu_tb01 where id = v_id; dbms_output.put_line (v_rec.id); dbms_output.put_line (v_rec.name); select * into v_student (1001) from stu_tb01 where id = v_id; dbms_output.put_l INE (v_student (1001) .id); dbms_output.put_line (v_student (1001) .name); loop dbms_output.put_line ('Hello'); if (v_cnt> = 10) Then EXIT; END IF; v_cnt: = v_cnt 1; end loop; while v_cnt> 0 loop dbms_output.put_line ('Hello'); v_cnt: = v_cnt-1; end loop; for v in reverse 1..5 loop dbms_output.put_line ('Huangzh'); end loop; IF (TRUE) THEN NULL; END IF; Open Stu_Cur; // Open Cursor Loop // Loop Performs Value Fetch Stu_cur Into V_ID, V_Name; DBMS_OUTPUT.PUT_LINE (V_ID); dBMS_OUTPUT.PUT_LINE (v_name); exit when stu_cur% notfound; end loop; close stu_cur; // Turn off the cursor end;
/ ◎ Note - ◎ You can use the parameters such as: Cursor Stu_cur (Variable Name type) such as: declare v_name varcha2 (20): = 'Huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; v_str varchar2 (200); type my_rec is record (v1 number, v2 varchar (20)); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; cursor stu_cur (vv_id number) is select id, name from stu_tb01 where id
DECLARE V_NAME VARCHAR2 (20): = 'Huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; v_str varchar2 (200); type my_rec is record (V1 Number, v2 varchar (20) ); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; cursor stu_cur (vv_id number) is select id, name from stu_tb01 where id
◎ Create a stored procedure to create or replace procedure firstProc is v_name varchar2 (20): = 'huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; v_str varchar2 (200); type my_rec is record (v1 number, v2 varchar (20)); v_rec stu_tb01% rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; cursor stu_cur (vv_id number) is select id, name from stu_tb01 where id
◎ Give a creation of a stored procedure: Grant Create Procedure to Student ◎ Give a creation function: Grant Create Function To Student ◎ Parameters in Procedure InUn for only the parameter OUT can only modify the parameter inout That is, the parameter that can be read and modified. ◎ Delete Procedure method DROP Procedure Stored Procedure Name ◎ When creating procedure, the parameter cannot specify the length, and can also pass the parameter name when the parameter name is specified, without the need according to the basis Location Introduction, such as: Exec Firstproc (p_str => 'Huangzh', p_num => 999) - Specify incoming parameters Exec Firstproc ('Huangzh', 999) - based on the location to specify the incoming parameter ◎ in Procedure Other Procedure ◎ Nesting in Procedure and sets its default values such as: Create or Replace Procedure Firstproc (p_str in varchar2: = 'Call Parameter', p_num in number: = 100) is v_name varchar2 (20): = 'Huangzh'; v_id number: = 999; c_id constant number: = 1000; v_cnt number: = 0; v_str varchar2 (200); v_num number; type my_rec is record (V1 Number, V2 varchar (20)); v_rec stu_tb01 % rowtype; type t_StuTable is table of stu_tb01% rowtype index by binary_integer; v_student t_StuTable; cursor stu_cur (vv_id number) is select id, name from stu_tb01 where id
v_cnt 1; end loop; while v_cnt> 0 loop dbms_output.put_line ('Hello'); v_cnt: = v_cnt-1; end loop; for vin reverse 1..5 loop dbms_output.put_line ('Huangzh'); END loop; if (true) then null; end if; open stu_cur (600); loop fetch stu_cur into v_id, v_name; dbms_output.put_line (v_id); dbms_output.put_line (v_name); exit when stu_cur% notfound; end loop; close stu_cur; dbms_output.put_line ( 'hello procedure'); dbms_output.put_line (p_str); dbms_output.put_line (v_num); exception when others then dbms_output.put_line ( 'occur exception'); end firstProc; ◎ packets can be stored in a database in
◎ Create a statement, such as: Create or Replace package my_pkgis g_num number; procedure pkg_proc (p1 number: = 0, p2 varchar); Function PKG_FUNC (F1 Number, f2 varchar) Return Number; End my_pkg;
◎ Function, the process can be overloaded, the primary condition is different, but varchar and varchar2 cannot distinguish ◎ Create a buffer Create or Replace package body huang_pkgas procedure pkg_proc (p1 number: = 0, p2 varchar) is begin null; -dbms_output.put_line ('Hello Procedure'); END PKG_PROC;
Function pkg_func (f1 number: = 0, f2 varchar) Return Number is v_num number; begin v_num: = 20; dbms_output.put_line ('Hello function "; returnif;
END PKG_FUNC;
Begin g_num: = 200; end huang_pkg; ◎ Creating a trigger ◎ One table is up to 12 triggers ◎ Trigger can not be installed in transaction statement in Trigger ◎ In the trigger, there is no change in modification, so-called changes The table is a table with this Trigger, and does not have the primary key of the restricted table and the field of Unique. The so-called restriction form is a table with the integrity of the change form ********************************************************* ************************ ◎ PROC's pre-processing process proc my_proc.pc => my_prog.cgcc my_prog.c-lclntsh -l (Lib is located Path or use shell variable name) ◎ Can be referenced in the C language, the variable referenced in the SQL language is SQL variable ◎ The variables used by SQL in the Oracle version must be enclosed in a statement, but there is no need to declare this in version 8. Execute SQL Begin Decute Section ... // Related C Variables Execute SQL End Decuan Section ◎ To add a query to C language variables to add ":" such as: char v_name [20]; int i; short i_name // Indicator Variables EXEC SQL SECT NAME INTO: V_NAME INDICATOR: I_NAME FROM Students WHERE ID =: i ◎ Execute SQL Statement with EXEC SQL ◎ Indicator Variable Short Variable Name, determine whether the SQL variable is worthless depending on the indicator variable. Query ◎ Determined operation is successful, you can use the indicator variable ◎ You can use the array to loop insertion ◎ Give the data in the database to the string variable of the PROC, Oracle does not pay for the PROC variable, can use the equivalent method For example: char name [41]; Exec SQL VAR Name is String [41]
EXEC SQL CREATE TABLE TABLE: Name WHERE CID = ID;, Oracle will automatically make 0 when confer it to Name, otherwise it is necessary to be artificial ◎ internal data type can be used directly ORACLE, external data types are used in PROC ◎ You can view the data operation after viewing data operations through the SQL communication area: for example: gcc connd.c -lclntsh -l $ oracle_home / lib -i $ Oracle_Home / Precomp / Public ◎ You can use the for: ROW method to specify how many records insert, not all elements in the array, insert all elements in the table ◎ Cannot use SELECT in a static dynamic statement (excluding dynamic statements 3) ◎
{
◎ sys.dbms.execute () does not get up, such as: v_str: = 'select * from stu_tb01 where id = v_id'; sys.dbms_sql.execute (v_str); ◎ How to customize an exception? ◎ Why can't the defined procedure can't display the result, but execution of success ◎ What is how to perform C Proc in SQLLIB ◎ Proc
}
