Oracle PL/SQL/Stored Procedure Function/Function Return
Содержание
- 1 Append result from generator function to a table
- 2 Concatenates two strings into one:
- 3 Function return Integer
- 4 Multiple RETURN Statements
- 5 Return a type
- 6 Return a varray from a function
- 7 RETURN statement.
- 8 Return value from a function
- 9 Return varchar2 value from function
- 10 Save the returning value from a procedure to a variable
- 11 This script demonstrates using a record type as a function return value
- 12 Use function return value in select statement
- 13 Use function to check passwords
- 14 Use user-defined function to combine and format columns
- 15 We use user function in DML statements
Append result from generator function to a table
<source lang="sql">
SQL> SQL> SQL> create table gift(
2 gift_id NUMBER, 3 emp_id NUMBER, 4 register_date DATE , 5 total_price NUMBER(7,2), 6 DELIVER_DATE DATE, 7 DELIVER_TIME VARCHAR2(7), 8 payment VARCHAR2(2) , 9 EMP_NO NUMBER(3,0), 10 DELIVER_NAME VARCHAR2(35), 11 message VARCHAR2(100) 12 ) 13 storage(initial 50m);
Table created. SQL> SQL> alter table gift
2 add constraint gift_pk primary key(gift_id);
Table altered. SQL> SQL> analyze table gift compute statistics; Table analyzed. SQL> SQL> SQL> create or replace type gift_type as object (
2 gift_id NUMBER, 3 emp_id NUMBER, 4 register_date DATE , 5 total_price NUMBER(7,2), 6 DELIVER_DATE DATE, 7 DELIVER_TIME VARCHAR2(7), 8 payment VARCHAR2(2) , 9 EMP_NO NUMBER(3,0), 10 DELIVER_NAME VARCHAR2(35), 11 message VARCHAR2(100) 12 ); 13 /
SQL> SQL> create or replace type gift_table as table of gift_type;
2 /
Type created. SQL> SQL> create or replace function gift_generator(p_num_rows in number)
2 RETURN gift_table 3 AS 4 v_gift_table gift_TABLE := gift_table(); 5 BEGIN 6 7 for i in 1..p_num_rows loop 8 v_gift_table.EXTEND; 9 v_gift_table(i) := ( gift_type(i,i,sysdate,0,sysdate,null,"CA",1,null,null )); 10 11 end loop; 12 13 return v_gift_table; 14 15 END; 16 /
Function created. SQL> show errors No errors. SQL> SQL> SQL> declare
2 v_start number;
3 v_run1 number;
4 begin
5 v_start := dbms_utility.get_time;
6 insert /*+APPEND */ into gift (select * from table(gift_generator(100) ) );
7 v_run1 := dbms_utility.get_time - v_start;
8 dbms_output.put_line("no pipe "||v_run1);
9 end;
10 /
no pipe 5 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table gift; Table dropped. SQL>
</source>
Concatenates two strings into one:
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE FUNCTION join_strings(string1 VARCHAR2, string2 VARCHAR2 ) RETURN VARCHAR2 IS
2 BEGIN 3 RETURN string1 ||" "|| string2||"."; 4 END; 5 /
Function created. SQL>
</source>
Function return Integer
<source lang="sql">
SQL> SQL> -- The WHILE loop as part of a function. SQL> SQL> CREATE OR REPLACE FUNCTION noSpaceF(p_pass_string VARCHAR2)
2 RETURN NUMBER IS 3 counter INTEGER := 1; 4 v_return NUMBER := 0; 5 BEGIN 6 WHILE counter <= LENGTH(p_PASS_STRING) LOOP 7 IF SUBSTR(p_PASS_STRING,counter,1) != " " THEN 8 v_return := v_return + 1; 9 ELSE 10 NULL; 11 END IF; 12 counter := counter + 1; 13 END LOOP; 14 RETURN v_return; 15 END noSpaceF; 16 /
Function created. SQL> SQL> select noSpaceF("www.sqle.ru www.sqle.ru") from dual; NOSPACEF("WWW.sqle.ruWWW.sqle.ru")
28
</source>
Multiple RETURN Statements
<source lang="sql">
SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION ClassInfo (
2 p_Department session.department%TYPE, 3 p_Course session.course%TYPE) 4 RETURN VARCHAR2 IS 5 6 studentCount NUMBER; 7 studentMax NUMBER; 8 v_PercentFull NUMBER; 9 BEGIN 10 SELECT current_lecturer, max_lecturer 11 INTO studentCount, studentMax 12 FROM session 13 WHERE department = p_Department 14 AND course = p_Course; 15 16 v_PercentFull := studentCount / studentMax * 100; 17 18 IF v_PercentFull = 100 THEN 19 RETURN "Full"; 20 ELSIF v_PercentFull > 80 THEN 21 RETURN "Some Room"; 22 ELSIF v_PercentFull > 60 THEN 23 RETURN "More Room"; 24 ELSIF v_PercentFull > 0 THEN 25 RETURN "Lots of Room"; 26 ELSE 27 RETURN "Empty"; 28 END IF; 29 END ClassInfo; 30 /
Function created. SQL> SQL> drop table session; Table dropped. SQL> SQL>
</source>
Return a type
<source lang="sql">
SQL> SQL> CREATE TABLE a_table(COL1 NUMBER); Table created. SQL> SQL> CREATE or replace TYPE debug_o AS OBJECT ( string VARCHAR2(100) );
2 /
SQL> CREATE or replace TYPE debug_t AS TABLE OF debug_o;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION tracer RETURN debug_t AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 v_trace debug_t := debug_t();
4 BEGIN
5 v_trace.EXTEND;
6 v_trace(v_trace.LAST) := debug_o("Started Insert At " || TO_CHAR(SYSDATE,"HH24:MI:SS"));
7 INSERT INTO a_table VALUES(1);
8 COMMIT;
9 v_trace.EXTEND;
10 v_trace(v_trace.LAST) := debug_o("Completed Insert At " || TO_CHAR(SYSDATE,"HH24:MI:SS"));
11 RETURN(v_trace);
12 END;
13 /
Function created. SQL> SQL> SELECT * FROM TABLE(tracer)
2 /
STRING
Started Insert At 19:30:39 Completed Insert At 19:30:39 SQL> SQL> drop table a_table; Table dropped. SQL>
</source>
Return a varray from a function
<source lang="sql">
SQL> SQL> create table gift(
2 gift_id NUMBER, 3 emp_id NUMBER, 4 register_date DATE , 5 total_price NUMBER(7,2), 6 DELIVER_DATE DATE, 7 DELIVER_TIME VARCHAR2(7), 8 payment VARCHAR2(2) , 9 EMP_NO NUMBER(3,0), 10 DELIVER_NAME VARCHAR2(35), 11 message VARCHAR2(100) 12 ) 13 storage(initial 50m);
Table created. SQL> SQL> alter table gift
2 add constraint gift_pk primary key(gift_id);
Table altered. SQL> SQL> analyze table gift compute statistics; Table analyzed. SQL> SQL> create or replace type gift_type as object
2 ( gift_id NUMBER, 3 emp_id NUMBER, 4 register_date DATE , 5 total_price NUMBER(7,2), 6 DELIVER_DATE DATE, 7 DELIVER_TIME VARCHAR2(7), 8 payment VARCHAR2(2) , 9 EMP_NO NUMBER(3,0), 10 DELIVER_NAME VARCHAR2(35), 11 message VARCHAR2(100) 12 ); 13 /
SQL> SQL> create or replace type gift_table as table of gift_type;
2 /
Type created. SQL> SQL> create or replace function gift_generator_piped
2 (p_num_rows in number) 3 RETURN gift_table 4 PIPELINED 5 AS 6 BEGIN 7 8 for i in 1..p_num_rows loop 9 PIPE ROW ( gift_type(i,i,sysdate,0,sysdate,null,"CA",1,null,null )); 10 end loop; 11 12 return; 13 14 END; 15 /
Function created. SQL> show errors No errors. SQL> SQL> SQL> declare
2 v_start number;
3 v_run1 number;
4 begin
5 v_start := dbms_utility.get_time;
6 insert /*+APPEND */ into gift (select * from table(gift_generator_piped(1000) ) );
7 v_run1 := dbms_utility.get_time - v_start;
8 dbms_output.put_line("piped "||v_run1);
9
10 end;
11 /
piped 2 PL/SQL procedure successfully completed. SQL> drop table gift; Table dropped. SQL>
</source>
RETURN statement.
<source lang="sql">
SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION ClassInfo(
2 p_Department session.department%TYPE, 3 p_Course session.course%TYPE) 4 RETURN VARCHAR2 IS 5 6 studentCount NUMBER; 7 studentMax NUMBER; 8 v_PercentFull NUMBER; 9 BEGIN 10 SELECT current_lecturer, max_lecturer 11 INTO studentCount, studentMax 12 FROM session 13 WHERE department = p_Department 14 AND course = p_Course; 15 16 v_PercentFull := studentCount / studentMax * 100; 17 18 IF v_PercentFull = 100 THEN 19 RETURN "Full"; 20 ELSIF v_PercentFull > 80 THEN 21 RETURN "Some Room"; 22 ELSIF v_PercentFull > 60 THEN 23 RETURN "More Room"; 24 ELSIF v_PercentFull > 0 THEN 25 RETURN "Lots of Room"; 26 ELSE 27 RETURN "Empty"; 28 END IF; 29 END ClassInfo; 30 /
Function created. SQL> SQL> SQL> drop table session; Table dropped.
</source>
Return value from a function
<source lang="sql">
SQL> -- The mypi function. SQL> CREATE OR REPLACE FUNCTION mypi
2 RETURN NUMBER IS 3 BEGIN 4 NULL; 5 RETURN 3.14; 6 END mypi; 7 /
Function created. SQL> SQL> -- Verifying the mypi function. SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("value of pi is " || mypi());
3 END;
4 /
value of pi is 3.14 PL/SQL procedure successfully completed. SQL>
</source>
Return varchar2 value from function
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
5 RETURN p1;
6 END CallFunc;
7 /
Function created. SQL> VARIABLE v_Output VARCHAR2(50); SQL> CALL CallFunc("Hello!") INTO :v_Output; CallFunc called with Hello! Call completed. SQL> PRINT v_Output V_OUTPUT
Hello!
</source>
Save the returning value from a procedure to a variable
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
5 RETURN p1;
6 END CallFunc;
7 /
Function created. SQL> SQL> VARIABLE v_Output VARCHAR2(50); SQL> CALL CallFunc("Hello!") INTO :v_Output; CallFunc called with Hello! Call completed. SQL> PRINT v_Output V_OUTPUT
Hello! SQL> SQL>
</source>
This script demonstrates using a record type as a function return value
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE emp
2 (emp_id INTEGER NOT NULL 3 ,fname VARCHAR2(30 CHAR) NOT NULL 4 ,mid_name VARCHAR2(1 CHAR) 5 ,lname VARCHAR2(30 CHAR) NOT NULL 6 ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));
Table created. SQL> SQL> SQL> SQL> SET ECHO ON SQL> SQL> SQL> SQL> DECLARE
2 TYPE emp_record IS RECORD (emp_id INTEGER,fname VARCHAR2(30 CHAR),mid_name VARCHAR2(1 CHAR),lname VARCHAR2(30 CHAR));
3
4 emp emp_RECORD;
5
6 FUNCTION get_row (emp_id_in INTEGER)
7 RETURN emp_RECORD IS
8 CURSOR c (emp_id_cursor INTEGER) IS SELECT * FROM emp WHERE emp_id = emp_id_cursor;
9
10 BEGIN
11 FOR i IN c(emp_id_in) LOOP
12 RETURN i;
13 END LOOP;
14
15 END get_row;
16
17 BEGIN
18 emp := get_row(1);
19 dbms_output.put_line(CHR(10));
20 dbms_output.put_line("emp_ID : "||emp.emp_id);
21 dbms_output.put_line("fname : "||emp.fname);
22 dbms_output.put_line("mid_name : "||emp.mid_name);
23 dbms_output.put_line("lname : "||emp.lname);
24
25 END;
26 /
DECLARE
ERROR at line 1: ORA-06503: PL/SQL: Function returned without value ORA-06512: at line 15 ORA-06512: at line 18
SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
Use function return value in select statement
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 $1,234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 $6,661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 $6,544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 $2,344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 $2,334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 $4,322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 $7,897.78 New York Manager ID Employee"s Fi LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
------------- ---------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 $1,232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> -- Cursor without parameters (simplest) SQL> CREATE OR REPLACE Function FindEmployeeSalary ( name_in IN varchar2 ) RETURN number
2 IS 3 mySalary number; 4 5 CURSOR c1 6 IS 7 SELECT salary 8 from employee 9 where first_name = name_in; 10 11 BEGIN 12 13 open c1; 14 fetch c1 into mySalary; 15 16 if c1%notfound then 17 mySalary := 0; 18 end if; 19 20 close c1; 21 22 RETURN mySalary; 23 24 END; 25 /
Function created. SQL> SQL> select FindEmployeeSalary(first_name) from employee; FINDEMPLOYEESALARY(FIRST_NAME)
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
FINDEMPLOYEESALARY(FIRST_NAME)
6544.78
8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Use function to check passwords
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION myFunction(in_username IN VARCHAR,in_new_password IN VARCHAR,in_old_password IN VARCHAR)
2 RETURN BOOLEAN 3 AS 4 pwd_okay BOOLEAN; 5 BEGIN 6 IF in_new_password = in_username THEN 7 raise_application_error(-20001, "Password may not be username"); 8 END IF; 9 RETURN TRUE; 10 END myFunction; 11 /
Function created. SQL>
</source>
Use user-defined function to combine and format columns
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created.
SQL>
SQL> create or replace function FORMAT_EMP (DeptNo IN number,EmpName IN varchar2,Title IN varchar2) return varchar2
2 is 3 concat_rslt varchar2(100); 4 begin 5 concat_rslt :="Department: " || to_char(DeptNo) ||" emp: " || initcap(EmpName) ||" Title: " || initcap(Title); 6 return (concat_rslt); 7 end; 8 /
Function created. SQL> SQL> select format_emp(deptno,ename,job) from emp; FORMAT_EMP(DEPTNO,ENAME,JOB)
Department: 20 emp: Smith Title: Clerk Department: 30 emp: Allen Title: Salesman Department: 30 emp: Ward Title: Salesman Department: 20 emp: Jones Title: Manager Department: 30 emp: Martin Title: Salesman Department: 30 emp: Blake Title: Manager Department: 10 emp: Clark Title: Manager Department: 20 emp: Scott Title: Analyst Department: 10 emp: King Title: President Department: 30 emp: Turner Title: Salesman Department: 20 emp: Adams Title: Clerk FORMAT_EMP(DEPTNO,ENAME,JOB)
Department: 30 emp: James Title: Clerk Department: 20 emp: Ford Title: Analyst Department: 10 emp: Miller Title: Clerk 14 rows selected. SQL> SQL> drop table emp; Table dropped.
</source>
We use user function in DML statements
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> CREATE TABLE myTable
2 (num_col NUMBER 3 ,char_col VARCHAR2(60));
Table created. SQL> SQL> CREATE OR REPLACE FUNCTION FullName (p_empID emp.ID%TYPE) RETURN VARCHAR2 IS
2 3 v_Result VARCHAR2(100); 4 BEGIN 5 SELECT fname || " " || lname INTO v_Result FROM emp WHERE ID = p_empID; 6 7 RETURN v_Result; 8 END FullName; 9 /
Function created. SQL> SQL> DECLARE
2 CURSOR c_IDs IS SELECT ID FROM emp WHERE ID BETWEEN 10 AND 20; 3 BEGIN 4 FOR v_Rec IN c_IDs LOOP 5 INSERT INTO myTable (num_col, char_col) 6 VALUES (v_Rec.ID, FullName(v_Rec.ID)); 7 END LOOP; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL> drop table myTable; Table dropped.
</source>
