Database PL/SQL script language function learning notes

Database PL/SQL script language function learning notes function build syntax rules: create [or replace] function function_name [(parameter,…)] Return datatype is variable declaration; begin statement ;... [Execption statement;…] The end; www.2cto.com function has only the IN mode parameter. The return value can be imported only from the calling environment, and the return type of the function can be returned to the calling environment. It cannot be the PL/SQL data type, such as table and record, but only the data types supported during oracle table creation. For example, DML, DDL, and other statement examples cannot appear in functions such as number and date. (1) -- Implement a function to concatenate two strings into one string, and return the create or replace function f_str (-- parameter type (note that the parameter has no length) www.2cto.com l_str1 varchar2, l_str2 varchar2) -- return type return varchar2 is l_result varchar2 (20); begin -- Method body l_result: = l_str1 | l_str2; return l_result; end; -- Test select f_str ('aaa', 'ggg ') from dual; -- Test select f_str (e. ename, e. job) from emp e; example (2) -- create A merged function Question 2: Table A data is as follows: FID Field1 1 A 1 B 1 C 2 D 2 E 2 F requirements are shown in the following format: FID Field1 1 A, B, C 2 D, E, F how to do it? (Wmsys. wm_concat) -- create table AB (fid number (3), field varchar2 (3); -- insert data to test insert into AB values (1, 'A '); insert into AB values (1, 'B'); insert into AB values (1, 'C'); insert into AB values (2, 'D '); insert into AB values (2, 'E'); insert into AB values (2, 'F'); select * from AB; -- create function f_ AB () it is used to connect multiple characters. The characters are separated by commas (,). The fid fields of the table are passed in to separate the field fields with the same fid fields with commas, returns the long string create or replace function f _ AB (l_fid number) -- return type return varchar2 is -- Define return value l_result varchar2 (20); -- create a cursor to receive field fields of records with the same fid field. cursor c is select field from AB where fid = l_fid; begin -- assign the initial value l_result: = ''To the returned value; -- use the for loop to traverse the cursor, use l_row to receive each element for l_row in c loop -- connect the field l_result: = l_result | l_row.field | ','; -- end the loop end loop; -- return data (note ',' on the left) return rtrim (l_result, ','); -- end of the function; -- Test select fid, f_ AB (fid) fro M AB group by fid; example (3) -- Question 1: Table A data is as follows: FYear FNum -- 2006 1 -- 2006 2 -- 2006 3 -- 2007 4 -- 2007 5 -- 2007 6 -- display in the following format: Year? 2006 2007 -- Summary 6 15 -- create table yearnum (FYear number (10) not null, FNum number (10) not null ); www.2cto.com -- insert data into the table to test insert into yearnum values (2006, 1); insert into yearnum values (2006, 2); insert into yearnum values (2006, 3 ); insert into yearnum values (2007, 4); insert into yearnum values (2007, 5); insert into yearnum values (2007, 6); -- create a function f_count () input the fyear field of the table to add the fnum fields of records with the same fyear field together and return the total value create or replace function f_count (l_year number) return number is l_sum number (10); -- creates an fnum field for storing records of the same fyear field with a cursor. cursor c is select y. fnum from yearnum y where y. fyear = l_year; c_temp c % rowtype; www.2cto.com begin l_sum: = 0; -- open the cursor open c; -- loop start loop -- fetch the Data fetch c into c_temp; -- exit loop exit at the end of the cursor exit when c % notfound; -- fnum field value addition l_sum: = l_sum + c_temp.fnum; -- end loop of loop end; return l_sum; -- close cursor close c; -- function end; -- Test select y. fyear year, f_count (fyear) summary from yearnum y group by y. fyear;