? Query? ???? SELECT statement is used to query data from the database. When using the SELECT statement in PL / SQL, use the INTO clause to use, the return value of the query is given the variable in the INTO clause, the variable The statement is in Delcare. SELECT ????????????? INTO syntax is as follows: ????? SELECT? [DISTICT | All] {* | Column [, column, ...]}? ????? INTO? (Variable [, variable, ...]? | Record) ?????? from? {Table | (sub-query)} [alias] ?????? Where ....... ..... ????? PL / SQL SELECT statement only returns a line of data. If you exceed a row of data, then use explicit cursors (discussions to the cursor will be done later), the INTO clause must have the same column number in the SELECT clause. The INTO clause can also be a record variable. ?% TYPE attribute? ????? In PL / SQL, the variables and constants can be declared as built-in or user-defined data types to reference a column name while inheriting his data type and size. This dynamic assignment method is very useful, such as the data type and size of the columns referenced by the variable, if the% TYPE is used, then the user does not have to modify the code, otherwise the code must be modified. Example:? ?? v_empno? Scott.emp.empno% type ;? ?? v_salary? Emp.salary% TYPE;??? Not only column names can use% TYPE, but also variables, cursors, records, or declarative constants You can use% Type. This is very useful for variables that define the same data type. ? ???? DELCARE? ???? v_a? Number (5): = 10;? ???? v_b? V_a% type: = 15; ???? v_c? V_a% type ;???? Begin ??????? dbms_output.put_line? ?????? ('v_a =' || v_a || 'v_b =' || v_b || 'v_c =' || v_c);?? ?? End ???? ???? SQL> /? ???? v_a = 10? V_b = 15? V_c =? ????? pl / sql? Procedure? SuccessFully? Completed. ???? ?? SQL>? ???? Other DML statements? ???? Other operational data DML statements are: INSERT, UPDATE, DELETE, and LOCK? TABLE, these statements in the syntax in PL / SQL and in SQL The syntax is the same. We have discussed the use of DML statements in front of the DML statement. In the DML statement, any variables declared in the DECLARE section can be used if it is a nest block, then pay attention to the scope of the variable.
Example:? ?? Create? Or? Replace? Procedure? Fire_employee? (Pempno? In? Number)?? As? ???? v_ename? Emp.ename% TYPE; ???? begin???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? SELECT? ENAME? INTO? V_ENAME? ?????? from? Emp ?????? WHERE? Empno = p_empno ;?????? INSERT? INTO? FORMER_EMP (EMPNO, ENAME)?? ????? Values? (P_empno, v_ename);? ?????? delete? From? EMP ??????? WHERE? Empno = p_empno ;?????? Update? FORMER_EMP?? ????? set? Date_deleted = sysdate? ?????? WHERE? Empno = p_empno ;? ?????? ???? EXCEPTION? ???????When? No_data_found? Then?? ?????? DBMS_OUTPUT.PUT_LINE ('Employee? Number? NOT? Found!'); ???? End? DML statement results? ???? When an DML statement is executed, the result of the DML statement is saved. In four cursor properties, these properties are used to control the program flow or the status of the program. When running the DML statement, PL / SQL opens an internal cursor and processes the result. The cursor is a region in the memory of the results of the query. The cursor is turned on when the DML statement is running, and then shuts down after completion. Implicit cursor only uses SQL% FOUND, SQL% NOTFOUND, SQL% ROWCOUNT three properties. SQL% FOUND, SQL% notfound is a Boolean value, SQL% ROWCOUNT is an integer value. • SQL% Found and SQL% NotFound? ???? SQL% FOUND and SQL% notfound are NULL before performing any DML statement, after executing the DML statement, the attribute value of SQL% Found will be:?. TRUE?: INSERT?.? True?: Delete and update, at least one line is delete or update.?.? True?: Select? INTO returns at least one line? When SQL% FOUND is TRUE, SQL% Notfound is False. • SQL% ROWCOUNT??? Before performing any DML statement, SQL% ROWCOUNT is NULL, for SELECT ???????????? INTO statement, if the execution is successful, SQL% ROWCOUNT The value is 1, if there is no success, the value of SQL% ROWCOUNT is 0, and generates an exception NO_DATA_FOUND.? SQL% isopen ?? SQL% isopen is a Boolean value, if the cursor is turned on, I is True, if the cursor is closed, It is false. For implicit cursors, SQL% isopen is always false because the implicit cursor is turned on when the DML statement is executed, and immediately shuts down. • Transaction control statement? ???? Transaction is a logical unit that can include one or more DML statements, and things control help users guarantee data consistency. If any of the DML statements in the transaction control logic unit fails, the entire transaction will roll back, and users in PL / SQL can explicitly use COMMIT, ROLLBACK, SAVEPOINT, and SET? Transaction statements.
? ????? Commit statement termination, permanently saving the database change, simultaneously releases all Lock, Rollback terminates the current transaction release all Lock, but does not save any changes in the database, savepoint is used to set the intermediate point, when transaction is too much When the database operation, the intermediate point is very useful, set? Transaction is used to set transaction properties, such as Read-Write and Isolation. ? Explicit cursor? ???? When the query return result exceeds one line, you need an explicit cursor. At this time, the user cannot use the SELECT? INTO statement. PL / SQL manages implicit cursor, when the query starts, the implicit cursor is opened, and the implicit cursor is automatically turned off. The explicit cursor declares in the declaration of the PL / SQL block, and the data is opened, the data is turned off in the execution section or the abnormality processing portion. • Use a cursor? ???? Here to make a statement, the cursor we say is usually referring to the strange cursor, so we have no special indication from now, and the cursors we say refers to the strange cursor. To use a cursor in the program, you must first declare the cursor. Declaring Cursor? Syntax:? ???? Cursor? Cursor_name? Is? Select_statement ;? The marked name in the PL / SQL is an undeclared variable and cannot assign a values or in the expression. ??? ???? delcare? ???? cursor? C_emp? Is? SELECT? Empno, ename, Salary? ???? from? EMP ?????? SALARY> 2000? ???? ORDER? BY? ENAME;? ???? ........ ???? begin ???? In the cursor definition, the SELECT statement is not necessarily a table can be a view, or you can Columns selected in a table or view, can even use * to select all columns? ???? Open the cursor? You should first open the cursor before using the value in the cursor, open the cursor initialization query processing. Open the syntax of the cursor is:? ???? Open? Cursor_name ???????? Cursor_name is a cursor name defined in the declaration part. ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????.? ??????????? CLOSE? C_EMP; • Extract data from the cursor? ???? From the cursor to get a row of data using the fetch command. After each extraction data, the cursor points to the next line of the result set. The syntax is as follows: ????? fetch? Cursor_name? INTO? VARIABLE [, variable, ...]? ????? For each column of the cursor defined by SELECT, the FETCH variable list should have a variable and Correspondingly, the type of variable is also the same.
??? ??? set? Serveriutput? On? ??? Declare ?????? v_ename? Emp.ename% type ;? ????? v_salary? Emp.salary% TYPE;??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? CURSOR? C_EMP? IS? SELECT? ENAME, SALARY? From? EMP;? ????? begin ??????? Open? C_emp;? ?????????? fetch? C_emp ? INTO? V_ENAME, V_SALARY;? ???????????? DBMS_OUTPUT.PUT_LINE ('Salary? Employee' ||? V_ename? || 'Is' ||? V_salary; ?? ?? ???????? fetch? C_emp? INTO? V_ENAME, V_SALARY; ????????????? dbms_output.put_line ('Salary? Employee' ||? V_ename? || Is '||? v_salary; ??????????? fetch? c_emp? INTO? V_ENAME, V_SALARY; ???????????? dbms_output.put_line (' Salary? of • Employee '||? V_ename? ||' Is' ||? V_salary; ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????? The code is undoubtedly very troublesome. If there is a multi-line return result, you can use the cycle and use the cursor properties to end the cycle, in this way, the readability and simpleness of the program are greatly improved, and we use the cycle below Re-write the program:? Set? Serveriutput? On? Declare? V_ename? Emp.ename% type ;? v_salary? Emp.salary% type ;? cursor? C_emp? Is? SELECT? ENAME, SALARY? From? EMP;? Begin ?? Open? C_emp;? ???? loop ?????? fetch? C_emp? INTO? V_ENAME, V_SALY; ?????? EXIT? WHEN? C_EMP% NotFound;???? ??? dbms_output.put_line ('Salary? Of? Employee' ||? V_e Name? || 'Is' ||? v_salary) ;? END? Record variable? ???? Define a record variable using type command and% rowType, please refer to relevant information about more information about% ROWSTYPE. ???? Record variables are used to extract data lines from the cursor. When the cursor chooses a lot of columns, then the record ratio is much easier to declare a variable for each column. • When using% ROWTYPE in the table and puts the value removed from the cursor into the record, if you want to select all columns in the table, use * in the SELECT clause to list all the column names More safe.
Example:? Set? Serveriutput? On? Declare? R_emp? Emp% rowType ;? Cursor? C_emp? Is? SELECT? *? From? Emp ;? begin ?? open? C_emp;??? Loop?????? ??? fetch? C_emp? INTO? R_EMP; ????? EXIT? WHEN? C_EMP% NotFound;? ????? dbms_out.put.put_line ('Salary? Employee' || r_emp.ename | | 'Is' ||? r_emp.salary); ??? End? loop ;? ?? CLOSE? C_EMP;? End ;?% RowType can also be defined by the trump name, such words must first declare the cursor: • set? Serveriutput? On? Declare? Cursor? C_emp? Is? Select? EName, Salary? From? Emp ;? r_emp? C_emp% rowType ;? begin?? C_emp;?? Loop????? Fetch ? c_emp? INTO? R_EMP;? ???? EXIT? WHEN? C_EMP% notfound ;? ???? dbms_out.put.put_line ('Salary? Employee' || r_emp.ename || 'Is' || ? r_emp.salary; ?? End? loop ;?? close? c_emp ;? End;? Trusted cursor with parameters? ???? Similar to the storage process and function, you can pass parameters to the cursor and use it in the query. . This is very useful for handling the situation of opening a cursor under certain conditions. Its syntax is as follows:? Cursor? Cursor_name [(parameter [, parameter], ...)]? Is? SELECT_STATEMENT;? Define the syntax of the parameter as follows:???? Parameter_name? [In]? Data_Type [{: = | Default}? Value]? Different from the stored procedure, the cursor can only accept the value passed without returning the value. The parameters only define the data type, no size. ??? You can also set a default value to the parameter. When there is no parameter value to pass to the cursor, the default value is used. The parameters defined in the cursor are just a placeholder, and this parameter is not necessarily reliable elsewhere. • Assign the value to the parameter when the cursor is opened, the syntax is as follows:? Open? Cursor_name [value [, value] ....] ;? The parameter value can be a text or variable.
? DECALRE? CURSOR? C_DEPT? IS? SELECT? *? From? Dept? Order? By? Deptno ;? cursor? C_emp? (P_dept? Varharachar2)? Is?? SELECT? ENAME, SALARY??? From ? EmpTNO = P_DEPT? ?? Order? BY? ENAME? R_DEPT? DEPT% ROETYPE;? V_ENAME? Emp.Name% TYPE;? V_SALARY? Emp.salary% TYPE;? V_tot_salary? Emp.salary% Type ;? begin ?? open? C_dept;? ????? loop? ???????? fetch? C_dept? INTO? R_DEPT;? ???????? EXIT? WHEN? C_DEPT% NOTFOUND;? ???????? dbms_output.put_line ('Department:' ||? R_dept.deptno || "- '|| r_dept.dname);? ???????? v_tot_salary: = 0 ;? ???????? Open? C_emp (r_dept.deptno);? ???????????? loop ??????????????? fetch ? c_emp? INTO? V_ENAME, V_SALARY;? ??????????????? EXIT? WHEN? C_EMP% notfound;? ??????????????? dbms_output .Put_line ('name:' ||? V_ename || '? Salary:' || v_salary; ??????????????? v_tot_salary: = v_tot_salary v_salary;???? ????????? end? Loop; ???????? close? C_emp;? ??????? dbms_output.put_line ('Toltal? Salary? For? Dept:' | |? v_tot_salary; ????? End? loop ;? ?? close? c_dept ;? End ;? Cursor for loop? In most, we follow the steps below when designing program:? 1, open Cursor? 2, start cycle? 3, take the value from the cursor? 4, check That line is returned? 5, processing? 6, turn off the loop? 7, close the cursor? However, there is still a loop that is different from this type, which is for loop. The cursor for the for loop is declared in accordance with normal declarations. It has the advantage that there is no need to explicitly open, close, taking data, and test data. , Define variables that store data, and so on.
The syntax for the cursor for loop is as follows:? For? Record_name? In? (Corsor_name [(parameter [, parameter] ...)?? |? (Query_difinition)? Loop ?? STATEments? END? Loop;? Below Use for loop to rewrite the above example:? DECALRE? CURSOR? C_DEPT? IS? SELECT? DEPTNO, DNAME? From? Dept nORDER? BY? Deptno ;? cursor? C_emp? (P_dept? Varhachar2)? Is?? SELECT? ENAME, SALARY? From? Emp?? WHERE? DEPTNO = P_DEPT? ORDER? BY? ENAME? V_TOT_SALARY? Emp.salary% type ;? begin ??? for? r_dept? IN? c_dept? loop? ??? ?? dbms_output.put_line ('Department:' ||? R_dept.deptno || '-' || r_dept.dname);? ????? v_tot_salary: = 0;? ????? for? R_emp? In ? c_emp (r_dept.deptno)? loop? ???? dbms_output.put_line ('name:'? ||? V_ename? ||? 'Salry:'? ||? v_salary; ?? ???? v_tot_salary: = v_tot_salary v_salary; ????? End? loop;? ????? dbms_output.put_line ('Toltal? Salry? for? dept:' ||? v_tot_salary); ?? End? loop ;? end In the cursor for loop, use the query in the cursor for loop, because there is no explicit declaration, the cursor does not have a name, the record name is defined by the cursor query.? DECALRE? V_TOT_SALARY? Emp.salary % Type ;? begin ?? for? R_dept? IN? (SELECT? Deptno, DNAME? From? Dept? Order? BY? DEPTNO)? Loop? ????? dbms_output.put_line ('Department:' ||? R_DEP T.DEPTNO || '-' || r_dept.dname);? ????? v_tot_salary: = 0;? ????? for? r_emp? in? (SELECT? ENAME, SALARY? ????? ??????? from? EMP ????????????? a? Deptno = p_dept? ???????????? Order? BY? ENAME)? LOOP? ??????? dbms_output.put_line ('name:' ||? V_ename || '? Salary:' || v_salary);? ??????? v_tot_salary: = v_tot_salary v_salary;??? ?? End? Loop; ?? dbms_output.put_line ('Toltal? Salary? For? Dept:' ||? V_tot_salary); ?? End? Loop ;? End ;? The child in the cursor??????????????????????????????????????????????????????????????????? The syntax is as follows: ???? Cursor? C1? Is? SELECT? *? From? Emp??? Where? Deptno? Not? IN? (SELECT? DEPTNO? ?? from? Dept? ??? WHERE? DNAME ! = 'Accounting') ;? It can be seen that there is no difference between subqueries in SQL.
