1. PL/SQL Overview
1) ConceptPL/SQL (procedural language) is an extension of Oracle based on the standard SQL language, enabling you to define variables, use logical control statements, and so on: The default Oracle can only process one SQL statement at a time, Each SQL statement sends a request call to the server, and if you use PL + SQL, the statement in a block acts as a group, sending only one request call to the server, reducing network transmission, saving bandwidth, improving efficiency, and increasing security disadvantages: Poor migration, only available in Oracle
2) CompositionPL/SQL programs consist of blocks consisting of blocks that can form different program forms, including: Anonymous blocks, stored procedures, functions, packages, triggers, etc. 3) comments--Single-line comments/* Multiline comments */
4) NamingWhen defining variables, we recommend v_ as a prefix: v_name when defining constants, it is recommended to prefix C_: c_rate When defining a procedure, it is recommended to prefix p_ when defining a function, it is recommended to prefix fun_ when defining a package, it is recommended to use PAC_ as a prefix when defining a cursor, cur _ As a prefix when defining exceptions, it is recommended to prefix ERR_
2 Block
1) OverviewA block is a basic constituent unit of PL/SQL, and writing PL/SQL is actually a block consisting of three parts: the definition section, the execution section, the Exception handling section (Exception handling part), the definition section declare: Defining variables, constants, complex data types, cursors, exceptions, etc. Optional execution section begin: The SQL statement or the PL/SQL statement to execute, the mandatory exception handling section exception: used to handle various errors at run time, optional
The above is the general basis ........ Let's go
Example 1: PL/SQL block containing only the execution part--Open the output to enable the PL/SQL program to output information
set serveroutput on; (used in sqlplus to open the output method of Oracle's own dbms_output)
Begin Dbms_output.put_line (' Hello oracle! '); End Note: If using a tool is similar to a terminal tool such as sqlplus/concluding sentence;
Example 2: a PL/SQL block that contains a definition section and an execution partDefine variable Syntax: variable name [constant] data type [NOT NULL] assign a value to a variable: variable name: = Variable Value Note: Constants must be assigned an initial value when declaring
Note: Constants must be assigned an initial value (the method of assigning a value to a variable) when declaring
Example 3: a PL/SQL block containing a definition part and an execution part, using a query to assign a value to a variable (table data is a table from the Scott account in Oracle)DECLARE v_ename VARCHAR2 (20); V_empno number (4); Begin v_empno:=& Please enter the employee ID; Select Ename to V_ename from EMP where empno=v_empno; Dbms_output.put_line (v_empno| | ' 's name: ' | | V_ename); Note: The red part is prompted to enter the data, depending on the platform differences, the prompt way is different, the effect is similar to Java under the scanner prompt input.
Example 4: Output the name and salary of the specified employee at the same time (query multiple values)DECLARE v_ename VARCHAR2 (20); V_sal number (7,2); Begin select Ename,sal into V_ename,v_sal from EMP where empno=&no; Dbms_output.put_line (v_ename| | ', ' | | V_sal); end;
Note: Into can only be used to assign values by query, for additions and deletions, you need to use returning into assignment
Example 5: Inserting a department to display the inserted department informationDECLARE v_deptno number (4); V_dname varchar2 (20); V_loc varchar2 (+); Begin INSERT INTO Dept VALUES ("Development ', ' Nanjing ') returning deptno,dname,loc into V_deptno, V_dname,v_loc; Dbms_output.put_line (' No.: ' | | v_deptno| | ', Name: ' | | v_dname| | ', location: ' | | V_loc); end;
Gets the method that inserts the value.
Example 6: Block (Exception block handling) that contains the definition section, the execution section, and the Exception handling sectionDECLARE v_ename VARCHAR2 (20); V_sal number (7,2); Begin select Ename,sal into V_ename,v_sal from EMP where empno=&no; Dbms_output.put_line (v_ename| | ', ' | | V_sal); exception--Exception handling when No_data_found then Dbms_output.put_line (' The employee you are looking for does not exist! '); end;
No_data_found for the system error reason, according to the abnormal reason, output specific abnormal causes.
From for notes (Wiz)
PL/SQL Introductory understanding (i)
