Base on PLSQL Programming

PL/SQL programming
Overview: it is an extension of Oracle in the standard SQL language. It supports not only embedding SQL languages, but also customizing constants and variables.
Conditional statements, loop statements, logical control statements, and other statements can be used to allow exceptions to handle various errors. This makes its functions more powerful.
Advantages:1) improve the running performance of the application-saving the time for compiling the SQL statements sent by the Java program in the database
2) Modular Design Thought ---------- for example, paging Stored Procedure module, order processing Stored Procedure module, transfer Stored Procedure module, etc.
3) Reduced network transmission volume ---------- you don't have to use Java programs to send so many SQL statements.
4) improve security --------------- directly calling the stored procedure name in a Java program, eliminating the risk of exposing SQL
Disadvantages: Poor portability -------------------- when porting data to other databases, it may even need to be rewritten.
Specifications:We recommend that you use V _ as the prefix when defining variables, such as v_sal.
We recommend that you use C _ as the prefix when defining constants, such as c_rate.
When defining exceptions, we recommend that you use E _ as the prefix, such as e_error.
We recommend that you use the _ cursor suffix when defining a cursor, for example, emp_cursor.
Classification: it is mainly block programming, which can be divided into process, function, trigger, package, etc.

-- Write a stored procedure, which can add the record create table student (name varchar2 (10), password varchar2 (30) to a table )); create or replace procedure mypro is -- replace indicates that if a stored procedure named mypro already exists, replace it with begininsert into student values ('zhang qiling ', 'zhang22 '); -- execute partial end; Exec mypro; -- call the Stored Procedure named mypro. The format is exec procedure_name (param1, param2,...) Call mypro; -- you can also use the call command to call

Block
Summary: blocks are the basic program units of PL/SQL. Compiling PL/SQL programs is actually writing PL/SQL blocks.
To complete a relatively simple function, you may only need to write a PL/SQL block. However, to implement complex functions, other PL/SQL blocks may need to be nested in one PL/SQL block.
Structure: PL/SQL blocks are composed of three parts,Definition, execution, and Exception Handling
Declare/* Define part ------- this part is optional. Define constants, variables, exceptions, cursors, and complex data types */
Begin/* Execution part ------- this part is required. PL/SQL statements and SQL statements to be executed */
Exception/* Exception Handling part-this part is optional. Handle various running errors */
End;

Protocol 1) only PL/SQL block including execution part set serveroutput on -- open the output option -- dbms_output is a package provided by Oracle. Similar to Java Development Kit, this package contains some processes, put_line is a process in this package. begindbms_output.put_line ('My name is jadyer '); -- output the my name is jadyer string end on the console; else ;------------------------------------------------------------------------------------------------ ------------------- 2) the PL/SQL block declarev_ename varchar2 (5); -- defines the string variable v_sal number (); begin -- into indicates the information to be queried, put it in the v_ename variable. Note that the order of ename, Sal, v_ename, and v_sal is the matching select ename, Sal into v_ename, v_sal from EMP where empno = & No; -- & indicates the variable dbms_output.put_line to be input from the console ('employee name: '| v_ename | 'salary:' | v_sal); end; Terminal 3) PL/SQL block including definition part, execution part, and Exception Handling part declarev_ename varchar2 (5); v_sal number (7, 2); beginselect ename, Sal into v_ename, v_sal F Rom EMP where empno = & No; dbms_output.put_line ('employee name: '| v_ename | 'salary:' | v_sal ); exception -- Exception Handling when no_data_found then -- Oracle predefines some exceptions. Specifically, no_data_found indicates that the exception dbms_output.put_line ('your employee ID does not exist. '); End; begin ;-----------------------------------------------------------------------------------------------------------------

Stored Procedure
Overview: used to perform specific operations. You can specify either an input parameter or an output parameter
You can transmit data to the execution part by using input parameters during the process;You can use output parameters to transfer the executed data to the application environment.

