Oracle%type and%rowtype small instances __oracle

%type//If the declared variable is mapped directly to a column in the database, you can use the%type keyword to set the variable//anchor to this column. What is the benefit of doing so? For example://declare v_ename scott.emp.ename%type; This method is very flexible when the data type is changed. If you change the length of a column, all the variables that are anchored to the column automatically change their length;//Suppose we define V_ename as VARCHAR2 (10), so when the ename column in the EMP table changes,//We have to manually v_ Enam changed to emp.ename the same length of data;//When we use the anchor type, the variable is automatically adjusted. %rowtype//%rowtype is similar to%type, but it anchors the variable to all columns of the table, rather than anchoring it to a column;//More about%rowtype and%type,//Please refer to: http://blog.csdn.net/ Bobo12082119/archive/2010/12/02/6051367.aspx//Below is an example: CREATE TABLE Dept (deptno VARCHAR2 (5), Dname varchar2 (), loc VARCHAR2 (20)); Create or Replace procedure Pro_insert (deptno_in in Dept.deptno%type, dname_in in Dept.dname%type, loc_in in Dept.loc%typ e) as V_dept Dept%rowtype; Begin begin INSERT INTO dept select Deptno_in,dname_in,loc_in from dual; Commit Dbms_output.put_line (' inserting successed '); Exception when others then rollback; End Begin select Deptno_in,dname_in,loc_in into v_dept from dual; Dbms_output.put_line (' The data has been inserted. ' | | ' Deptno: ' | | v_dept.deptno| | ', DnamE: ' | | v_dept.dname| | ', loc: ' | | V_DEPT.LOC); End End Pro_insert; In the process above, a nested block is used;//the nested block is a block that contains other child blocks;//nested blocks are allowed to appear in the exception handling and execution parts of the code block,//but are not allowed to appear in the declaration. Sql> set serveroutput on; sql> exec Pro_insert (' 111 ', ' finance department ', ' Fuzhou '); Inserting successed The data having been inserted.deptno:111,dname: Finance Department, loc: Fuzhou Pl/sql procedure successfully completed// /From here,%rowtype defines a variable that is somewhat similar to a cursor,//But we cannot use the variable defined by%rowtype as a cursor,//Otherwise we will get the following error://ora-01422:exact fetch returns more than requested number of rows declare v_dept dept%rowtype; Begin select Deptno,dname,loc into V_dept from dept; Dbms_output.put_line (' The data has been inserted. ' | | ' Deptno: ' | | v_dept.deptno| | ', Dname: ' | | v_dept.dname| | ', loc: ' | | V_DEPT.LOC); End Here we use a cursor to implement the above operation, specifically, look at the following anonymous block: Declare CURSOR cv_dept is select * FROM dept; Begin//variable v_dept do not need us to show the declaration//for Loop will open and close the cursor implicitly for us, without our realistic open and close cursors for v_dept in Cv_dept loop Dbms_output.put_line (' Deptno: '|| v_dept.deptno| | ', Dname: ' | | v_dept.dname| | ', loc: ' | | V_DEPT.LOC); End Loop; End Deptno111,dname: Finance department, loc: Fuzhou deptno:120,dname: Sales department, loc: Dalian Deptno:130,dname: Research Department, LOC: Beijing Pl/sql Procedure successfully  

Original post: http://topic.csdn.net/u/20110426/22/e766deb4-7426-4f82-b433-485bf5713696.html?41002