Notes
Notes - notes.io |
name shivani
2019/feb/17
week 16
function */
--server
SET SERVEROUTPUT on
--desc instructors
DESC instructors;
--creating function
CREATE OR REPLACE FUNCTION func_instructor_ct RETURN NUMBER IS
vn_instructor_ct NUMBER(3);
BEGIN
SELECT COUNT(*)
INTO vn_instructor_ct
FROM instructors;
RETURN vn_instructor_ct;
END func_instructor_ct;
/
--creating procedure
CREATE OR REPLACE PROCEDURE pro_using_func IS
vn_no_of_instructors NUMBER(3);
BEGIN
vn_no_of_instructors:= func_instructor_ct;
DBMS_OUTPUT.PUT_LINE('there are ' || vn_no_of_instructors|| 'instructor(s) in the database.');
END pro_using_func;
/
EXEC pro_using_func
-- output is--
-- there is 14 instructors in the database
--creating function
CREATE OR REPLACE FUNCTION func_param_ct (in_salary NUMBER)RETURN NUMBER IS
vn_param_ct instructors.salary%TYPE;
BEGIN
SELECT COUNT(*)
INTO vn_param_ct
FROM instructors i
WHERE i.salary > in_salary;
RETURN vn_param_ct;
END func_param_ct;
/
--creating procedure
CREATE OR REPLACE PROCEDURE pro_param_using_func(in_salary NUMBER)IS
vn_parm instructors.salary%TYPE;
BEGIN
vn_parm:= func_param_ct(in_salary);
DBMS_OUTPUT.PUT_LINE('there are ' || vn_parm|| 'instructor(s) in the database.');
END pro_param_using_func;
/
EXEC pro_param_using_func(2000);
--BOUNUS ACTIVATE ONE
CREATE TABLE staff(
staff_id NUMBER(4),
firstname VARCHAR2(45),
surname VARCHAR2(45),
username VARCHAR2(45));
INSERT INTO staff VALUES (1000, 'ALISON','APPLE', NULL);
INSERT INTO staff VALUES (1001, 'BERTY','BANANA', NULL);
INSERT INTO staff VALUES (1002, 'CANDY','CANE', NULL);
CREATE OR REPLACE FUNCTION fu_staff RETURN VARCHAR2 IS
vc_name VARCHAR2(30);
BEGIN
SELECT CONCAT(SUBSTR(firstname,1,2), SUBSTR(surname,1,5))
INTO vc_name
FROM staff
WHERE staff_id=1;
RETURN vc_name;
END fu_staff;
/
CREATE OR REPLACE PROCEDURE pro_fu_using(in_staff NUMBER)IS
vc_yourname VARCHAR2(30);
BEGIN
vc_yourname := fu_staff(in_staff);
UPDATE staff SET username = vc_yourname
WHERE staff_id = in_staff;
DBMS_OUTPUT.PUT_LINE('your name is'|| vc_yourname);
END pro_fu_using;
/
EXEC pro_fu_using
|
|
Notes.io is a web-based application for taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000 notes created and continuing...
With notes.io;
- * You can take a note from anywhere and any device with internet connection.
- * You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
- * You can quickly share your contents without website, blog and e-mail.
- * You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
- * Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.
Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.
Easy: Notes.io doesn’t require installation. Just write and share note!
Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )
Free: Notes.io works for 12 years and has been free since the day it was started.
You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;
Email: [email protected]
Twitter: http://twitter.com/notesio
Instagram: http://instagram.com/notes.io
Facebook: http://facebook.com/notesio
Regards;
Notes.io Team