-- Modify the salary of the specified user. Create procedure emp_pro (currname varchar2, newsal number) is -- similar to the parameter when Java defines the method, therefore, you cannot specify the length of the parameter type beginupdate EMP set sal = newsal where ename = currname; end; -- call the stored procedure. The effect is to change Scott's salary to 23456 exec emp_pro ('Scott ', 23456); -- call the Stored Procedure callablestatement cstmt = Java in a Java program. SQL. connection. preparecall ("{call emp_pro (?,?)} "); Cstmt. setstring (1," Scott "); cstmt. setint (2, 2345620.20.cstmt.exe cute ();

Function
Overview: Used to return specific data. When creating a function,The return clause must be included in the function header,The function body must contain the data returned by the Return Statement.

-- Returns the annual salary of the specified employee. create function emp_fun (currname varchar2) return number is yearsal number (); beginselect Sal * 12 + nvl (Comm, 0) * 12 into yearsal from EMP where ename = currname; return yearsal; end; -- when calling a function in sqlplus, the following three steps are required: var nianxin number; call emp_fun ('Scott ') into: nianxin; print nianxin; Java> // call this function in a Java program, and then use Rs. getint (1) returns the returned result. Java> select emp_fun ('Scott ') from dual;

Package
Overview: it is used to logically combine processes and functions. It consists of the package specification and package body.
The specification of the package only contains the description of the process and function, but there is no implementation code of the process and function.The package body is used to implement the process and functions in the package specification.

-- 1) use the create package command to create the package create or replace package emp_pack isprocedure emp_pro (currname varchar2, newsal number); -- declare that the package contains a process function emp_fun (currname varchar2) return number; -- declare that this package has an end function; -- 2) use the create package body command to create the package body: Create package body emp_pack isprocedure emp_pro (currname varchar2, newsal number) isbeginupdate EMP set sal = newsal where ename = currname; end; function emp_fun (currname varchar2) return number is yearsal number; beginselect Sal * 12 + nvl (Comm, 0) * 12 into yearsal from EMP where ename = currname; return yearsal; end; -- 3) when calling a package process or function, the package name must be included before the process and function; to access packages of other solutions, add the solution name call emp_pack.emp_pro ('Scott ', 400800) before the package name );

Trigger
Overview: triggers are stored in databases and implicitly executed. A trigger consists of trigger events, trigger conditions, and trigger operations.
Category: DML triggers, DDL triggers, and system triggers (the last two triggers are usually created by the system administrator, namely, Conn system/xxx As sysdba)

-- Manage the trigger (Use System logon) alter trigger trigger_name disable; -- disable the trigger (temporarily enable the trigger) alter trigger trigger_name enable; -- activate the trigger alter table table_name disable all triggers; -- disable all triggers of the table alter table table_name enable all triggers; -- enable all triggers of the table drop trigger trigger_name; -- delete a trigger

