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
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>
Concatenates two strings into one:
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>
Function return Integer
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
Multiple RETURN Statements
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>
Return a type
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>
Return a varray from a function
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>
RETURN statement.
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.
Return value from a function
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>
Return varchar2 value from function
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!
Save the returning value from a procedure to a variable
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>
This script demonstrates using a record type as a function return value
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>
Use function return value in select statement
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>
Use function to check passwords
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>
Use user-defined function to combine and format columns
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.
We use user function in DML statements
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.