PLSQL编程笔记（PLSQL programming notes）.doc

PLSQL编程笔记（PLSQL programming notes） / * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * the PL/SQL programming in * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * / / * procedural language/SQL * / 1. Procedures, functions, and triggers are written in pl/SQL The process, function, and trigger are in oracle -- 3, pl/SQL is a very powerful database process language Processes and functions can be invoked in Java programs - improve efficiency: optimize SQL statements or write stored procedures -- pl/SQL portability is not good -- IDE (Integration Develop Environment) integrated development Environment -- command rules: Variable (variable) v_ -- constant -- pointer, cursor (cursor) _cursor Exception, exception (exception) e_ - definable variables and constants: Scalar type: scalar Composite type: composite -- store records, tables, nested tables, varray Reference type: reference - a lob, object) / * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * PL/SQL basic grammar * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * / Example: create stored procedures Create or replace procedure pro_add is The begin Insert into mytest values ( Korean xx , 123); The end; The exec pro_add; - the call -- view the error message Show the error; -- invocation procedure The exec process (c1 and c2,...). ; Call process (c1 and c2,...). ; -- open/close the output option The set serveroutput on/off Input - Block structure diagram Declare -- defines the sections that define constants, variables, cursors, exceptions, and complex data types Begin -- execute part, execute pl/SQL statements and SQL statements Exception - exception handling part, handling running errors The end; End - -- demonstration of the example declare V_ival number (4) : = 100; Declare and initialize the variable - v_dtm date; V_dtm syslogs. DTM % type; -- take the table field type V_content varchar (512); The begin V_ival: = v_ival * 90; -- assignment Ins