-- Create [or replace] trigger trigger_name {before | after} {insert | Delete | update [of column [, column...]} on [schema.] table_name [for each row] -- represents a row-Level Trigger. If no such trigger exists, it represents a table-Level Trigger [When condition] -- representing the triggering condition begintrigger_body; end; -- Example -- 1) when a data entry is added to the table, the system prompts "create or replace trigger trigger_blog after insert on Scott. blogbegindbms_output.put_line ('added a piece of data '); end; -- 2) when multiple data entries are modified in the table, the system prompts "data modified" create or replace trigger trigger_blog after update on Scott. blog for each rowbegindbms_output.put_line ('Modified data'); end; -- 3) It is forbidden to modify table data on the Rest Day. developers can create before statement triggers, to ensure data security, create or replace trigger trigger_blog before insert or update or delete on Scott. blogbeginif to_char (sysdate, 'day') in ('saturday', 'sunday') then -- dbms_output.put_line ('data cannot be operated on a day'); -- this will only prompt, this operation cannot be prevented -- raise_application_error () is a process provided by Oracle. As long as PLSQL encounters it, PLSQL stops executing -- proceduer raise_application_error (error_number_in number, error_msg_in varchar2) -- error_number_in is between-200000 and-20999, so that it will not conflict with any error code in Oracle -- and the length of error_msg_in cannot exceed 2000, otherwise, Oracle automatically intercepts the first 2000 characters, raise_application_error (-20001, 'Data cannot be operated on the day'); end if; end; -- 4) to distinguish multiple trigger events contained in a trigger, three conditions can be used: inserting, updating, deletingcreate or replace trigger trigger_blog before insert or update or delete on Scott. blogbeginif to_char (sysdate, 'day') in ('saturday', 'sunday') thencasewhen inserting then raise_application_error (-20002, 'Do not add data on the day '); when updating then raise_application_error (-20003, 'Do not modify data on Day'); When deleting then raise_application_error (-20004, 'Do not delete data on Day'); End case; end if; end; -- 5) when modifying an employee's salary, make sure that the employee's salary cannot be lower than or equal to 20% of the original salary, the pre-and post-modified salary values are displayed: Create or replace trigger trigger_blog Before update on Scott. blog for each rowbegin -- since our trigger is for the EMP table, PLSQL knows that the Sal here is the field of the blog table --': the new 'modifier is used for the value of the column after the access operation is completed, and the': old' modifier is used for the value if (: New. sal <: old sal = "" Or = "": New = "">: Old. sal * 1.2) thenraise_application_error (-20005, 'Modified salary cannot be lower than or higher than the original salary 20% '); elsedbms_output.put_line ('original salary:' |: old. sal | 'current salary: '|: New. sal); end if; end; -- 6) When deleting a table record, the deleted record is automatically backed up to create or replace trigger trigger_blog before delete on Scott. blog for each rowbegininsert into blog_bak values (: Old. ID,: Old. name,: Old. sal); end;
 

-- DDL trigger create [or replace] trigger trigger_nameafter DDL on solution name. schema -- Here '. scheme' is a fixed writing method, such as Scott. schemabegintrigger_body; end; -- record a user's DDL operation Create Table log_ddl (uname varchar2 (20), ddl_event varchar2 (20), ddl_time date ); create or replace trigger trigger_ddl after DDL on Scott. schemabegininsert into log_ddl values (ora_login_user, ora_sysevent, sysdate); end;

-- 7) A system trigger is a trigger created based on Oracle events (such as logon and startup). When creating a system trigger, you must use the event attribute function and common event attribute functions, the following -- ora_client_ip_address -- returns the Client IP address (the IP address returned on Windows may be blank) -- ora_database_name -- returns the database name -- ora_login_user -- returns the logon username -- ora_sysevent -- returns the system event name of the trigger -- ora_des_encrypted_password -- returns the password encrypted by des -- the basic syntax of the system trigger. [or replace] trigger trigger_nameafter [before] logon [logoff] on database -- fixed syntax, this does not have the for each row attribute, because it is the begintrigger_body; end; -- Example create table log_sysevent (uname varchar2 (20), logon_time date, logoff_time date, IP varchar2 (20); -- logon trigger create or replace trigger trigger_logon after logon on database -- Record begininsert into log_sysevent (uname, logon_time, ip) values (ora_login_user, sysdate, begin); end; -- exit trigger create or replace trigger trigger_logoff before logoff on database -- Record begininsert into log_sysevent (uname, logoff_time, ip) values (ora_login_user, sysdate, region) before exiting ); end;

Define and use variables
Classification: variables and constants can be defined when compiling PL/SQL programs. PL/SQL programs include the following four common types:
1) scalar)
2) Composite)
3) Reference)
4) lob (large object)
Scalar Syntax: the syntax for defining variables and constants in PL/SQL is as follows:
Identifier [constant] datatype [not null] [: = | default expr]
Identifier ----- name
Constant ----- specifies a constant. You need to specify its initial value, and its value cannot be changed.
Datatype ----- Data Type
Not null ------ the specified variable cannot be null.
: = ------------- Specify the initial value for the variable or constant
Default ------- used to specify the Initial Value
Expr ---------- specify the initial PL/SQL expression, which can be a text value, other variables, functions, etc.

