1 create or replace procedure procedure_name --Stored procedure name 2 ( 3 --Input / output quantities _name in out quantity type 4 --e.g. 5 username in varchar2, --varchar2Type does not need to indicate length. 6 id out number(38) 7 ) 8 is/as 9 --Basically, is and as are the same, but slightly different, in this section you declare variables and constants // like the declare section in the PL / SQL statement block 10 11 --Variable / constant _name (constant) 1. variable / constant type; 12 -- 2.table_name.col_name%type //This variable follows the type of this column in this table. 13 -- 3._name //A variable needs to be imported from outside, & **_name as a placeholder. 14 -- 4.Conceptual understanding of custom types / / objects 15 -- 5.table_name%rowtype 16 17 --e.g. 18 --1 19 i constant number:=5; 20 username varchar2(40); 21 --2 22 username tb_user.username%type; 23 --3 24 username varchar2:=&name; 25 --4 26 type type_name is record 27 ( 28 id number(38); 29 username varchar2(40); 30 ); 31 type_obj type_name; --(When using: select tb. id, tb. username into type_obj from tb_user TB 32 --5 33 users tb_user%rowtype; --(When the value of a row is put in, use: select * into users from tb_user where id='1'; 34 35 36 --Declare the cursor to get the result set of the query statement (multiple rows and columns); do not use the into clause cursor cur_name is sel in the // cursor declarationEct col_name, col_name, from table_name; 37 --e.g. 38 cursor temCur is select * from tb_user; 39 40 begin 41 --Place SQL statements and pl/sql statement blocks. 42 43 --assignment 44 a:=b; 45 46 --Output statement 47 dbms_output.put_line(''||''||''); --If you need to display the result, set serveroutput on 48 49 --sqlSentence 50 --increase 51 insert into table_name(col_name,col_name,,) 52 values(val_val,val_val,,); 53 54 --change 55 update table_name set col_name = val_val; 56 57 --Delete 58 delete from table_name …… 59 60 --cursor 61 --1.Explicit Cursor 62 --2.Implicit cursor select * into EMP from table_name... 63 64 65 --open 66 --open cur_name; 67 --Assigns the value in the cursor to a value. 68 --fetch cur_name into get_name,get_name,,; 69 --Close the cursor 70 --close cur_name; 71 72 --Cursor status information 73 --%isoipen //boolean 74 --%notfound // 75 --%found // 76 --%rowcount //So far, the total number of banks 77 78 select col_name into temp_name from table_name --Extract the values in the table (values of one row and one column) to the temporary variable. 79 80 --loop 81 --1. 82 loop 83 *** 84 exit when *** 85 end loop; 86 87 --2. 88 while *** 89 loop 90 *** 91 end loop; 92 93 --3. 94 for index in[reverse] *** 95 loop 96 *** 97 end loop; 98 99 --judge 100 if *** then *** 101 elsif *** then *** 102 else *** 103 end if; 104 105 --Submission of transactions 106 commit; 107 108 exception 109 --//exception handling 110 when too_many_rows then 111 *** 112 when no_data_found then 113 *** 114 *when others then 115 *** 116 --rollback; 117 --commit 118 end procedure_name; 119 120 121 122 --Call stored procedure 123 exec pro_name(); 124 125 126 127 128 129 130 131 132 133 /* 134 --Using cursor to get cursor data135 declare 136 -- Define EMP type137 emp employees%rowtype; 138 -- Define a cursor: get all employees.139 cursor my_corsor is select * from employees; 140 begin 141 -- open142 open my_corsor; 143 --Loop starts printing cursors144 loop 145 -- Move the cursor and get the corresponding data.146 fetch my_corsor into emp; 147 -- If there is no corresponding data, leave.148 exit when my_corsor%notfound; 149 -- Without leaving representatives, data can be printed and displayed.150 dbms_output.put_line(emp.first_name||' '|| emp.salary); 151 end loop; 152 --Close the cursor153 close my_corsor; 154 end; 155 156 157 --For cycle using cursors158 PL/SQLThe language provides the for loop statement of the cursor.159 Automatically execute cursors of open, fetch and close.160 When entering the loop, the cursor for loop statement opens the cursor automatically and extracts the first row of data.161 When the program processes the current data, the next row data is automatically extracted.162 When the content of the result set is extracted, the cursor is automatically closed.163 164 Format:165 FOR variables IN cursor_name(value,value...) LOOP 166 --Processing statement167 END LOOP; 168 169 declare 170 171 --Define a cursor: get all employees.172 cursor my_corsor is select * from employees; 173 begin 174 --Loop starts printing cursors175 for emp in my_corsor loop 176 -- Without leaving representatives, data can be printed and displayed.177 dbms_output.put_line(emp.first_name||' '|| emp.salary); 178 end loop; 179 end; 180 181 182 183 */