-- Scalar case v_ename varchar2 (10); -- defines a variable-length string v_sal number (6, 2); -- defines a decimal number in the range of-9999.99 ~~ 9999.99v _ Sal number (6, 2): = 5.4 -- Define a decimal number and specify the initial value as 5.4v _ hiredate date; -- Define a date data v_valid Boolean not null default false; -- defines a Boolean variable, which cannot be empty and its initial value is false -- scalar usage -- note that PL/SQL blocks assign values to variables different from other programming languages, add a colon (: =) before the equal sign. Enter the employee number below to display the employee name, salary, and personal income tax (the tax rate is 0.03). For example, declarev_ename varchar2 (5 ); v_sal number (0.03); c_tax_rate number (): =; v_tax_sal number (); beginselect ename, Sal into v_ename, v_sal from EMP where empno = & No; v_t Ax_sal: = v_sal * c_tax_rate; -- calculate the income tax. In PL/SQL, dbms_output.put_line ('name: '| v_ename |' salary: '| v_sal | 'tax:' | v_tax_sal); end; -- if the employee name exceeds 5 characters, an error occurs. To reduce the maintenance workload of PL/SQL programs, you can use the (% Type) attribute to define the variable-so that it determines the variable type and length according to the database column, format: (identifier table name. column name % type), v_ename EMP. ename % type;

-- Composite -- a variable used to store multiple values, including PL/SQL records, PL/SQL tables, nested tables, and dynamic arrays) and so on -- PL/SQL record of the composite type -- similar to the struct in the advanced language -- Note: When referencing PL/SQL record Members, the record variable must be prefixed, that is, (record variable. record member) declare -- defines a PL/SQL record type. The type name is emp_record_type, which contains three data types: name, salary, titletype emp_record_type is record (currname EMP. ename % type, salary EMP. sal % type, title EMP. job % type); -- defines a variable named my_record. The variable type is emp_record_typemy_record emp_record_type; beginselect ename, Sal, job into my_record from EMP where empno = 7788; -- this variable my_record can receive three data items: dbms_output.put_line ('employee name: '| my_record.currname | 'salary:' | my_record.salary); end; -- PL/SQL tables of the composite type -- equivalent to arrays in advanced languages -- Note: In advanced languages, the array subscript cannot be negative, while PL/SQL can be negative, the subscript of the table element does not limit declare -- defines a PL/SQL table type. The type name is my_table_type, which is used to store EMP. array of the ename % Type -- index by binary_integer indicates that the subscript of this array is sorted by integer, so its subscript can be negative, because the negative integer is also an integer type my_table_type is table of EMP. ename % Type Index by binary_integer; -- defines a variable whose name is my_table and whose type is my_table_type -- PL/SQL always writes the variable name before it, variable types are written in my_table my_table_type; beginselect ename into my_table (0) from EMP where empno = 7788; dbms_output.put_line ('employee name: '| my_table (0); end;

-- Reference variable -- the variable used to store the value pointer. By using reference variables, applications can share the same object to reduce the occupied space. When compiling PL/SQL programs, you can use the cursor variable (ref cursor) and object type variable (ref obj_type) two types of reference variables-reference variable cursor variable-when defining the cursor, you do not need to specify the corresponding SELECT statement-but when using the cursor (open) you need to specify the SELECT statement, so that a cursor is combined with a select statement-use PL/SQL to write a block and enter the Department number, declaretype my_emp_cursor is ref cursor; -- defines a cursor type test_cursor my_emp_cursor; -- defines a cursor variable whose type is my_emp_cursorv_ename EMP. ename % type; -- defines the variable, used to receive the ename value v_sal EMP. sal % type; beginop En test_cursor for select ename, Sal from EMP where deptno = & No; -- combine test_cursor with a select loop -- use (loop... end loop) cyclically fetch the Data fetch test_cursor into v_ename, v_sal; -- use fetch to retrieve the data pointed to by the test_cursor cursor and put it in the variable exit when test_cursor % notfound; -- judge whether test_cursor is empty. If it is empty, exit the loop dbms_output.put_line ('employee name: '| v_ename | 'salary:' | v_sal); End loop; end;